ARA-C01 Question 307
Single answerPythonA data platform team is designing a Snowflake-native ingestion framework that uses Python. Source systems deliver files to cloud storage, and the team wants Snowflake to automatically detect new files, enrich the records with Python logic during ingestion, and keep operational overhead low. The architects want the transformation logic to run inside Snowflake, avoid managing external compute, and use a supported Python capability for row-by-row processing before loading into target tables. Which solution best meets these requirements?
- A
Create a Snowpipe that calls a Python stored procedure directly for each arriving file, then have the procedure transform rows before the COPY operation commits.
- B
Use Snowpipe to load raw data into a landing table, then trigger a task that runs SQL calling a Python UDF to apply row-level enrichment into the target table.
- C
Configure an external function written in Python as the ingestion transform so Snowpipe can invoke it inline for each row as files are loaded.
- D
Run a Python worksheet on a schedule to poll the stage, transform the files locally in the worksheet session, and then write results back to Snowflake tables.
Show answer and explanation
Correct answer: B
Explanation
The best answer is to separate ingestion from transformation using native Snowflake services. Snowpipe is designed for automated continuous loading of files from stages into tables using COPY INTO. For transformations, especially when Python logic is required, a common supported architecture is to land the raw data first and then apply SQL transformations that call Python UDFs, orchestrated by tasks if needed. This pattern keeps processing inside Snowflake and avoids external services.
Key architectural points:
- Snowpipe is for automated ingestion, not arbitrary inline Python execution during COPY processing.
- Python UDFs support row-level logic and can be invoked from SQL after data lands in a table.
- Python stored procedures are better suited for orchestration and control flow, not inline row-by-row transformation inside Snowpipe.
- External functions require remote services and therefore do not meet the low-ops, Snowflake-native requirement.
This aligns with Snowflake documentation and best practices around Snowpipe, tasks, Python UDFs, and Python stored procedures: use Snowpipe for continuous loading, tasks for orchestration/scheduling, UDFs for row-level transformation logic, and stored procedures for procedural orchestration rather than inline ingestion transforms.
- A. Incorrect.
Incorrect. Snowpipe executes continuous data loading via COPY INTO and does not directly invoke a Python stored procedure inline for each file as part of the load pipeline. Stored procedures can orchestrate operations in Snowflake, but they are not a native inline row transformation mechanism inside Snowpipe. A common misconception is to treat stored procedures like event handlers attached directly to Snowpipe ingestion events.
- B. Correct.
Correct. This design aligns with supported Snowflake patterns: Snowpipe automatically loads incoming files into a landing/raw table, and a task can execute downstream SQL to transform the data. If the enrichment is row-level Python logic, a Python UDF can be invoked from SQL against the landed data to populate the target table. This keeps execution inside Snowflake, avoids external compute management, and uses Snowpipe for automated file detection with Python-based processing in a supported way.
- C. Incorrect.
Incorrect. External functions can integrate Snowflake with remote services, but they require external infrastructure such as API Gateway and a remote service, which increases operational overhead and does not satisfy the requirement to avoid managing external compute. Also, Snowpipe does not use external functions as an inline transformation engine during file loading in the way described here.
- D. Incorrect.
Incorrect. A Python worksheet can run Python code in Snowflake, but using a scheduled worksheet to poll stages is not the best architectural choice for production ingestion automation. It introduces unnecessary orchestration overhead and is less appropriate than Snowpipe plus tasks for event-driven file loading. It also does not provide the low-operational-overhead, native ingestion workflow requested.