COF-C03 Question 50
Single answerSession and context variablesA data engineering team runs the same reporting query for many business units. To avoid maintaining separate SQL files, they want to pass the business unit ID into the session at runtime and use it inside the query filter. An engineer opens a worksheet and runs the following commands:
SET bu_id = 'FIN'; SELECT order_id, amount FROM sales.orders WHERE business_unit = $bu_id;
Later, another engineer opens a different session and reports that the same query returns an error because bu_id is not defined. Which statement best explains this behavior and the correct way to use the variable?
- A
Session variables are scoped to the current session only, so each session must set its own variable before referencing it with the $ prefix.
- B
Session variables are stored at the database level, but only the user who created them can read them from another session.
- C
The variable should be referenced as :bu_id instead of $bu_id because Snowflake only supports bind variables in worksheets.
- D
The variable becomes available to all sessions after the worksheet is committed with a COMMIT statement.
Show answer and explanation
Correct answer: A
Explanation
This scenario tests practical understanding of Snowflake session variables and session context. A variable created with SET is available only within the current session. It is not a database object, is not shared with other sessions, and does not persist across separate connections unless explicitly set again. In Snowflake SQL, session variables are referenced with the $ prefix, for example $bu_id. This makes them useful for parameterizing queries in a worksheet or session without changing the SQL text for each run. Best practice is to initialize required session variables at the start of each session or script that depends on them. This aligns with Snowflake documentation for SQL variables and session context behavior.
- A. Correct.
Correct. In Snowflake, SQL session variables are session-scoped. A variable set with SET exists only for the current session and must be referenced with the $ prefix in SQL statements, such as WHERE business_unit = $bu_id. If another user or another session needs the same variable, it must be set again in that session.
- B. Incorrect.
Incorrect. This reflects a common misconception that variables are shared like database objects. Session variables are not stored as database-level objects and are not visible across sessions, even for the same user.
- C. Incorrect.
Incorrect. In Snowflake SQL, session variables are referenced with the $ prefix, not with a colon. Colon-prefixed values are associated with client-side bind variable patterns in some tools or connectors, but the SQL session variable created with SET is used as $variable_name in worksheets and standard SQL execution.
- D. Incorrect.
Incorrect. COMMIT controls transaction persistence for DML changes, not visibility of session variables. Session variables are not made global or shared by committing a transaction.