Snowflake Database Replication: DR Design Guide

Database Replication is a Snowflake feature that replicates databases to accounts in different regions or cloud providers. Combined with Failover Group, it lets you build a robust architecture for disaster recovery (DR) and business continuity (BCP). Available on Business Critical Edition or higher.

Primary / Secondary Architecture

Snowflake replication operates on a Primary-Secondary model. The Primary database (or Failover Group) is the writable source of truth, and the Secondary is a read-only replica. The Secondary periodically receives incremental snapshots from the Primary to stay in sync.

┌──────────────────────────────┐     Replication          ┌──────────────────────────────┐
│  Source Account (US-EAST-1)  │ ──────────────────────→ │  Target Account (EU-WEST-1)  │
│                              │   Incremental snapshot   │                              │
│  [Primary]                   │                         │  [Secondary / Read-only]       │
│   ├─ DB: analytics           │                         │   ├─ DB: analytics (replica)   │
│   ├─ DB: raw_data            │                         │   ├─ DB: raw_data (replica)    │
│   ├─ Roles / Users           │                         │   ├─ Roles / Users (replica)   │
│   └─ Network Policies        │                         │   └─ Network Policies (replica)│
└──────────────────────────────┘                         └──────────────────────────────┘
        ↑ Normal ops run on Primary                           ↑ Promote to Primary on DR event

Replication Types

Creating a Failover Group

-- Create a Failover Group in the source account
CREATE FAILOVER GROUP prod_dr_group
  OBJECT_TYPES = DATABASES, ROLES, USERS, WAREHOUSES, NETWORK POLICIES, INTEGRATIONS
  ALLOWED_DATABASES = analytics, raw_data
  ALLOWED_INTEGRATION_TYPES = SECURITY INTEGRATIONS
  ALLOWED_ACCOUNTS = myorg.target_account
  REPLICATION_SCHEDULE = '10 MINUTE';

-- Create the Secondary Failover Group in the target account
CREATE FAILOVER GROUP prod_dr_group
  AS REPLICA OF myorg.source_account.prod_dr_group;

-- Manually trigger replication (for testing)
ALTER FAILOVER GROUP prod_dr_group REFRESH;

Failover Procedure

On a DR event, promote the Failover Group on the target account side to Primary. After promotion, the Failover Group on the original source account is demoted to Secondary.

-- Promote to Primary on the target account (DR activation)
ALTER FAILOVER GROUP prod_dr_group PRIMARY;

-- Failback: re-promote on the original source account
-- (Run this after data written on the target account has replicated back)
ALTER FAILOVER GROUP prod_dr_group PRIMARY;

RPO / RTO Design

RPO is determined by the REPLICATION_SCHEDULE interval. If you set it to 10 minutes, you may lose up to the last 10 minutes of data in the worst case. RTO depends on how long the failover command takes, which is driven by metadata volume (number of tables, views, etc.) rather than database size.

Client Redirect (Connection Failover)

Manually changing application connection targets after failover is operationally expensive, so Snowflake provides the Client Redirect (Connection Failover) feature. By using an Organization-level URL as the connection URL, clients are automatically redirected to the new Primary account on failover.

-- Organization URL format (supports Client Redirect)
-- https://<org_name>-<connection_name>.snowflakecomputing.com

-- Create the Connection object
CREATE CONNECTION prod_connection
  AS REPLICA OF myorg.source_account.prod_connection;

-- Switch the Primary connection
ALTER CONNECTION prod_connection PRIMARY;

Replication Monitoring

-- Check replication lag (run on the Secondary account)
SELECT SYSTEM$REPLICATION_LAG('analytics');

-- Refresh history and duration
SELECT
  replication_group_name,
  phase_name,
  start_time,
  end_time,
  DATEDIFF('SECOND', start_time, end_time) AS duration_sec,
  primary_snapshot_timestamp
FROM SNOWFLAKE.ACCOUNT_USAGE.REPLICATION_GROUP_REFRESH_HISTORY
WHERE start_time >= DATEADD(DAY, -7, CURRENT_TIMESTAMP())
ORDER BY start_time DESC;

-- Monitor credit consumption for replication
SELECT
  start_time::DATE AS usage_date,
  database_name,
  SUM(credits_used) AS total_credits,
  SUM(bytes_transferred) / POWER(1024, 3) AS gb_transferred
FROM SNOWFLAKE.ACCOUNT_USAGE.REPLICATION_USAGE_HISTORY
WHERE start_time >= DATEADD(MONTH, -1, CURRENT_TIMESTAMP())
GROUP BY 1, 2
ORDER BY usage_date DESC;

Edition Requirements Summary

DR Design Best Practices

• Include roles and network policies in the Failover Group: If you replicate only databases but lose your RBAC configuration, you cannot access the data. Include ROLES/USERS/NETWORK POLICIES in OBJECT_TYPES.
• Run regular DR drills: Execute the failover → failback procedure quarterly and record the measured RTO.
• Align REPLICATION_SCHEDULE with your RPO requirement: For an RPO of 10 minutes, set REPLICATION_SCHEDULE = '10 MINUTE' and configure lag-monitoring alerts.
• Adopt Client Redirect to eliminate application-side changes: Use the Organization URL to remove the need to reconfigure applications during failover.
• Estimate cross-region transfer costs: Transfers between different cloud regions are billed per GB, so estimate the cost of the initial full snapshot in advance.

Check Your Understanding

Frequently Asked Questions