Demonstration project
Data Reconciliation Monitor
A five-page monitor for Thornfield Retail Group, a fictional multi-channel retailer whose nightly batch loads eight feeds from six source systems into an analytics warehouse. This is the report a data team runs before the business runs theirs.
The morning check for Monday 29 June 2026: 4.65m rows compared, a 99.97% match rate against a stated 99.90% threshold, six of eight feeds passing, and what failed last night, worst first.
Synthetic data · fictional client · the full model, DAX and data generator are on GitHub (available on request).
The problem
Thornfield Retail Group's business reports are only as good as last night's batch. Every morning, before anyone opens a dashboard, the data platform team needs three answers: did the loads land, does the warehouse actually match the source systems, and which discrepancies are still open and whose job are they? The board is mostly green on purpose: a monitor that is always red teaches people to ignore it.
What the report does
Five pages, from the ten-second read to the single feed:
- Morning Check - the ten-second read: 4.65m source rows compared, the match rate against a stated 99.90% threshold, a status board with one tile per feed, the 30-night trend, and what failed last night, worst first.
- Reconciliation - the pairwise proof: source rows next to warehouse rows, source amounts next to warehouse amounts, feed by feed, with tolerances stated on the page. Below it, the check grid: one square per feed per night, a fortnight deep.
- Quality Rules - once data is in, is it fit to use? A 48-rule rulebook across the five classic quality dimensions, each rule with its stated tolerance, worst first.
- Break Register - every unresolved discrepancy, owned and aged: median age, mean time to resolve against a target, and a register with an owner and a status in plain words. The queue, judged as a process.
- Feed Detail - one feed end to end, reached by right-click drillthrough: paired source-versus-warehouse bars a fortnight deep, the feed's checks, and its open breaks.
The pairwise proof and the check grid: green means a check passed, amber a warning inside tolerance, red a failure, and no-run nights say "no run", never "failure". A recurring problem reads as a pattern, not an incident.
The break register: 14 open discrepancies owned and aged, median age three days, mean time to resolve 2.7 days against a 3.0-day target. Statuses in plain words, because a queue is a process, not a chart.
Under the hood
Two decisions carry the model. First, the facts are measurements about data, not the data itself: the warehouse does not re-store 2.4 million till-roll rows, it stores that last night's extract had 2,412,384 rows at source and 2,412,384 landed. About 4,700 fact rows describe millions of moving rows a night, which is how a real reconciliation monitor works. Second, verdicts are computed, never stored: the pipeline records only raw observations (counts, amounts, minutes late), and pass, warn or fail is derived in the model from thresholds held on the rule table. Change a tolerance and every verdict, colour and KPI on every page re-derives.
The wrinkles are meta this time: six deliberate defects in the monitoring data itself, handled in the open, including no-run nights that must read "no run", a duplicated rerun result set, a rule added mid-window, and one break recorded as resolved before it opened, flagged by a measure and excluded from the metrics but left visible.
The source
The whole project is readable as text: the semantic model in TMDL, the report in PBIR, the DAX measure by measure, and the data generator that produced every figure on this page. The repository is private while the portfolio is finalised; the source is available on request.
All data in this case study is synthetic, generated by a committed, deterministic script; Thornfield Retail Group is a fictional client created to demonstrate the work. No real client data appears anywhere in this project.