Disaster Recovery for Data Platforms: RPO/RTO, Cross-Region Replication & Backups

Disaster recovery is the discipline that decides whether a region outage, a corrupt overnight load, a ransomware event, or a fat-fingered DROP TABLE costs you an hour and a shrug — or your job. The uncomfortable truth is that "the data is in the cloud" is not a recovery plan: managed warehouses and object stores are durable, but durability protects against a disk dying, not against a whole region going dark, a pipeline writing garbage over yesterday's facts, or an attacker encrypting your lake. A data platform without a rehearsed recovery posture is one bad deploy away from an outage measured in days, and the difference between the teams that recover in minutes and the teams that recover in a support ticket is almost never the tooling — it is whether anyone measured, replicated, and tested before the incident.

This guide is the senior-data-engineering walkthrough for building that posture, framed the way interviewers actually probe it: the two numbers that define every recovery conversation — RPO (how much data you can afford to lose) and RTO (how long you can afford to be down); the DR-tier ladder from cheap backups and restore up through pilot light, warm standby, and multi-region active-active; how cross-region replication keeps a warm copy in another region and why replication lag is your effective RPO; how backups, snapshots, warehouse Time Travel, and immutable object storage give you a point-in-time undo; how a failover runbook promotes a standby, cuts over DNS and endpoints, and reconciles the gap; and how to apply RPO/RTO to tier each dataset so business continuity costs what it should and no more. Each section pairs a teaching block with a Solution-Tail interview answer — code, a step-by-step trace, an output table, then a concept-by-concept breakdown of why it works.

When you want hands-on reps immediately after reading, drill the system design practice library →, rehearse recovery-grade pipelines on the data pipeline practice library →, and harden your load paths on the ETL practice library →.

---

On this page

• Why disaster recovery matters for data platforms
• Backups — Time Travel, versioning, and immutability
• Cross-region replication — Snowflake, S3 CRR, and lag
• Failover and runbooks — promotion, cutover, and reconciliation
• Applying RPO/RTO — pick a DR tier per dataset
• Cheat sheet — disaster recovery for data platforms
• Frequently asked questions
• Practice on PipeCode

---

1. Why disaster recovery matters for data platforms

The two numbers first — RPO is data you can lose, RTO is downtime you can tolerate

The one-sentence invariant: disaster recovery for a data platform is the practice of guaranteeing that after a destructive event — a region failure, a corrupt load, a deletion, an attack — you can restore service within a bounded downtime (RTO) having lost at most a bounded amount of data (RPO), and the entire engineering job is to (1) measure those two numbers per dataset, (2) buy exactly the DR tier — backup/restore, pilot light, warm standby, or active-active — whose cost matches the business impact, and (3) test the recovery on a schedule, because an unrehearsed plan is a hypothesis, not a capability. Durability is table stakes; recoverability is the actual product, and it is bought with replication, backups, and rehearsed failover — not with a checkbox.

The failures DR actually protects against — none of which durability covers.

• Region-level outage. An entire cloud region degrades or goes dark. Your triple-replicated data inside that region is perfectly durable and completely unreachable — availability, not durability, is the problem, and only a copy in another region fixes it.
• Logical corruption. A pipeline bug overwrites fct_orders with half the rows, or a bad dbt model doubles every revenue figure. The corruption is faithfully, durably replicated everywhere in milliseconds — you need a point-in-time undo, not more copies of the bad data.
• Accidental or malicious deletion. A DROP TABLE, a wildcard DELETE, a mis-scoped lifecycle rule, or ransomware encrypting the lake. Recovery here needs immutable, retained history that a compromised credential cannot destroy.
• Dependency failure. An upstream source, an identity provider, or a key-management service fails and cascades. DR is not only "restore the data" — it is "restore the ability to serve," which includes roles, warehouses, and integrations.

The two numbers, defined precisely.

• RPO — Recovery Point Objective. The maximum acceptable amount of data loss, expressed in time: an RPO of 15 minutes means that after any disaster you must be able to recover to a state no more than 15 minutes before it. RPO is set by how often you capture a recoverable point — backup cadence or replication lag.
• RTO — Recovery Time Objective. The maximum acceptable downtime: an RTO of 30 minutes means service must be restored within 30 minutes of the incident. RTO is set by how fast you can promote, cut over, and reconcile — the speed of the recovery mechanism.
• They are independent. A nightly backup can give a tiny RTO (restore is fast) but a 24-hour RPO (you lose a day), while a slow-to-promote replica can give a tiny RPO (near-zero loss) but a large RTO. You size each separately, per dataset.
• They cost money in opposite directions. Shrinking RPO costs capture frequency (more replication, more snapshots); shrinking RTO costs standby readiness (warm or hot infrastructure waiting). The cheapest posture that meets both is the goal.

The DR-tier ladder — cost climbs as RPO and RTO shrink.

• Backup and restore (cold). Periodic backups/snapshots shipped to another region; recover by restoring them. RPO = backup interval (hours), RTO = restore time (hours). Cheapest; correct for cold, reproducible, or low-impact data.
• Pilot light. The core data is continuously replicated to a dormant DR environment with the minimal always-on footprint; compute is scaled up only on failover. RPO = replication lag (minutes), RTO ≈ time to start compute (tens of minutes).
• Warm standby. A scaled-down but fully functional copy runs continuously in the DR region and is scaled up on failover. RPO = minutes, RTO = minutes.
• Multi-region active-active (hot). Full capacity runs in two or more regions simultaneously; a region loss is absorbed with near-zero RPO and RTO. Most expensive by far; reserved for the datasets whose downtime is existential.

What interviewers listen for.

• Do you state RPO and RTO as numbers before proposing any architecture — and size them per dataset, not one blanket number? — senior signal.
• Do you distinguish durability from recoverability, and name logical corruption and deletion as threats replication alone does not fix? — required answer.
• Do you place a dataset on the tier ladder by business impact and cost, rather than reaching for active-active reflexively? — senior signal.
• Do you insist that an untested backup or unrehearsed failover does not count, and describe a game-day cadence? — required answer.

Worked example — compute RPO/RTO for a pipeline and pick a DR tier

Detailed explanation. The single most useful thing you can do in a DR interview is take a concrete pipeline, read its current RPO and RTO off the architecture, compare them to the required objectives, and then pick the cheapest tier that closes the gap. Walk it for an hourly orders pipeline landing in a Snowflake warehouse.

• The pipeline. Source events → hourly batch load → analytics.fct_orders → dashboards and a billing reconciliation job.
• Current posture. One region; nightly backup only; no cross-region copy.
• The required objectives. Business says: lose at most 1 hour of orders (RPO ≤ 1h), be back within 1 hour (RTO ≤ 1h).

Question. Given the current posture, what RPO and RTO does the pipeline actually have today, and which DR tier is the cheapest that meets RPO ≤ 1h and RTO ≤ 1h?

Input.

Factor	Current value	Required objective
Recovery points	nightly backup (24h apart)	RPO ≤ 1h
Recovery mechanism	restore backup + rerun loads	RTO ≤ 1h
Cross-region copy	none	needed (region outage)
Effective RPO today	up to 24h	must shrink to 1h
Effective RTO today	many hours	must shrink to 1h

Code.

Step 1 — read the CURRENT numbers off the architecture.
  Recovery points exist only nightly  -> worst-case data loss = 24h  -> RPO_now = 24h
  Recovery = restore last night's backup, then replay a day of loads
            + stand up compute in a new region by hand  -> RTO_now = many hours
  A region outage today = unbounded downtime (nothing in a second region).

Step 2 — compare to the objective.
  Required: RPO <= 1h, RTO <= 1h.  Gap: RPO off by 23h, RTO off by hours.

Step 3 — walk the tier ladder to the CHEAPEST tier that closes the gap.
  backup/restore (cold)  : RPO=24h, RTO=hours          -> FAILS both.
  pilot light            : RPO=~replication lag (min),
                           RTO=~time to start compute   -> MEETS both, cheapest that does.
  warm standby           : RPO=min, RTO=min             -> meets, but over-buys here.
  active-active          : RPO~0,  RTO~0                -> massively over-buys.

Step 4 — the pick: PILOT LIGHT.
  Continuously replicate fct_orders (and roles/warehouses) to a second region;
  keep DR compute OFF until needed; size Time Travel to cover the corruption case.
  Effective RPO = replication lag (target <= 15m, well under the 1h objective).
  Effective RTO = start DR compute + cut over + reconcile (target ~30m, under 1h).

Step-by-step explanation.

1. You first read the current RPO off the recovery-point cadence: with only a nightly backup, the worst case is an incident one minute before the next backup, so up to 24 hours of orders are unrecoverable — RPO_now = 24h. That single number already fails the objective.
2. The current RTO is the time to restore that backup, replay a day of loads, and stand up compute in a new region by hand — hours at best, and for a full region outage, effectively unbounded because nothing exists in a second region.
3. You then walk the ladder from cheapest to most expensive and stop at the first tier that meets both objectives. Backup/restore fails; pilot light — continuous replication with dormant compute — meets both while keeping DR compute (the expensive part) switched off between disasters.
4. Warm standby and active-active would also meet the objectives, but they pay for always-on DR compute this pipeline does not need, so choosing them is over-buying — the senior mistake is reaching for the most robust tier instead of the cheapest sufficient one.
5. The final pick is stated as numbers: replicate to a second region for an RPO of replication lag (target ≤ 15 min), keep compute dormant, and on failover start it, cut over, and reconcile for an RTO target of ~30 min — both comfortably inside the 1-hour objectives.

Output.

Tier	RPO	RTO	Cost	Verdict for RPO≤1h, RTO≤1h
Backup/restore (today)	24h	hours	$	fails both
Pilot light	~15m (lag)	~30m	$$	meets — cheapest that does
Warm standby	minutes	minutes	$$$	meets, over-buys
Active-active	~0	~0	$$$$	meets, massively over-buys

Rule of thumb. Read the current RPO off your recovery-point cadence and the current RTO off your recovery mechanism, compare both to the required objectives, then climb the tier ladder and stop at the first tier that meets both. The cheapest sufficient tier — not the most robust one — is the correct answer.

Worked example — the 5-minute DR interview monologue

Detailed explanation. The senior DR question escalates predictably: an ambiguous opener ("how would you make this recoverable?"), then narrowing into the region case, the corruption case, and the "prove it works" case. Candidates who volunteer RPO/RTO numbers, distinguish durability from recoverability, and insist on testing score highest. Rehearse a monologue that pre-empts every follow-up.

• Ambiguous opener. "The warehouse is our whole business. Is it safe?"
• Follow-up 1. "The region goes down. Now what?" — probes replication/failover.
• Follow-up 2. "A pipeline corrupted a table. Replication saved it… to the DR region too?" — probes point-in-time recovery.
• Follow-up 3. "How do you know any of this works?" — probes testing.

Question. Draft a 5-minute senior DR answer that pre-empts the region, corruption, and testing follow-ups without waiting to be asked.

Input.

Interview signal	Weak answer	Senior answer
Safety	"it's replicated, so it's safe"	"durable ≠ recoverable; here are my RPO/RTO"
Region outage	"the cloud handles it"	"cross-region replica + a promotion runbook"
Corruption	"restore from backup"	"Time Travel / versioned point-in-time undo"
Proof	"we have backups"	"quarterly game day measures actual RPO/RTO"
Scope	"back up everything the same"	"tier per dataset by impact and cost"

Code.

Senior disaster-recovery answer template (5 minutes)
====================================================

Minute 1 — measure before you architect
  "First I set RPO and RTO per dataset. Billing needs RPO<=5m, RTO<=15m;
   raw clickstream can tolerate RPO=6h because it's replayable. Durable
   isn't recoverable — I design to the numbers, not to a checkbox."

Minute 2 — region outage
  "For a region loss I keep a cross-region replica: a Snowflake failover
   group and S3 CRR ship a warm copy to a second region. On failover I
   promote the secondary and cut over DNS/endpoints. RPO = replication lag."

Minute 3 — logical corruption / deletion
  "Replication faithfully copies bad data, so it is NOT my corruption
   defence. Time Travel + Fail-safe give a point-in-time undo in the
   warehouse; S3 versioning + Object Lock give an immutable undo in the lake."

Minute 4 — proof by testing
  "None of this counts until it's tested. A quarterly game day promotes the
   secondary, cuts over, reconciles, and MEASURES the actual RPO/RTO against
   the objective. An unrehearsed plan is a hypothesis."

Minute 5 — cost discipline
  "I tier per dataset: active-active only for the existential data, pilot
   light or warm standby for the important, backup/restore for the rest.
   DR cost should match business impact — that's business continuity, not gold-plating."

Step-by-step explanation.

1. Minute 1 establishes the senior frame: measure first. Naming concrete RPO/RTO numbers per dataset — and stating "durable is not recoverable" — signals you understand the problem, not just the buzzwords.
2. Minute 2 pre-empts the region follow-up by naming the mechanism (cross-region replication) and the action (promote + cut over) and the resulting RPO (replication lag) — the full loop, not just "it's replicated."
3. Minute 3 is the differentiator: volunteering that replication copies corruption to the DR region too, and that the corruption defence is a point-in-time undo (Time Travel, versioning), separates candidates who have run a real recovery from those who have read a slide.
4. Minute 4 pre-empts the "prove it" follow-up before it is asked, which is the single most senior move — testing is the step every weak answer omits, and a measured game day is the evidence an interviewer is fishing for.
5. Minute 5 closes on cost discipline and the business-continuity framing: tiering per dataset shows you treat DR as an economic decision, not a maximise-robustness reflex — the sentence that separates an engineer from an architect.

Output.

Grading criterion	Weak score	Senior score
States RPO/RTO as numbers	rare	mandatory
Durability vs recoverability	rare	senior signal
Corruption ≠ replication	occasional	senior signal
Insists on tested failover	rare	mandatory
Tiers DR by cost/impact	rare	senior signal

Rule of thumb. The senior DR answer is a 5-minute monologue: measure RPO/RTO per dataset, cover the region case (replicate + promote), the corruption case (point-in-time undo), the proof (a measured game day), and the cost discipline (tier per dataset). Rehearse it once; deploy it every interview.

