Turning a Spreadsheet Into a Schema
TLDR
When you infer a database schema from spreadsheet rows, a single scored column is not enough: you need a ladder of independent rungs, each carrying its own confidence, and the column's final confidence must be the weakest rung, not the average. Cross-table structure (primary keys, foreign keys, cardinality) is a statement about a batch of sheets, so those rungs must refuse to fire on a single-sheet promotion. This is the difference between "the tool guessed" and "the tool knows it guessed".Problem class: extracting structure from messy data with honest uncertainty
Any developer who imports a CSV, scrapes a PDF, or reads a spreadsheet meets the same trap. The data looks like a table. The header row names columns. The values suggest types. But every one of those readings is an inference, and they do not have equal strength.
A header cell that says id is strong evidence the column is an id. It is not evidence the column is a primary key: primary keys are a statement about how rows identify an entity, which only makes sense once you can see the whole entity, and ideally other entities too. A column full of integers is evidence of an integer type, but one row of 19.99 is weaker than fifty rows of clean integers.
The naive approach scores the whole column once and calls it a day. A single number hides the fact that the name was read confidently while the type was a coin flip. Worse, the number can be lifted by a strong name reading into a confidence the data never earned.
Why the single-score model fails
Two failure modes, both observed in practice.
Mode one: confidence that rises with nothing. A zero-row sheet (header only, no data yet) still names its columns. A single-score model gives those columns high confidence, because the name is real. But there is no type evidence at all. The column is a header with a guess attached. Presenting it as confident is a lie, and any downstream tool that thresholds on confidence will trust it.
Mode two: a strong name silently certifying a weak type. created_at names a timestamp with high confidence. If one row contains 2024-01-01T00:00:00Z and the type reader only sampled two rows, the type is not established. A single score that leans on the name reading papers over that.
The fix is structural, not numerical: make the confidence the minimum across independent rungs, and make rungs that lack evidence produce zero.
The better model: a runged ladder with a weakest-link cap
Split promotion into rungs, each producing its own confidence, then take the minimum.
Rung 1, name: a real header reads 0.95. A synthesized col_3 reads 0.4.
Rung 2, type: agreed values across samples drive confidence. Fewer samples, lower confidence. Zero samples, zero confidence.
Rung 3, nullability: any blank cell flips the column to nullable, at 0.6, because nullability only promotes with evidence.
Then the column confidence is the minimum of the rungs that fired. A well-named, well-typed column settles at 0.9. A well-named column with no rows settles at 0.0. The name cannot rescue the type.
const nameConf = named ? 0.95 : 0.4; // rung 1
const t = inferType(values, dialects); // rung 2, confidence 0 at 0 samples
const col = createColumn({
name,
type: t.type,
confidence: Math.min(nameConf, t.confidence), // weakest link
});The same discipline applies to cross-table rungs. Primary key, foreign key, and cardinality only make sense across a batch. A single sheet promoted in isolation must come back with zero relations and zero inferred keys. That is not a limitation, it is the honest answer: you cannot witness a relationship by looking at one side of it.
Implementation evidence
The ladder ships as a promotion engine with two entry points. promoteTable walks rungs 1 through 3 on one sheet and stops. promoteBatch runs the cross-table pass, and only there do the key rungs fire.
The minimum rule is load-bearing. A zero-row column with a real header reads 0.0, because the type rung produced no evidence and the minimum refuses to let the name lift it. Removing the cap, letting a named id head a zero-row column, produced a column at 0.8 that the data never earned. The suite pins this with an exact assertion: confidence === 0.0 for the zero-row case, and a strict inequality for the few-samples case.
The batch gate is tested the same way. Two sheets promoted together resolve customer_id to customers.id as a foreign key with an explicit 0.85 confidence. The same two sheets promoted separately produce zero relations. The test names the invariant directly: rungs 4 through 6 fire only across a batch.
Tradeoffs
The weakest-link cap is unforgiving. A column with a perfect name and mediocre type evidence scores at the type's level, not the name's. That is the point, but it means the confidence number is not a "how good is this column overall" figure. It is a "how sure are we of the least-sure thing about this column" figure. Downstream consumers must read it that way.
Zero-confidence columns are honest but noisy. A header-only sheet now produces columns that any confidence threshold will drop, which is correct and also surprising to the first-time user. The alternative, letting names float uncertain columns, is the failure mode we removed.
The batch gate trades away convenience for honesty. Promoting one sheet and getting zero structure feels like a missing feature until you hold it next to the alternative: single-sheet FK inference that can only fire on naming conventions, which is exactly how false foreign keys get invented.
When the single-score model is right
If the reader is doing approximate work, a single score is fine. Fuzzy matching, search ranking, anything where the answer is "sort by plausibility" does not need per-rung honesty. The ladder earns its complexity when a downstream tool makes a binary trust decision on the number, because then a confident guess and a verified fact must not look the same.