Row counts lie: validate MLVs column by column

A migration checklist that only compares row counts will greenlight wrong money, wrong dates, and wrong statuses. Row counts catch catastrophic drops. They miss the quiet CAST that turns decimals into floats, the null that became empty string, and the column that silently defaulted.

Push MLV cutovers to per-column tables and type-aware aggregates. Borrow the tiered reconciliation classical data migrations already trust: rows, then keys, then column aggregates. Automate it. Treat the artifacts as release evidence, not as a screenshot buried in chat.

Figure 1. Row counts catch catastrophes. Column aggregates and quality reports catch the quiet CAST that breaks money. Source: Microsoft Learn: MLV data quality reports.

Figure 2. Treat per-column reconciliation artifacts as release evidence for MLV cutovers, not optional notebook scratch. Source: Microsoft Learn: MLV data quality reports.

I learned this when a cutover matched on rows and failed on a measure that summed a decimal column the MLV had widened into approximate floats. Finance did not care that Spark finished early. They cared that two totals disagreed by enough to start an argument.

The core idea

Semantic drift hides in columns. Validation that cannot name a column cannot protect a cutover.

Tiered reconciliation exists because each tier answers a different question. Rows ask "did we lose volume?" Keys ask "do we have the same entities?" Column aggregates ask "do the values still mean the same thing?" Type-aware compares catch float/decimal and null/empty mismatches that equality on strings will paper over. One checklist row per column makes ownership and sign-off possible. Screenshot QA is not evidence.

A model that stays explainable

1. Run tiers in order and stop on hard fails

Start with row counts and freshness windows. Then compare business keys (presence, duplicates, orphans). Only then run column aggregates and distribution checks. If keys disagree, column sums will lie about why. Stopping early saves analyst time and prevents false comfort from "totals look close."

2. Make type-aware compares mandatory

Decimals, floats, timestamps, and strings need different tolerances and null semantics. Empty string versus null is a product decision, not a Spark curiosity. Encode expected types from the contract and fail when the MLV publishes another type "because it still sorted." Floating compares need explicit epsilon or decimal quantization you can defend in review.

3. One checklist row per column

Each column gets owner, compare method, tolerance, last result, and sign-off. Wide tables stop being a vibes exercise. Reviewers can ask who accepted residual risk on a fuzzy float instead of hoping someone remembers. Missing checklist rows are unfinished work, not optional polish.

4. Automate in analyst scripts, not slide decks

Notebooks or jobs should emit machine-readable results: pass/fail, deltas, sample failing keys. Humans interpret exceptions. Humans should not retype counts from two query windows into Excel every Friday. Automation also means you can re-run after a "tiny" MLV edit without heroics.

5. Keep sample mismatches next to aggregates

Aggregates tell you something is wrong. Samples tell you what. Persist a bounded set of disagreeing keys per failed column for triage. Without samples, every failure becomes a research project. With samples, you see the CAST or join bug in minutes.

6. Store validation artifacts as release evidence

Attach outputs to the release record the same way you attach pipeline run ids. Auditors and future you will ask what was compared on cutover day. Chat screenshots age poorly. Versioned result files next to the MLV notebook age honestly.

Failure modes I design against

Rows-only green. Volume matches; money does not.

Stringify everything and diff. Types and nulls disappear into text coincidence.

One giant "data quality" score. Nobody knows which column failed or who owns the fix.

Manual screenshot QA. Unrepeatable, unreviewable, gone after the meeting.

Aggregates without samples. Endless debates about whether the harness is wrong.

Validating once at cutover. Drift returns the next MLV tweak; the harness must be reusable.

Building a harness people will actually run

Keep the harness boring: same input snapshots, same tier order, same output schema every run. Parameterize MLV name and stage. Publish a one-page how-to in the repo so on-call is not reverse-engineering your notebook from May. If the harness takes an hour to start, people will skip it under schedule pressure. Optimize for reruns. Cache baseline extracts when the warehouse side is expensive, and record which baseline snapshot you used so arguments stay about data, not about which Tuesday.

What "close enough" must mean in writing

If two decimal sums differ by a rounding policy you accept, write the policy next to the column. "Looks fine" is not a tolerance. Prefer comparing quantized values (for example money to two places) over hoping float equality will behave. When a column is intentionally non-deterministic (timestamps of processing), exclude it from parity or compare on business-effective times only. Exclusions are allowed. Silent exclusions are not.

Trade-offs

Column-level validation costs compute and authoring time. Wrong cutovers cost trust. Tight tolerances create noise on legitimate float columns; document epsilons per column instead of global looseness. Per-column ownership politics can stall sign-off. That stall is cheaper than unsigned production drift. Automating samples needs PII care; hash or restrict columns that leave the lakehouse.

What I would put on an ADR

1. MLV cutovers require tiered reconciliation: rows, keys, column aggregates.
2. Compare methods are type-aware with documented tolerances.
3. A per-column checklist with owner and sign-off is mandatory for gold (and recommended for silver).
4. Validation jobs emit machine-readable results plus bounded mismatch samples.
5. Artifacts are retained as release evidence beside the MLV definition.
6. The harness is rerun on material MLV changes, not only on first cutover.

Primary references: Fabric monitoring and data quality practices on Microsoft Learn, plus classical migration testing patterns for reconciliation. Pair platform tooling with a written column contract so "green" means something specific.

Closing

Row counts are a smoke test. They are not a semantic proof.

Validate column by column, with types and owners and artifacts you can replay. MLV cutovers stop being faith-based the moment the checklist can name what drifted.