Partition Scoping Is the Real Incremental Decision

Incremental refresh processes only changed partitions rather than a full dataset. That sentence gets written into a design doc, somebody adds materialized='incremental' to a model, the run time drops, and everyone moves on. The part nobody writes down is the harder one: what counts as changed, how wide a window you re-process to absorb the cases where you were wrong, and what happens to rows the source quietly deleted.

If you own the platform rather than the dashboard sitting on top of it, those three questions are your actual surface area. Run time is a symptom. Partition scoping is the decision.

What does “changed” actually mean in your source?

There are at least three different timestamps competing for the job, and picking the wrong one is the root cause of most silent gaps.

Event time is when the thing happened in the business — order placed, meter read, ticket closed. It is what analysts filter on and what your fact table is almost certainly partitioned by. It is also the one timestamp that can move backwards relative to your pipeline: an order placed before midnight can land in your source after you have already advanced the watermark past it.

Ingestion time is when the row arrived in your landing zone. It only ever moves forward, which makes it a safe watermark and a terrible partition key for anything a human will query.

Mutation time is when the source row was last updated. It exists if you are lucky, is maintained by application code if you are less lucky, and is not updated on delete at all.

The common failure is to filter on event time with a watermark derived from the last successful run. Every late arrival slips under it permanently. The data is not wrong in a way that throws an error — it is wrong in a way that shows up as a quietly low number in a month-old period that nobody re-checks.

How wide should the rescan window be?

Wide enough to cover the tail of late arrivals from your worst-behaved source, and no wider. This is an empirical question about your own data, not a convention you copy from someone else’s blog post. Measure the distribution of ingestion_time - event_time per source, look at where it stops, and set the window past that point. Then put the window in a variable, not inline in the SQL, because you will change it.

The important property is that the window is a replacement window, not a merge window. If you re-process the last N days by merging on a business key, you will update rows that changed and insert rows that are new, but you will never remove rows that were deleted upstream. Deletes only disappear if you drop the partition and rewrite it from source. Merge-on-key pipelines accumulate tombstoned rows for years and nobody notices until a reconciliation finally runs.

{{ config(
  materialized = 'incremental',
  incremental_strategy = 'insert_overwrite',
  partition_by = { 'field': 'event_date', 'data_type': 'date' }
) }}

select ...
from {{ source('ops', 'orders') }}

{% if is_incremental() %}
-- rebuild whole partitions, so upstream deletes actually disappear
where event_date >= date_sub(
  current_date(), interval {{ var('rescan_days') }} day
)
{% endif %}

Note what this does and does not promise. It replaces whole day partitions, so a deleted order vanishes. It does nothing at all for a correction landing outside the window — that needs a targeted re-run with an explicit date range, which means your orchestration has to accept a date range as a parameter rather than always computing one from “now”.

How small should a partition be?

Small enough to prune usefully, large enough that the file count does not strangle you. These pull in opposite directions, which is why the total cost of a refresh is convex in partition size rather than monotonic.

The failure here is almost always over-partitioning, and it is usually driven by a good intention: somebody notices that queries filter on region and source system as well as date, and adds both to the partition key. Now every daily load writes a cross-product of tiny files, the table’s metadata grows faster than its data, and maintenance operations that used to be background noise start competing with the load itself.

If a column is low cardinality and genuinely always in the predicate, clustering or a sort key gets you most of the pruning benefit without multiplying partition count. Keep the partition key one-dimensional and time-based unless you have a specific, measured reason not to.

Does the partition key match the filter the engine actually sees?

Partition pruning is a text-matching exercise more than people expect. It works when the predicate references the partition column directly with a literal or a parameter the planner can resolve. It silently stops working when the predicate wraps the column in a function, compares it to a value of a different type, or joins to a date dimension and filters on an attribute of that dimension instead.

The last one is the quiet killer in star schemas. The analyst filters on dim_date.fiscal_quarter, the engine has no way to translate that into a range on fact.event_date without reading the dimension first, and your beautifully partitioned fact table gets scanned end to end. Some engines handle this through dynamic partition pruning; relying on that without verifying it per engine is a guess. Read the plan.

The same mismatch shows up one layer higher. Power BI incremental refresh maintains its own partitions, driven by RangeStart and RangeEnd parameters folded back into the source query. If those parameters do not fold — because the query sits on top of a view with a non-foldable step, or hits a source that cannot push the predicate — the dataset refresh still works, and quietly pulls the full table for every partition it rebuilds. You now have two independent partition schemes that need to agree, and only one of them complains when it is wrong.

How do you know the incremental output still matches a full rebuild?

You run both, on a schedule, on a bounded slice, and compare. A reconciliation job that fully recomputes a rolling window into a scratch table and diffs row counts and key measures against the incremental table is unglamorous and catches the entire class of defects described above. It catches the dropped late arrival, the accumulated tombstone, the window that was too narrow when volumes grew.

Make it fail loudly and route it to whoever owns the pipeline, not to a dashboard. A reconciliation check that reports into a tile nobody opens is a reconciliation check that does not exist.

Should this model be incremental at all?

Not every model earns the complexity. Incremental logic doubles the number of code paths in a model — the first-run path and the incremental path — and those paths drift apart because only one of them runs regularly.

The pattern underneath all of this: incremental refresh converts a correctness problem into a cost problem, and then hands you back a new correctness problem that no longer announces itself. The partition grain, the rescan window, and the reconciliation job are the three things that decide whether that trade was worth making. Pick them deliberately, write down why, and re-examine them when the volume changes — because the window that covered your late arrivals at one scale will not necessarily cover them at the next.