SnowPro Associate: Platform Question 166
Single answer● Create and drop schemasA data engineering team wants to retire a temporary schema named STAGE_LOAD in the SALES database after a migration is complete. The schema still contains several transient tables created during testing. The team wants to remove the schema and everything inside it in a single step, while avoiding errors caused by dependent objects still existing. Which SQL statement should they use?
- A
DROP SCHEMA SALES.STAGE_LOAD;
- B
DROP SCHEMA SALES.STAGE_LOAD CASCADE;
- C
DELETE SCHEMA SALES.STAGE_LOAD CASCADE;
- D
DROP DATABASE SALES.STAGE_LOAD CASCADE;
Show answer and explanation
Correct answer: B
Explanation
In Snowflake, schemas are logical containers inside a database. To create a schema, administrators typically use CREATE SCHEMA <db_name>.<schema_name>. To remove a schema, DROP SCHEMA is used. If the schema contains objects such as tables, views, or stages, a simple DROP SCHEMA will fail unless the schema is empty. Using DROP SCHEMA <db_name>.<schema_name> CASCADE instructs Snowflake to drop the schema and all contained objects. This is the practical choice in cleanup or decommissioning scenarios like test, staging, or migration work. By contrast, RESTRICT behavior prevents dropping a non-empty schema. This aligns with Snowflake SQL command behavior documented for CREATE SCHEMA and DROP SCHEMA.
- A. Incorrect.
Incorrect. DROP SCHEMA without CASCADE succeeds only if the schema is empty. Because the scenario states that the schema still contains transient tables, this statement would fail rather than remove the schema and its objects.
- B. Correct.
Correct. DROP SCHEMA ... CASCADE removes the schema and all objects contained within it, which is the appropriate approach when the schema is not empty and the goal is to delete it in a single step.
- C. Incorrect.
Incorrect. DELETE SCHEMA is not valid Snowflake SQL syntax. DELETE is used for removing rows from tables, not database objects such as schemas.
- D. Incorrect.
Incorrect. DROP DATABASE is used to remove an entire database, not a schema within a database. Also, SALES.STAGE_LOAD is not a valid database name reference in this context.