The Delta Transaction Log

A Delta table is not the Parquet files sitting in its directory. It is the set of add actions that are currently live in _delta_log. Copy a perfectly good Parquet file into the table's folder and no query will ever see it. Delete a file the log still references and every query breaks. The files are storage; the log is the table.

That much is usually understood by the second week. What is almost never understood is the price. Before a query reads one byte of your data, the engine has to rebuild the table's current state from the log, and that rebuild is a sequence of object-store requests whose cost you control with two table properties and one habit. On a busy table it is routinely the slowest part of a small query.

Below is a Delta table being written continuously. The panel shows exactly what a reader does when it opens the table, and where the milliseconds go. Start by dragging commits since the last checkpoint, then tick _last_checkpoint is missing, and watch which of the three cost segments takes over.

On the defaults the table opens in about 200 milliseconds and 12 round trips: read a pointer, list ten objects, read one checkpoint, read nine tiny JSON files. Now untick _last_checkpoint. Nothing about the table changed — same data, same commits — and the same open now costs about three seconds and 58 round trips, because the reader has to list all 47,000 objects in the directory to find out where the newest checkpoint is.

What one commit contains

Each commit is a file named after its version number, zero-padded to twenty digits: _delta_log/00000000000000000042.json. The padding is not decoration. It makes the directory sort lexicographically in version order, which is the only reason a reader can ask an object store for "everything from version 42 onwards" — more on that below.

Inside is newline-delimited JSON, one action per line. A commit is the atomic unit: either the whole file lands or none of it does. The action types you will actually see are these.

• add — a file joins the table. Carries the path, the partition values, the size, and the per-file statistics (row count, and minimum and maximum values per column) that make file skipping possible.
• remove — a file leaves the table. The file is not deleted from storage; this is a tombstone, and it stays in the table's state until VACUUM collects it.
• metaData — schema, partition columns, table properties.
• protocol — the minimum reader and writer versions, and the named table features a client must implement.
• txn — a streaming query's application id and the last batch it committed. This is how a Structured Streaming sink is exactly-once.
• domainMetadata — namespaced configuration. Clustering keys live here, under the domain delta.clustering.
• commitInfo — provenance: the operation name, its parameters, the metrics. This is what DESCRIBE HISTORY reads.

To get the table as of version v, a reader replays every action from version 0 to v in order and applies four rules, which the protocol calls action reconciliation:

• The latest protocol wins. The latest metaData wins.
• For txn, the latest version per appId wins. For domainMetadata, the latest per domain wins.
• A logical file is identified by the pair (path, deletionVector.uniqueId), not by path alone. Keep the newest action for each logical file. If that action is an add, the file is in the table; if it is a remove, it is a tombstone that only VACUUM cares about.
• commitInfo is kept only from the commit at the version being read, which is why checkpoints do not carry it and why history has to come from the JSON files.

That third rule is the load-bearing one. Because identity includes the deletion vector, the same Parquet path can legitimately appear in a remove and an add in the same commit with different vectors — that is precisely what a deletion-vector delete is: remove (f.parquet, null), add (f.parquet, dv-7). If you reconcile by path alone, you conclude the file was deleted and re-added, and you get the row count wrong.

Now the two consequences that catch people. A Parquet file with no add action is not in the table, so copying files into the directory does nothing and you need CONVERT TO DELTA to write the actions. And a file with a remove action is still on storage, so DELETE FROM t frees no space until VACUUM runs. This is the same tombstone-then-reclaim shape as Postgres MVCC and vacuum, with object storage where the heap would be.

Checkpoints fix the tail, not the width

Replaying from version 0 every time would be absurd, so writers periodically collapse the whole replay into one Parquet file: 00000000000000008640.checkpoint.parquet. It contains the reconciled state as of that version — every live add, every unexpired remove tombstone, the protocol, the metadata, the transaction identifiers and the domain metadata — with commitInfo and change-data actions deliberately dropped. A reader that finds it can start there and replay only the JSON files after it.

Open-source Delta writes one every delta.checkpointInterval commits, default 10. That bounds the tail: a reader replays at most nine JSON files. Set the panel's tail slider to 500 to see what happens when that breaks — a client that does not write checkpoints, or a checkpoint job that keeps failing — and the open cost goes to 1.7 seconds and 503 round trips, almost all of it fetching tiny files one at a time.

Here is the part the word "checkpoint" hides. A checkpoint is not a summary. It has one row per live file. Its cost does not scale with how many commits you made; it scales with how many files your table currently has. Set the write rate to one commit a second and leave everything else alone: the tail is still nine files, the listing is still one page, and the table now takes 2.8 seconds to open — 2.6 of them reading a checkpoint with 5.2 million rows.

So the answer to "we have millions of small files, but our queries only touch one day, so who cares" is: every query cares, before it starts. The file count is a metadata cost paid on every table open, which is the part of the small-file problem that is invisible in a scan profile. Two things widen the checkpoint further than the file count alone: the per-column statistics stored inside each add, which is why delta.dataSkippingNumIndexedCols defaults to 32 columns and not all of them; and remove tombstones, which stay in the checkpoint for delta.deletedFileRetentionDuration — one week by default — so a table that rewrites itself nightly carries seven days of ghosts.

