Course contents

SnowPro Core COF-C03 Study GuidePart 5: Data protection and sharingDatabase Replication & Failover

Database Replication & Failover

Database Replication & Failover

Definition

Replication copies a database's data and metadata to a target account, potentially in a different region or even a different cloud provider, keeping it in sync on a schedule. Failover promotes a replica to primary during an outage; failback reverses that once the original recovers.

Replication and failover between two accounts of one organization. Account A is in AWS eu-west-1, account B in Azure West Europe. Normal day: prod_db on account A is the read-write primary; a scheduled refresh copies changed data to a read-only secondary on account B, and account B pays the replication compute, transfer and storage. Region outage: account A is unreachable; promoting the secondary on account B makes it the read-write primary. Failover needs Business Critical edition or higher. Data changed after the last refresh is lost.NORMAL DAY · REPLICATION · ALL EDITIONSREGION OUTAGE · FAILOVER · BUSINESS CRITICAL+ACCOUNT A · AWS EU-WEST-1ACCOUNT B · AZURE WEST EUROPEPRIMARYprod_dbread-write · apps write hereSECONDARYprod_dbread-only replicacopies changed databilled to account BPRIMARY · REGION DOWNprod_dbunreachableNEW PRIMARYprod_dbpromoted: now read-writeRUN ON BALTER …PRIMARY✓✗Recovery point: changes made after the last refresh are lost.Client Redirect (Business Critical+) points the apps' connection URL at account B.scheduled REFRESH

ACCOUNT A · AWS EU-WEST-1

ACCOUNT B · AZURE WEST EUROPE

NORMAL DAY · REPLICATION · ALL EDITIONS

PRIMARYprod_dbread-write · apps write here

SECONDARYprod_dbread-only replica

scheduled REFRESH
copies changed data

billed to account B

REGION OUTAGE · FAILOVER · BUSINESS CRITICAL+

✗PRIMARY · REGION DOWNprod_dbunreachable

RUN ON BALTER … PRIMARY

✓NEW PRIMARYprod_dbpromoted: now read-write

Recovery point: changes made after the last refresh are lost.

Client Redirect (Business Critical+) points the apps' connection URL at account B.

Figure 5.4 Replication keeps a read-only copy up to date; failover turns that copy into the primary. Top row: a normal day, with refreshes billed to the target account. Bottom row: during an outage you promote the secondary, which requires Business Critical or higher.

Two boundaries keep this honest. Replication targets are accounts in the same organization, and what replicates is the database's contents, not the account around it: shares go into a replication group, on any edition, while users, roles and warehouses need a replication or failover group on Business Critical, or their own setup on the target side. The distinction that matters is edition: replicating for read access is broadly available, but promoting a secondary (failover) and returning (failback) require Business Critical.

A replication group is a set of objects replicated and refreshed together; on any edition it can hold databases and shares. A failover group is a replication group that can also be promoted to primary. Putting users, roles or warehouses in a group needs Business Critical. Snowflake recommends groups over database-level replication; the example below shows both. Failover and failback, where an administrator promotes a secondary to primary (Snowflake doesn't fail over by itself), require Business Critical edition or above.

-- Recommended: a failover group (Business Critical)
-- Source account
CREATE FAILOVER GROUP prod_fg
  OBJECT_TYPES = DATABASES, ROLES, USERS, WAREHOUSES
  ALLOWED_DATABASES = prod_db
  ALLOWED_ACCOUNTS = myorg.eu_account
  REPLICATION_SCHEDULE = '10 MINUTE';
-- Target account
CREATE FAILOVER GROUP prod_fg AS REPLICA OF myorg.us_account.prod_fg;
ALTER FAILOVER GROUP prod_fg REFRESH;
ALTER FAILOVER GROUP prod_fg PRIMARY;   -- during an outage

-- Simple case: one database
ALTER DATABASE prod_db ENABLE REPLICATION TO ACCOUNTS myorg.eu_account;   -- source
ALTER DATABASE prod_db ENABLE FAILOVER TO ACCOUNTS myorg.eu_account;      -- source, Business Critical
CREATE DATABASE prod_db AS REPLICA OF myorg.us_account.prod_db;           -- target
ALTER DATABASE prod_db REFRESH;
ALTER DATABASE prod_db PRIMARY;                                           -- during an outage

A secondary (replica) database is read-only until promoted; applications keep writing to the primary. Refreshes aren't continuous streaming, they're triggered, either manually or on a schedule, so replicas trail the primary by however long since the last refresh, an RPO (recovery point objective) consideration worth flagging in any disaster recovery design.

Key takeaways
  • Time Travel: self-service, 0–1 day Standard, 0–90 days Enterprise+, controlled by DATA_RETENTION_TIME_IN_DAYS with an account-level floor.
  • Fail-safe: fixed 7 days, Support-only, permanent tables exclusively.
  • Cloning: instant, metadata-only, storage-cheap until modified; grants on the cloned container itself don't carry over, grants on its child objects do.
  • Replication: broadly available. Failover/failback: Business Critical+.