Worked example — the cost-vs-recovery decision table

Detailed explanation. Every DR tier trades money for a smaller RPO and RTO, and the senior skill is reading what you actually buy at each rung so you can defend the cheapest sufficient choice. Build the decision table that maps a dataset's business impact to a tier.

• The lever. Lower RPO buys more frequent capture (replication/snapshots); lower RTO buys more standby readiness (warm/hot compute).
• The anti-pattern. One blanket tier for everything — either under-protecting the critical data or gold-plating the cheap data.
• The rule. Tier by blast radius: what does an hour of downtime or an hour of lost data actually cost this dataset?

Question. For four datasets of different business impact, pick the DR tier and justify it by RPO/RTO and cost.

Input.

Dataset	Downtime cost	Loss tolerance	Candidate tier
Billing ledger	severe (revenue, legal)	~0	active-active / warm standby
Ops dashboards	high (blind ops)	minutes	warm standby / pilot light
Curated marts	moderate (rebuildable)	~1h	pilot light
Raw clickstream	low (replayable source)	hours	backup/restore

Code.

Cost climbs as RPO and RTO shrink — buy the cheapest tier that meets the impact.

Billing ledger      impact: SEVERE (money + compliance)
  -> RPO ~0, RTO minutes.  WARM STANDBY (or active-active if global).
     Pay for a live replica; a lost transaction is unacceptable.

Ops dashboards      impact: HIGH (you're flying blind during an incident)
  -> RPO minutes, RTO minutes.  WARM STANDBY / PILOT LIGHT.
     Rebuildable from marts, but you need them back fast DURING a disaster.

Curated marts       impact: MODERATE (rebuildable from raw in hours)
  -> RPO ~1h, RTO ~1h.  PILOT LIGHT.
     Replicate the definitions + a recent copy; recompute the rest.

Raw clickstream     impact: LOW (immutable, replayable from source/Kafka)
  -> RPO hours, RTO hours.  BACKUP/RESTORE (cross-region snapshot).
     Cheapest tier; the source IS the backup.

Anti-pattern: one blanket tier -> either you gold-plate clickstream or
             you under-protect the billing ledger. Tier by blast radius.

Step-by-step explanation.

1. The billing ledger's downtime cost is severe and its loss tolerance is essentially zero, so it earns the top of the ladder — a warm (or active-active) standby whose live replica means a failover loses no committed transactions.
2. Ops dashboards are rebuildable from marts, so their RPO can be loose, but during an incident you are blind without them, so their RTO must be tight — this splits RPO from RTO and lands them on warm standby or pilot light.
3. Curated marts are recomputable from raw data in hours, so you replicate their definitions and a recent copy (pilot light) and recompute the rest on failover rather than paying to keep them hot.
4. Raw clickstream is immutable and replayable from its durable source, so the source itself functions as the backup — a cheap cross-region snapshot is enough, and paying more would be pure gold-plating.
5. The blanket-tier anti-pattern is the failure the table prevents: a single policy either over-spends on the replayable clickstream or under-protects the billing ledger, whereas tiering by blast radius spends each dollar where downtime actually hurts.

Output.

Dataset	Tier	RPO / RTO	Why
Billing ledger	warm standby / active-active	~0 / minutes	money + compliance, zero loss
Ops dashboards	warm standby / pilot light	minutes / minutes	rebuildable but needed live
Curated marts	pilot light	~1h / ~1h	recomputable from raw
Raw clickstream	backup/restore	hours / hours	source is replayable

Rule of thumb. Tier each dataset by blast radius — what an hour of downtime or lost data actually costs it — and buy the cheapest tier that meets that impact. Split RPO from RTO (rebuildable-but-urgent data wants loose RPO, tight RTO), and never apply one blanket tier across a platform.

Senior interview question on disaster recovery strategy

A senior interviewer often opens with: "Your company runs its entire analytics and billing on a single-region Snowflake warehouse plus an S3 data lake, with only nightly backups. Leadership asks for a disaster recovery plan. Walk me through how you'd set RPO and RTO, which datasets get which DR tier, what mechanisms you'd put in place for a region outage versus a data-corruption event, and how you'd prove the plan actually works — and justify the cost."

Solution Using measured objectives, per-dataset tiering, layered mechanisms, and tested failover

-- Step 1 — MEASURE: set RPO/RTO per dataset before choosing any mechanism.
billing_ledger : RPO <= 5m,  RTO <= 15m   (severe: revenue + compliance)
ops_dashboards : RPO <= 15m, RTO <= 15m   (high: blind ops during an incident)
curated_marts  : RPO <= 1h,  RTO <= 1h    (moderate: recomputable from raw)
raw_events     : RPO <= 6h,  RTO <= 6h    (low: immutable, replayable source)

-- Step 2 — MECHANISM per threat. (a) Region outage: cross-region replication.
CREATE FAILOVER GROUP dr_fg
  OBJECT_TYPES = DATABASES, ROLES, WAREHOUSES, INTEGRATIONS
  ALLOWED_DATABASES = billing, analytics
  ALLOWED_ACCOUNTS = myorg.us_west_2
  REPLICATION_SCHEDULE = '5 MINUTE';        -- warehouse RPO ~ 5m (meets billing)

-- (b) Logical corruption/deletion: point-in-time undo — NOT replication.
ALTER TABLE billing.ledger  SET DATA_RETENTION_TIME_IN_DAYS = 30;   -- Time Travel
ALTER TABLE analytics.marts SET DATA_RETENTION_TIME_IN_DAYS = 7;

# Step 3 — lake: cross-region replication for the region case, immutability for the
#          deletion/ransomware case, tested restore for proof.
aws s3api put-bucket-versioning --bucket lake-raw --versioning-configuration Status=Enabled
aws s3api put-object-lock-configuration --bucket lake-raw \
  --object-lock-configuration '{"ObjectLockEnabled":"Enabled",
    "Rule":{"DefaultRetention":{"Mode":"COMPLIANCE","Days":35}}}'   # immutable undo
# CRR (config in section 3) ships versions to lake-raw-dr in us-west-2.

# Step 4 — PROVE it: a scheduled game day that MEASURES actual RPO/RTO, and tiering.
dr_program:
  tiers:
    billing_ledger:  { tier: warm-standby, rpo: 5m,  rto: 15m }
    ops_dashboards:  { tier: warm-standby, rpo: 15m, rto: 15m }
    curated_marts:   { tier: pilot-light,  rpo: 1h,  rto: 1h  }
    raw_events:      { tier: backup-restore, rpo: 6h, rto: 6h }
  game_day: { cadence: quarterly, steps: [promote, cutover, reconcile, measure], on_miss: revise }
  ownership: { runbook_owner: data-platform, exec_sponsor: cto }

Step-by-step trace.

Decision	Before (single region, nightly backup)	After (DR program)
RPO	up to 24h, undefined per dataset	5m–6h, measured per dataset
Region outage	unbounded downtime	promote replica, cut over
Corruption	restore whole backup (slow, lossy)	Time Travel / version undo
Deletion / ransomware	possibly permanent	immutable Object Lock undo
Proof	"we have backups"	measured quarterly game day
Cost	one-size, mis-matched	tiered to business impact

After the rollout, each dataset has an explicit RPO/RTO; a Snowflake failover group and S3 Cross-Region Replication keep a warm copy in us-west-2 for the region case; Time Travel, Fail-safe, S3 versioning, and Object Lock provide a point-in-time and immutable undo for the corruption and deletion cases; and a quarterly game day promotes the secondary and measures the RPO and RTO actually achieved. DR spend is tiered — warm standby for billing, backup/restore for replayable raw — so business continuity costs match business impact.

Output:

Metric	Before	After
Worst-case data loss (RPO)	up to 24h	5m (billing) – 6h (raw)
Region-outage downtime (RTO)	unbounded	≤ 15m–6h by tier
Corruption recovery	full restore, hours	point-in-time undo, minutes
Deletion / ransomware	possibly permanent	immutable, recoverable
Recovery confidence	untested hope	measured every quarter
DR cost	flat, mismatched	tiered to impact

Why this works — concept by concept:

• Measure before you architect — setting RPO and RTO as numbers per dataset turns DR from a vague aspiration into a spec you can design and test against, and reveals that different datasets need wildly different (and differently priced) protection.
• Layered mechanisms per threat — cross-region replication answers the region-outage threat, while Time Travel/versioning and Object Lock answer the corruption and deletion threats that replication faithfully copies rather than fixes; each threat gets the mechanism that actually addresses it.
• Per-dataset tiering — placing each dataset on the ladder by blast radius (warm standby for billing, pilot light for marts, backup/restore for replayable raw) spends money where downtime hurts and nowhere else, which is what business continuity means.
• Tested failover — a quarterly game day that promotes, cuts over, reconciles, and measures converts an untested plan into a demonstrated capability, and surfaces the runbook gaps that only appear under a real promotion.
• Cost — a handful of replication refreshes, retained versions, and one drill per quarter, versus the multi-day outage and permanent data loss a single-region, untested platform risks. The eliminated cost is the existential one — O(minutes) measured recovery instead of O(days) of undefined, unrehearsed scramble.

Design
Topic — design
Design problems on disaster recovery and DR tiers

Practice →

Data validation Topic — data-validation Data validation problems on recovery-point integrity

Practice →

---

2. Backups — Time Travel, versioning, and immutability

Rewind the warehouse, version the lake, lock it immutable — then prove the restore works

The mental model in one line: backups for a data platform are a point-in-time undo against corruption and deletion — the threats replication cannot fix — and they come in three layers: warehouse-native time travel (snapshots of recent history you can query and restore, plus a provider-only Fail-safe net), object-store versioning (every overwrite/delete keeps the prior version), and immutability (write-once Object Lock so a compromised credential or a lifecycle mistake cannot destroy history) — and none of it counts until a scheduled restore drill proves you can actually recover, because an untested backup is only a belief. The backup that matters is the one you have restored from in a rehearsal, not the one sitting in a bucket.

Warehouse-native recovery — Time Travel and Fail-safe.

• Time Travel is queryable history. Snowflake retains changed data for DATA_RETENTION_TIME_IN_DAYS (0–90 on Enterprise), letting you SELECT ... AT (TIMESTAMP => ...), clone a table as of a past point, or UNDROP a dropped table — a self-service undo for the recent past.
• Zero-copy clone is the restore primitive. CREATE TABLE ... CLONE ... AT (...) produces an instant, storage-cheap copy of a past state because it shares underlying micro-partitions — so "restore to 15 minutes ago" is seconds, not a bulk copy.
• Fail-safe is the provider net. After Time Travel expires, Snowflake keeps a further 7 days (non-configurable) of data recoverable only by the provider via a support request — a last resort, not a self-service tier, and not something to design your RPO around.
• BigQuery/Redshift analogues. BigQuery time travel (default 7 days) plus table snapshots; Redshift automated + manual snapshots with cross-region copy. The pattern is universal: recent-history undo plus explicit snapshots.

Object-store versioning — the lake's undo log.

• Versioning keeps every version. With S3 Versioning enabled, an overwrite creates a new version and the old one is retained; you recover by reading or restoring the prior versionId.
• A delete is a marker, not a destruction. A DELETE on a versioned object writes a delete marker that hides the object; removing the delete marker restores the prior version — accidental deletes are reversible.
• Noncurrent-version lifecycle bounds cost. Retained versions cost storage forever unless a lifecycle rule expires noncurrent versions after N days — the knob that keeps versioning affordable while preserving a recovery window.
• Table formats add their own time travel. Iceberg/Delta/Hudi keep snapshot history and support VERSION AS OF / time-travel reads and rollback, giving transactional point-in-time recovery on top of object versioning.

Immutability — the ransomware and fat-finger guard.

• Object Lock is write-once-read-many. S3 Object Lock prevents a version from being overwritten or deleted before a retain-until date — so even a stolen admin credential cannot destroy locked history.
• Governance vs compliance mode. Governance mode lets privileged users override the lock; compliance mode lets no one (not even the account root) delete before expiry — the mode you use when the threat model includes insiders or ransomware.
• Immutability is what makes a backup trustworthy. A backup an attacker can encrypt or delete is not a backup; immutable retention is the difference between "we have copies" and "we have recoverable copies."
• Air-gap by account/region. Replicate immutable backups to a separate account and region so a single compromised control plane cannot reach them — defence in depth for the deletion threat.

Backup testing, retention, and the failure modes senior engineers pre-empt.

• An untested backup is not a backup. Schedule a restore drill (weekly/monthly) that restores into an isolated environment and verifies row counts and checksums against a baseline — the only proof that RPO/RTO are real.
• Retention must match the objective. Time Travel days, version-expiry days, and Object Lock retain-until must each be ≥ the recovery window you promise; a 1-day Time Travel window cannot deliver a "restore to last week" RPO.
• Backup on the same blast radius = no backup. A backup in the same account/region as the primary shares its failure and its attacker; cross-account, cross-region, immutable copies are the ones that survive.
• Silent backup failure. A backup job that has been failing for a month is discovered during the recovery. Mitigation: alert on backup success/freshness, and let the restore drill be the real monitor.

Common interview probes on backups.

• "Does replication protect against a corrupt load?" — no; replication copies the corruption. You need Time Travel/versioning point-in-time undo.
• "What is Fail-safe and can I design my RPO around it?" — a 7-day provider-only last resort; no, it is not self-service.
• "How do you defend against ransomware deleting your lake?" — immutable Object Lock (compliance mode), cross-account/region, air-gapped.
• "How do you know your backups work?" — a scheduled restore drill that verifies counts/checksums; an untested backup is a hypothesis.

Worked example — Time Travel query, clone, and UNDROP

Detailed explanation. A bad load overwrote fct_orders this morning and someone else dropped a staging table. Time Travel recovers both without a backup restore: query the table as of before the load, clone that state back, and UNDROP the dropped table. Size the retention window to the RPO first.

• The window. DATA_RETENTION_TIME_IN_DAYS must cover the RPO you promise.
• The undo. AT (TIMESTAMP => ...) reads the past; CLONE ... AT (...) restores it.
• The rescue. UNDROP TABLE reverses a drop within the window.

