Schema-on-write and schema-on-read#

Schema-on-write checks data against a fixed structure when it is stored; schema-on-read stores data as it comes and applies a structure only when it is queried.

Why it matters#

Data from sources changes without warning: a field is added, renamed or filled differently. Where the structure is enforced decides whether such a change stops a load, breaks a report later, or passes silently. Platforms use both approaches deliberately, at different stages.

How it works#

With schema-on-write, a table has declared columns and types, and every write is checked against them. A row with text in a numeric column, or a missing required field, is rejected. Readers can trust the types: if a column says decimal, every value is a decimal. The cost is that incoming data must be made to fit before it can be stored. When the source changes, loads fail until someone updates the table or the pipeline.

With schema-on-read, data is stored as it arrives, as files or text, often JSON or CSV, and the reader decides how to interpret it. A classic form is an external table: a table definition laid over existing files, applied only when the files are queried. A query might say: read the field amount and treat it as a decimal. Nothing is rejected at load time, so new sources and changed sources are captured immediately. The cost moves to every reader. Each one must handle missing fields, wrong types and changed names, and two readers can interpret the same data differently.

flowchart TB
  subgraph Write[Schema-on-write]
    direction LR
    I1[Incoming record] --> C1{Matches table<br/>schema?}
    C1 -->|yes| T1[(Stored, typed)]
    C1 -->|no| R1[Rejected at load]
  end
  subgraph Read[Schema-on-read]
    direction LR
    I2[Incoming record] --> T2[(Stored as is)] --> Q[Query applies<br/>structure] --> E[Errors or nulls<br/>appear here]
  end
  Write ~~~ Read

Layered platforms combine the two. The first layer keeps data close to how it arrived, so nothing is lost when a source changes. The next layer reads it, applies a defined schema, and writes typed tables, so everyone downstream gets a stable structure. Problems with the source are caught once, in that step, instead of in every report. This is the idea behind medallion layers.

Schema-on-read is not the same as no schema. Even raw tables usually have a minimal structure, such as a payload column holding the record plus metadata columns for source and ingestion time.

Key terms#

Example#

-- Schema-on-read: raw events kept as JSON text
SELECT json_extract_scalar(payload, '$.page') AS page
FROM shop.raw_clickstream;
-- after the rename, new events return NULL here, silently

-- Schema-on-write: typed table built from the raw events
CREATE TABLE shop.clean_clickstream (
  event_id   varchar NOT NULL,
  page_url   varchar NOT NULL,
  event_at   timestamp(6) with time zone NOT NULL
);
INSERT INTO shop.clean_clickstream
SELECT json_extract_scalar(payload, '$.event_id'),
       json_extract_scalar(payload, '$.page'),
       from_iso8601_timestamp(json_extract_scalar(payload, '$.event_at'))
FROM shop.raw_clickstream;
-- after the rename, $.page returns NULL for new events,
-- and the NOT NULL constraint on page_url rejects the load
-- until the transformation maps both page and page_url

Pitfalls#

Learn more#

• Martin Kleppmann, Designing Data-Intensive Applications (1st edition, 2017), chapter 2 (schema flexibility in the document model). A 2nd edition appeared in 2026; chapter numbers differ.
• Joe Reis and Matt Housley, Fundamentals of Data Engineering (O'Reilly, 2022), chapter 5 (data generation in source systems: schemas).

Related#