A team I worked with was proud of their data quality checks, and they had earned it. Dozens of validations, all thoughtful, all catching real problems. Then a bad batch sailed straight into the published table, got read by three dashboards, and set off the usual fire drill.
The checks were not wrong. Every one of them fired. They fired at the very end of the pipeline, after the data was already published, which is a bit like wiring your smoke detector to go off the morning after the fire. Technically accurate. Not much help.
A check is only as useful as two things: where it runs, and whether it can stop anything. A brilliant check in the wrong place is just a very polite log line.
Detection is not control
A check that writes “this looks wrong” to a log is detection. A check that can stop the bad data moving forward is a control.
Detection is a witness. It saw the crime and wrote it down. Control is the locked door that stopped the crime. You want witnesses. You build the pipeline on locked doors. That proud team had a wall of excellent witnesses and not one locked door, so everything they detected still got through.
Three places data quality checks can live
Three natural checkpoints, each good at catching something different.
At ingestion, the front door
Raw data arrives from a source. Checks here are about basic fitness. Is the file the right shape? Are the required fields present? Are the ids the right format? A problem caught here is the cheapest possible outcome, because the bad data has not touched anything yet. Reject it or quarantine it before it gets comfortable.
In transformation, the workshop
Here you reshape, join, and calculate. Some errors can only appear here, because they are born here. A join that fans out and doubles your rows. A revenue calculation that mishandles a currency. A category mapping that silently drops the values it does not recognize. Transformation checks catch the problems your own logic creates, and every pipeline creates a few.
At the release gate, the last door before the public
The final block before anyone downstream reads the data. Your last chance to say “no, this batch is not going out.”
This is exactly what the proud team was missing. They had checks everywhere except the one place that could have stopped publication.
Block or warn
For each control, decide how loudly it speaks. Some failures should stop the pipeline cold. A duplicate in the field that feeds revenue. A load that is half its expected size. A required column gone missing. Publishing that is worse than publishing nothing, so the gate blocks.
Others should only raise a flag. A slightly higher rate of missing optional values deserves a warning, not a full stop. Make every check a hard block and you will be woken at 3 am by something cosmetic. By the third time you will start ignoring the alerts entirely, which defeats the whole point. Save the hard stop for failures that are genuinely worse than a delay.
Quarantine, or where bad rows go to think about what they did
When a check catches bad rows you have a choice. Throw out the whole batch, or set aside just the bad rows and let the good ones flow. The second option is quarantine, and it is usually the kinder one. Good data keeps moving, bad data waits in a holding area, and nothing is silently lost.
-- Good rows go on. Bad rows wait in quarantine.
INSERT INTO orders_clean
SELECT * FROM orders_staging WHERE region_code IS NOT NULL;
INSERT INTO orders_quarantine
SELECT * FROM orders_staging WHERE region_code IS NULL;Quarantine comes with a rule everyone forgets. Somebody has to actually look at it. A quarantine table nobody reviews is not a safety mechanism. It is a junk drawer, and six months later it holds forty thousand rows and a faint smell. Schedule the failed-record review. Someone owns opening that drawer and deciding what gets fixed, released, or thrown out for good.
The placement question, in one line
Where does a new check belong? Ask what is the earliest point where this problem can exist, and whether a check there can actually stop it. Put the control at the earliest place that can both see the problem and block it. Earlier is cheaper. Blocking is what turns a check into a control.
Try this on your pipeline
Pick your most important check and answer two questions honestly. Where in the pipeline does it run? And if it fails right now, does anything actually stop, or does the data keep flowing while an alert quietly logs itself somewhere nobody reads?
If the honest answer is “it logs and moves on,” you have a witness where you needed a locked door. Moving a check earlier and giving it the power to stop the flow is usually a small change. It just happens to pay for itself the first time it catches a bad batch at the front door instead of the front page.
If you want to see a full control plan with validation, gates, and quarantine, I build the whole pipeline in my Pluralsight course, Design and Implement Data Quality Controls.
Reference: Pinal Dave (https://blog.sqlauthority.com/), X
