ADA-C01 Question 546
Select 2Analyze the impact of database failover and fallback on schemasA global company uses Snowflake database replication and failover groups between AWS us-east-1 (primary) and AWS us-west-2 (secondary). The PROD database contains three schemas: SALES, HR, and STAGE_ONLY. SALES and HR contain permanent and transient tables. STAGE_ONLY contains transient tables and external stages used only for local ingestion. During a regional outage, the administrator promotes the secondary account and plans to run reporting workloads there. After failover, users report that only SALES and HR are available in the promoted database, while STAGE_ONLY is missing. The administrator must explain this behavior and decide how to design the environment going forward. Which TWO statements are correct?
- A
Schemas are not independently failed over; failover occurs at the database level, so only objects eligible for replication within the failed-over database appear on the secondary after promotion.
- B
A schema that contains transient tables can be excluded from the replicated database state, so STAGE_ONLY may be absent after failover if its objects are not supported for replication.
- C
Because failover groups replicate account objects only, database schemas must be recreated manually in the secondary region after every failover.
- D
External stages are not replicated as usable data-bearing objects across regions, so a schema designed primarily for local ingestion dependencies may need a separate post-failover rebuild process.
- E
Fallback automatically reconstructs missing schemas from Time Travel metadata in the secondary account, even if those schemas were not replicated before failover.
Show answer and explanation
Correct answers: A, D
Explanation
The key concept is that Snowflake failover operates on replicated databases and supported objects, not on schemas as standalone failover units. When a secondary is promoted, the resulting database reflects the replicated database state. Therefore, administrators must analyze schema contents to determine whether those schemas will be useful after failover. Transient tables do not by themselves prevent replication or failover; transient means reduced data protection compared with permanent tables, especially around Fail-safe, not exclusion from replication. By contrast, schemas centered on local ingestion dependencies, especially external stages and related integrations, may not be fully usable in the failover region without additional setup. In practice, Snowflake documentation on replication and failover emphasizes reviewing supported objects and understanding regional dependencies for stages, integrations, and data-loading designs. A strong DR design separates failover-ready analytical schemas from ingestion-specific schemas when necessary and documents post-failover remediation steps for non-portable components.
- A. Correct.
Correct. In Snowflake, database failover is performed by replicating and promoting databases through replication/failover groups; schemas are not promoted individually as independent failover units. The practical implication is that after failover you see the database state that was replicated, and only supported replicated objects within that database are available. This is why administrators evaluate schema contents when planning DR rather than assuming schema-level failover controls exist.
- B. Incorrect.
Incorrect. Transient tables themselves are supported in replicated databases; a schema does not become ineligible for failover simply because it contains transient tables. A common misconception is to treat 'transient' as 'not replicated,' but transient affects data protection behavior such as Fail-safe, not whether the schema can exist in a replicated database. If STAGE_ONLY is missing or not usable as expected, the issue is more likely related to unsupported or region-specific dependencies such as stages rather than the transient table type alone.
- C. Incorrect.
Incorrect. Failover groups can include databases and certain account objects, and replicated databases bring their schemas with them. Schemas do not need to be recreated manually after every failover when the database itself is properly replicated. This option reflects the misconception that only account-level metadata is moved and that database structures are excluded, which is not how Snowflake replication/failover is designed.
- D. Correct.
Correct. External stages reference cloud storage locations and related integration/dependency context that may be region-specific or require separate validation/recreation in a DR region. Even when database metadata is replicated, ingestion-oriented schemas that rely on external connectivity or non-replicated dependencies often need additional operational steps after failover. Designing such schemas separately, or documenting post-failover rebuild tasks, is a common best practice.
- E. Incorrect.
Incorrect. Snowflake does not use 'fallback' in the way some other platforms do, and Time Travel does not create schemas in a secondary account that were never part of the replicated state. Time Travel applies within the context of existing objects and retention policies; it is not a disaster recovery mechanism for reconstructing non-replicated schemas after cross-region failover. This option mixes unrelated concepts and represents a common terminology confusion.