Read first:
README.md. Medallion layers inpipeline-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:
- the result is partitionable by the period column (each partition is calculated on its own);
- the query orders by the period column, ASC, as the 1st key (history + recent already come out in the full's order);
- 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)#
- State — available. Eligibility and matching results between incremental and full are covered by automated tests.
- What it does — updates snapshots without recalculating everything, with a guarantee that each run matches the full with an identical result.
- How it works — full (fallback) · period (recent window) · key/watermark (delta + upsert) · backfill (wide window / 1st extraction) · checkpoint (resumption).
- Plan · Permission — core · access check · kill switches for ingestion runs / dashboard refresh / data console queries.
- Cost — the incremental exists to reduce cost (fewer bytes read → lower estimated cost); the per-query volume cap always protects.
- Limits — period window default 7 / cap 400 days; pagination cap 10,000 pages / 5,000,000 rows; cursor ≤ 64 chars.
- Security — column names validated as identifiers; anti-injection cursor literal; per-workspace scope on load.
- 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).
- 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.