One Bug Hit 11 Rows. Another Hit 36 Million.

#python #datascience #pandas #dataengineering

I built a predictive maintenance model on 37,164,054 telemetry records from an iron ore mine. Six months of alarms from haul trucks and excavators. The goal was to predict "Don't Go" events, the ones that stop a machine mid-operation, hours before they happen.

Before any of that, I had to deal with four data quality problems.

Here is what makes them interesting. One of them touched 11 rows. Another touched 36,104,611. Both were silent. Neither threw an exception.

The setup

The dataset came from Vale's Programa Desenvolver with quality problems deliberately inserted as part of the challenge. Three were documented in the challenge material. The fourth was not, and I only found it when I stopped working on a sample.

#	Column	Problem	Rows fixed
1	`Criticidade`	UTF-8 encoding corruption	11
2	`Classe`	Literal string `"NULL"`	36,104,611
3	`Valor`	Literal string `"NULL"`	237,443
4	`Valor`	Comma as decimal separator	821,849

1. Eleven rows out of thirty seven million

The Criticidade column holds the alarm severity label. Almost every row was fine. Eleven were not:

N??o Crítico

Não Crítico had lost its accented characters somewhere in the export chain, replaced by question marks.

Eleven rows. That is 0.00003% of the dataset. It would never show up in a head(), a random sample, or a summary statistic. But group by that column and you silently get an extra category, splitting one class into two.

_CRITICIDADE_PATTERN = r"N.{1,2}o Cr.{1,2}tico"

df["Criticidade"] = df["Criticidade"].str.replace(
    _CRITICIDADE_PATTERN, "Não Crítico", regex=True
)

The regex is loose on purpose. I did not want to assume the corruption was always exactly two question marks, because it was not consistent.

2. Thirty six million rows of the word "NULL"

The Classe column should hold Activate or Inactive. Instead, 97% of the dataset held this:

"NULL"

Not a null value. The four characters N, U, L, L, as text.

Every null check I had written came back clean. isna() saw a perfectly valid string. The column reported 100% populated while being almost entirely empty.

df["Classe"] = df["Classe"].replace("NULL", np.nan)

This is the one that worries me most, because it fails in the direction of looking healthy. A column that reports zero missing values does not get a second look.

3. A decimal separator that changed its mind

Valor holds the numeric sensor reading behind each alarm. It arrived typed as a string, which was already a hint. Inside, 821,849 rows looked like this:

"43,7999992370605"

Comma as decimal separator, Brazilian convention, in a column that also held dot-separated values.

Cast the column to float and you get an exception. Cast with errors="coerce" and you get NaN exactly where the values were, which then reads as missing data rather than as a bug you introduced.

df["Valor"] = df["Valor"].astype(str).str.replace(",", ".", regex=False).astype(float)

4. The one that only appeared at full scale

Valor also contained the literal string "NULL". 237,443 rows of it.

I did not find this one in the challenge documentation, and I did not find it while iterating on a sample. It surfaced the first time I ran the pipeline against all six months at once.

That is the part worth sitting with. The sample was not small: it was large enough to feel representative and fast enough to iterate on. It still hid a problem affecting a quarter of a million rows, because 237,443 out of 37 million is 0.6%, and 0.6% is easy to miss when you are slicing the first N rows of one file.

The fix is one line. Finding it was the work:

mask_valor_null = df["Valor"].astype(str) == "NULL"
df.loc[mask_valor_null, "Valor"] = np.nan

Ordering matters here. This has to run before the comma replacement, or "NULL" reaches astype(float) and takes the whole cast down with it.

The pattern that made this tractable

I stopped writing cleaning code first. Instead every fix reports what it touched, into a dataclass:

@dataclass
class QualityReport:
    criticidade_fixed: int
    null_string_fixed: int
    decimal_fixed: int
    valor_null_fixed: int
    inicio_after_fim: int
    tag_mismatches: List[str]
    duplicate_rows: int
    timestamp_gaps: List[str]
    summary: str

Three things came out of that:

The report is the diagnosis. When I had to defend the choices in the final report, I had exact counts per problem, not a memory of having fixed something.

Cleaning becomes testable. Every fix got a test asserting the count. Reruns are cheap and regressions are loud.

It has room for problems I had not found yet. Fields like inicio_after_fim, tag_mismatches and timestamp_gaps are checks on the operational log that were not part of the planted three. Building the structure to hold unknown problems is what let the fourth one surface as a number instead of as a crash three notebooks later.

What I took from it

None of these four throws an exception. A pipeline with no validation layer runs end to end, trains a model, prints a metric, and is wrong in a way no stack trace will ever mention.

The spread is the lesson. Eleven rows and thirty six million rows are the same class of bug and need the same defense, because neither announces itself. At this volume you cannot eyeball anything, and your sample is not as representative as it feels.

The validator is not overhead you add if there is time left. It is the thing standing between you and a confident wrong answer.

Full case study, including SHAP explainability and what the model actually learned (which was not what I hoped): https://torres-dev-ai.vercel.app/projects/vale-desenvolver-2026

Source code, tests and metrics: https://github.com/tutorres/Vale_Desenvolver

The dataset is proprietary and is not redistributed.