SnowPro Associate: Platform Question 104
Single answer● SchemasA data engineering team is reorganizing objects in a Snowflake database. They created a schema named ANALYTICS in the SALES database and loaded several permanent tables into it. Later, they realize the schema was created with the wrong owner role, and they also want to avoid dropping the schema because the contained tables must be preserved. Which action should they take to address the ownership issue while keeping the schema and its objects in place?
- A
Use ALTER SCHEMA SALES.ANALYTICS RENAME TO new_schema_name to change the schema owner without affecting contained objects.
- B
Use GRANT OWNERSHIP ON SCHEMA SALES.ANALYTICS TO ROLE <new_role> to transfer ownership of the schema.
- C
Drop the schema with CASCADE and recreate it under the correct role; the permanent tables will remain in Time Travel and automatically reconnect to the recreated schema.
- D
Clone the schema to a new schema owned by the correct role, then the original schema ownership will automatically update to match the clone.
Show answer and explanation
Correct answer: B
Explanation
The correct solution is to transfer ownership with GRANT OWNERSHIP ON SCHEMA. In Snowflake, OWNERSHIP is a special privilege that controls the object and can be transferred to another role. This is the standard method for correcting which role owns an existing schema without dropping or recreating it. RENAME only changes object names, and CLONE creates a new object rather than modifying the original. Dropping and recreating a schema is unnecessary and risky when the goal is simply to correct administrative control. This aligns with Snowflake documentation on access control and object privileges, where OWNERSHIP transfer is the supported way to reassign control of schemas and other securable objects.
- A. Incorrect.
Incorrect. RENAME changes only the schema name, not its owner. This is a common misconception because ALTER commands can modify many object properties, but ownership changes in Snowflake are handled with GRANT OWNERSHIP, not RENAME.
- B. Correct.
Correct. In Snowflake, ownership is a privilege, and transferring it is done with GRANT OWNERSHIP ON SCHEMA. This changes the owning role without requiring the schema to be dropped, allowing the schema and its contained objects to remain in place. This is the appropriate administrative action when the schema was created under the wrong role.
- C. Incorrect.
Incorrect. Dropping a schema with CASCADE removes the schema and its objects. While dropped objects may be recoverable during retention periods using Time Travel, they do not automatically reconnect to a newly recreated schema. This approach is disruptive and unnecessary for simply correcting schema ownership.
- D. Incorrect.
Incorrect. Cloning creates a separate copy of the schema and its objects at a point in time, but it does not change ownership of the original schema. The original and cloned schemas are distinct objects, and cloning is not the mechanism for fixing ownership on an existing schema.