1. Data Warehouses: Schema-on-Write & Columnar OLAP

Modern Data Warehouses (Snowflake, BigQuery) enforce strict Schema-on-Write validation during ingestion. Data is stored in Columnar OLAP Format:

  • Columnar stripes group values of identical attributes contiguously on disk.
  • Enables vector-quantized dictionary compression and run-length encoding (RLE).
  • Queries skip unreferenced columns entirely, minimizing physical I/O bandwidth.

2. Data Lakes: Schema-on-Read & Object Storage

A Data Lake stores raw structured, semi-structured, and unstructured blobs on object storage (AWS S3, GCS) under Schema-on-Read:

  • Ingestion incurs zero transformation latency.
  • Parsing schemas are applied dynamically at query time.
  • Without catalog governance, immutable Parquet/JSON files degrade into un-indexed Data Swamps.

3. Data Lakehouses: ACID Transactional Metadata Layers

A Data Lakehouse overlays ACID transactional guarantees onto cheap object storage using open table formats (Apache Iceberg, Delta Lake).

   SQL Query Engine (Trino / Spark / DuckDB)
                     │
                     ▼ (Reads Snapshot Manifest List)
   ├── Snapshot Manifest List (snap-1024.avro)
   │    ├── Manifest File A -> references Parquet Data File 1
   │    └── Manifest File B -> references Parquet Data File 2

Optimistic Concurrency Control (OCC)

Transactions commit by atomically swapping the active snapshot pointer. If two concurrent writes modify overlapping Parquet files, OCC detects file-level serialization conflicts and retries the transaction.

4. Python Verification: Columnar vs Row-Oriented I/O Simulation

import numpy as np

def simulate_columnar_vs_row_storage(num_rows: int = 100_000):
    """Demonstrates I/O efficiency of columnar scanning over row-oriented layouts."""
    np.random.seed(42)
    # Row-oriented: list of tuples (id: int, price: float, volume: int)
    ids     = np.arange(num_rows, dtype=np.int32)
    prices  = np.random.normal(100.0, 15.0, size=num_rows).astype(np.float64)
    volumes = np.random.randint(100, 5000, size=num_rows, dtype=np.int32)
    
    # In row storage, scanning 'prices' requires skipping 4B id and 4B volume per row
    row_bytes_read = num_rows * (4 + 8 + 4)  # Reads entire 16 bytes/row
    
    # In columnar storage (Parquet stripe), scanning 'prices' reads only the contiguous float64 column
    col_bytes_read = num_rows * 8
    
    print(f"Total Rows Scanned:                 {num_rows:,}")
    print(f"Row-Oriented Physical I/O Read:     {row_bytes_read / 1024:.2f} KB")
    print(f"Columnar Stripe Physical I/O Read:  {col_bytes_read / 1024:.2f} KB")
    print(f"Exact Bandwidth Saved by Columnar:  {(1.0 - col_bytes_read/row_bytes_read)*100:.1f}%")

if __name__ == "__main__":
    simulate_columnar_vs_row_storage(100_000)