All labs
Data Engineering · Databricks · Executable lab · 120 min
Incremental CDC into Delta with late-arriving data
A retailer's ERP exports order changes every 15 minutes. Updates and deletes arrive out of order, and finance reports double-counted revenue last quarter.
Implementation challenges · 0/5
1. Land raw changes in bronze
Use Auto Loader with schema hints and rescued data column.
2. Deduplicate to latest change per key
Window by order_id ordered by seq desc inside foreachBatch.
3. MERGE with sequence guard
Only apply a change when its seq is greater than the stored seq.
4. Prove idempotency
Replay the same micro-batch twice and compare row counts and checksums.
5. Optimize the table
Enable liquid clustering on order_id and schedule OPTIMIZE; compare file counts before/after.
Expected result
silver.orders contains exactly 812,406 active rows; replay yields zero changes.
Validation tests
- Row count equals reference
- No duplicate order_id
- Replay produces 0 updated rows
- Deleted orders flagged, not removed
Anti-patterns reviewers look for
- MERGE without a sequence guard
- Using updated_at timestamps as the only ordering
- Overwriting silver nightly