SnowPro Associate: Platform Question 92
Single answer● Python variable substitutionA data engineer is using the Snowflake Connector for Python to run ad hoc SQL against a table chosen at runtime. The engineer writes the following code:
schema_name = "PUBLIC" table_name = "SALES_2024" cur.execute("SELECT COUNT(*) FROM %s.%s", (schema_name, table_name))
When executed, the query fails. The engineer wants a safe and supported way to substitute the schema and table names from Python variables without hardcoding them into the SQL string. Which approach should the engineer use?
- A
Use SQL bind variables for object names: cur.execute("SELECT COUNT(*) FROM %s.%s", (schema_name, table_name)) because bind variables work for identifiers and literals.
- B
Build the SQL text with Python string concatenation because object names cannot be passed any other way, and concatenation is the recommended secure approach.
- C
Use Snowflake SQL IDENTIFIER() with bound values, for example: cur.execute("SELECT COUNT(*) FROM IDENTIFIER(%s)", (f"{schema_name}.{table_name}",))
- D
Wrap the object names in single quotes and bind them as strings: cur.execute("SELECT COUNT(*) FROM '%s'.'%s'", (schema_name, table_name))
Show answer and explanation
Correct answer: C
Explanation
This question tests the difference between substituting data values and substituting object identifiers when using Python with Snowflake. In the Snowflake Connector for Python, bind variables are appropriate for literal values in predicates and expressions, but not for directly replacing object names such as table names. For dynamic object names, Snowflake provides IDENTIFIER(), which interprets a string expression as an identifier. A practical pattern is to pass a fully qualified name like PUBLIC.SALES_2024 as a bound value and reference it with IDENTIFIER(%s). This avoids insecure manual concatenation and aligns with Snowflake SQL guidance for object identifier resolution. Candidates should recognize that quoted strings are literals, not identifiers, and that safe variable substitution depends on whether the variable represents a value or an object name.
- A. Incorrect.
Incorrect. In Snowflake, bind variables are used for literal values, not directly for database object identifiers such as database, schema, table, or column names. This is a common misconception because Python DB-API parameter substitution is often associated with general query parameterization, but object names require identifier handling rather than value binding.
- B. Incorrect.
Incorrect. Although string concatenation can produce a syntactically valid SQL statement, it is not the recommended secure approach when values come from variables or user input. It increases the risk of SQL injection and quoting errors. Snowflake best practice is to avoid manual concatenation for dynamic object names when a supported mechanism such as IDENTIFIER() can be used.
- C. Correct.
Correct. Snowflake supports using IDENTIFIER() to treat a string value as an object identifier. In Python, the fully qualified object name can be passed as a bound value and then resolved by IDENTIFIER() in the SQL statement. This is the supported and safer pattern for dynamic object-name substitution compared with manual string assembly.
- D. Incorrect.
Incorrect. Single quotes create string literals, not identifiers. A statement like FROM 'PUBLIC'.'SALES_2024' is not valid for referencing schema and table objects. This option reflects confusion between SQL string values and SQL identifiers.