🔄ETL pipelines

Extract, transform, load, or load then transform

Classic ETL cleanses before the warehouse. ELT lands raw rows cheaply, then uses warehouse SQL for joins, deduplication, and slowly changing dimensions. Either way, contracts look like APIs: keys, idempotency, schema versions, and poison-row handling.

SQL

ELT: stage then merge (MERGE sketch; dialect varies)

MERGE INTO warehouse.dim_customer AS d
USING staging.customers_delta AS s
ON d.customer_id = s.customer_id
WHEN MATCHED THEN UPDATE SET email = s.email, valid_to = s.valid_to
WHEN NOT MATCHED THEN INSERT (customer_id, email, valid_from) VALUES (s.customer_id, s.email, s.valid_from);

✓Illustrative result set

rows_affected
4200

SQL

Idempotent daily rollup

DELETE FROM reporting.daily_orders WHERE day = DATE '2026-04-11';

INSERT INTO reporting.daily_orders
SELECT order_date::date AS day, COUNT(*), SUM(total_usd)
FROM orders
WHERE order_date::date = DATE '2026-04-11'
GROUP BY 1;

✓Illustrative result set

status
DELETE + INSERT complete

Fragile loads

Full-table scans on production OLTP for “nightly everything” extracts.
Non-deterministic ordering when deduping without explicit window or tie-break keys.
Silent truncation during staging type coercion.

Watermarks and checks

Incremental pulls on updated_at watermarks with backfill windows.
Row-count and checksum validation between stages; quarantine bad rows with alerts.
Document timezone normalization at ingest; version MERGE tie-break rules.

🧰Engine behavior

Bulk paths differ from OLTP row mode.

Throughput

Prefer COPY, partition swaps, or temporarily dropped secondary indexes on huge loads, long transactions on the source can block vacuum and replication.

Idempotency

Make reruns safe: partition replace, delete+insert for a bounded key, or merge with explicit match rules, never “hope the job only ran once.”

SLA

Publish freshness targets (“dashboards ≤ 15 minutes stale”) and monitor pipeline duration and failure rates like product SLIs, not only whether the last cron exit code was zero.

TL;DR

ETL/ELT moves data from operational systems into warehouses or marts: Extract (read sources), Transform (clean, join, aggregate, often SQL), Load (write targets). Modern stacks often load raw landing tables first (ELT) then transform in-SQL for scale. Idempotent loads, watermark keys, and replayable batches matter more than clever one-off scripts.