Chapter 06 · 12 min · Basic
The check you run before you trust a table
Somebody points you at a table and asks for a number. The table has a sensible name, the column names read fine, and the first query returns rows.
None of that is evidence. A table that returns rows is not a table you can trust, and the failures that cost you a week are never the ones that error — they are the ones that quietly return a plausible number against a feed that stopped loading last Tuesday.
This chapter is a fixed audit set: six checks you run every single time, before you have decided what the analysis is. Running them in a fixed order is the point. You cannot be curious about a problem you have not thought of, and the checklist does not need to be.
The dataset
A thirteen-row payments table with no constraints at all — no primary key, no foreign key — plus a four-row customers table. It has been seeded with four separate real defects. The chapter finds all four with the same six queries you would run against any table, and never with a query written specifically for this data.
Schema
| customer_id | int |
| name | text |
| signup_date | date |
| payment_id | int |
| customer_id | int |
| paid_at | date |
| amount_myr | numeric(10,2) |
| method | text |
| reference | text |
Example data
Six questions, always the same six
The free tutorial's profiling a table you have never seen teaches you how to look around a table. This chapter is the other half: the fixed set you run every time, in the same order, without deciding what to look at.
That matters because the interesting failures are the ones you would not have thought to check. A checklist you run mechanically finds them; curiosity does not, because curiosity follows the columns that look relevant to today's question.
Six checks. Each one has a result that should make you do something specific next:
1. Shape — how many rows, and what period do they cover?
2. Null rate per column — which columns are actually populated?
3. Key — is the thing named _id unique?
4. Duplicates — if not, are the extra rows identical or contradictory?
5. Orphans — do the foreign keys point at anything?
6. Cardinality — how many distinct values, and does that number make sense?
The payments table below fails four of the six. Every query in this chapter runs against any table with a name change, which is the entire point — this is a thing you keep, not a thing you reason out each time.
Check 1 — shape. Row count, date range, and a total. Thirty seconds, and it catches the two failures that waste the most time downstream.
Wrong period. The data runs 1 March to 12 March — eleven days. If you were asked about March, this table does not have March in it yet. Every rate, average and trend you compute is going to be about a third of a month, and nothing in the data will tell you that later.
Wrong table. Thirteen rows and RM 502 for a national payments feed is a staging table, a sample, or a filtered view someone left behind. A row count that is the wrong order of magnitude is the cheapest signal that you are querying the wrong object, and it is worth having an expected number in your head before you start.
The table is meant to cover March. Read the last_payment date: does this table contain all of March?
Basic
The rest of this chapter is Basic
Learning SQL is free here, forever. Both data-science tracks are the paid half — this one is the level-up: grain, reshaping, change over time, and whether the number really moved.
RM 25/mo · cancel anytime
Still to come in this chapter
- 02Null rate: which columns are real
- 03Key: is the id actually an id
- 04Duplicates: identical, or contradictory
- 05Orphans: does the foreign key point at anything
- 06Cardinality: does the count of distinct values make sense
- 07What the audit is actually for