Delta Lake: The Transaction Log

Every table in the previous lesson was a Delta table. That means a directory of Parquet files plus a _delta_log that records what the table looked like at each version — and that log is the whole reason a pile of files in object storage behaves like a database.

What is on disk

dbutils.fs.ls("s3://bookshop-lake/raw/orders/")

[FileInfo(path='.../orders/_delta_log/', name='_delta_log/', size=0),
 FileInfo(path='.../orders/part-00000-8f2c1a44-...c000.snappy.parquet', name='part-00000-...', size=2048)]

dbutils.fs.ls("s3://bookshop-lake/raw/orders/_delta_log/")

[FileInfo(path='.../_delta_log/00000000000000000000.json', size=1204),
 FileInfo(path='.../_delta_log/00000000000000000001.json', size=988)]

One JSON file per commit, numbered in order. Each records the files added and removed:

print(dbutils.fs.head("s3://bookshop-lake/raw/orders/_delta_log/00000000000000000001.json", 600))

{"commitInfo":{"timestamp":1789459200000,"operation":"WRITE","operationParameters":{"mode":"Append"},"operationMetrics":{"numFiles":"1","numOutputRows":"5","numOutputBytes":"2048"}}}
{"add":{"path":"part-00000-8f2c1a44-...c000.snappy.parquet","size":2048,"modificationTime":1789459200000,"dataChange":true,"stats":"{\"numRecords\":5,\"minValues\":{\"order_id\":1001,\"amount\":8.75},\"maxValues\":{\"order_id\":1005,\"amount\":63.20}}"}}

Two things worth noticing. The commit is a single small JSON write, which is what makes it atomic — readers either see it or they do not. And each file carries stats with min and max per column, which is how Delta skips files at query time without an index.

History

describe history bookshop.raw.orders;

version  timestamp            operation  operationParameters      operationMetrics
-------  -------------------  ---------  -----------------------  ------------------------------------------
      1  2026-09-09 09:14:02  WRITE      {mode: Append}           {numFiles: 1, numOutputRows: 5}
      0  2026-09-09 09:14:00  CREATE TABLE {isManaged: true}      {}

Now make a mistake:

update bookshop.raw.orders set amount = amount * 100;

num_affected_rows
-----------------
                5

select order_id, amount from bookshop.raw.orders order by order_id limit 3;

order_id  amount
--------  -------
    1001  2550.00
    1002  1200.00
    1003  4000.00

describe history bookshop.raw.orders limit 2;

version  timestamp            operation  operationMetrics
-------  -------------------  ---------  -----------------------------------------------------
      2  2026-09-09 09:22:41  UPDATE     {numUpdatedRows: 5, numCopiedRows: 0, numOutputRows: 5}
      1  2026-09-09 09:14:02  WRITE      {numFiles: 1, numOutputRows: 5}

Time travel

select order_id, amount from bookshop.raw.orders version as of 1 order by order_id limit 3;

order_id  amount
--------  ------
    1001   25.50
    1002   12.00
    1003   40.00

select order_id, amount from bookshop.raw.orders timestamp as of '2026-09-09 09:20:00' limit 3;

order_id  amount
--------  ------
    1001   25.50
    1002   12.00
    1003   40.00

The UPDATE did not modify a file — Delta wrote a new Parquet file and logged the old one as removed. Version 1 still points at the original file, so reading it is a normal query.

Restoring is one statement:

restore table bookshop.raw.orders to version as of 1;

table_size_after_restore  num_of_files_after_restore  num_removed_files  num_restored_files
------------------------  --------------------------  -----------------  ------------------
                    2048                           1                  1                   1

RESTORE is itself a new commit rather than a rewrite of history, so you can undo the undo. The whole audit trail stays intact — important when someone asks what the table looked like before the fix.

MERGE

The statement that does the real work in a lakehouse — upserts in one atomic operation:

create or replace temp view orders_updates as
select * from values
    (1003, 1, date'2026-01-07', 'refunded',  40.00),   -- status changed
    (1006, 4, date'2026-01-14', 'completed', 31.20)    -- new row
    as t(order_id, customer_id, ordered_at, status, amount);

merge into bookshop.raw.orders t
using orders_updates s
on t.order_id = s.order_id
when matched then update set *
when not matched then insert *;

num_affected_rows  num_updated_rows  num_deleted_rows  num_inserted_rows
-----------------  ----------------  ----------------  -----------------
                2                 1                 0                  1

select order_id, status, amount from bookshop.raw.orders order by order_id;

order_id  status     amount
--------  ---------  ------
    1001  completed   25.50
    1002  completed   12.00
    1003  refunded    40.00
    1004  completed    8.75
    1005  pending     63.20
    1006  completed   31.20

