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?
Inspect the project evidenceIn 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.
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 retains executable SQL, schema, tests and outputs. Release checks compare row counts, key uniqueness and financial totals; reconciliation projects also retain the exact orders, receipts and invoices that do not agree.
A dashboard image cannot provide that assurance. The useful evidence is the query result, the failed-row output and the record trace showing how a source key contributed to the published measure.
Readable project evidence
Executed mart and release results
From SQL-backed Reporting Foundation. 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.
Evidence type: Rendered from retained project records
Used in public projects
How SQL shapes and checks records in published builds
These links cover the complete set of current projects that use this capability. Each project states its data, native build, validation and limitation.
SQL-backed Reporting Foundation
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.
Retained proof
Executed DuckDB outputs, source-to-report lineage and 18 passing release checks are retained.
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.
Retained proof
The retained output shows supported items, payment holds and the team responsible for each difference.
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.
Retained proof
Known delivery cases and one two-line split order prove how the denominator and completion date are assigned.
On-time Delivery Metric Reconciliation
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.
Retained proof
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.
Retained proof
The issue history records the original failure, mapping change, second-analyst retest and release decision.
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.
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.
Discuss this capabilityReturn to all capabilities