Snowflake Replication and Failover

Outages, regional disasters, and migrations threaten every data platform. Replication copies selected Snowflake objects from one account to another account, in the same region or a different one. Failover lets the secondary account take over when the primary account becomes unavailable. Together they protect business continuity.

Everyday Comparison

A newspaper keeps its main printing press in one city and a backup press in another. Every night, the layout files copy to the backup site. When flooding closes the main plant, staff at the backup site start printing. Customers barely notice. Replication is the nightly copy, and failover is the switch to the backup press.

REPLICATION AND FAILOVER OVERVIEW
=================================

 PRIMARY ACCOUNT (AWS US East)        SECONDARY ACCOUNT (AWS US West)
 +----------------------+   refresh   +----------------------+
 | SALES_DB             | ----------> | SALES_DB (replica)   |
 | Users, roles         |  every 10   | Users, roles         |
 | Warehouses           |  minutes    | Warehouses           |
 +----------------------+             +----------------------+
         |                                     |
   Read and write                      Read only until promoted
         |                                     |
   OUTAGE  X  ---- failover promotes ---->  Read and write

What Replication Can Copy

  • Databases and their schemas, tables, and views.
  • Users, roles, and grants.
  • Warehouses and resource monitors.
  • Network policies and integrations in supported scenarios.

Replication groups handle databases and shares. Failover groups add account-level objects such as users, roles, and warehouses, and they support promotion of the secondary account.

Setting Up a Replication Group

-- On the PRIMARY account
CREATE REPLICATION GROUP sales_rg
  OBJECT_TYPES      = DATABASES
  ALLOWED_DATABASES = sales_db
  ALLOWED_ACCOUNTS  = myorg.account_west
  REPLICATION_SCHEDULE = '10 MINUTE';

-- On the SECONDARY account
CREATE REPLICATION GROUP sales_rg
  AS REPLICA OF myorg.account_east.sales_rg;

-- Run the first refresh manually
ALTER REPLICATION GROUP sales_rg REFRESH;

The schedule keeps the secondary account current without manual work. Each refresh transfers only changed data after the first full copy.

Failover Groups and Promotion

Failover groups require Business Critical Edition or higher. The group lists every object type the secondary account needs to run business operations.

-- On the PRIMARY account
CREATE FAILOVER GROUP sales_fg
  OBJECT_TYPES = USERS, ROLES, WAREHOUSES, RESOURCE MONITORS, DATABASES
  ALLOWED_DATABASES = sales_db
  ALLOWED_ACCOUNTS  = myorg.account_west
  REPLICATION_SCHEDULE = '10 MINUTE';

-- On the SECONDARY account
CREATE FAILOVER GROUP sales_fg
  AS REPLICA OF myorg.account_east.sales_fg;

-- During an outage, run on the SECONDARY account
ALTER FAILOVER GROUP sales_fg PRIMARY;

The last command promotes the secondary account to primary. The old primary becomes a secondary after it recovers, and teams can fail back when ready.

Client Redirect

Applications connect through account URLs. A connection object supplies one stable URL that points to whichever account currently holds the primary role. Tools keep using the same address during failover, and administrators change only the target behind it.

CREATE CONNECTION sales_conn;
ALTER CONNECTION sales_conn ENABLE FAILOVER TO ACCOUNTS myorg.account_west;

-- After promotion, point the connection to the new primary
ALTER CONNECTION sales_conn PRIMARY;

Two Recovery Numbers

TERM   MEANING                          EXAMPLE
-----  -------------------------------  ----------------------------------
RPO    Maximum data loss acceptable     10 minute schedule = up to 10 min
                                        of recent changes may be missing
RTO    Maximum downtime acceptable      Time needed to promote and reconnect

Shorter schedules shrink the data loss window and raise transfer costs.

Cost and Planning

  • Replication consumes serverless compute, storage in the secondary account, and cross-region data transfer.
  • Replicate only the databases that matter for recovery.
  • Rehearse failover drills at least twice a year.
  • Document the steps for switching tools, pipelines, and users.

A Practical Scenario

A payments company runs its main Snowflake account in one cloud region. Regulators require a tested recovery plan. The team creates a failover group that replicates the payments database, roles, and warehouses to an account in another region every ten minutes. Twice a year, the team promotes the secondary account during a planned drill, reconnects the reporting tools through a connection object, and measures the time taken. The drill results become part of the company's audit file.

Frequently Asked Questions

Is the secondary account usable while it waits?

The secondary copy of replicated databases stays read-only. Teams can query it for reporting, though the data lags behind by up to the refresh schedule.

Does replication copy data shared by other companies?

Databases created from inbound shares follow separate rules. Review the replication documentation for each object type before relying on it.

Key Points

  • Replication copies databases and account objects to a second account.
  • Failover promotes the secondary account when the primary becomes unavailable.
  • Failover groups need Business Critical Edition or higher.
  • Connection objects give applications one stable address during failover.
  • Schedule length sets the recovery point; promotion speed sets the recovery time.
Previous lesson
Back to course
Next lesson