Apache Iceberg & Open Table Formats — ACID on S3

This lesson on Apache Iceberg & Open Table Formats — ACID on S3 is hands-on and example-driven. You will master the hierarchical metadata architecture of Apache Iceberg to achieve ACID guarantees, schema enforcement, and time travel over raw object storage like AWS S3. You will be able to trace how queries navigate from catalogs down to raw Parquet files and contrast lakehouse table formats with traditional data warehouses.

What You'll Be Able To Do

• Diagram the four-tier Iceberg structural hierarchy from Catalog to raw Data Files.
• Differentiate schema enforcement and transactional guarantees between traditional DW (ETL) and modern Lakehouse (ELT) paradigms.
• Trace the execution path of a point-in-time snapshot query to execute time travel.
• Explain how immutable snapshot metadata enforces ACID atomicity and isolation on object storage.
• Map data pruning operations across Manifest Lists and Manifest Files to optimize query planning.

Detailed Concept Walkthrough

1. Architectural Shift: ETL/DW to ELT/Lakehouse

Traditional architectures load structured data into proprietary data warehouses enforcing schema-on-write, whereas modern data lakehouses ingest diverse raw formats directly into object storage and enforce structure via an open table layer.

• Paradigm Shift: Traditional ETL transforms data before loading it into a centralized warehouse (schema-on-write), guaranteeing schema integrity and ACID transactions at the cost of ingestion speed and storage flexibility.
• Lakehouse Challenge: In ELT, raw files (JSON, CSV, Avro, media) land directly in cheap object storage (S3/GCS), leading to organizational chaos, lack of ACID controls, and performance degradation without a structural metadata layer.
• Table Format Solution: Open table formats like Apache Iceberg sit directly on top of raw storage to provide the schema enforcement, consistency, and durability of a data warehouse within an open lakehouse.

-- Traditional DW (Schema-on-write) vs Iceberg Lakehouse Table
-- Iceberg manages Parquet data on S3 while offering full SQL DDL/DML
CREATE TABLE lakehouse_catalog.db.events (
    event_id STRING,
    user_id BIGINT,
    event_type STRING,
    payload STRING,
    event_time TIMESTAMP
)
USING iceberg
LOCATION 's3://lakehouse-bucket/data/events/';

Key Takeaway: Iceberg bridges the gap between low-cost object storage and the reliability of a traditional data warehouse.

2. Base Layer: Data Files and Manifest Files

The foundational layers of Iceberg consist of the actual data files stored in columnar formats and manifest files that track these raw data files along with partition-level metrics.

• Data Layer: Raw data resides in immutable files (typically Parquet, ORC, or Avro) stored in object storage such as Amazon S3.
• Manifest Files: A Manifest File is an Avro-based metadata file that lists a subset of data files along with column-level metrics (min/max bounds, null counts) and partition membership.
• First Layer of Indirection: Instead of querying object storage directly by listing object prefixes, engines read manifest files to instantly prune data files that do not match the query predicate.

-- Example query benefiting from Manifest-level metadata filtering
SELECT user_id, event_type 
FROM lakehouse_catalog.db.events
WHERE event_time >= TIMESTAMP '2024-01-01 00:00:00'
  AND event_time < TIMESTAMP '2024-01-02 00:00:00';
-- Query engine uses manifest min/max stats to skip scanning non-matching Parquet files

Key Takeaway: Manifest files eliminate expensive object store directory listings by keeping an explicit, tracked inventory of data files.

3. Manifest Lists and Snapshot Organization

A Manifest List aggregates multiple Manifest Files, capturing a unified snapshot of the table state at a specific point in time.

• Hierarchy Aggregation: A Manifest List file contains an entry for every Manifest File that constitutes a specific table snapshot, including partition summaries.
• Pruning at Scale: Query planning engines check partition summaries in the Manifest List before even opening individual Manifest Files, skipping irrelevant partitions entirely.
• Snapshot Representation: Every committed write (insert, update, delete) generates a new Manifest List representing a distinct, immutable snapshot of the entire table.

-- Checking table snapshots in Iceberg
SELECT 
    snapshot_id, 
    parent_id, 
    operation, 
    manifest_list, 
    committed_at
FROM lakehouse_catalog.db.events.snapshots
ORDER BY committed_at DESC;

