SnowPro Associate: Platform Question 103
Single answer● SchemasA data engineering team maintains a database named ANALYTICS with a schema named STAGING that contains several transient tables used for short-term processing. To reduce risk, the team wants to prevent developers from accidentally dropping the STAGING schema while still allowing them to create and replace objects inside it during deployments. Which action should the Snowflake administrator take?
- A
Create the schema as a managed access schema.
- B
Create the schema with WITH MANAGED ACCESS and DATA_RETENTION_TIME_IN_DAYS = 0.
- C
Create the schema with the OBJECT_VISIBILITY property set to PRIVATE.
- D
Create the schema with the TRANSIENT property enabled.
Show answer and explanation
Correct answer: A
Explanation
In Snowflake, schemas are logical containers for database objects, and administrators commonly use schema properties to support governance and workload patterns. A managed access schema is the key schema feature for tighter administrative control because privilege management is performed by the schema owner or a role with MANAGE GRANTS, rather than by individual object owners. This supports controlled deployment processes and reduces the chance of unintended privilege sprawl.
There is no schema property whose purpose is to make a schema impossible to drop. Preventing accidental drops is primarily addressed through role design, least-privilege access, and ownership control. Transient schemas are useful for staging or temporary data because they are intended for objects that do not require the same level of data protection as permanent objects, but transient does not provide drop protection. Similarly, data retention settings affect Time Travel, not schema deletion permissions.
This aligns with Snowflake documentation on CREATE SCHEMA, managed access schemas, transient schemas, and access control best practices.
- A. Correct.
Correct. A managed access schema centralizes privilege management so that object owners do not automatically control grant decisions on objects within the schema; only the schema owner or a role with MANAGE GRANTS can manage object privileges. This is useful when an administrator wants tighter control over a schema and its objects during team deployments. While this does not literally make a schema undroppable, it is the valid schema-level governance feature among the options and best aligns with reducing accidental administrative actions through controlled access patterns.
- B. Incorrect.
Incorrect. WITH MANAGED ACCESS is a valid schema property, but DATA_RETENTION_TIME_IN_DAYS = 0 does not prevent a schema from being dropped. Retention affects Time Travel availability for supported objects, not whether the schema itself can be deleted. A candidate might choose this option because transient objects are often paired with lower retention for cost control, but that does not address accidental schema drops.
- C. Incorrect.
Incorrect. OBJECT_VISIBILITY is not a standard schema property used to protect schemas from being dropped in Snowflake. This distractor targets the misconception that there is a schema setting similar to visibility controls in other platforms. Snowflake schema management focuses on ownership, privileges, and schema types such as managed access or transient.
- D. Incorrect.
Incorrect. A transient schema reduces data protection costs by limiting Fail-safe for objects created in it, but it does not prevent the schema from being dropped. This is a common misconception because transient schemas are often used for temporary or staging workloads, yet their purpose is storage lifecycle and recovery behavior, not drop protection.