ARA-C01 Question 204
Single answer2.4 Given a scenario, outline how objects exist within the Snowflake object hierarchy and how the hierarchy impacts an architecture.A global company is redesigning its Snowflake environment after several incidents caused by developers creating objects in the wrong place. The architecture team wants to separate production and development data, simplify privilege management, and ensure that dropping a parent object cannot accidentally remove unrelated child objects. The team is considering whether to organize environments as separate databases or as schemas within a single database. Which design best aligns with Snowflake's object hierarchy and reduces operational risk?
- A
Create one database for each environment (for example, SALES_PROD and SALES_DEV), and place related schemas such as RAW, CURATED, and MART inside each database
- B
Create a single SALES database, and represent each environment as a schema such as PROD, DEV, and TEST under that database
- C
Create separate warehouses for PROD and DEV, and use warehouse-level isolation to prevent accidental object deletion across environments
- D
Create separate roles for PROD and DEV, but keep all tables in the PUBLIC schema because role separation is sufficient to isolate object hierarchy risks
Show answer and explanation
Correct answer: A
Explanation
Snowflake's logical object hierarchy is an important architectural consideration: an account contains databases; databases contain schemas; schemas contain objects such as tables, views, and stages. Parent-child relationships matter operationally because actions on parent objects affect their children. For example, dropping a database drops its schemas and their contained objects; dropping a schema drops the objects within that schema. For environment isolation, separate databases are often a better boundary than separate schemas when the goal is to reduce operational risk and simplify administration. Warehouses are separate compute objects and do not define storage containment. Roles govern privileges, but they do not change object hierarchy. This design principle is consistent with Snowflake documentation on databases, schemas, object hierarchy, and access control best practices.
- A. Correct.
Correct. In Snowflake, databases contain schemas, and schemas contain database objects such as tables, views, and stages. Using separate databases for PROD and DEV aligns with the object hierarchy and creates a cleaner administrative boundary. This helps with privilege delegation, replication/failover planning, data sharing design, and lifecycle operations. It also reduces the blast radius of database-level actions because dropping a database removes its schemas and the objects within them, so isolating environments into separate databases prevents one environment's database-level operation from affecting another.
- B. Incorrect.
Incorrect. Although this pattern is sometimes used, placing PROD and DEV as schemas in the same database weakens the environment boundary. Because schemas are children of a database, database-level actions such as DROP DATABASE affect all schemas within that database. This increases operational risk when environments must be strongly separated. It also complicates certain architecture decisions where the database is the intended boundary for administration, replication, and organization.
- C. Incorrect.
Incorrect. Warehouses are compute resources and are not part of the database object containment hierarchy for tables, views, and schemas. Separating warehouses can isolate compute usage and performance, but it does not change where objects exist or how parent-child object deletion behaves. A DROP DATABASE or DROP SCHEMA issue is unrelated to warehouse design.
- D. Incorrect.
Incorrect. Roles control access, not containment. Keeping all objects in PUBLIC does not provide strong structural separation between environments. PUBLIC is simply a schema, and using it for both PROD and DEV increases the chance of naming conflicts, deployment mistakes, and accidental impact from schema-level operations. Role-based access control is important, but it does not replace sound use of the Snowflake object hierarchy.