Two Dashboards, Two Numbers
Finance and growth disagree about revenue. Both queries are correct. This is almost always a governance failure wearing the costume of a bug.
Who needs this, what one row is, and why the obvious build breaks
Every lesson starts from the consumer, because designing from the source outward is this domain's characteristic mistake.
Two teams report yesterday's revenue as 1,245,892 and 1,318,440 from the same warehouse, and both SQL statements are correct. Which one is wrong?
Anyone who has to reconcile two reports before a decision: a finance team closing a period, an executive receiving both figures in the same week, a board pack, an experiment readout where the effect size is smaller than the gap between the two definitions.
The unit here is the metric definition, not the row. Both queries usually run against the same fact table at the same grain; what differs is the filter set, the date attribution and the measure expression layered on top (The Metrics Layer).
Treat it as a bug and diff the two queries. Someone forgot a WHERE clause; find it, fix it, close the ticket. This is the right first move often enough that it becomes a reflex, and it works whenever one of the two queries is genuinely mistaken.
The diff shows both queries are defensible. Finance excludes orders that were refunded after the period closed; growth counts them because the order happened. Neither team is wrong about their own question (Fact Tables).
- The diff shows both queries are defensible. Finance excludes orders that were refunded after the period closed; growth counts them because the order happened. Neither team is wrong about their own question (Fact Tables).
- The gap is date attribution: one team dates revenue by order timestamp, the other by payment capture. For subscriptions those are different months, and both are correct accounting practice in some jurisdiction (Event vs Snapshot Modeling).
- One query filters
status != 'test'and the other does not, because internal test accounts were flagged in the CRM after the fact table was built and nobody backfilled the flag. - Currency conversion is applied at the rate on the order date in one model and the rate at month end in the other. The two are the same number in one currency and different in the reporting currency.
- The two teams read different models: one reads
fct_orders, the other a mart that was built before a status category existed and whoseELSEbranch has been quietly absorbing it ever since (Enum Evolution: The New Value That Broke Old Clients). - Both dashboards are correct and *both* are wrong, because the upstream model double-counts a small category — which is invisible precisely because the two numbers being different focused everyone on the difference (Duplicate Rows).
What is actually happening
- A metric is an expression plus a filter set plus a date attribution rule plus an exclusion list. Four independent choices, each defensible in isolation, produce a large space of "revenue" — and the word itself carries none of them (The Metrics Layer).
- When definitions are not centralised, each dashboard author reimplements those four choices from their own understanding of the business. That is not carelessness: it is the only thing they can do, and their understanding is genuinely different because their question is different.
- The technical symptom appears late. The divergence is created the moment two people write the same word to mean two things, and it stays invisible until both numbers land in front of the same person (Data Ownership).
- This is why "just diff the SQL" resolves so few of these incidents. The diff is real and both sides of it are correct; the missing artefact is a decision about which definition the organisation means by default (Data Governance).
- Semantics drift without any schema changing. A
statusenum gaining a value, a country being reassigned to a region, a product moved between categories — all leave every type check passing and every dashboard reporting something slightly different from last quarter (Semantic Changes). - The blast radius grows superlinearly with dashboards. Twenty dashboards implementing one metric produce up to twenty definitions, and reconciling them is quadratic work that nobody schedules (Data Engineering Anti-Patterns).
Two correct queries
Start with the artefact rather than the argument. Below are the two statements behind the two figures, reduced to the parts that differ. Neither contains an error. Every clause in each is a deliberate choice that somebody could defend in a meeting, and the two sets of choices are simply not the same.
Read them as four independent decisions rather than as two queries. The measure expression differs (gross versus net of refunds). The date attribution differs (order timestamp versus payment capture). The exclusion set differs (test accounts filtered or not). The status filter differs. Any one of the four would produce a gap; all four together produce a gap that nobody can decompose by looking at the totals.
This is why the first useful move in a metric mismatch is not to diff the SQL but to decompose the difference. Compute both figures grouped by the dimension you suspect, and the axis of divergence usually announces itself: a gap concentrated in one status, one country or one week is a filter problem, while a gap smeared evenly across everything is a measure problem.
1-- Finance: net revenue, recognised on payment capture, internal excluded.2SELECT SUM(o.amount - COALESCE(r.refund_amount, 0)) AS revenue3FROM fct_orders o4LEFT JOIN fct_refunds r ON r.order_id = o.order_id5WHERE o.captured_at >= DATE '2026-03-01'6 AND o.captured_at < DATE '2026-04-01'7 AND o.status = 'completed'8 AND o.account_type <> 'internal';9 10-- Growth: gross bookings, attributed to when the order happened.11SELECT SUM(o.amount) AS revenue12FROM fct_orders o13WHERE o.ordered_at >= DATE '2026-03-01'14 AND o.ordered_at < DATE '2026-04-01'15 AND o.status IN ('completed', 'pending_fulfilment');16 17-- Decompose the gap before arguing about it.18SELECT o.status,19 o.account_type,20 DATE_TRUNC('week', o.ordered_at) AS week,21 SUM(o.amount) AS gross,22 SUM(o.amount - COALESCE(r.refund_amount, 0)) AS net,23 COUNT(*) AS orders24FROM fct_orders o25LEFT JOIN fct_refunds r ON r.order_id = o.order_id26WHERE o.ordered_at >= DATE '2026-03-01' AND o.ordered_at < DATE '2026-04-01'27GROUP BY 1, 2, 328ORDER BY 1, 2, 3;The third query is the one that ends the argument. A gap that lives entirely in pending_fulfilment is an attribution disagreement; a gap spread evenly across every group is the refund netting; a gap confined to internal accounts is an exclusion list nobody wrote down.
The axes along which one word becomes two numbers
The same handful of divergences account for most metric mismatches, and they recur across companies because they follow from the structure of business processes rather than from anyone's mistake. An order and its payment are separate events; a refund happens after the fact; currencies move; some customers are not customers.
The right-hand column below is the one that matters. In almost every row, both definitions have a legitimate owner and a legitimate use — which is why the resolution is a decision about defaults and names, not a bug fix. When a row genuinely has a wrong side, it is usually the exclusions row, because an exclusion that nobody can name a reason for is the one case where the argument settles itself.
Where both are needed, publish both under different names. gross_bookings and net_revenue sitting side by side in a metrics layer end the discussion permanently; one metric called revenue that means whichever the author had in mind restarts it every quarter (The Metrics Layer).
| Axis of divergence | One defensible definition | The other | Who normally decides |
|---|---|---|---|
| Measure expression | Gross: the amount the customer committed to. | Net: gross minus refunds, discounts and chargebacks. | Finance owns net. Growth legitimately needs gross. Both should exist, with different names. |
| Date attribution | The event date — when the order was placed. | The recognition date — when payment was captured, or when the service was delivered. | Accounting policy decides recognition; product analytics needs event date. This is the axis that produces the largest gaps in subscription businesses. |
| Status filter | Completed orders only. | Completed plus anything expected to complete. | Depends on the question. Forecasting wants pipeline; reporting does not. Never leave it implicit. |
| Exclusions | Internal, test and employee accounts removed. | Everything counted, because the flag was added later and history was never backfilled. | The one axis with a clear right answer: exclusions must be defined once and applied consistently, including retroactively. |
| Currency conversion | Rate on the transaction date. | Rate at period end, or a fixed budget rate. | Finance, always. The choice is an accounting policy and engineering has no standing in it. |
| Grain of the source | One row per order. | One row per order line, or per invoice, or per subscription period. | The modelling team, and the mismatch here is the one most likely to be a genuine bug rather than a definition (Grain: What Does One Row Represent?). |
| Time zone | The warehouse's UTC day. | The customer's local day, or the company's reporting time zone. | Whoever owns the report. A day boundary shifted by hours moves a measurable amount of revenue between periods and reconciles to nothing. |
Seven axes, and a mismatch is usually two or three of them at once. That compounding is why totals cannot be reconciled by inspection and why the decomposition query above is the first thing to run.
Fixing it is a governance change, not a code change
The engineering instinct is to pick the technically cleaner definition and ship it. That reliably fails, because the team whose figure changed did not agree to the change, was not consulted, and has a spreadsheet. Within a month the platform has one governed metric and one shadow metric, which is the state you started in with worse morale.
What actually resolves it is deciding where the definition lives and who owns it. Those are two separate questions and the second one is harder. The owner has to be someone whose job includes being accountable for the number — usually a finance or business lead — because an engineer asked to adjudicate between two correct definitions will either guess or escalate, and guessing is worse (Who Owns Data Quality).
The options below are not a ladder from bad to good. A small team with three dashboards and one analyst genuinely does not need a semantic layer, and installing one is how a two-person data team spends a quarter on infrastructure instead of answers. The criterion is the number of independent people who write the word "revenue" into a query.
How many independent authors implement this metric today, and who is accountable for what it means?
when One or two analysts, few dashboards, everyone talks daily, and divergence is caught in conversation before it reaches anyone senior.
cost Zero to build and quadratic to reconcile. It stops working at the exact moment the team grows, and the failure is discovered by an executive rather than by the team.
when The metric can be pre-computed as a column on a model everyone reads — a net_revenue column on fct_orders rather than an expression in each dashboard.
cost Locks in the filter set at model-build time, so a consumer needing a different exclusion has to fork the model. Cheap, effective, and inflexible in a way that is fine until it is not (Model Layering).
when Many dashboards, several teams, metrics that combine differently per query, and a real need to slice one definition by arbitrary dimensions.
cost A new component to operate and version, a compilation step in every query path, and only as much governance as its adoption rate. A layer half the dashboards bypass governs half a company (The Metrics Layer).
when The organisation has standardised on one BI tool and every consumer goes through it, including the ad-hoc analysts.
cost The definition becomes a property of a vendor product, invisible to data tests, invisible to lineage, and not portable if the tool is replaced. Convenient and quietly locking.
when Both definitions have a genuine owner and a genuine use — gross and net, booked and recognised.
cost Consumers must now choose, and some will choose wrongly. Mitigated by documentation and a stated default; not mitigated by deleting one of them, which produces a spreadsheet.
Diff the two queries, decide the shorter one is cleaner, update the other dashboard, close the ticket. The numbers now match.
Decompose the gap by status, account type and week to identify which axes are in play. Write both definitions down in full. Take them to the person accountable for the number. Record the decision with an owner and an effective date, implement it in one place, add a parity test, and announce the restatement to everyone who quoted the old figure.
The first approach makes the symptom go away without changing the condition that produced it: two people can still write the same word to mean two things tomorrow. The second changes who is allowed to define the word, which is the only intervention that prevents recurrence — and it also surfaces the case where both figures were wrong, which the diff-and-fix path structurally cannot find.
How to build it
Most important first.
- Define each metric once, in a layer both dashboards consume, with the four choices written explicitly: measure expression, filter set, date attribution, exclusions (The Metrics Layer).
- Name an owner for each metric who is a business stakeholder rather than an engineer. The engineering team can implement any definition; it cannot adjudicate between two correct ones (Who Owns Data Quality, Data Ownership).
- Where two definitions are both genuinely needed — gross and net, booked and recognised — give them two different names and publish both. The failure is not having two definitions; it is having two definitions with one name.
- Record an effective date on every definition, so a chart that crosses a definition change can draw the boundary rather than a fake trend (Semantic Changes).
- Make the metrics layer the path of least resistance. A governance rule that dashboards must use it, without the layer being easier than not using it, produces documented non-compliance rather than compliance.
- Add a parity test: for a closed period, assert that every dashboard claiming a given metric returns the same value. It converts a slow social discovery into a fast automated one (Data Tests).
What this actually promises
Naming the guarantee you do not have is worth more than naming the one you do — everything downstream inherits the weakest promise in the chain.
- SQL guarantees that each query returns what it asked for. That guarantee is complete, it is honoured, and it is the reason the incident is confusing — both parties have a correct result in hand.
- A metrics layer guarantees that consumers *going through it* compute the metric identically. It guarantees nothing about the ones that bypass it, which is why partial adoption is the dangerous state.
- Nothing guarantees that a metric's meaning is stable over time. Definitions change when the business changes, and the only defence is versioning the definition rather than assuming permanence.
- A data contract can guarantee the shape and nullability of an upstream field. It cannot guarantee that the field still means what the consuming metric assumes (Data Contracts).
Can I trust it?
A green pipeline is evidence that code ran. These four fields are the evidence that the data is right.
- The check is a cross-dashboard parity test: for a closed period, compute each published metric through every path that claims to produce it and assert equality. It catches definitional drift at the moment it is introduced rather than at the moment an executive notices.
- It misses metrics that are only computed in one place — which is most of the long tail — and any case where both paths share the wrong upstream model, since they will agree perfectly on a wrong answer.
- Pair it with reconciliation to the source, because parity and correctness are independent: two dashboards agreeing means they share an upstream, which is exactly what you would expect if that upstream is broken (Reconciliation).
- Freshness rarely causes a metric mismatch, but it is often blamed for one, because "one of them must be stale" is the cheapest hypothesis available. Rule it out first — compare the newest record in each source dataset — precisely so that it stops absorbing the discussion.
- When freshness genuinely is the cause, the signature is different: the two figures converge if you re-run the comparison later. A definitional gap does not converge, ever, which is the fastest discriminator between the two (Stale Dashboards).
- A metrics layer adds no freshness of its own if it compiles to SQL against the same models. If it materialises results, it becomes another dataset with its own lag and belongs on the freshness dashboard like any other.
- When a definition changes, publish the change with an effective date and restate history explicitly or not at all. Silently restating history makes every previously quoted figure wrong; silently *not* restating makes every trend chart lie at the boundary.
- Adding a new enum value upstream is a metric change even though it is a backward-compatible schema change. Every
CASEexpression with anELSEbranch will absorb it without complaint (Enum Evolution: The New Value That Broke Old Clients, Breaking Schema Changes). - Metric definitions accumulate exceptions — this customer excluded, that region handled specially. Each is reasonable and the aggregate is unreadable. Periodically ask which exceptions still have a reason.
- Recovery here is social before it is technical. Convene the two owners, write down both definitions, and get a decision about which is the default. Implementing it afterwards is the easy part.
- Once the default is chosen, backfill the metrics layer and restate the affected range with a visible note. A restatement that is not announced generates a second incident when someone compares old slides to new dashboards (Planning a Backfill).
- Keep both definitions if both are genuinely used. Deleting the loser generates a shadow implementation in a spreadsheet within a month, which is strictly worse than a named second metric.
What can go wrong
- The engineering team picks a winner on technical grounds and the business rejects the result, so the "fixed" dashboard is quietly abandoned and a spreadsheet takes over.
- A metrics layer is introduced and adopted by the dashboards that were already consistent, leaving every divergent one untouched.
- The parity test compares totals only, so two definitions that differ by a small category pass while the underlying disagreement persists.
- Both numbers turn out to be wrong in the same direction, and the incident closes when they are made equal (The Pipeline Succeeded. The Data Is Wrong.).
- The definition is documented in a wiki page that no query reads, so the documentation and the implementation drift apart from the first week.
- "One of the queries has a bug." Sometimes. More often both are correct implementations of different questions, and looking for a bug delays the actual resolution by a week.
- "The data is wrong." The data is usually fine. The word is wrong: it is doing double duty for two different quantities, and no amount of pipeline work fixes a word.
- "A metrics layer would prevent this." It prevents *re-implementation*. It does not prevent two teams from genuinely needing two definitions, and pretending otherwise produces a layer that half the company bypasses.
- "Once they agree, we are done." Agreement decays. Without an owner, an effective date and a test, the same divergence reappears the next time somebody builds a dashboard in a hurry.
- "The bigger number is the optimistic one, so the smaller is right." Conservatism is not accuracy. The smaller figure is just as likely to be excluding something it should count.
- Where a metric is used in external reporting, its definition is a controlled artefact: changes need approval, an effective date and a restatement policy. Treating it like ordinary code is how a reporting error becomes a compliance event (Data Governance).
- Exclusion lists — test accounts, internal orders, specific customers — often encode commercially sensitive judgements. They belong in a reviewed definition, not in an anonymous
WHEREclause in a dashboard.
Operating it
- A metric registry showing each metric, its owner, its definition, its effective date, and every dashboard that consumes it (The Data Catalog).
- A parity panel: one row per metric, one column per consuming path, the value for the most recent closed period, and the delta highlighted (The Data Quality Dashboard).
- Lineage from each dashboard field back to the model column, so a dashboard that implements its own logic shows up as an edge that stops early (Column-Level Lineage).
- The count of dashboards that bypass the metrics layer, tracked over time. It is the only honest measure of whether the governance decision took.
- At 10x dashboards the problem changes character. With ten dashboards the mismatches are discovered socially; with a hundred they are discovered by whoever happens to open two of them, which is to say randomly and late.
- At 10x metrics, the registry stops being optional. A definition that is not machine-readable is not enforceable, and a wiki page cannot be tested.
- Consumer growth, not data growth, drives this problem. A hundred-fold increase in rows changes nothing about it; a hundred-fold increase in people who use the word "revenue" changes everything (Who Actually Consumes This Data).
- A metrics layer costs an extra compilation step and, if it materialises, another dataset to store and refresh. Both are small next to the recurring cost of reconciling numbers by hand every quarter (Compute Waste).
- Parity tests cost one computation per metric per consuming path per run. Restrict them to metrics that leave the team and to closed periods; running them continuously over open periods produces noise and no information.
- The dominant cost is organisational: the meeting where two definitions are argued to a conclusion. It is paid once per metric, or repeatedly forever.
- Centralising definitions slows down dashboard authors, who now have to request a change rather than write a filter. That friction is the mechanism by which the layer works, and it is also the reason people route around it.
- Publishing two named metrics instead of one is honest and it confuses newcomers. The mitigation is documentation that says which one to use by default, not the deletion of the other.
- Parity tests turn a silent disagreement into a failing check that somebody must now own. That is the correct trade and it does add work to a queue that is already full.
Where this applies
Almost nothing here is universal. These labels say what each claim is specific to, and where a different engine, format, warehouse or scale would differ.
- ORG-SPECIFICThis is a coordination problem, not a technical one. In a team of three analysts who talk daily it barely exists; above roughly two independent reporting teams it is inevitable, and the fix is a named owner rather than a tool.
- GENERALThe four axes along which definitions diverge — measure expression, filter set, date attribution, exclusions — are the same in every stack. Which of them bites first depends on the business: subscriptions diverge on attribution, marketplaces on exclusions.
- TOOL-SPECIFICSemantic layers differ in whether they compile to SQL at query time or materialise results, and in whether the BI tool can bypass them. A layer the dashboard tool can ignore governs nothing, whatever the vendor documentation calls it.
Where the depth lives
This domain teaches how data moves and how you know it arrived intact. It hands the rest off by name.
- — DevOps / Production Engineering owns the change-management half of this: how a definition change is reviewed, versioned, promoted through environments and announced, which is the same discipline applied to a different artefact.