Large Facts: Why Does a Green Refresh Still Drift?

Large facts can "succeed" on every run and still lie. Row counts look fine. Pipelines are green. Measures drift because deletes, late arrivals, and corrections were never part of the incremental contract. Analysts discover it weekly. Engineers call it flaky. It is usually a missing agreement.

Large fact tables are the classic patient. The disease is mismatched notebooks, watermarks, and semantic consumers. Fast materialization without drift tests just fails faster.

Figure 1. Contract before green. Write incremental meaning, align watermarks with corrections, gate drift in-pipeline, then publish an owned refresh SLA. Exit code zero is not honesty.

Context and constraint

A common failure: on large fact tables, daily loads look healthy while measures wander. The pipeline is not random. The watermark tells a narrower story than the business believes. Once the contract is written and drift is tested in-pipeline, "flaky" becomes a closed ticket class.

Row-count success can hide deletes that never landed, late arrivals outside the watermark, and corrections applied upstream but ignored downstream. Semantic consumers assume a grain and a freshness story. If notebooks disagree, drift is the symptom. Naming it flaky delays the fix.

Figure 2 is the Learn reminder that scheduled refresh is an operations surface. Green schedules still need an incremental contract that covers late arrivals and corrections.

Figure 2. Refresh can be scheduled and still lie about history. The schedule is not the contract. Source: Microsoft Learn: data refresh in Power BI.

Design

1. Write the incremental contract in one page

State grain, watermark column meaning, how deletes appear, how late arrivals are handled, and how corrections reopen history. If two engineers disagree after reading it, the page is not done. Contracts beat tribal notebook comments.

2. Align watermarks with business correction rules

If the business expects same-day corrections to flow, a strict append-only watermark will drift measures. If the business expects immutable daily snapshots, soft corrections need a different path. Pick deliberately and test both happy and correction paths. Figure 3 is the fork.

Figure 3. Append-only is simpler. Correction-friendly is harder and sometimes required. The business rule picks the watermark story; notebook habit does not.

3. Put drift checks in the pipeline

Compare key measures or hash aggregates against a prior trusted mark or a reconciliation source within tolerance. Fail the run or quarantine the slice when drift exceeds policy. Weekly analyst surprise is not a control. Figure 4 is the gate.

Figure 4. Load can be green and still dishonest. The drift gate decides publish versus quarantine before consumers see the slice.

4. Publish a refresh SLA per fact grain

Say how fresh the fact should be, for whom, and what "good" means beyond green checks. SLAs without drift tests are uptime theatre. SLAs with owners create escalation paths.

5. Separate speed from honesty

Materialize quickly after the contract is testable. Faster wrong facts only spread wrong decisions sooner. Performance work follows correctness gates, not the reverse.

6. Trace notebook, watermark, and semantic assumptions

Inventory where each assumption lives. If the semantic model expects SCD behavior the notebook does not provide, document the mismatch as a defect. Drift often sits in the seams between teams.

What a drift test can look like without boiling the ocean

You do not need perfect reconciliation automation on day one. Start with stable keys: count of distinct rows in a closed window, sum of a key measure for a locked period, or null rates on critical foreign keys. Compare to the previous successful mark and to a source extract for a sample day. Tolerances should be explicit. Zero tolerance on volatile open periods is how you create false failures; infinite tolerance is how you create false calm.

Communicating drift to stakeholders

Say: the pipeline succeeded mechanically; the incremental contract failed for this class of correction; here is the business rule we will encode; here is the test that will block release if it regresses. Avoid "the platform was flaky." Avoid blaming analysts for noticing. Precision builds trust faster than reassurance.

Rebuild versus repair

Some drift requires a bounded rebuild of a date range. Some requires a forward fix only. Encode which situations demand rebuild in the runbook. Leaving that choice to 2 a.m. judgment guarantees inconsistent history. Pair rebuild playbooks with capacity awareness so honesty does not become an accidental denial-of-service on the capacity.

Common mistakes

Green equals true. Exit codes replace reconciliation.

Undefined deletes. Rows that should vanish stay forever.

Late arrivals ignored. Business thinks they are in; watermark says no.

Analyst-as-monitor. Drift found in meetings, not pipelines.

SLA as marketing. Freshness claims without tests.

Speed-first rewrites. Faster materialization, same contract hole.

Use as-is or adapt

Use as-is

• Large facts publish an incremental contract covering deletes, late arrivals, and corrections.
• Watermark semantics match documented business expectations.
• Drift checks run in-pipeline with explicit tolerances and failure policy.
• Each fact grain has a refresh SLA with an owner.
• Performance work on materialization follows honesty gates.
• Rebuild versus forward-fix rules live in a versioned runbook.

Adapt before you use

Contracts and drift tests add design time and CI minutes. Undefined incremental behavior adds silent arguments about which number is right. Tighter watermarks are simpler and righter for some cases; correction-friendly watermarks are harder and necessary for others. Quarantine paths complicate consumer wiring; silent publish of drifted slices complicates trust. SLAs create accountability and surface underfunding early.

Primary references: data refresh in Power BI, Fabric pipeline and semantic model refresh guidance on Microsoft Learn, plus your fact-grain ownership docs. Pair SLA text with the drift test names so operations and analytics share vocabulary.

Define incremental meaning. Test it in the pipeline. Publish the SLA. Align watermarks with corrections. Prefer honest speed. Stop calling contract breaks flaky.