Definition
- Schema-on-write – the structure and types are defined and enforced before data is stored; every row must conform at write time, as in a relational database.
- Schema-on-read – raw data is stored as-is with no enforced structure at write time; the schema is applied only when the data is read, as with files in a data lake.
- Data integrity – the guarantee that every stored row is valid, which schema-on-write provides automatically.
- Schema evolution – changing the structure over time (adding columns, changing types) without breaking existing data.
- Self-describing format – formats like Parquet that embed their schema in the file, providing a hybrid approach.
- Schema registry – a central service storing versioned schemas with compatibility rules, used even with schema-on-read lakes.
Schema-on-write is like the National Bank of India's account-opening form: before any detail is filed, every field must be filled correctly, name here, PAN there, amount as a number, and the clerk rejects the form if anything is wrong. Schema-on-read is like a big "miscellaneous documents" box where staff toss in anything: letters, screenshots, raw notes, without checking, and only when someone later opens the box do they interpret each paper. The form guarantees clean data but is strict and slow to change; the box accepts anything instantly but you must make sense of it later.
Understanding the Concepts
What is schema-on-write?
Schema-on-write is the relational database approach. You define tables with columns and types up front, and the database rejects any write that violates the schema or constraints. The benefits are data integrity and query performance: every row is guaranteed to conform, the optimizer knows the types, and consumers can trust the data.
-- Schema-on-write: table defined before any data enters
CREATE TABLE transactions (
txn_id VARCHAR(20) PRIMARY KEY,
account_id VARCHAR(20) NOT NULL,
amount DECIMAL(15,2) NOT NULL CHECK (amount > 0),
created_at TIMESTAMP NOT NULL
);
-- Valid insert: accepted
INSERT INTO transactions VALUES
('T1', 'SAV2001', 15000.00, '2024-10-29 09:15:00');
-- Invalid insert: REJECTED (null amount)
INSERT INTO transactions VALUES
('T2', 'SAV2001', NULL, '2024-10-29 09:17:00');
-- ERROR: null value in column "amount" violates not-null
INSERT 0 1 -- T1 accepted
ERROR: null value in column "amount" -- T2 rejected
What is schema-on-read?
Schema-on-read is the data lake approach. You store raw bytes, whether JSON, CSV, Avro, Parquet, or logs, without enforcing a structure at write time. The schema is imposed later, at query time, by the engine reading the files. Ingestion is fast and flexible, and you can land data whose shape you have not fully decided yet.
# Schema-on-read: apply structure only when reading
from pyspark.sql.types import StructType, StructField
from pyspark.sql.types import StringType, DecimalType
my_schema = StructType([
StructField("txn_id", StringType()),
StructField("account_id", StringType()),
StructField("amount", DecimalType(15, 2)),
StructField("created_at", StringType())
])
# Raw JSON in the lake has no enforced structure
df = spark.read.schema(my_schema).json(
"s3://national-bank-lake/raw/events/")
df.show(3)
+------+----------+--------+-------------------+
|txn_id|account_id| amount| created_at|
+------+----------+--------+-------------------+
| T1| SAV2001|15000.00|2024-10-29 09:15:00|
| T2| SAV2001| null|2024-10-29 09:17:00|
| T3| CUR3002|25000.00|2024-10-29 09:20:00|
+------+----------+--------+-------------------+
How is schema-on-write different from schema-on-read?
| Aspect | Schema-on-Write | Schema-on-Read |
|---|---|---|
| Enforcement | Before storing | When querying |
| Data integrity | Guaranteed at write | Discovered at read |
| Ingestion speed | Slower (validation) | Faster (no validation) |
| Flexibility | Rigid (migration needed) | High (land any shape) |
| Query performance | Fast (optimizer knows types) | Varies (depends on format) |
| Bad data | Rejected at the door | Appears as nulls/errors at read |
| Schema change | ALTER TABLE migration | Just change the read schema |
| Example system | PostgreSQL, Snowflake | S3 + Spark, HDFS |
package com.bank.pipeline;
public class SchemaComparison {
public static void main(String[] args) {
System.out.println("Schema-on-write (warehouse):");
System.out.println(" INSERT rejected if amount is null.");
System.out.println(" Consumer trusts every row.");
System.out.println();
System.out.println("Schema-on-read (lake):");
System.out.println(" Null amount lands fine.");
System.out.println(" Consumer discovers bad data at query time.");
}
}
Schema-on-write (warehouse):
INSERT rejected if amount is null.
Consumer trusts every row.
Schema-on-read (lake):
Null amount lands fine.
Consumer discovers bad data at query time.
What are the trade-offs?
The cost of schema-on-write is rigidity and slower ingestion. Adding a column or changing a type requires a migration, and loading data that does not fit the schema fails. The cost of schema-on-read is that bad or inconsistent data is only discovered at read time, queries can be slower, and without governance the lake becomes a swamp.
package com.bank.pipeline;
public class TradeOffs {
public static void main(String[] args) {
// Schema-on-write: rigid but safe
System.out.println("Adding a 'channel' column"
+ " to schema-on-write:");
System.out.println(" ALTER TABLE transactions"
+ " ADD COLUMN channel VARCHAR;");
System.out.println(" Requires migration and downtime.");
System.out.println();
// Schema-on-read: flexible but risky
System.out.println("Adding a 'channel' field"
+ " to schema-on-read:");
System.out.println(" Just start writing it."
+ " Old files lack it.");
System.out.println(" Reader must handle missing field.");
}
}
Adding a 'channel' column to schema-on-write:
ALTER TABLE transactions ADD COLUMN channel VARCHAR;
Requires migration and downtime.
Adding a 'channel' field to schema-on-read:
Just start writing it. Old files lack it.
Reader must handle missing field.
What is Parquet's hybrid approach?
Self-describing columnar formats like Parquet carry their own schema in the file footer. This is a hybrid: data has strong typing and structure, but the schema is embedded in the file rather than enforced by a central database. Consumers can read the footer to discover the schema at query time, getting the benefits of both approaches.
# Parquet embeds schema in the file footer
df.write.parquet("s3://bank-lake/curated/txns.parquet")
# Reader discovers schema automatically
df2 = spark.read.parquet("s3://bank-lake/curated/txns.parquet")
df2.printSchema()
root
|-- txn_id: string
|-- account_id: string
|-- amount: decimal(15,2)
|-- created_at: string
Why do mature lakes still use schemas?
Schema-on-read does not mean no schema. Mature lakes register schemas in a catalog (AWS Glue, Hive Metastore) and use a schema registry (Confluent Schema Registry) with compatibility rules so consumers are not blindsided by upstream changes.
package com.bank.pipeline;
public class SchemaRegistry {
public static void main(String[] args) {
System.out.println("Schema Registry compatibility rules:");
System.out.println(" BACKWARD: new schema reads old data");
System.out.println(" FORWARD: old schema reads new data");
System.out.println(" FULL: both directions");
System.out.println();
System.out.println("Safe change: add nullable 'channel'"
+ " field with default");
System.out.println("Unsafe change: rename 'amount'"
+ " to 'txn_amount' (breaks consumers)");
}
}
Schema Registry compatibility rules:
BACKWARD: new schema reads old data
FORWARD: old schema reads new data
FULL: both directions
Safe change: add nullable 'channel' field with default
Unsafe change: rename 'amount' to 'txn_amount' (breaks consumers)
How do banks use both approaches together?
Banks use schema-on-read for raw ingestion speed and flexibility, then validate and promote data into schema-on-write tables in the warehouse for trustworthy, regulated reporting. This gives agility on the ingest side and trust on the reporting side.
package com.bank.pipeline;
public class BothTogether {
public static void main(String[] args) {
System.out.println("Step 1: Raw events land in lake"
+ " (schema-on-read)");
System.out.println(" Fast, flexible, accepts anything.");
System.out.println();
System.out.println("Step 2: Conform job validates + types");
System.out.println(" Rejects malformed records.");
System.out.println();
System.out.println("Step 3: Clean data loads into warehouse"
+ " (schema-on-write)");
System.out.println(" Regulated reports trust every row.");
}
}
Step 1: Raw events land in lake (schema-on-read)
Fast, flexible, accepts anything.
Step 2: Conform job validates + types
Rejects malformed records.
Step 3: Clean data loads into warehouse (schema-on-write)
Regulated reports trust every row.
What are common interview follow-up questions?
- How does schema evolution work in Avro vs Parquet?
- What is backward vs forward compatibility?
- How do you handle a new column that appears upstream without warning?
- What is the role of a schema registry in a Kafka pipeline?
- How do lakehouse formats (Delta, Iceberg) blend schema-on-read with enforcement?
Visual Overview
Schema-on-Write vs Schema-on-Read ────────────────────────────────── Schema-on-WRITE Schema-on-READ ┌─────────────────┐ ┌─────────────────┐ │ Define schema │ │ Store raw bytes│ │ CREATE TABLE │ │ JSON, CSV, log │ │ (types, nulls) │ │ (no structure) │ │ │ │ │ │ │ │ ▼ │ │ ▼ │ │ Validate write │ │ Apply schema │ │ (reject bad) │ │ at read time │ │ │ │ │ (discover bad) │ │ ▼ │ │ │ │ │ Trusted data │ │ Raw data │ └─────────────────┘ └─────────────────┘ Bank pattern: use BOTH ───────────────────── Lake (schema-on-read) ──▶ Validate ──▶ Warehouse (schema-on-write) Fast ingest, flexible Gate Trusted, governed
Key Takeaway
Schema-on-write means the structure and types are defined and enforced before data is stored, so every row must conform at write time, which is how a relational database works. The benefits are data integrity and query performance, because every row is guaranteed valid, the optimizer knows the types, and consumers can trust the data. The cost is rigidity and slower ingestion, since adding a column or changing a type needs a migration and data that does not fit is rejected.
Schema-on-read means raw data is stored as-is with no enforced structure at write time, and the schema is applied only when you read it, which is how data lake files work. The benefit is agility, because new and varied data lands instantly and different consumers can interpret the same raw data differently. The cost is that bad or inconsistent data is only discovered at read time and queries can be slower.
The two fit together the same way the lake and warehouse do. Raw data lands schema-on-read in the lake for flexibility and speed, then a pipeline validates and conforms it into schema-on-write tables in the warehouse for trustworthy, fast analytics. Self-describing columnar formats like Parquet are a hybrid, carrying their own schema in the footer, and mature lakes still register schemas in a catalog with compatibility rules so consumers are not blindsided.
In a banking context at the National Bank of India, varied raw event streams landed schema-on-read in the lake so ingestion was fast and flexible, but a conform job validated and typed those records into strict schema-on-write tables in the warehouse, rejecting anything malformed. That way raw ingestion stayed agile while the regulated reporting layer stayed clean and trustworthy.
Remember one sentence: schema-on-write enforces structure before storing for trust, schema-on-read stores raw for speed, and banks use both to balance agility with regulatory correctness.
---