RPO & RTO Planning
Before configuring replication or failover groups, a Snowflake Administrator must work with the business to define two critical Disaster Recovery metrics: RPO and RTO.
Without defining these targets, you are either putting the business at risk by under-replicating, or burning unnecessary credits by over-replicating.
Topics Covered
- Defining RPO (Recovery Point Objective)
- Defining RTO (Recovery Time Objective)
- Balancing Cost vs Criticality
- Implementing RPO in Snowflake
1. RPO (Recovery Point Objective)
RPO is the maximum acceptable amount of data loss during a disaster, measured in time.
If an earthquake takes out the AWS Mumbai region at exactly 12:00 PM, and your RPO is "1 Hour", it means the business accepts losing all data ingested between 11:00 AM and 12:00 PM.
How Snowflake Meets RPO
In Snowflake, RPO is dictated entirely by your Replication Schedule.
- If you set
REPLICATION_SCHEDULE = '60 MINUTE', your RPO is essentially 60 minutes. - If you set it to
'24 HOUR', your RPO is 24 hours. You will lose an entire day's worth of data if the primary region goes down.
2. RTO (Recovery Time Objective)
RTO is the maximum acceptable amount of time it takes to restore the system to full operation after a disaster strikes.
If the AWS Mumbai region goes down at 12:00 PM, and your RTO is "4 Hours", it means the business expects the BI dashboards to be functioning again (pointing to the Azure Pune region) no later than 4:00 PM.
How Snowflake Meets RTO
Snowflake's actual database failover command (ALTER FAILOVER GROUP ... PRIMARY) takes milliseconds. However, your actual RTO depends on several external factors:
- Decision Time: How long does it take the Chief Information Officer (CIO) to officially declare a disaster and authorize the failover?
- DNS Propagation: If you are using Snowflake Client Redirect, it takes time for the global DNS to update and point
myorg-myaccount.snowflakecomputing.comto the new Azure Pune IP addresses. - Application Reconnect: How long does it take Tableau, dbt, or Airflow to restart and reconnect to the new region?
Balancing Cost vs Criticality
You cannot set RPO and RTO to "Zero Seconds" without spending an astronomical amount of money. Every time Snowflake replicates data, you pay for compute (to scan the primary and write to the secondary) and egress bandwidth (to move data out of AWS Mumbai).
You must map database criticality to replication tiers.
| Tier | Example Workload | RPO Target | RTO Target | Replication Schedule |
|---|---|---|---|---|
| Tier 1 (Mission Critical) | Fraud Detection, Payment Logs | 10 Minutes | 15 Minutes | 10 MINUTE |
| Tier 2 (Important) | Daily BI Dashboards, Sales Data | 1 Hour | 4 Hours | 60 MINUTE |
| Tier 3 (Non-Critical) | Historical Archives, Sandbox DBs | 24 Hours | 2 Days | 24 HOUR |
Hands-On: Designing Replication Schedules
Let's configure different replication schedules for a large bank in India. The payment logs are Tier 1, while the HR sandbox is Tier 3.
(To configure replication, you must use the ACCOUNTADMIN role. Verify this by running SELECT CURRENT_ROLE();)
USE ROLE ACCOUNTADMIN;
-- Tier 1: Payments (Aggressive RPO)
-- High cost, but minimal data loss
CREATE FAILOVER GROUP FG_PAYMENTS_CRITICAL
OBJECT_TYPES = DATABASES
ALLOWED_DATABASES = BEEMS_PAYMENTS_DB
ALLOWED_ACCOUNTS = orgname.azure_pune
REPLICATION_SCHEDULE = '10 MINUTE';
-- Tier 3: Sandboxes (Relaxed RPO)
-- Low cost, accepts up to 24 hours of data loss
CREATE FAILOVER GROUP FG_SANDBOX_NONCRIT
OBJECT_TYPES = DATABASES
ALLOWED_DATABASES = BEEMS_HR_SANDBOX_DB
ALLOWED_ACCOUNTS = orgname.azure_pune
REPLICATION_SCHEDULE = 'USING CRON 0 2 * * * Asia/Kolkata'; -- Runs once a day at 2 AM IST
Documenting and Monitoring RPO Commitments
Once you agree to a 10-minute RPO with the business, you must prove you are hitting it.
If a 10-minute refresh takes 15 minutes to run because the database is huge, you are failing your RPO!
-- Check if any replication refreshes took longer than the 10-minute RPO target
SELECT
REPLICATION_GROUP_NAME,
START_TIME,
END_TIME,
TIMESTAMPDIFF(minute, START_TIME, END_TIME) AS REFRESH_DURATION_MINUTES
FROM table(information_schema.replication_group_refresh_history('FG_PAYMENTS_CRITICAL'))
WHERE TIMESTAMPDIFF(minute, START_TIME, END_TIME) > 10
ORDER BY START_TIME DESC;
If this query returns rows, you must either negotiate a relaxed RPO with the business or optimize the data loading patterns in the primary database.
Summary
- RPO (Recovery Point Objective) dictates how much data you can lose. It is driven by the
REPLICATION_SCHEDULE. - RTO (Recovery Time Objective) dictates how fast you recover. It is driven by DNS and Failover speed.
- Never set a global 10-minute RPO for all databases; it will drain your budget. Use Tiering.
- Monitor
replication_group_refresh_historyto ensure your refresh duration does not exceed your RPO target.