Sev1: a bad UPDATE or DELETE hit a production Snowflake table. Recover rows with Time Travel BEFORE(STATEMENT)

#Symptoms

• Reports show zeros, nulls or missing rows after a manual fix or a pipeline change.
• Someone says "I ran an UPDATE and it said 4,800,000 rows affected".
• Downstream tasks and dynamic tables have already propagated the bad data.

#Step 1: stop propagation (5 minutes)

Suspend tasks and pipelines that read the damaged table so the bad data does not spread further:

ALTER TASK analytics.tasks.load_daily_revenue SUSPEND;
ALTER DYNAMIC TABLE analytics.marts.revenue_dt SUSPEND;

Tell report owners the data is wrong.

#Step 2: find the statement

SELECT query_id, user_name, role_name, start_time, rows_updated, rows_deleted, LEFT(query_text, 200) AS q
FROM TABLE(information_schema.query_history_by_user(USER_NAME => 'ALICE', RESULT_LIMIT => 200))
WHERE query_type IN ('UPDATE', 'DELETE', 'MERGE', 'TRUNCATE_TABLE')
ORDER BY start_time DESC;

If you do not know the user, use account usage (latency of minutes to hours):

SELECT query_id, user_name, start_time, query_type, rows_updated, rows_deleted, LEFT(query_text, 200)
FROM snowflake.account_usage.query_history
WHERE start_time > DATEADD(hour, -24, CURRENT_TIMESTAMP())
  AND query_text ILIKE '%orders%'
  AND query_type IN ('UPDATE', 'DELETE', 'MERGE', 'TRUNCATE_TABLE', 'INSERT_OVERWRITE')
ORDER BY start_time DESC;

Note the query_id and check the table's Time Travel retention:

SHOW PARAMETERS LIKE 'DATA_RETENTION_TIME_IN_DAYS' IN TABLE analytics.core.orders;

Caution

The clock is running. With the Standard edition default of 1 day, data older than 24 hours is gone from Time Travel (permanent tables then enter Fail-safe, which only Snowflake Support can access). Move quickly.

#Step 3: clone the table as it was before the statement

CREATE TABLE analytics.core.orders_before_fix
  CLONE analytics.core.orders BEFORE (STATEMENT => '01b8c2a4-0002-7d1e-0000-0012ab34cd56');

A clone is zero-copy and fast, even for large tables. It also preserves the data beyond the Time Travel window, because the clone owns those micro-partitions now.

#Step 4: compare

SELECT COUNT(*) FROM analytics.core.orders;
SELECT COUNT(*) FROM analytics.core.orders_before_fix;

-- rows that differ
SELECT order_id FROM analytics.core.orders_before_fix
MINUS
SELECT order_id FROM analytics.core.orders;            -- deleted rows

SELECT b.order_id, b.amount AS before_amount, c.amount AS current_amount
FROM analytics.core.orders_before_fix b
JOIN analytics.core.orders c USING (order_id)
WHERE b.amount IS DISTINCT FROM c.amount
LIMIT 100;                                              -- updated rows

#Step 5: restore

Choose based on what happened after the bad statement.

A. Nothing else wrote to the table since (or later writes can be replayed): swap the whole table.

ALTER TABLE analytics.core.orders SWAP WITH analytics.core.orders_before_fix;

SWAP is atomic and keeps grants on the names (check ownership and future grants in your setup). The damaged version is now orders_before_fix; keep it until the incident is closed.

B. Legitimate writes happened since: restore only the affected rows.

Deleted rows:

INSERT INTO analytics.core.orders
SELECT b.* FROM analytics.core.orders_before_fix b
LEFT JOIN analytics.core.orders c USING (order_id)
WHERE c.order_id IS NULL;

Updated columns:

UPDATE analytics.core.orders c
SET amount = b.amount, status = b.status
FROM analytics.core.orders_before_fix b
WHERE c.order_id = b.order_id
  AND c.amount IS DISTINCT FROM b.amount
  AND c.updated_at <= '2026-10-29 09:14:00';     -- do not overwrite rows legitimately changed later

Run both inside a transaction if you want all-or-nothing:

BEGIN;
-- insert and update statements
COMMIT;

#Step 6: repair downstream

• Resume tasks and dynamic tables; dynamic tables will refresh from the corrected source.
• Re-run dbt models or tasks for the affected period if they materialised tables from the bad data.
• For tables that were themselves updated from the bad data, repeat steps 3-5 on them, using their own query IDs.

ALTER DYNAMIC TABLE analytics.marts.revenue_dt RESUME;
ALTER DYNAMIC TABLE analytics.marts.revenue_dt REFRESH;
ALTER TASK analytics.tasks.load_daily_revenue RESUME;

#Step 7: confirm

• Row counts and key totals match the pre-incident values (or the expected values including legitimate later changes).
• Report owners sign off.
• Drop the helper clone after the agreed hold period.

#Prevent it next time

• Production roles for people are read-only; changes go through reviewed pipelines.
• Set DATA_RETENTION_TIME_IN_DAYS on critical tables to more than a weekend (Enterprise allows up to 90).
• Before a manual change: CREATE TABLE x_backup CLONE x; then run the change.
• Use transient tables only for data you can rebuild; they have no Fail-safe.