Wholesale Sales, Margin and Stock Reporting Mart

CREATE TABLE mart.monthly_commercial_report AS
WITH months AS (
  SELECT DISTINCT month_start
  FROM conformed.dim_date
),
sales AS (
  SELECT
    date.month_start,
    COUNT(*) AS invoice_lines,
    COUNT(DISTINCT invoice_id) AS invoices,
    SUM(quantity) AS units_sold,
    SUM(net_sales_value) AS net_sales_value,
    SUM(line_profit) AS gross_profit,
    SUM(extended_price) AS invoice_value
  FROM conformed.fact_sales sale
  JOIN conformed.dim_date date ON sale.invoice_date_key = date.date_key
  GROUP BY date.month_start
),
purchasing AS (
  SELECT
    date.month_start,
    COUNT(*) AS purchase_order_lines,
    SUM(expected_order_value) AS expected_purchase_value
  FROM conformed.fact_purchasing purchase
  JOIN conformed.dim_date date ON purchase.order_date_key = date.date_key
  GROUP BY date.month_start
),
stock AS (
  SELECT
    date.month_start,
    COUNT(*) AS stock_movements,
    SUM(quantity) AS net_stock_movement
  FROM conformed.fact_stock_movement movement
  JOIN conformed.dim_date date ON movement.transaction_date_key = date.date_key
  GROUP BY date.month_start
)
SELECT
  months.month_start,

Eight ordered files create the schemas, load the Microsoft records, build the reporting tables and stop the release when a check fails.

1. 0100_create_schemas.sql

   Creates raw, staging, conformed, mart and audit schemas before any reporting table is built.

2. 0210_load_raw.sql

   Loads the selected Wide World Importers tables without changing the Microsoft business values.

3. 0320_stage_sources.sql

   Joins operational headers to their lines, types the fields and records failures before the reporting tables are released.

4. 0430_build_dimensions.sql

   Creates shared date, customer, product, supplier, warehouse and salesperson dimensions.

5. 0540_build_facts.sql

   Builds one sales row per posted invoice line, one purchasing row per order line and one stock movement per event.

6. 0650_build_marts.sql

   Produces the monthly commercial report, product margin view and current stock position.

7. 0760_run_tests.sql

   Checks keys, dates, references, row counts and invoice, purchase and stock values before release.

8. 0870_build_trace_and_release.sql

   Retains the six-step invoice-line trace and records whether the monthly report is ready.

1. 01
   Original invoice lineSales.InvoiceLines

   Invoice 1 · stock item 67 · quantity 10 · invoice value £2,645

   The original Microsoft row is retained without business rewriting.

2. 02
   Staging joinstaging.sales_line_quality

   Customer 832 · order 1 · invoice date 1 January 2013

   The line is joined to its invoice and sales order, and its dates and references are checked.

3. 03
   Conformed keysconformed dimensions

   Customer key 434 · product key 67 · salesperson key 1

   Stable reporting keys connect the line to the same customer, product, salesperson and date used by every report.

4. 04
   Sales factconformed.fact_sales

   Net sales £2,300 · gross profit £850

   The published fact keeps one row per posted invoice line.

5. 05
   Validationaudit.validation_results

   18 of 18 checks passed

   Count, key, relationship, date and value checks must pass before release.

6. 06
   Report measuremart.monthly_commercial_report

   Revenue measure = SUM(fact_sales[net_sales_value]) · month 1 January 2013

   The line contributes once to the retained monthly sales result and to the documented downstream DAX measure.

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

Net sales

£4,970,933

Gross profit

£2,443,449

Invoice lines excluded

0

Publish or hold

READY

• Wide World Importers is a public Microsoft sample, not a client system or a current trading company.
• The local DuckDB build demonstrates modelling and controls, not production security, orchestration or operational scale.
• The selected Microsoft records produce no open exceptions, so nine deliberately broken records are tested in a separate fixture and never enter the reporting facts.
• A separate T-SQL deployment package is retained and linted, but it is not presented as executed SQL Server evidence.