Finance and marketing report different revenue for the same month from the same warehouse. Where do you look and what would have prevented it?

What is being tested

Whether you recognise that this is almost always a definitional problem rather than a technical one, and whether you know the modelling practice that prevents it.

Where to look, in order

1. The definitions, not the data. Nine times in ten the query is correct and the question is different:

• Are returns included as negative revenue, excluded, or netted in a later period?
• Is it gross or net of discounts, tax, shipping?
• Which date — transaction date, settlement date, shipment date, or fiscal period? A month boundary makes these differ materially.
• Which channel scope — does e-commerce include marketplace sales? Does a fulfilment centre count as a store?
• Cancelled and pending orders — counted at order time or at fulfilment?

2. The grain. If one query joins the fact table to a dimension that fans out — a product that belongs to multiple categories, an order with multiple shipments — measures are double-counted. This is the most common technical cause and it produces numbers that are wrong by a plausible-looking amount, which is why it survives review.

3. Filters and late data. One report may exclude test accounts or internal orders. One may have run before a late-arriving batch landed. Both are real and both are fixed by making the run's data watermark explicit.

4. Slowly changing dimensions. If a product was recategorised and the dimension is type 1 (overwrite), historical reports change retroactively. Two reports run on different days over the same period will disagree, correctly, according to their own logic.

What would have prevented it

Conformed dimensions and a single certified metric definition. Revenue is defined once, in one place, with its inclusions and exclusions stated, and both reports use that definition rather than each writing their own SQL. This is the central discipline of dimensional modelling and it exists precisely because this failure is otherwise inevitable.

Supporting mechanisms:

• Declare the grain of every fact table and enforce it in tests. Undeclared grain is how fan-out double-counting enters.
• Type 2 dimensions where history matters, so a recategorisation does not rewrite the past.
• A semantic layer so business logic lives in one versioned, tested place rather than in individual queries and dashboards.
• Lineage, so the question "where did this number come from" has a mechanical answer.
• Certified versus exploratory datasets, clearly labelled, so people know which numbers carry a guarantee.

Why this is architecture, not analytics

At retail scale — joining point-of-sale, e-commerce, inventory movements, supply-chain events and store attributes from dozens of systems with different grains and update frequencies — the hard part is never storage or query speed. It is definitional. Those questions are resolved once, in the conformed dimensions, or they are resolved differently by every analyst, two reports disagree, and trust in the entire platform collapses. Recovering that trust costs far more than the modelling would have.