Storage and Open Table Formats

Most lakehouse performance and cost problems are file layout problems. Know how bytes sit on disk and you can answer half of all “this query is slow” questions.

1. Row vs columnar formats

Rule: Avro/Protobuf on the wire (Kafka), Parquet at rest (lake).

2. Parquet internals

flowchart TB
    F["Parquet file (e.g. 512 MB)"] --> RG1["Row group 1 (~128 MB)"]
    F --> RG2["Row group 2"]
    F --> FT["Footer: schema + per row-group,<br/>per column stats (min, max, null count)"]
    RG1 --> C1["Column chunk: user_id"]
    RG1 --> C2["Column chunk: country"]
    RG1 --> C3["Column chunk: amount"]
    C2 --> P1["Pages (~1 MB)<br/>dictionary + RLE / bit-packed values"]

How a query like SELECT sum(amount) FROM t WHERE country = 'DE' reads less:

1. Column pruning: reads only country and amount chunks.
2. Row-group skipping: footer stats say a row group has country min=‘AT’, max=‘CH’ → skip it.
3. Dictionary filtering: if ‘DE’ isn’t in the dictionary page, skip.
4. Encoding: low-cardinality columns become tiny with dictionary + run-length encoding.

That’s why sorting/clustering data by commonly filtered columns makes min/max stats selective and queries 10–100× faster.

3. Why table formats exist

Plain Parquet folders have no atomic commits (readers see half-written jobs), no updates/deletes without rewriting partitions, no schema enforcement, and slow file listing on object storage.

Table formats add a metadata layer that lists exactly which files form each table version.

Delta Lake

flowchart LR
    subgraph log["_delta_log/"]
        J0["00000.json<br/>add f1, f2 · schema"]
        J1["00001.json<br/>add f3"]
        J2["00002.json<br/>remove f1 · add f4 (MERGE)"]
        CP["00010.checkpoint.parquet<br/>(state snapshot every N commits)"]
    end
    J0 --> J1 --> J2 --> CP
    J2 --> V["Version 2 = {f2, f3, f4}"]

• Each commit = a new JSON file with add/remove actions + stats. Atomic via put-if-absent on the next version number.
• Optimistic concurrency: writers read version N, write files, try to commit N+1; on conflict, check if the changes logically overlap; retry or fail.
• Time travel: SELECT * FROM t VERSION AS OF 42 / TIMESTAMP AS OF, as long as VACUUM hasn’t deleted the old files (default retention 7 days).
• Features: MERGE, Change Data Feed (row-level changes between versions), deletion vectors (soft-delete rows without rewriting files), liquid clustering, UniForm (Iceberg/Hudi-readable metadata).

Apache Iceberg

flowchart TB
    CAT["Catalog: table → current metadata pointer"] --> MD["metadata.json<br/>schema, partition spec, snapshots"]
    MD --> SN["Snapshot S3"]
    SN --> ML["Manifest list"]
    ML --> M1["Manifest A<br/>files + column stats"]
    ML --> M2["Manifest B"]
    M1 --> D1[data-1.parquet]
    M1 --> D2[data-2.parquet]
    M2 --> D3[data-3.parquet]

• Tree of metadata → fast planning on huge tables (prune manifests by partition ranges without listing).
• Hidden partitioning: partition by days(ts) transform; users filter on ts, and Iceberg maps it. Partition evolution without rewriting old data.
• Schema evolution by field IDs (safe renames). Branching/tagging (WAP).
• Engine-neutral: Spark, Flink, Trino, Snowflake, BigQuery, Athena, DuckDB.

Apache Hudi

• Built for upsert-heavy, incremental pipelines (born at Uber).
• Copy-on-Write (rewrite files on update, fast reads) vs Merge-on-Read (append delta logs, compact later: fast writes, reads merge on the fly).
• Built-in record-level index, incremental queries, clustering/compaction services.

Comparison

Interview answer: “All three give ACID, time travel and schema evolution on object storage. I’d choose based on the engine ecosystem: Delta if we’re Databricks-centric, Iceberg if multiple engines (Snowflake, Trino, Flink) must read and write the same tables. Interop layers like UniForm and XTable are narrowing the gap.”

4. Partitioning

Partitioning = physical directories by column value → whole-directory pruning.

Guidelines:

• Partition by a low-cardinality column that’s in almost every filter: usually a date.
• Aim for ≥ 1 GB per partition; if partitions would be smaller, partition coarser (month) or don’t partition. Tables under ~1 TB often shouldn’t be partitioned at all on modern Delta (use clustering).
• Never partition by high-cardinality columns (user_id) → millions of tiny files and directories.
• Partition column choice is (historically) hard to change → Iceberg partition evolution and Delta liquid clustering fix this.

5. The small-files problem

Causes: streaming with frequent triggers, over-partitioning, many parallel writers, high spark.sql.shuffle.partitions on small data.

Symptoms: slow queries (per-file open + task overhead), slow planning, driver OOM listing files, high object-storage request costs.

Fixes:

6. Data skipping: Z-order vs liquid clustering

flowchart LR
    subgraph Before["Random layout"]
        A1["file1: ids 1–9000"]
        A2["file2: ids 3–8800"]
        A3["file3: ids 7–9100"]
    end
    subgraph After["Clustered by id"]
        B1["file1: ids 1–3000"]
        B2["file2: ids 3001–6000"]
        B3["file3: ids 6001–9100"]
    end
    Before -->|"OPTIMIZE ... ZORDER BY (id)<br/>or CLUSTER BY (id)"| After

WHERE id = 4242 → before: all 3 files overlap → read all. After: only file2’s range matches → read one.

CREATE TABLE silver.events (...) CLUSTER BY (event_date, customer_id);
OPTIMIZE silver.events;   -- incrementally clusters new data

7. Maintenance operations cheat-sheet

8. Interview questions