Start with the source, not the pattern
Teams often begin with the question: "Should we use full load, incremental load or CDC?" A better question is: How does the source change, and what does the target need to know about those changes?
The answer depends on whether records are only inserted or can also be updated and deleted, whether a reliable change indicator exists, how quickly changes must arrive downstream, and how easy the pipeline must be to recover.
Once those constraints are clear, the ingestion pattern usually becomes much easier to choose.
Simple state model and easy reconciliation, but repeated processing grows with the source.
Efficient when a trustworthy watermark, sequence or changed-at field exists.
Preserves inserts, updates and deletes with low latency, but adds operational complexity.
Full load: simplicity first
A full load reads the complete source dataset and recreates or reconciles the target from that snapshot.
This is often the easiest ingestion pattern to understand because the pipeline does not need to remember where it stopped previously. Every run starts from the complete source state.
The source is small, refresh frequency is low, extraction is inexpensive and simplicity matters more than minimizing processed volume.
The dataset grows, source queries become expensive, load windows shrink or downstream processing repeats the same work unnecessarily.
Full loads are sometimes dismissed as unsophisticated, but that is the wrong test. If a complete reload finishes comfortably within the operational window and is easy to recover, it can be the most reliable design.
Incremental load: efficiency with state
An incremental load processes only records that are considered new or changed since the previous successful run.
The challenge is deciding how the pipeline knows that something changed. Common mechanisms include an updated_at timestamp, increasing sequence number, source-side change table or another deterministic watermark.
The watermark should advance only after the corresponding target data is committed successfully. Advancing it too early can cause records to be skipped after a failure.
Incremental does not automatically mean correct
A timestamp filter such as updated_at > last_watermark looks simple, but real source systems can make it unreliable.
- Multiple records may share the same timestamp.
- Source timestamps may have limited precision.
- Transactions can commit later than their business timestamp suggests.
- Records may arrive late.
- Deletes may not leave a record to query at all.
A robust design may use an overlap window, a compound watermark such as timestamp plus key, or source-specific sequencing. The goal is not merely to process less data; it is to process less data without creating gaps.
CDC: preserve the change itself
Change Data Capture focuses on the changes made to source records rather than repeatedly querying the current source state.
A CDC feed can identify events such as insert, update and delete, often together with ordering information that allows downstream systems to reproduce the latest state.
This makes CDC especially valuable when deletes matter, latency requirements are low, or the source is too large to query repeatedly for changed records.
Deletes are often the deciding factor
Inserts and updates are relatively easy to detect with an incremental timestamp when the source maintains that field correctly. Deletes are different because the row may simply disappear.
A missing row can be detected by reconciling the current snapshot with the previous target state.
A soft-delete flag, deletion table or other explicit mechanism is usually required.
The change stream can explicitly carry the deletion downstream.
If downstream systems must mirror source deletions accurately, this requirement should be considered before the ingestion pattern is chosen.
Late-arriving data changes the design
A record that belongs to an earlier time period may arrive after the pipeline has already moved its watermark forward.
For example, a source can write an event at 10:58 but make it visible to the extraction query only after the 11:00 ingestion run has completed.
A watermark defines what the pipeline has safely observed — not simply what time it is.
Depending on the source, pipelines may intentionally re-read a small overlap window and rely on idempotent merge logic to remove duplicates. CDC systems often provide ordering or offsets, but they still require careful checkpoint and replay management.
Recovery should influence the pattern
Efficient ingestion is useful only if the pipeline can recover safely after a failure.
Run the complete load again. The trade-off is repeating all processing.
Resume from the last successfully committed position and safely replay the next range.
Resume from a known committed change-stream position while preserving event order.
The best pattern is not necessarily the one that processes the fewest rows. It is the one whose failure and replay behavior the team can operate confidently.
How the patterns fit with Delta Lake
Delta Lake is useful across all three approaches because the ingestion method and target write strategy are separate decisions.
A full load may replace a table or partition. Incremental loads commonly append or use MERGE to apply updates. CDC pipelines can use change events to drive inserts, updates and deletes into a Delta target.
The write operation still needs to preserve the guarantees of the ingestion pattern. A pipeline can read changes correctly and still produce duplicates if the target write is not idempotent.
A practical decision framework
Do not over-engineer the first version
CDC is powerful, but it should not be selected simply because it is the most advanced option.
If a 50,000-row reference table changes once per day, a full load may be easier to understand, test and recover. If a large transactional system receives continuous updates and deletions, repeatedly scanning it for changed records may be the wrong trade-off.
Architecture should match the actual constraints. Complexity is justified when it buys a capability the system genuinely needs.
Final takeaway
Choose the simplest pattern that preserves the changes you need.
Full load favors simplicity. Incremental load favors processing efficiency. CDC favors detailed change capture and low latency.
The right choice comes from understanding source behavior, update and delete semantics, latency requirements, recoverability and operational cost. Once those are explicit, the ingestion pattern becomes an engineering decision rather than a preference.