Delta Lake Table History: What DESCRIBE HISTORY Actually Tells You

Shannon Lowder

Shannon Lowder

— 2 min read

One of the first questions I get from SQL Server DBAs when they start using Delta Lake is some version of: "How do I know what happened to this table?" In SQL Server, the answer is usually "it depends" — you might have CDC enabled, or an audit trigger, or a custom history table, or nothing at all, in which case you're looking at transaction log backups. It varies wildly by setup.

In Delta Lake, every table has a built-in audit log. No setup required. It's just there.

DESCRIBE HISTORY

-- Spark SQL
DESCRIBE HISTORY orders

-- Reference by path if the table isn't in the metastore
DESCRIBE HISTORY delta.`/mnt/myproject/orders`

In PySpark:

from delta.tables import DeltaTable

dt = DeltaTable.forName(spark, "orders")
display(dt.history())

The output is one row per version. Key columns:

  • version — integer starting at 0, increments with every write
  • timestamp — when the operation committed
  • operation — what happened: WRITE, MERGE, UPDATE, DELETE, CREATE TABLE, OPTIMIZE, VACUUM
  • operationParameters — details (write mode, merge predicates, filter conditions)
  • userName — who ran it
  • notebook — the notebook path, if run from a notebook
  • clusterId — which cluster executed it

The Difference Between History and Time Travel

History shows you the operations. Time travel gives you the data. They use the same version numbering but they're different commands:

-- Time travel: give me the data as it was at version 5
SELECT * FROM orders VERSION AS OF 5

-- Time travel: give me the data as it was on a specific date
SELECT * FROM orders TIMESTAMP AS OF '2019-01-15 00:00:00'

-- History: tell me what operations happened (metadata only)
DESCRIBE HISTORY orders LIMIT 10

The practical workflow: you notice something wrong with the data today. You run DESCRIBE HISTORY to find the version where the bad write happened and who ran it. You use VERSION AS OF to read what the table looked like before that write. You decide whether to restore or patch.

Restoring a Table to a Previous Version

-- Restore orders to version 12
RESTORE TABLE orders TO VERSION AS OF 12

-- Or by timestamp
RESTORE TABLE orders TO TIMESTAMP AS OF '2019-01-20 08:00:00'

RESTORE creates a new version in the history — it doesn't erase the bad version, it creates a new one that matches the old state. The audit trail stays complete. You can always see that a restore happened and who did it.

How Long Does History Last?

By default, Delta keeps 30 days of history. You can change this per table:

ALTER TABLE orders SET TBLPROPERTIES ('delta.logRetentionDuration' = 'interval 60 days')

Keep your VACUUM retention at least as long as how far back you actually need to time-travel. The default 7-day VACUUM retention is fine if a week covers your recovery window. If you need longer, bump both the VACUUM retain period and the log retention together — mismatching them creates a situation where the history references data files that no longer exist, making time travel fail for those versions. As always, I'm here to help.

Read more