Prompting for Data Pipelines and ETL

TL;DR: Data pipeline prompts for AI-generated SQL, dbt models, and Airflow DAGs need review before anything touches real data, because a broken transform rarely throws an error. It just loads the wrong rows, and nobody notices until a report is wrong days later. Check for idempotency, explicit schema-drift handling, and backfill logic before the first run.

A pipeline that runs and a pipeline that runs correctly are different claims, and only reading the generated SQL or DAG code verifies the second one. Application code with a bug usually fails somewhere visible: a test, a stack trace, a 500. A transform with a bug usually just produces a table: fewer rows than yesterday, a duplicated customer, a column quietly full of nulls where a join stopped matching. It loads, the DAG goes green, and the first evidence anything is wrong is a dashboard number that looks off a few days later, by which point the source system's own retention window may have already rolled past the state you'd need to diff against to see what actually changed. That gap between "it ran" and "it ran correctly" is wider here than almost anywhere else you'd use a generated prompt, because the object it operates on is data you cannot always get back. The same read-before-you-merge discipline covered in Prompting for CI/CD Pipelines applies here too: a workflow file is executable against your infrastructure, and a transform is executable against your data, and neither one gets a pass just because the syntax highlighting made it look tidy.

Why Does a Bad ETL Prompt Fail Quietly, Days Later?

Because most of the ways a generated transform goes wrong don't stop the job from finishing; they just change what it produces. Five specific shapes cover almost everything that goes wrong when a model writes the transform instead of a person, and none of them look alarming in the generated code. They look like slightly-too-simple SQL.

Every row in that table describes something that compiles, runs, and exits zero. That's the actual danger: none of it looks like a bug until someone reconciles a number against a source system and the two don't match, and by then the job has often run several more times on top of the bad state. It's worth reading this table with the same posture as Red-Team Your Own Prompt Before You Trust the Output: the question isn't "does this SQL look reasonable," it's "what does this do to a row it wasn't expecting."

What Makes a Generated Transform Idempotent?

Idempotent means the transform produces the same end state no matter how many times it runs for the same input: a retry after a failure, a scheduler hiccup, or someone manually rerunning a job should never change the answer. That property has to be asked for; it isn't the default shape a model reaches for when the request is "a script that loads yesterday's orders into the table."

dbt's own incremental-strategy documentation is explicit about where the default breaks: the append strategy "doesn't check for duplicates or verify whether a record already exists in the destination." As the same docs continue, "If the same record appears multiple times in the source, it will be inserted again, potentially resulting in duplicate rows" (docs.getdbt.com, verified 4 Sep 2026). That's not a dbt quirk specifically; it's what any bare-INSERT load does in any tool. The fix a prompt needs to name explicitly is a keyed upsert: dbt's own merge strategy, or the warehouse's native MERGE / ON CONFLICT / INSERT ... ON DUPLICATE KEY equivalent, with the unique key stated rather than implied.

Even merge isn't automatically safe by itself. The same docs note that when you specify a unique_key for the merge strategy, "by default, dbt will entirely overwrite matched rows with new values" — correct behavior for a corrected record, and exactly wrong if two legitimate source rows happen to share that key and only one should actually win. A prompt should name the merge key and ask what happens on a duplicate, not assume the model will raise the question unprompted.

-- Illustrative only — verify incremental_strategy support against your own
-- adapter's docs before running; not every dbt adapter supports every strategy.
{{ config(
    materialized='incremental',
    unique_key='order_id',
    incremental_strategy='merge',
    on_schema_change='fail'
) }}

select order_id, customer_id, order_total, updated_at
from {{ source('raw', 'orders') }}
{% if is_incremental() %}
where updated_at > (select max(updated_at) from {{ this }})
{% endif %}

Prompt clause: "Use incremental_strategy='merge' keyed on order_id, not append. State explicitly that rerunning with the same input must not create duplicate or changed rows, and set on_schema_change explicitly rather than leaving it at whatever the adapter defaults to."

How Do You Prompt for a Backfill That Doesn't Re-Break Yesterday's Data?

Backfill and idempotency are related but not the same problem. A load can be perfectly idempotent — safe to rerun for one day — and still corrupt history the moment you ask it to rerun for ninety days, if the transform logic itself has changed since those ninety days actually happened.

Airflow's own docs put it plainly: "You may want to run the Dag for a specified historical period." The worked example given is a DAG created with a start_date of 2024-11-21, where someone needs output from a month earlier (airflow.apache.org, verified 4 Sep 2026). The mechanism matters as much as the intent. The current CLI runs airflow backfill create --dag-id DAG_ID --from-date START_DATE --to-date END_DATE, and per the same page, that command "will re-run all the instances of the dag_id for all the intervals within the start date and end date" — every interval, not just the ones that look wrong. A backfill prompt has to say what changed between then and now, not just supply a date range.

The other half is catchup, which decides whether gaps get filled automatically at all. Airflow's docs are direct that the scheduler default is catchup_by_default=False, so "Dag runs that have not been run since the last data interval are not created by the scheduler upon activation of a Dag" unless catchup=True is set on the DAG itself. A generated DAG that leaves this unstated is making a silent choice for you either way, and a missed run under the default just doesn't run, with no alert attached.

# Illustrative CLI shape from Airflow's own backfill docs — confirm the exact
# flags against your installed version before running against a real scheduler.
airflow backfill create \
  --dag-id load_orders \
  --from-date 2026-06-01 \
  --to-date 2026-06-30 \
  --reprocess-behavior failed

