Incremental loads and CDC with Boomi Data Integration

The first version of a warehouse pipeline usually copies whole tables every night. That is fine for the item list, but the invoice lines table grows every day, and one morning the nightly load is still running at 9:00. This article explains the two ways to load only what changed, the traps around deletes and late changes, and how to set both up in Boomi Data Integration.

Why “just load what changed” is harder than it sounds

The obvious idea is to remember the time of the last run and fetch rows newer than that. In practice:

Not every table has a reliable “modified” column. Some ERP tables only have a creation date; updates don’t touch it.
Clocks and transactions don’t line up. A row updated at 23:59:58 in a transaction that commits at 00:00:03 can fall between two runs and be missed forever.
Deleted rows leave no trace. A query on “modified after” can’t return a row that no longer exists, so cancelled invoices stay in the warehouse.
Time zones. If the ERP stores local Greek time and the pipeline compares in UTC, you lose or duplicate an hour of changes at every daylight saving change.

The two patterns

Watermark-based incremental loads

Each run selects rows where a column (the watermark) is greater than the highest value seen in the previous run, then stores the new highest value for next time.

select * from invoice_lines
where last_modified > :last_watermark      -- e.g. 2026-10-22 05:30:00

Good watermark columns are a “last modified” timestamp that every update touches, or an ever-increasing number such as a row version. Works with any database or API that can filter by that column.

To avoid missing rows at the boundary, use a lookback window: start each run a little before the last watermark (for example 15 minutes) and rely on upsert/merge into the target to absorb the duplicates.

Log-based change data capture (CDC)

The database already records every change in its transaction log. Log-based CDC reads that log and delivers every insert, update and delete, in order, without querying the tables themselves.

	Watermark	Log-based CDC
Needs on the source	A reliable modified column	Log access enabled (e.g. SQL Server CDC, MySQL binlog, PostgreSQL logical replication)
Sees deletes	No	Yes
Load on the source	A query per run	Low, reads the log
Setup effort	Low	Higher, usually needs a DBA
Works for APIs	Yes, if the API filters by date	No, databases only

Rule of thumb: start with watermarks; move the big, frequently updated tables to CDC when deletes matter or the source’s load becomes a concern.

Handling deletes

Options, from simplest to most complete:

Ignore them if the source never really deletes (many ERPs cancel documents instead of deleting them, and the cancellation is an update you will see).
Periodic reconciliation: once a week, load only the primary keys of the source table and mark rows missing from the source as deleted in the warehouse.
CDC: deletes arrive as events; keep them as soft deletes (a flag column) so reports can filter them out and you keep history.

Backfills

Sooner or later you must reload a period: a load failed for a week, or a new column needs history. Make backfills safe by design:

Load with upsert/merge on the primary key, never plain append, so re-loading rows overwrites instead of duplicating.
Make the time range a parameter, so the same pipeline can run “from 2026-09-01 to 2026-09-30” on demand.
Never move the stored watermark backwards by accident during a backfill, or the next scheduled run will reload everything since then.

Building it in Boomi Data Integration

We continue from the ERP pipeline in Loading ERP and e-shop data into a cloud data warehouse with Boomi Data Integration: a source-to-target river from the ERP’s SQL Server database to Snowflake. Options and labels below reflect the product at the time of writing and may differ in your version.

Step 1: Switch large tables to incremental extraction

In the river’s table settings, change the extraction for invoice_lines from loading all rows to incremental, and choose the incremental field (for example last_modified). Boomi Data Integration keeps track of the last value it loaded, so each run continues from there. Set the loading mode to upsert/merge with the table’s primary key (for example invoice_id + line_no).

Keep small reference tables on full overwrite; incremental logic is not worth it for a few hundred rows.

Step 2: Add a lookback window

If your version supports an offset or lookback setting on the incremental field, use it. If not, achieve the same with a custom query for the table that subtracts a margin from the stored start value. Check the documentation for how variables for the incremental start and end values are exposed in custom queries. The upsert/merge mode from step 1 makes the overlap harmless.

Step 3: Use CDC where deletes matter

For tables where deletes or exact change history matter, switch the river to the Change Data Capture extraction method, available for common databases including SQL Server, MySQL, PostgreSQL and Oracle. On the source side this needs preparation, typically by a DBA:

-- SQL Server: enable CDC on the database and the table (run by a DBA)
exec sys.sp_cdc_enable_db;
exec sys.sp_cdc_enable_table
  @source_schema = N'dbo',
  @source_name   = N'invoice_lines',
  @role_name     = null;

Boomi Data Integration documents the exact prerequisites per database (permissions, log retention, whether it uses SQL Server CDC or Change Tracking). In CDC mode, the river adds metadata columns to the target, such as a deleted flag and change ordering information; check their exact names in your target tables before writing SQL against them.

Step 4: Expose current rows to reports

With CDC, deleted rows stay in the raw table as soft deletes. Create a view in the analytics schema that hides them, so every report doesn’t need to remember the filter:

create or replace view analytics.invoice_lines as
select *
from raw.erp_invoice_lines
where coalesce(is_deleted, false) = false;   -- replace is_deleted with the actual metadata column

Step 5: Plan backfills

For watermark-based tables, a backfill is a one-off run over a chosen period. Run it from a copy of the river with a custom query that has a fixed date range, rather than resetting the production river’s incremental value. With upsert/merge, the reloaded rows replace existing ones. For CDC tables, a backfill usually means a fresh initial snapshot of the table followed by CDC continuing from the current log position; check how your version handles re-initialising a table.

Before you go live

Verify the watermark column. Update a test row in the ERP and confirm the modified column changes. Some ERP screens update tables through procedures that skip it.
Store and compare in one time zone. Prefer UTC in the warehouse; convert for reports.
Check log retention for CDC. If the pipeline is down longer than the source keeps its logs, changes are lost and you need a full reload.
Reconcile regularly. A weekly row-count and totals comparison between source and warehouse catches silent drift.
Alert on stalls, not only failures. A river that succeeds but loads zero rows for two days deserves an alert too.

Summary

Incremental loading is a trade-off: watermarks are simple and work almost everywhere, log-based CDC sees everything including deletes but needs more setup. Combine either with upsert/merge loading and parameterised backfills, and your pipelines stay fast and correct as data grows. In Boomi Data Integration both approaches are options on the same river. For the wider picture, see our Data integration, ETL and ELT: a practical guide.