Skip to content

// data quality · 30 September 2026 · 5 min read

Never silently drop a row

What cleaning 2.4 million rows of CDC health data taught a backend engineer

I averaged a column called PHYSHLTH and got 60.7.

The question behind it is simple: “In the past 30 days, how many days was your physical health not good?” So apparently the average American had 60 bad days out of 30.

The data wasn’t broken. I was reading it wrong. In this column, 88 means “none”, and 77 and 99 mean “don’t know” and “refused”. Recode those and the average drops to about 4.3 days.

That one number is pretty much this whole post.

Why a backend engineer cares

I’ve spent six years building fintech backends. Payments, settlement, KYC/AML. In that world you don’t lose a transaction. If something gets rejected, there’s a reason, and someone can look it up later.

Right now I’m learning data engineering in public (I wrote about why here). So when I picked up the CDC’s health survey data, I brought that same rule with me: never silently drop a row.

The data

BRFSS is the CDC’s yearly phone survey about health habits and chronic conditions. I used five yearly files, 2011 to 2015. That’s 2,380,047 rows.

The first surprise came before I read a single row. The files have 454, 359, 336, 279 and 330 columns. Out of 644 column names across all five years, only 158 show up in every year. The CDC adds, drops and renames questions all the time.

So if your pipeline hardcodes a column list, it’s already wrong.

One question sorts every problem

Every issue I found came down to one question: is there exactly one right fix?

Yes → fix it

  • Some date columns came out as the literal text b'01202011'. That’s a Python bytes object written straight into the CSV, never decoded.
  • IDs came out as '1.0' instead of 1. pandas turns an integer column into floats if any value is missing. Now _STATE = 1 never matches '1.0', your join returns zero rows, and nothing throws an error. If you’ve done backend work, you’ve probably been bitten by this exact bug.
  • _BMI5 stores a BMI of 27.50 as 2750. The codebook says there are two implied decimals. Miss that and every BMI stat is off by 100x.
  • 55 dates in 2011 had a leading space instead of a zero, like ' 9142011'. There’s only one way to read that, so I fixed it.

It’s a real answer, just not a number → keep it, leave it out of the math

  • In DIABETE3, 1 is yes, 3 is no, 7 is “don’t know” and 9 is “refused”. Average the column and you get 2.76. That number means nothing.
  • PHYSHLTH is the 60.7 from the top of this post.
  • DIABAGE2 (age when told you have diabetes) is blank for 384,203 people. That’s because it’s only asked if you said yes to diabetes. Fill those blanks with 0 and you just invented a few hundred thousand people diagnosed at age zero. Blank isn’t zero.

No safe fix → quarantine it, with a reason

  • 73 rows have dates that don’t exist, like September 31st. Sure, I could guess the “closest” real date. But then I’d be making up a fact nobody ever recorded.
  • Quarantined rows aren’t deleted. They go into a separate table with the original data and a reason column. If you’re a backend person, think of it as a dead-letter queue for data.

Notice the leading-space dates and the impossible dates look like the same problem. They’re not. One has a known fix, the other was never valid. Telling those apart is honestly most of the job.

One more trap: SEQNO looks like a unique respondent ID. It isn’t. It’s only unique per state, per year. There are hundreds of thousands of duplicates on SEQNO alone, and zero on (_STATE, SEQNO). Dedupe on the wrong one and you merge strangers from different states into one person.

I matched a popular notebook exactly. That’s the problem.

There’s a popular public Kaggle notebook built on the 2015 file. It picks 22 columns, drops any row with a blank, then removes the “don’t know” and “refused” codes. Final result: 253,680 rows.

I ran its exact filters on the raw file and got the same numbers: 441,456 → 343,606 → 253,680. The class counts matched too. That was great news, because it proved my parsing was right, checked against work I didn’t write.

But matching its numbers isn’t the same as agreeing with its approach. That cleaning quietly throws away 187,776 rows, 42.5% of everyone, with no record of which rule removed which row.

The worst part? 38.3% of the people who said they have diabetes are gone. A model trained on that data has lost more than a third of the exact people it’s meant to find, and nothing in the output warns you.

The reading was perfect. The cleaning strategy is the part I’d skip.

The survey weight changes the answer

BRFSS isn’t a simple random sample. It oversamples some groups on purpose, and a column called _LLCPWT corrects for that. The notebook above never selects it.

Here’s 2015 diabetes prevalence with and without it:

Alabama: 17.14% unweighted vs. 13.46% weighted
Colorado: 9.09% unweighted vs. 6.75% weighted

Both move the same way, but by different amounts, so state rankings can shift. Nationally, the weighted number lands at 10.50%.

Quick sanity check: my top states lined up with the well-known “diabetes belt” (Mississippi, West Virginia, Alabama, plus Puerto Rico). That doesn’t prove I’m right. But a weird ranking would’ve told me I was wrong.

So what did it cost?

Across all 2,380,047 rows, my full rule set quarantined 1,606 rows. That’s 0.067%. Every single one has a named reason.

I also built the pipeline more than once: on Databricks and on Snowflake, and as both hand-written SQL and a declarative pipeline. Completely different code, same total. That’s stronger proof than one pipeline agreeing with itself. The platform comparison deserves its own post, so I’ll write that up next.

If you take one thing from this

  • Read the codebook before you read the data.
  • For every bad value, ask: is there exactly one right fix?
  • If you drop a row, write down why.
  • Check your numbers against something you didn’t build.
  • If it’s survey data, use the weights.

“Clean data” isn’t a property of a file. It’s a bunch of decisions. Make them visible.

The full walkthrough with code is in my Kaggle notebook, and the pipeline is on GitHub.

What’s the messiest dataset you’ve had to trust? Tell me in the comments.

Muhammad Mairaj is a Senior Backend Engineer with 8 years building payment and data systems. He writes about data engineering, measured. More about him.