Data engineering in the AI era · beginner · About 10 minutes
The revenue dashboard that is 17% too high
April revenue on the sales dashboard is 17.2% above finance's figure, and the board meeting is at 14:00. Find the cause in the query, not in the warehouse.
You are the analyst who owns the sales dashboard.
What happened
The sales dashboard shows April revenue of $1.84 million. Finance's month-end figure is $1.57 million. Both are built from the same closed deals, and finance's number is the one that goes to the board.
The head of sales has asked for an explanation and a corrected number before 14:00. An AI assistant wrote the dashboard query in March, and it looked right when it was reviewed.
The incident is made up. How things work in it is real, with sources at the end.
The evidence
Everything you need is here. Read it before you decide.
| Dashboard | Finance | Difference | |
|---|---|---|---|
| Revenue | $1.84M | $1.57M | +17.2% |
| Customers with a closed deal | 418 | 418 | same |
| Last data load | 06:10 today | 06:05 today | both fresh |
select
date_trunc('month', d.close_date) as month,
s.region,
sum(d.amount) as revenue
from closed_deals d
join account_segments s
on d.account_id = s.account_id
where d.close_date >= '2026-04-01'
and d.close_date < '2026-05-01'
group by 1, 2;closed_deals has 1 row per deal. account_segments has 1 row per segment an account has
ever been in, with valid_from and valid_to dates, so an account that moved from
Mid-market to Enterprise has 2 rows.
| Deal | Account | Amount | Segment rows for the account | Counted on the dashboard |
|---|---|---|---|---|
| D-1042 | ACME-18 | $42,000 | 2 | $84,000 |
| D-1091 | NOVA-07 | $31,500 | 3 | $94,500 |
| D-1144 | TRIAD-22 | $18,750 | 2 | $37,500 |
| D-1150 | KITE-03 | $9,200 | 1 | $9,200 |
Your decisions
Decision 1 of 3
Decision 1
Why is the dashboard higher than finance?
Decision 2
What do you change in the query?
Decision 3
How do you stop this coming back?
What went wrong
Every table has a grain: what 1 row stands for. closed_deals has 1 row per deal. account_segments
has 1 row per account per segment it has ever been in. Joining them on the account alone gives a row
for every pairing, so any account with segment history multiplies its deals. The sum then adds the
same money 2 or 3 times. This is called join fan-out.
Nothing about the query looks wrong. It runs, it groups by month and region, and it would be correct if each account had a single segment. That is why it passed review, and why an AI assistant wrote it with confidence: the mistake is in the data's shape, not in the SQL. A model writing SQL from table names alone cannot see how many rows each account has.
The fix keeps the grain. Choose exactly 1 segment row per deal, the one valid when the deal closed, and prove it with a test that a deal never appears twice.
Keep these
- Before you join, know each table's grain. After you join, check the grain is still what you meant.
- When a total is too high and the counts match, look for a join that duplicates rows.
- A uniqueness test on the key catches fan-out on the day it happens, whoever wrote the query.