What you will be able to do
- Use DESCRIBE HISTORY to see who changed a Delta table, what they ran and when
- Read the history columns, including readVersion, operationParameters and operationMetrics, to audit a specific write
- Tell an automatic OPTIMIZE from one a user ran by querying table history with SQL
Key concept
Table version — Every write to a Delta Lake table (insert, update, delete, merge, optimize and so on) commits a new numbered version. The table's history is the list of those versions, and you use it both to audit changes and to query or restore an earlier state.
1.Audit a table: view its history with DESCRIBE HISTORY
A dashboard number changes overnight and someone asks what happened to the table behind it. With a Delta Lake table you can find out directly, because the table records each change it receives. Every operation that modifies the table creates a new table version, and that list of versions is what you use to audit operations, roll the table back, or query it as it was at an earlier point in time.
The SQL entry point is DESCRIBE HISTORY. It returns one row per write, showing the operation, the user who ran it and the timestamp. Rows come back in reverse chronological order, so the newest change is at the top. Add LIMIT 1 when you only need the latest operation, for example to confirm that last night's load actually ran. The command reference also restricts what you can pass in: the table name cannot carry a temporal specification such as VERSION AS OF. You are asking for the whole history, not for one snapshot.
DESCRIBE HISTORY table_name LIMIT 1 -- get the last operation onlyIf you would rather not write SQL, Catalog Explorer shows the same history on the table's History tab. Keep two limits in mind. First, history retention is set by the table property logRetentionDuration, which defaults to 30 days. Second, the documentation says plainly that table history is not a long-term backup or archive.
Checkpoint 1 of 4· Fill the gap
An analyst only wants the most recent write to a table. Which keyword completes the command?
DESCRIBE HISTORY table_name ? 1 -- get the last operation onlyDESCRIBE HISTORY returns rows newest-first, so LIMIT 1 keeps only the latest operation.
Source: docs.databricks.comCheckpoint 2 of 4· Exam question
An analyst runs `DESCRIBE HISTORY orders LIMIT 5;` and wants to understand what the returned `operationMetrics` column contains for a row where `operation` is `MERGE`. What does that column represent?
Correct answer: A — Quantitative details about the operation, such as the number of rows and files inserted, updated, or deleted by that commit
- A. Correct: `operationMetrics` reports numeric outcomes of the commit, such as rows and files affected by inserts, updates, and deletes, letting an analyst quantify the scale of a MERGE without re-scanning the table.
- B. Incorrect: the raw SQL text submitted by the user is not stored in `operationMetrics`; operation-specific configuration instead appears in the separate `operationParameters` column.
- C. Incorrect: column-level min/max statistics used for data skipping are maintained internally in the Delta transaction log's add-file actions, not exposed through the `operationMetrics` field of `DESCRIBE HISTORY`.
- D. Incorrect: compute configuration such as node type or autoscaling settings is a property of the cluster or SQL warehouse that ran the job, not something `DESCRIBE HISTORY` records per commit.
2.What each history column tells an auditor
DESCRIBE HISTORY returns 14 columns. Learn them by the audit question each one answers. The table below covers the ones you will use most.
| Column | What it records | Audit question it answers |
|---|---|---|
| version | The table version generated by the operation | Which version do I time travel or restore to? |
| timestamp | When this version was committed | When did the change happen? |
| userName / userId | The user that ran the operation | Who made the change? |
| operation | The name of the operation (for example WRITE, UPDATE, DELETE, OPTIMIZE) | What kind of change was it? |
| operationParameters | The parameters of the operation, for example predicates | Which rows or settings did it target? |
| job / notebook | Details of the Lakeflow job or notebook that ran it, otherwise null | Which pipeline or notebook was the source? |
| readVersion | The table version that was read to perform the write | Which version was this write based on? |
| isBlindAppend | Whether this operation appended data | Was this a pure append? |
| operationMetrics | Metrics such as the number of rows and files modified | How much data did it change? |
Two details matter in practice. The job and notebook columns are filled in only when the commit came from a Lakeflow job or a Databricks notebook, and are null otherwise. Some columns are also missing when the write came in over JDBC or ODBC, through the REST API, or from some job task types. A null in those columns therefore does not mean the write had no source. It may just mean the client did not record one. Also note that readVersion is the version the write read from, which is not always the version just before it.
Checkpoint 3 of 4· Check yourself
A row in DESCRIBE HISTORY shows version 5 with readVersion 4. What does readVersion 4 tell you?
readVersion records which table version a write was based on. It says nothing about retries or retention.
“The version of the table that was read to perform the write operation.”Source: docs.databricks.com
Sources3
3.Read operationParameters and operationMetrics to explain a change
The operation column only names the type of change. The detail is in two map columns: operationParameters, which holds the inputs such as a DELETE or UPDATE predicate, and operationMetrics, which holds the results, such as the number of rows and files modified. Comparing a write's metrics with what you expected it to do is a quick way to check the write.
Auto compaction runs automatically after a write and sets the auto parameter to true. When auto is false, a user or scheduled job ran OPTIMIZE. A populated clusterBy lists the liquid clustering columns, and a populated zOrderBy lists the Z-order columns. DESCRIBE HISTORY can also sit in a FROM clause, so you can filter and classify history with ordinary SQL. The documented query below labels every OPTIMIZE and pulls file counts out of operationMetrics.
SELECT
version,
timestamp,
CASE
WHEN operationParameters.clusterBy IS NOT NULL AND operationParameters.clusterBy <> '[]' THEN 'Liquid clustering'
WHEN operationParameters.zOrderBy IS NOT NULL AND operationParameters.zOrderBy <> '[]' THEN 'Z-ordering'
WHEN operationParameters.auto = 'true' THEN 'Auto compaction'
ELSE 'Manual OPTIMIZE'
END AS optimize_type,
operationParameters.auto AS is_auto_compaction,
operationParameters.clusterBy AS cluster_by,
operationParameters.zOrderBy AS z_order_by,
operationMetrics.numRemovedFiles AS files_compacted,
operationMetrics.numAddedFiles AS files_added,
operationMetrics.numRemovedBytes AS bytes_removed,
operationMetrics.numAddedBytes AS bytes_added
FROM (DESCRIBE HISTORY table_name)
WHERE operation = 'OPTIMIZE'
ORDER BY version DESC;Checkpoint 4 of 4· Check yourself
An OPTIMIZE row has operationParameters showing auto = false and empty clusterBy and zOrderBy arrays. What ran?
Auto compaction would set auto to true, and clustering or Z-ordering would fill their arrays. With auto false and both arrays empty, someone ran OPTIMIZE themselves.
“When auto is false, a user or scheduled job ran the OPTIMIZE command.”Source: docs.databricks.com
Not every parameter is a reliable signal. For appends to an existing table, the partitionBy field can show an empty array or the partition columns, depending on the write method. That inconsistency is expected and does not change how the data is written, so do not use partitionBy to check an append.
No. partitionBy is only meaningful for CREATE and OVERWRITE operations. An empty array on an append is expected, and the data still goes to the correct partitions under the table's existing partition schema.
Exam traps
Each one states something that sounds right. Open it to see what is actually true.
1.An OPTIMIZE entry in table history means a person or job explicitly ran the OPTIMIZE command.Why is that wrong?
Auto compaction, liquid clustering and Z-ordering are also logged as OPTIMIZE. Check operationParameters (auto, clusterBy, zOrderBy) to see which one ran.
Covered in Read operationParameters and operationMetrics to explain a change
2.An empty partitionBy on an APPEND history row shows the data was written without partitioning.Why is that wrong?
partitionBy is only meaningful for CREATE and OVERWRITE. On appends it can be empty depending on the write method, and it should not be used to validate appends.
Covered in Read operationParameters and operationMetrics to explain a change
Sources
Every claim above is drawn from one of these pages, quoted as it was written on the date shown.
- 1.https://docs.databricks.com/aws/en/tables/historyOfficial docs
“The operations are returned in reverse chronological order.”
↩︎ Audit a table: view its history with DESCRIBE HISTORY“Run the DESCRIBE HISTORY command to retrieve information including the operations, user, and timestamp for each write to a table.”
↩︎ Audit a table: view its history with DESCRIBE HISTORY“Use history information to audit operations, roll back a table”
↩︎ Audit a table: view its history with DESCRIBE HISTORY“Catalog Explorer shows table history visually on the History tab.”
↩︎ Audit a table: view its history with DESCRIBE HISTORY“Table history retention is determined by the table setting logRetentionDuration, which is 30 days by default.”
↩︎ Audit a table: view its history with DESCRIBE HISTORY“Don't use table history as a long-term backup solution for data archival.”
↩︎ Audit a table: view its history with DESCRIBE HISTORY“Auto compaction sets the auto parameter to true.”
↩︎ Read operationParameters and operationMetrics to explain a change“each operation that modifies a table creates a new table version”
↩︎ Key concept“To determine which one ran, inspect the operationParameters column.”
↩︎ Exam trap 1“Auto compaction, liquid clustering, and Z-ordering all appear in table history as OPTIMIZE operations.”
↩︎ Prediction“When auto is false, a user or scheduled job ran the OPTIMIZE command.”
↩︎ Checkpoint - 2.
“The name must not include a temporal specification or options specification.”
↩︎ Audit a table: view its history with DESCRIBE HISTORY - 3.
“The DESCRIBE HISTORY command returns 14 columns”
↩︎ What each history column tells an auditor“Populates only for commits written from a Lakeflow job. Otherwise, null.”
↩︎ What each history column tells an auditor“If you write into a table using the following methods, some columns aren't available: JDBC or ODBC”
↩︎ What each history column tells an auditor“The metrics of the operation (for example, number of rows and files modified.)”
↩︎ Read operationParameters and operationMetrics to explain a change“The partitionBy field in table history is only meaningful for CREATE and OVERWRITE operations”
↩︎ Read operationParameters and operationMetrics to explain a change“You shouldn't use it to validate append operations.”
↩︎ Exam trap 2“The version of the table that was read to perform the write operation.”
↩︎ Checkpoint