Lakehouse - Delta Lake & Best Practices: Theoretical Quiz

This assessment details Delta Lake transaction log mechanics, ACID compliance, and OPTIMIZE/Z-Ordering optimizations.

Scenario 1: The Delta Lake Transaction Log (_delta_log/) & ACID Compliance

The Scenario

A high-throughput ETL job continually appends records to a Delta Lake table:

df.write.format("delta").mode("append").save("/mnt/delta/customers")

At the same time, analytical users are querying the table. No writer blocking occurs, and readers always see a consistent state.

The Questions

1. Detail how the Delta Transaction Log (JSON commit files) achieves ACID transactions over standard object storage (like AWS S3 or HDFS).
2. What is Optimistic Concurrency Control (OCC), and how does Delta resolve commit conflicts when two writers commit simultaneously?

Detailed Solution & Architectural Analysis

1. Delta Transaction Log Mechanics

Delta Lake stores tables as standard Parquet files. However, it implements a transaction layer using metadata log files inside a subdirectory called _delta_log/.

• Commit Files: Every write transaction generates a single JSON commit file: 00000000000000000000.json, 00000000000000000001.json, etc.
• Commit Content: Each JSON file details the exact actions performed: which physical Parquet files were added and which were deleted (logically removed) during that transaction.
• Single Source of Truth: When a reader queries the table, Spark parses the JSON commit history sequentially, building the current active list of physical Parquet files. It completely ignores files marked as deleted or files written by uncommitted transactions, achieving strict Read Isolation and Consistency.
• Checkpoints: Every 10 commits, Delta merges the JSON commits into a single Parquet checkpoint file (00000000000000000010.checkpoint.parquet), preventing readers from having to parse millions of small JSON log files.

2. Optimistic Concurrency Control (OCC)

Delta uses OCC to manage multi-user transactions:

• Optimistic Assumptions: Delta assumes that multiple writers can write data without conflicting. When a transaction starts, it records the active table version (e.g. Version 5).
• Commit Validation: When the transaction completes, Delta checks if another writer committed a change (creating Version 6) while it was running.
• Conflict Resolution:
  • If no overlapping changes occurred, Delta commits the new transaction as Version 7.
  • If a conflict occurs (e.g., both attempted to delete the same row ranges), Delta rolls back the transaction, updates to the latest table version, and automatically retries the operation in a safe loop.

Scenario 2: File Compaction and Z-Ordering Indexing

The Scenario

A Delta table is updated by multiple micro-batch streaming jobs, creating 150,000 tiny Parquet files (average size: 200KB). Queries scanning this table slow down heavily due to "many tiny files" metadata overheads.

The Questions

1. What is the execution mechanism of the OPTIMIZE command, and how does it resolve file system performance degradation?
2. Explain how Z-Ordering clusters multidimensional data to maximize data skipping during query scans.

Detailed Solution & Architectural Analysis

1. OPTIMIZE (Compaction) Mechanics

Small files degrade storage networks because each file read requires a separate metadata handshake.

• Compaction: The OPTIMIZE command instructs Spark to read the thousands of tiny Parquet files in parallel and rewrite their contents into larger, uniform Parquet files (typically target size 1 Gigabyte).
• Transaction Update: Delta writes a new JSON commit file marking the old tiny Parquet files as deleted (logically) and the new 1GB compacted files as added. Old files remain on disk for Time Travel safety but are ignored by new queries.

2. Z-Ordering Indexes

Z-Ordering is a multidimensional clustering algorithm:

• Data Clustering: When you run OPTIMIZE table Z-ORDER BY (country, category), Spark maps the selected columns along a space-filling curve (Z-curve).
• Locality Preservation: It organizes the records so that rows sharing similar values for both columns are physically grouped into the same Parquet files.
• Data Skipping: This clustering narrow the min/max statistics for these columns within each Parquet file. When queries filter by country or category, Spark skips reading up to 90% of the physical files, accelerating scans.

Scenario 3: Time Travel Mechanics and Historical Reads

The Scenario

A business analyst discovers a data corruption bug that occurred in an ETL run 2 hours ago. They need to inspect the data state prior to that run (Version 12) and restore the table.

The Questions

1. Explain how Delta Lake structures Time Travel queries without keeping duplicate copies of unchanged rows.
2. How does the VACUUM command interact with Time Travel capabilities?

Detailed Solution & Architectural Analysis

1. Time Travel Execution Mechanics

• No File Duplication: Delta Lake never overwrites Parquet files. If an update modifies 10 rows, it writes a new Parquet file containing the updated rows and creates a new JSON commit marking the old file as logically deleted.
• Lineage Replay: When the query runs spark.read.format("delta").option("versionAsOf", 12).load(), Spark ignores all JSON commits after Version 12. It builds the physical file list exactly as it stood at Version 12, allowing immediate historical reads.

2. VACUUM Metadata Purging

• The Problem: Over time, thousands of logically deleted Parquet files accumulate on disk, leading to high storage costs.
• The Command: VACUUM physically deletes files that are no longer in the active table state and are older than a specified retention threshold (default: 7 days).
• The Hazard: Once VACUUM deletes these historical files, time-travel queries attempting to read states older than the vacuum threshold will fail with a file-not-found error, as the physical source bytes are gone.

Scenario 4: Optimistic Concurrency Control Conflict Policies

The Scenario

Two concurrent YARN streams commit updates to the same Delta table. Writer A updates metadata, and Writer B appends rows.

The Questions

1. In what scenarios does OCC allow simultaneous commits without throwing a conflict exception?
2. What write operations will trigger a mandatory commit rollback?

Detailed Solution & Architectural Analysis

1. Conflict-Free Commits

Delta OCC analyzes the specific actions inside the JSON commit logs:

• If Writer A only adds partition directories (year=2026/month=05), and Writer B appends rows to a separate partition (year=2025/month=10), Delta recognizes that their operations are mutually exclusive. It commits both versions without any rollback.

2. Mandatory Conflict Exceptions

A rollback is triggered when:

1. Row Collision: Writer A and Writer B attempt to update/delete rows sharing the exact same file blocks.
2. Partition Overlaps: Both attempts to overwrite the same partition path simultaneously, creating concurrency collisions.