Prompt clause: "If the transform's logic has changed since [date range], say so explicitly and ask whether the backfill should apply today's logic or the logic that was live during that period. State the DAG's current catchup value rather than assuming it." If you're running some version of this same request for a different table or date range every week, Reusable Prompt Variables for Dev Teams covers not retyping that context by hand each time.

Why Does the Same MERGE Statement Behave Differently on Postgres, Snowflake, and BigQuery?

MERGE looks like one SQL statement, and prompting for "a MERGE statement" as if it were one syntax is where a lot of copied answers go wrong. All three of the following are accurate for their own vendor, verified directly against that vendor's own docs, and not interchangeable — which is the point How to Prompt for Tables and Structured Data makes about structured output more generally: the shape a model defaults to is rarely the shape your specific target actually requires.

PostgreSQL's own docs (MERGE was added in version 15) are specific about clause order: "the status of MATCHED or NOT MATCHED is set just once, after which WHEN clauses are evaluated in the order specified... No more than one WHEN clause is executed for any candidate change row" (postgresql.org/docs/15, verified 4 Sep 2026). Reorder your WHEN clauses and you change which action wins for a row that could match more than one.

Snowflake adds a failure mode Postgres's own docs don't describe at all. When more than one source row matches the same target row, the outcome depends on a session parameter, ERROR_ON_NONDETERMINISTIC_MERGE. Snowflake's docs state that if it's TRUE (the default), "the merge returns an error"; if it's FALSE, "one row from among the duplicates is selected to perform the update or delete; the row selected is not defined" (docs.snowflake.com, verified 4 Sep 2026). That second branch is the one to worry about — it isn't a crash, it's a silent update or delete against a row nobody chose on purpose.

BigQuery's own docs describe MERGE as a statement that can "combine INSERT, UPDATE, and DELETE operations into a single statement and perform the operations atomically" (Google Cloud's BigQuery documentation, verified 4 Sep 2026) — atomic in the sense that it runs as one job, not that duplicate matches are resolved the same way Postgres or Snowflake resolve them. The same page flags a subtlety worth asking a model to account for directly: a row inserted partway through a MERGE isn't eligible to be matched again within that same statement, because matching is based on the state of the tables when the query started.

None of this is a reason to avoid MERGE. It's a reason to name the actual warehouse in the prompt and ask specifically what happens on a duplicate match, rather than accepting whichever vendor's syntax the model happened to reach for by default.

What Should You Ask For When a Source Schema Changes Under You?

Schema drift is the quiet version of the same problem: the source system adds, renames, or drops a column, and your transform either breaks loudly — good — or keeps running and just stops being complete — bad. dbt's on_schema_change config exists specifically for this, and its default is the quiet kind. dbt's own docs describe that default plainly: "If you add a column to your incremental model, and execute a dbt run, this column will not appear in your target table. If you remove a column from your incremental model and execute a dbt run, dbt run will fail" (docs.getdbt.com, verified 4 Sep 2026). New data simply isn't there, and nobody is told.

The other two named options — append_new_columns and sync_all_columns — trade that silent gap for a different one worth naming explicitly. dbt's docs note: "None of the on_schema_change behaviors backfill values in old records for newly added columns." So a report that joins old and new rows on a newly added column will still show nulls for everything before the change, by design, not by bug. sync_all_columns also removes columns, inclusive of type changes, and dbt's docs flag a real cost on one specific platform: "On BigQuery, changing column types requires a full table scan" — worth knowing before you turn that setting on for a large table. This is a schema validation decision as much as a prompting one, and it deserves to be named in the request rather than left implicit.

A prompt that just says "handle schema changes gracefully" leaves the model to pick one of these silently on your behalf. Naming the config and stating which failure mode you'd rather have — a loud break in development, or a quiet gap in production — is the actual ask.

A Working Example: A Prompt Template for Idempotent, Backfill-Safe SQL

Put the requirements in the prompt itself rather than trusting a general request for "a data pipeline" to reach for all of them on its own. The template below names the warehouse, the key, the schema-drift policy, and the backfill behavior explicitly — everything above, as one checklist a model can act on directly.

Write a [dbt model / Airflow task / warehouse SQL script] that loads
[source] into [target] on [warehouse: Postgres 17 / Snowflake / BigQuery].

Requirements:
- Idempotent: rerunning with the same input must not create duplicate or
  changed rows. Use an upsert keyed on [unique_key], not a bare INSERT/append.
- If this uses MERGE, state what happens when more than one source row
  matches one target row (should it error, or is deduplication upstream?).
- Schema drift: if a source column is added or removed, say so explicitly —
  don't run silently. State whether new columns should be backfilled or
  left null for existing rows.
- Backfill: if I ask you to reprocess a past date range, confirm whether
  today's transform logic should apply, or the logic that was live during
  that period, before writing the query.
- Do not include a DELETE, TRUNCATE, or full-table overwrite unless I
  explicitly ask for one, and scope any write to the stated partition or
  date range only.

This is a template for a real pipeline — do not run the output against
production data before a human has read every INSERT, UPDATE, DELETE,
MERGE, and TRUNCATE it contains.

None of this requires a warehouse connection, and every clause above works as plain text in ChatGPT, Claude, or Gemini before you go anywhere near a real connection string. What we sell is the layer on top of that: turning "load orders into the warehouse" into the checklist above in one click, so you're not retyping the same five requirements into every pipeline prompt you write, and Best Prompt Management Tools for Developers covers where to keep the template itself once you've settled on one. The free plan gives 5 prompt enhancements a day, forever, per our FAQ page; Pro and Advanced remove that cap.