Distributed Feature Engineering with Snowpark and Snowflake ML Preprocessors

1.Snowpark DataFrames are lazy queries

In Snowpark, the DataFrame is the main way to query and process data, so every feature pipeline here starts with one. Its key property is laziness. A Snowpark DataFrame does not hold rows. It describes a relational dataset, much like a query that has not been run yet. Snowpark libraries exist for Java, Python and Scala, and the examples below use Python.

Working with a DataFrame takes three steps. First you construct it from a data source using the Session. session.table() reads a table, view or stream. session.create_dataframe() takes local values. session.range() generates a sequence. session.read followed by a format method such as json or csv reads files in a stage. session.sql() wraps the result of a SQL statement. Second, you specify transformations: which columns to select, how to filter rows, and how to sort and group. Third, you call an action such as collect(). Only the action sends the generated SQL to Snowflake for execution.

>>> # Create a DataFrame with the "id" and "name" columns from the "sample_product_data" table.
>>> # This does not execute the query.
>>> df = session.table("sample_product_data").select(col("id"), col("name"))

>>> # Send the query to the server for execution and
>>> # return a list of Rows containing the results.
>>> results = df.collect()

Lazy evaluation matters for feature engineering. Because nothing runs until the end, Snowpark can defer transformations and batch many of them into a single operation. That cuts the data moved between your client and Snowflake and improves performance. And because the work is pushed down, you need no separate cluster: Snowflake runs the computation and manages scale.

This laziness is also the starting point for analyzing a use case as SQL versus Snowpark. A DataFrame is a query that sends SQL to Snowflake when an action runs, so a SQL statement and a Snowpark DataFrame are not rival engines. Both execute in Snowflake, next to the data. Your analysis is about authoring, not about where the compute runs. The section "Choosing between SQL and Snowpark" below works through the authoring options.

Sources12

2.Three ways to engineer features in Snowflake ML

After the data is in a DataFrame, you choose how to transform it into features. Snowflake ML documents three approaches. It tells you to pick based on data size, performance requirements and how much custom transformation logic you need. The main difference between them is where the compute runs.

Open-source preprocessors such as scikit-learn run in memory on a single node within Container Runtime. They are quick for prototyping, but they don't distribute. Read the table's two OSS cells together: "in memory" describes how the data is processed, and "Snowpark Container Services (Compute Pool)" names the compute that hosts the Container Runtime node doing that work. Neither is a warehouse. Only the Snowflake ML preprocessors list a warehouse as their compute. They are the warehouse-native option: their processing is distributed across warehouse compute resources. Ray map_batches runs custom functions in parallel across the nodes of a compute pool. Its execution is also lazy: nothing is processed until you materialize the dataset, which reduces memory use. Ray is the choice for large-scale processing with custom logic, especially on unstructured data.

Sources3

3.Snowflake ML preprocessors and Pipelines

The Snowflake ML preprocessors are in snowflake.ml.modeling.preprocessing, and the Pipeline class that chains them is in snowflake.ml.modeling.pipeline. They look like scikit-learn, with two important differences. First, each preprocessor names its columns with input_cols and output_cols rather than receiving a column selection through a ColumnTransformer. Second, the input is a Snowpark DataFrame, usually from session.table(), not a pandas DataFrame held in memory. When you call fit_transform on the pipeline, the fit and the transform run in Snowflake as distributed work on the warehouse.

# Load your data from a Snowflake table as a DataFrame
df = session.table('CUSTOMER_DATA')

# Define Snowflake ML preprocessors
scaler = StandardScaler(input_cols=['AGE', 'INCOME'], output_cols=['AGE_SCALED', 'INCOME_SCALED'])
encoder = OneHotEncoder(input_cols=['CITY'], output_cols=['CITY_ENCODED'])

pipeline = Pipeline(steps=[
    ('scaling', scaler),
    ('encoding', encoder)
])

# Fit and transform data in Snowflake (distributed)
result = pipeline.fit_transform(df)
result.show()

The fit step is what makes a scaler stateful. A min-max scaler, for example, learns the minimum and maximum values of each column from the training sample, and that learned state is what the transform step applies. The Snowflake guide describes such transformations as capturing the global state of the training sample, which is why they belong with the model rather than being recomputed on whatever data arrives later.

from snowflake.snowpark import Session
from snowflake.ml.modeling. ?  import StandardScaler, OneHotEncoder
from snowflake.ml.modeling.pipeline import Pipeline

Answer to the reflection: refitting on the inference batch would learn new statistics, such as a different minimum and maximum, from that batch alone. Its features would then be scaled on a different basis from the one the model was trained on. Reuse the state captured from the training sample instead.

Sources3456

4.Choosing between SQL and Snowpark

"SQL or Snowpark" is less of a choice than it sounds. A Snowpark DataFrame evaluates by sending SQL statements to Snowflake, so both run on the same engine. The real decision is how you author the logic and where any custom code runs.

Set-based logic you already have as SQL. session.sql() accepts a SQL string, and it can run SELECT statements over tables and staged files. The native Snowpark constructs, such as select and col, are still the better authoring option. They give intelligent code completion and type checking. For reading data, the docs also say session.table() and session.read give better syntax and error highlighting than embedding the same query in a string.

Logic that SQL expresses poorly. For loops or batch functions, write a user-defined function. Snowpark sends the UDF code to the server, and Snowflake runs it in parallel next to the data, so you don't pull rows to the client.

Python code that needs a lot of memory. Snowpark code runs on standard warehouses too. Snowpark-optimized warehouses are recommended for Snowpark workloads with large memory requirements or a dependency on a specific CPU architecture, such as ML training in a stored procedure on a single warehouse node. By default they provide 16x the memory per node of a standard warehouse. The resource_constraint property of CREATE or ALTER WAREHOUSE configures memory and CPU architecture. Creating or resuming one can take longer than a standard warehouse.

These sources give no formal SQL-versus-Snowpark decision matrix. The guidance above is what they do support.

Sources217

Exam traps

Each one states something that sounds right. Open it to see what is actually true.

1. 1.Snowflake ML preprocessors work like scikit-learn: they pull the table into client memory and transform it there.Why is that wrong?

   They are pushed down. The fit and transform run as distributed work on the warehouse, which is why they suit large datasets and production workloads.

   Covered in Snowflake ML preprocessors and Pipelines

2. 2.A Snowpark-optimized warehouse is a general upgrade that speeds up any query.Why is that wrong?

   It targets Snowpark code with large memory or specific CPU-architecture needs. The extra memory per node solves memory pressure in that code, not general SQL performance.

   Covered in Choosing between SQL and Snowpark

3. 3.Choosing SQL over Snowpark (or the reverse) decides whether the work runs inside Snowflake.Why is that wrong?

   Both do. A Snowpark DataFrame sends SQL statements to Snowflake when an action runs, so the choice is about how you author the logic, not where it executes.

   Covered in Snowpark DataFrames are lazy queries