Recover from an accidental bulk UPDATE by time-travelling through versions, picking the last good snapshot, and restoring the table with a single RESTORE TO VERSION command.
-- SETUP
CREATE ZONE IF NOT EXISTS demo_zone TYPE EXTERNAL
COMMENT 'External and Delta tables -- demo datasets';
CREATE SCHEMA IF NOT EXISTS demo_zone.delta_demos
COMMENT 'Delta table management tutorial demos';
CREATE DELTA TABLE IF NOT EXISTS demo_zone.delta_demos.course_grades (
record_id INT,
student_name VARCHAR,
course VARCHAR,
grade INT,
semester VARCHAR
) LOCATION '~/delta-data/course_grades';
INSERT INTO demo_zone.delta_demos.course_grades VALUES
(1, 'Alice', 'CS101', 82, 'Fall-2025'),
(2, 'Bob', 'CS101', 71, 'Fall-2025'),
(3, 'Carol', 'CS101', 90, 'Fall-2025'),
(4, 'David', 'CS101', 65, 'Fall-2025'),
(5, 'Eve', 'CS101', 88, 'Fall-2025'),
(6, 'Alice', 'CS201', 78, 'Fall-2025'),
(7, 'Bob', 'CS201', 84, 'Fall-2025'),
(8, 'Carol', 'CS201', 73, 'Fall-2025'),
(9, 'David', 'CS201', 91, 'Fall-2025'),
(10, 'Eve', 'CS201', 69, 'Fall-2025'),
(11, 'Alice', 'MATH101', 95, 'Fall-2025'),
(12, 'Bob', 'MATH101', 62, 'Fall-2025'),
(13, 'Carol', 'MATH101', 88, 'Fall-2025'),
(14, 'David', 'MATH101', 74, 'Fall-2025'),
(15, 'Eve', 'MATH101', 81, 'Fall-2025'),
(16, 'Alice', 'PHYS101', 70, 'Fall-2025'),
(17, 'Bob', 'PHYS101', 86, 'Fall-2025'),
(18, 'Carol', 'PHYS101', 77, 'Fall-2025'),
(19, 'David', 'PHYS101', 83, 'Fall-2025'),
(20, 'Eve', 'PHYS101', 92, 'Fall-2025'),
(21, 'Alice', 'ENG101', 88, 'Fall-2025'),
(22, 'Bob', 'ENG101', 79, 'Fall-2025'),
(23, 'Carol', 'ENG101', 94, 'Fall-2025'),
(24, 'David', 'ENG101', 68, 'Fall-2025'),
(25, 'Eve', 'ENG101', 85, 'Fall-2025');
-- QUERIES
-- V2: legitimate curve -- CS101 +5
UPDATE demo_zone.delta_demos.course_grades
SET grade = grade + 5
WHERE course = 'CS101';
-- V3: ACCIDENT -- a script zeros every grade
UPDATE demo_zone.delta_demos.course_grades
SET grade = 0;
-- Inspect V1: original, pre-curve
SELECT course, ROUND(AVG(grade), 2) AS avg_grade, COUNT(*) AS students
FROM demo_zone.delta_demos.course_grades VERSION AS OF 1
GROUP BY course
ORDER BY course;
-- Inspect V2: last known good state (curve applied)
SELECT course, ROUND(AVG(grade), 2) AS avg_grade, COUNT(*) AS students
FROM demo_zone.delta_demos.course_grades VERSION AS OF 2
GROUP BY course
ORDER BY course;
-- Inspect V3: confirm the accident -- all zeroes
SELECT COUNT(*) AS zero_count
FROM demo_zone.delta_demos.course_grades VERSION AS OF 3
WHERE grade = 0;
-- RESTORE TO VERSION 2 -- creates V4, a new commit replicating V2's snapshot
RESTORE demo_zone.delta_demos.course_grades TO VERSION 2;
-- Verify recovery: averages now match V2 exactly
SELECT course,
ROUND(AVG(grade), 2) AS avg_grade,
COUNT(*) AS students
FROM demo_zone.delta_demos.course_grades
GROUP BY course
ORDER BY course;
-- The bad V3 commit is STILL reachable -- log is append-only
SELECT COUNT(*) AS zero_count
FROM demo_zone.delta_demos.course_grades VERSION AS OF 3
WHERE grade = 0;
-- CLEANUP
DROP DELTA TABLE IF EXISTS demo_zone.delta_demos.course_grades WITH FILES;
DROP SCHEMA IF EXISTS demo_zone.delta_demos;
DROP ZONE IF EXISTS demo_zone;## When to Use Reach for this pattern whenever someone points the wrong job at a production table. Delta keeps every committed snapshot addressable by integer version (and by timestamp), so you can open any historical view read-only via VERSION AS OF, compare it against the current state, and only then -- once you are sure you picked the right target -- use RESTORE to replay that snapshot as a new commit. This is Delta's built-in undo. The scenario: a teaching assistant runs a grade-reset script against the wrong table, zeroing 25 student grades. You time-travel to V1 (pre-curve), V2 (post-curve -- the last good state) and V3 (the accident) before deciding. The right target is V2 because V1 would discard a legitimate CS101 curve that you want to keep. ## What You Will Learn 1. How every DML statement bumps the Delta version and how to read the version ladder 2. How VERSION AS OF <n> lets you query any historical snapshot without touching the table 3. Why you should always inspect several versions before recovering -- the "last good" version is rarely the oldest one 4. How RESTORE TO VERSION appends a NEW commit (V4) that materialises the target snapshot, rather than rewriting history 5. That the bad commit (V3) stays reachable via time travel after RESTORE -- the log is append-only 6. How to use ASSERT VALUE ... WHERE to verify aggregates at specific recovery checkpoints 7. Delta also supports TIMESTAMP AS OF '2024-01-15 10:00:00' if you prefer wall-clock recovery ## Prerequisites None -- the setup seeds 25 grade rows. The demo deliberately performs a destructive UPDATE inside the queries.sql to simulate the accident you need to recover from.
| Name | Type | Description |
|---|---|---|
TABLE_PATH | Filesystem or object-store location for the Delta table. | |
TARGET_VERSION | Version number to restore to. Inspect candidates via VERSION AS OF before running RESTORE. |