SnowPro Associate: Platform Question 157
Single answer● Understand contextA data engineering team uses a shared Snowflake service account to run deployment scripts. One script creates a temporary staging table and then runs several unqualified SQL statements such as INSERT INTO load_audit and SELECT * FROM customer_stage. In one deployment, the script fails because it writes to the wrong schema after another process changed the session settings for the same connection pool. The team wants to prevent this issue by making the script behave predictably regardless of prior session state. Which action is the BEST way to address this problem?
- A
At the start of the script, explicitly set the session context with USE ROLE, USE WAREHOUSE, USE DATABASE, and USE SCHEMA before running unqualified object references
- B
Grant the service account the ACCOUNTADMIN role so Snowflake can automatically resolve the correct objects across databases and schemas
- C
Convert the temporary staging table to a permanent table so its object location is fixed even if session context changes
- D
Increase the warehouse size so context changes do not affect object resolution during script execution
Show answer and explanation
Correct answer: A
Explanation
This question tests understanding of Snowflake session context. Snowflake uses session-level context for the current role, warehouse, database, and schema. When SQL statements use unqualified object names, Snowflake resolves them based on the current database and schema in that session. In automation, connection pooling, and shared service-account scenarios, relying on inherited session state can cause objects to be created or queried in unintended locations. The best practice is to explicitly establish context at the start of the script with commands such as USE ROLE, USE WAREHOUSE, USE DATABASE, and USE SCHEMA, or to fully qualify object names when appropriate. Snowflake documentation on sessions and object name resolution emphasizes the importance of current context for command execution.
- A. Correct.
Correct. In Snowflake, context such as the current role, warehouse, database, and schema is session-based and affects how unqualified object names are resolved. Explicitly setting these values at the beginning of the script ensures predictable behavior, especially when using shared connections or pooled sessions where prior context may be unknown. This is a common best practice for deployment and automation scripts.
- B. Incorrect.
Incorrect. ACCOUNTADMIN is a highly privileged administrative role, but granting it does not automatically fix object resolution caused by incorrect session context. Unqualified names still resolve based on the current database and schema in the session. This option reflects a common misconception that broader privileges eliminate the need to manage context explicitly.
- C. Incorrect.
Incorrect. Temporary versus permanent table type does not solve the main issue. The problem is not table durability; it is that unqualified references depend on the current database and schema. A permanent table can still be created or referenced in the wrong schema if the session context is not set correctly.
- D. Incorrect.
Incorrect. Warehouse size affects compute capacity and performance, not SQL name resolution or session context. Context settings such as current warehouse, database, schema, and role determine where commands run and how object names are interpreted.