When Your Schema Is Inferred From One Sampled Row

One of our scheduled transformations had an unusual failure pattern. It ran fine most days. Roughly once a week it failed, and re-running it without changing anything usually fixed it. Nobody had deployed anything on the days it broke.

Intermittent failures with no correlated change are almost always non-determinism somewhere you didn't know existed. In this case it was in the type system.

A field with two shapes

The source was a document database. One field on the collection — a tax breakdown attached to each line item — was written by the application in two different shapes. In the overwhelming majority of documents it was a single object. In a small minority, written by an older code path, it was an array of those objects.

Both are valid documents. The database enforced nothing, because that is the point of a schemaless store. The data had been like this for a long time and nothing downstream had noticed, because most consumers touched other fields.

The staging layer flattened that collection using a macro that infers the shape of each JSON column and generates the appropriate SQL. To decide whether a column is an object or an array, the macro sampled one non-null row.

One row. Out of millions.

Non-determinism compiles into your DDL

If the sampled row happened to be one of the common documents, the macro generated a struct and the model built correctly. If it happened to catch one of the rare array documents, it generated an array of structs instead — a different column type, and therefore a different table schema.

The downstream scheduled query expected one of those and failed against the other.

What made this hard to see is that the failure was never in the same place as the cause. The error surfaced in a query two layers downstream, complaining about a type mismatch. The model that actually produced the inconsistency reported success every time, because from its own point of view it had done nothing wrong — it built a valid table, just not always the same one.

This is the property worth internalising: a sampling-based type inference turns your schema into a random variable. Most of the time the distribution is so skewed that you never notice. You notice when the rare shape gets sampled, which is exactly the situation where nobody has recently changed anything and nobody thinks to look at the modelling layer.

Three fixes, in increasing order of how much you'll thank yourself

Fix the shape at the source. The application should write one type. This is the only fix that addresses the actual problem, and it is usually the slowest to land because it needs an application team, a migration for existing documents, and a deploy. Start it anyway.

Declare the type instead of inferring it. Wherever your tooling lets you pin a column's type explicitly, do it for any field that has ever been ambiguous. An explicit declaration turns a silent, intermittent schema change into a loud, immediate parse failure on the rows that don't conform — which is what you wanted in the first place.

Sample more than one row, and fail on disagreement. If you must infer, infer from a large sample and treat a mixed result as an error rather than picking a winner. A type inference that quietly resolves a conflict is worse than one that refuses to.

The general lesson

Schemaless at the write layer does not mean schemaless at the read layer. Something, somewhere, is going to decide what type that column is. If you don't decide it explicitly, your tooling will decide it implicitly — and implicit decisions made from a sample of one are not decisions at all, they are coin flips embedded in your data warehouse.

The fix is rarely difficult. Finding it is, because the symptom appears nowhere near the cause and disappears when you retry.