Skip to content

Automatic translation — the Portuguese version is the reference. View in Portuguese

Updated on 2026-09-08

Tutorial — create an incremental pipeline by watermark

Step-by-step guide to setting up an incremental load by watermark, bringing in only the delta with a guarantee that it matches the full load.

On this page (15)

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#

  1. A connected source with at least one full load already done (the base snapshot).
  2. A watermark column: increasing, with no gaps going backward. Good choices: atualizado_em (timestamp) or a sequential id.

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 (unlike OFFSET). 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#

  1. Make sure there is an initial full load of the source/table (the base snapshot).
  2. In the source/table settings (or the time-series widget settings), turn on incremental mode and enter the watermark column (e.g., atualizado_em).
  3. Save and run the refresh. Ingestia fetches only the delta (> mark) and upserts it.
  4. 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:

code
Modo: incremental
Coluna de watermark: atualizado_em
Chave de upsert: id

The delta query Ingestia builds (PostgreSQL, conceptual):

sql
SELECT * FROM "public"."pedidos"
WHERE "atualizado_em" > $1 -- $1 = last watermark
ORDER BY "atualizado_em" ASC
LIMIT 5000; -- one chunk

The 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 null and 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#

SymptomLikely causeWhat to do
Always falls back to fullno previous snapshot, or no valid cursorDo the 1st full load; choose a truly increasing column
Rows deleted at the source are still therewatermark does not see deletionsUse period/complete partitions/tombstones or a full load
Numbers do not match the full loadwrong ordering/keyMake sure ORDER BY = key ASC; review the upsert key
Cursor rejectedvalue > 64 chars or with quotesUse 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.

Related links