Data Pipelines

ETL vs ELT

• ETL (Extract, Transform, Load): transform before loading. Older pattern, optimized for storage cost.
• ELT (Extract, Load, Transform): land raw to warehouse, transform inside. The modern default.

Why ELT won:

• Cloud warehouses made compute cheap relative to storage.
• Having raw data lets you re-derive when business logic changes.
• dbt makes warehouse-side transformation ergonomic.
• Multiple consumers can derive different views from the same raw.

ETL still makes sense for specific cases: very high volume where most data is uninteresting (filter early), or PII that must be stripped before landing.

Batch vs streaming

Delivery semantics

Idempotency — the most-asked-about pattern

A pipeline is idempotent when running it twice for the same window produces the same result. Non-negotiable for production data pipelines.

Pattern: idempotency keys

Every event carries a unique ID. Consumers track seen IDs and skip duplicates.

Pattern: window-replaceable

A pipeline run produces output for a specific window (e.g., a date partition). Re-running replaces the output for that window rather than appending.

-- delete + insert into one partition
DELETE FROM fct_orders WHERE date_key = @run_date;

INSERT INTO fct_orders
SELECT ... FROM source WHERE date(occurred_at) = @run_date;

Run a thousand times for the same @run_date → same output. Idempotent.

Pattern: MERGE / upsert

dbt's incremental "merge" strategy. The unique_key determines which rows are updated vs inserted. Re-running on overlapping data updates rather than duplicating.

Backfills

A backfill is "re-run the pipeline for historical windows." It happens when:

• Business logic changed and you need to apply it to history.
• The pipeline had a bug and produced wrong output.
• You're adding a new metric and need to fill in history.
• You're bootstrapping a new model.

The principle

Backfill should use the production code, not a special-cased script. Special-cased backfill scripts diverge from prod and break.

For dbt: parameterize the model by date variable, then run with the variable set to each window:

for d in $(seq 0 30); do
  date_to_backfill=$(date -d "30 days ago + $d days" +%Y-%m-%d)
  dbt run --select fct_orders --vars "{run_date: $date_to_backfill}"
done

For Airflow: partitioned DAG runs. Each run is for a specific logical date; backfilling = scheduling the missing logical dates.

Late-arriving data — the silent killer

Data that should have arrived in window N actually arrives in window N+1 (or N+5). If your pipeline filters "only process new" by current timestamp, late data falls off the edge.

Pattern: lookback window

Always look back further than "since last run."

{% if is_incremental() %}
  -- 24-hour lookback, merged on unique_key
  WHERE occurred_at > (SELECT MAX(occurred_at) FROM {{ this }}) - INTERVAL '24 hours'
{% endif %}

Combined with incremental_strategy='merge' and a unique_key, late events upsert correctly within the lookback window.

Pattern: watermarking (streaming)

Streaming systems use watermarks — "we're now processing data up to this event time; anything later is late." Late events go to a side channel for special handling.

Pattern: reprocessing window

For very late data (days or weeks late), schedule a periodic full-rerun of the prior week. Catches stragglers, costs more, but reliable.

Schema evolution

Upstream schemas change. Strategies for handling it:

Detect

• Source-freshness alerts on missing columns.
• Schema tests that fail when expected columns are missing.
• Schema registry (Confluent, Glue) for streaming events.

Adapt

• dbt on_schema_change='append_new_columns' — auto-add new columns.
• on_schema_change='sync_all_columns' — also drop columns that disappear. Use with caution.
• Explicit staging contract — staging models list the columns they depend on; missing columns fail loudly.

Coordinate

For breaking changes — column renames, type changes, semantic shifts — there's no auto-magic. This is a coordination conversation. The pattern: data contracts. Producers commit to "this schema for this version," consumers commit to "we only rely on documented columns." Schema changes are versioned and announced.

CDC (Change Data Capture)

Replicate database changes in near-real-time without full table dumps. Tools:

