HomeAboutProjectsHow I workContact
02Data quality

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.

Data profilingSQL testsData note
00What you get

One pagethat tells you which number to use.

Five numbers, each with its verdict. Example figures.

Data note · SeptemberFor: the Monday sales meeting
NumberOfficial sourceModelDiff.Verdict
Net sales412,000,000412,000,0000✓Use
Cost330,000,000331,940,0000.59%!With a note
Margin19.90%19.43%−0.47 pp!With a note
Customers who bought2842840✓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.

What’s in itThe data note
  • ✓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
00What I do and don’t

What I don’t do,and what I do.

1The dictionary
✗I don’t

Believe it untested

✓I do

I test it with data profiling

2The keys
✗I don’t

Model even if they don’t match

✓I do

Primary keys and orphan rows

3What’s odd
✗I don’t

Silently delete rows

✓I do

I flag it and ask

4Where I clean
✗I don’t

By hand, or in Power Query

✓I do

At the source, with SQL

5What didn’t match
✗I don’t

Hide it

✓I do

A visible data note

00Data profiling

The dictionary says one thing.The data, another.

Before any test, I look at what really arrived.

WhereWhat it saysWhat arrived
fact_ordersOne order per rowUnique key and 0 orphan rows✓
dim_time_slotTime slot17 impossible hours, like 02:95✗
dim_kitchenKitchensKIT-YB-001 is called “Lekki Phase 1” and sits in Yaba✗
dim_riderAssigned kitchenMatches 25.3% of orders: what chance gives✗
temperature°C of the food90.3% above 100 °C. Max: 1,000 °C✗
profitValue − delivery cost0% of the rows comply✗
Public competition dataset (Golden Wok, Onyx Data).
10/10

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.

00Five questions

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.

tablelast loadrows
raw_salestoday 06:1041,200
raw_stores9 days ago38
raw_coststoday 06:120

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_idline_noamount
104115,000
104115,000
104125,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.

checkrulerows
sale_datenot empty1
sale_datenot in the future0
discount_pct0–1002
profitrevenue − cost0

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_idrowsamount
S-14121,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.

monthsourcemodel
Aug318.2 M318.2 M
Sep330.0 M331.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

00Cleaning

Cleaningwithout changing the meaning.

Once, at the source or in staging, with SQL. It stays in a script, and whatever leaves is noted.

✗What arrives
sale_idlineupdateddateproductamountstatus
1041110:0003/10/26café$ 5.000ENTREGADO
1041118:002026-10-03Cafe5000entregado
1042118:002026-10-03Cafe5000entregado
1042218:002026-10-03Cafe5000entregado
1043118:00—CAFÉ5000ok

Two versions of 1041, two coffees in 1042 and a row with no date.

✓How it ends up
sale_idlinedateproductamount_clpdq_flag
104112026-10-03CAFÉ5000—
104212026-10-03CAFÉ5000—
104222026-10-03CAFÉ5000—
10431—CAFÉ5000missing_date

The row with no date isn’t deleted: it’s flagged and the owner is told. “ok” is the owner’s call.

5rows in
4rows out
1old version, kept aside
1flagged row, owner notified
00Reconciliation

Trust is earnedwith numbers that match.

And what doesn’t match is explained line by line. Example figures.

September cost: from one total to the other
Source · accounting$330,000,000
October invoices dated in Septembercut-off+ $2,300,000
Credit notes not loadedsource− $360,000
Model$331,940,000

Difference: $1,940,000 (0.59%). Agreed tolerance: 0.10%.

Data note

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 15
00What they catch

The same questions, on public data.None passed everything.

They’re competitions, not companies: they show what each question catches, not how your data is.

0of 4

passed everything

Public data from Onyx Data challenges.
✗Golden WokIs it credible?

90.3% of temperatures above 100 °C. The keys pass.

!Sunrise CoffeeDoes it add up?

The daily fact is a sample: 22.8% of the monthly one.

!AI and jobsDoes it represent?

Synthetic data, declared. Weighting changes the rankings.

!PharmaciesIs it credible?

The cleanest, but 6,221 lines were sold before the launch.

00When to review

Before deciding,not after.

  1. 1
    A new source arrivesBefore anyone builds on it.
  2. 2
    Two areas bring different figuresBefore the meeting turns into an argument.
  3. 3
    An AI assistant gets connectedBefore it answers with the wrong number.
✓It’s worth it if
  • Two areas bring different figures.
  • Someone is about to decide with that number.
  • Copilot or Claude will read that data.
✗It isn’t for this
  • What’s needed is a financial audit.
  • Someone wants a signature that everything is perfect.
  • Nobody will decide anything with the result.
Read-only accessNo personal data in examplesEverything in writing
00How I know I’m done

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.

I’m done when…
  1. ✓
    Within tolerance, or accepted in writingCost: 0.59%, accepted by Accounting.
  2. ✓
    Rows flagged, not deleted1043 with no date, owner notified.
  3. ✓
    Every finding repeats with the SQLAnyone can run it again.
  4. ✓
    What I didn’t test, also in writingReturns from other channels.
Data note

Arrived?✓

Unique?0 dup.

Credible?1 flagged

Connects?0 orphan

Adds up?0.59% · accepted

!Passes with a note

With the note signed, it gets modelled. Next comes Model.