ETL versus ELT
ETL transforms before loading, which suited expensive warehouse storage. ELT loads raw data first and transforms inside the warehouse with SQL, which is now standard because compute is elastic and raw data remains available for reprocessing.
Incremental loading and CDC
Full reloads are simple but stop scaling. Incremental loads use a watermark column or change data capture from the database write-ahead log (Debezium), which also captures deletes. Handle late-arriving data by reprocessing a trailing window.
Make every load idempotent
Pipelines will re-run. Use MERGE/upsert on a stable key, partition-level overwrite, or deterministic deduplication so a re-run cannot double count. Backfills should use the same code path as normal runs, parameterised by date.
MERGE INTO silver.orders AS t
USING staging.orders_delta AS s
ON t.order_id = s.order_id
WHEN MATCHED AND s.updated_at > t.updated_at THEN UPDATE SET *
WHEN NOT MATCHED THEN INSERT *;