skip to content

Two dashboards report different revenue for the same month — how do you diagnose and fix it?

level: seniorimportance: must knowfreq 62%

answer

  1. compare the two definitions before judging
  2. filters, grain, date, currency, dimension version
  3. a join to a second fact fans rows out
  4. the shape of the delta names the cause
  5. one owner, one definition, then a test

basics

~20 s

Put the two queries side by side and find where they differ: filters, join grain, which date column, currency handling, and which dimension version. Then agree one owner and one definition, migrate both dashboards to it, and add a reconciliation test.

solid answer

~50 s

Diagnose before you arbitrate. Pull the SQL behind both numbers and compare five things: **filters** (cancelled orders, internal test accounts, one subsidiary included), **grain and fan-out** (one query joins to a second fact and multiplies the amount across the join), **which date** (order date vs ship date vs invoice date, and which timezone the day boundary uses), **currency** (converted at transaction rate or today's rate), and **dimension version** (one query groups by the current attribute, the other by the value as of the transaction). Then reconcile numerically — join the two results month by month and look at the deltas, because the shape of the gap usually names the cause: a constant few percent smells like a filter, a large multiple smells like a fan-out. The fix is organisational, not technical: one owner decides the definition, it is written once in the semantic layer or a certified mart model, both dashboards point at it, and a test asserts they still agree.

code

sql · 12 lines
sql
-- A: revenue at the order-line grain
SELECT d.month_start, SUM(f.extended_price) AS revenue
FROM fct_order_line f
JOIN dim_date d ON d.date_key = f.order_date_key
GROUP BY d.month_start;

-- B: same metric name, joined to shipments -> one row per shipment
SELECT d.month_start, SUM(f.extended_price) AS revenue
FROM fct_order_line f
JOIN dim_date d     ON d.date_key = f.order_date_key
JOIN fct_shipment s ON s.order_line_key = f.order_line_key
GROUP BY d.month_start;

go deeper

for a junior

Know that identical-looking metrics can differ because of filters or which date column is used, and that the first step is to look at the actual SQL behind each number rather than guessing.

for a middle

Walk the diagnostic list — filters, join fan-out, date column and timezone, currency conversion, dimension version — and explain concretely why joining a fact to a second fact inflates a sum.

for a senior

Demonstrate that you reconcile numerically and read the shape of the delta to narrow the cause, then drive the durable fix: a named owner, one definition, migrated consumers, and a test that fails on drift.

for a principal

Own the case where both definitions are legitimate: name them distinctly, publish both, document the relationship, and resist forcing a single number onto two real business questions — that only hides the second definition in a spreadsheet.

## Do not arbitrate before you diagnose The worst answer to this question is "we'd decide which one is right." Both numbers are usually right *for the definition each one implements*. The work is finding which definitions differ, and that is a mechanical comparison, not a judgment call. Get the SQL behind both numbers first. Every BI tool can show the query it issued. If a number's SQL cannot be recovered, that alone is the finding. ## The five usual causes **Filters.** The commonest and dullest cause. One query excludes cancelled orders and the other does not; one strips internal test accounts, refunds, intercompany transfers, or a newly-acquired subsidiary and the other does not. Symptom: a stable, smallish percentage gap in every period. **Grain and fan-out.** One query joins the order-line fact to a second fact — shipments, payments, allocations — and then sums an order-line column. The join duplicates each order-line row once per shipment, so the amount is counted multiple times: ```sql -- Correct: sum at the fact's own grain SELECT SUM(f.extended_price) FROM fct_order_line f; -- Inflated: one row per shipment, price counted per shipment SELECT SUM(f.extended_price) FROM fct_order_line f JOIN fct_shipment s ON s.order_line_key = f.order_line_key; ``` Symptom: one number is a large, irregular multiple of the other, and the ratio moves with how many child rows exist. **Which date, and whose midnight.** Revenue "for March" can mean orders placed in March, shipments made in March, or invoices issued in March. Even with the same column, a UTC day boundary and a local one move transactions across month ends. Symptom: month totals differ but the annual totals nearly match — value moved between periods rather than appearing or vanishing. **Currency and rates.** One query converts at the rate on the transaction date, the other at today's rate or the month-end rate. Symptom: the gap tracks FX movement and is concentrated in non-domestic regions. **Dimension version.** One query attributes a sale to the customer's *current* segment, the other to the segment recorded at transaction time. Both are legitimate; they are different questions. Symptom: totals match but every breakdown disagrees — a strong signal, because it rules out the filter and fan-out causes immediately. ## Reconcile numerically Don't eyeball two dashboards. Materialise both definitions as queries and full-outer-join them by period, so the deltas are visible and the shape of the gap points at the cause: ```sql SELECT COALESCE(a.month_start, b.month_start) AS month_start, a.revenue AS defn_a, b.revenue AS defn_b, a.revenue - b.revenue AS delta FROM revenue_defn_a a FULL OUTER JOIN revenue_defn_b b ON a.month_start = b.month_start ORDER BY 1; ``` If the delta is a constant ratio, look at filters. If it is a large multiple, look for a fan-out join. If totals agree over a year but not by month, look at date columns and timezones. If totals agree but breakdowns don't, look at dimension versioning. Working from the shape of the delta usually gets you to the cause in one pass instead of five. ## The fix is a definition, not a patch Once you know why they differ, patching the loser's SQL solves this incident and nothing else — a third dashboard will appear next quarter. The durable fix has four parts: 1. **One owner.** A named business owner decides what "revenue" means for reporting, and a modelling owner implements it. Without this, the technical fix has no authority behind it. 2. **One definition, in one place.** Written as a metric in the semantic layer, or as a certified model in the mart if there is no layer. Everything else references it. 3. **Migrate the consumers.** Both dashboards point at the definition; the ad-hoc SQL is deleted, not left running in parallel. 4. **A test that fails when it drifts.** A scheduled reconciliation query asserting the certified metric matches the finance system of record, or that two derived reports still agree, catches the next divergence before a meeting does. ## When both definitions are legitimate Sometimes finance genuinely needs recognised revenue on the accounting calendar and the growth team genuinely needs booked revenue on the order date. The mistake is calling both `revenue`. Name them distinctly — `recognised_revenue` and `booked_revenue` — define both in the layer, and document the expected relationship between them. Forcing a single number where the business truly has two just moves the divergence into a spreadsheet where nobody can see it. ## In an interview Lead with the diagnostic checklist, mention that the shape of the delta narrows the cause, and finish on ownership. Candidates who only give the technical causes sound like debuggers; candidates who only give governance sound like they have never opened the SQL.

  • The two monthly numbers differ but the annual totals match almost exactly. What does that tell you?
    Value is moving between periods rather than appearing or disappearing, so it is a date-boundary problem, not a filter. Check which date column each query uses — order, ship, or invoice date — and whether the day boundary is computed in UTC or a local timezone. Late-arriving corrections restating prior months produce the same signature.
  • Both dashboards agree on the company total but disagree on every regional breakdown. What is the likely cause?
    They are using different versions of the dimension attribute. One attributes each sale to the customer's region as it is today, the other to the region recorded when the sale happened. Totals are unaffected because every row is still counted once — only its bucket changed. Both are valid questions; the model has to say which one the metric means.
  • Finance and the growth team genuinely need different revenue figures. How do you handle that?
    Stop calling both of them revenue. Define each with a distinct name — recognised versus booked — publish both in the semantic layer, and document how they relate and why they differ. Forcing one number onto two real business questions doesn't resolve the conflict, it just pushes the second definition into an ungoverned spreadsheet.
  • How do you stop the next divergence rather than just fixing this one?
    Make the certified definition the only easy path: one metric in the semantic layer, dashboards referencing it rather than hand-written SQL, and a scheduled reconciliation against the system of record that alerts when it drifts. Then remove the old queries. Leaving the superseded SQL running in parallel guarantees the argument recurs.

saying these in an interview costs you the question

  • Picks a winner without comparing the two queries
  • Blames stale data before checking filters and joins
  • Misses that a join to a second fact duplicates rows
  • Patches one dashboard's SQL and calls it fixed
  • Assumes matching totals mean matching definitions

context

resolving links…