SnowPro Associate: Platform Question 168
Single answer● Create and drop schemasA data engineering team is decommissioning a project-specific schema named ANALYTICS_STAGE in the PROD_DB database. The schema currently contains several tables and views created during testing. The team wants to remove the schema in a single step without manually dropping each object first. Which SQL statement will accomplish this?
- A
DROP SCHEMA PROD_DB.ANALYTICS_STAGE;
- B
DROP SCHEMA PROD_DB.ANALYTICS_STAGE CASCADE;
- C
DROP DATABASE PROD_DB.ANALYTICS_STAGE CASCADE;
- D
REMOVE SCHEMA PROD_DB.ANALYTICS_STAGE CASCADE;
Show answer and explanation
Correct answer: B
Explanation
Snowflake supports creating and dropping schemas with standard DDL commands such as CREATE SCHEMA and DROP SCHEMA. When dropping a schema, RESTRICT behavior applies by default, meaning the schema must be empty. If the schema contains objects, the CASCADE keyword is required to drop the schema and its contained objects in one operation. In this scenario, because ANALYTICS_STAGE contains tables and views, the correct statement is DROP SCHEMA PROD_DB.ANALYTICS_STAGE CASCADE;. This aligns with Snowflake documentation and common administrative practice when decommissioning project-specific environments.
- A. Incorrect.
Incorrect. In Snowflake, DROP SCHEMA without CASCADE succeeds only if the schema is empty. Because the scenario states that the schema contains tables and views, this statement would fail unless those objects were removed first.
- B. Correct.
Correct. In Snowflake, DROP SCHEMA ... CASCADE drops the schema and all objects contained within it. This is the appropriate command when a non-empty schema must be removed in a single step.
- C. Incorrect.
Incorrect. PROD_DB is the database, and ANALYTICS_STAGE is the schema. DROP DATABASE is used to remove an entire database, not a schema within a database. This option reflects confusion between database and schema object hierarchy.
- D. Incorrect.
Incorrect. REMOVE SCHEMA is not valid Snowflake SQL syntax for deleting a schema. The correct command is DROP SCHEMA. This distractor targets candidates who know the intent but not the precise SQL command.