Choose CDC or batch exports for warehouse integration
Choose CDC when the business needs ongoing changes and the team can operate source-log capture, ordering, and recovery. Choose batch when a bounded snapshot meets the freshness requirement and simplifies reconciliation; either approach needs explicit update, deletion, and publication semantics before reports can trust the result.
Inside this article 5 sections +
A warehouse can receive updates every few seconds and still report the wrong state. Freshness is only useful when snapshots, updates, and deletions are applied consistently. Change data capture, or CDC, reduces the delay between source changes and downstream processing, but it adds an ongoing replication lifecycle.
Begin with the business question: how stale may this report be, and what must happen when the feed falls behind? A daily planning report may tolerate a validated batch snapshot. An operational dashboard may need a stream of changes, provided the team can detect gaps and recover them.
Define whether the target represents state or history
A current-state table should contain the latest known row for each business key, with deletions reflected according to policy. A history table records changes over time. Mixing the two models creates confusion: appending every update to a table intended for current-state reporting can multiply counts.
Specify the key, source version or ordering marker, operation type, schema version, and deletion behavior. Event arrival time is not necessarily source order. Use the source's supported replication position or version semantics, and document their scope across tables and transactions.
A batch snapshot also needs a contract. Identify its consistent source boundary, included tables, extraction filters, and completion marker. Absence from an incomplete export should not be interpreted as deletion. Publish the batch only after its manifest and reconciliation checks pass.
Protect the handoff between snapshot and changes
Validated batch
Readers see a known complete generation. Freshness advances when the next validated batch is published.
Snapshot plus CDC
Readers need a known applied position and a policy for lag or interrupted capture.
Managed replication tools can coordinate full load and ongoing changes, but source prerequisites and target limitations still matter. AWS DMS supports both full-load-plus-CDC and ongoing capture from supported starting positions. Verify the chosen source's log retention, permissions, and restart behavior.
Do not improvise a snapshot followed by “start reading changes now.” Updates committed during extraction can fall into the gap or be overwritten by late snapshot rows. Use the platform's supported consistent handoff or design a version-aware merge whose assumptions are tested.
Test stale updates and tombstones explicitly
The following executable Python fixture models one key with an illustrative monotonically increasing source sequence. Real databases have their own ordering markers; wall-clock timestamps are not a drop-in substitute. The tombstone preserves the latest sequence even after the value is deleted.
state = {}
def apply(key, sequence, value):
previous = state.get(key)
if previous is None or sequence > previous["sequence"]:
state[key] = {"sequence": sequence, "value": value}
apply("account-A", 100, {"plan": "basic"}) # snapshot
apply("account-A", 101, {"plan": "pro"}) # update
apply("account-A", 102, None) # deletion
apply("account-A", 101, {"plan": "pro"}) # duplicate or late update
apply("account-A", 100, {"plan": "basic"}) # late snapshot row
assert state["account-A"] == {"sequence": 102, "value": None}
This tests one state-application rule, not a complete CDC implementation. It omits transaction grouping, schema changes, and multiple source partitions. Those concerns need explicit integration tests using the actual capture tool and warehouse apply path.
Retain deletion markers long enough to prevent stale replays from recreating removed rows. If the target physically removes both the row and its ordering metadata, an old update may look like a new insertion. Recovery and retention policies must account for that possibility.
Include target loading in the freshness budget
For Redshift, data may pass through files, staging tables, and merge or apply operations before it becomes reportable. The DMS target path has its own documented behavior and limits. A low source-capture lag does not prove that the reporting table is current.
Measure source commit to capture, capture to staged availability, staged availability to applied state, and applied state to report publication. Keep the applied checkpoint visible. A dashboard timestamp showing only the last received event can conceal a stalled merge.
Deduplicate and validate staging input before applying changes. If several source rows match one target key, merge behavior may be invalid or ambiguous. Preserve deterministic ordering and test duplicate-key fixtures instead of relying on a primary-key declaration to protect the load.
Choose the operating burden you can support
CDC requires monitoring log retention, capture health, target lag, checkpoints, and schema evolution. Batch requires managing extraction windows, complete manifests, publication boundaries, and catch-up after missed runs. Both need reconciliation against authoritative source totals or samples.
Estimate freshness using observed stage delays rather than the scheduler interval alone. A batch scheduled every hour but taking forty minutes to extract and apply has a different user-visible age from a ten-minute job. A CDC pipeline under sustained backlog can be less fresh than a healthy batch.
Run a recovery drill: interrupt capture or extraction, change and delete records, restart, and compare the published target with the intended source boundary. The choice is ready when the team can explain recovery, not merely when a connector shows green.
See batch frequency planning and warehouse reconciliation. Our architecture consulting services help align the feed with reporting and operating requirements.