SnowPro Associate: Platform Question 77
Single answer○ PythonA data engineering team wants to automate a daily cleanup task directly in Snowflake using Python. The task must delete rows older than 90 days from a staging table and run on a schedule without requiring an external server. The team also wants the code to execute close to the data and use Snowflake-managed infrastructure. Which approach best meets these requirements?
- A
Create a Python stored procedure for the delete logic, then invoke it from a Snowflake task scheduled to run daily.
- B
Create a Python UDF that performs the DELETE statement, then schedule it with a Snowflake task.
- C
Run the Python script from a user workstation with SnowSQL and rely on the workstation's OS scheduler.
- D
Create an external function in Python and attach a Snowflake alert to execute it daily.
Show answer and explanation
Correct answer: A
Explanation
The best solution is to use a Python stored procedure together with a Snowflake task. In Snowflake, stored procedures are the correct Python-based object for encapsulating procedural logic and executing SQL statements, including DML such as DELETE. Tasks provide native scheduling inside Snowflake, allowing recurring execution without relying on an external orchestrator or server. By contrast, Python UDFs are for row-level or expression-level computation and are not designed to perform side-effecting operations like table deletes. External functions depend on external services, which conflicts with the stated requirement. This aligns with Snowflake best practices for in-platform automation: use stored procedures for procedural SQL logic and tasks for scheduling.
- A. Correct.
Correct. Python stored procedures in Snowflake can contain procedural logic and execute SQL statements such as DELETE. A Snowflake task can schedule the stored procedure to run daily using Snowflake-managed compute, which satisfies the requirement to avoid external infrastructure and run close to the data.
- B. Incorrect.
Incorrect. A Python UDF is intended to return a value for use in SQL expressions and does not perform DML operations like DELETE against tables. This is a common misconception because both UDFs and stored procedures support Python, but only stored procedures are appropriate for procedural actions and SQL command execution.
- C. Incorrect.
Incorrect. Although this could automate the process, it depends on external infrastructure such as a workstation and OS scheduler, which violates the requirement to use Snowflake-managed infrastructure and keep execution inside Snowflake.
- D. Incorrect.
Incorrect. External functions are used to call code outside Snowflake, typically through remote services such as API integrations, so they do not meet the requirement to avoid external servers. Also, alerts are designed to evaluate conditions and trigger actions, not to replace scheduled data maintenance tasks in this scenario.