Skip to content

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

Updated on 2026-10-05

Load modes — full, incremental by watermark and backfill

When to use a full load, an incremental load by watermark and a backfill — and why continuous capture (CDC) is not available yet.

On this page (12)

Read first: conceitos-fluxo-de-dados.md. Detailed reference: ingestao-incremental-backfill.md.


The rule that cannot break#

There is a single invariant behind all incremental modes: the incremental result must be IDENTICAL to the full refresh — same rows, same order. If there is the slightest doubt about eligibility, the product refuses the incremental and does the full (with the reason in the log).

"An expensive refresh is a cost problem; a wrong number is a product problem."

The three modes, in business language#

Full

Recalculates everything, from scratch. It is the safe fallback that is always available: any refusal from an incremental mode lands here. Correct by definition, it is the most expensive.

  • Use when: it is the 1st load; the table is small; the source deletes rows and you have no deletion signal; or you want maximum assurance of accuracy.

Incremental by watermark

Stores the watermark — the highest value of a column that only grows (an update date, a sequential id) — and, on the next run, fetches only the new rows (column > watermark). The rest of the history is reused intact.

In a database pull, this is done with KEYSET (seek): the query orders by the watermark column (ORDER BY wm ASC) and filters WHERE wm > <last watermark>, advancing the watermark to the highest value of the last batch. Unlike paging with OFFSET, KEYSET is stable even if the source changes during extraction.

  • Use when: the table is large and only grows/updates (orders, events, ledger entries) and there is a reliable increasing column.
  • Do not use when: the source physically removes rows without leaving a trace (see "Deletion semantics" below) or there is no stable increasing column.

Backfill (historical load)

Bringing in the history before the incremental window — for example, the last 24 months when the incremental only looks at the last few days.

  • How: widen the recent window (in incremental by period) or run a full once. In a database/BigQuery pull, the first extraction is already the backfill: it goes through the pagination up to the cap and the following runs resume from the watermark.
  • Use when: you just connected a source, changed a business rule, or need to reprocess a specific period.

Which one to choose (quick guide)#

SituationRecommended mode
First load of a sourceFull (it is the initial backfill)
Large table that only grows, with an increasing date/idIncremental by watermark
Small table (a few thousand rows)Full (simple and cheap enough)
Source that deletes rows without a deletion signalFull (or period re-fetching whole days)
Need to reprocess an old historyBackfill (wide window or one-off full)

Watermark is not CDC (honesty)#

This is important and the product is explicit:

  • Watermark (what exists today): compares an increasing column and brings the delta with >. It is simple, robust and cheap — but it does not see deletions and depends on the column actually growing.
  • CDC — Change Data Capture: reading the source's transaction log to capture each INSERT/UPDATE/DELETE. It is not available in Ingestia yet. Do not confuse the two: when this documentation talks about "incremental load", it is always by watermark.

Need fresher data than the daily batch? Micro-batch runs the incremental load by watermark every 5 or 15 minutes (Business and Scale plans) — it is the product's near real time option. See Micro-batch.

Deletion semantics (accepted limitation)

In the incremental snapshot refresh (widget/mart), the delta comes in by UPSERT by key; in the Bronze layer there is no upsert or MERGE — the window is deleted at the destination and appended again (DELETE of the window + append). In neither case does the incremental remove a key that disappeared from the source without a deletion signal — the same contract as tools like dbt and Power BI. Two optional signals restore accuracy under deletion:

  • complete partitions — within a partition re-fetched in full, the old key missing from the delta is removed;
  • tombstones (deleted keys) — explicit deletions, when the source provides them.

Data whose source deletes rows should not use pure watermark. Use the period mode (re-fetches whole days) or do a full.

Why the incremental "refuses and does a full"#

The product prefers an expensive refresh to a wrong number. It falls back to full, with a readable reason in the log, when: there is no previous snapshot; the snapshot is truncated/empty/has an error; there is no usable cursor column; the ordering is not exactly the key ASC; or the delta cannot be cut safely. This is a feature, not a failure.

Limits and costs#

  • Incremental window by period: default 7 days, cap 400.
  • Extraction pagination: hard cap 10,000 pages / 5,000,000 rows.
  • Database pull chunking: batchSize default 5,000 (between 100 and 50,000); maxRows default 200,000 (up to 5,000,000).
  • Cursor literal: ≤ 64 characters, no quotes/slash/line break (anti-injection).
  • The incremental exists to reduce cost (fewer bytes read). Cost values on screen are estimates; the amount charged is the one on the invoice.

Security#

Column names (key/cursor) are validated as simple identifiers before entering the SQL; the cursor value uses per-engine binding (named parameter), never concatenation. Per-workspace scope across the whole load.

Related#


Last reviewed: 2026-10-05.

Related links