What you will be able to do
- Select and filter rows with Column objects built from col
- Chain transformations and join DataFrames, telling apart same-named columns
- Group a DataFrame and run aggregations with groupBy
- Pick the right action method to evaluate a DataFrame and return results
1.Selecting and filtering with Column objects
After you have a DataFrame, you shape it with transformation methods, and most of those methods take columns. In Python you refer to a column by making a Column object with col, which you import from the snowflake.snowpark.functions module. Java uses the Functions.col static method, and Scala imports col from com.snowflake.snowpark.functions.
# Import the col function from the functions module.
from snowflake.snowpark.functions import col
df_product_info = session.table("sample_product_data").select(col("id"), col("name"))
df_product_info.show()Column objects can also be combined into expressions, which is how you write filters and aliases. Each language spells comparison its own way. Java calls Functions.col("id").equal_to(Functions.lit(12)) as the equivalent of WHERE id = 12. Scala writes col("id") === 20. Arithmetic works as well: Scala's df.filter((col("a") + col("b")) < 10) stands for WHERE a + b < 10. The table below shows how common SQL clauses map to Snowpark methods. Note that filter and where are interchangeable.
| SQL | Snowpark method | Example |
|---|---|---|
| SELECT id, name | DataFrame.select | df.select(col("id"), col("name")) |
| SELECT id AS item_id | Column.as, Column.alias or Column.name | df.select(col("id").as("item_id")) |
| WHERE id = 1 | DataFrame.filter or DataFrame.where | df.filter((col("id") === 1)) |
| ORDER BY category_id | DataFrame.sort | df.sort(col("category_id")) |
Column names follow Snowflake identifier rules. Snowflake treats an unquoted name you pass as upper case, so col("id123") and col("ID123") point to the same column.
Checkpoint 1 of 6· Check yourself
A Snowpark Java developer writes df.select(Functions.col("id123")) while a colleague writes df.select(Functions.col("ID123")). What is the result?
Snowflake treats specified identifiers as upper case, so both calls resolve to ID123. lit() is for literal values, not column references.
“When you specify a name, Snowflake considers the name to be in upper case.”Source: docs.snowflake.com
Checkpoint 2 of 6· Exam question
A team is deciding between writing a transformation pipeline as raw SQL executed through a database driver versus writing it with the Snowpark Python DataFrame API. Which statement correctly describes a core architectural property of Snowpark that should inform this decision?
Correct answer: A — Snowpark pushes DataFrame operations down to execute inside Snowflake's compute engine, so the underlying data never has to move to the client machine running the Python code.
- A. This is correct: Snowpark's core architectural property is pushdown. DataFrame transformations are translated into SQL and executed inside Snowflake's compute engine, so the data being processed never has to be moved to the machine running the client code.
- B. This describes the opposite of Snowpark's design. Materializing the full result locally before transforming it is what client-side pandas over a driver does; Snowpark instead keeps computation server-side and only returns data when an action is called.
- C. Snowpark does not require a separate standing compute cluster outside Snowflake. DataFrame operations are executed by a Snowflake virtual warehouse like any other query, not by an external always-on service mirroring table data.
- D. Snowpark does not compile code into a permanently stored binary that replaces warehouse compute. Each action still triggers a SQL statement that a virtual warehouse executes; there is no binary artifact substituting for that compute.
2.Chaining transformations and joining DataFrames
Transformation methods never change the DataFrame you call them on. Each call returns a new DataFrame that carries the extra step, and the original stays as it was. That is why chaining works: filter(...).select(...).sort(...) calls each method on the DataFrame returned by the one before it. None of these calls touches the data. The documentation says the transformation methods "simply specify how the SQL statement should be constructed."
Joins work the same way. Pass a second DataFrame and a join condition built from Column objects. When both sides have a column with the same name, a bare col("id") would be ambiguous. Use the col method on each DataFrame object instead, such as df_lhs.col("id"), to say which side you mean. The Python example below joins a table to itself to link each product to its parent. copy clones the DataFrame for the right-hand side.
from copy import copy
# Create a DataFrame object for the "sample_product_data" table for the left-hand side of the join.
df_lhs = session.table("sample_product_data")
# Clone the DataFrame object to use as the right-hand side of the join.
df_rhs = copy(df_lhs)
# Create a DataFrame that joins the two DataFrames
# for the "sample_product_data" table on the
# "id" and "parent_id" columns.
df_joined = df_lhs.join(df_rhs, df_lhs.col("id") == df_rhs.col("parent_id"))
df_joined.count()Scala adds a shortcut: DataFrame.apply also returns a Column, so df("column_name") means the same as df.col("column_name").
Checkpoint 3 of 6· Check yourself
In Snowpark Scala, df is built from session.table("sample_product_data"). You run val df2 = df.filter(col("id") === 1). What is true afterwards?
Transformation methods return a new DataFrame and leave the original alone. They also fetch no data. Only an action does that.
“Each method returns a new DataFrame object that has been transformed. (The method does not affect the original DataFrame object.)”Source: docs.snowflake.com
3.Aggregating with groupBy
Aggregation takes two steps. groupBy names the grouping columns and returns a RelationalGroupedDataFrame. That object is not a finished DataFrame, so calling an aggregation on it is the second step. The Java quick reference uses SELECT category_id, count(*) FROM sample_product_data GROUP BY category_id as its example, and the Snowpark version is below. The documentation available for this lesson gives the aggregation example in Java only. It does not show the Python spelling, so check the Python API reference for the exact method names.
DataFrame df = session.table("sample_product_data"); DataFrame dfCountPerCategory = df.groupBy(Functions.col("category_id")).count(); dfCountPerCategory.show();Running totals and other per-row aggregates that need a partition and an ordering use window functions instead of groupBy. Build a WindowSpec with the Window object, for example Window.partitionBy(Functions.col("category_id")).orderBy(Functions.col("product_date")). This is the Snowpark form of SQL's <function> OVER ... PARTITION BY ... ORDER BY.
Checkpoint 4 of 6· Check yourself
You need the equivalent of SUM(amount) OVER (PARTITION BY category_id ORDER BY product_date) in Snowpark Java. What do you build?
groupBy collapses rows into one per group, but an OVER clause keeps every row. Snowpark expresses that with a WindowSpec built from the Window object.
“To call a window function, use the Window object methods to build a WindowSpec object”Source: docs.snowflake.com
Checkpoint 5 of 6· Exam question
A data engineer needs to filter a Snowpark DataFrame named `orders_df` down to rows where the `order_status` column equals `'SHIPPED'` and the `order_total` column is greater than 100. Which approach correctly expresses this filter using the Snowpark Python API?
Correct answer: A — ```python filtered_df = orders_df.filter((col("order_status") == "SHIPPED") & (col("order_total") > 100)) ```
- A. Using the `col()` function to reference columns and combining conditions with the `&` operator inside a `filter()` call is the correct Snowpark pattern for a multi-condition WHERE-style filter, and it returns a new lazily evaluated DataFrame.
- B. Snowpark's `where()` method (an alias for `filter()`) takes a Column expression built from `col()` comparisons, not a raw string with a `parse_string` keyword argument; that parameter and calling convention do not exist in the API.
- C. `select()` projects columns and does not accept keyword arguments like `order_status` or `order_total` to restrict rows, and `limit()` only caps the row count returned; neither method performs conditional row filtering.
- D. `group_by()` aggregates rows into groups and does not have a `having()` method that takes a raw filter string in this form; grouping is the wrong tool for filtering individual order rows before any aggregation is needed.
Sources5
4.Actions: evaluating the DataFrame
Each step so far, including select, filter, join and groupBy, has only added to the query description. An action method is what runs it. The actions differ in what they hand back, and the exam asks you to pick the right one for the situation.
| Action | What it does |
|---|---|
| DataFrame.collect() | Evaluates the DataFrame and returns the dataset as an Array of Row objects |
| DataFrame.toLocalIterator() | Returns an Iterator of Row objects, so a large result is not loaded into memory all at once |
| DataFrame.count() | Evaluates the DataFrame and returns the number of rows |
| DataFrame.show() | Prints the rows to the console, 10 rows by default |
| DataFrame.cacheResult() | Runs the query and stores the results in a temporary table |
| DataFrame.write().saveAsTable() | Saves the DataFrame's data to the specified table |
Two details trip people up. show() is a preview, not a full dump: by default it prints 10 rows. And collect() brings the whole result set into client memory, which is risky for a large result. For that case the documentation points to toLocalIterator(). In a Snowsight Python worksheet you don't call show() at all. You return the DataFrame so that it appears as a table.
Checkpoint 6 of 6· Check yourself
A Snowpark Java job has to process a large aggregated result row by row on the client without running out of memory. Which action fits?
toLocalIterator() returns rows through an iterator rather than one array, so the full result is never held in memory at once. show() prints only 10 rows by default.
“If the result set is large, use this method to avoid loading all the results into memory at once.”Source: docs.snowflake.com
Exam traps
Each one states something that sounds right. Open it to see what is actually true.
1.df.filter(...) narrows df itself, so later code that uses df sees only the filtered rows.Why is that wrong?
Every transformation returns a new DataFrame and leaves the original untouched. Assign the result, or chain the calls.
2.DataFrame.show() prints the entire result set, so it can be used to inspect every row.Why is that wrong?
show() evaluates the DataFrame but prints 10 rows by default. Use collect() or toLocalIterator() to get every row.
Covered in Actions: evaluating the DataFrame
Practise it for real
Create the sample table from Snowpark Python, then build a projection that runs only when you call an action
1.Run session.sql('CREATE OR REPLACE TABLE sample_product_data (id INT, parent_id INT, category_id INT, name VARCHAR, serial_number VARCHAR, key INT, "3rd" INT)').collect()
Why: session.sql returns a DataFrame, and collect() is the action that makes the DDL actually run
You should see: [Row(status='Table SAMPLE_PRODUCT_DATA successfully created.')]
2.Run the documented INSERT INTO sample_product_data VALUES ... statement through session.sql(...).collect()
Why: This loads the 12 sample products that the DataFrame examples query
You should see: [Row(number of rows inserted=12)]
3.Run session.sql("SELECT count(*) FROM sample_product_data").collect()
Why: Confirms the table holds the expected rows before you start transforming it
You should see: [Row(COUNT(*)=12)]
4.Import col and build df = session.table("sample_product_data").select(col("id"), col("name")) without calling an action
Why: Shows lazy evaluation: the DataFrame is defined, but no query has run yet
You should see: No result rows come back. The variable holds a query description.
5.Call df.show()
Why: show() is an action, so it sends the generated SELECT to Snowflake
You should see: A two-column ID/NAME grid with the first 10 products, because show() defaults to 10 rows
Stuck? Get a nudge
If you are in a Python worksheet, return df instead of calling show().
Sources
Every claim above is drawn from one of these pages, quoted as it was written on the date shown.
- 1.
“To refer to a column, create a Column object by calling the col function in the snowflake.snowpark.functions module.”
↩︎ Selecting and filtering with Column objects“To return the DataFrame as a table in a Python worksheet use return instead of show()”
↩︎ Actions: evaluating the DataFrame - 2.
“To filter data, use DataFrame.filter or DataFrame.where.”
↩︎ Selecting and filtering with Column objects - 3.
“When you specify a name, Snowflake considers the name to be in upper case.”
↩︎ Selecting and filtering with Column objects“The transformation methods simply specify how the SQL statement should be constructed.”
↩︎ Chaining transformations and joining DataFrames“you can use the col method in each DataFrame object to refer to a column in that object”
↩︎ Chaining transformations and joining DataFrames“Evaluates the DataFrame and returns the number of rows.”
↩︎ Actions: evaluating the DataFrame“Each method returns a new DataFrame object that has been transformed. (The method does not affect the original DataFrame object.)”
↩︎ Exam trap 1“Evaluates the DataFrame and prints the rows to the console. Note that this method limits the number of rows to 10 (by default).”
↩︎ Exam trap 2“If the result set is large, use this method to avoid loading all the results into memory at once.”
↩︎ Checkpoint - 4.
“the DataFrame.apply method accepts a column name as input and returns a Column object.”
↩︎ Chaining transformations and joining DataFrames“Each method returns a new DataFrame object that has been transformed. (The method does not affect the original DataFrame object.)”
↩︎ Checkpoint - 5.
“To group data, use groupBy. This returns a RelationalGroupedDataFrame object, which you can use to perform the aggregations.”
↩︎ Aggregating with groupBy“To call a window function, use the Window object methods to build a WindowSpec object”
↩︎ Aggregating with groupBy