← All handbooks

ETL / ELT Handbook

Moving data reliably, end to end — extract, load, transform; incremental & CDC; idempotent reruns; data quality; orchestration. The engineering discipline behind every dashboard and model.

01 ETL vs ELT

Modern cloud default is ELT: land raw data first (replayable), transform with SQL/dbt where the compute is. dbt = SQL transforms as version-controlled, tested, documented models (the T in ELT).

02 Ingestion & CDC

• Full load — copy everything each run. Simple; fine for small/dimension tables; expensive for big facts.
• Incremental — pull only new/changed rows using a high-water mark (updated_at > last_run or an increasing id).
• CDC (Change Data Capture) — capture inserts/updates/deletes from the source’s transaction log (Debezium, Fivetran, native log readers). Catches deletes & hard updates the watermark misses.
• Batch vs streaming — batch (scheduled windows) vs streaming (Kafka/Kinesis + Spark/Flink) for low-latency. Most analytics is micro-batch.

03 Transformation

• Cleanse: types, nulls, trims, dedupe, standardize units/encodings/timezones (store UTC).
• Conform: map source keys to surrogate keys, unify code/lookup values across sources.
• Model: build dimensions & facts (star schema) or wide marts; derive metrics once, reuse everywhere.
• Layer it (medallion / staging→intermediate→marts in dbt) so each step is testable and re-runnable.

04 Load patterns & SCD

• Append (immutable events), truncate-reload (small dims), upsert/MERGE (mutable entities), insert-overwrite by partition (idempotent reprocess of a date).
• Slowly Changing Dimensions: Type 1 overwrite (no history); Type 2 new row + valid-from/valid-to + current flag (full history); Type 3 add a previous-value column.

-- idempotent partition overwrite (re-run a day safely)
DELETE FROM fact WHERE dt = :run_date;
INSERT INTO fact SELECT ... WHERE dt = :run_date;
-- or MERGE for Type-2 SCD
MERGE INTO dim_customer t USING stg s ON t.id=s.id AND t.is_current
WHEN MATCHED AND t.hash <> s.hash THEN UPDATE SET t.is_current=FALSE, t.valid_to=:now
WHEN NOT MATCHED THEN INSERT ...;

05 Idempotency & backfills

• Idempotent = running the same task with the same input twice yields the same result (no dupes). Achieve via insert-overwrite by partition, MERGE on keys, or dedupe on a natural/business key.
• Parameterize every run by a logical date; never use “now” inside transforms — it breaks reruns & backfills.
• Backfill = replay history by looping the pipeline over past dates. Only possible if tasks are idempotent and raw data is retained.
• Make extraction at-least-once + load idempotent → effectively exactly-once.

06 Data quality

• Test dimensions: uniqueness (PK), not-null, referential (FK exists), accepted values/ranges, freshness (max(ts) recent), row-count anomaly.
• Tools: dbt tests, Great Expectations, pandera, Soda. Run them in the pipeline; fail or quarantine bad rows.
• Quarantine pattern: route failing rows to a side table, alert, continue with good data.
• Reconcile against source counts/sums; assert business invariants (revenue ≥ 0, no future dates).

07 Schema evolution & contracts

• Sources change. Handle additive changes (new nullable columns) gracefully; coordinate breaking changes via data contracts.
• Land raw with schema-on-read (JSON/VARIANT, mergeSchema) so new fields don’t break ingestion; surface them downstream deliberately.
• Version transformations; document lineage so consumers know what changed and when.

08 Orchestration

• DAG of tasks with dependencies, schedules, retries, and SLAs. Tools: Airflow, Dagster, Prefect, Databricks Jobs, ADF.
• Patterns: sensors/triggers (wait for upstream data), parameterized runs (per date), dynamic task mapping (per partition/table), retries with backoff.
• Keep tasks small, idempotent, and independently retryable. Externalize config & secrets. Separate orchestration (when/order) from compute (where it runs).

# Airflow-style DAG (sketch)
extract >> load_raw >> [dq_check, transform] >> publish_marts >> notify

09 Observability & ops

• Log run metadata: rows in/out, duration, status, watermark. Emit metrics & alerts on failure, lateness, and volume anomalies.
• Track lineage (which model feeds which) for impact analysis and debugging.
• Define SLAs (data fresh by 7am) and SLOs; page on breach. Cost-monitor warehouse/cluster usage.
• CI/CD the pipeline code (tests, dbt build on PR); deploy with environments (dev/stage/prod) and zero-copy clones for safe testing.

10 Interview drills

• ETL vs ELT and when each; where does dbt fit.
• Full vs incremental vs CDC; why watermarks miss deletes.
• Make a pipeline idempotent; how to backfill safely.
• Implement SCD Type 2; insert-overwrite by partition.
• What data-quality tests you’d add; quarantine vs fail-fast.
• Design an end-to-end daily pipeline: ingest → raw → transform → marts, with orchestration, retries, DQ, and alerting.