How would you design an incremental load for a large orders table that also receives updates to old orders?

Question

RED SAG · Accepted Answer

I need a reliable way to find changed rows. Best is change data capture from the database log, which includes inserts, updates and deletes. If that is not available, I use an updated_at column that the app always sets on change. Each run selects rows where updated_at is greater than the max already loaded, minus a lookback window of, say, two hours to catch transactions that committed late. Then I MERGE into the target on order_id, updating existing rows and inserting new ones, so overlap from the lookback is safe. For deletes I prefer soft deletes in the source; otherwise CDC is needed. Because incremental logic can drift, I schedule a weekly reconciliation that compares counts and sums with the source, and support a full refresh when the logic changes. In dbt this is an incremental model with unique_key and a merge strategy.

How would you design an incremental load for a large orders table that also receives updates to old orders?

What the interviewer wants to hear

Model answer

More medium Data Engineer questions