Choosing the right incremental loading strategy
Incremental loading replaces full reloads by processing only what has changed. Done well, it reduces runtimes and makes data pipelines easier to operate at scale.
Incremental loading is not a single pattern. Different strategies identify and apply change in different ways, each relying on specific assumptions about the source data. Choosing the wrong approach can introduce subtle correctness issues that are hard to detect.
This article builds on the earlier discussion of the full-load anti-pattern and explains the main incremental loading strategies, their trade-offs, and how to choose an approach that fits the data and source systems you are working with.
Incremental loading is not one pattern
At a high level, incremental loading means processing change rather than history. What differs is how change is identified, captured, and applied.
In practice, teams tend to rely on a small set of established strategies. These include high-watermark loading based on timestamps or sequence numbers, change data capture, snapshot differencing, and append-only ingestion with downstream state resolution.
Each approach makes different assumptions about the source system and about what it means for data to be correct over time. Each works well in some conditions and poorly in others. The goal is not to standardise on a single approach, but to align the strategy with the guarantees and limitations of the source system.
High-watermark loading
How it works
High-watermark loading tracks the most recent successfully processed record, usually using a timestamp or a monotonically increasing identifier.
On each run, the pipeline extracts only records newer than the last known value:
SELECT *
FROM source_table
WHERE modified_at > :last_processed_timestamp
The watermark advances only after a successful run.
When it works well
High-watermark loading works best when the source system reliably updates a created_at or modified_at field and when updates are mostly append-only. It is also suitable when late-arriving changes are rare or acceptable within the business context.
Because it is simple to implement, this pattern often delivers performance improvements quickly.
Where it breaks down
Problems arise when updates arrive out of order, when historical records are corrected long after creation, or when timestamps are unreliable or inconsistently maintained.
In these cases, data can drift out of sync without obvious failures. Pipelines continue to run successfully while correctness degrades quietly.
As a rule of thumb, high-watermark loading is better suited to operational systems than to datasets that are frequently corrected retrospectively.
Change data capture (CDC)
How it works
Change data capture records inserts, updates, and deletes directly from the source system, typically using database logs or event streams. Each change is emitted as an event and processed downstream in sequence.
When it works well
CDC is a strong option when the source system supports it natively, when near-real-time freshness is required, and when deletes must be handled explicitly. It is also well suited to cases where ordering and completeness guarantees matter.
Trade-offs
CDC introduces additional complexity. It requires more infrastructure, stronger monitoring, and careful handling of recovery and replay. It also often depends on coordination with source system teams around permissions and operational impact.
CDC is rarely the fastest path to improvement, but it is often the most robust choice for critical datasets where correctness matters more than simplicity.
Snapshot differencing
How it works
Snapshot differencing compares two versions of a dataset, usually the current snapshot and a previous one, to identify changes.
This pattern is common when the source system only provides full extracts and no reliable indicators of change are available.
When it works well
Snapshot differencing can be effective when dataset sizes are moderate and when the proportion of change between snapshots is relatively small. It is often used when other incremental options are not available.
Limitations
As datasets grow, the cost of computing differences grows with them. At scale, snapshot differencing recreates many of the same performance and resource issues associated with full reloads.
For this reason, it is usually best treated as a transitional strategy rather than a long-term solution.
Append-only ingestion with downstream state resolution
How it works
In append-only ingestion, new records are always added and existing records are never updated in place. Downstream logic determines the current state by resolving the most recent or relevant records.
This pattern is common in event-driven systems, ledger-style datasets, and audit or regulatory workloads.
Strengths and costs
Append-only ingestion provides strong auditability and complete historical traceability. Ingestion logic is typically simple and robust.
The cost is pushed downstream. Queries become more complex, state resolution logic must be carefully designed, and performance depends on how derived datasets are materialised and maintained.
This approach aligns naturally with ELT-style architectures, where raw history is preserved and interpretation happens later.
Choosing the right strategy
Rather than starting with tools, it is more effective to start with constraints.
Teams should consider what guarantees the source system provides, how often historical data changes, how important auditability and replay are, and what level of operational complexity is acceptable.
In practice, different sources often require different strategies, even within the same platform.
Implementation discipline matters
Incremental loading introduces state in the form of watermarks, offsets, versions, and dependencies. That state must be managed deliberately.
Successful teams make incremental state explicit and observable, ensure failures can be recovered without data loss, and maintain clear lineage between raw and derived datasets.
Without this discipline, incremental systems can fail quietly while appearing healthy.
Incremental execution in governed datasets
Incremental loading only works reliably when incremental state is treated as part of the data model, not as an implementation detail hidden inside pipelines.
In practice, this means making inputs, transformation logic, and refresh behaviour explicit. In Bragi, this is expressed through Stages. A Stage represents a derived dataset with defined upstream dependencies and a clear refresh contract. Incremental execution happens at the Stage level, so changes are processed deliberately and traceably rather than implicitly through ad hoc jobs.
This structure makes incremental behaviour visible and auditable. Teams can see what data a dataset depends on, how freshness is defined, and how failures are recovered. Incremental state is no longer scattered across scripts and schedulers.
Incremental loading in practice: a real-world example
In the banking transformation discussed in the earlier technical deep-dive, no single incremental strategy was applied everywhere. High-watermark loading was used where timestamps were reliable. Append-only ingestion was chosen for regulatory datasets that required full traceability. Snapshot differencing remained in a small number of constrained cases as a temporary measure.
The outcome was not just faster pipelines. Runtimes dropped from hours to minutes, and scaling became more predictable. Processing time grew with the size of change, not with the size of history.
The key point is not the choice of a single strategy, but the ability to operate multiple incremental patterns consistently. When incremental execution is tied to well-defined, governed datasets, different approaches can coexist while remaining explainable, auditable, and safe to reuse.
Conclusion
Replacing full reloads with incremental loading is one of the highest-impact improvements a data platform can make. But incremental loading is not a single technique, and the wrong choice can introduce subtle correctness risks.
The most effective platforms combine multiple incremental strategies, chosen to fit the data and the source systems, and treat incremental state as a first-class concern. The goal is not just faster pipelines, but systems that scale predictably, recover cleanly, and remain explainable as complexity grows.
About the author
Explore the full series
A series designed to help you and your team understand Bragi’s core concepts and patterns before diving into the technical documentation.