Lesson 5 — Historical Loads, CDC & Low Latency
Historical load vs ongoing update
A historical load creates the baseline/backfill—often one or a few large loading efforts covering prior periods. It frequently exposes old Data Quality problems and may be rerun while mappings and rules are corrected.
An ongoing update is the recurring process that keeps the warehouse current after the baseline exists. The key problem becomes: How can the source reliably prove what changed?
That is Change Data Capture (CDC).
Five CDC methods
1. Time-stamped delta
Select rows using a reliable updated_at timestamp/high-water mark.
Strength: simple and often efficient.
Risk: physical deletes are not inherently visible. Watermark overlap/recovery must be controlled.
2. Log-table delta
A source/application writes explicit insert/update/delete records to a dedicated change table.
Strength: clear change evidence, including deletes when designed that way.
Trade-off: depends on the application/source maintaining the table correctly.
3. Database transaction-log CDC
Read the DBMS transaction log.
Strength: detailed, delete-aware, avoids timestamp-overlap problems.
Trade-off: more technically complex and platform-dependent.
4. Message delta
The source publishes change events/messages.
Strength: can support low latency and decoupled consumers.
Trade-off: requires reliable event delivery, ordering/replay/duplicate controls as appropriate.
5. Full-load comparison
Extract complete states and compare.
Strength: fallback when no useful change indicator exists.
Trade-off: expensive at volume and can create large processing windows.
There is no universally “best” CDC. Match the method to source capability, delete requirements, latency, source impact, recovery, and complexity.
Three low-latency patterns
Memorize the accumulation point:
- Trickle feed = source accumulation. The source holds small/frequent mini-batches until a time/count trigger.
- Messaging = bus accumulation/distribution. Applications publish events to middleware/bus; subscribers consume independently.
- Streaming = target accumulation. Continuous arrivals land in a target-side queue/buffer for processing.
“Fast” is not enough to choose streaming.
Latency should be justified by the decision
If morning reporting is satisfied by a nightly load, adding real-time complexity may be wasteful. If fraud decisions need events in seconds, batch is insufficient.
Do not make the entire historical warehouse volatile because one operational dashboard needs lower latency. Isolate the current/near-current need in an ODS or appropriate low-latency integration pattern when that better preserves the historical role.
Stop and check
Timestamp CDC + physical deletes required: timestamp alone is insufficient unless another deletion indicator exists.
Events accumulate on enterprise bus: Messaging.
Direct continuous arrivals accumulate in target queue: Streaming.
Source sends every 300 changed rows: Trickle feed.
Source anchor: pp. 371–374.