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
In a nutshell
  • group_by().agg() for aggregations; Window.partition_by().order_by() with row_number, lag, rank for window logic.
  • join types: inner, left, right, full, cross; union removes duplicates, union_all keeps them.
  • with_column, with_column_renamed, drop, when().otherwise(), na.fill for 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.

Source DataFramesjoinJoinedfilter and with_columnCleanedgroup_by and aggAggregatedwrite.save_as_tableResult table

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, median and more. Import with an alias such as sum_ so you do not hide Python's built in sum.
  • 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, and unpivot reverses it.
  • Filtering after aggregation (the HAVING step) is simply filter on 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

OperationMethod
Inner, left, right, full joindf1.join(df2, df1["id"] == df2["id"], "left") (how is inner, left, right, full, cross)
Join on same named columndf1.join(df2, "id") or using_columns
Stack with duplicatesunion_all
Stack and remove duplicatesunion
Rows in one not the other, or rows in bothexcept_, intersect
Remove duplicatesdistinct() 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

TaskMethod
Add or replace a columnwith_column("new", expr); several at once with with_columns([...], [...])
Renamewith_column_renamed("old", "new") or col("x").alias("y")
Removedrop("col")
Conditional logicwhen(cond, value).otherwise(other)
Constantslit(0)
Castcol("x").cast("int")
Missing valuesdf.na.fill(...), df.na.drop(), df.na.replace(...)
Semi-structuredcol("v")["key"], flatten
Sample rowsdf.sample(frac=0.1)
Call your own UDFcall_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.

GoalCallWhere it runs
Rows to the client as Row objectscollect()Executes, copies to client
Print a sampleshow(n)Executes with a limit
Pandas DataFrameto_pandas()Executes, copies all rows to client memory
Stream large resultsto_local_iterator()Executes, returns rows in batches
Persist in Snowflakewrite.save_as_table("t", mode="overwrite")Entirely server side
Unload to stagewrite.copy_into_location(...)Entirely server side
Reuse expensive resultcache_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.

Watch out

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.

Exam tip

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() with row_number, lag, rank for window logic.
  • join types: inner, left, right, full, cross; union removes duplicates, union_all keeps them.
  • with_column, with_column_renamed, drop, when().otherwise(), na.fill for reshaping.
  • collect, to_pandas move data to the client; write.save_as_table keeps 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.