Two tables that must agree
Multi-statement transactions on catalog-managed Unity Catalog tables: BEGIN ATOMIC across two tables, Delta time travel to undo a half-finished write, and what a rollback leaves in the table history.
What you will learn
- Why two writes that have to land together are not safe just because each one is atomic on its own.
- How to put a table back to a known good version with time travel.
- How to make both writes commit as one unit with
BEGIN ATOMICon catalog-managed tables. - What a failed transaction leaves behind, and how to prove it from the table history.
The problem
A ledger and a balance, written by two statements. The job dies between them, so the ledger has the new rows and the balance does not. Both tables look fine on their own, nothing fires, and the reports are wrong until somebody reconciles them by hand.
Set up
The starter builds both tables from spark.range in a schema of your own: 4,096 postings over 64
accounts, a balance derived from them, and a check that they agree. An account agrees when its
balance equals the sum of its postings, which is the only question this lab asks.
Paste your salt into the first cell. One of the five answers is yours alone, so a notebook borrowed from somebody else fails that check and passes the rest.
The work
- Reproduce it. The starter posts a late batch the way the job does today and stops between the two statements. Count the accounts that now disagree.
- Get back to a state you can trust. Every write left a version behind and
DESCRIBE HISTORYlists them, so you do not have to work out which 300 rows were yours. - Replay it atomically. The same two writes, as one unit.
- Prove it holds. Fail a transaction halfway on purpose. The error is not the answer. What the ledger holds afterwards is, and so is what the history recorded while the block ran.
Steps 2 to 4 are yours to write.
The questions
| key | what to print |
|---|---|
drift_accounts |
accounts whose balance disagrees with their ledger after the failed batch |
abort_versions |
versions the ledger gained while the failing transaction ran |
abort_rows |
rows in the ledger once that transaction has failed |
mismatch_after |
accounts still disagreeing after the atomic replay |
probe |
the balance of the account your salt picks out |
Notes
A transaction needs every table it touches to be catalog-managed, which is a table property:
create table ... using delta tblproperties ('delta.feature.catalogManaged' = 'supported')BEGIN ATOMIC ... END; commits whole or rolls back whole, and is the only form serverless compute
runs. BEGIN TRANSACTION; ... COMMIT; needs a SQL warehouse. The
docs tutorial covers both.
Inside a BEGIN ATOMIC block the range() table function is not available. Generate rows there
with explode(sequence(0, 299)) as t(id), and keep range() for the cells outside the block.
Documentation Databricks' own pages for what this lab uses. They are reference, not the answer.
- Transactions
BEGIN ATOMIC and BEGIN TRANSACTION, what a transaction may contain, and the catalog commits the participating tables need.
- Transactions tutorial
A worked multi-statement transaction end to end, which is the shape the replay step wants.
- Managed tables
What makes a table catalog-managed, which is the precondition the transaction syntax checks.
- Work with Delta Lake table history
Time travel by version and by timestamp, and how long the history stays readable.
- DESCRIBE HISTORY
The columns the history returns, which is where a rolled back write either shows up or does not.
-
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.