COF-C03 Question 28
Single answerSchemasA data engineering team is reorganizing a Snowflake database used by several analytics applications. They need to move an existing schema named STAGE from the SALES_DB database into a new database named RAW_DB. The team wants to preserve all objects in the schema, minimize application changes, and complete the move with a single SQL statement if possible. Which approach should they use?
- A
Use ALTER SCHEMA SALES_DB.STAGE RENAME TO RAW_DB.STAGE;
- B
Use CREATE SCHEMA RAW_DB.STAGE CLONE SALES_DB.STAGE; then drop the original schema
- C
Use ALTER SCHEMA SALES_DB.STAGE SWAP WITH RAW_DB.STAGE;
- D
Snowflake does not support moving a schema to a different database with a single rename operation; the team should recreate or clone the schema in RAW_DB and then repoint applications as needed
Show answer and explanation
Correct answer: D
Explanation
Snowflake's object hierarchy places schemas inside databases, and a schema cannot simply be reassigned to another database with ALTER SCHEMA ... RENAME TO. Renaming changes the schema name within its current database, not its parent database. In real-world migrations, teams commonly use CREATE SCHEMA ... CLONE to quickly duplicate a schema into another database with zero-copy cloning, then validate grants, dependent objects, and application references before retiring the original. This is an important distinction for SnowPro Core candidates: object names can often be changed, but container relationships in the hierarchy are more constrained. See Snowflake documentation for ALTER SCHEMA, CREATE ... CLONE, and the database/schema object hierarchy.
- A. Incorrect.
Incorrect. In Snowflake, ALTER SCHEMA ... RENAME TO can rename a schema, but it does not move the schema from one database to another. A schema belongs to a single database, and changing the database container is not accomplished by a rename statement.
- B. Incorrect.
Incorrect. Cloning the schema into RAW_DB is a valid way to create a copy of the schema and its contained objects with minimal storage impact, but this is not a true move. It also requires additional steps such as validating object references, permissions, and eventually dropping the source if desired. Because the question asks for the correct approach given Snowflake's capabilities, this option is incomplete as stated.
- C. Incorrect.
Incorrect. SWAP is supported for certain object types such as tables, but not as a mechanism to move a schema between databases. This option reflects a common misconception that all Snowflake objects support swap-style relocation.
- D. Correct.
Correct. In Snowflake, a schema is contained within a database and cannot be moved to another database using a single rename command. If a team needs the schema under a different database, they typically recreate it or use zero-copy cloning in the target database, then update references and permissions as needed. This aligns with Snowflake object hierarchy behavior and practical migration patterns.