Incremental Load: Move Only Changed Data

Reloading every record from a source each time a pipeline runs is simple and easy to reason about. As data grows, though, full reloads take longer, cost more, put strain on source systems and APIs and eventually stop fitting into the time available. An incremental load moves only the records that are new or have changed since the last successful run. It can make pipelines far faster and cheaper, but it introduces new ways to go wrong: missed updates, duplicates, deletes that never reach the target and late-arriving data. This guide explains how to design an incremental load that stays correct, from choosing a change marker to merging records, handling deletes and scheduling occasional full reloads.

What an incremental load is

An incremental load processes a subset of source data on each run, usually the rows created or updated since the previous run, and merges them into the target. The dbt documentation describes the same idea for transformation models: on an incremental run, the rows you filter for are often those created or updated since the last time dbt ran, which limits the amount of data that needs to be transformed (dbt: incremental models).

Compared with full reloads, incremental loads:

• Run faster as data grows
• Reduce load on source databases and API quotas
• Lower compute and transfer costs
• Need more careful design to stay accurate

Choose a reliable change marker

The pipeline needs a way to identify changed records. Common options:

• An updated timestamp column that the source sets on every insert and update
• An auto-incrementing ID, suitable only for data that is inserted and never updated
• A version or sequence number that increases with each change
• A change data capture stream from the database log

Check the marker carefully. Timestamp columns are not always updated by every process that changes a record; bulk updates, imports or direct database fixes may bypass them. Ask the source owner how the column is maintained and test it.

Track the high-water mark

Record the position reached after each successful run, often called the high-water mark: the latest timestamp or ID processed. On the next run, extract records beyond that point. Only move the mark forward after the load has fully succeeded; if the run fails partway, the next run should start from the old mark and reprocess the same records.

To avoid missing records committed slightly late, many teams overlap windows, re-reading a short period before the last mark on each run and relying on idempotent merges to handle duplicates.

Merge on keys, not append

Changed records must update existing rows, not create new ones. Merge incoming records into the target using a stable unique key from the source:

• Insert records whose key does not exist
• Update records whose key already exists
• Use the source's key, not one generated by the pipeline

The dbt documentation shows a unique_key setting for incremental models that serves this purpose. Whatever tool you use, idempotent merges mean that re-running the same batch does not create duplicates.

Handle deletes

Incremental loads based on timestamps usually cannot see deleted records, because a deleted row no longer exists to be extracted. Options include:

• Soft deletes in the source, where records are flagged rather than removed, so the flag appears as an update
• Change data capture, which can capture delete events from the database log
• Periodic comparisons of all keys between source and target to find records that have disappeared
• Regular full reloads for smaller tables

Decide which approach fits each table, based on how often deletes happen and how much they matter to reporting.

Handle late-arriving and corrected data

Some records arrive or change after the period they belong to: a late invoice, a corrected order total, a back-dated adjustment. If the pipeline only looks at records changed since the last run, it will catch these as long as the change marker updates. Problems arise when downstream summaries, such as daily totals, are not recalculated. Rebuild affected summaries for a rolling window, such as the last few days or weeks, on each run.

Worked example: incremental load design by table

The table below is a hypothetical design for loading tables from an order system into a reporting database. It shows the decisions to record, not a recommended setup.

TableChange markerMerge keyDelete handlingFull reloadOrdersUpdated timestamp, with short overlapOrder IDSoft delete flagMonthlyOrder linesUpdated timestamp, with short overlapOrder line IDKey comparison weeklyMonthlyCustomersUpdated timestampCustomer IDSoft delete flagMonthlyProductsNone reliableProduct IDNot neededEvery run, small tableAudit eventsIncreasing event IDEvent IDNever deletedNever

Schedule full reloads

Even well-designed incremental loads can drift: a missed update, a bug in an earlier version, a manual fix in the source. Schedule occasional full reloads or full comparisons to reset the target and catch drift. The dbt documentation, for example, describes a full-refresh option that rebuilds an incremental model from scratch. Choose a frequency that balances cost with how much drift you can tolerate.

Plan for logic changes

When transformation logic changes, records already loaded were processed under the old rules, while new records follow the new ones. The dbt documentation makes the same point about incremental models: if the logic changes, new rows may diverge from historical ones, and the model should be rebuilt. Plan a full reload or targeted backfill whenever business rules, mappings or calculations change, so historical and new data stay consistent.

Monitor correctness

Monitor more than run success:

• Records processed per run, compared with normal ranges
• The age of the latest record in the target
• Reconciliations of counts and totals against the source
• Differences found during full reloads, which indicate drift

Our guide to scheduled data sync covers running and monitoring regular data movements.

Test edge cases

Test the incremental logic with cases such as records updated twice between runs, records updated during a run, failed runs followed by retries, deletes and late corrections. Confirm the target matches a full reload of the same data.

Keep the code maintainable

Incremental logic adds complexity, so keep it clear and well documented. Store high-water marks in a dedicated table, log the window used for each run and keep extraction, merge and summary steps separate. Our guide to Python ETL covers structuring pipeline code for maintainability.

Frequently Asked Questions

What is an incremental load?

It is a data loading approach that processes only records that are new or changed since the last successful run and merges them into the target.

How does an incremental load track what has changed?

Usually with a change marker such as an updated timestamp, increasing ID or version number, plus a stored high-water mark from the last successful run.

How are deleted records handled in incremental loads?

Through soft-delete flags, change data capture, periodic key comparisons or regular full reloads, since timestamp-based extraction cannot see deleted rows.

Why schedule full reloads if incremental loads work?

To catch drift from missed updates, bugs or manual source fixes, and to reset the target to a known correct state.

Load data faster without losing accuracy

We design incremental pipelines with reliable change tracking, merges and reconciliation. See our CRM and API integration services, or talk to us about incremental loading.

About the Author

Manav Sharma is the founder of Insyrge and a CRM solutions architect. He works across Zoho, Salesforce, HubSpot, Odoo and more, designing the CRM setups, integrations and automations that keep sales and operations teams working from the same data.