update set * and insert * map columns by name, which keeps the statement short and survives a new column being added to both sides. Spell the columns out when the source and target genuinely differ.

MERGE is idempotent for a given source: run it twice and the second run reports one updated row and zero inserted, rather than duplicating. That property is what makes a retried pipeline step safe.

Conditional clauses handle deletes and let you skip no-op updates:

merge into bookshop.raw.orders t
using orders_updates s on t.order_id = s.order_id
when matched and s.status = 'cancelled' then delete
when matched and t.status <> s.status then update set status = s.status
when not matched then insert *;

num_affected_rows  num_updated_rows  num_deleted_rows  num_inserted_rows
-----------------  ----------------  ----------------  -----------------
                1                 0                 0                  1

Zero updates this time — the t.status <> s.status guard skipped the row whose status already matched. On a wide table that guard avoids rewriting files for no reason.

Schema enforcement

from pyspark.sql import Row

bad = spark.createDataFrame([Row(order_id=1007, customer_id=2, amount="twelve pounds")])
bad.write.mode("append").saveAsTable("bookshop.raw.orders")

AnalysisException: Failed to merge fields 'amount' and 'amount'.
Failed to merge incompatible data types DecimalType(10,2) and StringType

A schema mismatch detected when writing to the Delta table.
To enable schema migration using DataFrameWriter or DataStreamWriter,
please set: '.option("mergeSchema", "true")'.

Rejected before anything was written. A plain Parquet directory would have accepted this happily and produced a table that fails at read time — usually months later, for someone else.

Adding a genuinely new column is allowed when you ask for it:

from pyspark.sql.functions import lit

(spark.table("bookshop.raw.orders").limit(1)
    .withColumn("channel", lit("web"))
    .write.mode("append").option("mergeSchema", "true")
    .saveAsTable("bookshop.raw.orders"))

OK

describe bookshop.raw.orders;

col_name     data_type
-----------  -------------
order_id     bigint
customer_id  bigint
ordered_at   date
status       string
amount       decimal(10,2)
channel      string

Existing rows get NULL for the new column. Type changes still need an explicit overwriteSchema and a rewrite — widening a column is not free.

Concurrent writers

Delta uses optimistic concurrency: two writers proceed independently and the second to commit re-checks for conflicts.

ConcurrentAppendException: Files were added to the root of the table by a concurrent update.
Please try the operation again.
Conflicting commit: {"timestamp":1789459260000,"operation":"MERGE"}

Two writers appending to different partitions do not conflict. Two MERGE statements over overlapping rows do, and the loser must retry — which is safe, because nothing was committed. Partition or filter concurrent writers so they touch disjoint data, and retry on this exception rather than serialising everything.

Cleaning up

vacuum bookshop.raw.orders retain 168 hours dry run;

path
----------------------------------------------------------
s3://bookshop-lake/raw/orders/part-00000-3a1f...snappy.parquet
s3://bookshop-lake/raw/orders/part-00000-7c2e...snappy.parquet

vacuum bookshop.raw.orders;

path
----
s3://bookshop-lake/raw/orders

VACUUM deletes data files no longer referenced by the current version, keeping 7 days by default. The trade-off is direct: vacuuming shortens how far back you can time travel. Version 1 may still be in the log, but if its Parquet file is gone the read fails:

FileNotFoundException: s3://bookshop-lake/raw/orders/part-00000-3a1f...snappy.parquet
It is possible the underlying files have been updated or deleted by VACUUM.

Always DRY RUN first, and never lower the retention below the duration of your longest running job — a query that started before the vacuum can have its files deleted underneath it.

Practice

select sum(amount) from bookshop.raw.orders;
select sum(amount) from bookshop.raw.orders version as of 1;

sum(amount)
-----------
   14945.00

sum(amount)
-----------
     149.45

-- first run
num_updated_rows  num_inserted_rows
----------------  -----------------
               1                  1
-- second run
num_updated_rows  num_inserted_rows
----------------  -----------------
               1                  0

AnalysisException: Failed to merge incompatible data types DecimalType(10,2) and StringType

describe history bookshop.raw.orders;

version  operation  operationMetrics
-------  ---------  -------------------------------------------
      4  MERGE      {numTargetRowsInserted: 1, numTargetRowsUpdated: 1}
      3  RESTORE    {numRestoredFiles: 1}
      2  UPDATE     {numUpdatedRows: 5}
      1  WRITE      {numOutputRows: 5}
      0  CREATE TABLE {}

Next: Unity Catalog — governing all of this with grants that follow the data.