SnowPro Associate: Platform Question 78
Single answer○ PythonA data engineering team wants to transform sales records in Snowflake using Python so analysts can call the logic directly in SQL. The transformation computes a risk score from several input columns and must run entirely inside Snowflake without managing external servers. The team also wants the function to return a single value for each row. Which Snowflake feature is the best fit for this requirement?
- A
Create a Python UDF that accepts the input columns and returns the risk score for each row
- B
Create a Python stored procedure because stored procedures are designed for per-row scalar calculations in SQL statements
- C
Use SnowSQL with a local Python script, because Python code cannot run inside Snowflake
- D
Create an external function that calls a Python service, because Python logic for SQL queries must be hosted outside Snowflake
Show answer and explanation
Correct answer: A
Explanation
The best answer is to create a Python UDF. In Snowflake, Python UDFs are appropriate when you need SQL-accessible Python logic that returns a value, including scalar outputs for row-by-row use in queries. Python stored procedures are better for control-flow and operational tasks rather than inline scalar expression logic. External functions are intended for integrating with services outside Snowflake, which conflicts with the requirement to run fully in-platform. This aligns with Snowflake best practices for choosing between UDFs, stored procedures, and external functions based on workload type and execution model.
- A. Correct.
Correct. A Python UDF is designed for reusable Python logic that can be invoked from SQL and can return a scalar value for each row. This matches the requirement to compute a risk score from input columns and make the logic available directly in SQL while running inside Snowflake.
- B. Incorrect.
Incorrect. Python stored procedures in Snowflake are intended for procedural logic such as orchestration, administrative tasks, or multi-step workflows. They are not the best choice for simple per-row scalar calculations embedded directly in SQL queries. A common misconception is that any Python code in Snowflake should use stored procedures, but scalar row-level logic is a stronger fit for UDFs.
- C. Incorrect.
Incorrect. Snowflake supports Python execution inside Snowflake through features such as Python UDFs and Python stored procedures. SnowSQL is a command-line client and does not provide the in-database execution model required here. This option reflects the misconception that Python must run only outside the platform.
- D. Incorrect.
Incorrect. External functions are used when logic must call an external service through API integration, typically involving remote endpoints outside Snowflake. Because the requirement explicitly states the logic must run entirely inside Snowflake and avoid managing external servers, an external function is unnecessarily complex and does not best fit the scenario.