SnowPro Associate: Platform Question 165
Single answer● Create and drop schemasA data engineering team created a temporary development schema named DEV_TEST in the ANALYTICS database to validate a new ingestion process. The testing is complete, and the team wants to remove the schema and everything inside it in a single step. When the engineer runs DROP SCHEMA ANALYTICS.DEV_TEST;, Snowflake returns an error because the schema is not empty. Which statement should the engineer use to meet the requirement?
- A
DROP SCHEMA ANALYTICS.DEV_TEST CASCADE;
- B
DROP DATABASE ANALYTICS.DEV_TEST;
- C
TRUNCATE SCHEMA ANALYTICS.DEV_TEST;
- D
DROP SCHEMA ANALYTICS.DEV_TEST RESTRICT;
Show answer and explanation
Correct answer: A
Explanation
To remove a non-empty schema in Snowflake, the correct approach is DROP SCHEMA <db_name>.<schema_name> CASCADE. By default, or when using RESTRICT, Snowflake does not allow a schema to be dropped if it still contains objects. CASCADE is specifically intended for this administrative cleanup scenario. This aligns with Snowflake SQL command behavior for dropping containers such as schemas and databases: RESTRICT blocks deletion of non-empty objects, while CASCADE removes dependent or contained objects as part of the drop operation. In practice, engineers should use CASCADE carefully in non-production environments or after confirming that all contained objects can be safely removed.
- A. Correct.
Correct. In Snowflake,
DROP SCHEMA ... CASCADEdrops the schema and all objects contained within it. This is the appropriate command when the schema is not empty and the goal is to remove the schema and its contents in one step. - B. Incorrect.
Incorrect.
DROP DATABASEis used to drop an entire database, not a schema within a database. Also,ANALYTICS.DEV_TESTis a database-and-schema reference, not a valid database name in this context. - C. Incorrect.
Incorrect. Snowflake does not support a
TRUNCATE SCHEMAcommand.TRUNCATEapplies to tables, not schemas. This option reflects a common misconception based on table-level operations. - D. Incorrect.
Incorrect.
RESTRICTprevents a schema from being dropped if it contains objects. Since the engineer already received an error because the schema is not empty, usingRESTRICTwould not solve the problem.