Snowflake Time Travel: SQL Recovery & UNDROP Guide

Now simulate a legitimate update after the mistake: the product team corrects the backpack’s display name. Restoring every old column would undo that valid correction. We will repair only unit_price and leave the current name intact.

UPDATE PRODUCT_PRICES
SET product_name='Travel backpack' WHERE product_id=2;

BEGIN;
UPDATE PRODUCT_PRICES t
SET unit_price=r.unit_price
FROM PRICES_BEFORE_BAD_UPDATE r
WHERE t.product_id=r.product_id;

SELECT * FROM PRODUCT_PRICES ORDER BY product_id;
SELECT COUNT(*) AS mismatched_prices
FROM PRODUCT_PRICES t
JOIN PRICES_BEFORE_BAD_UPDATE r USING(product_id)
WHERE t.unit_price <> r.unit_price;
-- STOP: keep this transaction open and inspect both result sets.

Stop and choose one transaction action before continuing. Inspect the preceding output in the same session: three rows, 650.00 total, expected prices and the valid Travel backpack name. If correct, execute the following COMMIT block separately. If not, execute ROLLBACK instead. Do not run both, and do not begin the next drill with this transaction open.

COMMIT;

Alternative only if validation fails:

ROLLBACK;

For the supplied lab, mismatched_prices should be zero. Product 2 should now be named Travel backpack with a price of 500.00. The other products should be Notebook/100.00 and Pen set/50.00. Before COMMIT in an exploratory run, choose ROLLBACK if the results do not match the intended repair.

In a real incident, a later valid price update complicates this repair too. A historical price must not overwrite a newer approved price simply because it predates the incident. Establish a write pause, an approved affected-key set or a change-aware repair rule as appropriate to the system. Time Travel supplies evidence; it does not decide which later business changes to preserve.

Check missing and unexpected keys as well

SELECT COALESCE(t.product_id,r.product_id) AS product_id,
 CASE WHEN t.product_id IS NULL THEN 'MISSING_CURRENT'
      WHEN r.product_id IS NULL THEN 'NOT_IN_SNAPSHOT'
 END AS key_exception
FROM PRODUCT_PRICES t
FULL OUTER JOIN PRICES_BEFORE_BAD_UPDATE r USING(product_id)
WHERE t.product_id IS NULL OR r.product_id IS NULL;

The seed should produce no key exceptions. Outside this lab, an exception may be a valid later insert rather than an error. Investigate it using the incident scope. Do not make the recovery snapshot’s key set automatically authoritative for all later business activity.

6.1 Verify the repaired business state with a full comparison

Run this after the selective repair and legitimate name update. The correct output combines historical prices with the current approved product name; it is deliberately not identical to the entire historical snapshot.

WITH expected(product_id,product_name,unit_price) AS (
 SELECT * FROM VALUES (1,'Notebook',100.00),
 (2,'Travel backpack',500.00),(3,'Pen set',50.00)
)
SELECT 'MISSING_OR_WRONG' AS issue,* FROM (
 SELECT * FROM expected MINUS SELECT * FROM PRODUCT_PRICES
)
UNION ALL
SELECT 'UNEXPECTED_OR_WRONG' AS issue,* FROM (
 SELECT * FROM PRODUCT_PRICES MINUS SELECT * FROM expected
);
SELECT COUNT(*) AS rows_after_repair,SUM(unit_price) AS total_after_repair
FROM PRODUCT_PRICES;
SELECT product_id,COUNT(*) AS copies FROM PRODUCT_PRICES
GROUP BY product_id HAVING COUNT(*)>1;

Expect zero difference rows, three current rows, total 650.00 and zero duplicate IDs. A whole-table restoration could pass the price-total check while silently reverting Travel backpack to Backpack. This is why the acceptance specification includes a later valid change.

6.2 Practise a missing-row repair on a disposable copy

This is a separate extension. Create a scratch copy from the repaired table, delete product 3 there and restore only that row from the retained pre-delete state. Do not run the DELETE against PRODUCT_PRICES.

CREATE TABLE PRICE_DELETE_DRILL DATA_RETENTION_TIME_IN_DAYS=1 AS SELECT * FROM PRODUCT_PRICES;
DELETE FROM PRICE_DELETE_DRILL WHERE product_id=3;
SET delete_qid=LAST_QUERY_ID();
SELECT * FROM PRICE_DELETE_DRILL BEFORE(STATEMENT => $delete_qid)
WHERE product_id=3;
BEGIN;
INSERT INTO PRICE_DELETE_DRILL(product_id,product_name,unit_price)
SELECT h.product_id,h.product_name,h.unit_price
FROM PRICE_DELETE_DRILL BEFORE(STATEMENT => $delete_qid) h
WHERE h.product_id=3
 AND NOT EXISTS(SELECT 1 FROM PRICE_DELETE_DRILL c WHERE c.product_id=h.product_id);
SELECT COUNT(*) AS restored_rows,SUM(unit_price) AS restored_total FROM PRICE_DELETE_DRILL;
-- STOP: expected three rows and 650.00. Inspect before choosing an action.

Stop and choose one transaction action before continuing. Inspect the preceding output in the same session: three rows, 650.00 total, expected prices and the valid Travel backpack name. If correct, execute the following COMMIT block separately. If not, execute ROLLBACK instead. Do not run both, and do not begin the next drill with this transaction open.

COMMIT;

Alternative only if validation fails:

ROLLBACK;

The anti-join prevents a second sequential run from adding the already restored key. It is not a concurrency guarantee: coordinate writers and validate uniqueness before a real repair. If validation fails inside the transaction, choose ROLLBACK instead of COMMIT. Keep DDL outside the repair transaction.