Skip to content
Tutorials Center
My learning

Topics

Lesson 34 of 38 ยท Snowpark and Cost

Snowpark DataFrames in Python

Learn Snowpark for Python: sessions, lazy DataFrames that push work down to Snowflake SQL, col and functions, joins, aggregation, writing tables and when to use pandas.

Snowpark is a set of libraries and a runtime that lets you write data pipelines and machine learning code in Python, Java or Scala and run them inside Snowflake, next to the data. Instead of pulling a billion rows to your laptop, you describe the work with a DataFrame; Snowpark translates it to SQL and Snowflake's warehouse does the processing. The data never leaves the platform.

Snowpark needs a live Snowflake connection, so the real API is shown in read-only blocks. To let you practice the thinking, the runnable examples use a tiny lazy DataFrame written in Python that builds SQL, plus SQLite to execute it. It is a teaching stand-in, not the Snowpark library.

A session and a first DataFrame

Everything starts with a Session:

Python
from snowflake.snowpark import Session
from snowflake.snowpark.functions import col, sum as sum_, avg, count, lit

session = Session.builder.configs({
    "account": "myorg-myaccount",
    "user": "ETL_SERVICE",
    "private_key_file": "rsa_key.p8",      # key-pair authentication
    "role": "ANALYST",
    "warehouse": "REPORTING_WH",
    "database": "SALES",
    "schema": "PUBLIC",
}).create()

orders = session.table("ORDERS")           # a DataFrame; nothing has run yet

Inside a Snowflake Notebook or a stored procedure, a session already exists as get_active_session().

Lazy evaluation

DataFrame operations such as filter, select, join and group_by do not execute. They only add to a plan. Work happens when you call an action: collect(), show(), count(), to_pandas(), or you write the result with save_as_table.

Python
big_emea = (
    orders
    .filter((col("REGION") == "EMEA") & (col("AMOUNT") > 100))
    .select("ORDER_ID", "CUSTOMER_ID", "AMOUNT")
    .sort(col("AMOUNT").desc())
)

print(big_emea.queries)        # the generated SQL, still not run
rows = big_emea.limit(10).collect()   # now Snowflake runs it

Laziness lets Snowpark combine all steps into one SQL statement that Snowflake optimizes, and lets you inspect or test the SQL with .queries or explain().

Python A lazy DataFrame that builds SQL (stand-in)

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Four method calls produced one statement and no database work. That is the model behind Snowpark.

SQL Executing the generated SQL ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Columns, expressions, joins and aggregates

Python
from snowflake.snowpark.functions import col, sum as sum_, avg, when, iff, upper

enriched = (
    orders.join(session.table("CUSTOMERS"),
                orders["CUSTOMER_ID"] == session.table("CUSTOMERS")["ID"])
          .with_column("IS_LARGE", when(col("AMOUNT") > 500, lit(True)).otherwise(lit(False)))
          .with_column("REGION_UP", upper(col("REGION")))
)

summary = (
    enriched.group_by("REGION_UP")
            .agg(sum_("AMOUNT").alias("REVENUE"),
                 avg("AMOUNT").alias("AVG_ORDER"),
                 count("*").alias("ORDERS"))
            .sort("REVENUE", ascending=False)
)
summary.show()

Notes on names: Snowpark follows Snowflake's identifier rules, so unquoted names are upper-case. col("amount") and col("AMOUNT") are the same; use double quotes for exact-case names (col('"Amount"')).

The same logic in SQL, which you can run on the shop data:

SQL Group and aggregate in SQL ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Writing results and moving data

Python
summary.write.mode("overwrite").save_as_table("MART.REVENUE_BY_REGION")   # runs in Snowflake

df = session.create_dataframe([[1, "a"], [2, "b"]], schema=["ID", "NAME"])   # small local data
pdf = summary.to_pandas()                  # pulls rows to the client: only for small results
spdf = session.create_dataframe(pdf)       # push a pandas frame back

Rule of thumb: keep heavy work in Snowpark DataFrames or SQL; use to_pandas() only on results small enough for client memory. The Snowpark pandas API (modin.pandas) goes further and runs pandas-style code as SQL in Snowflake, so you can use familiar pandas syntax at warehouse scale.

Where Snowpark code runs

  1. On your machine, connected to Snowflake: the DataFrame code runs locally, but all data work runs in the warehouse.
  2. Inside Snowflake: as a Python stored procedure, a UDF, or a Notebook, so the whole program runs near the data (see UDFs and stored procedures).
  3. Snowpark-optimized warehouses provide much more memory per node, suited to model training and other memory-hungry Python, where a standard warehouse would spill.

Machine learning builds on this: Snowflake ML provides preprocessing, modeling (scikit-learn-style and XGBoost) and a model registry, all running in warehouses or container services.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What happens when you call .filter() on a Snowpark DataFrame?

    DataFrames are lazy; only actions such as collect(), show() or save_as_table() execute.
  2. 2. Where is the heavy data processing done in Snowpark?

    Snowpark pushes the work down to Snowflake, so data stays in the platform.
  3. 3. Which call pulls the result to the client as a pandas DataFrame?

    to_pandas() downloads rows to the client; keep results small.
  4. 4. A training job runs out of memory on a standard warehouse. What can help?

    Snowpark-optimized warehouses give much more memory per node.

Summary

  • Snowpark runs Python (also Java, Scala) DataFrame code as SQL inside Snowflake.
  • DataFrames are lazy; actions trigger execution; .queries shows the SQL.
  • Use col, functions, join, group_by, agg, save_as_table; limit to_pandas() to small results.
  • Snowpark-optimized warehouses and Snowflake ML support heavier Python workloads.

Next: UDFs and stored procedures