Question. Recover fct_orders to its state at 09:00 (before a 09:15 bad load) and restore a table that was accidentally dropped, using Time Travel only.

Input.

Event	Time	Recovery
Retention set	—	DATA_RETENTION_TIME_IN_DAYS = 30
Bad load overwrote rows	09:15	query/clone AT (TIMESTAMP => 09:00)
Staging table dropped	09:40	UNDROP TABLE
Window covers it	within 30 days	yes

Code.

-- 0. Size the Time Travel window to the RPO you promise (Enterprise: up to 90 days).
ALTER TABLE analytics.fct_orders SET DATA_RETENTION_TIME_IN_DAYS = 30;

-- 1. Inspect the past: how many rows did fct_orders have BEFORE the 09:15 bad load?
SELECT count(*) AS rows_before_bad_load
FROM analytics.fct_orders AT (TIMESTAMP => '2026-08-26 09:00:00'::timestamp_tz);

-- 2. Restore that state with a zero-copy clone (instant, storage-cheap), then swap.
CREATE OR REPLACE TABLE analytics.fct_orders_recovered
  CLONE analytics.fct_orders AT (TIMESTAMP => '2026-08-26 09:00:00'::timestamp_tz);
ALTER TABLE analytics.fct_orders SWAP WITH analytics.fct_orders_recovered;

-- 3. Someone dropped a staging table at 09:40 — reverse it within the window.
UNDROP TABLE staging.orders_incoming;

Step-by-step explanation.

1. Setting DATA_RETENTION_TIME_IN_DAYS = 30 is the precondition: Time Travel can only reach as far back as the retention window, so the window must be ≥ the RPO you promise. A default 1-day window cannot recover a corruption discovered two days later.
2. The AT (TIMESTAMP => '09:00') clause reads the table exactly as it existed before the 09:15 load — a query against history that lets you confirm the pre-incident row count before you touch anything.
3. CLONE ... AT (...) materialises that past state as a new table instantly and cheaply (it shares micro-partitions rather than copying bytes), and SWAP WITH atomically replaces the corrupted table with the recovered clone — the restore is a metadata operation, not a bulk copy.
4. UNDROP TABLE reverses the accidental drop because Snowflake keeps the dropped table's data for the retention window — a self-service rescue with no backup restore and no support ticket.
5. The whole recovery happened inside the warehouse in seconds, with no external backup, because Time Travel is a queryable, cloneable history — which is exactly why the retention window is a DR decision, not a default to leave at 1 day.

Output.

Action	Mechanism	Result
Read pre-load state	AT (TIMESTAMP => 09:00)	correct row count confirmed
Restore the table	CLONE ... AT + SWAP	corruption undone in seconds
Recover dropped table	UNDROP TABLE	table back, no restore job
Cost of the restore	zero-copy clone	metadata, not a bulk copy

Rule of thumb. Size DATA_RETENTION_TIME_IN_DAYS to the RPO you promise, then recover corruption with AT (TIMESTAMP => ...) + zero-copy CLONE + SWAP, and reverse drops with UNDROP. Time Travel is a self-service, in-warehouse undo — but only as far back as the retention window you set in advance.

Worked example — S3 versioning plus immutable Object Lock

Detailed explanation. The lake's undo is object versioning; its ransomware guard is Object Lock. Enable both so an overwrite keeps the old version, a delete is reversible, and no credential — stolen or malicious — can destroy locked history before its retain-until date. Recover an object that was overwritten and one that was "deleted."

• Versioning. Every overwrite retains the prior versionId.
• Delete marker. A delete hides but does not destroy; remove the marker to restore.
• Object Lock. Compliance mode blocks deletion before retain-until, even for root.

Question. Configure versioning and immutable Object Lock, then recover an overwritten object and an accidentally deleted one.

Input.

Piece	Value
Bucket	lake-raw (versioning + Object Lock)
Lock mode	COMPLIANCE, 35 days
Overwrite recovery	read prior versionId
Delete recovery	remove the delete marker

Code.

# 1. Enable versioning: overwrites and deletes now retain prior versions.
aws s3api put-bucket-versioning --bucket lake-raw \
  --versioning-configuration Status=Enabled

# 2. Enable immutable Object Lock in COMPLIANCE mode — no one (not even root)
#    can delete or overwrite a version before its 35-day retain-until date.
aws s3api put-object-lock-configuration --bucket lake-raw \
  --object-lock-configuration '{"ObjectLockEnabled":"Enabled",
    "Rule":{"DefaultRetention":{"Mode":"COMPLIANCE","Days":35}}}'

# 3. A "delete" writes a delete marker; the real version is still there.
aws s3api delete-object --bucket lake-raw --key events/2026-08-26/part-000.parquet
# List versions to find the delete marker and the prior good version:
aws s3api list-object-versions --bucket lake-raw --prefix events/2026-08-26/part-000.parquet

# 4. Recover by removing the delete marker (the prior version becomes current again):
aws s3api delete-object --bucket lake-raw --key events/2026-08-26/part-000.parquet \
  --version-id <delete-marker-version-id>

# 5. Bound cost: expire NONCURRENT versions after the recovery window.
aws s3api put-bucket-lifecycle-configuration --bucket lake-raw --lifecycle-configuration '{
  "Rules":[{"ID":"expire-old-versions","Status":"Enabled","Filter":{},
    "NoncurrentVersionExpiration":{"NoncurrentDays":90}}]}'

Step-by-step explanation.

1. Enabling versioning turns the bucket into an append-only history: every overwrite creates a new version and preserves the old one, so a bad pipeline that overwrites part-000.parquet has not destroyed the good data — it is one version back.
2. Object Lock in compliance mode makes every version immutable until its retain-until date, so even a stolen root credential or ransomware process cannot delete or encrypt-in-place the locked history — the guarantee that makes the backup trustworthy against an active attacker.
3. A delete-object on a versioned bucket writes a delete marker rather than removing bytes; the object appears gone to normal reads, but list-object-versions reveals both the marker and the intact prior version.
4. Removing the delete marker (by deleting that specific version id) makes the prior version current again — the accidental delete is fully reversed, with no restore job, because nothing was ever destroyed.
5. Retained versions would accumulate cost forever, so the lifecycle rule expires noncurrent versions after 90 days — long enough to cover the recovery window, short enough to bound storage — which is how versioning stays affordable at lake scale.

Output.

Scenario	Without versioning/lock	With versioning + Object Lock
Overwrite by bad load	old data gone	prior versionId recoverable
Accidental delete	object gone	remove delete marker → restored
Ransomware / rogue delete	destroys data	blocked until retain-until (compliance)
Version storage cost	n/a	bounded by lifecycle expiry

Rule of thumb. Enable S3 Versioning for a reversible undo, add Object Lock in compliance mode so no credential can destroy history before retain-until, and expire noncurrent versions on a lifecycle rule to bound cost. Versioning reverses mistakes; immutability defeats attackers; lifecycle keeps both affordable.

Worked example — a scripted restore-verification drill

Detailed explanation. The backup you never restored is a hypothesis. A restore drill restores into an isolated environment and asserts the recovered data matches a known baseline — row counts and checksums — failing loudly if it drifts. Automate it so recoverability is monitored, not assumed.

• Isolation. Restore into a throwaway schema; never touch prod.
• Verification. Assert row count and a partition checksum against a recorded baseline.
• Gating. Fail the drill (and page) on any mismatch — a silent backup failure surfaces here.

Question. Write a scheduled drill that restores a table to a point in time and verifies it against a baseline, failing on drift.

Input.

Step	Action	Assertion
Restore	clone AT (TIMESTAMP) into dr_drill	isolated, non-prod
Verify rows	count(*) vs baseline	must match
Verify content	hash_agg of a partition	must match
On mismatch	exit non-zero, page	drill fails

Code.

#!/usr/bin/env bash
# restore_drill.sh — an untested backup is not a backup. Runs WEEKLY in CI.
set -euo pipefail
RESTORE_POINT="2026-08-26 09:00:00"

# 1. Restore into an ISOLATED schema (never touch prod) via a zero-copy clone.
snowsql -q "CREATE OR REPLACE TABLE dr_drill.fct_orders
            CLONE analytics.fct_orders
            AT (TIMESTAMP => '${RESTORE_POINT}'::timestamp_tz);"

# 2. Verify: recovered row count and a partition checksum must match the baseline.
ROWS=$(snowsql -o output_format=csv -o header=false -o timing=false \
       -q "SELECT count(*) FROM dr_drill.fct_orders;")
CHK=$(snowsql -o output_format=csv -o header=false -o timing=false \
      -q "SELECT hash_agg(*) FROM dr_drill.fct_orders WHERE order_date='2026-08-25';")

# 3. Assert against the recorded baseline; FAIL the drill (and page) on any drift.
[[ "$ROWS" == "$(cat baselines/rows.txt)" ]] || { echo "RESTORE FAILED: row-count drift"; exit 1; }
[[ "$CHK"  == "$(cat baselines/chk.txt)"  ]] || { echo "RESTORE FAILED: checksum drift"; exit 1; }
echo "restore drill OK: ${ROWS} rows, checksum verified at ${RESTORE_POINT}"

Step-by-step explanation.

1. The drill restores into a dedicated dr_drill schema via a zero-copy clone, so it exercises the real recovery path (Time Travel restore) without any risk to production data — the restore is real, the blast radius is zero.
2. It then reads two independent signals from the recovered table: the row count (catches truncation or partial restore) and a hash_agg checksum of a known partition (catches silent content corruption that a row count would miss).
3. Both signals are compared against a recorded baseline committed alongside the script, so the drill has an objective pass/fail criterion rather than a human eyeballing output — recoverability becomes a test, not a judgement.
4. Any mismatch exits non-zero, which fails the CI job and pages the owner; a backup process that silently broke a month ago is caught here, in a drill, instead of during a real disaster when it is too late.
5. Running weekly in CI makes the restore drill the true backup monitor: the only evidence that RPO/RTO are achievable is a recent, green, verified restore — which is why "we have backups" is never an acceptable answer and "our last restore drill passed on Tuesday" is.

Output.

Drill outcome	Signal	Consequence
Rows + checksum match	green	recoverability proven for the window
Row-count drift	non-zero exit	drill fails, owner paged
Checksum drift	non-zero exit	silent corruption caught
Backup silently broken	restore errors	discovered in a drill, not a disaster

Rule of thumb. Schedule a restore drill that clones a point-in-time state into an isolated schema and asserts row counts and checksums against a recorded baseline, failing loudly on drift. The passing drill — not the backup sitting in a bucket — is your proof that RPO and RTO are real.

Senior interview question on backups and point-in-time recovery

A senior interviewer might ask: "A dbt run last night overwrote your core fact table with a broken model, and a week ago someone reported a mysterious deletion in the data lake. Design the backup and recovery posture that would have made both a non-event: what warehouse-native recovery you'd rely on, how you'd protect the lake against overwrite, deletion, and ransomware, how you'd bound the cost, and how you'd prove the whole thing works before you actually need it."

Solution Using Time Travel, Fail-safe, versioning, immutable Object Lock, and a tested restore

-- 1. Warehouse: Time Travel window sized to the RPO; Fail-safe is the last resort.
ALTER TABLE analytics.fct_orders SET DATA_RETENTION_TIME_IN_DAYS = 30;   -- self-service undo
-- Fail-safe: +7 days, non-configurable, provider-only. NOT part of the designed RPO.

-- Recover the broken dbt overwrite to just before the run (seconds, zero-copy):
CREATE OR REPLACE TABLE analytics.fct_orders_fixed
  CLONE analytics.fct_orders AT (OFFSET => -60*45);     -- 45 minutes ago
ALTER TABLE analytics.fct_orders SWAP WITH analytics.fct_orders_fixed;

# 2. Lake: versioning (undo) + immutable Object Lock (ransomware/insider guard).
aws s3api put-bucket-versioning --bucket lake-raw --versioning-configuration Status=Enabled
aws s3api put-object-lock-configuration --bucket lake-raw \
  --object-lock-configuration '{"ObjectLockEnabled":"Enabled",
    "Rule":{"DefaultRetention":{"Mode":"COMPLIANCE","Days":35}}}'

# 3. Bound cost: expire noncurrent versions after the recovery window.
aws s3api put-bucket-lifecycle-configuration --bucket lake-raw --lifecycle-configuration '{
  "Rules":[{"ID":"expire-old","Status":"Enabled","Filter":{},
    "NoncurrentVersionExpiration":{"NoncurrentDays":90}}]}'

# 4. Prove it: a scheduled, gating restore drill that MEASURES recoverability.
restore_drill:
  cadence: weekly
  restore: clone AT (TIMESTAMP) into dr_drill   # isolated, real recovery path
  verify:  [row_count, partition_checksum]      # vs a recorded baseline
  on_fail: { action: page, block_release: true }
  records: { measured_rpo: "<= 15m", last_green: auto }

Step-by-step trace.

Layer	Mechanism	Threat it answers
Warehouse recent	Time Travel (AT/CLONE/UNDROP)	corrupt load, dropped table
Warehouse last resort	Fail-safe (7d, provider)	Time Travel expired
Lake undo	S3 Versioning	overwrite, accidental delete
Lake immutability	Object Lock (compliance)	ransomware, rogue delete
Cost control	noncurrent-version lifecycle	version sprawl
Proof	weekly verified restore drill	silent backup failure

After deployment, the broken dbt overwrite is undone in seconds with a zero-copy clone AT (OFFSET => -45m) and a SWAP; the lake keeps every version so the mysterious deletion is just a delete marker to remove, and Object Lock in compliance mode means even a compromised credential could not have destroyed it; noncurrent versions expire after 90 days to bound cost; and a weekly restore drill clones a point-in-time state into an isolated schema and verifies row counts and checksums, so recoverability is measured, not assumed.

Output:

Metric	Before	After
Corrupt-load recovery	full backup restore (hours)	Time Travel clone (seconds)
Accidental delete (lake)	possibly permanent	reversible (delete marker)
Ransomware on the lake	destroys data	blocked (compliance lock)
Version storage cost	unbounded	bounded (lifecycle)
Recovery confidence	untested	weekly verified drill
Designed RPO source	vague	Time Travel window + lag

