6.2 Disaster Recovery Architecture & Client Redirect

6.2 Disaster Recovery Architecture & Client Redirect

A comprehensive disaster recovery (DR) architecture ensures business continuity during regional cloud outages, severe infrastructure degradation, or cyber incidents. For an enterprise data platform, DR is evaluated on two core metrics:

1. Recovery Point Objective (RPO): The maximum acceptable age of data lost during an interruption (data currency loss).
2. Recovery Time Objective (RTO): The maximum acceptable elapsed duration between an outage declaration and the complete restoration of functional business operations (downtime duration).

Snowflake combines Failover Groups with Client Redirect connection routing to achieve near-zero RTO and low RPO across cloud providers and geographical regions without requiring manual DNS configuration or updating connection strings in hundreds of downstream client applications.

Business Continuity Metrics: RPO vs. RTO in Snowflake

Normal Operations (Primary Active)
├──────────────────────────┼─────────────────────────►
   T1: Last Completed         T2: Primary Cloud
   Replication Snapshot       Outage Occurs
   │◄─────── RPO ────────────►│
   │  (Data Loss Window)      │◄─────── RTO ────────►│
   │                          │  (Downtime Window)   │
                              ▼                      ▼
                       Outage Occurs          Failover Promoted:
                                              Secondary Read-Write
                                              Connection Redirected

RPO Mechanics & Tradeoffs

In Snowflake, RPO represents the delta between the time of the incident and the timestamp of the last successful replication snapshot committed to the secondary account: $\text{Actual RPO} = \text{Outage Timestamp} - \text{Last Successful Refresh Timestamp}$

• If REPLICATION_SCHEDULE = '10 MINUTE' and synchronization requires 2 minutes, the theoretical maximum RPO is 12 minutes under steady-state conditions.
• Architectural Levers to Minimize RPO:
  • Decrease the replication schedule interval (e.g., from 1 HOUR to 10 MINUTE).
  • Partition large databases into distinct failover groups: replicate critical transactional and operational reporting databases on high-frequency schedules (e.g., 10 minutes), while replicating historical archive or staging databases daily.
• Cost Impact of Lower RPO: Shorter replication intervals trigger more frequent serverless compute checks and snapshot delta calculations, slightly increasing credit consumption and cloud egress charges.

RTO Mechanics & Promotion Speed

In Snowflake, RTO represents the time required to detect an outage, declare disaster recovery, execute promotion commands, and redirect client connections: $\text{Actual RTO} = \text{Detection Time} + \text{Promotion Execution Time} + \text{DNS / Routing Convergence}$

• The physical execution of ALTER FAILOVER GROUP ... PRIMARY completes in seconds to minutes, regardless of database size, because metadata pointers are modified in the cloud services layer without moving physical micro-partitions.
• Client Redirect DNS routing updates propagate through Snowflake's global routing infrastructure within seconds.
• With practiced runbooks and automation, the promotion steps themselves are fast; overall RTO is usually dominated by detection, decision-making, and restarting pipelines.

RPO vs. RTO Architectural Matrix

Snowflake Client Redirect Architecture

Historically, failing over a database fleet required updating DNS records, managing external global load balancers, or manually reconfiguring database connection strings in hundreds of BI dashboards (Tableau, PowerBI), ETL/ELT pipelines (dbt, Airflow), and custom application microservices.

Snowflake eliminates this operational friction through Client Redirect (Business Critical Edition or higher), which introduces connection objects — account-level objects whose URL belongs to the organization rather than to one account.

Connection Objects & Unified Connection URLs

A Connection Object provides an abstract, cloud-agnostic endpoint URL that client applications connect to. Behind the scenes, Snowflake routes incoming connection requests to whichever account currently holds the PRIMARY status for that connection.

                              Client Applications
                        (JDBC, ODBC, Python, SnowSQL)
                                     │
                                     ▼
                 Unified Connection URL (Decoupled Namespace)
          https://acme_org-bi_reporting_conn.snowflakecomputing.com
                                     │
                         Snowflake Global Routing
                                     │
           ┌─────────────────────────┴─────────────────────────┐
           │                                                   │
     (Normal State)                                     (Failover State)
           ▼                                                   ▼
   Primary Account (AWS)                               Secondary Account (Azure)
   Connection Status: PRIMARY                          Connection Status: PRIMARY
   Failover Group: PRIMARY (Read-Write)                Failover Group: PRIMARY (Read-Write)

Unified Connection URL Format

Connection URLs follow a standardized organization-scoped naming convention:

https://<organization_name>-<connection_name>.snowflakecomputing.com

Example: https://acme_org-bi_reporting_conn.snowflakecomputing.com

Declarative Connection Configuration Workflow

Configuring Client Redirect requires executing DDL in the primary account and subsequently creating a replica in the secondary account:

-- Step 1: Execute on Primary Account (e.g., prod_aws_useast1) with ORGADMIN or ACCOUNTADMIN
USE ROLE ACCOUNTADMIN;

