COF-C03 Question 49
Single answerSession and context variablesA data engineering team uses a shared Snowflake worksheet to run ad hoc validation queries against different environments. They want to avoid editing SQL text each time they switch between DEV and PROD. An engineer proposes using session variables so the same query can reference the target database and a date filter set at runtime.
They run the following statements in a single session:
SET target_db = 'ANALYTICS_DEV'; SET cutoff_dt = '2025-01-01';
SELECT COUNT(*) FROM IDENTIFIER($target_db || '.CURATED.ORDERS') WHERE ORDER_DATE >= $cutoff_dt;
Later, another engineer opens a separate session and runs only the SELECT statement, expecting it to use the same values.
Which statement correctly describes Snowflake behavior in this scenario?
- A
The SELECT succeeds in both sessions because session variables persist until explicitly unset at the user level.
- B
The SELECT succeeds in the first session, but the second session must set its own session variables before using $target_db and $cutoff_dt.
- C
The SELECT fails in the first session because session variables cannot be used with IDENTIFIER() to build object names dynamically.
- D
The SELECT succeeds in the second session if both engineers are using the same role and warehouse, because session variables are shared by execution context.
Show answer and explanation
Correct answer: B
Explanation
In Snowflake, session variables are scoped to a single session. They are created with SET and referenced with the $ prefix. If a query needs to use those variables in another session, that session must define them again. Snowflake also allows dynamic object resolution by passing a string or expression to IDENTIFIER(), which is the correct approach when a variable contains part or all of an object name such as a database or table. In this scenario, the first session can successfully query ANALYTICS_DEV.CURATED.ORDERS and apply the date filter because both variables were set in that session. The second engineer's session will fail unless it also sets target_db and cutoff_dt before running the SELECT. This aligns with Snowflake documentation and best practices for session variables and SQL variables, including the use of IDENTIFIER() for dynamic object names and the session-scoped nature of variables.
- A. Incorrect.
Incorrect. Snowflake session variables are scoped to the current session, not to the user across all sessions. They do not automatically persist for later or parallel sessions opened by the same user. A common misconception is to treat them like account-level or user profile settings.
- B. Correct.
Correct. Session variables must be defined in each session where they are used. In Snowflake, variables referenced as $variable_name are session-scoped. Using IDENTIFIER() with a string expression is a valid way to resolve an object name dynamically, so the first session can succeed after setting the variables, but a separate session must issue its own SET commands.
- C. Incorrect.
Incorrect. Snowflake supports using session variables together with IDENTIFIER() to reference object names dynamically. This is a common pattern when parameterizing database, schema, table, or other object references in SQL. The failure point in the scenario is not IDENTIFIER(), but the assumption that another session can reuse variables that were never set there.
- D. Incorrect.
Incorrect. Role, warehouse, database, and schema are session context settings, but session variables are still isolated to each individual session. Sharing the same role or warehouse does not make variables visible across sessions. This option reflects confusion between execution context and variable scope.