Skip to main content

Disaster Recovery Testing & Runbooks

A Disaster Recovery (DR) plan that has never been tested is just a piece of paper. In an actual outage, panic sets in, steps are forgotten, and systems break. For enterprise data platforms (especially in regulated industries like banking or healthcare), regular DR testing is a strict compliance requirement.

This module covers how Snowflake Administrators design DR tests, write runbooks, and perform non-destructive drills.

Topics Covered​

  • Why DR Testing is Essential
  • Designing a Non-Destructive DR Drill
  • Application Reconnectivity
  • Writing a DR Runbook

Why DR Testing is Essential​

Imagine AWS Mumbai goes down. You failover to Azure Pune. The data is there, but suddenly you realize:

  1. The BI tool (Tableau) is hardcoded to the AWS URL, so dashboards are broken.
  2. The dbt pipeline fails because the specific Virtual Warehouse it uses wasn't included in the Failover Group.
  3. The Snowpipe ingestion completely stops because the AWS SQS queues don't exist in Azure.

DR testing exposes these gaps before a real disaster happens.

Designing a Non-Destructive DR Drill​

You do not want to test your DR plan by randomly turning off the Production database on a Tuesday morning. Instead, you design a Non-Destructive Drill using a dedicated Test Failover Group.

1. Configure the Test Group​

(Run this in the Primary Account, e.g., AWS Mumbai. Verify your role with SELECT CURRENT_ROLE();)

USE ROLE ACCOUNTADMIN;

-- We only include a specific "DR_TEST" database, NOT the production databases
CREATE FAILOVER GROUP FG_TEST_DRILL
OBJECT_TYPES = DATABASES, USERS, ROLES, WAREHOUSES
ALLOWED_DATABASES = BEEMS_DR_TEST_DB
ALLOWED_ACCOUNTS = orgname.azure_pune
REPLICATION_SCHEDULE = '10 MINUTE';

2. Prepare the Replica​

(Run this in the Secondary Account, e.g., Azure Pune)

USE ROLE ACCOUNTADMIN;
CREATE FAILOVER GROUP FG_TEST_DRILL AS REPLICA OF orgname.aws_mumbai.FG_TEST_DRILL;

3. Execute the Simulated Failover​

During the scheduled maintenance window, the administrator logs into the Secondary Account and promotes it.

-- Run this in Azure Pune to trigger the simulated failover
ALTER FAILOVER GROUP FG_TEST_DRILL PRIMARY;

At this moment, BEEMS_DR_TEST_DB in Azure Pune becomes writable.

4. Application Connectivity Testing​

Now, the data engineering team in Bengaluru must point their test BI tools and test dbt pipelines to the Azure Pune account URL and verify that:

  1. They can successfully authenticate.
  2. They can write data to BEEMS_DR_TEST_DB.
  3. The Virtual Warehouses spin up correctly.

5. The Rollback (Failback)​

Once the test is successful, you must revert the architecture to its normal state. Log back into AWS Mumbai and reclaim the Primary status.

-- Run this in AWS Mumbai
ALTER FAILOVER GROUP FG_TEST_DRILL PRIMARY;

Writing a DR Runbook​

A DR Runbook is a step-by-step manual that anyone (even a junior engineer awakened at 3:00 AM) can follow to execute the failover.

A high-quality runbook must include:

  1. Decision Matrix: Who has the authority to declare a disaster? (e.g., "The VP of Engineering or the CISO").
  2. Escalation Path: Phone numbers and Slack handles for the core incident response team.
  3. Execution Steps: The exact SQL commands to run, and specifically which account URL to run them in.
  4. Application Changes: A checklist of downstream systems (Tableau, Airflow, Fivetran) that must have their Snowflake connection URLs updated. (Note: If you use Snowflake Client Redirect, this step is handled by DNS).
  5. Success Criteria: How to verify the failover worked (e.g., "Run this specific SELECT count(*) FROM BEEMS_PROD_DB.CORE.TRANSACTIONS query in the new region").

Compliance and Auditing​

For organizations complying with RBI (Reserve Bank of India) guidelines, GDPR, or SOC2, you must prove that you test your DR plan.

Best Practice:

  • Schedule DR drills Quarterly.
  • Record the start time, end time, and any errors encountered.
  • Save the results in an internal audit table or a secure document repository.
-- Conceptual Audit Log (Stored in a non-replicated Admin database)
INSERT INTO BEEMS_ADMIN_DB.AUDIT.DR_DRILL_LOGS
VALUES (CURRENT_TIMESTAMP(), 'Q3 2024 Drill', 'SUCCESS', 'Failover completed in 12 minutes. dbt pipeline required manual restart.');

Summary​

  • Testing is the only way to expose missing Virtual Warehouses, hardcoded URLs, or broken pipelines in the secondary region.
  • Use a dedicated Test Failover Group to perform drills without impacting production data.
  • Always write a detailed Runbook containing the escalation path and exact SQL commands.
  • Perform tests quarterly to satisfy compliance and audit requirements.