Why this works — concept by concept:

• Time Travel and Fail-safe — queryable, cloneable recent history turns a corrupt load or dropped table into a seconds-long, zero-copy undo, and the 7-day Fail-safe net is the provider-only last resort you rely on but never design your RPO around.
• Object versioning — retaining every version makes overwrites and deletes reversible in the lake, converting "the data is gone" into "the data is one version back," with delete markers making even deletions non-destructive.
• Immutable Object Lock — compliance-mode write-once retention means no credential, stolen or malicious, can destroy locked history before its retain-until date, which is precisely what separates copies from recoverable copies under a ransomware threat model.
• Tested restore + bounded cost — a weekly verified restore drill proves recoverability against a baseline while noncurrent-version lifecycle expiry keeps the retained history affordable, so the posture is both trustworthy and cheap.
• Cost — a right-sized Time Travel window, retained-but-expiring versions, and one weekly drill, versus the hours-to-permanent loss of an untested single-copy platform. The eliminated cost is unrecoverable data — O(seconds) point-in-time undo instead of O(hours) restores or O(∞) permanent loss.

Data validation
Topic — data-validation
Data validation problems on restore verification and checksums

Practice →

ETL Topic — etl ETL problems on idempotent loads and safe overwrites

Practice →

---

3. Cross-region replication — Snowflake, S3 CRR, and lag

Keep a warm copy in another region — and remember the lag you tolerate is the data you can lose

The mental model in one line: cross-region replication is the mechanism that answers the region-outage threat — a Snowflake **failover group continuously refreshes databases, roles, and warehouses into a secondary account in another region/cloud, and S3 Cross-Region Replication asynchronously copies every object version into a bucket in another region — and because both are asynchronous, your effective RPO is exactly the replication lag, which makes lag not a background metric but a first-class SLO you monitor and alert on, since a region loss forfeits everything the secondary had not yet received.** Replication buys you a warm copy to promote; it does not buy you a corruption defence, because it faithfully copies bad data too — that is backups' job (section 2).

Snowflake replication and failover groups.

• Replication group vs failover group. A replication group copies objects read-only to a secondary; a failover group additionally lets you promote the secondary to primary — the one you want for DR, since recovery requires a writable target.
• What it replicates. Databases and account objects — roles, warehouses, resource monitors, integrations — so the secondary can actually serve, not just hold data. Replicating tables without the roles/warehouses that use them is a half-recovery.
• Refresh cadence sets the RPO. REPLICATION_SCHEDULE (e.g. every 10 minutes) controls how often the secondary catches up; the schedule is your warehouse RPO, so you set it to the objective.
• Cross-cloud and cross-region. Failover groups work across regions and across clouds (AWS↔Azure↔GCP), so DR can even survive a whole-cloud event for the datasets that justify it.

S3 Cross-Region Replication (CRR).

• Versioning is required. CRR replicates object versions, so both source and destination buckets must have versioning enabled — replication is built on the versioning history from section 2.
• Replication Time Control (RTC). RTC adds a 15-minute replication SLA plus CloudWatch metrics and events, converting "eventually" into a bounded, monitored RPO for the lake.
• Delete-marker replication. Configure whether delete markers replicate; for DR you typically replicate them so the secondary tracks deletions, but combine with Object Lock so a malicious delete cannot propagate destructively.
• Same-account or cross-account. Replicating into a separate account in another region gives an air-gap: a compromised primary control plane cannot reach the DR copy.

Lag and consistency — replication is asynchronous.

• RPO equals lag. Because replication is async, a region loss forfeits whatever had not yet replicated; the newest data is the most likely to be lost, and the amount is the current lag.
• Eventual consistency. The secondary is always slightly behind and may momentarily reflect a mix of refreshed and not-yet-refreshed objects; recovery logic must tolerate a boundary that is "as of the last refresh," not "exactly now."
• Lag is an SLO. Monitor Snowflake refresh recency and S3 ReplicationLatency/BytesPendingReplication; alarm when lag exceeds the RPO objective, because a silently growing backlog means your real RPO has already breached the promised one.
• Backpressure and cost. Replication consumes compute (Snowflake refresh) and transfer/storage (S3 CRR egress); a huge burst can grow lag, so DR capacity planning includes the replication path, not just the primary.

The failure modes senior engineers pre-empt.

• Replicating data but not the ability to serve. Databases replicate but roles, warehouses, or integrations do not, so the promoted secondary cannot run the workload. Mitigation: replicate account objects in the failover group.
• Treating replication as a corruption backup. A corrupt load replicates to DR in minutes; promoting the secondary just gives you the same bad data in another region. Mitigation: pair replication (region case) with Time Travel/versioning (corruption case).
• Unmonitored lag. The RPO objective is 15 minutes but lag has silently grown to two hours. Mitigation: lag is a paged SLO, not a dashboard nobody watches.
• No air-gap. DR copy in the same account/credentials as primary; one compromise reaches both. Mitigation: cross-account replication with immutable retention.

Common interview probes on replication.

• "What's your RPO with async replication?" — the replication lag; that is why lag is an SLO.
• "Replication group or failover group?" — failover group, because DR needs a promotable, writable secondary.
• "Does replication protect against a bad load?" — no, it copies it; that is what backups/Time Travel are for.
• "How do you make S3 CRR meet a bounded RPO?" — enable RTC (15-minute SLA) and alarm on ReplicationLatency.

Worked example — a Snowflake failover group across regions

Detailed explanation. Stand up cross-region DR for a Snowflake account: a failover group on the primary that replicates the databases and the account objects needed to serve, a replica on the secondary, and a refresh schedule that sets the RPO. Then verify the lag.

• Primary. Create a failover group; set the refresh schedule to the RPO.
• Secondary. Create the replica of that group in the other region.
• Verify. Read refresh history to confirm lag is within objective.

Question. Configure a failover group that gives a warehouse RPO of ~10 minutes across two regions and replicates enough to actually serve after promotion.

Input.

Piece	Value
Primary account	myorg.us_east_1
Secondary account	myorg.us_west_2
Replicated objects	databases + roles + warehouses + integrations
Refresh schedule	every 10 minutes → RPO ~10m

Code.

-- On the PRIMARY (us-east-1): define WHAT replicates and HOW OFTEN (RPO).
CREATE FAILOVER GROUP dr_fg
  OBJECT_TYPES = DATABASES, ROLES, WAREHOUSES, RESOURCE_MONITORS, INTEGRATIONS
  ALLOWED_DATABASES = billing, analytics
  ALLOWED_ACCOUNTS = myorg.us_west_2
  REPLICATION_SCHEDULE = '10 MINUTE';        -- refresh cadence == warehouse RPO

-- On the SECONDARY (us-west-2): create the replica of the group.
CREATE FAILOVER GROUP dr_fg
  AS REPLICA OF myorg.us_east_1.dr_fg;

-- Trigger an immediate refresh (otherwise it waits for the schedule):
ALTER FAILOVER GROUP dr_fg REFRESH;          -- run on the secondary

-- Verify lag: how long since the last COMPLETED refresh on the secondary?
SELECT
  max(end_time)                                                 AS last_refresh,
  timestampdiff('second', max(end_time), current_timestamp())  AS lag_seconds
FROM TABLE(information_schema.replication_group_refresh_history('DR_FG'))
WHERE phase_name = 'COMPLETED';

Step-by-step explanation.

1. On the primary, CREATE FAILOVER GROUP declares what replicates: not just DATABASES but ROLES, WAREHOUSES, RESOURCE_MONITORS, and INTEGRATIONS, so the promoted secondary has the identities and compute to actually run the workload — replicating tables alone is a half-recovery.
2. REPLICATION_SCHEDULE = '10 MINUTE' is the RPO knob: the secondary catches up every ten minutes, so a region loss forfeits at most ten minutes of changes — you set this to the objective, not to a default.
3. On the secondary, CREATE FAILOVER GROUP ... AS REPLICA OF links it to the primary's group; from here it pulls refreshes on the schedule, and (crucially) it can be promoted later because it is a failover group, not a read-only replication group.
4. ALTER FAILOVER GROUP dr_fg REFRESH forces an immediate catch-up rather than waiting for the schedule — useful right after setup and just before a planned failover to minimise the gap.
5. The refresh-history query turns lag into a number: seconds since the last completed refresh. This is the effective RPO, and it is the value you alarm on — if it climbs above the objective, your real RPO has already breached the promised one.

Output.

Aspect	Configuration	Effect
What replicates	DBs + roles + warehouses + integrations	secondary can serve after promotion
RPO	REPLICATION_SCHEDULE = '10 MINUTE'	≤ ~10m data loss
Promotable	failover group (not replication group)	secondary can become primary
Effective RPO	lag_seconds from refresh history	monitored, alarmable

Rule of thumb. Use a failover group (promotable), replicate the account objects — roles, warehouses, integrations — not just databases, set REPLICATION_SCHEDULE to your RPO objective, and monitor refresh recency as the effective RPO. Replicating data without the ability to serve it is a recovery that cannot run.

Worked example — S3 Cross-Region Replication with Replication Time Control

Detailed explanation. Give the data lake a bounded, monitored RPO: enable CRR with RTC so every new object version lands in a DR-region bucket within a 15-minute SLA, replicate delete markers so the secondary tracks the namespace, and pair with immutable retention so replication cannot propagate a destructive delete. Configure and verify.

• RTC. A 15-minute replication SLA plus CloudWatch metrics.
• Delete markers. Replicate them so the DR bucket mirrors the source namespace.
• Air-gap. Destination in a separate account so a primary compromise cannot reach it.

Question. Configure S3 CRR with RTC so the lake's DR copy has a bounded 15-minute RPO, and state how you'd monitor it.

Input.

Piece	Value
Source bucket	lake-raw (versioning on)
Destination	lake-raw-dr in us-west-2 (separate account)
SLA	RTC, 15 minutes
Monitor	ReplicationLatency, BytesPendingReplication

Code.

// S3 replication config on lake-raw: async-copy every new version to the DR region.
// Versioning must be ON for both buckets. RTC = 15-minute SLA + CloudWatch metrics.
{
  "Role": "arn:aws:iam::111122223333:role/s3-crr-role",
  "Rules": [{
    "ID": "crr-lake-raw",
    "Status": "Enabled",
    "Priority": 1,
    "Filter": {},
    "DeleteMarkerReplication": { "Status": "Enabled" },
    "Destination": {
      "Bucket": "arn:aws:s3:::lake-raw-dr-uswest2",
      "Account": "444455556666",
      "ReplicationTime": { "Status": "Enabled", "Time": { "Minutes": 15 } },
      "Metrics":         { "Status": "Enabled", "EventThreshold": { "Minutes": 15 } }
    }
  }]
}

# Monitor lag as the lake's RPO (CloudWatch, per replication rule):
#   ReplicationLatency          p95 > 900s  -> ALARM: 15-min RPO at risk
#   BytesPendingReplication     rising, not draining -> ALARM: backlog building
#   OperationsFailedReplication > 0 sustained -> ALARM: rule/permission broken
#
# RTC guarantees 15 min for the vast majority of objects; the ALARM catches the tail
# and any misconfiguration. Replication lag IS the RPO, so it is paged, not just graphed.

Step-by-step explanation.

1. The rule replicates every new version of every object (Filter: {}) from lake-raw to a bucket in another region, so the lake's data continuously lands in the DR region rather than only at a nightly snapshot.
2. ReplicationTime with Minutes: 15 enables RTC — Amazon's 15-minute replication SLA — which converts CRR's default "eventually" into a bounded RPO you can promise, and Metrics publishes the CloudWatch signals to verify it.
3. DeleteMarkerReplication: Enabled makes the DR bucket track deletions in the source namespace, so the secondary is a faithful mirror; paired with Object Lock (section 2), a malicious delete is still blocked at the immutable layer rather than destructively mirrored.
4. Setting the destination Account to a different account creates an air-gap: the DR copy lives under separate credentials and control plane, so a compromise of the primary account cannot reach or destroy the DR data.
5. The monitoring block treats ReplicationLatency and BytesPendingReplication as RPO signals: RTC covers the common case, but the alarms catch the long tail and any broken rule — because an unmonitored replication path can silently fall hours behind while everyone believes the RPO is 15 minutes.

Output.

Aspect	Configuration	Effect
Coverage	Filter: {} all objects	whole bucket replicated
Bounded RPO	RTC Minutes: 15	15-min replication SLA
Namespace fidelity	delete-marker replication	DR mirrors deletions
Air-gap	cross-account destination	primary compromise can't reach DR
RPO monitoring	CloudWatch alarms	lag is a paged SLO

Rule of thumb. Enable S3 CRR with Replication Time Control for a bounded 15-minute lake RPO, replicate into a separate account for an air-gap, and alarm on ReplicationLatency/BytesPendingReplication because the lag is the RPO. Replication is only as good as its monitoring — an unwatched backlog is an already-breached objective.

Worked example — treating replication lag as a first-class SLO

Detailed explanation. The number that decides whether your DR is real is the replication lag, because with async replication it is the RPO. Turn lag into an SLO: define the objective, measure both tiers continuously, alert on breach, and record it so a game day can compare achieved-vs-promised. Wire it up.

• Objective. Warehouse lag ≤ 10 min; lake lag ≤ 15 min.
• Measure. Snowflake refresh recency; S3 ReplicationLatency.
• Act. Page on breach; the breach means the real RPO exceeds the promised one.

Question. Define and enforce a replication-lag SLO across warehouse and lake so a silently growing backlog cannot inflate your real RPO undetected.

Input.

Tier	Signal	Objective	On breach
Warehouse	refresh recency (s)	≤ 600s	page
Lake	ReplicationLatency p95	≤ 900s	page
Lake backlog	BytesPendingReplication	draining	page if rising
Record	last-good lag	for game-day compare	—

Code.

