SnowPro Associate: Platform Question 158
Single answer● Understand contextA data engineering team uses a shared Snowflake worksheet to run deployment scripts in a DEV environment. A script creates a table with an unqualified name and then loads data into it:
CREATE TABLE customer_stage (id NUMBER, name STRING); INSERT INTO customer_stage VALUES (1, 'Ava');
One engineer reports that the table was created in an unexpected schema, causing downstream queries to fail. The team wants to prevent this issue by understanding how Snowflake resolves object names during a session. Which action would best ensure that unqualified object names are created and referenced in the intended location before running the script?
- A
Run USE WAREHOUSE to select the correct virtual warehouse before executing the script
- B
Run USE DATABASE and USE SCHEMA to explicitly set the current database and schema for the session before executing the script
- C
Grant the engineer OWNERSHIP on the target schema so Snowflake automatically creates objects there
- D
Fully qualify only the INSERT statement, because CREATE TABLE uses the user's default schema automatically
Show answer and explanation
Correct answer: B
Explanation
This question tests understanding of Snowflake session context. In Snowflake, context functions such as current database, current schema, current role, and current warehouse influence how statements execute. For object name resolution, the key context settings are the current database and current schema. If a table name is not fully qualified as database.schema.object, Snowflake resolves it using those session settings. Best practice for deployment scripts is either to explicitly set context at the start with USE DATABASE and USE SCHEMA, or to fully qualify object names throughout the script. The warehouse selection affects compute only, while privileges affect authorization only. See Snowflake documentation on session context and object name resolution, including USE DATABASE, USE SCHEMA, and qualified versus unqualified object references.
- A. Incorrect.
Incorrect. The current warehouse controls which compute resources execute the query, not where unqualified database objects are created or resolved. Selecting the correct warehouse is important for performance and cost, but it does not determine the current database or schema context.
- B. Correct.
Correct. In Snowflake, unqualified object names are resolved based on the session context, specifically the current database and current schema. Running USE DATABASE and USE SCHEMA before executing the script ensures that statements like CREATE TABLE customer_stage and subsequent DML reference the intended location.
- C. Incorrect.
Incorrect. Privileges such as OWNERSHIP affect what a user is allowed to do, not how Snowflake resolves unqualified object names. Even with sufficient privileges, an object can still be created in the wrong schema if the session context is set incorrectly.
- D. Incorrect.
Incorrect. CREATE TABLE does not automatically use a special user default schema independent of session context. Both CREATE TABLE and INSERT statements that use unqualified object names rely on the current database and schema. Qualifying only the INSERT would not prevent the table from being created in an unintended schema.