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()andlit()to manipulate columns programmatically. - Use
.write.save_as_table()to push the transformed data back into Snowflake without moving data across the network.