-- Create the primary connection object
CREATE CONNECTION bi_reporting_conn
  COMMENT = 'Global connection endpoint for enterprise BI dashboards';

-- Enable failover to secondary accounts in Azure and GCP
ALTER CONNECTION bi_reporting_conn
  ENABLE FAILOVER TO ACCOUNTS acme_org.prod_azure_westeurope, acme_org.prod_gcp_uscentral1;

-- Step 2: Execute on Secondary Account (e.g., prod_azure_westeurope)
USE ROLE ACCOUNTADMIN;

-- Create replica of the connection object
CREATE CONNECTION bi_reporting_conn
  AS REPLICA OF acme_org.prod_aws_useast1.bi_reporting_conn;

Connection Failover Promotion

When failover is initiated, an administrator promotes the connection on the secondary account. Snowflake's global routing layer updates immediately:

-- Executed on Secondary Account during disaster recovery
USE ROLE ACCOUNTADMIN;

ALTER CONNECTION bi_reporting_conn PRIMARY;

Architect Exam Tip: Promoting a connection object does not automatically promote the underlying failover group, and promoting a failover group does not automatically promote the connection object. Both operations must be executed (or orchestrated together in an automated script) during a disaster recovery event.

Disaster Recovery Drills & Failover Execution Lifecycle

Enterprise architects must distinguish between two operational failover scenarios: Planned Failover (disaster recovery testing, regional maintenance) and Unplanned Failover (catastrophic cloud provider blackout).

Scenario 1: Planned Failover (Zero Data Loss Maintenance Drill)

During a scheduled disaster recovery drill or proactive regional cloud migration, the primary account remains accessible. The goal is achieving RPO = 0:

-- Phase 1: On Secondary Account (prod_azure_westeurope), trigger final sync to capture all deltas
USE ROLE ACCOUNTADMIN;
ALTER FAILOVER GROUP fg_enterprise_dr REFRESH;

-- Phase 2: Promote Failover Group to Primary on Secondary Account
ALTER FAILOVER GROUP fg_enterprise_dr PRIMARY;

-- Phase 3: Promote Connection Object to Primary on Secondary Account
ALTER CONNECTION bi_reporting_conn PRIMARY;

Result of Planned Promotion:

• The secondary account in Azure transitions from read-only to read-write.
• The former primary account in AWS is automatically demoted to a secondary replica.
• Client traffic arriving via the connection URL is routed to Azure.
• Zero data is lost because the manual refresh executed immediately prior to promotion.

Scenario 2: Unplanned Failover (Emergency Outage Runbook)

When the primary cloud region suffers a total catastrophic loss (e.g., datacenter power loss or fiber severance), the primary account is unreachable. Manual refresh cannot execute.

-- Phase 1: Execute emergency promotion on Secondary Account (Azure)
USE ROLE ACCOUNTADMIN;

-- Force promotion of Failover Group
ALTER FAILOVER GROUP fg_enterprise_dr PRIMARY;

-- Force promotion of Connection Object
ALTER CONNECTION bi_reporting_conn PRIMARY;

Inflight Transactions & Data Loss Realities

In an unplanned failover event:

1. Active Inflight Queries: Any queries, multi-statement transactions, or COPY INTO bulk loading operations actively executing on the failed primary at the time of the crash are aborted and terminated. They are not buffered or replayed by Snowflake.
2. Unreplicated Committed Data: Transactions committed on the primary after the last successful replication refresh are unrecoverable in the secondary account at failover time. This data loss reflects the RPO window.
3. Read-Only to Read-Write State: The secondary replica immediately begins accepting write queries. Orchestration tools must repoint or restart application pipelines to resume ingestion.

Failback & Re-synchronization Procedure

Once the original cloud region recovers, the former primary account is in a stale, diverged state. It cannot immediately resume as primary without risking data corruption or overwriting writes executed in Azure during the outage.

Outage Ends & Primary Region Recovers
                    │
                    ▼
1. Recovered Former Primary assumes SECONDARY status
                    │
                    ▼
2. Reverse Synchronization: Former Primary refreshes from New Primary (Azure)
   ALTER FAILOVER GROUP fg_enterprise_dr REFRESH;
                    │
                    ▼
3. Planned Failback: Promote original account back to PRIMARY
   ALTER FAILOVER GROUP fg_enterprise_dr PRIMARY;
   ALTER CONNECTION bi_reporting_conn PRIMARY;

-- Executed on original AWS account once restored:
USE ROLE ACCOUNTADMIN;

-- Step 1: Re-synchronize changes made in Azure back into AWS
ALTER FAILOVER GROUP fg_enterprise_dr REFRESH;

-- Step 2: Execute planned failback to restore AWS as primary
ALTER FAILOVER GROUP fg_enterprise_dr PRIMARY;
ALTER CONNECTION bi_reporting_conn PRIMARY;