// data quality · 19 October 2026 · 5 min read
I rebuilt my hand-written pipeline in dbt and planted the bugs myself
All 31 tests passed. Three rows still vanished without a record.
The biggest difference between a hand-written data pipeline and one built in dbt isn’t the code. It’s where the checks live.
When you write the SQL by hand, a quality check is a query you remember to run. In dbt, it’s a test that stops the build when it fails. That sounds like a small difference. It isn’t.
But a test only checks what you thought to check. I learned that the useful way: by planting bugs in my own data and watching one of them walk straight past 31 passing tests.
First, the vocabulary
A few terms come up a lot here, so here is each one in a line before we go further.
- A data pipeline is the chain of steps that takes raw data, cleans it, and turns it into tables people can use for reports.
- The medallion pattern splits that chain into layers: bronze is the raw data exactly as it arrived, silver is cleaned and checked, and gold is the finished tables a dashboard reads from.
- Quarantine is where rows that fail a check go, with a note saying why, instead of being quietly deleted.
- dbt (data build tool) is a tool where you write each table as its own SQL file, and it takes care of running them in the right order, testing them, and documenting them.
The hand-written version
Earlier this year I built a pipeline on the CDC’s yearly US health survey: 2.38 million rows across five years, built on both Databricks and Snowflake and compared side by side. I wrote that comparison up in Databricks vs Snowflake: what actually happens when you build the same pipeline on both.
Every step was a SQL file I wrote and ran myself, in order. The order lived in the file names: 03b_silver, 04_gold. Nothing in the SQL itself said which table depended on which.
It worked. Both platforms matched value for value. But the safety checks were queries I ran and read with my own eyes.
The most important one asked a simple question, year by year: do the rows that came in equal the rows that passed plus the rows I quarantined? If those numbers ever disagree, a row has gone somewhere it shouldn’t.
That habit comes from fintech, not data engineering. When I built the settlement engine at Normies.co, the part that makes sure money moving across accounts and blockchains actually lands where it should, “every unit accounted for” was the whole job. A ledger that doesn’t add up is a bug, even when no single transaction looks wrong.
The rebuild, with bugs I planted on purpose
To see what dbt actually changes, I rebuilt the same pattern on sales data: customers, products, orders, and order items. I generated the data myself with a fixed random seed, which meant I could plant defects and know exactly how many there were.
There were 258 orders. Five of them point to a customer who doesn’t exist, and three are exact duplicate copies, the kind you get when the same file gets loaded twice. There were 662 order items. Eight have a quantity of zero or less, and two point to a product that doesn’t exist.
This is the part I’d recommend to anyone testing a pipeline. Tests passing on clean data proves almost nothing. Tests passing on data where you know exactly what’s broken proves the tests can actually see.
What dbt caught
I ran the whole thing on a Databricks SQL warehouse. All 13 models built (a “model” is dbt’s word for one table or view defined in one SQL file). All 31 tests passed. All 5 orphan orders and all 10 bad order items landed in quarantine, each with its reason attached.
The test that matters most to me is a gate: if more than 10% of orders end up in quarantine, the build fails. A little quarantine is normal. A lot of it means something upstream broke, and a pipeline should stop and say so rather than happily publish a half-empty report.
That’s the real upgrade over the hand-written version. These checks no longer depend on me remembering to run them.
What dbt missed, because I never asked
Here’s the count. 258 orders came in. 250 passed. 5 went to quarantine.
That’s 255. Three orders are unaccounted for.
They’re the three duplicates. One step in the project keeps the first copy of each order and drops the rest, and its own comment says “nothing is silently dropped.” The comment is wrong. Those copies never reach quarantine. They just stop existing.
No real information was lost, since they were exact copies. But the one check that would have flagged it, rows in equals rows passed plus rows quarantined, is exactly the check my hand-written pipeline had and the dbt version doesn’t. In the health-survey pipeline, duplicates went to quarantine under their own rule, and the year-by-year count would have caught any gap.
So: 31 of 31 tests passing, and the books still didn’t balance. dbt runs every test you write, every single time. It doesn’t write the one you forgot.
Two things my hand-written pipeline never had
Lineage is the family tree of your data. For any table, it tells you what it was built from, and what gets affected if it changes.
Think of a bakery. If you find out a batch of eggs was bad, lineage tells you exactly which cakes to pull, without tasting every cake in the shop.
In dbt, each table refers to its parent tables by name using dbt’s ref() function, and dbt builds the family tree from those references. In this project, the daily sales table traces back through the cleaned order items to the raw file. dbt uses that tree to run everything in the right order, and can draw it as a clickable diagram. My hand-written pipeline had the same tree, but only in file names and in my head.
A snapshot is dbt’s way of keeping history. Normally, when a customer moves to a different state, the table just overwrites the old value and the past is gone.
With a snapshot, dbt keeps the old row, marks when it stopped being true, and adds the new one next to it. The industry name for this is SCD Type 2 (a “slowly changing dimension”): a record of how something changed over time, not just what it is today. Every table in my hand-written pipeline was rebuilt from scratch on each run, so it had no history at all.
To be straight about it: both are set up in this project, but neither is fully proven yet. The snapshot has run once, which only records the starting point, and I haven’t generated the lineage diagram. Proving them means changing a customer and running it again. That’s the next run.
When I’d pick which
For a one-off analysis or a handful of tables, hand-written SQL is fine, and arguably clearer. Nothing sits between you and the database.
Once there are more than a few tables, more than one person, or anything that runs on a schedule, I’d reach for dbt. Not because its SQL is better, but because the checks and the order of operations stop living in one person’s memory.
Just write the reconciliation test first. Rows in, rows out, rows quarantined. If those three numbers don’t add up, nothing else your tests say matters.
p.s. The project, including the planted defects and the script that generates them, is on GitHub.
Muhammad Mairaj is a Senior Backend Engineer with 8 years building payment and data systems. He writes about data engineering, measured. More about him.