Skip to content
BENIBIX Learn

Schema-on-Write

Data🧭 FoundationsData Modeling

Also known as Schema on Write

The shape is fixed first. Data that does not fit is turned away.

Think of it like

It is like a mailbox slot. Only letters of the right size fit through.

Example

A bank's payments table only takes amounts as numbers. A file with "ten pounds" in that column fails to load.

Why it matters

Bad data is caught at the door. Everyone who reads the table can trust its shape.

Definition

Schema-on-write means the shape of the data is set before any data goes in. Each table is created with fixed columns and types. When a row is written, the database checks it against that shape. A row that does not fit is turned away. Classic databases and warehouses work this way.

Example code

sql
CREATE TABLE visit (id INT, guests INT);
INSERT INTO visit VALUES (1, 'ten');
-- ERROR:  invalid input syntax for type integer: "ten"
PostgreSQL turns away a row that does not match the table's types.

Common mistake

Thinking it is always slower. Checking on the way in makes each read simpler and often faster.

Key takeaways

  1. 1
    Shape first. Columns and types come before data.
  2. 2
    Checked on write. Each row is tested as it goes in.
  3. 3
    Trusted reads. Readers know what to expect.

Compare

Learn it properly