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
SELECT * FROM orders
AT (TIMESTAMP => '2026-03-21 14:00:00'::TIMESTAMP_NTZ);
CREATE OR REPLACE TABLE orders
CLONE orders AT (TIMESTAMP => '2026-03-21 14:00:00'::TIMESTAMP_NTZ);
UNDROP TABLE orders;
UNDROP SCHEMA my_schema;
UNDROP DATABASE my_database;
SELECT * FROM orders AT (OFFSET => -300);
SELECT * FROM orders BEFORE (STATEMENT => '<query_id_of_bad_update>');
ALTER TABLE critical_data SET DATA_RETENTION_TIME_IN_DAYS = 90;
ALTER TABLE temp_staging SET DATA_RETENTION_TIME_IN_DAYS = 0;
Step 2: Database Replication Across Regions
ALTER DATABASE PROD_DW ENABLE REPLICATION TO ACCOUNTS
myorg.us_east_account,
myorg.eu_west_account;
CREATE DATABASE PROD_DW_REPLICA
AS REPLICA OF myorg.us_west_account.PROD_DW;
ALTER DATABASE PROD_DW_REPLICA REFRESH;
SELECT * FROM TABLE(INFORMATION_SCHEMA.DATABASE_REPLICATION_USAGE_HISTORY(
DATE_RANGE_START => DATEADD(hours, -24, CURRENT_TIMESTAMP())
));
SELECT database_name, primary_snowflake_region,
replication_allowed, is_primary,
DATEDIFF('minute', snowflake_region_last_refresh_time, CURRENT_TIMESTAMP()) AS lag_minutes
FROM TABLE(INFORMATION_SCHEMA.REPLICATION_DATABASES())
WHERE database_name = 'PROD_DW_REPLICA';
Step 3: Account Failover Groups
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';
CREATE FAILOVER GROUP prod_failover
AS REPLICA OF myorg.us_west_account.prod_failover;
ALTER FAILOVER GROUP prod_failover PRIMARY;
ALTER FAILOVER GROUP prod_failover PRIMARY;
Step 4: Application-Level Connection Failover
import snowflake from 'snowflake-sdk';
interface FailoverConfig {
primary: snowflake.ConnectionOptions;
secondary: snowflake.ConnectionOptions;
healthCheckIntervalMs: number;
}
class ResilientSnowflakeConnection {
private activeConn: snowflake.Connection | null = null;
private isPrimary = true;
private config: FailoverConfig;
constructor(config: FailoverConfig) {
this.config = config;
}
async connect(): Promise<snowflake.Connection> {
try {
this.activeConn = await this.tryConnect(this.config.primary);
this.isPrimary = true;
return this.activeConn;
} catch (primaryErr) {
.();
{
. = .(..);
. = ;
.;
} (secondaryErr) {
();
}
}
}
(: snowflake.): <snowflake.> {
( {
conn = snowflake.(opts);
conn.( (err ? (err) : (conn)));
});
}
(): <{ : ; : }> {
{
<>( {
.!.({
: ,
: (err ? (err) : ()),
});
});
{ : , : . };
} {
{ : , : . };
}
}
}
resilientConn = ({
: {
: ,
: process..!,
: process..!,
: ,
: ,
},
: {
: ,
: process..!,
: process..!,
: ,
: ,
},
: ,
});
Step 5: Pipeline Retry and Idempotency
MERGE INTO 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
WHEN NOT MATCHED THEN INSERT
(order_id, customer_id, amount, order_date)
VALUES
(source.order_id, source.customer_id, source.amount, source.order_date);
CREATE OR REPLACE TASK reliable_transform
WAREHOUSE = ETL_WH
SCHEDULE = '5 MINUTE'
ALLOW_OVERLAPPING_EXECUTION = FALSE
SUSPEND_TASK_AFTER_NUM_FAILURES = 3
WHEN SYSTEM$STREAM_HAS_DATA('orders_stream')
AS
MERGE INTO dim_orders ...;
ALTER TASK reliable_transform RESUME;
Step 6: Backup Strategy
CREATE DATABASE PROD_DW_BACKUP_20260322
CLONE PROD_DW;
CREATE OR REPLACE TASK daily_backup
WAREHOUSE = ADMIN_WH
SCHEDULE = 'USING CRON 0 3 * * * UTC'
AS
BEGIN
LET backup_name VARCHAR := 'PROD_DW_BACKUP_' || TO_CHAR(CURRENT_DATE(), 'YYYYMMDD');
EXECUTE IMMEDIATE 'CREATE DATABASE IF NOT EXISTS ' || :backup_name || ' CLONE PROD_DW';
END;
Recovery Time Objectives
| Scenario | Recovery Method | RTO | RPO |
|---|
| Accidental table drop | UNDROP | Instant | Zero |
| Bad data update | Time Travel CLONE | Minutes | Configurable (up to 90 days) |
| Regional outage | Failover group | 10-30 min | Replication lag |
| Account compromise | Contact Snowflake support | Hours | Last backup |
Error Handling
| Issue | Cause | Solution |
|---|
| Time Travel expired | Past retention period | Increase DATA_RETENTION_TIME_IN_DAYS |
| Replication lag high | Large data changes | Check replication schedule, network |
| Failover fails | Secondary not synced | Verify failover group status |
| UNDROP fails | Object recreated with same name | Rename current object first |
Resources
Next Steps
For policy enforcement, see snowflake-policy-guardrails.
