6.3 Delta Lake Audit History & Commit Logs

Delta Lake Audit History & Commit Logs

At the core of the Databricks Lakehouse architecture is Delta Lake, an open-source storage layer that brings ACID (Atomicity, Consistency, Isolation, Durability) transactions, data versioning, and operational auditability to cloud object storage. For data analysts, Delta Lake's transaction log provides an immutable audit trail and historical time travel capabilities.

---

The Delta Lake Transaction Log (_delta_log/) Architecture

Delta Lake achieves ACID guarantees by maintaining an ordered, single source of truth called the Transaction Log (or Commit Log) stored in a hidden _delta_log/ sub-directory at the root of every Delta table.

/mnt/analytics/sales_fact/
├── _delta_log/
│   ├── 00000000000000000000.json       <-- Initial table creation commit (v0)
│   ├── 00000000000000000001.json       <-- Batch append commit (v1)
│   ├── 00000000000000000002.json       <-- UPDATE commit (v2)
│   ├── ...
│   ├── 00000000000000000010.json       <-- Commit v10
│   └── 00000000000000000010.checkpoint.parquet <-- State Checkpoint (v0-v10 rollup)
├── part-00000-c000.snappy.parquet      <-- Active data file
├── part-00001-c000.snappy.parquet      <-- Active data file
└── part-00002-c000.snappy.parquet      <-- Tombstoned data file (after UPDATE/DELETE)

Commit Files (.json) and Checkpoints (.checkpoint.parquet)

1. JSON Commit Logs: Every mutation (insert, update, delete, merge, optimize) writes an atomic commit file formatted as a 20-digit zero-padded JSON file (e.g., 00000000000000000001.json). Each file records actions such as adding data files (add), removing data files (remove), or updating table metadata (metaData).
2. Parquet Checkpoints: Reading hundreds of JSON commit files to compute current table state would create high metadata latency. To optimize table reading, Delta Lake automatically generates a Checkpoint file in Parquet format every 10 commits (.checkpoint.parquet). The checkpoint aggregates all actions up to that version, enabling readers to reconstruct table state instantaneously.

---

Auditing Table Operations with DESCRIBE HISTORY

Data analysts can inspect the complete operational lineage of any Delta table using the DESCRIBE HISTORY SQL statement.

-- Audit full history of the financial_ledger table
DESCRIBE HISTORY analytics.financial_ledger;

-- Limit history retrieval to the 5 most recent operations
DESCRIBE HISTORY analytics.financial_ledger LIMIT 5;

Essential Audit Columns in DESCRIBE HISTORY

---

Time Travel Queries & Historical Data Access

Because Delta Lake uses copy-on-write or merge-on-read mechanisms, modifying records does not overwrite physical underlying Parquet files; instead, modified files are marked as removed (tombstoned) in the commit log while new Parquet files are added. This architecture enables Time Travel.

Time Travel SQL Syntax Options

Analysts can query historical snapshots using either Version Number or Timestamp:

-- 1. Time Travel using Version Number
SELECT * FROM analytics.financial_ledger VERSION AS OF 14;

-- 2. Time Travel using Timestamp (RFC 3339 or standard ISO format)
SELECT * FROM analytics.financial_ledger TIMESTAMP AS OF '2026-04-15 14:30:00';

-- 3. Time Travel using Delta path syntax
SELECT * FROM delta.`/mnt/analytics/financial_ledger@v14`;

Restoring Historical Table States

If an incorrect bulk update or accidental row deletion occurs, analysts can restore the table to a known good historical state using RESTORE TABLE:

-- Restore table state back to Version 12
RESTORE TABLE analytics.financial_ledger TO VERSION AS OF 12;

Restoring a table creates a new commit version (e.g., version 15) recording a RESTORE operation, preserving complete auditing transparency.

---

Interplay Between OPTIMIZE, VACUUM, and Audit Trails

Table maintenance commands alter physical data layout and file retention:

1. OPTIMIZE (File Compaction & Z-Ordering)

Compacts small Parquet files into larger ~1 GB files to accelerate read queries.

• Audit Impact: Creates a new commit version (operation: "OPTIMIZE"). It marks small files as removed and adds compacted files. Time travel remains fully functional.

2. VACUUM (Data File Garbage Collection)

Permanently deletes physical data files that were tombstoned in commit logs and are older than the retention threshold.

• Default Retention: 7 days (168 hours) (spark.databricks.delta.vacuum.parallelDelete.enabled check).
• Audit & Time Travel Impact: Once VACUUM executes, physical data files prior to the retention threshold are deleted from object storage. Attempting to time travel to a version whose underlying data files were vacuumed fails with a FileNotFoundException.

-- Remove tombstoned files older than 168 hours (default)
VACUUM analytics.financial_ledger;

-- Retain files for custom window (7 days)
VACUUM analytics.financial_ledger RETAIN 168 HOURS;

---

Real-World Scenario: Compliance Audit & Data Recovery

During a quarterly financial audit, a compliance director discovers that 50,000 customer records disappeared from analytics.customer_master between 08:00 UTC and 12:00 UTC on May 1st.

1. Executing Audit History: The analyst runs DESCRIBE HISTORY analytics.customer_master and reviews recent commits:

   version | timestamp           | userName        | operation | operationParameters
   18      | 2026-05-01 09:14:22 | rogue_script    | DELETE    | {"predicate": "['status = ACTIVE']"}
   

2. Identifying Root Cause: Version 18 shows a DELETE operation triggered by rogue_script at 09:14:22 UTC with an overly broad predicate.
3. Historical Verification: The analyst verifies record counts before the deletion using time travel:

   SELECT COUNT(*) FROM analytics.customer_master VERSION AS OF 17; -- 500,000 records
   SELECT COUNT(*) FROM analytics.customer_master VERSION AS OF 18; -- 450,000 records
   

4. Data Recovery: The analyst restores the table to Version 17 prior to business impact:

   RESTORE TABLE analytics.customer_master TO VERSION AS OF 17;
   

   The table is restored in seconds, and DESCRIBE HISTORY logs Version 19 as a RESTORE operation, satisfying regulatory compliance requirements.