SnowPro Associate: Platform Question 89
Single answer● Python variable substitutionA data engineer is using the Snowflake Connector for Python to load daily sales data. The load date is stored in a Python variable named load_dt with the value "2025-01-15". The engineer wants to run a query that returns rows from SALES where SALE_DATE matches that value, while avoiding manual string concatenation in the SQL text. Which approach should the engineer use?
- A
Use a parameterized query, for example: cursor.execute("SELECT * FROM SALES WHERE SALE_DATE = %s", (load_dt,))
- B
Embed the variable directly with Python string formatting, for example: cursor.execute(f"SELECT * FROM SALES WHERE SALE_DATE = '{load_dt}'")
- C
Use SnowSQL-style variable substitution in the Python script, for example: cursor.execute("SELECT * FROM SALES WHERE SALE_DATE = &load_dt")
- D
Set a Snowflake SQL session variable first, then reference it as $load_dt in the same Python execute call without a separate statement
Show answer and explanation
Correct answer: A
Explanation
For Python applications connecting to Snowflake, the practical and recommended way to substitute runtime values into SQL is to use parameter binding supported by the Snowflake Connector for Python. This avoids constructing SQL text manually and reduces errors around quoting and data types. A common misconception is to reuse SnowSQL substitution syntax such as &var inside Python code, but that syntax is for SnowSQL client-side variable substitution and is not how the Python connector handles parameters. Another misconception is to rely on Python f-strings or concatenation, which may work syntactically but is not the best practice for safe, maintainable SQL execution. Snowflake documentation for the Python connector describes binding data values when executing statements, while SQL session variables are a separate Snowflake SQL feature that must be explicitly set before use.
- A. Correct.
Correct. In the Snowflake Connector for Python, variable substitution should be done with parameter binding rather than manually building SQL strings. Passing the value separately lets the connector handle quoting and type conversion appropriately. This is the recommended approach for dynamic values in SQL executed from Python.
- B. Incorrect.
Incorrect. Although this may appear to work, it is manual string interpolation, not proper variable substitution through the connector. It can lead to quoting mistakes, harder-to-maintain code, and security risks if values are user-supplied. The question specifically asks to avoid manual string concatenation or interpolation in SQL text.
- C. Incorrect.
Incorrect. The &variable syntax is associated with client-side substitution tools such as SnowSQL, not the Snowflake Connector for Python. Python code using the connector does not automatically interpret SnowSQL substitution syntax.
- D. Incorrect.
Incorrect. Snowflake SQL session variables do exist and can be referenced with $variable after being set, but they are not created implicitly from Python variables inside a single SQL statement. To use a SQL session variable, the engineer would need a separate SET statement or equivalent logic. This option describes behavior that does not occur automatically.