SnowPro Associate: Platform Question 90
Single answer● Python variable substitutionA data engineer is using Snowflake Connector for Python to run the same query for different warehouse names during an environment validation script. They want to avoid building SQL strings manually and instead use Snowflake SQL variable substitution supported by the Python connector. Which approach correctly enables variable substitution and references the variable in the SQL statement?
- A
Execute
SET wh_name='COMPUTE_WH'and then runSELECT * FROM INFORMATION_SCHEMA.WAREHOUSES WHERE WAREHOUSE_NAME = $wh_namein the same session. - B
Pass
{'wh_name': 'COMPUTE_WH'}as a bind parameter dictionary and runSELECT * FROM INFORMATION_SCHEMA.WAREHOUSES WHERE WAREHOUSE_NAME = $wh_namewithout any session variable setup. - C
Run
ALTER SESSION SET wh_name='COMPUTE_WH'and then querySELECT * FROM INFORMATION_SCHEMA.WAREHOUSES WHERE WAREHOUSE_NAME = :wh_name. - D
Set a Python variable
wh_name = 'COMPUTE_WH'and runSELECT * FROM INFORMATION_SCHEMA.WAREHOUSES WHERE WAREHOUSE_NAME = wh_namebecause the connector substitutes Python variable names automatically.
Show answer and explanation
Correct answer: A
Explanation
The correct answer is to use Snowflake SQL session variables: define the variable with SET and reference it with a $ prefix in later SQL statements executed in the same session. This is the Snowflake-supported variable substitution pattern and is distinct from Python-side string formatting or connector bind parameters. For SnowPro Associate, it is important to distinguish among three concepts: Snowflake session variables (SET myvar='x' then use $myvar), session parameters (ALTER SESSION SET ...), and connector parameter binding. Best practice is to avoid manual string concatenation for dynamic values when a supported mechanism exists, because it improves correctness and reduces risk of malformed SQL. See Snowflake documentation on SQL variables/session variables and the Snowflake Connector for Python parameter binding behavior.
- A. Correct.
Correct. In Snowflake SQL, session variables are created with
SETand referenced with a$prefix, such as$wh_name. When using the Snowflake Connector for Python, statements executed on the same connection share the same session, so the script can first executeSET wh_name='COMPUTE_WH'and then use$wh_namein a subsequent SQL statement. This is the valid pattern for Snowflake SQL variable substitution. - B. Incorrect.
Incorrect. This option mixes up Snowflake session variables with client-side bind variables. A Python dictionary of bind values is not the same as defining a Snowflake SQL session variable. If the SQL text uses
$wh_name, Snowflake expects a session variable namedwh_nameto already exist. Bind parameters are handled differently and use parameter binding syntax supported by the connector, not$session_variablesyntax. - C. Incorrect.
Incorrect.
ALTER SESSION SETis used for session parameters, not for creating arbitrary SQL variables. Also,:wh_nameis not the Snowflake SQL syntax for referencing a session variable. Snowflake session variables are created withSETand referenced as$wh_name. - D. Incorrect.
Incorrect. The Snowflake Connector for Python does not automatically inspect Python variable names inside SQL text and replace them. Writing
WAREHOUSE_NAME = wh_namewould be interpreted by Snowflake as an identifier or invalid expression context, not as the Python variable's value. Developers must use either proper session variables (SETplus$variable) or supported parameter binding.