Idempotency & incremental loads
The property that separates pipelines you trust from pipelines you fear: running twice produces the same result as running once.
Here is the pipeline failure that actually happens, everywhere, forever: the 2am job dies halfway. Or it succeeds but the scheduler retries anyway. Or an engineer re-runs yesterday manually 'just to be safe.' If your load is a bare INSERT INTO fct ... SELECT FROM new_file, every one of those events duplicates data — and duplicated facts inflate every metric downstream, quietly, until someone senior asks why revenue jumped 40% on a Tuesday.
Idempotency is the cure: design every load so running it twice — or five times, or halfway-then-fully — leaves the warehouse in exactly the state one clean run would. Idempotent pipelines convert 'something re-ran' from an incident into a non-event. There are three standard constructions:
- Full refresh —
CREATE OR REPLACE TABLE dim_product AS SELECT ... FROM stg_products. Rebuild the whole table from staging every run. Trivially idempotent, zero bookkeeping. Right answer for small dimensions; wrong for a billion-row fact table. - Partition overwrite (delete + insert) — for facts organized by date: delete the date range you're about to load, then insert it. Re-running just deletes and rewrites the same days. This is the workhorse pattern for batch facts, and the one you'll implement today.
- Keyed upsert (MERGE) — match on a key: update the row if it exists, insert if not. Right when rows mutate (order status: placed → completed → refunded) rather than accumulate. Costs more care: you must know the true key, and Module 3's uniqueness tests exist because people are wrong about that.
Incremental loads, and when not to bother
Incremental means loading only what's new — files newer than the watermark, rows with updated_at past it — instead of everything. It's an efficiency choice, not a correctness one, and it stacks on top of idempotency: an incremental partition-overwrite re-run is still safe. The mistake to avoid is incremental-without-idempotency: watermark logic that assumes exactly-once execution shatters on the first retry.
At Harbor Lane's scale (thousands of rows/day), full-reload-everything runs in under a second — incremental machinery would be pure ceremony. Adopt incremental when a full reload's runtime or cost actually hurts, and not before. But idempotency has no scale threshold: the 40-row pipeline and the 40-billion-row pipeline both get retried by schedulers.
One more habit that separates professionals: log what every run did. Rows in, rows written, watermark before and after, duration. When Module 3's checks catch an anomaly, the run log is how you find which load caused it — and when the AI Governance course asks 'can you prove what data entered the model?', the run log is the evidence.