-- Warehouse lag SLO: seconds since the last completed failover-group refresh.
-- Schedule this as a task; emit to your alerting when lag_seconds > objective.
SELECT
  timestampdiff('second', max(end_time), current_timestamp()) AS lag_seconds,
  iff(timestampdiff('second', max(end_time), current_timestamp()) > 600,
      'BREACH', 'OK')                                          AS rpo_status
FROM TABLE(information_schema.replication_group_refresh_history('DR_FG'))
WHERE phase_name = 'COMPLETED';

# Lake lag SLO (CloudWatch alarm as code, illustrative):
alarm "s3-crr-rpo-breach":
  metric   = ReplicationLatency (bucket=lake-raw, rule=crr-lake-raw)
  stat     = p95
  when     = > 900 for 3 datapoints (15 min)
  action   = page data-platform-oncall

alarm "s3-crr-backlog":
  metric   = BytesPendingReplication
  when     = rising for 30 min AND OperationsPendingReplication > 0
  action   = page (backlog not draining -> real RPO growing)

# Record the measured lag so a game day can compare ACHIEVED vs PROMISED RPO.

Step-by-step explanation.

1. The warehouse query computes lag as seconds since the last completed refresh and labels it BREACH/OK against the 600-second objective, so the SLO is evaluated continuously rather than eyeballed — scheduled as a task, it emits an alert the moment lag exceeds the promised RPO.
2. The lake alarm watches ReplicationLatency at p95 over a 15-minute window, catching sustained lag that RTC's common-case SLA misses in the tail — the difference between "usually 15 minutes" and "guaranteed within objective."
3. The backlog alarm on BytesPendingReplication catches the dangerous silent failure: replication that is falling behind faster than it drains, which means the real RPO is growing even if individual object latencies look fine.
4. Both breaches page, not merely graph, because a replication SLO nobody is woken for is a dashboard, not an objective — and the entire premise of async replication DR is that lag equals RPO, so lag must be treated with the seriousness of the RPO itself.
5. Recording the measured lag over time gives a game day (section 4) the achieved-vs-promised comparison: you can only claim an RPO of ten minutes if the historical lag actually stayed under ten minutes, which turns the SLO into evidence.

Output.

Signal	Objective	Meaning if breached
Warehouse refresh recency	≤ 600s	real RPO > promised (warehouse)
ReplicationLatency p95	≤ 900s	real RPO > promised (lake)
BytesPendingReplication	draining	RPO actively growing
Recorded lag history	—	evidence for game-day RPO claim

Rule of thumb. Make replication lag a paged SLO on both tiers — warehouse refresh recency and S3 ReplicationLatency/BytesPendingReplication — because with async replication the lag is your RPO. Record it over time so a game day can prove the achieved RPO matches the promised one; an unwatched backlog is a silently breached objective.

Senior interview question on cross-region replication and RPO

A senior interviewer might ask: "Design cross-region disaster recovery for a Snowflake warehouse and an S3 data lake so a full region outage loses at most ~15 minutes of data and the DR region can actually serve. Cover what you replicate and how often, how you give the lake a bounded RPO, why replication is not your corruption defence, and how you'd know — before a real outage — that your promised RPO is the one you're actually achieving."

Solution Using a failover group, S3 CRR with RTC, an air-gap, and a lag SLO

-- 1. Warehouse: failover group replicating data AND the ability to serve; RPO = 10m.
CREATE FAILOVER GROUP dr_fg
  OBJECT_TYPES = DATABASES, ROLES, WAREHOUSES, INTEGRATIONS
  ALLOWED_DATABASES = billing, analytics
  ALLOWED_ACCOUNTS = myorg.us_west_2
  REPLICATION_SCHEDULE = '10 MINUTE';                 -- warehouse RPO ~10m
CREATE FAILOVER GROUP dr_fg AS REPLICA OF myorg.us_east_1.dr_fg;   -- on secondary

// 2. Lake: CRR with RTC (15-min SLA) into a SEPARATE account (air-gap).
{ "Role": "arn:aws:iam::111122223333:role/s3-crr-role",
  "Rules": [{ "ID": "crr", "Status": "Enabled", "Priority": 1, "Filter": {},
    "DeleteMarkerReplication": { "Status": "Enabled" },
    "Destination": { "Bucket": "arn:aws:s3:::lake-raw-dr", "Account": "444455556666",
      "ReplicationTime": { "Status": "Enabled", "Time": { "Minutes": 15 } },
      "Metrics": { "Status": "Enabled" } } }] }

# 3. Replication is NOT the corruption defence — pair it with section-2 backups.
region outage      -> promote the replica (region case)      : replication
corrupt/bad load   -> Time Travel / S3 version undo (logical) : backups
# A corrupt load replicates to DR in minutes; only a point-in-time undo fixes it.

# 4. Prove the promised RPO: lag is a PAGED SLO on both tiers.
warehouse refresh recency > 600s -> page   (warehouse RPO breached)
S3 ReplicationLatency p95 > 900s -> page   (lake RPO breached)

Step-by-step trace.

Layer	Mechanism	RPO contribution
Warehouse	failover group, 10-min schedule	≤ ~10m, promotable
Serve-ability	replicate roles/warehouses/integrations	secondary can run the workload
Lake	S3 CRR + RTC	≤ 15m bounded
Air-gap	cross-account destination	compromise-resistant DR
Corruption	Time Travel / versioning (section 2)	not replication's job
Proof	lag SLO, paged	achieved RPO = promised RPO

After deployment, a failover group refreshes billing and analytics — plus the roles, warehouses, and integrations needed to serve — into us-west-2 every ten minutes, and S3 CRR with RTC lands every lake object version in a separate DR-region account within fifteen minutes. Replication handles only the region case; the corruption case is covered by Time Travel and S3 versioning from section 2. Replication lag is a paged SLO on both tiers, so the RPO you promise is the RPO you can prove you achieve — verified continuously, not discovered during an outage.

Output:

Metric	Single region	Cross-region DR
Region-outage data loss	total (nothing elsewhere)	≤ ~10–15m (lag)
DR region can serve	no	yes (roles + warehouses replicated)
Lake RPO	unbounded	≤ 15m (RTC)
DR reachable if primary compromised	no	yes (air-gapped account)
Corruption defence	conflated with replication	separate (backups)
RPO confidence	assumed	paged, measured

Why this works — concept by concept:

• Failover group, not just data — replicating databases together with roles, warehouses, and integrations means the promoted secondary can actually run the workload, and being a failover group (not a read-only replication group) makes promotion possible at all.
• S3 CRR with RTC — versioning-based cross-region replication plus the 15-minute Replication Time Control SLA gives the lake a bounded, monitored RPO instead of "eventually," and a cross-account destination air-gaps the DR copy from a primary compromise.
• Replication is not a backup — because async replication copies corruption faithfully, the region-outage mechanism (replication) is kept strictly separate from the corruption mechanism (Time Travel/versioning), so each threat has the tool that actually fixes it.
• Lag as a paged SLO — with async replication the lag is the RPO, so monitoring warehouse refresh recency and S3 replication latency (and paging on breach) is what turns a promised RPO into a proven one.
• Cost — periodic refresh compute and cross-region transfer, versus the total, unbounded loss of a single-region outage. The eliminated cost is a business-ending region failure — O(lag) bounded loss with a promotable warm copy instead of O(everything) with nowhere to fail over to.

Data processing
Topic — data-processing
Data processing problems on cross-region replication and lag

Practice →

ETL Topic — etl ETL problems on replicated and multi-region pipelines

Practice →

---

4. Failover and runbooks — promotion, cutover, and reconciliation

Promote the standby, repoint the clients, replay the gap, then measure what you actually recovered

The mental model in one line: failover is the rehearsed sequence that turns a replicated warm copy into live service — fence the failed primary to avoid split-brain, promote the secondary to primary, cut over DNS/endpoints and connection strings so clients hit the DR region, then reconcile the RPO gap by idempotently replaying the last few minutes from a durable source — and the whole thing is worthless unless it is a written runbook you rehearse on a **game day that measures the actual RPO and RTO against the objective, because the numbers on the architecture diagram are aspirations and the numbers from a drill are facts.** A failover you have never run is a plan; a failover you ran last quarter is a capability.

Promotion — making the secondary the primary.

• Fence first. Before promoting, stop writes to the old primary (revoke access, disable pipelines) so you do not end up with two writable primaries — split-brain, the worst failure, where both regions accept divergent writes.
• Promote the failover group. ALTER FAILOVER GROUP ... PRIMARY on the secondary makes it the read/write primary; the former primary (if reachable) becomes the secondary. This is the point of no return.
• Verify the promotion. Confirm the new primary is writable, roles/warehouses are present, and a canary write/read succeeds before directing traffic — a promotion that half-worked is worse than none.
• Plan fail-back. After the incident, reverse-replicate and promote the original region back during a controlled window; fail-back is a planned failover, not an emergency.

Cutover — repointing the clients.

• DNS/endpoint swap. Update the DNS record (e.g. Route 53 weighted/failover routing) or the endpoint alias so clients resolve to the DR region; pre-set a low TTL (30–60s) so the cutover propagates in seconds, not hours.
• Connection-string rotation. Applications, BI tools, and pipelines hold connection strings/account URLs; rotate them (via config/secret store) to the new primary so nothing keeps hammering the dead region.
• Idempotent, scripted, ordered. The cutover is a pre-written script run in a fixed order (fence → promote → cutover → resume), not a series of console clicks under pressure — humans make mistakes during outages.
• Atomic as possible. Minimise the window where some clients hit the old region and some the new; a staged but quick cutover avoids reads served from stale/dead endpoints.

Reconciliation — closing the RPO gap.

• Find the gap. The async lag means the newest data never replicated; compute gap = incident_time − last_replicated_point — that is the actual RPO you hit, and the data you must recover.
• Replay from a durable source. Re-ingest the gap from a replayable source (Kafka retention, immutable S3 raw, a CDC log); this is why append-only, replayable raw sources are the DR backbone — they make the gap recoverable instead of permanent.
• Idempotent replay. Replay with an idempotent MERGE keyed on the event/natural key so overlapping records cannot double-count — replays must be safe to run twice.
• Reconcile and verify. Compare row counts/control totals against the source, resolve duplicates and partials, and confirm the reconciled state before declaring recovery complete.

Testing DR with game days — and the failure modes senior engineers pre-empt.

• A game day is a measured drill. On a schedule (quarterly), actually promote, cut over, reconcile, and serve from DR — then record the achieved RPO and RTO. You have not tested DR until you have served real reads from the DR region under a stopwatch.
• Runbook as code. The runbook is versioned, scripted, and owned; each game day exercises and improves it, so the steps are proven, not improvised.
• Split-brain. Promoting without fencing the old primary yields two divergent primaries. Mitigation: fence-then-promote, always, and verify the old primary is truly down or isolated.
• Untested cutover. DNS TTLs too high, stale connection strings, missing DR roles — all discovered only under a real promotion. Mitigation: the game day surfaces them before the real disaster does.

Common interview probes on failover.

• "What's the first step of a failover?" — fence the old primary to prevent split-brain, then promote.
• "How do clients find the DR region?" — DNS/endpoint cutover with a pre-set low TTL, plus connection-string rotation.
• "What about the data that didn't replicate?" — reconcile: compute the gap and idempotently replay from a durable source.
• "How do you know your RTO is real?" — a game day that measures the achieved RPO/RTO against the objective.

Worked example — a failover runbook: promotion and cutover

Detailed explanation. Write the ordered, scripted failover: fence the old primary, promote the secondary, swap DNS with a low TTL, and rotate connection strings — the sequence that must run identically whether it is a drill or a 3 a.m. outage. Order matters more than any single command.

• Fence. Stop writes to the old primary (no split-brain).
• Promote. ALTER FAILOVER GROUP ... PRIMARY on the secondary.
• Cutover. Route 53 record swap (low TTL) + connection-string rotation.

Question. Write the failover runbook that promotes the DR region and repoints clients, in the correct order, with split-brain prevention.

Input.

Step	Action	Guards against
1. Fence	stop writes to old primary	split-brain
2. Promote	ALTER FAILOVER GROUP dr_fg PRIMARY	non-writable DR
3. Cutover	Route 53 swap (TTL 30s) + rotate strings	clients on dead region
4. Resume	restart pipelines on new primary	stale reads/writes

Code.

# failover_runbook.md — run TOP to BOTTOM. Same steps for a game day or a real outage.

# 1. FENCE the old primary (prevent split-brain): disable pipelines + revoke writes.
#    (If the region is fully down this is automatic — but never assume.)

-- 2. PROMOTE the secondary to primary (run on us-west-2). Point of no return.
ALTER FAILOVER GROUP dr_fg PRIMARY;

-- Verify the new primary is writable and can serve (canary):
CREATE TEMP TABLE _canary AS SELECT current_region(), current_timestamp();
SELECT * FROM _canary;                    -- must succeed before cutover

# 3. CUTOVER: repoint DNS at the DR region (TTL pre-set to 30s -> seconds to propagate).
aws route53 change-resource-record-sets --hosted-zone-id Z123 --change-batch '{
  "Changes":[{"Action":"UPSERT","ResourceRecordSet":{
    "Name":"warehouse.example.com","Type":"CNAME","TTL":30,
    "ResourceRecords":[{"Value":"myorg-us-west-2.snowflakecomputing.com"}]}}]}'

# Rotate the account URL / connection strings apps + BI + pipelines read from config:
aws ssm put-parameter --name /prod/warehouse/account_url --overwrite \
  --value "myorg-us-west-2.snowflakecomputing.com"
# 4. RESUME pipelines pointed at the new primary; begin reconciliation (next example).

Step-by-step explanation.

1. Step 1 fences the old primary — disabling pipelines and revoking write access — so it cannot accept writes while the secondary is being promoted; skipping this is how you get split-brain, two regions with divergent writes that are agonising to reconcile.
2. Step 2 promotes the secondary with ALTER FAILOVER GROUP dr_fg PRIMARY, the point of no return, and immediately runs a canary write/read to verify the new primary is actually writable and has its roles/warehouses — a promotion you did not verify is a promotion you cannot trust.
3. Step 3's DNS swap works in seconds only because the record's TTL was pre-set to 30 seconds before the incident; a default 24-hour TTL would leave clients resolving to the dead region for a day, so low TTLs are a DR design decision made in advance.
4. Rotating the connection string in the parameter/secret store repoints every app, BI tool, and pipeline that reads its target from config, so the whole fleet follows the DNS change instead of some clients clinging to the old account URL.
5. The entire runbook is ordered and scripted — fence, promote, verify, cutover, resume — so it runs identically in a calm game day and a panicked 3 a.m. outage; the value is that no one is inventing steps under pressure, because the steps were proven on the last drill.