• Debezium — open-source Kafka-based CDC.
• Fivetran HVR, Airbyte, Stitch — managed CDC for major databases.
• Snowflake Streams — track changes within Snowflake.
• BigQuery Datastream — managed CDC into BigQuery.

CDC produces a stream of (op_type, before, after, ts) per row change. The warehouse-side challenge: collapse the stream to the current state of each row, while preserving history for SCD-style queries.

Talking points

✓ Check yourself

Answer from memory before opening each one.

1. A job appends yesterday's orders to a table every night. The scheduler retries it after a timeout and the nightly total doubles. What property is missing, and how do you get it?

   Show answer

   Idempotency: a rerun for the same window must produce the same result. Replace the window (delete the partition, then insert), merge on a unique key, or use idempotency keys, rather than blindly appending. See idempotency.

2. The business says it needs a "real-time" dashboard. What do you ask, and what is the cost of simply agreeing?

   Show answer

   Ask what latency the decision actually needs; the answer is often 5 to 15 minutes, which micro-batch serves. Agreeing buys streaming complexity: exactly-once is hard, and debugging and observability get harder. See batch versus streaming.

3. A Kafka consumer loads events into the warehouse with at-least-once delivery. After a broker restart, some events appear twice. Is that a bug, and what is the fix?

   Show answer

   It is expected behaviour of at-least-once delivery. The fix is an idempotent consumer: dedupe on the event ID or merge on a unique key. Exactly-once is usually this combination, not a separate guarantee. See delivery semantics.

4. Your incremental model has a 24-hour lookback, but a mobile app sometimes uploads events three days late. What happens, and what are your options?

   Show answer

   Events later than the window never merge in, so history is silently wrong. Widen the lookback, which costs compute, or schedule a periodic reprocess of the prior week for stragglers. In streaming, route late events to a side channel past the watermark. See late-arriving data.

5. A teammate writes a one-off script to recompute two years of revenue under new logic. What do you object to, and what do you propose?

   Show answer

   A special script drifts from production logic, so the backfill and the live pipeline disagree. Run the production code with the date range as a parameter, one window at a time. See backfills.

6. Upstream renames amount to amount_cents without telling anyone. Why does on_schema_change='append_new_columns' not save you?

   Show answer

   It adds the new column but cannot know it is the old column renamed, and the model's reference to amount still fails or goes null. A rename is a breaking change that needs a contract and coordination between producer and consumer. See schema evolution.

Takeaway

• ELT is the default because warehouse compute is cheap and raw data lets you re-derive after logic changes; ETL still fits when you must filter huge volume early or strip PII before landing.
• "Real-time" usually means "fresher": pick the cheapest tier of daily, hourly or micro-batch that meets the stated latency.
• Exactly-once delivery is approximated by at-least-once delivery plus an idempotent consumer.
• A pipeline is idempotent when a rerun for the same window gives the same result: replace the window or merge on a key, never blindly append.
• Backfills run production code with different parameters, not a special-case script.
• Late data falls off a "since the last run" filter, so use a lookback and a periodic reprocess for very late data.
• Schema drift is detected by tests and absorbed by settings, but breaking changes need a contract between producer and consumer.

Pipelines that run are not the same as pipelines that are right, so the next chapter turns to testing and quality. → Data Quality & Testing

Further reading

• Configure incremental models — dbt Labs docs, accessed 2026. Merge, append, delete+insert and insert_overwrite strategies.
• Dag Runs — Apache Airflow docs, accessed 2026. Logical dates, catchup and backfill semantics.
• Debezium reference documentation — Debezium project, accessed 2026. How log-based change data capture produces change events.
• Message delivery semantics — Apache Kafka documentation, accessed 2026. At-most-once, at-least-once and exactly-once as Kafka defines them.
• The world beyond batch: Streaming 101 — Tyler Akidau, O'Reilly Radar, 2015. Event time, processing time and watermarks.
• Model contracts — dbt Labs docs, accessed 2026. Enforcing column names and types at build time.