COF-C03 Question 19
Single answer1.3 Differentiate Snowflake object hierarchy and typesA data engineering team is setting up a new Snowflake environment for a sales analytics application. They want to create a table named DAILY_ORDERS in a new schema called REPORTING under an existing database named SALES_DB. A junior engineer attempts to run CREATE TABLE SALES_DB.DAILY_ORDERS (...); and gets an error because the object path is incomplete. Which statement correctly describes the Snowflake object hierarchy and how the table should be created?
- A
A table is created directly under an account, so CREATE TABLE SALES_DB.DAILY_ORDERS is valid if SALES_DB exists.
- B
A table belongs to a warehouse, so the correct object path must include the warehouse name before the database and schema.
- C
A table belongs to a schema within a database, so the correct fully qualified name is SALES_DB.REPORTING.DAILY_ORDERS.
- D
A schema is created inside a warehouse, so the engineer must create REPORTING inside the active warehouse before creating the table.
Show answer and explanation
Correct answer: C
Explanation
Snowflake separates compute resources from logical data object organization. At a high level, an account contains databases; databases contain schemas; and schemas contain schema-level objects such as tables, views, and stages. Warehouses are independent compute objects used to run SQL statements, but they are not containers for tables or schemas. In this scenario, the engineer omitted the schema portion of the object name. To create the table in the intended location, the object should be referenced as SALES_DB.REPORTING.DAILY_ORDERS, or the session should first set the current database and schema with USE DATABASE and USE SCHEMA. This aligns with Snowflake documentation on object hierarchy and fully qualified object naming.
- A. Incorrect.
Incorrect. In Snowflake, a table is not created directly under the account level. The account contains databases, and databases contain schemas, which then contain schema-level objects such as tables and views. SALES_DB.DAILY_ORDERS is missing the schema name, so it is not a complete fully qualified name unless the schema is provided separately through session context.
- B. Incorrect.
Incorrect. Warehouses are compute resources used to execute queries and DML/DDL operations, but they are not part of the logical container hierarchy for storing tables. Tables are not owned by or nested inside warehouses. This distractor reflects a common misconception that compute and storage objects share the same hierarchy.
- C. Correct.
Correct. In Snowflake's object hierarchy, databases contain schemas, and schemas contain tables. Therefore, a fully qualified table name requires database.schema.object. If the team wants the table in the REPORTING schema of the SALES_DB database, the correct name is SALES_DB.REPORTING.DAILY_ORDERS.
- D. Incorrect.
Incorrect. Schemas are created inside databases, not inside warehouses. Warehouses provide compute for processing workloads, while schemas are logical namespaces for organizing database objects. This option incorrectly mixes Snowflake's compute layer with its storage and metadata hierarchy.