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.