Usually neither is right, because neither was ever told what “right” means. When a dashboard and the pipeline underneath it disagree, the cause is almost always a missing definition or a step that changes the number without anyone noticing. You find it by tracing one number, for one period, through every layer until the two figures split. You fix it with a written definition and an automated test that checks it. The fix is a definition plus a test, not a new tool.
This matters more than it looks. Once people stop trusting a number, they rebuild it in their own spreadsheet, and now you have three numbers instead of two. The real cost is every meeting that starts with “which version is this?”
First, check that they are measuring the same thing
Before you debug anything, ask both sides to state in one sentence what their number counts. You will often find the disagreement is a naming problem. Finance’s “revenue” is invoiced amounts net of refunds, booked on invoice date. The dashboard’s “revenue” is order value at checkout, in the customer’s timezone, including orders later cancelled.
Both numbers can be correct. They answer different questions. If that is what you find, the fix is to give them different names and decide which one leadership looks at. No engineering required.
If they really are supposed to be the same number, move on to tracing.
How to trace a number from source to chart
Pick one metric and one short period. A single day is ideal. A month hides too much.
Then reproduce the number at every layer it passes through:
- The source system. The application database, the billing tool, the CRM. Whatever the business treats as the record of what happened.
- The raw data in your warehouse or analytics database, as the loader landed it.
- Each model or transformation between raw data and the reporting table.
- The query the dashboard runs, including any filters set in the BI tool itself.
At each step, write down two things: the total and the row count. The step where they diverge from the previous one is where your problem lives. Then narrow down to individual records. Find one order that appears in one place and not the other, and follow it.
An example, for illustration. The dashboard shows 412 orders for 3 September. The source database shows 398. The raw table in the warehouse also shows 398. The cleaned orders model shows 398. The reporting table shows 412. So the extra 14 appear in the last transformation. Looking at them, each one is an order with two shipments, and the reporting model joins orders to shipments. That took one afternoon and a few queries. Nobody needed a new tool to find it.
Two practical tips. Do the trace with someone who knows the business process, because “is this record supposed to count?” is a business question. And save every query you write along the way. They become your tests later.
The five usual causes of drift
In our experience, the split almost always comes from one of these.
1. Different definitions. Gross or net. Including tax or not. Counting trials as customers or not. One side excludes internal and test accounts, the other does not. This is the most common cause and the one that technology cannot fix on its own.
2. Time. Which date does a record belong to: created, paid, shipped, invoiced? In which timezone? An order placed at 23:30 in Rome on the last day of the month lands in the next month if the pipeline works in UTC. Month-end figures are where this shows up first.
3. Freshness and late data. The dashboard reads a table that refreshed at 06:00. The finance export was pulled at 11:00. Or a source sends records late, a refund arrives three days after the order, and nobody reprocesses the earlier period. Both numbers were right at the moment they were taken.
4. Joins that duplicate or drop rows. Like the example above: joining orders to a table with several rows per order multiplies them. The opposite also happens, an inner join to a customer table silently drops orders whose customer record is missing. Totals move and no error is raised.
5. Logic hidden in the dashboard layer. A filter set in the BI tool, a calculated field someone added last year, a cached extract that stopped refreshing. This logic is invisible from the warehouse, so the pipeline team cannot see it and the dashboard team forgot it exists.
A sixth cause appears less often but hurts more: the source itself changed. A new status value, a renamed field, a product that moved to a new billing plan. The pipeline keeps running and produces a quietly different number.
The fix: a definition plus a test
Once you know the cause, fixing that one bug is easy. The real work is making sure the next one is caught before anyone puts it on a slide.
Write the definition down. One short paragraph per metric: what counts, what is excluded, which date, which timezone, which currency, who owns it. Put it next to the SQL that computes it, in version control, so the document and the code change together. If the definition lives in a wiki and the logic lives in the BI tool, they will drift apart.
Move logic out of the dashboard. Filters and calculated fields that define a metric belong in the modelled tables, where they are versioned and testable. The dashboard should only display them.
Turn your trace into tests. The queries you wrote while tracing are the tests you need. Typical ones:
- The reporting table has exactly one row per order (a uniqueness test at the grain you expect).
- The total in the reporting table matches the total in the source for yesterday, within an agreed tolerance.
- Every order has a matching customer, or the missing ones are counted and reported.
- Each source table was refreshed within its expected window.
Tools such as dbt make these tests easy to write and run on every build, and its documentation on data tests is a good starting point. Plain SQL checks run by a scheduler work too. What matters is that a failing test reaches a person who can act on it, the same way a failing health check on a production service would.
Name an owner. Each key metric needs one person or team who decides what it means when the business changes. Without an owner, the definition goes stale and the argument comes back.
When this advice is wrong
When the difference does not matter. If the two numbers differ by a small amount and no decision would change, write down why they differ and move on. Chasing the last unit on a vanity metric is a poor use of anyone’s week.
When the source system is the legal record. For revenue, your accounting system is what the auditors and the tax office see. If the dashboard disagrees with the ledger, the dashboard is wrong by definition, and the job is to match the ledger or explain the gap.
When you have one dashboard and one analyst. If one person builds the pipeline and the chart and reads both, a formal definition catalogue is overhead. Fix the bug, add one or two tests and keep going. Formalise once a second team starts quoting the number.
When the real problem is that nobody looks. Sometimes the disagreement survives for months because neither number drives a decision. Then the right move might be to retire one of them.
FAQ
Why do my dashboard numbers not match my database?
Usually because of a difference in definition, date handling, refresh timing, joins or filters set in the BI tool. Trace one day of data through each layer and compare totals and row counts at every step to find where they split.
Should we buy a data observability tool to fix this?
Not as a first step. Those tools help once you know what to check and have many tables to watch. If you have not written down what your key metrics mean, a tool will alert you about the wrong things. Start with definitions and a handful of SQL tests.
Who should own a metric definition?
The team that makes decisions with it, usually with finance involved for anything touching revenue. The data team implements and tests the definition. The business side decides what it means.
How we approach it
This kind of problem sits right on the line between infrastructure and analysis, which is why we handle both with one team. We trace your key numbers from source to dashboard, fix where they drift, and leave the definitions and tests in your repository. If the gap is in the pipelines, that is our data platform and pipeline work. If it is in definitions and dashboards, see analytics and KPI dashboards.