Explain Replication & Failover Across Regions in Databases

65 / 66

Snowflake

Replication & Failover Across Regions

Maintain business continuity by replicating databases and account objects across regions or clouds.

Snowflake replicates a database to another account, in another region, or on another cloud provider. That is how you survive a regional outage.

It's like maintaining a mirror office in another city that's kept in sync with regular data deliveries — if the main office suddenly goes dark, the mirror office can be promoted to take over operations, ready-made with the same files, staff badges, and access rules already in place.

Key Concepts

How it is wired. A primary database is linked to secondaries in target accounts. Refreshes copy only what changed since the last one:

    ALTER DATABASE sales_db REFRESH;    -- run in the secondary account

Secondaries are read-only until a failover promotes one to primary.

It is not continuous by default. Replication is triggered, usually by a task on a schedule. The interval you choose is your recovery point objective:

    refresh every 10 minutes  ->  up to 10 minutes of data lost in a failover
    refresh every 24 hours    ->  up to a day lost, at a fraction of the cost

State that trade in an interview rather than saying "it replicates". Frequency is a conscious choice between data loss and cost.

Replicating a database alone leaves you unable to use it. The data arrives; the users, roles, warehouses and network policies do not. After failover, nothing can connect and nothing has permission.

Failover groups fix that, by replicating account objects as a unit:

    CREATE FAILOVER GROUP prod_fg
      OBJECT_TYPES = DATABASES, ROLES, USERS, WAREHOUSES,
                     RESOURCE MONITORS, NETWORK POLICIES
      ALLOWED_DATABASES = sales_db, ops_db
      ALLOWED_ACCOUNTS = myorg.backup_account
      REPLICATION_SCHEDULE = '10 MINUTE';

Now promoting the secondary gives you a working account, not just a copy of the rows.

What failover does and does not do. Promotion is a command and takes effect quickly. It does not move your applications — client connection strings point at a specific account URL, so somebody or something must repoint them. Connection URLs, DNS, and secrets management are part of the plan, and the part most often missing when the plan is tested.

Costs. Compute to detect and transfer changes, plus storage for the second copy, plus cross-region egress. Replicating everything continuously is expensive and rarely justified.

The realistic approach is tiering: critical databases replicated every few minutes, important ones daily, and the rest not at all — because they can be rebuilt from source systems.

And test the failover. A replication configuration that has never been promoted is a belief, not a capability. The usual discoveries are missing grants, a hard-coded account URL, and nobody being sure who is allowed to press the button.

What the interviewer is probing.1. "What is replicated?" Probing: the scope. Stalls: "Everything." Moves up: databases, and with a failover group also users, roles, warehouses and other account objects — which is what makes failover usable.

2. "How does a client fail over?" Probing: the mechanism. Stalls: "Change the URL." *Moves up:* a connection URL on the replication group, so promoting the secondary redirects clients without reconfiguring them.

3. "What is the data loss exposure?" Probing: honesty. Stalls: "None." Moves up: replication is asynchronous, so the recovery point is the last completed refresh — the schedule sets your RPO.

4. "What must be tested?" Probing: the operational point. Stalls: "Nothing." Moves up: an actual failover drill — an untested failover plan is a guess, and costs are incurred for the secondary regardless.