ARA-C01 Question 308
Single answerPythonA data engineering team is building a Snowflake-native enrichment pipeline. Source files land in an internal stage, and the team wants to use Python to parse semi-structured payloads, call a small set of approved third-party libraries, and write normalized rows into Snowflake tables. The solution must be fully governed inside Snowflake, avoid moving data to external compute, and be schedulable as part of an in-platform orchestration design. Which approach best meets these requirements?
- A
Create a Python stored procedure using Snowpark Python, specify required packages, read staged files from within Snowflake, transform the data, and invoke it from a Snowflake task.
- B
Deploy a Python application on an external VM, download files from the stage with SnowSQL, transform them locally, and write results back through the Snowflake connector.
- C
Create a Python UDF that performs the entire multi-step pipeline, including transactional writes to multiple target tables and orchestration of downstream processing.
- D
Use a JavaScript stored procedure to call arbitrary local Python code installed on the warehouse nodes, because warehouses can execute any language runtime available to the account.
Show answer and explanation
Correct answer: A
Explanation
The best answer is to use a Python stored procedure built with Snowpark Python and schedule it with a task. This pattern keeps data processing inside Snowflake's governed environment, supports package dependencies through Snowflake's supported Python execution framework, and allows procedural control flow plus DML operations against Snowflake tables. By contrast, external Python applications require data movement and separate infrastructure, which conflicts with a Snowflake-native architecture goal. Python UDFs are not the right tool for full pipeline orchestration and multi-step table-writing workflows; they are better suited for encapsulated function logic. Snowflake best practices distinguish between UDFs for expression-style computation and stored procedures for operational workflows and orchestration. Relevant Snowflake documentation includes Snowpark Python, Python stored procedures, user-defined functions, stages for file access, and tasks for scheduling and orchestration.
- A. Correct.
Correct. A Python stored procedure with Snowpark Python is designed for in-platform procedural logic and data processing inside Snowflake. It can use supported packages, access staged files, transform data, and write to Snowflake tables without moving data to external infrastructure. It can also be scheduled and orchestrated using Snowflake tasks, which aligns with the requirement for a governed, Snowflake-native pipeline.
- B. Incorrect.
Incorrect. This approach moves processing to external compute, which violates the requirement to keep processing governed inside Snowflake and avoid moving data out for transformation. While it is technically possible to use external Python applications with Snowflake connectors, it is not the best fit for a fully Snowflake-native architecture.
- C. Incorrect.
Incorrect. Python UDFs are intended for row-level or set-oriented function logic that returns values, not for orchestrating complex procedural workflows with multi-step DML across multiple tables. For pipeline control flow, multi-statement logic, and writes to target tables, a stored procedure is the appropriate construct.
- D. Incorrect.
Incorrect. Snowflake does not allow customers to execute arbitrary local Python code installed directly on warehouse nodes. Language runtimes must be supported by Snowflake through its managed execution environments, such as Snowpark Python for stored procedures and UDFs. A JavaScript stored procedure also would not provide the native Python execution model described in the scenario.