Every pipeline has to answer one question on each run: reload everything, or load only what changed. Full refresh is the simple answer, and incremental loading is the efficient one, and each fails in its own way.
What a full refresh does
A full refresh reads the entire source dataset and replaces the destination table with it on every run. It is simple to build and easy to reason about, because the destination always matches the source at the moment of the load. Deleted rows disappear naturally, and a bug in an earlier run is fixed by the next one. The cost is time and compute, which grow with the size of the data.
What an incremental load does
An incremental load brings in only the rows that are new or changed since the last run. The usual method is a watermark: a column such as an updated-at timestamp or an increasing ID that tells the pipeline where the previous run stopped. Another method is change data capture, which reads the source’s change log, as compared in change data capture vs batch ETL. Incremental runs are faster and cheaper, but they depend on the source behaving the way your logic assumes.
How incremental loads go wrong
The common failures are quiet ones. A source row can change without its timestamp updating, so the change is never picked up. Late-arriving records can show up with timestamps older than your watermark. Hard deletes in the source are invisible to a watermark, so deleted rows linger in the destination. And a failed run that advanced the watermark before finishing can skip data permanently.
Ways to make incremental loading safer
Use a lookback window, reprocessing a recent period on every run to catch late data. Write with an upsert or merge on a unique key, or replace whole partitions, so reruns do not create duplicates, as described in idempotent data pipelines. Partitioning the destination by date makes partition replacement cheap, which is covered in table partitioning strategies. Tools such as dbt support incremental models with a unique key, though the same watermark risks apply.
Use both, on purpose
Many teams run incremental loads day to day and schedule a periodic full refresh to reconcile any drift. That gives you the speed of incremental loading and a regular check that the destination still matches the source. If you track history with slowly changing dimensions, plan how the incremental logic interacts with them. Row-count and freshness checks, in the spirit of data observability, catch the silent failures above before users do.
How to choose
Start with a full refresh when the table is small, the source has no reliable change column, or deletes matter. Move to incremental loading when run time or cost becomes a problem and you can trust a change column or a change log. The surrounding design choices, such as where transformation happens, follow the trade-offs in ETL vs ELT.
A quick checklist
- Does the source have a column that reliably changes whenever a row changes?
- How will you detect deleted rows?
- Do late-arriving records need a lookback window?
- Is the write an upsert or partition replace, so reruns are safe?
- Is there a periodic full refresh or reconciliation check?
If you want a second opinion on a pipeline design, see my ETL and ELT engineering service.
FAQ
Is incremental loading always better than a full refresh?
No. It is faster and cheaper at scale, but a full refresh is simpler and handles deletes and source corrections automatically. For small tables, the simpler approach is often the right one.
How do incremental loads handle deleted rows?
A watermark alone cannot see deletes. You need a change log, a soft-delete flag in the source, or a periodic full refresh to remove deleted rows from the destination.
What is a watermark in data engineering?
It is a stored value, such as the latest timestamp or ID loaded so far, that the next run uses as its starting point to fetch only newer data.