Output.

Step	Command	Verified by
Fence	disable pipelines / revoke writes	no writes on old primary
Promote	ALTER FAILOVER GROUP dr_fg PRIMARY	canary write/read succeeds
DNS cutover	Route 53 UPSERT, TTL 30s	resolves to DR in seconds
String rotation	SSM parameter overwrite	fleet targets new primary

Rule of thumb. Failover is an ordered, scripted runbook — fence, promote, verify, cut over (low-TTL DNS + connection-string rotation), resume — run identically for a drill or a disaster. Fence before you promote to prevent split-brain, and pre-set low DNS TTLs so the cutover is seconds, not hours.

Worked example — post-failover reconciliation and replay

Detailed explanation. Async replication means the last few minutes never made it to DR. After promotion you must find that gap and idempotently replay it from a durable source, or it is permanent loss. Compute the gap, replay from the replayable raw source, and reconcile.

• The gap. incident_time − last_replicated_point = the actual RPO hit.
• The source. A replayable raw source (Kafka retention, immutable S3 raw).
• The replay. An idempotent MERGE keyed on the event key so re-runs are safe.

Question. After promoting the DR region, recover the un-replicated gap by replaying from a durable source, without double-counting.

Input.

Piece	Value
Last replicated event	09:05:00 (on new primary)
Incident time	09:12:00
Actual RPO (gap)	7 minutes
Replay source	immutable S3 raw / Kafka (after 09:05)
Dedupe key	event_id (idempotent MERGE)

Code.

-- 1. What is the newest event the promoted primary ACTUALLY has? (defines the gap)
SELECT max(event_ts) AS last_replicated FROM analytics.raw_events;    -- -> 09:05:00
-- gap = incident_time (09:12) - last_replicated (09:05) = 7 min  == the ACTUAL RPO hit.

-- 2. Stage the un-replicated tail from the DURABLE, replayable source (S3 raw/Kafka).
COPY INTO staging.replay_after_0905
FROM @raw_stage/events/2026-08-26/           -- immutable raw, survives the region loss
FILE_FORMAT = (TYPE = parquet)
PATTERN = '.*(0905|0906|0907|0908|0909|0910|0911|0912).*';

-- 3. Idempotent MERGE keyed on event_id — replaying overlaps cannot double-count.
MERGE INTO analytics.raw_events t
USING staging.replay_after_0905 s
  ON t.event_id = s.event_id                  -- natural/event key = dedupe key
WHEN NOT MATCHED THEN
  INSERT (event_id, event_ts, payload) VALUES (s.event_id, s.event_ts, s.payload);

-- 4. Reconcile: recovered count must match the source's count for the gap window.
SELECT count(*) FROM analytics.raw_events WHERE event_ts > '09:05:00';   -- vs source

Step-by-step explanation.

1. The first query reads the newest event the promoted primary holds (09:05); subtracting that from the incident time (09:12) yields the actual RPO — 7 minutes — which is both the data you lost to replication lag and the exact window you must replay.
2. The replay source is immutable, append-only raw in S3 (or Kafka retention), which survived the region loss because it lives in the DR region too or in a durable stream — this is why replayable raw sources are the DR backbone: they make the gap recoverable rather than permanent.
3. Staging only the gap window (0905–0912) limits the replay to exactly the missing tail rather than reprocessing everything, keeping the recovery fast and focused.
4. The MERGE ... ON t.event_id = s.event_id ... WHEN NOT MATCHED THEN INSERT is idempotent: records already present (some of the boundary may have replicated) are skipped, and only truly missing events are inserted, so running the replay twice — or replaying an overlapping window — cannot double-count. Idempotency is what makes replay safe under uncertainty.
5. The final reconciliation compares the recovered count for the gap window against the source's count, giving objective proof that the gap is closed before declaring recovery complete — reconciliation is a verification step, not an assumption.

Output.

Aspect	Value
Actual RPO (measured gap)	7 minutes (09:05 → 09:12)
Replay source	immutable S3 raw / Kafka
Double-count risk	none (idempotent MERGE on event_id)
Recovery proof	recovered count == source count

Rule of thumb. After promotion, compute the gap (incident_time − last_replicated) as your actual RPO, then idempotently replay it from a durable, replayable source keyed on the event id so re-runs cannot double-count, and reconcile counts against the source before declaring done. Replayable raw sources are what make the RPO gap recoverable instead of permanent.

Worked example — a game day that measures RPO and RTO

Detailed explanation. The only proof your DR works is a game day: a scheduled, timed drill that promotes, cuts over, reconciles, and serves from DR while a stopwatch runs — producing the achieved RPO and RTO to compare against the objective. Script the drill and its measurements.

• Freeze the objective. State RPO/RTO targets before starting.
• Run the runbook. Fence → promote → cutover → reconcile, timestamped.
• Measure. Achieved RTO = serving_time − incident; achieved RPO = incident − last_replicated.

Question. Design a quarterly game day that produces the two numbers — achieved RPO and achieved RTO — and decides pass/fail against the objective.

Input.

Milestone	Target	Measured as
Objective	RPO ≤ 10m, RTO ≤ 30m	frozen at T+0
Promote + cutover	—	timestamps
Serving from DR	RTO	serving − incident
Gap replayed	RPO	incident − last_replicated

Code.

# game_day.md — quarterly, MEASURED. Not tested until you SERVE from DR under a clock.

Quarterly DR game day
  T+0    declare drill; FREEZE objective: RPO <= 10m, RTO <= 30m
  T+0    fence primary writes; ALTER FAILOVER GROUP dr_fg PRIMARY  (on secondary)
  T+4m   Route 53 cutover (TTL 30s); rotate connection strings
  T+9m   smoke tests: canary queries + row-count reconciliation pass
  T+12m  replay the gap (idempotent MERGE); reconciliation counts match
  T+12m  declare "SERVING FROM DR"

MEASURE (the deliverable):
  achieved_RTO = serving_ts(12m) - incident_ts(0m) = 12m   (<= 30m)  -> PASS
  achieved_RPO = incident_ts     - last_replicated  = 7m    (<= 10m)  -> PASS

FAIL-BACK (planned, after the window):
  reverse-replicate DR -> original; promote original back; cut over; measure again.

OUTCOME: record achieved RPO/RTO in the DR register; file runbook gaps as tickets.

Step-by-step explanation.

1. The game day freezes the objective at T+0 (RPO ≤ 10m, RTO ≤ 30m) so the drill has an unambiguous pass/fail bar, then executes the exact production runbook — fence, promote, cutover, reconcile — with every milestone timestamped.
2. The achieved RTO is measured as the time from the (simulated) incident to serving real reads from DR — 12 minutes here — which is the honest recovery time including promotion, cutover propagation, and reconciliation, not just the promotion command's runtime.
3. The achieved RPO is the reconciliation gap — 7 minutes — measured from the newest replicated event, giving the real data-loss figure rather than the theoretical replication-schedule number.
4. Both numbers are compared to the frozen objective and recorded; a miss is not a disaster but a finding — the drill's purpose is to surface the too-high DNS TTL, the missing DR role, or the un-replayable source before a real outage, and file each as a ticket.
5. Fail-back is run as a planned failover after the window, proving the recovery is reversible and that the original region can be restored to primary in a controlled way — because a DR capability you cannot fail back from is a one-way trip you will hesitate to take.

Output.

Metric	Objective	Achieved (drill)	Verdict
RTO	≤ 30m	12m	pass
RPO	≤ 10m	7m	pass
Runbook gaps found	—	filed as tickets	improves next drill
Fail-back proven	yes	yes (planned)	reversible

Rule of thumb. Run a quarterly game day that freezes the objective, executes the real runbook, and measures achieved RPO (the reconciliation gap) and achieved RTO (incident-to-serving), then records both and files every gap as a ticket. You have not tested DR until you have served from the DR region under a stopwatch and written down the two numbers.

Senior interview question on failover execution and runbooks

A senior interviewer might ask: "Your primary region just went dark. Walk me through the failover end to end: how you avoid split-brain, how you promote and repoint every client, how you recover the data that hadn't replicated yet without double-counting, and — critically — how you'd have known in advance that this exact sequence achieves your promised RPO and RTO."

Solution Using fenced promotion, scripted cutover, idempotent replay, and a measured game day

-- 1. FENCE + PROMOTE (prevent split-brain, then make DR the primary). On us-west-2:
ALTER FAILOVER GROUP dr_fg PRIMARY;
-- canary: confirm writable + roles/warehouses present before any traffic.
CREATE TEMP TABLE _canary AS SELECT current_timestamp(); SELECT * FROM _canary;

# 2. CUTOVER: low-TTL DNS swap + connection-string rotation (pre-scripted, ordered).
aws route53 change-resource-record-sets --hosted-zone-id Z123 --change-batch file://cutover.json
aws ssm put-parameter --name /prod/warehouse/account_url --overwrite \
  --value "myorg-us-west-2.snowflakecomputing.com"

-- 3. RECONCILE: measure the gap, idempotently replay from the durable source.
SELECT max(event_ts) AS last_replicated FROM analytics.raw_events;   -- gap boundary
MERGE INTO analytics.raw_events t
USING staging.replay s
  ON t.event_id = s.event_id                    -- idempotent: no double-count
WHEN NOT MATCHED THEN INSERT VALUES (s.event_id, s.event_ts, s.payload);

# 4. PROVE it in advance: the quarterly game day already measured this exact sequence.
achieved_RTO = serving_ts - incident_ts   # 12m  (objective <= 30m)  PASS
achieved_RPO = incident_ts - last_replicated  # 7m (objective <= 10m)  PASS
# The runbook is the drilled one; the numbers are measured, not hoped for.

Step-by-step trace.

Phase	Action	Guarantee
Fence	stop writes to old primary	no split-brain
Promote	ALTER FAILOVER GROUP dr_fg PRIMARY + canary	writable, serve-able DR
Cutover	low-TTL DNS + string rotation	clients on DR in seconds
Reconcile	measure gap, idempotent replay	no permanent loss, no double-count
Measure	achieved RPO/RTO vs objective	proven, not assumed
Fail-back	planned reverse promotion	reversible recovery

After the failover, the old primary is fenced so no split-brain can occur; the secondary is promoted and canary-verified as writable; a pre-scripted low-TTL DNS swap plus connection-string rotation moves every client to the DR region in seconds; the un-replicated 7-minute gap is idempotently replayed from an immutable source so nothing is permanently lost and nothing double-counts; and the achieved RPO (7m) and RTO (12m) are known to be within objective because this exact runbook was measured on the last quarterly game day. Recovery is a rehearsed, measured procedure — not an improvisation.

Output:

Metric	Improvised failover	Runbook + game day
Split-brain risk	high (promote-first)	none (fence-first)
Cutover time	hours (high TTL, manual)	seconds (low TTL, scripted)
Un-replicated data	lost or double-counted	replayed idempotently
RPO/RTO known in advance	no	yes (measured last quarter)
Recovery repeatability	one-off scramble	drilled, reversible

Why this works — concept by concept:

• Fence before promote — stopping writes to the old primary before promoting the secondary eliminates split-brain, the divergent-writes failure that is the single worst outcome of a botched failover and the hardest to reconcile afterward.
• Scripted low-TTL cutover — a pre-written DNS swap with a pre-set low TTL plus connection-string rotation repoints the entire client fleet in seconds and in a fixed order, so no one improvises endpoint changes under outage pressure.
• Idempotent replay from a durable source — computing the gap and replaying it with a MERGE keyed on the event id recovers the un-replicated tail without double-counting, and only works because append-only, replayable raw sources were kept as the DR backbone.
• Measured game day — rehearsing the exact runbook quarterly and recording achieved RPO/RTO converts the promised numbers into demonstrated ones and surfaces runbook gaps (TTLs, missing roles, un-replayable sources) before a real disaster does.
• Cost — a scripted runbook and one drill per quarter, versus a multi-hour improvised scramble with split-brain and double-counted or lost data. The eliminated cost is the chaos of an unrehearsed recovery — O(minutes) measured, repeatable failover instead of O(hours) of guesswork with data-integrity damage.

Design
Topic — design
Design problems on failover runbooks and cutover

Practice →

Optimization Topic — optimization Optimization problems on recovery-time and replay efficiency

Practice →

---

5. Applying RPO/RTO — pick a DR tier per dataset

Not every dataset earns active-active — tier each one by impact, RPO/RTO, and the cost of recovery

The mental model in one line: applying disaster recovery at platform scale is a per-dataset exercise — you take every dataset, set its RPO and RTO from business impact, and place it on the tier ladder (backup/restore → pilot light → warm standby → active-active) at the cheapest rung that meets both, then compose the individual choices into one **business continuity blueprint with an owner, a drill cadence, and a cost you can defend — because a blanket "replicate everything active-active" gold-plates the replayable data while a blanket "nightly backup" under-protects the ledger, and the senior move is spending each dollar where downtime and data loss actually hurt.** The DR matrix — dataset → tier → mechanism → RPO/RTO → cost — is the artifact that turns DR from a slogan into a budgeted, testable program.

Sizing RPO/RTO from business impact.

• Start from the cost of downtime and loss. For each dataset ask: what does an hour of unavailability cost, and what does losing an hour of its data cost? Revenue, compliance, and safety-critical data have low tolerance; replayable or rebuildable data has high tolerance.
• RPO and RTO are separate sliders. Rebuildable-but-urgent data (dashboards) wants a loose RPO and a tight RTO; a compliance archive wants a tight RPO and can tolerate a loose RTO. Size them independently.
• Rebuildability lowers the tier. If a dataset is deterministically recomputable from a retained raw source, its effective RPO/RTO can be met by replicating the source and the code and recomputing — far cheaper than replicating the derived data hot.
• Regulatory floors. Some data has externally mandated retention/recovery requirements; those set a floor the business impact analysis cannot go below.

