Time travel and table history
Delta table versions end to end: describe history and what a write records in it, querying version as of an earlier version, and restore table to undo two bad writes without losing them.
What you will learn
- That every write to a Delta table adds a version, and the earlier ones stay readable.
- That some of those versions were not written by you.
describe history, and what a write records about itself in it.version as of, which queries the table as it stood without changing anything.restore table, and why undoing a mistake does not erase the mistake.
The problem
The update went to eight regions instead of one. Somebody then deleted the negative amounts to tidy up, not knowing the update had made some of them. Two statements, both committed, and the table now holds neither the right numbers nor a record of what the right numbers were.
Set up
Nothing to download. The starter creates a schema, builds one table from spark.range with
create table ... as select, which is a single write and so leaves the table at version 0, and then
runs the two bad writes for you. The damage is the premise, not the exercise.
If workspace is not the catalog you can create a schema in, change CATALOG in the setup cell.
Paste your salt into the first cell. One of the four answers is yours alone, so a notebook borrowed from somebody else fails that check and passes the rest.
The work
- Look at what happened.
describe historylists one row per version, and not every row is yours. Read theoperationcolumn, then count only the three statements this lab ran. - Read the table as it was. Query version 0 and count its rows, then work out how many rows the delete took by comparing that to the count now.
- Put it back. Restore to version 0, and look at the history again afterwards.
- Answer for your own region. Your salt picks one of the 64. Total its amounts in the restored table.
Steps 1 to 4 are yours to write.
The questions
| key | what to print |
|---|---|
my_writes |
versions that came from your own three statements, before you restore |
v0_rows |
rows the table held at version 0 |
deleted_rows |
rows the delete took |
bucket_total |
the total for your region, in the restored table |
Notes
Serverless optimises tables on its own. An OPTIMIZE you never ran will appear in the history
between your writes, so the number of versions is not the number of statements you typed, and it is
not the same number twice. Counting by operation is what makes the answer yours.
my_writes is counted before the restore. The restore is itself a write, so running it first
changes that answer.
describe history has an operationMetrics column, and it is worth opening, but the keys in it are
different for every kind of operation. Comparing version 0 to the table now is the answer that does
not depend on knowing which key a delete happens to use.
select ... from <table> version as of 0 is an ordinary query. It reads the old version and changes
nothing, which is what makes it safe to run before you decide whether to restore.
A restore writes a new version rather than removing the ones it undoes, so the two bad writes are still on the history afterwards.
Documentation Databricks' own pages for what this lab uses. They are reference, not the answer.
- Work with Delta Lake table history
Versions, time travel and retention, which is the whole lab in one page.
- DESCRIBE HISTORY
Every column the history returns, operationMetrics included.
- RESTORE
Restoring to a version or a timestamp, and what it writes.
- DELETE FROM
The statement that caused the damage the lab undoes.
-
v12026-09-21 First release.
Re-run needed means an answer changed, so passes from before it have to be earned again. Every other release leaves them alone.