The mitigations exist and are worth naming. V2 checkpoints move the file actions into sidecar Parquet files in _delta_log/_sidecars/ and leave a small top-level checkpoint that references them, so a writer can produce a new checkpoint without rewriting every row. Tables created with clustering on Databricks Runtime 14.3 LTS and above use them by default. Log compaction files, named <start>.<end>.compacted.json, pre-reconcile a range of commits so a reader can substitute one object for many. Both shrink the constant; neither changes the fact that the width is your file count.

Why listing is what actually gets slow

The protocol states the problem in its own words: the log "will often contain many (e.g. 10,000+) files. Listing such a large directory can be prohibitively expensive."

Object stores do not have directories. A listing is a paginated scan over keys sorted as strings, a thousand keys per request, each request a separate round trip that cannot start until the previous one returns its continuation token. A _delta_log with 47,000 objects is 48 sequential requests, and at a realistic 60 milliseconds each that is three seconds of doing nothing but reading file names.

The escape is one small file, _delta_log/_last_checkpoint, which holds the version number of a recent checkpoint. Because version numbers are zero-padded to a fixed width, that number can be turned back into a key prefix, and the reader can ask the store to list starting from that key. In Delta's own code the call is literally listFrom(startVersion) against the padded prefix. It returns the checkpoint and the handful of JSON files after it — ten objects instead of 47,000, one request instead of 48.

So _last_checkpoint is not a cache or an optimisation you can take or leave. It is the difference between listing a bounded suffix and listing the entire history. Tick the box in the panel and the cost model falls back to startVersion = 0, which is what Delta genuinely does when the pointer is absent, stale, or points at a checkpoint that no longer exists. You meet this in real life when a third-party writer commits without updating the pointer, when a table is copied file-by-file to another bucket, or when a restore leaves the pointer behind.

Now set the age slider to 90 days with the write rate at one commit a minute and the pointer missing, then toggle metadata cleanup off and on. Cleanup on: 47,000 objects, three seconds. Cleanup off: 142,000 objects, nearly nine seconds. Retention is a read-latency setting, not only a storage setting.

Retention: two clocks, and they are not the same clock

Two properties look interchangeable and govern different things.

delta.logRetentionDuration, default 30 days, controls how long log entries live. Metadata cleanup deletes commits and checkpoints older than the cutoff, but it must leave a checkpoint at the oldest kept version so the remaining log is still replayable. That is why DESCRIBE HISTORY stops 30 days back, and why time travel fails with the strangely specific Cannot time travel Delta table to version 1. Available versions: [10, 22]. Version 1 is not corrupt. Its JSON file was deleted, and the oldest surviving checkpoint is at version 10.

delta.deletedFileRetentionDuration, default 7 days, controls how long data files that have been removed remain eligible to sit on storage. VACUUM refuses to delete anything newer than this, and the reason is concurrent readers: a query that built its snapshot ten minutes ago still holds paths to files that a later commit removed. Delete them early and that query fails mid-scan with a missing-file error.

Delta validates the pair on every write: logRetentionDuration must be greater than or equal to deletedFileRetentionDuration. Raising the file retention to 30 days without raising the log retention is rejected, because it would leave you with data files whose defining commits no longer exist.

The failure mode to plan around is a snapshot that outlives the retention window: a long-running job, a notebook left open, or a Delta Sharing consumer that holds a version for longer than a week. The engineering answer is not to push retention to a year — that widens every checkpoint and every listing, as the panel shows. It is to shorten the snapshot.

Checking it yourself

List the log directly and read the shape of the problem off the file names:

%fs ls dbfs:/mnt/lake/events/_delta_log/

Count the .json files between the newest .checkpoint.parquet and the highest version. More than delta.checkpointInterval of them means checkpoint writing is failing or disabled, and every reader is paying for it. Then cat the pointer:

{"version":8640,"size":412037,"sizeInBytes":91238400,
 "numOfAddFiles":412001,"checkpointSchema":{...}}

size is the row count of the checkpoint and numOfAddFiles is your live file count. If that number is in the millions, your table-open latency is that number divided by a few million rows per second, on every query, from every cluster that does not already have the snapshot cached.

In the Spark UI, this time appears before the first job — it is driver time, not task time, so it is invisible in the stage timeline and shows up as an unexplained gap. On Databricks, DESCRIBE DETAIL my_table gives numFiles and sizeInBytes directly; divide them. If the average file is under about 16 MB, the metadata is the workload.

Finally, check SHOW TBLPROPERTIES my_table for delta.checkpointPolicy. classic means every checkpoint rewrites every row; v2 means sidecars. And read delta.logRetentionDuration before you go looking for a version that no longer exists.

Read next: why file count is a metadata problem first, and how two writers race to append the next commit — conflict detection is log replay pointed at a rival's commit instead of at your own.