Skip to main content

Joins, Grouping & Aggregations in Snowpark

Transforming data usually involves bringing multiple datasets together, summarizing them, and finding advanced analytical patterns. Snowpark provides a clean, Pythonic API for executing complex SQL operations like JOIN, GROUP BY, and WINDOW functions directly on DataFrames.

Topics Covered​

  • Joining DataFrames
  • Grouping and Aggregating
  • Window Functions
  • Set Operations (Union)

1. Joining DataFrames​

To demonstrate, let's assume we have two DataFrames: df_orders (containing transaction data) and df_customers (containing customer demographic data for a retail bank in India).

from snowflake.snowpark.functions import col

# df_orders: [ORDER_ID, CUST_ID, AMOUNT, STATUS]
# df_customers: [CUST_ID, NAME, CITY]

# 1. Standard Inner Join
# Join condition is simply matching the "CUST_ID" column
df_inner = df_orders.join(df_customers, "CUST_ID")

# 2. Left Outer Join
df_left = df_orders.join(df_customers, "CUST_ID", join_type="left")

# 3. Multi-Column or Complex Join Conditions
# If the column names differ, or you need to join on multiple conditions
df_complex_join = df_orders.join(
df_customers,
(df_orders["CUST_ID"] == df_customers["CUST_ID"]) & (df_orders["STATUS"] == "COMPLETED"),
join_type="inner"
)

2. Grouping and Aggregating​

Aggregations allow you to summarize your joined data. You use the .group_by() method followed by .agg().

You must import the specific aggregation functions from snowflake.snowpark.functions.

from snowflake.snowpark.functions import sum, avg, count, max

# Find total revenue and average order value per City
df_city_summary = df_left.group_by("CITY").agg(
sum("AMOUNT").alias("TOTAL_REVENUE"),
avg("AMOUNT").alias("AVERAGE_ORDER_VALUE"),
count("ORDER_ID").alias("TOTAL_ORDERS"),
max("AMOUNT").alias("LARGEST_SINGLE_ORDER")
)

# Remember, nothing executes until you call an action!
df_city_summary.show()

3. Window Functions​

Window functions are incredibly powerful in Data Engineering for answering questions like "What was this customer's previous order amount?" or "Rank these customers by total spend within their city."

In Snowpark, you define a WindowSpec object to describe the partition and ordering, and then apply it to a column using .over().

from snowflake.snowpark import Window
from snowflake.snowpark.functions import rank, lag

# Question: Rank customers in Mumbai based on their transaction amount.

# 1. Define the Window (Partition by City, Order by Amount Descending)
window_spec = Window.partition_by("CITY").order_by(col("AMOUNT").desc())

# 2. Apply the Rank function over the Window
df_ranked = df_left.with_column(
"CITY_SPEND_RANK",
rank().over(window_spec)
)

# Question: Compare current order amount to the customer's previous order amount
customer_window = Window.partition_by("CUST_ID").order_by("ORDER_DATE")

df_lagged = df_left.with_column(
"PREVIOUS_ORDER_AMOUNT",
lag(col("AMOUNT"), 1).over(customer_window) # Get the value from 1 row prior
)

4. Set Operations (Union and Intersect)​

If you have two DataFrames with identical schemas (e.g., historical data and current data), you can combine them vertically using set operations.

# df_historical: Orders from 2022
# df_current: Orders from 2023

# UNION ALL: Combines all rows, keeping duplicates
df_all_orders = df_historical.union_all(df_current)

# UNION: Combines rows, but removes duplicates (slower due to deduplication overhead)
df_unique_orders = df_historical.union(df_current)

# INTERSECT: Only returns rows that exist in BOTH DataFrames
df_common_orders = df_historical.intersect(df_current)

5. Pivoting Data​

Pivoting allows you to rotate rows into columns. For example, if you have a DataFrame showing sales per region per month, you can pivot it so that each month becomes its own column.

# df_sales: [REGION, MONTH, AMOUNT]

# Pivot the data so "Jan", "Feb", and "Mar" become their own columns containing the sum of AMOUNT
df_pivoted = df_sales.pivot("MONTH", ["Jan", "Feb", "Mar"]).sum("AMOUNT")

Summary​

  • Use .join() to combine DataFrames horizontally. You can specify complex conditions and join types (left, right, full outer).
  • Use .group_by().agg() to calculate sums, averages, and counts.
  • Use Window.partition_by() to execute advanced analytical functions like rank() and lag().
  • Use .union_all() to combine DataFrames vertically.