SnowPro Associate: Platform Question 160
Single answer● Understand contextA data engineering team uses a shared Snowflake worksheet to troubleshoot issues in a pipeline. The worksheet contains the following statements in order:
- USE ROLE SYSADMIN;
- USE WAREHOUSE ETL_WH;
- USE DATABASE RAW;
- USE SCHEMA INGEST;
- SELECT CURRENT_ROLE(), CURRENT_WAREHOUSE(), CURRENT_DATABASE(), CURRENT_SCHEMA();
Later, an engineer manually runs the following statement in the same session before re-running statement 5:
USE SCHEMA ANALYTICS.REPORTING;
What will statement 5 return after this change?
- A
SYSADMIN, ETL_WH, RAW, REPORTING
- B
SYSADMIN, ETL_WH, ANALYTICS, REPORTING
- C
SYSADMIN, NULL, ANALYTICS, REPORTING
- D
PUBLIC, ETL_WH, ANALYTICS, REPORTING
Show answer and explanation
Correct answer: B
Explanation
This question tests understanding of Snowflake session context. Snowflake maintains separate current context values for role, warehouse, database, and schema. Commands such as USE ROLE, USE WAREHOUSE, USE DATABASE, and USE SCHEMA change the current session context for subsequent statements. A key behavior is that when USE SCHEMA is executed with a fully qualified name in the form database.schema, Snowflake updates both the current schema and the current database to match that object reference. Previously set role and warehouse values remain unchanged until explicitly modified. This aligns with Snowflake documentation on session context and context functions such as CURRENT_ROLE, CURRENT_WAREHOUSE, CURRENT_DATABASE, and CURRENT_SCHEMA. Best practice in shared worksheets and scripts is to set context explicitly to avoid confusion caused by session state changes between statements.
- A. Incorrect.
Incorrect. When a fully qualified schema is set with USE SCHEMA ANALYTICS.REPORTING, both the current database and current schema context are updated. The schema becomes REPORTING and the database becomes ANALYTICS, not RAW. A common misconception is that USE SCHEMA changes only the schema name and leaves the current database unchanged.
- B. Correct.
Correct. In Snowflake, context includes the current role, warehouse, database, and schema. After USE ROLE SYSADMIN and USE WAREHOUSE ETL_WH, those values remain in effect unless explicitly changed. When the engineer runs USE SCHEMA ANALYTICS.REPORTING, Snowflake sets the current schema to REPORTING and also sets the current database to ANALYTICS because the schema was referenced with a database-qualified name.
- C. Incorrect.
Incorrect. The warehouse context does not reset when the schema context changes. Since ETL_WH was explicitly set earlier and no later USE WAREHOUSE statement changed it, CURRENT_WAREHOUSE() still returns ETL_WH. This distractor reflects the mistaken belief that changing database or schema context clears warehouse selection.
- D. Incorrect.
Incorrect. The current role remains SYSADMIN because no subsequent USE ROLE statement changed it. Also, changing schema to ANALYTICS.REPORTING does not implicitly switch the role to PUBLIC. PUBLIC is a role in Snowflake, but it is not automatically activated by changing object context.