Reporting Foundations

SQL-backed Reporting Foundation

A wholesale reporting mart built from Microsoft’s Wide World Importers sample database so sales, purchasing, stock and margin reports use the same customers, products, dates and totals.

Executed wholesale mart evidenceOpen image

Executed DuckDB evidence generated from the retained Wide World Importers reporting mart. This is not a Power BI or database-client screenshot.

A wholesale month end

Can the sales and stock report be refreshed without carrying a bad record into the totals?

A reporting analyst prepares monthly sales, margin, purchasing and stock figures for finance, sales and warehouse managers. The operational database stores those records in different tables, so a customer, product or value can disagree long before anyone sees the finished report.

This project gives the four managers one reporting model. It also keeps any invoice line, purchase order line or stock movement that fails a check out of the published totals until the responsible team has investigated it.

Invoice lines: 228,265
Business months: 41
Stock movements: 236,667
Release checks: 18 passed

How the model works

Operational rows converge on one set of reporting definitions.

Each fact table has a declared grain: one posted invoice line, one purchase order line or one stock movement. Shared dimensions make sure a product or customer means the same thing wherever it appears.

01
Operational records
Sales invoices, purchase orders and warehouse stock movements arrive at their own level of detail.
02
Checks before reporting
Business keys, references, dates and values are tested. Failed rows keep their reason and original key.
03
Shared dimensions and facts
Every report uses the same customers, products, suppliers, warehouses, salespeople and dates.
04
Monthly review
Sales, margin, purchasing and stock outputs are released only after all 18 checks pass.

The reporting tables cover 2013-01-01 to 2016-05-31. Sales and margin come from invoice lines, purchasing commitments come from order lines, and warehouse movement remains a separate event fact.

The selected official source rows all passed the publication rules. Nine deliberately broken fixture records prove that duplicate, missing-reference, date, cancellation, credit and value-mismatch cases are detected without being mixed into Microsoft's sample data.

Executable SQL

The build can be read, rerun and checked one stage at a time.

The files use the modular staging, conformed and mart structure common in analytics engineering. DuckDB keeps this public example portable; it does not imply a live cloud platform or production scheduler.

0100_create_schemas.sql
Creates raw, staging, conformed, mart and audit schemas before any reporting table is built.
0210_load_raw.sql
Loads the selected Wide World Importers tables without changing the source values.
0320_stage_sources.sql
Joins operational headers to their lines, types the fields and records failures before publication.
0430_build_dimensions.sql
Creates shared date, customer, product, supplier, warehouse and salesperson dimensions.
0540_build_facts.sql
Builds one sales row per posted invoice line, one purchasing row per order line and one stock movement per event.
0650_build_marts.sql
Produces the monthly commercial report, product margin view and current stock position.
0760_run_tests.sql
Checks keys, dates, references, row counts and source-to-report values before release.
0870_build_trace_and_release.sql
Retains the six-step invoice-line trace and records whether the monthly report is ready.

Executed evidence

The retained outputs show the model running, not a promotional substitute for it.

These views are generated from the checked DuckDB build. The complete CSV outputs, SQL files, database, manifest and execution log remain with the project.

Executed DuckDB evidence showing sales, purchasing and stock fact counts, the May 2016 commercial result and passing release controls — **Executed mart and release results**
The retained result combines the actual query result with the release decision. It shows what loaded, what the latest monthly mart contains and whether the checks allow publication.

Wholesale reporting lineage and six-step trace of InvoiceLineID 1 from the Microsoft source table to the monthly revenue measure — **Invoice line trace and reporting lineage**
InvoiceLineID 1 keeps its original source values, gains shared reporting keys, enters the sales fact once, passes all controls and contributes to the documented monthly measure.

One invoice line, start to finish

InvoiceLineID 1 can be followed from Microsoft's table to the monthly revenue measure.

The trace is generated by the build. It is not a narrative example typed into the website after the fact.

01
Original invoice lineSales.InvoiceLines
Invoice 1 · stock item 67 · quantity 10 · invoice value £2,645
The original Microsoft row is retained without rewriting the business values.
02
Sales contextstaging.sales_line_quality
Customer 832 · order 1 · invoice date 1 January 2013
The invoice line is joined to its order and customer, then its dates and references are checked.
03
Shared reporting keysconformed dimensions
Customer key 434 · product key 67 · salesperson key 1
The line now uses the same customer, product, salesperson and calendar references as every report.
04
Sales factconformed.fact_sales
Net sales £2,300 · gross profit £850
The reporting fact keeps one row for this posted invoice line.
05
Release checksaudit.validation_results
18 of 18 checks passed
Counts, keys, relationships, dates and values all pass before the report is released.
06
Monthly measuremart.monthly_commercial_report
Revenue = SUM(fact_sales[net_sales_value])
The line contributes once to the January 2013 result and to the documented downstream measure.

Before the report is released

Counts, relationships and values must all reconcile.

The SQL build stops if a control fails. A second clean rebuild then compares the row counts, financial totals and output fingerprint so the same inputs cannot quietly produce a different result.

Check	Result	Owner	Why it matters
Invoice-line source scale	Pass	Data engineer	The official source contains 228,265 invoice lines, above the publication threshold.
Invoice-line business key	Pass	Finance systems analyst	InvoiceLineID is unique, so one source line cannot be counted twice.
Invoice value reconciliation	Pass	Finance business partner	The source invoice value balances to the sales fact and held records to one penny.
Purchase-order line reconciliation	Pass	Purchasing analyst	Every valid purchase line enters the purchasing fact once.
Stock movement reconciliation	Pass	Warehouse manager	Every valid movement enters the stock fact once.
Monthly sales reconciliation	Pass	Finance business partner	The monthly mart retains the complete net sales value from the sales fact.

The retained validation file contains all 18 checks, including 6 release controls highlighted here.

Latest retained month · May 2016

The report answers a monthly question without hiding the records behind it.

The May 2016 mart contains 6,351 invoice lines and £4,970,933 of net sales. Gross margin is 49.15%.

Purchasing contributes 210 order lines, while the warehouse view retains 6,562 movement events. Finance, sales and warehouse managers can compare those outputs without flattening them into one ambiguous table.

Net sales: £4,970,933
Gross profit: £2,443,449
Source rows held: 0
Release decision: READY

Data and limits

Built from Microsoft's public wholesale sample and verified by 18 controls.

This proves the retained SQL build and checks. It does not claim a client result, production security or live scheduling.

Technical documentationOpen the retained data, rebuild command and detailed limitations

npm run verify:sql-foundation

The command verifies Microsoft's BACPAC checksum, extracts the selected tables, runs all eight SQL stages, exports the results and performs a second clean rebuild. The retained result fingerprint is 47ea77cb3db30d65821907bb622a48a1.

The data comes from Microsoft's Wide World Importers public sample, release wide-world-importers-v1.0, under the Microsoft SQL Server samples MIT licence.

Next step

Review the model beneath a monthly commercial report.

Start with the files, joins and checks that make the current reporting route difficult to trust or maintain.

Discuss a reporting model