Skip to main content

Snowpark DataFrames API (Python)

If you are a Data Scientist or a Data Engineer who prefers Python (specifically PySpark or Pandas), writing complex SQL can be frustrating.

Snowpark solves this by providing a Python DataFrame API. You write Python code, but Snowpark automatically translates that Python code into optimized SQL and executes it inside Snowflake's Virtual Warehouse.

Topics Covered​

  • Establishing a Session
  • Creating DataFrames
  • Lazy Evaluation
  • Filtering and Sorting
  • Writing Data Back to Snowflake

1. Establishing a Session​

To use Snowpark, you must first connect to Snowflake. This is done by creating a Session object.

(Ensure you have the Snowpark library installed: pip install snowflake-snowpark-python)

from snowflake.snowpark import Session

# Define connection parameters (In production, load these securely from env variables!)
connection_parameters = {
"account": "xyz123.ap-south-1.aws",
"user": "arjun",
"password": "SuperSecretPassword123",
"role": "SYSADMIN",
"warehouse": "COMPUTE_WH",
"database": "BEEMS_RETAIL_DB",
"schema": "PUBLIC"
}

# Create the session
session = Session.builder.configs(connection_parameters).create()
print("Connected to Snowflake successfully!")

2. Creating DataFrames​

Once connected, you can create a DataFrame that points to an existing Snowflake table, or create one from raw SQL.

# Create a DataFrame pointing to a table
df_customers = session.table("CUSTOMERS")

# Create a DataFrame using raw SQL
df_orders = session.sql("SELECT ORDER_ID, AMOUNT FROM ORDERS WHERE STATUS = 'COMPLETED'")

3. The Magic of Lazy Evaluation​

When you define df_customers in the code above, Snowflake does not download any data to your laptop.

Snowpark uses Lazy Evaluation. It simply builds a computational graph (a SQL query) in the background. The query is only sent to Snowflake when you perform an Action.

Transformations (Lazy - Do not execute immediately):

  • .select(), .filter(), .join(), .group_by()

Actions (Eager - Execute immediately):

  • .show(): Prints the first 10 rows to the console.
  • .collect(): Downloads the results into your local Python memory as a list of Row objects.
  • .to_pandas(): Downloads the results into a Pandas DataFrame.
# This does NOT run any query
df_mumbai = df_customers.filter(df_customers["CITY"] == "Mumbai")

# THIS runs the query in Snowflake
df_mumbai.show()

4. Column Expressions and Filtering​

Snowpark provides a col() function to reference columns safely, and a lit() function to use literal values (like hardcoded strings or numbers).

from snowflake.snowpark.functions import col, lit

# Filtering data
df_active_mumbai = df_customers.filter(
(col("CITY") == "Mumbai") & (col("IS_ACTIVE") == lit(True))
)

# Selecting and creating new columns
df_discounted = df_orders.select(
col("ORDER_ID"),
col("AMOUNT"),
(col("AMOUNT") * 0.90).alias("DISCOUNTED_AMOUNT") # Apply a 10% discount
)

5. Sorting and Limiting​

You can easily sort your DataFrames to find top records.

# Find the top 5 highest value orders
df_top_orders = df_orders.sort(col("AMOUNT").desc()).limit(5)

df_top_orders.show()

Behind the scenes, Snowpark translates this exactly into ORDER BY AMOUNT DESC LIMIT 5 and pushes it to Snowflake.

6. Writing Data Back to Snowflake​

After you finish transforming your data in Python, you usually want to save it back into Snowflake as a new table for analysts to query.

# We have a clean DataFrame of discounted orders
# Save it as a new table in the database
df_discounted.write.mode("overwrite").save_as_table("CLEAN_ORDERS_DISCOUNTED")

The .write.save_as_table() method is an Action. It executes the query and creates the table directly in Snowflake without ever downloading the data to your local machine.

Summary​

  • The Snowpark Session connects your Python environment to Snowflake.
  • Lazy Evaluation ensures that data is only processed when an action like .show() or .collect() is called.
  • Use col() and lit() to manipulate columns programmatically.
  • Use .write.save_as_table() to push the transformed data back into Snowflake without moving data across the network.