Case 03 · Data quality
The job finished. Nothing broke. The numbers are wrong.
A blank dashboard gets fixed by lunchtime, because everyone can see it is broken. A dashboard covered in believable wrong numbers gets acted on. That is the problem this whole case is about.
Start here
Being wrong quietly is the expensive way to be wrong.
If a table is empty, everybody notices within the hour. Somebody shouts, somebody fixes it, and the damage is a morning of annoyance.
If a table is packed with figures that look entirely normal and sit twenty per cent too high, nothing happens at all. The dashboard opens. People read it. Somebody cuts a budget, pauses a campaign, or tells the board that revenue is growing when it is flat. Three weeks later somebody in finance adds it up by hand and asks a very awkward question.
So the job here is not really “clean data”. It is stopping the wrong number before anyone sees it, and knowing the moment it happens rather than three weeks later.
Six questions in one
“Is this data any good?” is really six separate questions.
Here is one night’s file from a delivery partner. You did not write the system that produced it, and you cannot make them be careful. Tap each question and watch which cells it objects to.
Eight rows. Almost every one of them fails something, and they fail different things. That is the part worth holding on to.
Why six and not one
Five of these can be fine and the sixth still ruins your week.
It is tempting to flatten all six into a single feeling — this looks fine to me. That does not hold, because each one breaks by itself.
One of the comments under an article on this subject put the hard truth about consistency well: getting one customer id to mean the identical thing in fifteen tables, fed by five different systems, is a fight with no end in it. That is not a failure of your pipeline. It is the normal condition of a company that grew.
The mechanism
Nobody reads a warning. Everybody reacts to a stopped pipeline.
This is tonight’s load: the same eight rows, on their way to the dashboard. Switch checks on and off, then run it. Watch what the job reports, and separately, watch what reaches the people looking at the screen.
the job’s own report
what people end up seeing
Where it goes
The whole idea is one arrow, in the right place.
Everything above comes down to putting the check after you have built the table and before anybody can read it. Pass, and it goes out. Fail, and the run stops and yesterday’s good numbers stay up.
People call that a quality gate. It is worth more than every clever thing in this case put together, and it is about fifteen lines of code the first time you write it.
What does not work: checking after the data has been served, by looking at the dashboard and going “hmm, that seems high”. By then people have read it. You are no longer preventing a problem, you are explaining one — and you are doing it from behind.
The one that gets you
Five nights. Every job went green. One of them was lying.
New engineers are afraid the pipeline will crash. People who have done this a while are afraid of the opposite — the run that finishes beautifully and loads the wrong thing. Nothing alerts. Nobody looks. Here are five nights of a real-looking load. Pick the broken one.
The row count was normal. The run time was normal. The error count was zero. The only thing that gave it away was a column nobody thinks to look at: the newest order date did not move.
A date filter was one day out, so the job happily pulled Wednesday again and wrote it over Wednesday. The code followed its instructions perfectly. The instructions were wrong.
Two different jobs
Watching the code is not the same as watching the data.
This is why data quality is a separate thing from ordinary monitoring, and not a nicer version of it.
Job monitoring
Did the code finish?
status SUCCESS took 4m 12s errors 0
Tells you the machine did what it was asked. Says nothing at all about whether what it was asked made sense.
Data quality checks
Is the result sane?
rows today 0 newest order yesterday revenue nulls 100%
Looks at what actually landed. This is the half that catches the silent ones, and it is the half most teams skip.
You want both, and you want them to disagree loudly. A run that finishes in four minutes with zero errors and zero new rows should be a failure, not a success with a note.
Further upstream
Catching it is good. Not letting it in is better.
Everything so far catches bad data after it has arrived. There is a second move that happens earlier, at the door.
Instead of accepting whatever a source system sends and sorting it out afterwards, you write down what you expect — these columns, these types, this one is never null, this one is always one of four values — and you reject anything that does not match, at the moment it arrives. When that agreement is written down and shared with the team who sends the data, people call it a data contract. In practice it is usually a schema file plus a short, unglamorous conversation about who fixes it when it breaks.
This is cheaper than fixing things downstream, and it is cheaper by a lot, because bad data spreads. One wrong column at the door becomes eleven wrong tables by Thursday.
But you need both, and here is the neat reason why. Stopping data at the door only catches the problems you already knew to describe. Checking after the fact is what catches the ones nobody has thought of yet — the vendor who silently starts sending prices in a different currency, the app release that changes what “cancelled” means. You cannot write a rule against a surprise. You can notice one.
How strict
Stop trying to get data that is perfect. There is no such thing.
Flawless is not the target. Trustworthy enough for whatever is being decided on top of it — that is the target. One per cent of a marketing list missing is nothing at all. One per cent missing from payment records is a very bad week.
So set the bar per column, based on what the column does. Two rough starting points that people quote, and they are reasonable:
There is a story in the comments under one of these articles that is worth more than the rule: a pipeline produced duplicate customer records for three weeks because a de-duplication step was missing. Nothing failed. The machine learning model downstream started making strange predictions, and that is how they found out — through the symptom, weeks later, nowhere near the cause.
The temptation
If it fails every night, do not turn it down.
Here is the moment that decides whether any of this survives. A check fails on Monday. It fails again Tuesday. By Thursday somebody is under pressure, and the quickest way to a green pipeline is to relax the rule.
Do not. A check that fails every night is not broken. It is working, and it is pointing at something real, over and over.
If the partner keeps sending rows with no city, the fix is a conversation with the partner, or a rule at the door that rejects those rows on arrival. The fix is not deleting the test so the pipeline turns green. No amount of testing downstream repairs a source that keeps sending rubbish. All the test decides is whether you find out.
What people actually use
Your first one can be a few queries and an if statement.
None of this needs a tool to start. A few SQL queries and an if statement that raises an error is a real quality gate. When you outgrow that, these are the names you will hear.
| dbt tests | If your transformations already live in dbt, this is nearly free. You add a few lines to a YAML file — not null, unique, accepted values, this key exists in that table — and they run with the build. Start here if you have the choice. |
| Great Expectations | More powerful, and more to set up. Handles complicated rules and keeps a record of what passed. People who use it say the same two things: it can do anything, and it is wordy. |
| Soda | The easier one to get going with. If what you need is null checks, uniqueness and ranges, you will be running in an hour rather than a day. |
| Your scheduler | Whatever runs your jobs can hold the gate itself. In Airflow, for example, a check task can simply stop everything after it when the numbers look wrong, so nothing downstream ever runs. |
Tool choice is genuinely not the interesting decision here. A basic check that runs on every load beats a sophisticated one that runs when somebody remembers.
The awkward part
Nobody gets promoted for a dashboard that was already correct.
This work is invisible when it goes well, which makes it hard to get time for. Somebody asked exactly this in the comments of an article on this topic: how do you convince leadership to pay for data quality when it is not a feature anyone can see?
The answer that works is to stop describing it as quality and start describing it as cost.
This is also, quietly, the most senior-sounding thing in this whole case. Anyone can list six dimensions. Being able to say what bad data cost the company last quarter is a different conversation.
If you get asked
“How do you know the numbers are right?”
Resist naming tools. That is what everybody else does, and it tells the interviewer nothing.
Name a few of the six in plain words. Complete, unique, in a sensible range, recent enough, and agreeing with the table next door. You do not need to recite all six or use the textbook words.
Then describe the gate. Tests that sit between building the table and letting anyone see it, and that stop the whole run instead of writing a note in a log. Explain why that matters: a note gets skimmed past, a stopped pipeline gets dealt with before lunch.
Then say the thing that lands. That what keeps you up is not the job that crashes, it is the one that finishes happily having written nonsense — so you look at the rows themselves, how many arrived and how recent they are, rather than trusting a success message.
That is the sentence they are waiting for. It is almost impossible to say convincingly unless it has happened to you once, which is precisely why it works.
Two questions
Did it land?
Last night’s job: SUCCESS, 4 minutes, zero errors, zero new rows loaded. What should happen?
A completeness check on city has failed every night for a week. The pipeline keeps stopping. What do you do?
Your playbook · Case 03
Nine things to keep
- Wrong is worse than emptyAn empty dashboard gets fixed today. A confident wrong one gets acted on, then found out three weeks later.
- “Good data” is six questionsComplete, unique, valid, fresh, consistent with its neighbours, and actually true. Each one breaks on its own.
- Accuracy is the one code cannot seeA perfectly formatted wrong city passes every automated test. You catch it by sampling against the real source.
- Put the check between build and serveThat single arrow is the whole idea. Pass and it goes out, fail and yesterday’s good numbers stay up.
- Fail the run, do not warnA warning is a thing nobody reads. A stopped pipeline is a thing somebody fixes this morning.
- Watch the rows, not the runZero new rows on a normal weekday is a failed load however green it looks. Count what landed and see how recent it is.
- Stop it at the door and watch behind itContracts catch the problems you knew about. Checks catch the surprises. You cannot write a rule against something nobody has thought of yet.
- Strict where it matters, relaxed where it does notRoughly 95% complete is fine for descriptive fields. The key is unique 100% of the time, always.
- Never quiet a check to get a green pipelineA check that keeps failing is working. Fix the source, or reject the rows on arrival.
Your review
How was this case?
Loading ratings…
Next · Case 04
The customer who moved.
Dimensional modelling, slowly changing dimensions and data marts — taught by watching last year’s revenue quietly change city.