DEV Community: Arthur de Oliveira Torres

One Bug Hit 11 Rows. Another Hit 36 Million.

Arthur de Oliveira Torres — Wed, 05 Aug 2026 03:13:02 +0000

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.

From Raw Prices to Real Signals: Financial Metrics for a Data Pipeline (Daily Return, MA, RSI, MACD, Volatility)

Arthur de Oliveira Torres — Thu, 09 Jul 2026 20:29:43 +0000

In this article I will share my understanding and use of some financial metrics used in my Financial Dashboard project. You can see it in this link and the full article by clicking here.

The main point here is understanding the math used to transform data from the bronze layer — where we have raw information such as date, open, high, low, close, and volume — and aggregate useful data to improve consumption and future use in the data pipeline.

First metric: Daily Return

Not a difficult one. It returns the percentage change between today's and yesterday's stock prices.

Why? Seeing day-to-day changes can be useful and later used in the gold layer for analysis.

The math is:

(today - yesterday) / yesterday

Example:

day 1: 150.0 → NaN
day 2: 152.3 → (152.3 - 150.0) / 150.0 = +0.0153 (+1.53%)
day 3: 149.8 → (149.8 - 152.3) / 152.3 = -0.0164 (-1.64%)

Simple, right? In code, we use pandas:

close.pct_change()

Second metric: Moving Averages (MA7, MA21, MA50)

Moving average shows the average price over the last X days.

Why? Individually, they show the direction of the stock — not very powerful alone, but when analyzed together they can provide strong signals. Golden cross and death cross are the most relevant examples.

The math is:

MA(X) = (day_t + day_(t-1) + ... + day_(t-X+1)) / X

As we can see, for example, MA7 will only return a valid result after the 7th day.

df["ma_7"] = close.rolling(7).mean()

Third metric: RSI (Relative Strength Index)

Probably the most complex in this list, so let's go step by step.

RSI returns a number between 0 and 100:

70+ → overbought
30- → oversold

Why? It helps analyze market momentum. Sometimes stocks are not priced only by their real value — hype, news, or unexpected events can influence behavior.

Let's dive into the math:

Period = 14 (default market value)
Delta = difference between current day and previous day
Gain = delta if positive, otherwise 0
Loss = absolute delta if negative, otherwise 0

Then:

Average Gain = exponential moving average (EWM) of gains
Average Loss = exponential moving average (EWM) of losses
RS = avg_gain / avg_loss

About EWM

EWM (Exponential Weighted Mean) gives more weight to recent values. The weight decays exponentially over time:

most recent day: weight = 1
2 days ago: weight = (1 - α)
3 days ago: weight = (1 - α)²

If α is close to 1  →  fast decay (focus on recent data)
If α is close to 0  →  slow decay (longer memory)

In the RSI calculation, we use com=period-1 in pandas, which relates to alpha as:

α = 1 / (1 + com) = 1 / 14

Small alpha → slow decay → all 14 days matter, not just the most recent ones.

Final RSI formula

RSI = 100 - (100 / (1 + RS))

The result is interpreted as:

RSI < 30 → oversold
RSI > 70 → overbought

Code

def _rsi(close: pd.Series, period: int = 14) -> pd.Series:
    delta = close.diff()
    gain = delta.clip(lower=0)
    loss = -delta.clip(upper=0)
    avg_gain = gain.ewm(com=period - 1, min_periods=period).mean()
    avg_loss = loss.ewm(com=period - 1, min_periods=period).mean()
    rs = avg_gain / avg_loss.replace(0, float("nan"))  # Avoid division by zero
    return 100 - (100 / (1 + rs))

Fourth indicator: MACD (Moving Average Convergence Divergence)

MACD shows the momentum of the stock using EMA (Exponential Moving Average) to weight recent values more heavily.

Unlike a simple moving average where all days have equal weight, EMA applies exponential decay:

EMA(t) = Price(t) × α + EMA(t-1) × (1 - α)

Where alpha is defined as:

α = 2 / (span + 1)

span = 12  →  α ≈ 0.154  (more reactive, short-term)
span = 26  →  α ≈ 0.074  (smoother, long-term)

So EMA12 reacts faster to price changes, while EMA26 captures the longer trend.

MACD = EMA12 - EMA26

The MACD Signal line is an EMA of the MACD itself (span=9), used to identify buy/sell triggers.

The MACD Histogram is the difference between MACD and Signal:

MACD Histogram = MACD - Signal

Value	Meaning
Positive and going up	Growing momentum
Positive and going down	High stock losing strength
Negative and going down	Downward momentum accelerating
Negative and rising	Going down but losing strength

Fifth metric: Volatility (21-day)

Simple but useful — evaluates the standard deviation of daily returns over the last 21 days. Higher volatility means bigger price oscillations in the period. We use it to set risk/reward accuracy.

Code (MACD + Volatility)

ema12 = close.ewm(span=12, adjust=False).mean()
ema26 = close.ewm(span=26, adjust=False).mean()
df["macd"] = ema12 - ema26
df["macd_signal"] = df["macd"].ewm(span=9, adjust=False).mean()
df["macd_hist"] = df["macd"] - df["macd_signal"]
df["volatility_21"] = df["daily_return"].rolling(21).std()

With that, we complete our silver layer.

Conclusion

We can create from raw data some really interesting and useful indicators to aggregate information and extract real value from it. Data without purpose is useless — we need to learn how to extract meaning from it.

And that's the point of this article: not the math, not the code, but that we can get real-world insights from data. That's what makes this work worthwhile.