Retain source rows
Invoice, order, receipt and reference records keep their original keys and load details.
SQL
Use SQL to make reporting totals traceable to the record.
For analysts and data teams working with operational tables that were designed to process orders, invoices and stock movements rather than answer management questions.
Quanta Meridian uses SQL to give each reporting table a clear grain, apply shared business rules once and retain the checks needed to explain a total.
The working question
How do invoice, order, receipt and reference records become one reporting result without changing their meaning?
See the finished examplesIn the wholesale reporting project, invoice lines, purchase orders and stock movements arrive in separate operational tables. SQL stages those records, assigns shared customer, product and date keys, and builds facts whose totals reconcile to the source. The same approach supports invoice matching, delivery measures and the retest of a corrected product mapping.
Type and standardise
Dates, identifiers and values are prepared once in named staging models.
Declare the grain
Each fact states whether one row is an invoice line, order line, receipt or monthly balance.
Apply the rule
Joins, exclusions and measures use the agreed business definition.
Reconcile the result
Counts and values tie back to source records, with exceptions retained for investigation.
Executed record lineage
One invoice line, followed into the released total.
The values below come from the retained Wide World Importers build. InvoiceLineID 1 keeps its source identity, enters a fact at declared grain, contributes once to January 2013 and reaches a recorded publish or hold outcome.
Source row used in the calculation
Sales.InvoiceLinesInvoiceLineID 1
- Invoice 1
- Stock item 67
- Quantity 10
- £2,645.00
Conformed sales fact
conformed.fact_salesOne posted invoice line
- CustomerKey 434
- ProductKey 67
- Net sales £2,300.00
- Gross profit £850.00
Monthly aggregate
mart.monthly_commercial_reportJanuary 2013
- 5,246 invoice lines
- Net sales £3,770,410.85
- Gross profit £1,890,687.80
Reconciliation decision
audit.validation_results18 of 18 checks passed
- 0 source exceptions
- 9 failure cases detected
- Decision READY
grain = one posted invoice line
key = invoice_line_id
value = SUM(fact_sales.net_sales_value)
group = month_startThe complete ordered SQL remains in the retained build; this compact contract shows the grain, key, value and grouping used by the graphic.
Named report checks
Four controls that challenge the result.
Invoice-line business key is unique
0 duplicate InvoiceLineID values
Invoice value reconciles to fact and held rows
£0.00 residual; checked within one penny
Failure fixture detects every named case
9 of 9 cases detected
Monthly sales mart ties to sales fact
£0.00 residual; checked within one penny
The row grain comes before the join
A sales fact built at invoice-line level can answer product and customer questions without duplicating revenue. A delivery measure calculated at order level needs a different table and a clear rule for split or cancelled orders. Naming that grain first prevents a technically valid join from producing the wrong business total.
Shared dimensions then give Finance, Sales and Operations the same customer, product and date references. Transformations remain close enough to the source for an analyst to trace one record through the model.
The result must survive a challenge
The build keeps the executable SQL, schema, tests and results. Final checks compare row counts, key uniqueness and financial totals; reconciliation projects also keep the exact orders, receipts and invoices that do not agree.
A dashboard image cannot show that calculation. The useful detail is the query result, the failed rows and one source key followed through to the reported measure.
A finished example
DuckDB mart results and report checks
From Wholesale Sales, Margin and Stock Reporting Mart. The saved result combines the actual query result with the decision to publish or hold the report. It shows what loaded, what the latest monthly mart contains and whether the checks allow publication.
Evidence type: executed analytical evidence
Where this is used
How SQL shapes and checks records in inspectable builds
These examples use SQL at different grains: invoice line, purchase item, order, failed record and collision. Each link names the business question, SQL treatment and the check applied to the result.
Browse all 8 project examplesWholesale Sales, Margin and Stock Reporting Mart
A wholesale company needs sales, purchasing, stock and margin reports to use the same customers, products, dates and totals.
How SQL is used
SQL builds shared dimensions and invoice, purchase and stock facts from the Wide World Importers sample database.
Evidence retained
Executed DuckDB results, source-to-report lineage and 18 passing report checks are available to inspect.
Purchase Order, Receipt and Invoice Reconciliation
Accounts Payable should not clear a supplier invoice until the purchase order and warehouse receipt support it.
How SQL is used
SQL compares ordered, received and invoiced quantities and values at purchase-item level.
Evidence retained
The retained output shows supported items, payment holds and the team responsible for each difference.
Supplier Invoice Matching and Review
An Accounts Payable reviewer needs to decide whether an invoice can proceed, must be held or should be returned to the supplier.
How SQL is used
SQL compares the supplier invoice with the approved order and accepted warehouse receipts, then records the reason and reviewer responsible for every held case.
Evidence retained
The retained DuckDB build reconciles 2,075 invoice cases and preserves the calculation, question, response, rematch and final decision for INV-000004.
Order-to-Delivery Metric Dictionary
Finance and Operations calculate on-time delivery differently when orders are split or partially delivered.
How SQL is used
The approved delivery definition is implemented in SQL at order level and matched to the DAX and Excel versions.
Evidence retained
Known delivery cases and one two-line split order prove how the denominator and completion date are assigned.
Why five reports show different on-time delivery rates
SQL, Power BI, Excel and the management pack publish different on-time-delivery percentages.
How SQL is used
Independent SQL recalculations reproduce each treatment and bridge every result to the approved definition.
Evidence retained
Row-level inputs, numerators, denominators and an exact numerical bridge are retained.
Reporting Data Issue and Retest Register
A missing product code removes a supplier category from the monthly report.
How SQL is used
The retest query confirms that the corrected reference value restores the affected rows and total.
Evidence retained
The issue history records the original failure, mapping change, second-analyst retest and whether the report was published or held.
Road Collision Trends and Engineering Review
A local road-safety team needs to decide which reported collision patterns merit a highways site inspection.
How SQL is used
SQL joins final collision, vehicle and casualty records, calculates adjusted severe-casualty trends and orders one-kilometre review cells by visible criteria.
Evidence retained
The full DfT population, validation results, inspection shortlist and one-collision trace are retained.
Cycle Hire Demand and Service Planning
A cycle-hire service planner must prepare for short-term station demand, but completed journeys alone do not reveal bikes that customers wanted and could not hire. The planner needs a forecast, a visible range and a clear account of where the model performs poorly.
How SQL is used
SQL supports the model, calculation or review route used to answer this decision: Which selected stations warrant an operational check before a planner changes a rebalancing or staffing plan?
Evidence retained
A reproducible Python and DuckDB build across 356,057 journeys used in the study and 41,460 model-ready station-hours, with three forecast horizons, rolling-origin evaluation, fitted models and one station-day trace.
Working boundary
What still needs a business rule or reporting owner
SQL can make a rule repeatable and a total traceable. It cannot decide the business meaning of an order, delivery or financial measure without the people responsible for that process. The cited build executes in DuckDB; the separate T-SQL deployment package is retained and linted, but remote SQL Server execution is not claimed.
Bring one disputed report total and the tables or extracts used to produce it. The first useful step is usually to state the grain and trace one record.
View the Analytics Engineering solutionDiscuss this capabilityReturn to all capabilities