Operational table contracts
Included files, fields, owners and expected controls are declared before transformation.
Solution Example collection 01
Reporting Foundations
These projects show two controls that come before a report. The first builds one reliable set of sales, purchasing and stock tables from Microsoft's Wide World Importers sample. The second checks whether a purchase order, warehouse receipt and supplier invoice agree before payment or reporting.
Both SQL builds run from retained files. Their published counts, dates and checks are recalculated by the verification commands.
Two engineering decisions
Two reporting problems need two different controls.
The wholesale model aligns customers, products, dates and totals before anyone compares margin. The payment check brings the order, receipt and invoice together before an Accounts Payable analyst decides what happens next.
Declared grain
The row meaning is declared for every transaction, fact, snapshot and exception table.
Staging and marts
File preparation remains separate from the reporting tables used by the business.
Matching rules
Document joins, reason precedence and tolerances are visible in executable SQL.
Tests
Counts, values, keys, dates and exception treatments are checked on each run.
Lineage
A result can be followed back through the model to the records that produced it.
Reproducible execution
Retained records, code, database and verification scripts rebuild the published results.
The two builds
Different questions require different models.
The first project builds reusable reporting tables. The second makes a payment decision for one supplier invoice. Their records, checks and reviewers are deliberately different.
Flagship reporting build
SQL-backed Reporting Foundation
A wholesale distributor needs sales, purchasing, stock and margin reports to use the same customers, products, dates and totals. The build prepares Microsoft’s Wide World Importers sample for that reporting routine.
- Decision
- Can the May 2016 sales and stock report be released, and which sales, purchase or stock records need correction first?
- One record to trace
- InvoiceLineID 1 is followed from Sales.InvoiceLines through staging, shared keys, the sales fact, validation and the monthly revenue measure.
- Current maturity
- Native build validated with 18 passing controls and a repeatable clean rebuild.
Purchase-to-pay decision
Purchase Order, Receipt and Invoice Reconciliation
An Accounts Payable analyst holds an invoice until the purchase order, warehouse receipt and supplier bill agree within tolerance. Every unresolved difference keeps its reason and the team responsible for the next step.
- Decision
- Can the supplier invoice be released for payment, held for investigation or returned for correction?
- One record to trace
- Purchase order item 4507000232_00010 is followed through invoice receipt, goods receipt, clearing and the final payment decision.
- Current maturity
- Native SQL and Python build validated with 21 passing controls, complete event and item counts and a reproducible rebuild.
Evidence comparison
How each build proves its figures.
The wholesale mart reconciles invoice, purchase and stock facts after a clean rebuild. The purchase-to-pay model checks complete event and item counts, unique classification, payment decisions and one item-level trace.
Reporting mart
SQL-backed Reporting Foundation
- Platform
- Microsoft Wide World Importers with a portable DuckDB reporting mart
- Data scale
- 228,265 invoice lines, 8,367 purchase order lines and 236,667 stock movements across 41 business months
- Record trace
- InvoiceLineID 1 is followed from Sales.InvoiceLines through staging, shared keys, the sales fact, validation and the monthly revenue measure.
- Boundary
- The Microsoft sample does not prove a production database, live refresh or customer scale.
- Build status
- Native build validated with 18 passing controls and a repeatable clean rebuild.
Reconciliation control
Purchase Order, Receipt and Invoice Reconciliation
- Platform
- BPI Challenge 2019 with a SQL and Python matching build
- Data scale
- 251,734 purchase order items and 1,595,923 events from the complete BPI Challenge 2019 event log, verified
- Record trace
- Purchase order item 4507000232_00010 is followed through invoice receipt, goods receipt, clearing and the final payment decision.
- Boundary
- The historical anonymised event log supports matching and process analysis; it does not prove a live payment control or the anonymised organisation’s current performance.
- Build status
- Native SQL and Python build validated with 21 passing controls, complete event and item counts and a reproducible rebuild.
Start with one disputed figure
Trace the figures that are hardest to explain.
Bring the current files, one disputed result and the checks already in use. The first conversation does not require confidential or protected data.