Case Study 2: The Zeroes

"The alert fired on the first morning. It took three days to work out what it was telling us, because it was pointing at the wrong table."

Executive Summary

A supplier changed the export tool that produces Kestrel's daily inventory feed. The new tool wrote the same data with one difference: it no longer quoted the SKU column.

pandas.read_csv inferred sku as an integer, and 0041229 became 41229. All 346 SKUs in the feed stopped matching dim_product — 8.4% of Kestrel's catalogue, and 11.2% of order lines.

The same loader had read eleven other feeds correctly for three years. This one broke because its SKU column is the only one that is entirely numeric, and type inference on a column of digits has exactly one plausible answer.

The control worked. The unknown-member volume metric added after Chapter 19's Case Study 2 fired on the first morning: 1,988 order lines a day landing on the unknown product against a threshold of 100.

The diagnosis took three days anyway, because the alert named fct_order_item and the defect was in a supplier feed that nobody had connected to it. And the damage was in a third place entirely: 346 phantom rows in dim_product, created by an upsert that treated each unmatched SKU as a new product. It ran wrong for three nights and created them once, because the upsert keys on SKU — an idempotency discipline adopted for an unrelated reason, containing this one.

Skills applied: type inference at the read (§22.11); declaring a schema (§22.11's ⚠️ callout); the unknown-member metric (Chapter 19 Case Study 2); the difference between where an alert fires and where a defect lives.

Background

The feed. A supplier sends a daily CSV of on-hand inventory: 346 rows, SKU and quantity, dropped on SFTP at 01:30. It is one of twelve supplier feeds read by the same loader.

This supplier serves one product family. Kestrel acquired a business in 2021 whose SKUs are numbered 00412xx; that supplier still supplies it, and their feed contains those SKUs and nothing else. Every value in the column is digits.

The loader, unchanged for three years:

df = pd.read_csv(path)                       # ← no dtype argument
df["_ingested_at"] = pd.Timestamp.utcnow()
df.to_parquet(f"{LAKE}/bronze/supplier_inventory/dt={day}/")

Two versions of the same file:

BEFORE (quoted)                    AFTER (unquoted)
sku,on_hand                        sku,on_hand
"0041229",412                      0041229,412
"0041230",88                       0041230,88
"0041233",7                        0041233,7

Both are valid CSV. Both contain the same data. The supplier's change was to a tool's default quoting behaviour, was not announced, and was not a change to the data by any definition the supplier would recognize.

Kestrel's SKUs. 4,120 active. Most look like SP-99120 — a letter prefix, so any reader keeps them as strings and there is nothing to infer wrongly. The 346 in the legacy family are the only ones that are pure digits, and they all arrive through this one feed.

That is why three years of the same loader had never produced this failure. The other eleven feeds are protected by an accident of their SKU format.

The Problem

At 06:00 on the first morning, the alert fired:

[ALERT] gold.fct_order_item — unknown-member volume
  product_key = -1 : 1,988 rows for 2026-07-14
  threshold        : 100

Which is exactly what that alert is for, and it fired within four hours of the first bad load. Chapter 19's Case Study 2 ends with the argument that the unknown-member pattern creates a monitoring obligation; this is the obligation being discharged, and it worked.

And then three days went by.

⚠️ Failure Mode — an alert names where a defect is visible, not where it is

The alert said gold.fct_order_item. The defect was in a CSV reader, four hops upstream, in a pipeline that loads a different table.

The investigation went where the alert pointed, which is what an investigation does:

text day 1 fct_order_item "are we losing products?" → the join is correct day 2 dim_product "is the dimension stale?" → it is loading fine (in fact it is loading MORE than usual ←) day 3 supplier_inventory ← someone finally read the bronze table

Day 2 walked directly past the evidence. dim_product was gaining about 346 rows a night, which anyone would have noticed as anomalous if they had been looking at the row count. They were looking at freshness, because the question was "is the dimension stale?"

The general shape: a lineage graph tells you where a defect can have come from, and almost nobody consults it during an incident — because during an incident you look at the thing that is complaining.

Two things that shorten this, and the second is cheap:

• Put the upstream lineage in the alert itself. dbt's manifest has it (Chapter 19 §19.9); an alert that lists the six sources feeding the failing model turns a three-day search into a six-item checklist.
• Alert on the sources too. A row-count or schema-change check on bronze.supplier_inventory would have fired on the same morning, naming the right table. The cost is that you now have two alerts for one incident — which is a real cost, and worth paying for the ones that sit at a trust boundary.

The Analysis

Step 1: read the bronze table, which is what day 3 finally did:

>>> pl.read_parquet("bronze/supplier_inventory/dt=2026-07-14/").head(3)
┌─────────┬─────────┐
│ sku     ┆ on_hand │
│ i64     ┆ i64     │      ← i64
╞═════════╪═════════╡
│ 41229   ┆ 412     │
│ 41230   ┆ 88      │
└─────────┴─────────┘

sku is an integer. And on the previous day's partition it is a string. Two partitions of the same table have different schemas, which is legal in Parquet-on-object-storage and is precisely what Chapter 17's contract exists to prevent.

Step 2: quantify.

SELECT COUNT(*) FROM bronze.supplier_inventory
 WHERE dt = '2026-07-14' AND sku NOT IN (SELECT sku FROM dim_product);
-- 346

$$\frac{346}{4{,}120} = 8.4\%\ \text{of SKUs},\ \text{covering } 11.2\%\ \text{of order lines}$$

$$17{,}753\ \text{lines/day} \times 0.112 = 1{,}988\ \text{lines/day}$$

Which is the alert's number, exactly. The arithmetic reconciling is what turned a hypothesis into a finding — the same move as Chapter 18's Case Study 2.

Step 3: find the damage, which is not where anyone was looking.

dim_product is maintained by an upsert: a SKU in the feed that is not in the dimension is inserted as a new product. That is correct behaviour — it is how genuinely new products arrive, and it matters because a product that sells before its dimension row exists is a real occurrence.

So every night, 346 integer SKUs were inserted as 346 new products.

dim_product rows
  2026-07-13   4,120
  2026-07-14   4,466      +346
  2026-07-15   4,466      +0     ← the upsert is idempotent on the key
  2026-07-16   4,466

Three nights of a broken load produced one night's worth of damage, because the upsert keys on SKU and the second night matched what the first had created. Chapter 20 §20.3's idempotency discipline contained a defect it was not written for, which is the one piece of good news in this incident and the strongest available argument for adopting it before you have a reason.

The 346 phantom products each have a null name, a null category, and a _source of the supplier feed — which is what made them findable once anyone looked at the right table.

🔎 Read the Plan — the three readers do not agree, and one of them is right

Measured, on the versions in §22.2's 🧭 note (pandas 3.0.2, Polars 1.41.2, DuckDB 1.4.4), reading the same two files with no type declared:

column contents	pandas	Polars	DuckDB
0041229, 0041230 (all digits)	41229 int64	41229 Int64	'0041229' VARCHAR
0041229, SP-99120 (mixed)	'0041229' str	'0041229' String	'0041229' VARCHAR

DuckDB's CSV sniffer treats a leading zero as evidence that the column is not a number, and keeps it as text. pandas and Polars do not. All three are "correct" — nothing in CSV says which reading is intended — and the divergence is invisible until the day a column's contents change character.

Two things follow, and the second is the more useful:

• A pipeline that reads the same file with two engines can disagree about its contents. If you prototype in DuckDB and ship in pandas, you have tested a different program.
• The second row is why this had never happened. The eleven other feeds have letter-prefixed SKUs, so every reader keeps them as strings. Those loaders are not safe; they are lucky, and the difference matters because luck expires when a supplier renumbers.

Do not rely on a good sniffer either. DuckDB happens to be right here; it is still inference, and the correct answer is to declare the type in all three.

The Decision

Four changes, and the second is the one that generalizes.

One: declare the schema.

SUPPLIER_SCHEMA = {"sku": pl.String, "on_hand": pl.Int64}

df = pl.read_csv(path, schema_overrides=SUPPLIER_SCHEMA)
# and fail loudly rather than coercing:
assert df.schema == SUPPLIER_SCHEMA, "schema drift: %s" % df.schema

Two lines. The second one matters as much as the first, because schema_overrides will happily cast 41229 to the string "41229" — which is a different wrong answer, and one that would have produced exactly the same 346 non-matches with no error at all.

📐 Design Decision — casting is not validating

This is the trap inside the fix, and it caught the first attempt at it.

The zeros are in the file. 0041229 is on disk, in the bytes, in both versions — the supplier's change removed the quotes, not the digits. So the information is recoverable, and everything depends on when you declare the type.

text declared at the read 0041229 ──read as text──▶ "0041229" ✅ cast after inference 0041229 ──inferred i64──▶ 41229 ──cast──▶ "41229" ❌

The integer inference is where the zeros are destroyed, and a cast afterwards faithfully preserves the damage. In code the two are one line apart and look equally like "declaring the schema":

python pl.read_csv(path, schema_overrides={"sku": pl.String}) # ✅ "0041229" pl.read_csv(path).with_columns(pl.col("sku").cast(str)) # ❌ "41229"

The general rule: a type declaration is only a control if it is applied at the boundary where the information still exists. One step later it is a cast, and a cast preserves whatever the inference already threw away.

And that is why the assertion is separate from the declaration. The assertion compares against a known-good value — a checksum of the SKU set, or a count of SKUs matching ^0 — rather than against the type, because the type will be right in both cases above.

Two: assert something the cast cannot fake.

# Every SKU in this feed is 7 digits beginning with a zero. If that stops
# being true, the export tool changed -- and unlike a type check, this
# fails on BOTH ways of losing the zeros.
bad = df.filter(~pl.col("sku").str.contains(r"^0\d{6}$"))
assert bad.is_empty(), (
    "%d SKU(s) do not match the expected format, e.g. %r -- check the "
    "export tool" % (bad.height, bad["sku"][0]))

A pattern, not a count. A count of 346 breaks the day the family gains a product; the format is a property of the supplier's numbering scheme and will outlive the catalogue.

Three: a data contract on the feed (Chapter 17). The supplier is external and will not sign one, so it is an observed contract — status observed, with measured guarantees and an explicit note that no agreement exists, which is exactly the case Chapter 17 §17.8 describes.

Four: alert on the source, not only the mart. A schema-change check on bronze.supplier_inventory that compares each partition's schema to the previous one and fails on any difference.

And the cleanup. The 346 phantom products were deleted rather than repaired, after confirming that none had been referenced by anything except the unknown-member joins. The fct_order_item rows were rebound using the nightly rebind from Chapter 20 §20.11 — which existed, which is why the repair was a scheduled job doing its ordinary work rather than an emergency script.

What Happened

The alert did its job and the incident still took three days. That is the thing worth taking from this case study: detection and diagnosis are different problems, and a control that solves the first can leave the second untouched.

An audit of every CSV and JSON reader in the platform found nine without a declared schema. Three were reading columns that were unambiguously identifiers:

• A payment-processor reconciliation file with a merchant_reference column, currently all-numeric. It has been an integer for two years and will become a string the first time a reference contains a letter — at which point the join breaks in the other direction.
• An internal export whose order_id column arrived as a float on any day containing a null.
• A marketing feed whose campaign_code had leading zeros in exactly one campaign, launched the previous week and not yet loaded.

None of the three had failed. All three were one file away.

Lessons

1. A reader that infers types makes your schema a property of whatever arrived this morning. The supplier changed a quoting default, not the data.

2. Any identifier that looks numeric is a string. SKUs, postcodes, account numbers, merchant references, campaign codes. The test is whether arithmetic on it means anything, not whether it parses as a number.

3. The failure needs a column that is entirely numeric. A single letter anywhere in the column protects it in all three readers, which is why eleven other feeds through the same loader were fine for three years. They were not safe; they were lucky, and luck expires when a supplier renumbers.

4. The three readers disagree. On an all-digit column with leading zeros, DuckDB keeps VARCHAR while pandas and Polars infer an integer. Prototype in one and ship in the other and you have tested a different program. Do not rely on the good sniffer either — it is still inference.

5. A type declaration is only a control at the boundary where the information still exists. read_csv(schema_overrides=...) keeps the zeros; read_csv().cast(str) preserves the damage, and the two lines look equally like declaring a schema.

6. Casting is not validating. Assert something the cast cannot fake — a format, not a count, because a count breaks the day the product family grows.

7. The control worked and the incident still took three days. Detection and diagnosis are different problems, and a control that solves the first can leave the second untouched.

8. An alert names where a defect is visible, not where it is. Put the upstream lineage in the alert, and alert on sources at a trust boundary — accepting that you now get two alerts for one incident.

9. Day 2 walked past the evidence because it was asking about freshness while the anomaly was in the row count. The question you ask determines what you can see.

10. Three nights of a broken load produced one night's worth of damage, because the upsert was idempotent on the key. A discipline adopted for an unrelated reason contained this one.

11. The unknown-member metric from Chapter 19 caught a Chapter 22 defect. Controls added after one incident catch unrelated ones, which is the strongest available argument for adding them.

12. Nine readers had no declared schema and three were one file away from the same failure, in both directions — an integer that will become a string, and a float that appears whenever a null does.

Questions for Discussion

1. The supplier changed a quoting default. By what standard is that a breaking change, and who should have known?

2. schema_overrides and a post-read cast look equally correct in a diff. What review practice, or what tooling, distinguishes them?

3. The assertion counts SKUs starting with a zero and hardcodes 346. That number will change when the legacy family does. Is that a good assertion or a maintenance burden? What is the alternative?

4. Alerting on sources as well as marts means two alerts per incident. Where would you draw the line, and what does "trust boundary" mean concretely in your systems?

5. The investigation looked at dim_product on day 2 and asked about freshness. What would have made the row count the natural question instead?

6. Three of the nine unprotected readers are one file away from an incident, and none has failed. How do you prioritize fixing something with no symptom against work that has one?

7. Chapter 19's control caught this. Estimate how many incidents your existing alerts would catch that they were not designed for — and whether that changes how you would justify the next one.