Schema-on-Read
Schema-on-read applies a structure, such as column names and types, when stored data is read. Schema-on-write checks a defined structure before accepting data into a destination. These describe when a particular schema is applied, not whether a platform has quality checks or governance. A pipeline can validate an incoming payload and still expose the stored data through a schema-on-read query engine.
Two places to put the decision
| Schema-on-write | Schema-on-read | |
|---|---|---|
| When structure is checked | At load time | At query time |
| What happens to data that does not fit | Rejected, corrected, or routed elsewhere according to the load contract | Depends on ingestion checks and the reader’s error-handling contract |
| Cost of adding a new source | Model it first | Land it now, model it later |
| Who absorbs a format change | The loading pipeline and affected downstream consumers | A shared table or view, or individual consumers if no common interpretation exists |
| Typical home | Data warehouses | Data lakes and raw landing zones |
The distinction matters because a schema never really disappears. Someone still has to decide what a field means and what to do when it is missing or malformed. Schema-on-read applies the query’s schema at the point of use; a shared definition can still be agreed and maintained beforehand.
Why it became popular
Traditional warehouses applied schemas during loading. Dixon’s data-mart criticism concerns retaining only selected attributes or aggregates: questions requiring discarded information can no longer be answered from that mart. A fixed schema that retains the necessary detail can still support new questions. An orders table initially used for total revenue may later answer revenue by country without changing its schema, provided country and order amounts were retained.
Cheap object storage and open file formats made that practical. Initial landing could precede a full analytical model, and exploratory work — data science in particular — could start from raw data rather than waiting for a warehouse team.
What it defers
The Lakehouse paper describes lakes where quality and governance problems were deferred downstream. That is a risk of leaving these responsibilities unresolved, not a requirement of schema-on-read. Athena applies schemas at query time while supporting access controls for source data and catalog metadata. Shared table definitions and views can also give consumers a common interpretation. Without those arrangements, the following problems can arise.
- Readers disagree. Two teams reading the same raw events can infer different types for the same field, or treat a missing value differently, and produce different numbers from identical data.
- Bad records fail late. A malformed record that ingestion checks do not catch may fail when a query touches it — possibly months later, in a report someone depends on.
- Inference is a guess. Tools that infer structure do so from what they see. Apache Spark, for example, can automatically infer the schema of a JSON dataset, and a
samplingRatiooption controls what fraction of records is used to do it; a rare field or type absent from the sample is simply not part of the inferred schema.
Even the choice of what to do with bad data becomes a reader’s setting. Spark’s JSON reader offers three modes: PERMISSIVE puts the malformed string into a designated corrupt-record column and sets unparseable fields to null — though with a user-supplied schema that lacks that column, it drops the record instead; DROPMALFORMED ignores malformed records entirely; and FAILFAST throws an error. The same file can therefore yield a count that keeps malformed rows, one that silently omits them, or a failure, depending on how the reader was configured.
Where each fits
Most platforms now use both, at different layers. Retaining an unchanged source payload can support reprocessing, provided it remains available; schema-on-read alone does not guarantee that preservation. Publishing curated tables schema-on-write checks their structure at the destination, while consumers still need a shared meaning for the fields and a plan for changes. Lakehouse table formats point the same way: they keep data in open files but give each table a defined schema that writers are expected to respect, bringing back much of the protection of schema-on-write without giving up the lake’s storage model. How strictly a given engine enforces that on write is a setting to check rather than assume.
The useful question is therefore not which approach is better, but where in the flow structure should be enforced — and who is responsible for the data between arrival and that point. How warehouses, lakes, and lakehouses divide those responsibilities is worked through in Warehouse, Lake, and Lakehouse.
References
- James Dixon: Pentaho, Hadoop, and Data Lakes
- Armbrust et al.: Lakehouse (CIDR 2021)
- Apache Spark: JSON files
- Amazon Athena: Data protection
- Amazon Athena: CREATE VIEW
Discover more from Insightful Data Lab
Subscribe to get the latest posts sent to your email.