Skip to content

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

Ingestion — full, incremental, backfill and checkpoint

On this page (7)

Read first: README.md. Medallion layers in pipeline-medalhao.md.


The rule that cannot break#

A single invariant holds across 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 a readable reason in the log).

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


Mode 1 — Full#

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

Mode 2 — Incremental by PERIOD#

For per-period aggregates (any GROUP BY day/month): recalculates only the recent window (default 7 days, cap 400) and reuses the history from the previous snapshot.

Three conditions that sustain the identity:

  1. the result is partitionable by the period column (each partition is calculated on its own);
  2. the query orders by the period column, ASC, as the 1st key (history + recent already come out in the full's order);
  3. the previous snapshot is not truncated.
  • Time zone: the partition key comes out in the dashboard's time zone (America/Sao_Paulo), never UTC — a sale at 10 p.m. on the 31st in São Paulo is 1 a.m. on the 1st in UTC and would fall in the wrong month.
  • Accepted limitation: data that arrives late with a date older than the window is not recalculated (same contract as Power BI/dbt).
  • Column types: date (compared directly) · timestamp (converted to the time zone first).

Mode 3 — Incremental by KEY / watermark#

For detail tables with a stable id: stores a watermark (the highest value of the cursor column in the previous snapshot) and, on the next refresh, fetches only the rows with cursor >= watermark (the delta). Each delta row does an UPSERT by key over the snapshot; the rest of the history is reused intact.

  • >= predicate (not >) on purpose: it re-fetches the row exactly at the watermark (upsert is idempotent) and does not lose a row at the watermark boundary.
  • Watermark = highest cursor value among the rows; no usable cursor → writes null → the next refresh falls back to full (safe).

Deletion semantics (accepted limitation): merge by key is an UPSERT. Without a deletion signal, the incremental does not remove a key that disappeared from the source (same contract as any watermark: dbt, Power BI). Two optional signals restore the identity under deletion:

  • completePartitions — within a partition re-fetched in full, the old key missing from the delta IS removed;
  • deletedKeys — explicit tombstones (CDC with deletion).

A visual whose source deletes rows should not use pure mode:"key" — use the period mode (re-fetches whole days) or pass one of the signals above.

Refusals → full (each with a reason): widget without incremental configured · no SQL · no previous snapshot · snapshot with error/truncated/empty · no cursor in the previous snapshot (N−1 case) · ORDER BY is not exactly the keys ASC · key missing in some history row · delta cannot be cut safely.

Anti-injection: column names (keys/cursor) are validated as simple identifiers before entering the SQL raw; the cursor literal rejects quotes/slash/line break and length > 64 (outside that → does a full).

N−1 compatibility: a new reader reads an old snapshot (without a cursor) without breaking; and when facing an old snapshot, it refuses the incremental and does a full.

Backfill (historical load)#

Backfill = bringing in the history before the incremental window. In practice:

  • By period: increasing recentDays (up to the cap of 400) forces the recalculation of a larger window; a full covers the whole history at once.
  • By connector: the initial extraction goes through the pagination up to the hard cap (default 10,000 pages / 5,000,000 rows) — the first run is the backfill; the following ones resume by checkpoint/watermark. Connectors like Azure Blob skip blobs already processed (name ≤ last processed) so as not to reprocess.

Checkpoint (resume without duplicating or losing)#

The connector SDK exposes an injectable checkpoint contract (where the state lives — product database/managed cache/memory — is the caller's decision; no table is created in the SDK):

  • reads the checkpoint before extracting;
  • writes after each delivered batch (only accepted batches count);
  • clears (null) on completion → the next scheduled run starts from scratch.

Semantics: the checkpoint only exists between the interruption and the resumption. Retry lives below the extraction, so each batch is never delivered twice.


Fact sheet (9-field template)#

  1. State — available. Eligibility and matching results between incremental and full are covered by automated tests.
  2. What it does — updates snapshots without recalculating everything, with a guarantee that each run matches the full with an identical result.
  3. How it works — full (fallback) · period (recent window) · key/watermark (delta + upsert) · backfill (wide window / 1st extraction) · checkpoint (resumption).
  4. Plan · Permission — core · access check · kill switches for ingestion runs / dashboard refresh / data console queries.
  5. Cost — the incremental exists to reduce cost (fewer bytes read → lower estimated cost); the per-query volume cap always protects.
  6. Limits — period window default 7 / cap 400 days; pagination cap 10,000 pages / 5,000,000 rows; cursor ≤ 64 chars.
  7. Security — column names validated as identifiers; anti-injection cursor literal; per-workspace scope on load.
  8. How to validate — in the console, turn on incremental in a time-series visual and confirm that the number from the incremental refresh matches the one from the full refresh; force an ineligible condition and confirm that the product falls back to full (for the safety of the number).
  9. In short — incremental by period and by key, with the same result as the full load; when that cannot be guaranteed, the product falls back to full.

Related links