Data Engineering Study Guide — ARA-C01

Snowpark and Other Development

This domain is the smallest topic area on the SnowPro Advanced: Architect (ARA-C01) exam under the current (carried-over, unverified) blueprint weighting. It tests your ability to design, implement, and troubleshoot data engineering and ML workflows using Snowflake's programmatic interfaces: Snowpark (Python, Java, Scala), stored procedures, UDFs/UDTFs, Snowpark ML, Snowpark Container Services, the Native App Framework, and external functions. Expect questions that require you to choose the right runtime, understand execution semantics, and identify architectural trade-offs — not just recall syntax.

Snowpark DataFrame API: Lazy Evaluation and Pushdown

Core Concepts

Snowpark is Snowflake's developer framework that lets you write DataFrame-style code in Python, Java, or Scala that executes inside Snowflake's query engine. The three supported languages have feature parity for core operations but differ in deployment details.

Lazy evaluation is the fundamental execution model. When you call df.filter(...), df.join(...), or df.select(...), no SQL is executed. Snowpark builds an execution plan (an abstract syntax tree) that is only materialized when an action is called.

Pushdown means the entire DataFrame plan is compiled to a single SQL statement and sent to the Snowflake engine. There is no data movement to the client during transformation chaining. This is architecturally distinct from Spark, where shuffles occur across worker nodes.

Key pushdown rules:

• All standard SQL-expressible operations push down fully.
• Python UDFs break full pushdown — they execute row-by-row inside Snowflake but require a round-trip to the Python sandbox per batch.
• Vectorized UDFs (Pandas UDFs) reduce this overhead by processing batches rather than individual rows.
• toPandas() pulls all data to the client — avoid on large datasets.

Worked scenario: A pipeline reads 500 million rows, applies 12 filter and join transformations, then writes to a table. Should you call collect() or write.saveAsTable()? Reasoning: collect() returns all rows to the client driver, causing massive network transfer. write.saveAsTable() executes the entire plan server-side. Always prefer server-side actions for large datasets.

Common traps

• Confusing Snowpark's lazy model with immediate execution; .show(10) does execute (it's an action).
• Assuming Snowpark distributes compute across nodes like Spark — Snowflake's Virtual Warehouse is the compute unit, and Snowpark does not expose parallelism controls to the developer.
• Thinking df.schema is free — it triggers a lightweight describe query.
• Mixing toPandas() into large pipelines without recognizing the client-side data transfer cost.

Snowpark Python UDFs and UDTFs

UDF Fundamentals

User-Defined Functions (UDFs) allow custom scalar logic. User-Defined Table Functions (UDTFs) return multiple rows per input row. Both execute inside Snowflake's sandbox environment — an isolated container per Virtual Warehouse node.

Vectorized UDFs use the @vectorized decorator and receive a pandas.DataFrame as input. They are the preferred approach for ML inference inside UDFs because libraries like NumPy and scikit-learn operate on arrays natively.

Handler performance tips:

• Import heavy libraries (e.g., import numpy) at module level, not inside the handler function, to avoid re-import on each call.
• Use permanent UDFs (stored in a schema) rather than temporary ones in pipelines; temporary UDFs exist only for the session.
• Packages available from Anaconda (the Snowflake-managed channel) do not require a stage — specify them in PACKAGES = (...) in the CREATE FUNCTION DDL.
• Custom packages not in Anaconda must be uploaded to a named stage and referenced via IMPORTS.

UDTF class contract:

class MyUDTF:
    def process(self, col1, col2) -> Iterable[Tuple]:  # yields rows
    def end_partition(self) -> Iterable[Tuple]:         # optional, runs after partition

end_partition() enables partition-scoped aggregation — critical for sliding-window ML feature generation.

Common traps

• Forgetting that vectorized UDFs still execute inside Snowflake — they do not run on an external server.
• Using end_partition() without a PARTITION BY clause in the calling query — the entire table becomes one partition, causing memory issues.
• Believing temporary UDFs persist across sessions — they do not.
• Specifying a package not in the Snowflake Anaconda channel without uploading it to a stage; the CREATE will fail.