Data Transformation · Topic 14 of 14
Snowpark Transformations and DataFrame Manipulation
Transform data with Snowpark DataFrames: aggregations, joins, window functions, new columns, null handling, pandas conversion and saving to tables.
- 4 min
group_by().agg()for aggregations;Window.partition_by().order_by()withrow_number,lag,rankfor window logic.jointypes:inner,left,right,full,cross;unionremoves duplicates,union_allkeeps them.with_column,with_column_renamed,drop,when().otherwise(),na.fillfor reshaping.
What it is
Once data is in a Snowpark DataFrame, you reshape it with transformation methods: aggregate it, join it, add or rename columns, clean nulls, and write the result. Every method returns a new DataFrame and stays lazy, so a long pipeline is still one SQL statement when it finally runs.
How it works
Aggregations
group_by returns a grouped object; agg or a shortcut method then computes the measures.
from snowflake.snowpark.functions import col, sum as sum_, avg, count, max as max_
sales = (orders
.group_by("region")
.agg(sum_("amount").alias("total"),
avg("amount").alias("avg_amount"),
count("order_id").alias("orders")))
- Aggregate functions come from
snowflake.snowpark.functions:sum,avg,min,max,count,count_distinct,listagg,medianand more. Import with an alias such assum_so you do not hide Python's built insum. - Without
group_by,df.agg(...)aggregates the whole DataFrame. group_by(...).count()is a shortcut for a row count per group.pivot("col", [values]).agg(...)turns row values into columns, andunpivotreverses it.- Filtering after aggregation (the HAVING step) is simply
filteron the aggregated DataFrame.
Window functions
Window functions compute across related rows without collapsing them.
from snowflake.snowpark import Window
from snowflake.snowpark.functions import row_number, sum as sum_
w = Window.partition_by("customer_id").order_by(col("order_date").desc())
latest = (orders.with_column("rn", row_number().over(w))
.filter(col("rn") == 1))
Use Window.partition_by(...).order_by(...) with row_number, rank, dense_rank, lag, lead or an aggregate. Add .rows_between(...) for a running total or moving average.
Joins and set operations
| Operation | Method |
|---|---|
| Inner, left, right, full join | df1.join(df2, df1["id"] == df2["id"], "left") (how is inner, left, right, full, cross) |
| Join on same named column | df1.join(df2, "id") or using_columns |
| Stack with duplicates | union_all |
| Stack and remove duplicates | union |
| Rows in one not the other, or rows in both | except_, intersect |
| Remove duplicates | distinct() or drop_duplicates("col") |
When both sides have columns with the same name, use lsuffix and rsuffix or alias the DataFrames, otherwise the duplicate column names are ambiguous.
Manipulating DataFrame columns and rows
| Task | Method |
|---|---|
| Add or replace a column | with_column("new", expr); several at once with with_columns([...], [...]) |
| Rename | with_column_renamed("old", "new") or col("x").alias("y") |
| Remove | drop("col") |
| Conditional logic | when(cond, value).otherwise(other) |
| Constants | lit(0) |
| Cast | col("x").cast("int") |
| Missing values | df.na.fill(...), df.na.drop(), df.na.replace(...) |
| Semi-structured | col("v")["key"], flatten |
| Sample rows | df.sample(frac=0.1) |
| Call your own UDF | call_udf("my_udf", col("x")) |
cleaned = (orders
.with_column("amount_usd", col("amount") * col("fx_rate"))
.with_column("size", when(col("amount") > 1000, lit("large")).otherwise(lit("small")))
.na.fill({"region": "UNKNOWN"})
.drop("fx_rate"))
Getting results out
Some calls only describe work and some execute it. The table separates calls that copy data to the client from calls that keep everything inside Snowflake, which is the distinction the exam usually tests.
| Goal | Call | Where it runs |
|---|---|---|
Rows to the client as Row objects | collect() | Executes, copies to client |
| Print a sample | show(n) | Executes with a limit |
| Pandas DataFrame | to_pandas() | Executes, copies all rows to client memory |
| Stream large results | to_local_iterator() | Executes, returns rows in batches |
| Persist in Snowflake | write.save_as_table("t", mode="overwrite") | Entirely server side |
| Unload to stage | write.copy_into_location(...) | Entirely server side |
| Reuse expensive result | cache_result() | Writes a temporary table so later steps do not recompute |
save_as_table modes are append, overwrite, truncate, errorifexists (default) and ignore. Pandas data can also come back in with session.create_dataframe(pandas_df) or write_pandas.
to_pandas() loads every row into memory. For big data, transform and aggregate in Snowpark first, or write the result to a table and let downstream tools read it.
If the same intermediate DataFrame feeds several later actions, cache_result() avoids running its SQL each time.
Common mistakes
Modifying a DataFrame in place. Methods return new DataFrames; assign the result.
Shadowing Python sum and max. Import the Snowpark versions with aliases.
Ambiguous column names after a join. Alias or use suffixes before selecting.
Using to_pandas() too early. Pull to the client only small, final results.
What to remember for the exam
group_by().agg()for aggregations;Window.partition_by().order_by()withrow_number,lag,rankfor window logic.jointypes:inner,left,right,full,cross;unionremoves duplicates,union_allkeeps them.with_column,with_column_renamed,drop,when().otherwise(),na.fillfor reshaping.collect,to_pandasmove data to the client;write.save_as_tablekeeps it in Snowflake.cache_result()materializes a DataFrame to a temporary table.
Check yourself
Which Snowpark call computes total sales per region?
df.group_by("region").agg(sum_("amount")), with sum imported from snowflake.snowpark.functions.
Difference between union and union_all in Snowpark?
union removes duplicate rows, while union_all keeps all rows.
How do you save a transformed DataFrame without pulling data to the client?
Use write.save_as_table, which runs entirely inside Snowflake.
Topic 14 of 14 in Data Transformation
Test yourself on Data Transformation
The full Snowflake DEA-C02 question bank has 709 questions, with timed practice exams and an explanation for every answer.
This study guide is independent and is not affiliated with, endorsed by, or sponsored by Snowflake. Vendor names, certification names and exam codes are trademarks of their respective owners and are used for identification only.