Key Takeaway: Manifest Lists allow Iceberg to scale metadata operations by pruning entire groups of manifest files before reading them.

4. Metadata Files, Catalog, and ACID Time Travel

The Catalog acts as the single source of truth pointing to the current Metadata File, which references all historic snapshots to guarantee ACID compliance and enable time travel.

• Metadata File Internals: The Metadata File stores table schemas, partition specs, properties, and an array of all valid snapshots pointing to their respective Manifest Lists.
• Atomic Swaps via Catalog: The Catalog (e.g., Hive Metastore, AWS Glue, DynamoDB, JDBC) maintains a pointer to the single active Metadata File; ACID commits occur via atomic compare-and-swap operations on this pointer.
• Time Travel Mechanics: Because previous snapshots and their metadata chains remain intact and immutable, querying historical data simply requires resolving an older snapshot ID from the Metadata File.

-- Time travel query using snapshot ID
SELECT * FROM lakehouse_catalog.db.events
VERSION AS OF 8923487192384712093;

-- Time travel query using timestamp
SELECT * FROM lakehouse_catalog.db.events
FOR SYSTEM_TIME AS OF TIMESTAMP '2024-01-01 12:00:00 UTC';

Key Takeaway: ACID guarantees and time travel work via atomic catalog pointer swaps over an immutable tree of snapshot metadata.

Topics Covered in Apache Iceberg & Open Table Formats — ACID on S3

1. Evolution of Data Paradigms (0:00 - 1:37) — The shift from traditional ETL workflows to lakehouse ELT architectures is examined.
2. Traditional Warehouse Structures (1:37 - 3:13) — The schema enforcement, transactional integrity, and tooling of traditional data warehouses are reviewed.
3. Data Lakehouse Challenges (3:13 - 5:04) — The challenges of managing messy data and missing schema enforcement in object storage are detailed.
4. Data Files and Manifests (5:04 - 7:09) — The base layer pairing raw columnar files with metadata manifest files is introduced.
5. Manifest List Hierarchy (7:09 - 8:36) — The aggregation of multiple manifest files into higher-level manifest lists is explained.
6. Metadata, Snapshots, and Catalog (8:36 - 11:37) — The end-to-end query resolution path and snapshot mechanism powering ACID time travel are demonstrated.

Data Engineering Cheat Sheet

• Catalog Reference — Stores the atomic pointer to the current table metadata file

  spark.sql.catalog.lake = org.apache.iceberg.spark.SparkCatalog
  

• Table Metadata File — Defines table schema, partition specs, and historical snapshot references

  s3://lakehouse-bucket/metadata/v3.metadata.json
  

• Manifest List — Aggregates manifest files that compose a specific point-in-time snapshot

  s3://lakehouse-bucket/metadata/snap-892348-1.avro
  

• Manifest File — Tracks individual data files and their partition/column statistics

  s3://lakehouse-bucket/metadata/984b2c8a-manifest.avro
  

• VERSION AS OF — Queries table state at a specific snapshot ID (time travel)

  SELECT * FROM db.events VERSION AS OF 10923847293847
  

• SYSTEM_TIME AS OF — Queries table state as it existed at a past timestamp

  SELECT * FROM db.events FOR SYSTEM_TIME AS OF '2024-01-01 00:00:00'
  

Comparison Table

Common Pitfalls

• Mistake: Relying on directory listing operations on S3 to discover table partitions. Avoid: Use Iceberg manifest files to look up explicit data file paths without object scans.
• Mistake: Assuming updates overwrite raw Parquet data files in place. Avoid: Recognize that writes produce new immutable data and metadata files linked by a new snapshot.
• Mistake: Bypassing the Iceberg catalog when writing or committing table changes. Avoid: Always commit metadata updates through the designated catalog to preserve atomicity and isolation.

FAQs

• How does Iceberg achieve atomic commits on S3 without POSIX file locking? Iceberg writes immutable metadata files first, then performs an atomic compare-and-swap pointer update in the Catalog to finalize the transaction.
• What is the structural difference between a Manifest List and a Manifest File? A Manifest List tracks an entire snapshot by pointing to multiple Manifest Files; a Manifest File points directly to the underlying raw data files.
• Why does time travel not break when new data is inserted or deleted? Historical data files and metadata files remain unchanged; previous snapshots in the metadata file retain valid references to their original Manifest Lists.