What you will be able to do
- Use DESCRIBE HISTORY to find the version number and commit timestamp of each write to a Delta table
- Query an earlier state of a table with VERSION AS OF or TIMESTAMP AS OF
- Write the same query with the @ shorthand, using the correct version and timestamp formats
- Recognise the analysis tasks that time travel is designed for
Key concept
Table version (time travel) — Every write to a Delta table is committed as a new numbered version with a timestamp in the transaction log. Time travel lets you read the table as it was at any retained version, chosen by version number or by timestamp.
1.Every write creates a version
A Delta table does not overwrite itself in place. Each INSERT, UPDATE, DELETE, MERGE or OPTIMIZE is committed to the table's transaction log as a new version, numbered from 0 and stamped with the time of the commit. The current table is simply the latest version. Earlier versions stay readable for as long as their log entries and data files are kept.
This is what makes time travel possible. You don't need a backup copy or a snapshot table to see what the data looked like yesterday. You name the version, or a point in time, and Databricks reads the table as it stood then.
The documentation names four uses for this:
- Re-creating analyses, reports, or outputs, for example to debug or audit a number that has since changed. This matters most in regulated industries. - Writing complex temporal queries, such as comparing today's table with last week's. - Fixing mistakes in your data by reading the correct rows from before a bad write. - Providing snapshot isolation so that a set of queries against a fast-changing table all see the same data.
Checkpoint 1 of 6· Check yourself
A finance analyst has to show an auditor exactly what a quarterly report was based on. The source table has been updated many times since then. Which use of time travel does this match?
Rebuilding a past report for debugging or audit is a documented time travel use. Archival is explicitly something table history should not be used for.
“Re-creating analyses, reports, or outputs, such as the output of a machine learning model.”Source: docs.databricks.com
Sources1
2.Finding versions with DESCRIBE HISTORY
Before you can query an old version, you need its version number or commit time. DESCRIBE HISTORY gives you both. It returns one row per write, newest first, showing the operation, the user who ran it and when it ran. In Catalog Explorer, the same history is shown on the table's History tab.
DESCRIBE HISTORY table_name LIMIT 1 -- get the last operation onlyThe output has 14 columns. For time travel, a handful of them do most of the work:
| Column | Type | What it tells you |
|---|---|---|
| version | long | The table version generated by the operation. This is the number you pass to VERSION AS OF. |
| timestamp | timestamp | When this version was committed. Use it to pick a value for TIMESTAMP AS OF. |
| operation | string | The name of the operation, for example WRITE, UPDATE or DELETE. |
| userName | string | The name of the user who ran the operation. |
| readVersion | long | The version of the table that was read to perform the write operation. |
One restriction applies: the table name you pass to DESCRIBE HISTORY must be the plain table name. It cannot contain a time travel specification. You ask for the history of the table, not the history of one of its versions.
Checkpoint 2 of 6· Check yourself
An accidental DELETE ran on the orders table some time this morning. You want to query the table as it was just before that DELETE. What should you run first to find the version to target?
DESCRIBE HISTORY lists every write with its version and timestamp, so you can spot the DELETE and target the version before it. The table name in DESCRIBE HISTORY cannot carry a temporal specification.
“version is a long value that can be obtained from the output of DESCRIBE HISTORY table_spec.”Source: docs.databricks.com
3.VERSION AS OF and TIMESTAMP AS OF
To time travel, you add a clause directly after the table name in the FROM clause. Everything else in the query (filters, joins, aggregates) works as usual. There are two forms. One targets an exact version number from the history. The other targets a point in time, and returns the table as of that moment.
SELECT * FROM people10m TIMESTAMP AS OF '2018-10-18T22:15:12.013Z';
SELECT * FROM people10m VERSION AS OF 123;The rules for each argument are narrow:
- version is a long value, which you read from the version column of DESCRIBE HISTORY.
- timestamp_expression can be a full timestamp string such as '2018-10-18T22:15:12.013Z', or a date string such as '2018-10-18'. It can also be an explicit cast(... as timestamp), or any expression that is or can be cast to a timestamp. For relative queries, current_timestamp() - interval 12 hours and date_sub(current_date(), 1) both work.
- Neither argument can be a subquery.
Checkpoint 3 of 6· Fill the gap
Which keyword completes the second query so that it reads version 123 of the table?
SELECT * FROM people10m TIMESTAMP AS OF '2018-10-18T22:15:12.013Z';
SELECT * FROM people10m ? AS OF 123;A numeric value from the history's version column is passed with VERSION AS OF. TIMESTAMP AS OF expects a timestamp expression, and SNAPSHOT and COMMIT are not time travel keywords.
Source: docs.databricks.comCheckpoint 4 of 6· Exam question
An analyst suspects a nightly job corrupted the `orders` table's `status` column sometime yesterday, but does not know the exact version number when the corruption started. Which approach lets them inspect the table's contents as of a specific date and time without first looking up a version number?
Correct answer: A — Run `SELECT * FROM orders TIMESTAMP AS OF '2026-09-14T00:00:00.000Z';` to read the table snapshot as it existed at that timestamp
- A. Correct: the `TIMESTAMP AS OF` clause lets a query target the table state as of a specific date and time directly, so the analyst does not need to first resolve a version number before inspecting historical rows.
- B. Incorrect: `VERSION AS OF 0` returns only the table's initial state, not yesterday's state, and manually diffing every row against the current table is far less direct than querying a timestamp.
- C. Incorrect: `DESCRIBE HISTORY` lists operations, users, and timestamps for each commit, but its metrics columns summarize row counts and file changes, not the actual column values, so it cannot substitute for querying the data.
- D. Incorrect: `VACUUM` permanently deletes stale data files that are no longer referenced by the retention window; it does not restore a table to a prior state and running it can actually remove files needed for time travel.
4.The @ shorthand
You can also put the version or timestamp in the table name itself, using @. For a version, add v before the number. For a timestamp, write the value as one run of digits in yyyyMMddHHmmssSSS format: year, month, day, hour, minute, second and milliseconds, with no separators.
-- Timestamp version
SELECT * FROM people10m@20190101000000000
-- Version number
SELECT * FROM people10m@v123| Form | Example | What you supply |
|---|---|---|
| VERSION AS OF | SELECT * FROM events VERSION AS OF 123 | A long version number from DESCRIBE HISTORY |
| TIMESTAMP AS OF | SELECT * FROM events TIMESTAMP AS OF '2018-10-18T22:15:12.013Z' | Any expression that is or can be cast to a timestamp (not a subquery) |
| @v | SELECT * FROM events@v123 | The version number with a v prefix |
| @ timestamp | SELECT * FROM events@20190101000000000 | A timestamp in yyyyMMddHHmmssSSS format |
Checkpoint 5 of 6· Match them up
Match each table reference to what it reads
Tap a term, then the definition that fits it.
With @, a v prefix marks a version number and a 17-digit value is a yyyyMMddHHmmssSSS timestamp. date_sub(current_date(), 1) is one of the documented timestamp expressions.
“The timestamp must be in yyyyMMddHHmmssSSS format. You can specify a version after @ by prepending a v to the version.”Source: docs.databricks.com
Checkpoint 6 of 6· Exam question
Before comparing today's `inventory` table against last Friday's snapshot, an analyst runs `DESCRIBE HISTORY inventory;` and sees a `version` column alongside `timestamp` and `operation`. Which statement correctly describes how to use the `version` value returned there in a subsequent query?
Correct answer: A — Pass the version number after `VERSION AS OF` in a `SELECT`, such as `SELECT * FROM inventory VERSION AS OF 45;`, to read that exact committed snapshot
- A. Correct: `VERSION AS OF` accepts the long integer version number shown by `DESCRIBE HISTORY` and returns the table exactly as it existed after that commit was written.
- B. Incorrect: Delta tables do not store a queryable `version` column on individual rows; version is metadata about the table's commit history in the transaction log, not a per-row attribute available to a `WHERE` clause.
- C. Incorrect: version numbers are sequential commit identifiers, not a function of elapsed time or checkpoint frequency, so there is no arithmetic conversion from a version number to a timestamp.
- D. Incorrect: `VACUUM` takes a retention duration such as `RETAIN 168 HOURS`, not a version number, and it deletes stale unreferenced files rather than pruning commits after a given version.
Sources1
Exam traps
Each one states something that sounds right. Open it to see what is actually true.
1.You can compute the target version inside the query, for example VERSION AS OF (SELECT max(version) ...).Why is that wrong?
The version and the timestamp expression must not be subqueries. Read the version from DESCRIBE HISTORY and pass it as a literal, or use a timestamp expression that does not contain a subquery.
Covered in VERSION AS OF and TIMESTAMP AS OF
2.The @ shorthand accepts the same timestamp strings as TIMESTAMP AS OF, and a bare number after @ is a version.Why is that wrong?
An @ timestamp must be a yyyyMMddHHmmssSSS digit string such as @20190101000000000. A version needs the v prefix, as in @v123.
Covered in The @ shorthand
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
“each operation that modifies a table creates a new table version”
↩︎ Every write creates a version“Providing snapshot isolation for a set of queries for fast changing tables.”
↩︎ Every write creates a version“Don't use table history as a long-term backup solution for data archival.”
↩︎ Every write creates a version“Run the DESCRIBE HISTORY command to retrieve information including the operations, user, and timestamp for each write to a table.”
↩︎ Finding versions with DESCRIBE HISTORY“The operations are returned in reverse chronological order.”
↩︎ Finding versions with DESCRIBE HISTORY“Catalog Explorer shows table history visually on the History tab.”
↩︎ Finding versions with DESCRIBE HISTORY“You query a table with time travel by adding a clause after the table name specification.”
↩︎ VERSION AS OF and TIMESTAMP AS OF“You can also use the @ syntax to specify the timestamp or version as part of the table name.”
↩︎ The @ shorthand“Time travel supports querying previous table versions based on timestamp or table version (as recorded in the transaction log).”
↩︎ Key concept“The timestamp must be in yyyyMMddHHmmssSSS format. You can specify a version with @v.”
↩︎ Exam trap 2“Re-creating analyses, reports, or outputs, such as the output of a machine learning model.”
↩︎ Checkpoint“version is a long value that can be obtained from the output of DESCRIBE HISTORY table_spec.”
↩︎ Checkpoint“Neither timestamp_expression nor version can be subqueries.”
↩︎ Prediction - 2.
“The name must not include a temporal specification or options specification.”
↩︎ Finding versions with DESCRIBE HISTORY - 3.
“The version of the table that was read to perform the write operation.”
↩︎ Finding versions with DESCRIBE HISTORY - 4.
“Delta tables support the time travel options described in this section.”
↩︎ VERSION AS OF and TIMESTAMP AS OF
Also cited
- https://docs.databricks.com/aws/en/sql/language-manual/sql-ref-syntax-qry-select-table-referenceOfficial docs
“Neither timestamp_expression nor version can be subqueries.”
↩︎ Exam trap 1“The timestamp must be in yyyyMMddHHmmssSSS format. You can specify a version after @ by prepending a v to the version.”
↩︎ Checkpoint