Implement Snowflake reliability patterns: replication, failover, Time Travel recovery,
and application-level resilience for Snowflake integrations.
Use when building fault-tolerant pipelines, configuring disaster recovery,
or adding resilience to production Snowflake services.
Trigger with phrases like "snowflake reliability", "snowflake failover",
"snowflake replication", "snowflake disaster recovery", "snowflake Time Travel".
Implement Snowflake reliability patterns: replication, failover, Time Travel recovery,
and application-level resilience for Snowflake integrations.
Use when building fault-tolerant pipelines, configuring disaster recovery,
or adding resilience to production Snowflake services.
Trigger with phrases like "snowflake reliability", "snowflake failover",
"snowflake replication", "snowflake disaster recovery", "snowflake Time Travel".
allowed-tools
Read, Write, Edit
version
1.5.0
license
MIT
author
Jeremy Longshore <jeremy@intentsolutions.io>
tags
["saas","data-warehouse","analytics","snowflake"]
compatibility
Designed for Claude Code
Snowflake Reliability Patterns
Overview
Production-grade reliability patterns for Snowflake: database replication, account failover, Time Travel recovery, and application-level circuit breakers.
Instructions
Step 1: Time Travel for Point-in-Time Recovery
-- Query historical data (up to 90 days on Enterprise Edition)SELECT*FROM orders
AT (TIMESTAMP=>'2026-03-21 14:00:00'::TIMESTAMP_NTZ);
-- Restore a table to a previous stateCREATEOR REPLACE TABLE orders
CLONE orders AT (TIMESTAMP=>'2026-03-21 14:00:00'::TIMESTAMP_NTZ);
-- Restore a dropped table
UNDROP TABLE orders;
UNDROP SCHEMA my_schema;
UNDROP DATABASE my_database;
-- Query by offset (5 minutes ago)SELECT*FROM orders AT (OFFSET=>-300);
-- Query by statement ID (before a specific query ran)SELECT*FROM orders BEFORE (STATEMENT =>'<query_id_of_bad_update>');
-- Set retention period per tableALTER TABLE critical_data SET DATA_RETENTION_TIME_IN_DAYS =90;
ALTER TABLE temp_staging SET DATA_RETENTION_TIME_IN_DAYS ;
=
0
-- No Time Travel
Step 2: Database Replication Across Regions
-- Enable replication on source account (primary)ALTER DATABASE PROD_DW ENABLE REPLICATION TO ACCOUNTS
myorg.us_east_account,
myorg.eu_west_account;
-- On target account: create replica databaseCREATE DATABASE PROD_DW_REPLICA
AS REPLICA OF myorg.us_west_account.PROD_DW;
-- Refresh replica (manual or scheduled)ALTER DATABASE PROD_DW_REPLICA REFRESH;
-- Check replication statusSELECT*FROMTABLE(INFORMATION_SCHEMA.DATABASE_REPLICATION_USAGE_HISTORY(
DATE_RANGE_START => DATEADD(hours, -24, CURRENT_TIMESTAMP())
));
-- Check replication lagSELECT database_name, primary_snowflake_region,
replication_allowed, is_primary,
DATEDIFF('minute', snowflake_region_last_refresh_time, CURRENT_TIMESTAMP()) AS lag_minutes
FROMTABLE(INFORMATION_SCHEMA.REPLICATION_DATABASES())
WHERE database_name ='PROD_DW_REPLICA';
Step 3: Account Failover Groups
-- Create failover group (replicates databases, warehouses, roles, etc.)-- On primary account:CREATE FAILOVER GROUP prod_failover
OBJECT_TYPES = DATABASES, WAREHOUSES, ROLES, USERS, INTEGRATIONS
ALLOWED_DATABASES = PROD_DW
ALLOWED_ACCOUNTS = myorg.us_east_account
REPLICATION_SCHEDULE ='10 MINUTE';
-- On secondary account: create as replicaCREATE FAILOVER GROUP prod_failover
AS REPLICA OF myorg.us_west_account.prod_failover;
-- Promote secondary to primary (during outage)ALTER FAILOVER GROUP prod_failover PRIMARY;
-- After recovery, switch back-- On original primary:ALTER FAILOVER GROUP prod_failover PRIMARY;
-- Idempotent data loading (safe to retry)MERGEINTO silver.orders AS target
USING (
SELECT*FROM bronze.raw_orders
WHERE ingestion_time >= DATEADD(hours, -1, CURRENT_TIMESTAMP())
) AS source
ON target.order_id = source.order_id
WHENNOT MATCHED THENINSERT
(order_id, customer_id, amount, order_date)
VALUES
(source.order_id, source.customer_id, source.amount, source.order_date);
-- MERGE is idempotent: running it twice with the same data produces the same result-- Prefer MERGE over INSERT for retry-safe pipelines-- Task retry configurationCREATEOR REPLACE TASK reliable_transform
WAREHOUSE = ETL_WH
SCHEDULE ='5 MINUTE'
ALLOW_OVERLAPPING_EXECUTION =FALSE-- Prevent concurrent runs
SUSPEND_TASK_AFTER_NUM_FAILURES =3-- Auto-suspend after 3 failuresWHENSYSTEM$STREAM_HAS_DATA('orders_stream')
ASMERGEINTO dim_orders ...;
ALTER TASK reliable_transform RESUME;
Step 6: Backup Strategy
-- Zero-copy clone backups (instant, no extra storage until data changes)CREATE DATABASE PROD_DW_BACKUP_20260322
CLONE PROD_DW;
-- Automated daily backup via taskCREATEOR REPLACE TASK daily_backup
WAREHOUSE = ADMIN_WH
SCHEDULE ='USING CRON 0 3 * * * UTC'ASBEGIN
LET backup_name VARCHAR :='PROD_DW_BACKUP_'|| TO_CHAR(CURRENT_DATE(), 'YYYYMMDD');
EXECUTE IMMEDIATE 'CREATE DATABASE IF NOT EXISTS '|| :backup_name ||' CLONE PROD_DW';
-- Clean up backups older than 7 days-- (done via separate cleanup task or stored procedure)END;