The tier ladder, with rough numbers.

• Backup/restore (cold). RPO hours, RTO hours, cost $. Cross-region snapshots/backups; recover by restoring. For replayable raw, rebuildable staging, low-impact data.
• Pilot light. RPO minutes (replication lag), RTO tens of minutes (start compute), cost $$. Core data replicated to a dormant DR environment; scale up on failover.
• Warm standby. RPO minutes, RTO minutes, cost $$$. A scaled-down live copy running continuously; scale up on failover. For important, must-be-back-fast data.
• Active-active (hot). RPO ~0, RTO ~0, cost $$$$. Full capacity in multiple regions simultaneously. For existential, zero-loss, always-on data only.

Composing the blueprint.

• The DR matrix. One row per dataset: RPO, RTO, tier, mechanism (backup / replication / active-active), and estimated cost. This is the single source of truth for the DR program and the thing you present to leadership.
• Shared mechanisms, per-dataset settings. One failover group and one CRR configuration can serve several datasets at different effective tiers by tuning schedules and which objects they include — you do not build four separate stacks.
• Ownership and cadence. Every tier names a runbook owner and a drill cadence; the higher the tier, the more frequently it is game-dayed, because the cost of it silently not working is higher.
• Business continuity, not just DR. The blueprint ties technical recovery to the business's continuity plan: who declares a disaster, who communicates, and what "acceptable degraded service" looks like per dataset.

The failure modes senior engineers pre-empt.

• Blanket tiering. One policy for all datasets either over-spends on the cheap ones or under-protects the critical ones. Mitigation: the per-dataset matrix.
• Tiering by gut, not impact. "It feels important" instead of a downtime/loss cost. Mitigation: a business impact analysis that produces the RPO/RTO numbers.
• Ignoring rebuildability. Paying to replicate derived data hot when it recomputes cheaply from a replicated source. Mitigation: replicate source + code, recompute on failover.
• A blueprint that is never drilled. A beautiful matrix nobody rehearses. Mitigation: each tier has an owner and a game-day cadence; the matrix records the measured RPO/RTO, not the aspirational one.

Common interview probes on DR tiering.

• "Does everything need active-active?" — no; tier by impact, most data is cheaper tiers.
• "How do you set RPO/RTO for a dataset?" — from the cost of its downtime and data loss, sized independently.
• "How does rebuildability change the tier?" — replicate the source and code and recompute, instead of replicating derived data hot.
• "What's the deliverable of a DR program?" — a per-dataset matrix with tiers, mechanisms, costs, owners, and measured drill results.

Worked example — a per-dataset RPO/RTO to tier matrix

Detailed explanation. Take a real platform's datasets and build the DR matrix: for each, set RPO/RTO from impact, pick the cheapest sufficient tier, and name the mechanism. The matrix is the artifact you defend to leadership and drive the implementation from.

• The inputs. Business impact (downtime cost, loss tolerance) per dataset.
• The output. Tier + mechanism + RPO/RTO + relative cost, per dataset.
• The discipline. Cheapest tier that meets both objectives; exploit rebuildability.

Question. Build a DR matrix for four datasets, choosing each tier from its RPO/RTO and justifying the cost.

Input.

Dataset	Downtime cost	Loss tolerance	RPO	RTO
Billing ledger	severe	~0	≤ 5m	≤ 15m
Ops dashboards	high	minutes	≤ 15m	≤ 15m
Curated marts	moderate	~1h	≤ 1h	≤ 1h
Raw clickstream	low	hours	≤ 6h	≤ 6h

Code.

# dr_matrix.yaml — the single source of truth for the DR program.
datasets:
  billing_ledger:
    rpo: 5m   ; rto: 15m
    tier: warm-standby            # live scaled-down replica; scale up on failover
    mechanism: failover-group (5-min schedule) + Time Travel 30d
    cost: "$$$"                   # justified: revenue + compliance, zero loss
  ops_dashboards:
    rpo: 15m  ; rto: 15m
    tier: pilot-light             # rebuildable from marts, but needed back fast
    mechanism: failover-group (15-min) + recompute on failover
    cost: "$$"
  curated_marts:
    rpo: 1h   ; rto: 1h
    tier: pilot-light             # recomputable from raw in ~1h
    mechanism: replicate definitions + recent copy; recompute the rest
    cost: "$$"
  raw_clickstream:
    rpo: 6h   ; rto: 6h
    tier: backup-restore          # immutable, replayable source IS the backup
    mechanism: S3 CRR (no RTC) cross-region snapshot
    cost: "$"

Step-by-step explanation.

1. The billing ledger's zero loss tolerance and severe downtime cost force the top tiers: a warm standby with a 5-minute failover-group schedule (for the region case) plus a 30-day Time Travel window (for the corruption case), and the $$$ cost is explicitly justified by revenue and compliance exposure.
2. Ops dashboards split their sliders — loose-ish RPO (they are rebuildable from marts) but tight RTO (you are blind without them during an incident) — landing on pilot light where the definitions replicate and the dashboards recompute quickly on failover, at $$ rather than $$$.
3. Curated marts exploit rebuildability: rather than replicating the derived tables hot, you replicate their definitions and a recent copy and recompute the rest from raw in about an hour, meeting the 1-hour objectives at pilot-light cost.
4. Raw clickstream sits at the cheapest rung because its immutable, replayable source is the backup — a plain cross-region snapshot (CRR without the RTC premium) meets a 6-hour objective, and paying more would be gold-plating replayable data.
5. Composed together, the four rows are the DR matrix: each dataset gets the cheapest tier that meets its measured objectives, the mechanisms reuse shared infrastructure (one failover group, one CRR setup, tuned per dataset), and the total cost is defensible line by line to leadership.

Output.

Dataset	Tier	RPO / RTO	Mechanism	Cost
Billing ledger	warm standby	5m / 15m	failover group + Time Travel	$$$
Ops dashboards	pilot light	15m / 15m	replicate + recompute	$$
Curated marts	pilot light	1h / 1h	replicate defs + recompute	$$
Raw clickstream	backup/restore	6h / 6h	cross-region snapshot	$

Rule of thumb. Build one DR matrix — a row per dataset with RPO, RTO, tier, mechanism, and cost — sizing each from business impact and choosing the cheapest sufficient tier, exploiting rebuildability to drop derived data to a cheaper rung. The matrix, not a slogan, is the deliverable you defend and implement from.

Worked example — what a higher tier actually buys (and costs)

Detailed explanation. The tier decision is an economic one, so you must be able to say what the next rung buys and costs. Compare pilot light, warm standby, and active-active for the same dataset and show the marginal RPO/RTO gain per marginal dollar — the reasoning that justifies (or rejects) an upgrade.

• The question. Is upgrading a dataset from pilot light to warm standby to active-active worth it?
• The comparison. Marginal RPO/RTO improvement vs marginal always-on cost.
• The rule. Upgrade only when the downtime/loss cost saved exceeds the standby cost added.

Question. For the billing ledger, compare pilot light, warm standby, and active-active on RPO/RTO and cost, and decide the right tier.

Input.

Tier	RPO	RTO	Always-on cost	Buys you
Pilot light	~lag (min)	tens of min (start compute)	low	cheap warm data
Warm standby	~lag (min)	minutes (scale up)	medium	fast recovery
Active-active	~0	~0	high	zero loss, no failover

Code.

Billing ledger — marginal analysis (what each upgrade BUYS and COSTS).

Pilot light      RPO ~5m, RTO ~30m,  cost $$
  - data is replicated, but DR compute is OFF -> ~30m to start it on failover.
  - risk: 30m RTO may breach a 15m objective for revenue-critical billing.

  --> upgrade to WARM STANDBY? marginal cost: +running a small live DR warehouse ($).
      marginal buy: RTO 30m -> ~5m (compute already running, just scale up).
      DECISION: YES. A 25-minute faster recovery for revenue+compliance data is
                worth a small always-on warehouse. Meets RTO <= 15m.

Warm standby     RPO ~5m, RTO ~5m,   cost $$$
  --> upgrade to ACTIVE-ACTIVE? marginal cost: +full second-region capacity ($$$).
      marginal buy: RPO 5m -> ~0, RTO 5m -> ~0 (no failover at all).
      DECISION: usually NO. Paying full double capacity to shave 5m of RPO/RTO
                is only justified if a single lost transaction or 5m outage is
                existential (e.g. real-time payments). For batch billing: over-buy.

Verdict: WARM STANDBY. Cheapest tier that meets RPO<=5m, RTO<=15m.

Step-by-step explanation.

1. At pilot light the data is replicated but DR compute is dormant, so the RTO is dominated by the ~30 minutes to start compute on failover — which may breach a 15-minute objective for revenue-critical billing, exposing the gap that justifies looking at an upgrade.
2. The upgrade to warm standby costs a small always-on DR warehouse but buys a drop from ~30-minute to ~5-minute RTO, because compute is already running and only needs scaling — a large recovery-time gain for a modest, bounded cost, so the decision is yes.
3. The upgrade from warm standby to active-active costs a full second-region capacity but only shaves the last ~5 minutes of RPO/RTO to near zero — a small marginal gain for a large marginal cost.
4. That final upgrade is justified only when a single lost transaction or a 5-minute outage is existential (real-time payments, safety systems); for batch billing it is over-buying, so the decision is usually no.
5. The verdict is warm standby — the cheapest tier meeting RPO ≤ 5m and RTO ≤ 15m — reached by marginal analysis: upgrade while the downtime/loss cost saved exceeds the standby cost added, and stop when it no longer does.

Output.

Upgrade	Marginal buy	Marginal cost	Decision
Pilot light → warm standby	RTO 30m → 5m	small live warehouse	yes (meets 15m RTO)
Warm standby → active-active	RPO/RTO 5m → ~0	full 2nd-region capacity	usually no (over-buy)
Chosen tier	RPO 5m, RTO 5m	$$$	warm standby

Rule of thumb. Decide a tier by marginal analysis: upgrade a rung only while the downtime and data-loss cost it saves exceeds the always-on cost it adds. Active-active's near-zero RPO/RTO is worth its full double-capacity cost only for existential, zero-loss data — for everything else, stop at the cheapest tier that meets the objective.

Worked example — the DR blueprint and drill calendar

Detailed explanation. The per-dataset choices compose into one program: a blueprint that names mechanisms, owners, and a drill calendar so the matrix is a living, tested capability rather than a document. Assemble it and schedule the game days by tier.

• The blueprint. Shared mechanisms serving datasets at their tiers.
• The calendar. Higher tiers drilled more often; every tier has an owner.
• The tie-in. Business continuity — who declares, who communicates.

Question. Compose the per-dataset matrix into a DR program with shared mechanisms, ownership, and a tier-driven drill calendar.

Input.

Element	Content
Mechanisms	1 failover group + 1 CRR config, tuned per dataset
Ownership	runbook owner per tier; exec sponsor
Drill cadence	warm standby monthly; pilot light quarterly; backup semiannually
Continuity	who declares a disaster; comms plan

Code.

# dr_program.yaml — the matrix, made operational.
shared_mechanisms:
  snowflake_failover_group: { schedules: { billing: 5m, dashboards: 15m, marts: 15m } }
  s3_crr: { rtc: { billing_lake: on }, plain: { clickstream: on } }
  backups: { time_travel: { billing: 30d, marts: 7d }, object_lock: { lake: COMPLIANCE-35d } }

drill_calendar:                          # higher tier -> drilled more often
  warm_standby:   { datasets: [billing_ledger],            game_day: monthly }
  pilot_light:    { datasets: [ops_dashboards, curated_marts], game_day: quarterly }
  backup_restore: { datasets: [raw_clickstream],           restore_drill: weekly, failover_test: semiannual }

ownership:
  runbook_owner: data-platform-oncall
  exec_sponsor:  cto
business_continuity:
  declare_disaster: incident-commander
  comms: { status_page: on, stakeholders: [finance, support], cadence: 30m }
  degraded_service: "reads from last good mart; writes queued until primary restored"

Step-by-step explanation.

1. The blueprint reuses shared mechanisms — one failover group and one CRR configuration — tuned per dataset (5-minute schedule for billing, 15-minute for dashboards/marts), so four tiers are served by one stack rather than four, which is what keeps the program affordable to build and operate.
2. Backups are layered in per dataset (30-day Time Travel for billing, compliance Object Lock on the lake), so the corruption/deletion defence is part of the same blueprint as the region-outage replication, not a separate afterthought.
3. The drill calendar is tier-driven: warm standby (billing) is game-dayed monthly, pilot light quarterly, backup/restore verified weekly with a semiannual failover test — because the cost of a higher tier silently not working is higher, so it is exercised more often.
4. Every tier names a runbook owner and the program names an exec sponsor, so recovery has clear accountability; a blueprint without an owner is a document, not a capability.
5. The business-continuity block ties technical recovery to the organisation: who declares a disaster, how stakeholders are communicated with, and what degraded service is acceptable — because DR that restores data but leaves the business unsure how to operate is only half a plan.

Output.

Program element	Choice	Why
Mechanisms	1 failover group + 1 CRR, tuned	shared infra, per-dataset tiers
Drill cadence	monthly / quarterly / semiannual	by tier risk
Ownership	owner per tier + exec sponsor	accountable recovery
Continuity	declare + comms + degraded mode	DR tied to the business

Rule of thumb. Compose the per-dataset matrix into one program with shared, per-dataset-tuned mechanisms, a tier-driven drill calendar (higher tiers drilled more often), a named owner per tier, and a business-continuity plan for who declares and communicates. The program — owned and drilled — is what makes the matrix a capability instead of a spreadsheet.

Senior interview question on platform-wide DR strategy

A senior interviewer might ask: "You own DR for a whole data platform — a warehouse and a lake feeding billing, ops dashboards, curated marts, and raw event capture. Design the strategy: how you set RPO/RTO per dataset, how you tier each one and justify the cost, how you compose it into a blueprint with shared mechanisms and ownership, and how you keep it a tested capability rather than a document."

