ADA-C01 Question 547
Single answerAnalyze the impact of database failover and fallback on schemasA company uses Snowflake database replication and failover groups to support disaster recovery between AWS us-east-1 (primary) and AWS us-west-2 (secondary). The database SALES_DB contains two schemas: CORE and STAGE. CORE is a regular permanent schema that stores production tables. STAGE is a transient schema used for short-lived ingestion tables. During a regional outage, administrators promote the secondary database and direct applications to the failed-over account. After failover, an engineer asks how the schemas in SALES_DB are affected and what to expect if the company later fails back to the original primary account.
Which statement best describes the impact of failover and fallback on these schemas?
- A
Both CORE and STAGE schemas can be present after failover if they were included in database replication, but only replicated data and metadata that existed at the last successful refresh are available on the promoted secondary.
- B
Only permanent schemas such as CORE are supported in replicated failover databases; transient schemas such as STAGE are skipped during replication and must be recreated manually after failover.
- C
Schema-level failover changes a schema from transient to permanent on the secondary so that fallback can preserve historical data during failback.
- D
After failover, Snowflake automatically merges any schema changes made independently in both regions during the outage when the original primary is restored.
Show answer and explanation
Correct answer: A
Explanation
The main administrative consideration is that database failover operates on replicated state, so schemas in the promoted secondary are available as of the last successful replication refresh. In this scenario, both the permanent schema (CORE) and the transient schema (STAGE) can exist in the failed-over database if they were part of the replicated database and their objects are supported by replication. Failover does not transform schema types or automatically reconcile divergent changes made in multiple regions.
This aligns with Snowflake documentation on replication and failover groups: secondary databases are read-only until promotion, and after promotion they become writable and represent the latest replicated state. Administrators should also remember that transient versus permanent affects data protection features such as Fail-safe and retention characteristics, but not in the way suggested by the incorrect answers. Best practice is to design DR procedures with clear RPO/RTO expectations, understand replication lag, and avoid assuming automatic conflict resolution during failback.
- A. Correct.
Correct. In Snowflake replication/failover, supported objects in the database are replicated to the secondary, including schemas and their supported child objects, regardless of whether the schema is permanent or transient. After a failover, the promoted secondary reflects the state from the most recent successful replication/refresh, not an instantaneous bidirectional synchronization point. This is the key operational impact on schemas: the schema definitions and supported objects are available only up to the last replicated point in time.
- B. Incorrect.
Incorrect. This reflects a common misconception that transient schemas are excluded from replication because they have reduced data protection characteristics such as no Fail-safe. However, transient schemas are still schemas within the replicated database and are not automatically omitted solely because they are transient. The important distinction is retention/protection behavior, not replication eligibility in this way.
- C. Incorrect.
Incorrect. Failover does not change the schema type. A transient schema remains transient, and a permanent schema remains permanent. Snowflake does not convert object types during replication, failover, or fallback. Also, 'fallback' in account/database disaster recovery does not mean Snowflake adds historical preservation semantics to transient objects.
- D. Incorrect.
Incorrect. Snowflake failover/failback is not a multi-master conflict-resolution system. If independent writes occur in different places, administrators must plan carefully; Snowflake does not automatically merge divergent schema changes across regions after an outage. Replication is directional, and failback requires re-establishing replication and understanding which side is the authoritative source.