Schema-on-Write vs on-Read

A schema can be enforced at two moments. Schema-on-write declares types and rules before data is stored, and rejects anything that does not fit: the classic database and warehouse approach. Schema-on-read stores data as it arrives, often as raw JSON or CSV files in a lake, and applies a structure only when a query reads it. DuckDB 61,228 shows both in a few lines, using the order events from Generation:

Schema-on-read infers types at query time; schema-on-write rejects a bad row

-- Schema-on-read: DuckDB infers the types from the raw JSON when the query runs.
SELECT column_name, column_type FROM (DESCRIBE SELECT * FROM read_json('order-events.jsonl'));
-- Schema-on-write: types and rules are declared before any row is stored.
CREATE TABLE orders (order_id INTEGER PRIMARY KEY, book_id INTEGER NOT NULL,
                     qty INTEGER CHECK (qty > 0), unit_price DECIMAL(6,2), ts TIMESTAMPTZ);
INSERT INTO orders SELECT * FROM read_json('order-events.jsonl');
INSERT INTO orders VALUES (1009, 2, 0, 39.50, '2026-09-28 18:00:00+00');
SELECT count(*) AS stored_orders FROM orders;

Output

┌─────────────┬─────────────┐
│ column_name │ column_type │
│   varchar   │   varchar   │
├─────────────┼─────────────┤
│ order_id    │ BIGINT      │
│ book_id     │ BIGINT      │
│ qty         │ BIGINT      │
│ unit_price  │ DOUBLE      │
│ ts          │ TIMESTAMP   │
└─────────────┴─────────────┘
Constraint Error: CHECK constraint failed on table orders with expression CHECK((qty > 0))
┌───────────────┐
│ stored_orders │
│     int64     │
├───────────────┤
│             8 │
└───────────────┘

The inferred schema is a guess: unit_price became a floating-point DOUBLE, a poor type for money, and the Z on each timestamp was read as a plain TIMESTAMP without a time zone. The declared table stored the eight good events with exact decimals and refused the order for zero copies. Schema-on-read is flexible and lets you land data before you understand it; schema-on-write catches problems at the door. Most platforms use both, landing raw data schema-on-read and enforcing contracts and types as it moves into curated tables (JSON Schema 2020-12 and Anomalies, Contracts, Lineage).

MENU