Delta Tables & the Transaction Log

The one-liner

What the log gives you — click each

▸ click a superpower

Why it matters

• It's why cheap files in S3/ADLS/GCS behave like a warehouse table — reliable reads/writes, no corruption, full history, fast filtered scans.
• The classic interview question — "How does Delta give ACID on object storage that has no transactions?" — answers itself: the transaction log + optimistic concurrency.

How it works — deep dive

1 · Physical layout: Parquet + _delta_log/

A Delta table on disk is just data files + a log directory. Data files are immutable — an "update" writes new files and tombstones old ones in the log.

/orders/                                  # table root
├── part-00000-....snappy.parquet         # immutable Parquet data
├── part-00001-....snappy.parquet
└── _delta_log/                           # the transaction log
    ├── 00000000000000000000.json         # commit 0 (atomic unit)
    ├── 00000000000000000001.json         # commit 1
    ├── 00000000000000000010.checkpoint.parquet  # written periodically
    └── _last_checkpoint                   # pointer to newest checkpoint

Inspect the log from SQL

DESCRIBE HISTORY main.sales.orders;   -- one row per commit: version, op, user, metrics
DESCRIBE DETAIL  main.sales.orders;   -- numFiles, sizeInBytes, location, format

2 · What lives inside a commit (the "actions")

Each JSON commit is a list of actions — the diff applied at that version. The mental shift: a write is not an edit, it's a set of add/remove records appended to the log.

3 · Snapshot reconstruction — how a write becomes a version

To read the table, Delta nets add minus remove across the log (starting from the newest checkpoint) to compute the exact live file list. Checkpoints (cadence set by delta.checkpointInterval — default is runtime-dependent: 100 on recent Databricks Runtimes, 10 in OSS Delta) keep this O(recent commits).

▸ click a version

4 · ACID via atomic commits + optimistic concurrency (OCC)

Writers don't lock. Each writer reads a snapshot, does its work, then tries to commit the next version number with a put-if-absent write. If another writer got there first, the loser detects the conflict and retries. Walk the flow:

▸ step through two concurrent writers

Avoid conflicts by scoping writers to disjoint partitions

# Each job writes only its own region — OCC won't treat them as conflicting
(df_us.write.format("delta").mode("append")
   .option("replaceWhere", "region = 'US'")   # disjoint predicate
   .saveAsTable("main.sales.orders"))
# A parallel job using replaceWhere "region = 'EU'" commits cleanly in parallel.

Conflict types interviewers probe: ConcurrentAppendException (overlapping partition/predicate), ConcurrentDeleteReadException / ConcurrentDeleteDeleteException (a file you read/deleted was deleted), MetadataChangedException (schema changed under you).

5 · Schema enforcement (write-time validation)

Delta validates the incoming schema on every write and rejects mismatches. Enforcement rejects; evolution (opt-in) adds columns — don't confuse them.

# This APPEND fails fast — `bonus` is not a column in the target table
bad = spark.createDataFrame([(1, "ACME", 99.0)], ["id", "name", "bonus"])
bad.write.format("delta").mode("append").saveAsTable("main.sales.customers")
# AnalysisException: A schema mismatch detected when writing to the Delta table.

# Opt in to evolution only when you *intend* to add the column:
(bad.write.format("delta").mode("append")
    .option("mergeSchema", "true")            # adds `bonus` to the table
    .saveAsTable("main.sales.customers"))

6 · Data skipping via column statistics

Each add stores min/max/nullCount for the first 32 columns (delta.dataSkippingNumIndexedCols). A filtered query reads those stats from the log and skips whole Parquet files whose range can't match — often 10–100× less data scanned. OPTIMIZE/ZORDER/liquid clustering (Topic 3) tighten those ranges to make skipping far more effective.

-- Delta reads file-level min/max from the log and skips non-matching files
SELECT * FROM main.sales.orders
WHERE order_date BETWEEN '2024-01-01' AND '2024-01-07';

7 · Version history & time travel

Because the log keeps every commit, you can read the table as of any past version/timestamp (within retention) and roll back with one command. Compare the two APIs:

SELECT * FROM main.sales.orders VERSION AS OF 12;            -- by version
SELECT * FROM main.sales.orders TIMESTAMP AS OF '2024-01-15';  -- by time

-- Roll a bad load back (writes a NEW commit; no data loss)
RESTORE TABLE main.sales.orders TO VERSION AS OF 12;

df = (spark.read
        .option("versionAsOf", 12)        # or .option("timestampAsOf", "2024-01-15")
        .table("main.sales.orders"))

# Restore via the Delta table API
from delta.tables import DeltaTable
DeltaTable.forName(spark, "main.sales.orders").restoreToVersion(12)

How to do it — from scratch

catalog, schema = "main", "de_demo"
spark.sql(f"CREATE SCHEMA IF NOT EXISTS {catalog}.{schema}")
spark.sql(f"USE {catalog}.{schema}")

# Delta is the default — no USING DELTA needed
spark.sql("""
  CREATE TABLE IF NOT EXISTS orders (
    order_id BIGINT, customer STRING,
    amount DECIMAL(10,2), order_date DATE
  )
  TBLPROPERTIES (delta.checkpointInterval = 10)   -- explicitly set checkpoint cadence
""")

# Each write = a new version = a new JSON commit in _delta_log/
spark.sql("INSERT INTO orders VALUES (1,'ACME', 250.00, DATE'2024-01-03')")
spark.sql("DESCRIBE HISTORY orders").show(truncate=False)

Delta vs. plain Parquet/CSV "tables"

Uses, edge cases & limitations

• Uses: every bronze/silver/gold table; concurrent batch + streaming ETL; reliable upserts, audits, rollbacks; CDC targets.
• Edge cases: the small-files / tiny-commits problem — committing per-row bloats the log and creates thousands of small files; batch writes and use OPTIMIZE (Topic 3). Concurrent writes to the same files raise a conflict the writer must retry (or avoid via disjoint replaceWhere).
• Limitations: VACUUM physically deletes files past the retention window (default 7 days) — after that, old versions aren't queryable. Delta is for analytical workloads, not high-frequency OLTP single-row point writes.

Common gotchas

• ❌ Hand-editing or deleting files in _delta_log/ — corrupts the table. Always use Delta operations.
• ❌ Assuming time travel is forever — VACUUM + retention decide how far back you can go.
• ❌ Confusing schema enforcement (rejects by default) with schema evolution (opt-in, adds columns).
• ❌ Committing per row in a loop — explodes commit + small-file count. Write in batches.
• ❌ Saying "Delta locks the table for writers" — it's optimistic concurrency (no locks; detect-and-retry).