Sev1: Snowflake table dropped, truncated or overwritten — UNDROP, Time Travel and clones

#First: find the time or query

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

ACCOUNT_USAGE has latency; for the last minutes use TABLE(information_schema.query_history()) in the database.

#1. Dropped table, schema or database

SHOW TABLES HISTORY LIKE 'ORDERS' IN SCHEMA sales.public;
UNDROP TABLE sales.public.orders;
-- also: UNDROP SCHEMA ..., UNDROP DATABASE ...

If a new table with the same name already exists (a pipeline recreated it), rename it first:

ALTER TABLE sales.public.orders RENAME TO sales.public.orders_new_empty;
UNDROP TABLE sales.public.orders;

#2. Bad change, table still exists

Look at the data before the bad statement:

SELECT COUNT(*) FROM sales.public.orders BEFORE (STATEMENT => '01b2c3d4-0000-1234-0000-000123456789');
SELECT COUNT(*) FROM sales.public.orders AT (TIMESTAMP => '2026-10-25 06:00:00'::timestamp_ltz);

Restore with a zero-copy clone, then swap:

CREATE TABLE sales.public.orders_restore CLONE sales.public.orders
  BEFORE (STATEMENT => '01b2c3d4-0000-1234-0000-000123456789');

ALTER TABLE sales.public.orders SWAP WITH sales.public.orders_restore;

SWAP WITH is atomic. Grants on the table may need to be re-applied (or use COPY GRANTS patterns when recreating).

Restore only some rows instead:

INSERT INTO sales.public.orders
SELECT * FROM sales.public.orders BEFORE (STATEMENT => '01b2...')
WHERE order_date = '2026-10-24'
  AND order_id NOT IN (SELECT order_id FROM sales.public.orders);

Warning

Time Travel retention for the table decides how far back you can go (1 day by default; up to 90 days on Enterprise and above if configured; 0 for transient tables set that way). Act fast — and check DATA_RETENTION_TIME_IN_DAYS on the table.

#3. Beyond Time Travel: Fail-safe

After Time Travel expires, permanent tables enter a 7-day Fail-safe period. Only Snowflake Support can recover from it, it's best-effort, and it can take time. Transient and temporary tables have no Fail-safe.

#4. Pause the pipeline

Before restoring, stop the job that caused it (task, dbt run, Airflow DAG) so it doesn't overwrite your restore:

ALTER TASK sales.public.load_orders SUSPEND;

#Prevent it next time

• Set DATA_RETENTION_TIME_IN_DAYS to 7+ on important tables/schemas (Enterprise edition).
• Separate roles: pipelines get DML on their tables, not OWNERSHIP (which allows DROP).
• Clone before risky operations: CREATE TABLE orders_bkp_20261025 CLONE orders; costs nothing until data diverges.
• For critical data, add replication/failover groups to another account or backups via Snowflake's backup features where available.