Two dashboards report different revenue for the same month. How do you find out why, and how do you stop it happening again?

Reconcile before you theorise

The instinct is to open both pipelines and read SQL until something looks wrong. That is the slow path, because both queries usually look reasonable and the difference is in what they mean rather than in how they are written. The fast path is to make the two numbers explain themselves: pull the underlying rows behind each figure at the finest grain both can produce, full-outer-join them on the natural key, and look at what does not match.

That reconciliation immediately classifies the problem. If one side has rows the other does not, it is a filter or a join difference. If both sides have the same rows with different values, it is a definition or a currency or a units difference. If one side has the same row several times, it is fan-out. And if the row sets differ only near the month boundary, it is time. Four classes, distinguished in one query, and each has a different owner and a different fix.

Fan-out: the join that invents revenue

The mechanism worth being able to draw from memory is a join between two facts at different grains. Suppose orders have a total, and shipments record one row per parcel. Joining them on order id and summing the order total multiplies each order's revenue by its parcel count, so a report is wrong in proportion to how many orders shipped in more than one box. The query is valid, the join key is correct, and the number is nonsense.

-- Wrong: one order with three shipments contributes its total three times.
select sum(o.order_total)
from   fact_orders o
join   fact_shipments s on s.order_id = o.order_id;

-- Right: aggregate the finer fact to the coarser grain first, then join.
select sum(o.order_total)
from   fact_orders o
where  exists (select 1 from fact_shipments s where s.order_id = o.order_id);

The generalisation is that an additive measure may only be summed at the grain it was declared at. Two facts join safely through their shared dimensions, not directly to each other, and where you genuinely need both you aggregate one to the other's grain first or you accept a semi-additive measure and stop summing it. A many-to-many bridge table has the same hazard with a friendlier name: a customer in three segments appears three times, and any measure summed across that bridge is inflated unless the bridge carries an allocation weight that sums to one.

Definition drift is the commonest cause of all

Assuming both queries are grain-correct, the difference is usually that the two dashboards are measuring different things while calling them the same word. Revenue is a particularly rich example. Gross or net of refunds. Net of discounts or not. Including or excluding tax, shipping, and gift-card redemptions. Booked at order time or at fulfilment or at cash receipt. Including internal test accounts, cancelled orders, and the staff discount programme, or excluding them. In a company with more than one currency, converted at the transaction-date rate, the month-end rate, or the current rate.

None of those choices is wrong. What is wrong is that two teams each made a defensible choice, wrote it into a WHERE clause, and shipped a tile labelled "Revenue". No amount of data quality work fixes that, because the data is fine and the disagreement is semantic. This is why the first question to ask the two dashboard owners is not "which pipeline is broken" but "what does your number include", and why the answer to the discrepancy is frequently that both numbers are correct and one of them is mislabelled.

Time, which is three different columns

The second recurring cause is that a month is not a well-defined set of rows until you say which timestamp defines membership and in which zone you evaluate it. An orders fact typically carries at least an event time, an ingestion time and a last-updated time, and grouping by each produces a different March. Add a warehouse that stores timestamps in UTC while the business reports in local time, and roughly the last hour of every month moves.

Late arrival compounds it. If one dashboard is a materialised table built on the second of the month and the other queries the base fact live, then every record that arrived late is in one and not the other, and the gap grows for a few days and then stabilises. That is not a bug in either, but it does mean a certified monthly number needs a stated close policy: after which point the month is frozen, and whether corrections after that appear as restatements of the original period or as adjustments in the current one.

Making a second definition impossible rather than merely discouraged

Explaining the discrepancy is the easy half. The design question is why the metric could be written twice at all, and the answer is that both dashboards were allowed to reach the base fact tables and write their own aggregation. Every fix that survives works by removing that option.

The practical shape is a governed layer between the model and the tools: a metric defined once, in version control, with its measure, its grain, its filters, its time column and its permitted dimensions declared, and both dashboards forced to reference it by name rather than to re-derive it. Whether that is a semantic layer in the BI tool, a metrics definition in your transformation framework, or a small set of certified views is much less important than the property being bought, which is that there is exactly one place the definition can change and one place to look when someone asks what the number includes.

Around it put the cheap guards. Tests that assert grain uniqueness on every fact's declared key, so fan-out is caught by a failing build rather than by a wrong dashboard. A convention that distinguishes certified from exploratory content in the tool itself, so a reader knows which number carries a promise. And a reconciliation job that recomputes the headline figure against the source system on a schedule, because a definition that is right and a pipeline that has silently stopped loading produce the same confident tile.

The discrepancy is nearly always semantic rather than technical, and the durable fix is not correcting one query. It is making it structurally impossible for the metric to be defined in two places, and stating who owns the definition when the two teams disagree about what revenue means.