Whenever a nightly CSV import starts failing on dirty strings, somebody eventually opens a pull request with this one-liner:
python
df["amount"] = pd.to_numeric(df["amount_raw"], errors="coerce").fillna(0.0)
I get why people write it. When you have 500,000 payment events waiting to load and row 41,209 contains "$1,299.00" instead of "1299.00", letting float() blow up the whole job at 3:00 AM feels worse than coercing bad strings to NaN.
Except errors="coerce" doesn't just skip broken rows. Paired with .fillna(0.0), it turns formatted enterprise invoices into zero-dollar charges without logging a single warning. And if the same CSV feed also contains webhook retries or legacy cent-denominated rows (4900 instead of 49.00), your script finishes with exit code 0 and hands finance a completely made-up number.
I put together a 500,000-row benchmark (github.com/endlessfountain/silent-data-quarantine
) to see how badly a standard pandas script drifts on realistic payment noise, and how fast we can quarantine bad rows with reason codes instead of dropping or guessing values.
Where the numbers actually go wrong
If your CSV comes from a single clean Postgres table, you rarely see this. It shows up as soon as you merge exports from two or three sources: checkout webhooks, manual finance uploads, and older API versions.
Four things usually slip into the same column set. Manual enterprise exports show up with currency formatting like "$1,299.00" or commas, which to_numeric wipes out to NaN. Timed-out webhooks get delivered two or three times with the exact same event_id. Legacy mobile or v1 billing endpoints send integer cents (4900) next to v2 decimal dollars (49.00), and both parse cleanly as floats. Finally, missing timestamps often arrive as 1970-01-01T00:00:00Z because an upstream serializer defaulted null to zero, quietly wrecking monthly cohort queries even though pd.to_datetime() accepts the string.
Running the naive script against 500,000 rows
In generate_corrupt_feed.py, I generated 485,000 clean USD subscription charges ($19, $49, $99, and $299) adding up to an exact ground truth of $26,870,210.00, then mixed in 15,000 dirty rows across those four failure modes:
5,000 formatted enterprise charges like "$1,299.00" ($9,525,000.00 total)
5,000 duplicate event_id retries
3,000 rows recorded in USD_CENTS (4900, 9900)
2,000 rows stamped 1970-01-01T00:00:00Z
Running the usual coercion script (naive_pipeline.py) takes 2.48 seconds:
python
df = pd.read_csv(csv_path, dtype=str)
df["amount"] = pd.to_numeric(df["amount_raw"], errors="coerce").fillna(0.0)
df["ts"] = pd.to_datetime(df["created_at"], errors="coerce", utc=True)
naive_total = round(float(df["amount"].sum()), 2)
Here is what it prints on Python 3.11:
json
{
"pipeline": "naive_pandas_coerce",
"exit_status": "SUCCESS (0 errors raised!)",
"rows_counted": 500000,
"reported_revenue_usd": 49472490.0,
"true_clean_revenue_usd": 26870210.0,
"silent_dollar_error_usd": 22602280.0,
"dropped_enterprise_invoices_usd": 9525000.0,
"elapsed_ms": 2483.4,
"memory_delta_mb": 55.4
}
What caught me off guard wasn't just the size of the error, it was that two huge bugs partially masked each other's tracks. The script threw away $9.52M of enterprise revenue by zeroing out "$1,299.00", yet still came out $22.6M above reality because the 3,000 USD_CENTS rows got summed at 100x face value alongside 5,000 duplicate webhook IDs. If you only spot-checked row counts (500,000 in == 500,000 out), everything looked healthy.
Splitting clean and dirty rows without iterrows()
The tempting fix is validating row by row with Pydantic or df.iterrows(). On 500,000 rows, Python-level row loops drag runtime from ~2 seconds out to 30+ seconds.
We can keep the check under five seconds (including writing CSVs back to disk) by building a vectorized reasons Series in pandas. Instead of failing the whole file or filling NaN with zero, every rule appends a tag to rows that fail it:
python
MIN_VALID_TS = "2020-01-01T00:00:00Z"
MAX_SINGLE_CHARGE_USD = 10_000.00
def quarantine_dataframe(df: pd.DataFrame) -> tuple[pd.DataFrame, pd.DataFrame, dict]:
reasons = pd.Series("", index=df.index, dtype="object")
bad_unit = df["currency_unit"] != "USD"
reasons = reasons.where(~bad_unit, reasons + "UNIT_NOT_USD;")
parsed_amount = pd.to_numeric(df["amount_raw"], errors="coerce")
bad_number = (
parsed_amount.isna()
| (parsed_amount <= 0)
| (parsed_amount > MAX_SINGLE_CHARGE_USD)
)
reasons = reasons.where(~bad_number, reasons + "UNPARSED_OR_OUT_OF_RANGE_AMOUNT;")
parsed_ts = pd.to_datetime(df["created_at"], errors="coerce", utc=True)
bad_ts = parsed_ts.isna() | (parsed_ts < pd.Timestamp(MIN_VALID_TS))
reasons = reasons.where(~bad_ts, reasons + "INVALID_OR_EPOCH_TIMESTAMP;")
dupe_id = df.duplicated(subset=["event_id"], keep="first")
reasons = reasons.where(~dupe_id, reasons + "DUPLICATE_WEBHOOK_ID;")
is_quarantined = reasons != ""
clean_df = df.loc[~is_quarantined].copy()
clean_df["amount_usd"] = parsed_amount.loc[~is_quarantined].astype(float)
quarantine_df = df.loc[is_quarantined].copy()
quarantine_df["quarantine_reason"] = reasons.loc[is_quarantined].str.rstrip(";")
return clean_df, quarantine_df, {
"total_input_rows": int(len(df)),
"clean_rows_accepted": int(len(clean_df)),
"quarantined_rows_rejected": int(len(quarantine_df)),
"verified_clean_revenue_usd": round(float(clean_df["amount_usd"].sum()), 2),
"quarantine_breakdown": quarantine_df["quarantine_reason"].value_counts().to_dict(),
}
Notice why reasons concatenates strings rather than stopping at the first failed if check. When a single broken webhook row has both USD_CENTS and a 1970 timestamp, its quarantine_reason comes out as UNIT_NOT_USD;INVALID_OR_EPOCH_TIMESTAMP. Whoever fixes the upstream exporter sees every problem on that row in one pass instead of playing whack-a-mole across two re-runs.
Meanwhile, the 485,000 valid charges don't wait on manual cleanup. They land straight in clean_events.csv, and the 5,000 "$1,299.00" enterprise invoices sit safely in quarantined_events.csv where you can strip currency symbols and replay them without losing a cent.
Measured output and where pandas hits a wall
Running python quarantine_gate.py processes all 500,000 rows and writes both output files:
Metric Naive errors="coerce" Quarantine Gate
Reported USD total $49,472,490.00 $26,870,210.00
Error vs. ground truth +$22,602,280.00 $0.00
Enterprise invoices lost to $0 $9,525,000.00 $0 (5,000 rows quarantined)
Wall time 2.48 s (read only) 4.58 s (read + check + write 2 CSVs)
Process RAM delta 55.4 MB 138.6 MB
I also added a small regression test in test_quarantine.py with five synthetic rows covering all four failure types. Anytime someone touches the ingestion logic later, python -m unittest test_quarantine.py runs in 0.09s and fails immediately if .fillna(0) creeps back in.
One honest caveat before you copy this into a tiny container: look at that 138.6 MB memory jump. Reading columns as dtype=str in pandas and building string masks allocates eager copies in RAM. For hourly batches up to a million rows on a normal worker, 4.5 seconds and 139 MB is completely fine. Once single files grow past 5 million rows, or if your API runs inside a tight 512MB container with multiple requests arriving at once, eager pandas DataFrames will OOM-kill your worker. At that point you are much better off expressing these exact filter masks in Polars (scan_csv / scan_parquet) or DuckDB.
Top comments (0)