What this prompt does
This prompt makes the AI build a production-ready Apache Airflow DAG for a specific data pipeline. You define [pipeline_purpose], the [schedule_interval], the [data_sources], and the [destination], and the model generates a DAG with proper default_args — owner, retries=[retry_count], retry delay, failure email to [alert_email], and an SLA of [sla_duration] — plus task dependencies that model the real extract/transform/load flow.
The structure works because it bakes in the parts that keep a DAG maintainable a year later. It uses dynamic task generation driven by a config dictionary for [dynamic_tasks], so adding a new source is a config change rather than a code edit. It wires an on_failure_callback that sends task context to [alert_channel], inserts data quality checks via [quality_framework] between extract and load, manages XCom for metadata with cleanup to avoid table bloat, and makes tasks idempotent with date-partitioned staging and MERGE/upsert. Each variable maps to a concrete operational decision, so the output is a DAG you can run, not a skeleton.
When to use it
- You're building a scheduled ETL pipeline and want the alerting, retries, and SLA configured correctly from the start.
- You expect to add new data sources over time and want config-driven dynamic tasks instead of copy-pasted DAG code.
- You need data quality gates between extract and load so bad data doesn't reach the warehouse.
- You want idempotent tasks that are safe to re-run after a partial failure or backfill.
- You're routing failure alerts to a chat channel and want full task context (dag_id, task_id, execution_date, log URL) in the message.
- You're standardizing how your team writes DAGs and want a solid reference implementation.
Example output
Expect a complete Python DAG file: the default_args block, a config dictionary driving the dynamic source tasks, parallel extract tasks, transform tasks targeting your [executor_type], quality-check tasks, and load tasks with the correct upstream dependencies. It includes the failure callback, XCom usage with cleanup, and date-partitioned staging plus MERGE logic for the final load, with comments explaining the dependency graph.
Pro tips
- Make
[dynamic_tasks]genuinely config-driven: each entry should carry its own credentials and rate limits so adding a feed never touches DAG logic. - Set
[retry_count]and[sla_duration]from real behavior — too many retries can mask a broken source, and an SLA shorter than the actual runtime fires constant false alarms. - Choose
[executor_type]to match your cluster; a KubernetesExecutor reference won't help if you're on a Celery or local setup. - Keep XCom payloads small (row counts, file paths) and rely on the cleanup step — pushing large objects through XCom bloats the metadata DB.
- Put the
[quality_framework]checks before the load, not after, so failures quarantine bad data instead of poisoning the destination. - Verify idempotency by running the same execution date twice; the MERGE/upsert and partitioned staging should leave the destination unchanged.
