Data Pipelines

ELT vs ETL

ETL (extract, transform, load): transform before loading — historically because storage/compute were expensive. ELT (extract, load, transform): land raw data in the warehouse first, transform inside it with SQL/dbt. ELT is the modern default because cloud warehouses make storage cheap and compute elastic.

• Why ELT wins for a first hire: raw data is preserved (you can re-transform when logic changes without re-extracting), transformations are versioned SQL, and you're not maintaining bespoke transform code in an extraction layer.
• When you still pre-transform: PII that must never land raw (mask/drop at ingest), or huge volumes where you filter before loading to control cost.

The layered warehouse

Even as one person, impose layers — it's how you keep trust as complexity grows. The common naming:

This maps directly onto a dbt project (staging/, intermediate/, marts/). Mentioning it signals you'll build maintainable structure, not a tangle of one-off queries.

Batch vs streaming — and why you'll choose batch first

Batch: process data in scheduled chunks (hourly/daily). Simple, cheap, easy to reason about and backfill. Streaming: process events continuously (Kafka, Kinesis, Flink). Low latency, but far more operational complexity — exactly-once semantics, state, watermarks, and harder debugging.

A pragmatic middle ground: micro-batch (every few minutes) gives near-real-time freshness with batch's simplicity. Often the right "real-time" answer.

Idempotency — the most important property

An idempotent pipeline produces the same result whether it runs once or five times. Without it, retries and backfills duplicate or corrupt data. The standard techniques:

• Delete-insert / overwrite by partition: before loading day D, delete day D's rows, then insert. Re-running just replaces the partition. Clean and the most common pattern.
• MERGE / upsert on a primary key: insert new keys, update existing ones. Requires a reliable unique key.
• Deterministic outputs: no now() or random ordering baked into stored values; given the same input the transform yields the same output.

-- Re-runnable load of one day's usage. Safe to run any number of times.
BEGIN;
DELETE FROM usage_daily WHERE day = DATE '2026-06-23';
INSERT INTO usage_daily
SELECT DATE(started_at) AS day, customer_id, SUM(gpu_seconds) AS gpu_seconds
FROM usage_events
WHERE started_at >= '2026-06-23' AND started_at < '2026-06-24'
GROUP BY 1,2;
COMMIT;

Contrast with a naive INSERT ... SELECT with no delete: re-running doubles the day. Interviewers probe exactly this.

Incremental loads

Reprocessing all history every run is slow and expensive. Incremental models process only new/changed rows since a high-water mark.

• High-water mark: track the max updated_at (or an ingestion timestamp) processed; next run reads rows above it.
• Lookback window: reprocess the last N hours/days too, so late-arriving rows within the window are captured. A small lookback is cheap insurance.
• The trap: using created_at when rows can be updated later — you'll miss updates. Use a column that moves on every change.

{{ config(materialized='incremental', unique_key='event_id') }}
SELECT * FROM {{ source('app','usage_events') }}
{% if is_incremental() %}
  -- 3-day lookback to catch late/updated rows
  WHERE updated_at > (SELECT MAX(updated_at) FROM {{ this }}) - INTERVAL '3 days'
{% endif %}

Late & out-of-order data

Events arrive after their event-time (mobile offline, retries, clock skew). Decide how to handle it explicitly:

• Event time vs ingestion time: partition and aggregate by event time for business correctness, but track ingestion time for ops/debugging. Knowing the difference is a senior signal.
• Lookback reprocessing (above) folds late data into the correct day.
• Accept the cutoff: document that, say, data older than 7 days is final. Tradeoff between correctness and never-ending recomputation.

Backfills

Loading or reprocessing historical data — for a new model, a bug fix, or a new source. The interview wants to hear that backfills are safe and bounded:

• Idempotency makes backfills safe — you can replay any partition without duplication. This is why it's the first principle.
• Chunk by partition (per day/month) rather than one giant query — bounds cost, lets you resume after failure, avoids warehouse blowups.
• Backfill into a side table, validate, then swap for risky reprocessing, so you never expose half-rebuilt data.
• Watch cost on usage-based warehouses — a naive full-history backfill can be a surprise bill. Estimate bytes scanned first.

Schema evolution

Source schemas change without warning — that's the reality you must absorb. Strategies:

• Additive changes are safe (new nullable column). Drops and type changes break downstream — these are what you defend against.
• Land semi-structured raw (JSON/VARIANT) so a new field doesn't break ingestion; promote fields into typed columns deliberately in staging.
• Schema-on-read vs schema-on-write: raw layer is forgiving (read-time parsing), modeled layers are strict (write-time contracts). Know the terms.
• Tests as tripwires: a "expected columns present / types unchanged" test catches an upstream change before it silently corrupts a metric. Leads into chapter 05.

Orchestration

The scheduler that runs tasks in dependency order with retries and visibility. Options: Airflow (ubiquitous, mature, verbose), Dagster (asset-centric, great local dev, modern), Prefect (Pythonic, lightweight), or managed/built-in schedulers (dbt Cloud, cloud-native).

Concepts to know regardless of tool: DAG (dependency graph), idempotent tasks, retries with backoff, sensors (wait for upstream readiness), SLAs/alerting, and backfill support.