Solution Using a per-dataset matrix, cheapest-sufficient tiers, shared mechanisms, and a drill calendar

# 1. MEASURE per dataset, then TIER at the cheapest sufficient rung.
matrix:
  billing_ledger: { rpo: 5m,  rto: 15m, tier: warm-standby,   cost: $$$ }
  ops_dashboards: { rpo: 15m, rto: 15m, tier: pilot-light,    cost: $$  }
  curated_marts:  { rpo: 1h,  rto: 1h,  tier: pilot-light,    cost: $$  }
  raw_clickstream:{ rpo: 6h,  rto: 6h,  tier: backup-restore, cost: $   }

-- 2. SHARED region-outage mechanism, tuned per dataset (one failover group).
CREATE FAILOVER GROUP dr_fg
  OBJECT_TYPES = DATABASES, ROLES, WAREHOUSES, INTEGRATIONS
  ALLOWED_DATABASES = billing, analytics
  ALLOWED_ACCOUNTS = myorg.us_west_2
  REPLICATION_SCHEDULE = '5 MINUTE';        -- tightest tier (billing) drives it
-- 3. Corruption defence layered on (NOT replication): Time Travel per impact.
ALTER TABLE billing.ledger  SET DATA_RETENTION_TIME_IN_DAYS = 30;
ALTER TABLE analytics.marts SET DATA_RETENTION_TIME_IN_DAYS = 7;

# 4. Keep it a CAPABILITY: tier-driven drill calendar + ownership + continuity.
drills:
  billing_ledger:  game_day: monthly       # highest tier, drilled most
  dashboards_marts: game_day: quarterly
  raw_clickstream: { restore_drill: weekly, failover_test: semiannual }
ownership: { runbook_owner: data-platform-oncall, exec_sponsor: cto }
continuity: { declare: incident-commander, comms_every: 30m,
              degraded: "serve last good mart; queue writes" }
records: { measured_rpo_rto: from-last-game-day }   # matrix stores ACHIEVED numbers

Step-by-step trace.

Step	Decision	Result
Measure	RPO/RTO per dataset from impact	5m–6h, sized independently
Tier	cheapest sufficient rung	warm/pilot/backup, no gold-plating
Region mechanism	one failover group, tuned	shared infra serves all tiers
Corruption mechanism	Time Travel/versioning	separate from replication
Prove	tier-driven drill calendar	measured, not assumed
Own	runbook owner + exec sponsor	accountable, continuity-tied

After the rollout, every dataset has a measured RPO/RTO and sits at the cheapest tier that meets it — warm standby for billing, pilot light for dashboards and marts, backup/restore for replayable raw — served by one shared failover group and CRR configuration tuned per dataset, with Time Travel and versioning layered on for the corruption case. A tier-driven drill calendar (billing monthly, others quarterly/semiannually) keeps the whole thing a tested capability whose matrix stores the RPO/RTO actually achieved, owned by an on-call team with an exec sponsor and a business-continuity plan.

Output:

Metric	Blanket policy	Per-dataset DR program
RPO/RTO	one number, mismatched	measured per dataset
Cost fit	gold-plated or under-protected	cheapest sufficient per tier
Infrastructure	four stacks or one wrong stack	one shared, tuned stack
Corruption vs region	conflated	separate mechanisms
Tested	rarely	tier-driven drill cadence
Accountability	diffuse	owner + sponsor + continuity

Why this works — concept by concept:

• Measure per dataset — sizing RPO and RTO from each dataset's downtime and loss cost (independently) produces a spec you can tier and test against, and exposes that a platform's datasets need very different, differently priced protection.
• Cheapest sufficient tier — placing each dataset at the lowest rung that meets its objectives, and exploiting rebuildability to drop derived data cheaper, spends money only where downtime and loss actually hurt — the definition of business continuity done economically.
• Shared, tuned mechanisms — one failover group and one CRR configuration, tuned per dataset by schedule and scope, serve every tier, so the program is affordable to build and operate instead of four parallel stacks.
• Separate mechanisms per threat, tested on a cadence — replication for the region case and Time Travel/versioning for the corruption case, each drilled at a tier-appropriate frequency, keep the matrix a demonstrated capability with measured numbers rather than a document with hopeful ones.
• Cost — one shared stack, per-dataset-tuned schedules, and a tier-driven drill calendar, versus the waste of blanket active-active or the risk of blanket nightly backups. The eliminated cost is both over-spend and catastrophic under-protection — O(impact) spend that matches each dataset instead of O(max) everywhere or O(min) everywhere.

Design
Topic — design
Design problems on DR tiering and business continuity

Practice →

Optimization
Topic — optimization
Optimization problems on cost-vs-recovery trade-offs

Practice →

---

Cheat sheet — disaster recovery for data platforms

• The two numbers. RPO = maximum data loss, in time (how far back your last recovery point is; set by capture/replication cadence). RTO = maximum downtime, in time (how long to restore service; set by recovery-mechanism speed). Size both per dataset, independently, from business impact. Durable ≠ recoverable.
• The tier ladder (cost climbs as RPO/RTO shrink). Backup/restore (RPO/RTO hours, $) → pilot light (minutes / tens of minutes, $$) → warm standby (minutes / minutes, $$$) → multi-region active-active (~0 / ~0, $$$$). Pick the cheapest tier that meets both objectives; never gold-plate.
• Threats and their mechanisms. Region outage → cross-region replication + failover. Corrupt load → warehouse Time Travel point-in-time undo. Deletion/ransomware → object versioning + immutable Object Lock. Replication does not fix corruption — it copies it.
• Backups recipe. Snowflake DATA_RETENTION_TIME_IN_DAYS sized to RPO (Time Travel: query AT, CLONE AT, UNDROP); Fail-safe = 7-day provider-only last resort (never design RPO around it). S3 Versioning (delete = reversible marker) + Object Lock COMPLIANCE (immutable) + noncurrent-version lifecycle (bound cost). An untested backup is not a backup — run a verifying restore drill.
• Replication recipe. Snowflake failover group (promotable) replicating databases and roles/warehouses/integrations; REPLICATION_SCHEDULE = your RPO. S3 CRR (versioning-gated) + RTC (15-min SLA) into a separate account (air-gap). Replication is async, so lag = effective RPO → make lag a paged SLO (refresh recency; ReplicationLatency).
• Failover recipe. Fence the old primary (no split-brain) → promote (ALTER FAILOVER GROUP ... PRIMARY) + canary-verify → cut over (low-TTL DNS swap + connection-string rotation) → reconcile (gap = incident − last_replicated; idempotent MERGE replay from a durable source) → measure. Order is fixed; the runbook is scripted and owned.
• Reconciliation. The un-replicated tail is recoverable only from a replayable source (Kafka retention, immutable S3 raw) — append-only raw is the DR backbone. Replay idempotently (dedupe on the event key) so re-runs never double-count; reconcile counts against the source before declaring done.
• Game days. You have not tested DR until you have served from DR under a stopwatch. Quarterly (higher tiers monthly): freeze the objective, run the real runbook, measure achieved RPO/RTO, file gaps as tickets, prove fail-back. The matrix stores the achieved numbers, not the aspirational ones.
• Per-dataset tiering. Build a DR matrix (dataset → RPO → RTO → tier → mechanism → cost). Split RPO from RTO (rebuildable-but-urgent = loose RPO, tight RTO). Exploit rebuildability: replicate source + code and recompute, instead of replicating derived data hot. Reuse shared mechanisms (one failover group, one CRR) tuned per dataset.
• Marginal tier decision. Upgrade a rung only while the downtime/loss cost it saves exceeds the always-on cost it adds. Active-active's ~0 RPO/RTO is worth its full double-capacity cost only for existential, zero-loss data.
• Business continuity. DR is not just data restore: name who declares a disaster, the comms plan, acceptable degraded service, a runbook owner per tier, and an exec sponsor. A blueprint nobody owns or drills is a document, not a capability.

Frequently asked questions

What is disaster recovery for a data platform?

Disaster recovery is the practice of guaranteeing that after a destructive event — a region outage, a corrupt load, an accidental or malicious deletion, a dependency failure — you can restore your warehouse, lake, and pipelines to service within a bounded downtime, having lost at most a bounded amount of data. The crucial distinction is that durability (the cloud keeping many copies of your bytes) is not recoverability: durable data inside a failed region is unreachable, and a corrupt load is durably replicated everywhere. DR therefore layers mechanisms — cross-region replication for the region case, point-in-time backups (Time Travel, object versioning) for the corruption case, and immutable retention for the deletion/ransomware case — behind two measured objectives, RPO and RTO, sized per dataset. Done well, it is a budgeted, owned, and regularly tested program, not a backup checkbox.

What is the difference between RPO and RTO?

RPO (Recovery Point Objective) is the maximum acceptable data loss, expressed in time: an RPO of 15 minutes means that after any disaster you must recover to a state no more than 15 minutes old, and it is set by how often you capture a recovery point — your backup cadence or, for replication, the replication lag. RTO (Recovery Time Objective) is the maximum acceptable downtime: an RTO of 30 minutes means service must be back within 30 minutes, and it is set by how fast your recovery mechanism can promote, cut over, and reconcile. They are independent: a nightly backup gives a fast restore (small RTO) but up to a day of loss (large RPO), while a slow-to-promote replica gives near-zero loss (small RPO) but a slow recovery (large RTO). You size them separately, per dataset, because shrinking RPO costs capture frequency while shrinking RTO costs standby readiness.

Which DR tier should I choose — backup/restore, pilot light, warm standby, or active-active?

Choose the cheapest tier that meets the dataset's RPO and RTO, because cost climbs steeply as those objectives shrink. Backup/restore (cross-region snapshots; RPO/RTO in hours) is right for replayable, rebuildable, or low-impact data — the cheapest rung. Pilot light (core data replicated to a dormant DR environment, compute started on failover; RPO in minutes, RTO in tens of minutes) suits important data that can tolerate a short startup. Warm standby (a scaled-down live copy running continuously; RPO and RTO in minutes) suits data that must be back fast. Multi-region active-active (full capacity everywhere, ~0 RPO/RTO) is reserved for existential, zero-loss, always-on data because it costs full duplicate capacity. Decide by marginal analysis — upgrade a rung only while the downtime and data-loss cost it saves exceeds the always-on cost it adds — and tier each dataset separately rather than applying one blanket policy.

Are backups and replication the same thing?

No, and conflating them is the most common DR mistake. Replication continuously copies your current data to another region, which protects against a region outage — you promote the replica and keep serving. But replication is faithful: a corrupt load or an accidental DELETE is copied to the replica within minutes, so promoting the secondary just gives you the same bad data in another region. Backups — warehouse Time Travel, object versioning, snapshots — are a point-in-time undo that protects against corruption and deletion by letting you recover an earlier state. You need both: replication for the region case (RPO = replication lag) and backups for the logical-damage case (RPO = your Time Travel/version window). Add immutability (Object Lock) so ransomware or a compromised credential cannot destroy the backups themselves, and keep the backups on a different blast radius (cross-account, cross-region) so they survive what took out the primary.

How do I actually test disaster recovery?

With a game day: a scheduled, timed drill where you actually fence the primary, promote the secondary, cut over DNS and connection strings, reconcile the un-replicated gap, and serve real reads from the DR region — while a stopwatch runs. The deliverable is two measured numbers: the achieved RTO (time from incident to serving) and the achieved RPO (the reconciliation gap), compared against your objectives. You have not tested DR until you have served from the DR region under a clock, because the numbers on an architecture diagram are aspirations and the numbers from a drill are facts. Run it quarterly (monthly for the highest tiers), file every gap you find — a too-high DNS TTL, a missing DR role, an un-replayable source — as a ticket, and prove fail-back by promoting the original region back in a controlled window. Also run a lighter-weight restore drill weekly that clones a point-in-time state into an isolated schema and verifies row counts and checksums, so a silently broken backup surfaces in a test rather than a disaster.

How do I set RPO and RTO per dataset?

Start from a business impact analysis: for each dataset, ask what an hour of unavailability costs and what losing an hour of its data costs. Revenue, compliance, and safety-critical data (a billing ledger) have very low tolerance and earn tight objectives and higher tiers; replayable or rebuildable data (raw clickstream that streams from an immutable source, marts recomputable from raw) has high tolerance and earns loose objectives and cheap tiers. Size RPO and RTO independently, because they trade differently — rebuildable-but-operationally-urgent data (dashboards) wants a loose RPO but a tight RTO, while a compliance archive wants a tight RPO but tolerates a loose RTO. Exploit rebuildability to lower cost: if a dataset deterministically recomputes from a retained raw source, replicate the source and the code and recompute on failover instead of replicating the derived data hot. Record every dataset's numbers, tier, mechanism, and cost in a DR matrix — that matrix, sized from impact and validated by drills, is the artifact you defend to leadership and drive the implementation from.

Practice on PipeCode

• Drill the system design practice library → for the DR-tiering, replication-topology, failover-runbook, and business-continuity trade-offs a recoverable data platform has to get right.
• Rehearse recovery-grade pipelines on the data pipeline practice library → for the cross-region replication, replayable-source, and reconciliation scenarios where RPO and RTO are earned or lost.
• Harden your load paths on the ETL practice library → for the idempotent-load, safe-overwrite, and replay patterns that make a failover gap recoverable instead of permanent.
• Stack the prerequisites against PipeCode's broader 450+ data-engineering catalogue to anchor the RPO/RTO, backup, replication, and failover patterns against real graded inputs — snapshots, immutability, lag SLOs, and tested recovery.

Lock in disaster-recovery muscle memory

Docs explain Time Travel, failover groups, and S3 replication. PipeCode drills explain the decision — when replication is the wrong tool for a `corrupt load`, when `RPO` equals replication lag, when a per-request `restore` beats a full rebuild, and when a dataset earns active-active instead of a nightly snapshot. Pipecode.ai is Leetcode for Data Engineering — disaster-recovery practice tuned for the production trade-offs senior data engineers actually face.

Practice system design problems →
Practice data pipeline problems →