What this prompt does
This prompt casts the model as a senior data engineer and forces it to return a runnable Airflow DAG rather than a sketch. You feed it three placeholders -- [source], [tool], and [warehouse] -- and it wires a daily extract-transform-load flow that pulls from your source, transforms with your chosen tool, and loads into the target warehouse. The six numbered deliverables push it past a happy-path skeleton: sensors that wait on upstream readiness, retries with exponential backoff, an SLA-miss alert path, Great Expectations checks that halt the load on failure, idempotent tasks, and lineage tracking.
The structure works because it names the failure modes that actually page you at 3am. [source] decides which sensor and extract operator make sense; [tool] (dbt, Spark, plain SQL) shapes the transform step; and [warehouse] determines the load operator and partition semantics. By demanding idempotency and a quality gate up front, the prompt makes the model design for re-runs and bad rows instead of bolting them on later. Each of the three variables narrows an otherwise generic DAG into one that fits your actual stack, so the operators, retries, and partition handling all line up with what you really run in production.
When to use it
- You're standing up a new daily batch pipeline and want a production shape, not a tutorial DAG.
- A re-run double-loaded a partition and you need an idempotent rewrite that's safe to retry.
- You want Great Expectations quality gates wired so a bad load fails loud instead of silently.
- Upstream data lands unpredictably and you need sensors before extraction starts.
- You need SLA-miss alerting and exponential-backoff retries baked into the DAG from day one.
- You want lineage so a suspicious downstream number is traceable back to its source row.
Example output
You get the full DAG as a single runnable Python file -- imports, default args, the sensor, extract/transform/load tasks with retry and SLA config, the Great Expectations checkpoint task, and the task dependencies expressed explicitly -- followed by one short paragraph explaining the idempotency strategy (typically delete-then-insert or merge keyed on the partition). It reads like something you can drop into a DAGs folder and adjust to your environment, not a high-level diagram you still have to translate into code.
Pro tips
- Set
[source]precisely (e.g.Postgres production replicavs a raw table) so the sensor and extract logic fit your readiness signal. - Match
[tool]to your stack; if you set it todbt, the transform step becomes a dbt run task rather than inline SQL. - Make
[warehouse]specific (Snowflake, BigQuery, Redshift) because partition and merge syntax differ across them. - Test a retry mid-run before you trust the schedule -- a non-idempotent task only bites on the second attempt.
- If the generated idempotency note is vague, ask it to show the exact delete-then-insert or merge statement for your partition key.
- Treat the Great Expectations checkpoint as a hard gate: confirm the load task is downstream of the check, not parallel to it.
