Read first: Load modes · Reference: incremental and backfill.
What it is#
An incremental load by watermark brings in only the new rows since the last
run, using an increasing column (an update date or an id) as the
watermark. It is much cheaper than a full load and, by construction, produces the same result.
This is not CDC. It is a watermark comparison (
column > last mark). Real CDC (INSERT/UPDATE/DELETE from the source log) is Roadmap — it does not exist today.
What it is for#
Keeping a large table that grows/updates (orders, events, entries) fresh without rereading the entire history on every refresh.
When to use / when not to use#
- Use it when: there is a reliable, increasing column (e.g.,
atualizado_em,id) and old rows do not disappear from the source. - Do not use it when: the source deletes rows without a deletion signal — use by-period mode (refetches whole days) or a full load. See "Deletion" below.
Plan and permissions#
- Core · access check · kill switches for ingestion runs, dashboard refresh, and data console queries.
Prerequisites#
- A connected source with at least one full load already done (the base snapshot).
- A watermark column: increasing, with no gaps going backward. Good choices:
atualizado_em(timestamp) or a sequentialid.
How it works under the hood#
- Database / BigQuery pull: KEYSET (seek) pagination —
WHERE wm > <last mark> ORDER BY wm ASC, advancing the mark to the highest value in the last batch. Stable even if the source changes during extraction (unlikeOFFSET). The mark value goes in as a named parameter per engine (Postgres$1, SQL Server/BigQuery@since, MySQL?, Oracle:since), never concatenated. - Widget/mart refresh: stores the watermark of the previous snapshot and does an UPSERT
by key with the delta; the history is reused. The predicate is
>=on purpose (it refetches the row exactly at the mark; the upsert is idempotent and does not lose the boundary).
Step by step#
- Make sure there is an initial full load of the source/table (the base snapshot).
- In the source/table settings (or the time-series widget settings), turn on
incremental mode and enter the watermark column (e.g.,
atualizado_em). - Save and run the refresh. Ingestia fetches only the delta (
> mark) and upserts it. - Validate the identity: run an incremental refresh and a complete refresh and confirm that the numbers match. If they cannot match safely, the product refuses the incremental load and does a full one (with the reason in the log) — this is expected.
Example (synthetic)#
A pedidos table with id and atualizado_em:
Modo: incremental
Coluna de watermark: atualizado_em
Chave de upsert: idThe delta query Ingestia builds (PostgreSQL, conceptual):
SELECT * FROM "public"."pedidos"
WHERE "atualizado_em" > $1 -- $1 = last watermark
ORDER BY "atualizado_em" ASC
LIMIT 5000; -- one chunkThe watermark advances to the highest atualizado_em in the batch; the next chunk
continues where it left off.
Expected result#
- Each refresh reads much less data (only the delta) and the result is identical to the full load — same rows, same order.
- With no usable cursor in the previous snapshot, Ingestia writes
nulland the next refresh falls back to a full load (safe).
Deletion semantics (accepted limitation)#
The merge is an UPSERT. Without a deletion signal, the incremental load does not remove a key that disappeared from the source (the same contract as dbt/Power BI). To restore accuracy under deletion:
- use complete partitions (refetches the whole partition and removes the missing key); or
- provide tombstones (explicit deleted keys); or
- use by-period mode (refetches whole days); or
- run a periodic full load.
Limits and costs#
- Window by period (when applicable): default 7 days, cap 400.
- Pagination: cap of 10,000 pages / 5,000,000 rows.
- Cursor literal: ≤ 64 characters, no quotes/backslash/line break.
- Incremental exists to reduce cost; the billable-bytes ceiling always protects you.
Security#
- Column names (key/cursor) validated as simple identifiers before the SQL.
- Cursor value by bind (named parameter); the literal rejects quotes/backslash/line breaks.
- N−1 compatibility: a new reader reads an old snapshot (without a cursor) without breaking — and, when facing one, refuses the incremental load and does a full one.
Common errors#
| Symptom | Likely cause | What to do |
|---|---|---|
| Always falls back to full | no previous snapshot, or no valid cursor | Do the 1st full load; choose a truly increasing column |
| Rows deleted at the source are still there | watermark does not see deletions | Use period/complete partitions/tombstones or a full load |
| Numbers do not match the full load | wrong ordering/key | Make sure ORDER BY = key ASC; review the upsert key |
| Cursor rejected | value > 64 chars or with quotes | Use a simple watermark column (date/id) |
Troubleshooting#
- Compare the results of the incremental refresh and the full load side by side.
- Read the reason in the log when the product falls back to a full load — it says exactly which eligibility condition was not met.
Related#
Last reviewed: 2026-09-08.