Skip to content
BENIBIX Learn

Schema on Write and on Read

Level 4 · Models and workloads

Two moments to check the shape of data. Schema on write checks it as data is saved, schema on read checks it at query time. Then what changes break.

Course contentsLesson 10 of 11

After this lesson you can

  • Explain when each approach checks the shape
  • Say which approach a warehouse and a lake use
  • Change a schema without breaking its readers

11 mincovers 3 glossary entries

Worth reading first: Data Models and ER Diagrams

When the shape of data gets checked

A schema is the agreed shape of data: its fields, their types and their rules. There are two moments to check it. Schema on write checks the shape when data is saved, and refuses rows that do not fit. Schema on read saves anything and applies the shape later, at the moment someone runs a query.

Schema on write is how a database and a warehouse work. You declare the table first, with every column and its type. A row with a word where a number should be is turned away at the door. Bad data never lands, so every reader can trust what is there.

The cost is speed of change. A new field in the source means changing the table before the data can load. Until someone does, the new field is dropped or the load fails. A team with fifty sources can spend a lot of its week on this.

Schema on read is how a data lake works. Files land as they are, JSON, CSV or logs, with no check at all. The shape is applied by the query engine at the moment someone reads the files. Landing is fast, and nothing is lost, even fields nobody planned for.

The cost moves to the reader. Every query has to cope with missing fields, odd types and broken rows. Ten analysts may handle the same mess in ten slightly different ways. The data is all there, but each person has to clean it again before they can trust it.

Either way, schemas change over time. That is called schema evolution. Adding a column is most often safe, because old readers just skip it. Renaming or removing a column is the risky kind. Every report that asked for the old name breaks the next day. Add new columns freely, and take old ones away slowly.

People often think schema on read means no schema at all. There is always a schema. The only question is who writes it down, and when. With schema on read, every reader writes it, every time, unless someone writes it once for all of them.

Which one to pick

  1. 1

    Schema on write

    Pick it for data that many people report on, such as orders. There, a wrong value costs money.

  2. 2

    Schema on read

    Pick it for raw data you are still looking through. It also suits sources that change shape often, such as app logs.

There is always a schema. The only question is who applies it, and when.

Worked example

An app adds a new field

Jack's app starts sending a new field in its events, the kind of phone. The warehouse table has no such column. So the nightly load fails, and the morning report is empty.

His team now lands raw events in the lake first and adds new columns to the warehouse table each week. Nothing is lost while the table catches up.

The words for this lesson3

Check yourself

A team renames the column total to amount. What usually happens next?

Finished this lesson?

Marking it complete also marks its 3 glossary entries learned, which updates your roadmap and your saved page. 0 are already marked.