What do schema-on-write and schema-on-read mean for an analytical data platform?

## The two positions Every analytical platform decides **when** the structure of data is checked. Schema-on-write checks it at ingestion; schema-on-read checks it at query. The choice is not about whether the data has a schema — all data has one, implicitly or explicitly — but about who is forced to confront it, and when. ## Schema-on-write A table is declared with columns and types before data arrives. The load path validates every row: a string where a number is expected, a missing required column, a value outside the type's range — each is rejected, or diverted to an error table, at the moment of ingest. What this buys: - **Trust.** Any query against the table can assume the declared types hold. Downstream code has no defensive parsing. - **Performance.** Knowing the type of a column up front lets the engine choose a compact physical encoding, compute meaningful min/max statistics for skipping data, and plan without guessing at cardinality or width. - **A single interpretation.** The structure is defined once, centrally, rather than re-derived by each consumer. - **Early failure.** Errors surface at the boundary, attached to a specific load, while the producer who caused them is still around. What it costs: - **Coupling.** A producer that adds a field must coordinate a schema change before the data can land, which turns every upstream change into a cross-team ticket. - **Loss.** Fields not in the contract are typically dropped. Data you did not anticipate is gone, not merely unmodelled. - **Ingest fragility.** A malformed upstream batch can halt the pipeline rather than degrade it, which is right for finance and wrong for a best-effort telemetry firehose. ## Schema-on-read Data is landed as-is — nested JSON, semi-structured events, files whose fields drift over time — and structure is applied at query time. In practice this means casting and extracting in SQL, or defining an external table or view whose column list is a *claim* about the files rather than a constraint on them. What this buys: - **Cheap, resilient ingestion.** Producers can evolve without asking permission; the pipeline does not break because a field appeared. - **Preservation.** Everything that arrived is retained, including fields nobody has a use for yet. When a new question arrives, the history is already there. - **Multiple interpretations.** The same raw events can be read as a session table by one team and as a fraud feature table by another, without either forcing its model on the other. What it costs: - **Deferred failure.** A type change surfaces as a cast error — or worse, a silent `NULL` — in someone's dashboard, days later and far from the cause. - **Repeated work.** Parsing and cleaning run on every query instead of once at load, which is both slower and more expensive at scale. - **Divergence.** Two teams write two extraction queries, they disagree about a corner case, and two numbers for the same metric reach two meetings. - **Weaker pruning.** An engine that does not know a column's real type and range has fewer opportunities to skip data. ## The failure mode to name in an interview The classic schema-on-read disaster is **silent** rather than loud: an upstream service starts emitting a numeric field as a quoted string, the read-time cast yields `NULL` instead of throwing, aggregates quietly shrink, and nobody notices until a monthly total looks wrong. The defence is not abandoning schema-on-read but adding explicit contracts and validation at the boundary between the raw zone and the curated zone, plus tests that assert row counts and null rates rather than trusting the cast. ## How real platforms combine them The standard shape is schema-on-read at the edge and schema-on-write in the middle. Raw data lands with minimal validation so ingestion never blocks and nothing is lost. A transformation step then parses, validates and writes typed, enforced tables that everyone reports from; rows that fail validation go to a quarantine table with the reason attached. The curated tables get the trust, statistics and performance of schema-on-write, and the raw zone keeps the flexibility and the history. Table formats have blurred the line further. Lakehouse tables enforce a schema on write even though the files sit in a lake, and support controlled evolution — adding a column, widening a type — as a recorded change rather than an accident. Meanwhile warehouses have gained semi-structured column types that let you land a JSON document into one column and apply structure at query time. So "warehouse means schema-on-write, lake means schema-on-read" is a useful first approximation and a bad final answer; the honest statement is that you choose per zone, and every zone that people make decisions from should be schema-on-write.