SnowPro Associate: Platform Question 93
Single answer1.5 Describe Snowflake objects and how they fit into the Snowflake hierarchy.A data engineering team is setting up a new Snowflake environment for the SALES department. They want to load CSV files from cloud storage into a raw table, transform the data, and make curated results available to analysts. A junior engineer proposes the following order for creating objects: warehouse, table, schema, database, stage. Which sequence best reflects how these Snowflake objects fit into the hierarchy and should be created to support this workflow?
- A
Create a warehouse, then a database, then a schema, then a stage and tables within the schema
- B
Create a database, then a schema, then create stages and tables in the schema, and use a warehouse separately for compute
- C
Create a schema, then a database inside the schema, then tables and stages, and finally attach a warehouse to the database
- D
Create a stage first, then a warehouse, then a table, and Snowflake will automatically create the required schema and database
Show answer and explanation
Correct answer: B
Explanation
In Snowflake, understanding the distinction between storage hierarchy and compute resources is essential. The logical hierarchy for persistent data objects is: account -> database -> schema -> schema-level objects such as tables, views, stages, file formats, and sequences. A warehouse is a separate compute object used to run SQL, load data, and perform transformations, but it does not belong inside the database/schema hierarchy. In this scenario, the team should first define the database for the SALES department, then create schemas such as RAW and CURATED, and then create stages and tables within those schemas. After that, a warehouse can be used to load and transform the data. This aligns with Snowflake documentation on object hierarchy and the separation of storage objects from virtual warehouses.
- A. Incorrect.
Incorrect. A warehouse provides compute resources but is not part of the database object hierarchy. While a warehouse may be created before other objects in practice, this option implies it belongs in the hierarchy order for organizing data objects, which is misleading. In Snowflake, databases contain schemas, and schemas contain objects such as tables and stages.
- B. Correct.
Correct. This matches Snowflake's logical hierarchy and object model: an account contains databases; databases contain schemas; schemas contain objects such as tables, stages, views, and file formats. Warehouses are independent compute objects used to execute loading and transformation operations, but they do not sit inside a database or schema.
- C. Incorrect.
Incorrect. This reverses the hierarchy. A schema cannot contain a database; instead, a database contains one or more schemas. It is also incorrect to say a warehouse is attached to a database. Warehouses are independent from the storage hierarchy and can be used with different databases and schemas depending on the session context and privileges.
- D. Incorrect.
Incorrect. Snowflake does not automatically create a required schema and database just because a stage or table is created. A named stage is a schema-level object and must exist within a schema in a database. This option reflects a common misconception that object dependencies are auto-resolved.