π Replication & Business Continuity in Snowflake
Disaster recovery and business continuity are critical requirements for enterprise data platforms. Snowflake provides robust features to replicate databases and account metadata across different regions and cloud providers, ensuring minimal data loss (RPO) and quick recovery times (RTO) during cloud outages.
π― Core Concepts & Architectureβ
Snowflakeβs replication model operates at two main levels:
- Database Replication: Replicates database objects (tables, views, schemas) and transactional data.
- Account Replication: Replicates account-level metadata (users, roles, privileges, warehouses, resource monitors) using Replication Groups and Failover Groups.
π‘ RPO & RTO Planningβ
When designing a Business Continuity plan, two metrics drive the architecture:
- Recovery Point Objective (RPO): The maximum acceptable period during which data might be lost due to an outage. In Snowflake, this is controlled by the replication frequency (e.g., sync every 10 minutes = 10-minute RPO).
- Recovery Time Objective (RTO): The maximum acceptable duration of downtime before the platform is restored. In Snowflake, promoting a secondary database/failover group to primary takes only a few seconds, enabling an RTO of under 1 minute.
π Database Replication (Primary β‘οΈ Secondary)β
Database replication copies data from a Primary Database to a read-only Secondary Database in another Snowflake account (which can be in a different region or cloud provider).
Step 1: Enable Replication on the Primary Accountβ
Before replicating, the account administrator must allow replication to the target secondary account.
-- Run as ACCOUNTADMIN on the Primary Account
ALTER DATABASE sales_db ENABLE REPLICATION TO ACCOUNTS azure_eu.account_name;
Step 2: Create the Secondary Databaseβ
On the target account, create a replica database using the AS REPLICA OF syntax.
-- Run as ACCOUNTADMIN on the Target (Secondary) Account
CREATE DATABASE sales_db
AS REPLICA OF aws_us.account_name.sales_db;
Step 3: Manually Refresh the Secondary Databaseβ
To pull the latest changes from the primary database:
-- Run on the Target Account
ALTER DATABASE sales_db REFRESH;
π₯ Account Replication & Failover Groupsβ
For a complete DR strategy, you must replicate not just data but also the users, roles, network policies, and permissions that grant access to that data. Snowflake uses Failover Groups to bundle databases and account metadata together.
-- Failover Groups enable unified replication of:
- DATABASES
- USERS & ROLES
- GRANTS & PRIVILEGES
- WAREHOUSES (Configuration)
- INTEGRATIONS (API, Storage, Security)
1. Create a Failover Group on the Primary Accountβ
Create a failover group containing both databases and metadata, and define which target accounts can receive it.
-- Run as ACCOUNTADMIN on the Primary Account
CREATE FAILOVER GROUP my_dr_group
OBJECT_TYPES = DATABASES, USERS, ROLES, WAREHOUSES, NETWORK_POLICIES
ALLOWED_DATABASES = sales_db, inventory_db
ALLOWED_ACCOUNTS = azure_eu.account_name;
2. Create the Replica Failover Group on the Secondary Accountβ
-- Run as ACCOUNTADMIN on the Target Account
CREATE FAILOVER GROUP my_dr_group
AS REPLICA OF aws_us.account_name.my_dr_group;
3. Schedule Automatic Replicationβ
Instead of manual refreshes, you can automate replication by specifying a replication schedule on the primary failover group.
-- Run on the Primary Account to sync every 10 minutes (10m RPO)
ALTER FAILOVER GROUP my_dr_group SET REPLICATION_SCHEDULE = '10 MINUTE';
π¨ Failover & Failback Proceduresβ
During an outage in the primary region/cloud provider, you must promote the secondary replica to be the primary active instance.
Performing a Failover (Promoting Secondary)β
When the primary cloud region is experiencing an outage, run the promote command in the secondary account. The secondary replica immediately becomes read-write.
-- Run as ACCOUNTADMIN on the Target (Secondary) Account
ALTER FAILOVER GROUP my_dr_group PRIMARY;
Performing a Failback (Restoring Original State)β
Once the outage is resolved and the original primary region is fully functional:
- Sync any new data written to the backup target back to the original primary account.
- Demote the backup target and restore the original account as the active primary.
-- 1. Run REFRESH on the original Primary Account to sync updates made during DR
ALTER FAILOVER GROUP my_dr_group REFRESH;
-- 2. Promote the original Primary Group back to active primary
ALTER FAILOVER GROUP my_dr_group PRIMARY;
π Cross-Region vs. Cross-Cloud Replicationβ
| Scenario | Cross-Region Replication | Cross-Cloud Replication |
|---|---|---|
| Outage Protected | Protected against localized regional disaster (e.g., AWS us-east-1 down). | Protected against complete provider outage (e.g., entire AWS platform down). |
| Network Cost | standard regional data egress charges. | cross-cloud egress charges (potentially higher). |
| Latency | lower sync latency between nearby regions. | latency depends on provider backbones. |
| Ideal For | High availability and compliance within a single cloud provider. | Ultimate enterprise business continuity and disaster recovery. |
π Disaster Recovery Runbook Templateβ
A robust DR testing strategy requires executing scheduled mock failovers at least twice a year. Below is an outline for a DR runbook:
### ποΈ Disaster Recovery Test Runbook
1. **Pre-requisites & Verification**:
- Check sync health: `SHOW REPLICATION DATABASES;`
- Record the last sync timestamp to verify data replication state.
2. **Simulate Outage**:
- Temporarily revoke network connection credentials/access to AWS Primary workspace.
3. **Execute Failover**:
- Log into Azure Secondary account.
- Run: `ALTER FAILOVER GROUP my_dr_group PRIMARY;`
4. **Verify Application Connectivity**:
- Point test applications to the secondary account connection URL.
- Run verify queries: `SELECT COUNT(*) FROM sales_db.public.orders;` (Ensure write actions succeed).
5. **Execute Failback**:
- Log back into AWS Primary account.
- Run sync: `ALTER FAILOVER GROUP my_dr_group REFRESH;`
- Promote primary: `ALTER FAILOVER GROUP my_dr_group PRIMARY;`
π Key Takeawaysβ
- π Complete Protection: Replicate databases alongside account metadata (users, roles, warehouses) using Failover Groups.
- β‘ Fast Recovery: Promotion of a secondary failover group takes seconds, enabling an RTO of under 1 minute.
- π Cloud Agnostic: Replicate across different regions (Cross-Region) or different cloud vendors (Cross-Cloud) seamlessly using Snowgrid.