CDC & ingestion patterns

• Full snapshot: copy the whole table each run. Simple, fine for small dimension tables; wasteful for large ones.
• Incremental key: pull rows where updated_at > last_seen. Good default for app DBs with a reliable update column.
• CDC (change data capture): stream inserts/updates/deletes from the DB's transaction log (Debezium, Fivetran log-based). Captures deletes and is low-impact on the source — but more moving parts.
• Event/stream ingestion: product events to a queue/warehouse directly. Watch for duplicates (at-least-once delivery → dedup in staging, see problem 6).

First-hire lean: buy ingestion, build transformation. Managed connectors (Fivetran/Airbyte) for getting data in; your value-add is the modeling, not maintaining a Salesforce connector. Covered in chapter 08.

Designing a pipeline out loud (interview script)

When asked "design a pipeline to get usage data into reporting," walk this skeleton:

1. Clarify: volume, freshness need, can rows be updated/deleted, what decisions the output drives.
2. Ingest: how data lands in raw (managed connector / CDC / event stream), append-only, partitioned by load date.
3. Stage: cast, rename, dedup (at-least-once → ROW_NUMBER), one model per source table.
4. Model: join to the business grain, build the fact/dim (see chapter 06).
5. Make it idempotent & incremental: delete-insert by partition, high-water mark with lookback.
6. Test & monitor: freshness, volume, key uniqueness, the reconciliation check (chapter 05).
7. Orchestrate: schedule, dependencies, retries, alert on failure.
8. State tradeoffs: batch over streaming, buy ingestion over build, and what would change your mind.

✓ Check yourself

Answer from memory before opening each one.

1. A daily load runs INSERT ... SELECT for yesterday at 02:00. The job fails at a later step, and the orchestrator retries it. What does the table hold afterwards, and what change makes the load safe to retry?

   Show answer

   Yesterday's rows appear twice, because the first insert was never undone. Make the load idempotent: delete that day's partition and insert it again inside one transaction, or MERGE on a key. A re-run then replaces the day instead of adding to it. See idempotency.

2. An incremental model filters on created_at greater than the maximum already loaded. Order statuses change after creation. What breaks, and what do you use instead?

   Show answer

   Updates never arrive, because a changed row keeps its old created_at. Use a column that moves on every change, such as updated_at, with a unique key so changed rows overwrite, and add a lookback window for late rows. See incremental loads.

3. A usage event for Tuesday lands on Thursday. Your model has a three-day lookback. What happens? What if one lands ten days late?

   Show answer

   The Thursday event folds into Tuesday, because the lookback reprocesses those days. The ten-day-late event is missed unless you reprocess further back. Document a finality cutoff, such as seven days, and reconcile older corrections separately. See late and out-of-order data.

4. A vendor table has two million rows, no updated_at column, and rows get deleted. Which ingestion pattern do you choose, and which tempting one is wrong?

   Show answer

   A full snapshot each run is fine at two million rows and captures deletes. Log-based CDC is better if the source supports it. An incremental key is wrong here: with no reliable timestamp it misses changes, and it can never see a deleted row. See CDC and ingestion patterns.

5. A logic bug means 18 months of data must be rebuilt on a warehouse that bills per byte scanned. How do you run it?

   Show answer

   Estimate bytes first with a dry run. Process one partition at a time so a failure can resume and cost stays bounded. Write to a side table, validate against the live one, then swap. Idempotent partitions make each chunk safe to repeat. See backfills.

6. You have three pipelines and one engineer. A colleague says to install Airflow because everyone uses it. What do you answer, and what would change your mind?

   Show answer

   Start with a managed scheduler or Dagster, since running Airflow is operations work for one person. Move when DAG count, dependencies between teams, or the need for sensors and dynamic fan-out outgrow the simple tool. Match the tool to the stage. See orchestration.

Takeaway

• Idempotency comes first: a re-run must give the same result, through delete-insert by partition or MERGE on a key, with deterministic transforms.
• Land raw data append-only, stage it lightly, model it last; the raw layer keeps every later change replayable.
• Start with batch, use micro-batch as the middle step, and add streaming only when a decision needs sub-minute data.
• An incremental load needs a column that moves on every change plus a lookback window; created_at misses updates.
• Aggregate by event time and write down the finality cutoff for late data.
• Backfill by partition, estimate the cost first, and swap in only after validation.
• Buy ingestion and build transformation, and defer heavy orchestration until complexity pays for it.

Batch is the right start, but interviews also probe streaming semantics, CDC internals and table formats. → Pipelines Advanced

Further reading

• Incremental models — dbt Labs docs, 2026. High-water marks, unique keys and the lookback pattern.
• How we structure our dbt projects — dbt Labs docs, 2026. Staging, intermediate and marts layers with the rules for each.
• DAGs — Apache Airflow docs, 2026. How a DAG declares tasks and dependencies, and how runs are scheduled.
• Assets — Dagster docs, 2026. The asset-centric model the chapter recommends for a small team.
• Debezium reference — Debezium project, 2026. How log-based change data capture works and what events it emits.