Demonstration project
Data Reconciliation Monitor
A morning check that tells the business which data it can use today, and gives the data team the evidence behind every answer.
Synthetic data · fictional client · model, DAX and data generator on GitHub.
The problem
Thornfield's warehouse is loaded every night from six source systems into eight data areas that finance, trading and supply chain report from. When a load failed, the business found out from a wrong figure in a meeting. The data team needed one page to say, before the reports were opened, which areas could be used and which reports to hold back.
What it showed
For the load of Friday 30 October 2026, the Status page gives the answer in one board, and any of the last 90 nights can be selected to see that morning as it was.
- Seven of eight areas can be usedSupplier invoices are not ready: one of three files did not arrive, so 1,204 invoices worth £214,608 are missing and the last file landed at 05:41, 41 minutes late. Accounts payable and supplier spend reporting waits for the resend.
- Supplier invoices are the recurring problemThey had trouble on 5 of the last 14 nights and hold 5 of the 14 open issues, mostly late or incomplete files from the supplier. Online orders are 3 orders (£412) short for the third night running, inside tolerance and traced to refunds posted after the extract closes, so they are fine for trends.
- Issues are resolved on time, but the queue is growingIssues closed in the last four weeks took 2.8 days on average against a 3-day target, yet 14 are open against 2 four weeks earlier. The backlog is what the data team lead needs to raise.
How it's built
- No status is storedThe load records what it moved: rows and value at the source and in the warehouse, files received and when, and records failing each of 48 rules. One measure compares those figures with the limits in the rulebook, so a tolerance changes in one place and every status follows.
- Only the load checks decide whether an area can be usedRows and value against the source and files in by 05:00 set the status. The other quality rules add notes for the data team and never hold a report back.
- Any past morning reads as it wasTrend charts use their own night axis, so they always end on the night selected, and the issue measures compare the dates opened and resolved with that night, so the queue shows what was open at the time.
- Real problems, handled in the openThe data carries the problems real monitoring tables do: one night's store sales checks written twice by a rerun, arrival times recorded in three formats, nights with no load scheduled, areas with no money to reconcile, a rule added part-way through the window and an issue closed before it was opened. The Reconciliation page counts each one.
Every figure on this page was recalculated from the source files outside Power BI, and matches the report.
The source
The semantic model, the DAX and Power Query, the report pages and the script that writes the data are on GitHub, all readable as plain text. The report itself, with its data, is a .pbix file in the project's release, with a PDF of every page.
All data in this project is synthetic. Thornfield Retail Group is a fictional client created to demonstrate the work, and no real client data appears anywhere in it.