L0
Landing
Retain the imported invoice, order and stock columns.
- invoices_raw
- purchase_orders_raw
- stock_movements_raw
CheckLoad count, file date and table metadata
Analytics Engineering
Turn operational records into reporting tables with a declared grain.
For reporting and data teams whose important figures depend on repeated transformations, unclear joins or datasets that have drifted apart.
Quanta Meridian builds facts and dimensions for orders, invoices, receipts and reference data, then keeps the SQL, tests and example rows that explain each reported total.
Model contractreporting.fct_sales
- Grain
- One row per invoice line
- Input records
- Invoices, customers, products and dates
- Rebuild
- Reproducible from retained transactions
- Change owner
- Reporting model owner
The wholesale mart shows how invoice, purchase and stock records become shared reporting tables. The matching project compares purchase orders, warehouse receipts and supplier invoices before payment, while the delivery projects prove one metric definition across SQL, Excel and Power BI.
When the model needs attention
The report works until somebody needs to explain or change it.
A distributor can process orders, invoices and warehouse receipts successfully while still lacking reporting tables that Finance and Operations can use consistently.
If one report joins invoice lines to order-level delivery records and another averages delivery lines, both queries may run while producing different totals. The disagreement starts with grain, not presentation.
The reporting model gives each fact a declared row meaning, uses shared customer, product and date dimensions, and keeps the tests that reconcile reported figures to the retained orders, invoices and receipts.
Architecture view
One visible model from invoice line to report.
Each layer has a specific job, a named table and a check before the next model uses it.
ContractSales.InvoiceLines
- Grain
- One row per invoice line
- Owner
- Reporting data engineer
- Freshness
- Monthly reporting cut-off
- Required key
- InvoiceLineID
L1
Staging
Standardise names, types, keys and record rules.
- stg_invoice_lines
- stg_purchase_orders
- stg_stock_movements
CheckTypes, keys and valid references
L2
Intermediate
Apply purposeful joins and reusable business logic.
- int_sales_enriched
- int_purchase_enriched
- int_stock_position
CheckJoin cardinality and exception rules
L3
Reporting
Publish facts, dimensions and agreed measures.
- fct_sales
- fct_purchase
- fct_stock_movement
CheckGrain, totals and semantic definitions
Published reporting martWholesale sales, purchasing and stock mart
Fact grain, measures, refresh owner and known limitations remain beside the model.
Invoice linesLandingStagingIntermediateReportingReport
Executed record lineage
Follow one invoice from retained arrival to reviewed exception.
This trace is generated from the published Supplier Invoice Matching and Review build. It keeps the typed records, rule result and human decision distinct.
- 01Retained arrivalbronze_invoice_header
INV-000004
Accounts Payable retains the arrived document, checksum and extracted fields as separate records.
Source document and load metadata - 02Typed comparisonsilver_invoice_line
Invoice £81 / order £75
Supplier Litware, Inc., purchase order 4 and 11 accepted outers are joined without replacing the source records.
Typed keys and accepted receipts - 03Matched factgold_line_match
PRICE_DIFFERENCE
The four-way MATCH-1.0.0 rule applies TOL-DEFAULT-GBP and retains the failed price comparison.
Versioned rule and tolerance - 04Reviewed exceptiongold_review_fact
MANUALLY_ACCEPTED_EXCEPTION
A buyer-approved price change is attached during independent rematch; the original mismatch remains recoverable.
Reviewer decision; no payment executed
- 01Define the decision and grain
- 02Register the operational tables
- 03Retain the landing data
- 04Standardise in staging
- 05Build purposeful models
- 06Publish reporting marts
- 07Test, document and own
Why the structure matters
The model makes reporting easier to check and change.
Layers and tests are useful because they connect a question about a figure to the exact record and calculation that need attention.
Rebuild the result
Run the same orders, invoices and rules through documented preparation steps instead of reconstructing logic from a finished report.
Investigate a difference
Trace a figure to the invoice line or order event, calculation, definition and check that produced it.
Change with control
See which models, reports and owners are affected before a changed file or rule is released.
Engineering method
Set the grain first, then make every transformation earn its place.
The model moves from imported transaction tables to business-shaped facts and dimensions. Each step has one purpose, a named table and a point where it can be checked.
The reporting question and row grain are agreed before invoice lines, order lines, receipts or delivery events are joined. That prevents a one-to-many relationship from multiplying a financial or service total.
Orders, invoices, receipts and reference records are typed and standardised in named stages. Shared keys and business rules are applied once before facts and dimensions are prepared for Excel or Power BI.
The retained build includes executable SQL or Python, mappings from business fields to report columns, failed records and instructions for rerunning the result after a monthly file or definition changes.
A bounded engagement
Begin with a reporting decision, not a platform shopping list.
The commercial route is deliberately concrete: isolate one disputed result, build the reporting model it requires and leave the next run inspectable by another analyst.
Start with one disputed report result
Bring the report total, the records used to calculate it and the people who prepare and review it. The first review identifies the grain, joins and ownership that need attention.
Build the smallest durable model
Retain the source records, type the fields, make purposeful joins and publish only the facts, dimensions and checks needed for the agreed reporting decision.
Leave a controlled next run
Handover includes executable transformations, failed records, lineage, test results, change notes and a named owner for the next reporting cycle.
The maintained reporting model
Reporting tables, their controls and the information needed to run them.
The reporting question, row grain and available records determine the model. Its contract, transformations, failed tests and rerun instructions remain connected to the tables another analyst uses.
Define the contract
Agree which records enter the model, what each table means and what must remain true.
The build retains operational table register and contract, declared grain and key rules and field-to-model mapping.
Build the reporting tables
Move the logic out of repeated reports and into models with one clear purpose.
The build retains landing and staging models, intermediate transformations and reporting marts and semantic definitions.
Operate the build
Leave the checks and documentation needed to rerun, investigate and change the model.
The build retains automated tests and failed records, lineage and build instructions and named ownership and known limitations.
Implementation environments
Choose tools for the reporting task, not for the label.
The same model discipline applies whether the build is compact or sits inside a larger data environment.
SQL
Use when
Relationships, repeated transformations, history or record-level traceability need a structured model.
Boundary
The model still needs an agreed grain, business definitions and checks; SQL alone does not provide them.
Python
Use when
Validation, file handling or analysis needs configuration, tests and retained run evidence.
Boundary
Python is used for a clear task with declared records and results, not as an unexplained processing layer.
Power BI
Use when
A curated model needs shared measures and an accessible way to inspect reporting detail.
Boundary
The semantic model consumes agreed data; it should not become the hidden home for every transformation.
Git and documentation
Use when
Model changes need review, history and instructions that remain beside the working code.
Boundary
Version control records a change. It does not replace testing, ownership or an agreed release process.
A finished example
Three featured builds expose the modelling decisions, not just the final report.
Each featured example has a different evidential role: declared grain, purchase-to-pay reconciliation or one invoice carried through a versioned tolerance and human review.
01
Supplier Invoice Matching and Review
Supplier invoice reviewer report
Tolerance and reviewer lineage
Shows versioned matching rules, effective tolerances, named reviewer questions, independent rematch and the retained decision for INV-000004.
The invoice asks for payment, but the purchase order and warehouse receipt must agree before the reviewer can release it.
- Invoices reviewed
- 2,075
- Payment holds
- 13
02
Purchase Order, Receipt and Invoice Reconciliation
Executed purchase item reconciliation and payment hold queue
Purchase-to-pay joins and ownership
Shows ordered reconciliation rules across 251,734 purchase items, with one final class, payment state and responsible team for every item.
A three-way reconciliation checks whether the purchase order, warehouse receipt and supplier invoice agree before an invoice is cleared for payment.
- Purchase items
- 251,734
- Source events
- 1,595,923
03
Wholesale Sales, Margin and Stock Reporting Mart
DuckDB wholesale reporting mart
Declared grain and release controls
Shows how shared sales, purchasing and stock records become tested reporting facts and dimensions with reconciliation retained beside the build.
A reporting analyst prepares the wholesaler’s May sales, margin and stock report from invoice lines, supplier orders and warehouse movements, with each figure checked before finance, sales and warehouse managers use it.
- Invoice lines
- 228,265
- Business months
- 41
Related capabilities
Model, test and publish through a connected set of practices.
SQL
SQL gives invoice lines, orders, receipts and reference data a declared grain before they become shared reporting totals.
Explore the SQL capabilityPython
Python turns reporting rules and analytical methods into repeatable runs with declared inputs, retained results and tests another analyst can inspect.
Explore the Python capabilityPower BI
Power BI connects an agreed management question to a dimensional model, explicit measures and the records needed to explain the result.
Explore the Power BI capabilityScope and limitation
Start with the result that needs to be trusted or changed.
A first review follows one figure to its source records, grain, joins, rules and current owner. That is enough to define a useful next step without sharing confidential records in the initial conversation.
Bring one report or dataset whose logic is difficult to trace and a short account of how it is refreshed today. Confidential or protected data is not needed for the first conversation.
Discuss a reporting modelReturn to all solutions