What you will be able to do
- Build lazy Snowpark DataFrames from tables, stages and SQL, and know which call actually runs the query
- Fit and apply Snowflake ML preprocessors and Pipelines on large tables without pulling rows to the client
- Decide whether a transformation belongs in plain SQL, Snowpark DataFrames, a UDF or Snowflake ML preprocessors
- Choose tables, internal or external stages for training and inference data, and write results back with DataSink
Key concept
Pushdown — Snowpark DataFrame operations and Snowflake ML preprocessors become SQL that the warehouse runs where the data already lives. Your Python process only describes the work. The rows stay in Snowflake, so data size is the warehouse's problem and not your client's.
1.Snowpark DataFrames build queries, not result sets
Feature engineering on a billion-row table only works if the table never has to fit in your notebook's memory. Snowpark handles this with the DataFrame. A Snowpark DataFrame is a description of a relational query. It is not a copy of the data. Selecting columns, filtering rows, grouping and sorting all add to that description, and nothing reaches the warehouse until you call an action such as collect().
>>> # 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()Laziness is what makes long feature pipelines cheap. Every join, filter and derived column you chain up is batched into one operation, which runs as late as possible. Only the final result crosses the network, and only if you ask for it. If an action like collect() or show() sits in the middle of the pipeline, it forces a round trip there. If a final collect() runs on a wide training table, every row comes back to the client, and that cancels the benefit.
The Session object gives you a different constructor for each kind of source:
session.table(...)for a table, view or streamsession.read.json(...)orsession.read.schema(...).csv(...)for files in a stage, optionally with an explicitStructTypeschemasession.sql(...)for the result of a SQL statementsession.create_dataframe(...)andsession.range(...)for small local values, which are useful in tests
Checkpoint 1 of 7· Fill the gap
The first line only builds a lazy DataFrame. Which method completes the last line so the SQL is sent to Snowflake and the results come back as a list of Rows?
>>> # 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() is an action. It evaluates the DataFrame, sends the SQL to Snowflake and returns Rows. select, sort and group_by are transformations: they only add to the lazy query description and run nothing.
Source: docs.snowflake.com2.Snowflake ML preprocessors and Pipelines run in the warehouse
Scaling and encoding have a fit step, and that is the hard part to distribute. A scaler such as MinMaxScaler must first learn each column's minimum and maximum. An encoder such as OneHotEncoder must first learn the set of categories. In scikit-learn, fit scans a pandas DataFrame in local memory. The classes in snowflake.ml.modeling.preprocessing take a Snowpark DataFrame instead and push the work down to the warehouse your session is using. Statistics like a column's minimum and maximum are computed inside Snowflake, so the rows never leave it.
Two terms in the comparison below need defining. Container Runtime is a preconfigured environment for ML workloads, used by Snowflake Notebooks and ML Jobs. It runs on compute pools (Snowpark Container Services) instead of a warehouse, and it uses Ray, an open-source framework for distributed computing, to spread Python across nodes. Ray map_batches is the Ray call that applies your own Python function to batches of data in parallel. It is lazy, so nothing runs until the dataset is materialized. A warehouse, by contrast, runs SQL that Snowflake generates for you.
| Aspect | OSS (scikit-learn) | Snowflake ML preprocessors | Ray map_batches |
|---|---|---|---|
| Scale | Small and medium datasets | Large/distributed data | Large/distributed data |
| Execution | In memory | Pushdown to the default warehouse running your SQL queries | Across nodes in a compute pool |
| Compute | Snowpark Container Services (Compute Pool) | Warehouse | Snowpark Container Services (Compute Pool) |
| Best for | Quick prototyping and experimentation | Production workflows with large datasets | Custom logic, unstructured data, custom resource controls |
The table rules out the usual wrong answers about where a preprocessor's fit runs. In-memory execution belongs to the open-source column. Compute pools belong to scikit-learn on Container Runtime and to Ray. A Snowflake ML preprocessor uses neither: it does not provision a container job, and it does not stream rows through the Python driver. It sends SQL to the warehouse. That also makes warehouse size the setting that matters for these preprocessors.
# 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()Compared with scikit-learn, the visible API change is that columns are named on the transformer itself. You pass input_cols and output_cols, and you don't need a ColumnTransformer to route columns. Pipeline comes from snowflake.ml.modeling.pipeline and chains steps exactly as its scikit-learn namesake does. fit_transform returns another Snowpark DataFrame, which means the result is still lazy until show() or another action runs. A fitted pipeline holds what it learned from the data it was fitted on, such as ranges and category lists. Calling fit again on different data replaces those learned values.
The catalogue is smaller than scikit-learn's. Snowflake ML ships a subset of the scikit-learn preprocessors that covers the common cases, including StandardScaler, MinMaxScaler and OneHotEncoder. To see the parameters of any of them, import it and call help(OneHotEncoder) in a Python session.
No. The preprocessor pushes its work down to the session's warehouse. The compute pool only runs the Python that sends the queries. In-memory or compute-pool execution happens only if you choose scikit-learn or Ray instead.
Checkpoint 2 of 7· Check yourself
A production job fits a Snowflake ML StandardScaler and OneHotEncoder Pipeline on a billion-row table-backed Snowpark DataFrame, and the fit is too slow. Which change speeds up the fit itself?
Snowflake ML preprocessors push their fit down as SQL to the session's warehouse, so warehouse size is the setting that matters. Compute pools and client memory only matter for scikit-learn or Ray, which are not built for data at this scale.
“Pushdown to the default warehouse that you’re using to run SQL queries”Source: docs.snowflake.com
Checkpoint 3 of 7· Exam question
A team stores 2 billion customer rows in a Snowflake table. A data scientist calls fit on a snowflake.ml.modeling.preprocessing.MinMaxScaler using a Snowpark DataFrame built from that table. Where are the per-column minimum and maximum values computed?
Correct answer: B — Inside the active virtual warehouse, which evaluates generated SQL aggregates over the table so rows never leave Snowflake
- A. Incorrect: no container service is provisioned automatically, and the statistics are exact over the full table rather than estimated from a sample.
- B. Correct: with a Snowpark DataFrame input, the fit step is translated into SQL aggregate queries that run on the warehouse, so the data is processed where it lives.
- C. Incorrect: the library does not unload the table into staged Parquet files to compute statistics; it pushes the computation down as queries.
- D. Incorrect: row-by-row transfer to the client is what happens with local pandas workflows, not with Snowflake ML preprocessors on Snowpark DataFrames.
Checkpoint 4 of 7· Exam question
Which Python module contains the scikit-learn-style distributed preprocessors such as MinMaxScaler, OneHotEncoder, and OrdinalEncoder that execute inside Snowflake?
Correct answer: A — snowflake.ml.modeling.preprocessing
- A. Correct: the Snowflake ML preprocessing classes live in snowflake.ml.modeling.preprocessing and mirror a subset of scikit-learn's preprocessors.
- B. Incorrect: snowflake.snowpark.functions holds SQL-function wrappers for DataFrame columns and has no preprocessing submodule of transformers.
- C. Incorrect: the Feature Store package manages entities and feature views, not the scaler and encoder classes.
- D. Incorrect: snowflake.ml.data covers data connectors and ingestion helpers rather than fit/transform preprocessors.
3.Choosing SQL, Snowpark or ML preprocessors for a transformation
Plain SQL and Snowpark DataFrames both run in the warehouse, so speed rarely decides between them. The Snowpark API provides Python constructs for building SQL statements: a DataFrame select produces the same kind of query you would otherwise write as a string. For set-based work like filters, joins and aggregations, both execute as warehouse SQL. Choose based on how complex the logic is, whether it will be reused, and who will maintain it.
Usually not. session.sql(...) wraps the existing query in a lazy DataFrame. From there the engineer can join it, pass it to a preprocessor or write it out, while the analysts keep owning SQL they already understand. Snowpark DataFrame methods win when the transformation is built in code: logic shared across pipelines as Python functions, column lists generated from configuration, or steps that branch on parameters. Native methods such as session.table and session.read also give better syntax highlighting, error highlighting and code completion than SQL inside a string.
| Tool | Wins when | Where it runs |
|---|---|---|
| Plain SQL (session.sql or a SQL worksheet) | Set-based logic that SQL-fluent analysts own and maintain | Warehouse |
| Snowpark DataFrame methods | Logic composed and reused in Python code, with completion and type checking | Warehouse, as generated SQL |
| Snowpark UDF | Custom row-level or looping Python logic that SQL cannot express | Warehouse, where the data is |
| Snowflake ML preprocessors | Standard scaling or encoding that needs a fit step over a large table | Warehouse |
| Ray map_batches | Custom large-scale logic, especially on unstructured data | Compute pool nodes |
UDFs fill the gap between SQL and full Python. If a feature needs procedural logic, write the function in Python and register it as a UDF. Snowpark pushes the code to the server and runs it in parallel next to the data, so there is no need to pull rows out to loop over them. Don't hand-write min-max scaling in SQL or a UDF, though. Fitting and applying a scaler or encoder across a big table is exactly the job Snowflake ML preprocessors are built for.
Checkpoint 5 of 7· Match them up
Match each transformation need to the tool that should express it in a Snowflake feature pipeline.
Tap a term, then the definition that fits it.
SQL and Snowpark DataFrames both run as warehouse SQL, so the choice between them depends on who owns the logic and whether it is built in code. UDFs push procedural Python to the server. Standard scaling and encoding with a fit step belong to Snowflake ML preprocessors. Custom unstructured work runs on Ray in a compute pool.
“useful for looping or batch functionality where creating as a UDF will allow Snowflake to parallelize and apply the codeful logic at scale”Source: docs.snowflake.com
4.Storing training and inference data: tables, stages and write-back
Where features and scoring inputs live determines how they load, what they cost and who can read them. Structured data that you query, join and transform belongs in Snowflake tables. That is where Snowpark DataFrames and preprocessors do their work, and where results are written back. Stages hold files. An internal stage keeps them inside Snowflake. An external stage points at Amazon S3, Google Cloud Storage or Microsoft Azure. Raw files, Parquet exports and unstructured data like images typically land in a stage.
| Data source | Data type | Load with | Write with |
|---|---|---|---|
| Snowflake Tables | Structured | DataConnector | DataSink |
| Snowflake Datasets | Structured | DataConnector | DataSink |
| CSV or Parquet Files (Stage) | Structured | DataSource API | DataSink |
| Other Staged Files | Unstructured | DataSource API | N/A |
The two loading APIs split by where the data sits. DataConnector loads structured data from Snowflake tables and Snowflake Datasets. The DataSource API loads files from Snowflake stages in formats such as CSV, Parquet and images. Reach for DataConnector when the data is already a table or Dataset, and for the DataSource API when it is staged files, which is the only loading path for unstructured files like images.
DataConnector is the shared loading interface for structured data, whether it comes from a table, a Dataset or shared data. That means the storage choice affects load performance and governance but not the loading code. It speeds up loading by spreading reads across multiple compute nodes. A DataConnector built from a Snowpark DataFrame on a table reads that table directly, which suits development. For production training, the guidance is a versioned Snowflake Dataset. Datasets carry their own storage cost and their own privileges: reading one requires USAGE on that Dataset.
With staged files, performance depends on layout. Partition stage paths by source and date, for example United_States/California/Los_Angeles/2016/06/01/11/. Point each load at the most specific path that contains the data, so it lists and reads fewer files and parallel loads can each take a separate slice. If a stage holds thousands of small files, stage sharding merges them into larger shards to reduce per-file I/O. A node-local disk cache, on by default, serves repeated reads without fetching from Snowflake again.
CREATE STAGE my_stage URL='s3://mybucket/United_States/California/Los_Angeles/' CREDENTIALS=(AWS_KEY_ID='1a2b3c' AWS_SECRET_KEY='4x5y6z');Writing results back follows the same split. DataSink writes structured output, such as engineered features or batch predictions, into Snowflake tables, where they can be governed and queried like any other table. Unstructured staged files have a loading API but no DataSink path back. Once processed, their output has to be stored as structured rows.
Checkpoint 6 of 7· Check yourself
An inference job uses the DataSource API to load product photos from an internal stage and classifies each image. Where and how should the job persist its output?
DataSink only writes structured data back to Snowflake tables. Unstructured staged files can be loaded with the DataSource API, but there is no DataSink path to write them back, so the results have to be stored as rows in a table. DataConnector only loads data. It does not write anything.
“DataSink: Write data back to Snowflake tables.”Source: docs.snowflake.com
Checkpoint 7 of 7· Exam question
A fraud model was trained with a snowflake.ml.modeling.preprocessing.OneHotEncoder fitted on the merchant_category column. During batch inference the encoder raises an error because new merchant categories appeared that were absent from the training data. Which change allows scoring to continue without refitting?
Correct answer: D — Construct the encoder with handle_unknown="ignore" so that unseen categories are encoded as all-zero indicator vectors
- A. Incorrect:
drop_input_colsonly controls whether the source columns remain in the output; it does not alter how unknown categories are handled. - B. Incorrect: refitting on inference data changes the encoded column set, which breaks the feature layout the trained model expects (training/serving skew).
- C. Incorrect: a MinMaxScaler works on numeric data and cannot map arbitrary strings, so it does not address unseen categories.
- D. Correct:
handle_unknown="ignore"makes unseen categories produce all-zero encodings instead of raising, keeping the learned vocabulary and column layout stable for the model.
Sources
(7)
Every claim above is drawn from one of these pages, quoted as it was written on the date shown.
- 1.
“A DataFrame represents a relational dataset that is evaluated lazily: it only executes when a specific action is triggered.”
↩︎ Snowpark DataFrames build queries, not result sets“To create a DataFrame from data in a table, view, or stream, call the table method”
↩︎ Snowpark DataFrames build queries, not result sets“using the table method and read property offer better syntax highlighting, error highlighting, and intelligent code completion in development tools”
↩︎ Choosing SQL, Snowpark or ML preprocessors for a transformation“A DataFrame represents a relational dataset that is evaluated lazily: it only executes when a specific action is triggered.”
↩︎ Exam trap 1 - 2.
“delay running data transformation until as late in the pipeline as possible while batching up many operations into a single operation”
↩︎ Snowpark DataFrames build queries, not result sets“build applications that process data in Snowflake without moving data to the system where your application code runs”
↩︎ Snowflake ML preprocessors and Pipelines run in the warehouse“The Snowpark API provides programming language constructs for building SQL statements.”
↩︎ Choosing SQL, Snowpark or ML preprocessors for a transformation“useful for looping or batch functionality where creating as a UDF will allow Snowflake to parallelize”
↩︎ Choosing SQL, Snowpark or ML preprocessors for a transformation“Snowpark pushes down all data transformation and heavy lifting to the Snowflake data cloud”
↩︎ Key concept“The data isn’t retrieved when you construct the DataFrame object.”
↩︎ Prediction“useful for looping or batch functionality where creating as a UDF will allow Snowflake to parallelize and apply the codeful logic at scale”
↩︎ Checkpoint - 3.
“Snowflake ML preprocessors handle distributed transformations directly within Snowflake.”
↩︎ Snowflake ML preprocessors and Pipelines run in the warehouse“Pushdown to the default warehouse that you’re using to run SQL queries”
↩︎ Snowflake ML preprocessors and Pipelines run in the warehouse“Use Snowflake ML preprocessors for large datasets and production workloads.”
↩︎ Choosing SQL, Snowpark or ML preprocessors for a transformation“Pushdown to the default warehouse that you’re using to run SQL queries”
↩︎ Exam trap 2“The Snowflake ML preprocessors are a subset of the preprocessors available in scikit-learn, but they cover the most common use cases.”
↩︎ Exam trap 3 - 4.
“from snowflake.ml.modeling.preprocessing import OneHotEncoder”
↩︎ Snowflake ML preprocessors and Pipelines run in the warehouse - 5.
“The Container Runtime uses Ray, an open-source framework for distributed computing, to efficiently process data across multiple compute nodes.”
↩︎ Snowflake ML preprocessors and Pipelines run in the warehouse“Cut per-file I/O when reading many small files by consolidating them into larger shards.”
↩︎ Storing training and inference data: tables, stages and write-back“DataSink: Write data back to Snowflake tables.”
↩︎ Storing training and inference data: tables, stages and write-back“The DataConnector accelerates data loading by parallelizing the reads across multiple compute nodes.”
↩︎ Storing training and inference data: tables, stages and write-back“DataSource APIs: Load data from various file formats (CSV, Parquet, images, and more) from Snowflake stages.”
↩︎ Storing training and inference data: tables, stages and write-back“Unstructured | Other Staged Files | DataSource API | N/A”
↩︎ Exam trap 4 - 6.
“Both internal (i.e. Snowflake) and external (Amazon S3, Google Cloud Storage, or Microsoft Azure) stage references can include a path”
↩︎ Storing training and inference data: tables, stages and write-back“narrow the path to the most granular level that includes your data for improved data load performance”
↩︎ Storing training and inference data: tables, stages and write-back - 7.
“Datasets incur storage costs. Delete unused datasets to minimize costs.”
↩︎ Storing training and inference data: tables, stages and write-back