SnowPro Associate: Platform Question 167
Single answer● Create and drop schemasA data engineering team created a temporary working area in the ANALYTICS database called STAGE_WORK. After the project ended, an administrator needs to remove the schema. When attempting to drop it, Snowflake returns an error because the schema still contains several tables and views created during testing. The administrator wants to remove the schema and everything inside it in a single statement. Which command should the administrator use?
- A
DROP SCHEMA ANALYTICS.STAGE_WORK;
- B
DROP SCHEMA ANALYTICS.STAGE_WORK CASCADE;
- C
DELETE SCHEMA ANALYTICS.STAGE_WORK CASCADE;
- D
TRUNCATE SCHEMA ANALYTICS.STAGE_WORK;
Show answer and explanation
Correct answer: B
Explanation
This question tests practical knowledge of creating and dropping schemas in Snowflake. A schema is a logical container for database objects such as tables and views. In Snowflake, DROP SCHEMA removes a schema only if it is empty, while DROP SCHEMA ... CASCADE removes the schema along with its contained objects. This is a common administrative task when cleaning up development or temporary environments. Best practice is to use CASCADE carefully in production because it deletes dependent objects within the schema. This aligns with Snowflake SQL command behavior documented for DROP SCHEMA.
- A. Incorrect.
This is incorrect because DROP SCHEMA without additional clauses succeeds only when the schema is empty. In the scenario, the schema still contains tables and views, so Snowflake returns an error instead of removing it.
- B. Correct.
This is correct. In Snowflake, DROP SCHEMA ... CASCADE removes the schema and all objects contained within it. This is the appropriate command when the goal is to delete a non-empty schema in one step.
- C. Incorrect.
This is incorrect because DELETE SCHEMA is not valid Snowflake SQL syntax. A candidate might choose this option by confusing DML commands such as DELETE with DDL commands used to manage database objects.
- D. Incorrect.
This is incorrect because TRUNCATE SCHEMA is not a valid Snowflake command. TRUNCATE applies to certain object types such as tables, where it removes rows but does not drop the object itself. It cannot be used to remove a schema.