ReviewI find out why your number doesn’t add up.
First we agree on what the number means. Then I test whether the data holds it up. If it doesn’t pass, I stop and say so in writing.
One pagethat tells you which number to use.
Five numbers, each with its verdict. Example figures.
| Number | Official source | Model | Diff. | Verdict |
|---|---|---|---|---|
| Net sales | 412,000,000 | 412,000,000 | 0 | ✓Use |
| Cost | 330,000,000 | 331,940,000 | 0.59% | !With a note |
| Margin | 19.90% | 19.43% | −0.47 pp | !With a note |
| Customers who bought | 284 | 284 | 0 | ✓Use |
| Stock value | — | — | 12% with no cost | ✗Not yet |
ConfirmedSales and customers match accounting.
OpenCost: October invoices dated in September. Owner: Accounting.
Not testedReturns from other channels.
- ✓Each number with its verdict
- ✓What was confirmed, what’s open and what wasn’t tested
- ✓The SQL to repeat every finding
- ✓What to fix first, and who owns it
What I don’t do,and what I do.
Believe it untested
I test it with data profiling
Model even if they don’t match
Primary keys and orphan rows
Silently delete rows
I flag it and ask
By hand, or in Power Query
At the source, with SQL
Hide it
A visible data note
The dictionary says one thing.The data, another.
Before any test, I look at what really arrived.
| Where | What it says | What arrived | |
|---|---|---|---|
fact_orders | One order per row | Unique key and 0 orphan rows | ✓ |
dim_time_slot | Time slot | 17 impossible hours, like 02:95 | ✗ |
dim_kitchen | Kitchens | KIT-YB-001 is called “Lekki Phase 1” and sits in Yaba | ✗ |
dim_rider | Assigned kitchen | Matches 25.3% of orders: what chance gives | ✗ |
temperature | °C of the food | 90.3% above 100 °C. Max: 1,000 °C | ✗ |
profit | Value − delivery cost | 0% of the rows comply | ✗ |
The official file says it passed everything. It checks that the table exists and the keys match. Not whether 1,000 °C makes sense. That’s why I look before I test.
Five questionsbefore the first chart.
They run twice: when the data arrives and before publishing. Hover or pick one.
What I look forThat the table arrived today, with rows and the agreed columns.
| table | last load | rows |
|---|---|---|
| raw_sales | today 06:10 | 41,200 |
| raw_stores | 9 days ago | 38 |
| raw_costs | today 06:12 | 0 |
An empty table passes every other test. That’s why this one goes first.
-- 0 rows or an old load: I stop
SELECT table_name, total_rows,
storage_last_modified_time
FROM `region-us`.INFORMATION_SCHEMA.TABLE_STORAGE
WHERE table_schema = 'raw';In dbtsource freshness
What I look forOne row per sale line. If a sale shows up twice, everything inflates.
| sale_id | line_no | amount |
|---|---|---|
| 1041 | 1 | 5,000 |
| 1041 | 1 | 5,000 |
| 1041 | 2 | 5,000 |
Two coffees on one receipt aren’t a duplicate: the line tells them apart.
-- 0 rows = it passes
SELECT sale_id, line_no, COUNT(*) AS n
FROM stg_sales
GROUP BY sale_id, line_no
HAVING COUNT(*) > 1
OR sale_id IS NULL OR line_no IS NULL;In dbtunique + not_null
What I look forNo empty or future dates, discounts between 0 and 100, and profit = revenue − cost.
| check | rule | rows |
|---|---|---|
| sale_date | not empty | 1 |
| sale_date | not in the future | 0 |
| discount_pct | 0–100 | 2 |
| profit | revenue − cost | 0 |
Ranges are set per column: a 150% markup can be normal.
-- All 0 = it passes
SELECT COUNTIF(sale_date IS NULL) AS no_date,
COUNTIF(sale_date > CURRENT_DATE()) AS future,
COUNTIF(discount_pct NOT BETWEEN 0 AND 100) AS bad_discount,
COUNTIF(ABS(revenue - cost - profit) > 1) AS bad_profit
FROM stg_sales;In dbtnot_null + accepted_values
What I look forThat every sale points to a store that exists. If not, it’s an orphan row and the total by store won’t add up.
| store_id | rows | amount |
|---|---|---|
| S-14 | 12 | 1,240,000 |
12 sales with no store: the total by area comes up short.
-- 0 rows = it passes
SELECT f.store_id, COUNT(*) AS rows_affected,
SUM(f.amount) AS amount_affected
FROM stg_sales f
LEFT JOIN stg_stores d USING (store_id)
WHERE d.store_id IS NULL
GROUP BY f.store_id;In dbtrelationships
What I look forFor each closed month, the total equals the official source, within tolerance.
| month | source | model |
|---|---|---|
| Aug | 318.2 M | 318.2 M |
| Sep | 330.0 M | 331.9 M |
September goes over the agreed 0.1%: the reconciliation explains it.
-- 0 rows = it passes
SELECT month, s.total, m.total,
s.total - m.total AS diff
FROM gl_close s FULL JOIN mart_sales m USING (month)
WHERE month < DATE_TRUNC(CURRENT_DATE(), MONTH)
AND ABS(s.total - m.total) > 0.001 * ABS(s.total);In dbta singular test against the source
Cleaningwithout changing the meaning.
Once, at the source or in staging, with SQL. It stays in a script, and whatever leaves is noted.
| sale_id | line | updated | date | product | amount | status |
|---|---|---|---|---|---|---|
| 1041 | 1 | 10:00 | 03/10/26 | café | $ 5.000 | ENTREGADO |
| 1041 | 1 | 18:00 | 2026-10-03 | Cafe | 5000 | entregado |
| 1042 | 1 | 18:00 | 2026-10-03 | Cafe | 5000 | entregado |
| 1042 | 2 | 18:00 | 2026-10-03 | Cafe | 5000 | entregado |
| 1043 | 1 | 18:00 | — | CAFÉ | 5000 | ok |
Two versions of 1041, two coffees in 1042 and a row with no date.
| sale_id | line | date | product | amount_clp | dq_flag |
|---|---|---|---|---|---|
| 1041 | 1 | 2026-10-03 | CAFÉ | 5000 | — |
| 1042 | 1 | 2026-10-03 | CAFÉ | 5000 | — |
| 1042 | 2 | 2026-10-03 | CAFÉ | 5000 | — |
| 1043 | 1 | — | CAFÉ | 5000 | missing_date |
The row with no date isn’t deleted: it’s flagged and the owner is told. “ok” is the owner’s call.
Trust is earnedwith numbers that match.
And what doesn’t match is explained line by line. Example figures.
Difference: $1,940,000 (0.59%). Agreed tolerance: 0.10%.
What didn’t matchCost differs by $1,940,000 (0.59%).
What it affectsSeptember margin drops from 19.90% to 19.43%.
MeanwhileIt’s published with a note.
What I needAccounting to confirm the October invoices.
Owner: Accounting · Oct 15The same questions, on public data.None passed everything.
They’re competitions, not companies: they show what each question catches, not how your data is.
passed everything
Public data from Onyx Data challenges.90.3% of temperatures above 100 °C. The keys pass.
The daily fact is a sample: 22.8% of the monthly one.
Synthetic data, declared. Weighting changes the rankings.
The cleanest, but 6,221 lines were sold before the launch.
Before deciding,not after.
- 1A new source arrivesBefore anyone builds on it.
- 2Two areas bring different figuresBefore the meeting turns into an argument.
- 3An AI assistant gets connectedBefore it answers with the wrong number.
- Two areas bring different figures.
- Someone is about to decide with that number.
- Copilot or Claude will read that data.
- What’s needed is a financial audit.
- Someone wants a signature that everything is perfect.
- Nobody will decide anything with the result.
Every number has a verdict.And what didn’t match, with owner and date.
Reviewing ends when every number has a verdict and what’s missing is in writing.
- ✓Within tolerance, or accepted in writingCost: 0.59%, accepted by Accounting.
- ✓Rows flagged, not deleted1043 with no date, owner notified.
- ✓Every finding repeats with the SQLAnyone can run it again.
- ✓What I didn’t test, also in writingReturns from other channels.
Arrived?✓
Unique?0 dup.
Credible?1 flagged
Connects?0 orphan
Adds up?0.59% · accepted
With the note signed, it gets modelled. Next comes Model.





