Introduction to Snowpark
Snowpark is Snowflake's developer framework that enables you to write data pipelines and transformations in Python, Scala, or Java directly within Snowflake. Operations run lazily in Snowflake's engine, pushing transformations to the cloud for optimal performance.
π Why Snowpark?β
- Language Flexibility: Code in Python instead of pure SQL
- Performance: Lazy execution pushed to Snowflake's compute
- Security: Data stays within Snowflake, no egress
- Scalability: Handle large datasets without local limitations
π Prerequisitesβ
- Snowflake account with appropriate roles
- Basic Python knowledge
- Snowpark library:
pip install snowflake-snowpark-python
π Use Casesβ
- ETL Pipelines: Automate data workflows with Python logic
- Data Science: Prepare ML datasets without exporting data
- Real-time Analytics: Query and transform data on-the-fly
- Complex Reporting: Use Python constructs for advanced reports
π οΈ Working Example: Snowflake Python Worksheetβ
This is the standard pattern for Snowflake worksheetsβSnowflake automatically passes the session and invokes the main() handler.
SQL Setup (Run First in Snowflake)β
-- Create demo database and table
CREATE DATABASE IF NOT EXISTS DEMO_DB;
CREATE SCHEMA IF NOT EXISTS DEMO_DB.DEMO_SCHEMA;
CREATE TABLE IF NOT EXISTS DEMO_DB.DEMO_SCHEMA.CUSTOMERS (
ID INT,
NAME VARCHAR(50),
AGE INT,
CITY VARCHAR(50)
);
-- Insert sample data
INSERT INTO DEMO_DB.DEMO_SCHEMA.CUSTOMERS (ID, NAME, AGE, CITY) VALUES
(1, 'Alice Johnson', 28, 'New York'),
(2, 'Bob Smith', 35, 'Los Angeles'),
(3, 'Charlie Brown', 42, 'Chicago'),
(4, 'Diana Prince', 30, 'Houston'),
(5, 'Eve Adams', 25, 'Phoenix');
Python Worksheet Scriptβ
import snowflake.snowpark as snowpark
from snowflake.snowpark.functions import col, concat, lit
def main(session: snowpark.Session):
# Session is automatically provided by Snowflake
# Get current context
current_user = session.sql("SELECT CURRENT_USER()").collect()[0][0]
current_db = session.sql("SELECT CURRENT_DATABASE()").collect()[0][0]
print(f"β Connected as: {current_user} in {current_db}")
# Load table from current session
df = session.table("CUSTOMERS")
# Filter: age > 30
df_filtered = df.filter(col("AGE") > 30)
# Add derived column using concat() (correct Snowpark syntax)
df_result = df_filtered.with_column(
"AGE_GROUP",
concat(col("AGE").cast("string"), lit(" years old"))
)
# Display results
df_result.show()
return df_result
How to use:
- Switch to
DEMO_DB.DEMO_SCHEMAcontext - Create a Python Worksheet in Snowflake
- Copy the script above
- Click Run β session is passed automatically
- View results in Results tab
οΏ½ Real-time Pipeline: Medallion Architecture (Bronze β Silver β Gold)β
Demonstrate a multi-table transformation pipeline with JSON parsing and data enrichment.
SQL Setup (Medallion Tables)β
-- Create medallion architecture tables
CREATE DATABASE IF NOT EXISTS DEMO_DB;
CREATE SCHEMA IF NOT EXISTS DEMO_DB.DEMO_SCHEMA;
-- BRONZE: Raw ingestion layer (includes JSON)
CREATE TABLE IF NOT EXISTS DEMO_DB.DEMO_SCHEMA.BRONZE_ORDERS (
ORDER_ID INT,
RAW_DATA VARIANT, -- JSON data
INGESTED_AT TIMESTAMP DEFAULT CURRENT_TIMESTAMP()
);
-- SILVER: Cleaned, parsed layer
CREATE TABLE IF NOT EXISTS DEMO_DB.DEMO_SCHEMA.SILVER_ORDERS (
ORDER_ID INT,
CUSTOMER_NAME VARCHAR(100),
ORDER_AMOUNT DECIMAL(10, 2),
ORDER_DATE DATE,
PROCESSED_AT TIMESTAMP DEFAULT CURRENT_TIMESTAMP()
);
-- GOLD: Business layer (aggregated, enriched)
CREATE TABLE IF NOT EXISTS DEMO_DB.DEMO_SCHEMA.GOLD_CUSTOMER_SUMMARY (
CUSTOMER_NAME VARCHAR(100),
TOTAL_ORDERS INT,
TOTAL_SPENT DECIMAL(12, 2),
FIRST_ORDER_DATE DATE,
LAST_ORDER_DATE DATE,
CREATED_AT TIMESTAMP DEFAULT CURRENT_TIMESTAMP()
);
-- Insert sample data into BRONZE (including JSON with VARIANT type)
INSERT INTO DEMO_DB.DEMO_SCHEMA.BRONZE_ORDERS (ORDER_ID, RAW_DATA)
SELECT 1, PARSE_JSON('{"customer": "Alice Johnson", "amount": 150.50, "order_date": "2024-01-15"}')
UNION ALL
SELECT 2, PARSE_JSON('{"customer": "Bob Smith", "amount": 230.75, "order_date": "2024-01-16"}')
UNION ALL
SELECT 3, PARSE_JSON('{"customer": "Alice Johnson", "amount": 89.99, "order_date": "2024-01-17"}')
UNION ALL
SELECT 4, PARSE_JSON('{"customer": "Charlie Brown", "amount": 450.00, "order_date": "2024-01-18"}')
UNION ALL
SELECT 5, PARSE_JSON('{"customer": "Bob Smith", "amount": 175.25, "order_date": "2024-01-19"}');
Script 1: Bronze β Silver (Parse JSON)β
Run this script to parse JSON from BRONZE and load SILVER:
import snowflake.snowpark as snowpark
from snowflake.snowpark.functions import col, to_date
def main(session: snowpark.Session):
print(" SCRIPT 1: Bronze β Silver (JSON Parsing)")
# Load raw data from BRONZE
bronze_df = session.table("BRONZE_ORDERS")
# Parse JSON and extract fields
silver_df = bronze_df.select(
col("ORDER_ID"),
col("RAW_DATA")["customer"].cast("string").alias("CUSTOMER_NAME"),
col("RAW_DATA")["amount"].cast("decimal(10, 2)").alias("ORDER_AMOUNT"),
to_date(col("RAW_DATA")["order_date"]).alias("ORDER_DATE")
)
print("β Parsed data from BRONZE:")
silver_df.show()
# Write to SILVER table
silver_df.write.save_as_table("SILVER_ORDERS", mode="overwrite")
print("β Data loaded into SILVER_ORDERS table")
return silver_df
How to use:
- Set context to
DEMO_DB.DEMO_SCHEMA - Create Python Worksheet
- Copy Script 1
- Click Run β Silver table populated
Script 2: Silver β Gold (Transform & Aggregate)β
Run this script to aggregate SILVER data and load GOLD:
import snowflake.snowpark as snowpark
from snowflake.snowpark.functions import col, sum as spark_sum, count, min as spark_min, max as spark_max
def main(session: snowpark.Session):
print(" SCRIPT 2: Silver β Gold (Aggregation & Transformation)")
# Load cleaned data from SILVER
silver_df = session.table("SILVER_ORDERS")
# Aggregate by customer
gold_df = silver_df.group_by("CUSTOMER_NAME").agg(
count("ORDER_ID").alias("TOTAL_ORDERS"),
spark_sum("ORDER_AMOUNT").alias("TOTAL_SPENT"),
spark_min("ORDER_DATE").alias("FIRST_ORDER_DATE"),
spark_max("ORDER_DATE").alias("LAST_ORDER_DATE")
)
print("β Aggregated customer data:")
gold_df.show()
# Write to GOLD table
gold_df.write.save_as_table("GOLD_CUSTOMER_SUMMARY", mode="overwrite")
print("β Data loaded into GOLD_CUSTOMER_SUMMARY table")
return gold_df
How to use:
- Set context to
DEMO_DB.DEMO_SCHEMA - Create Python Worksheet
- Copy Script 2
- Click Run β Gold table populated
Real-time Pipeline Flowβ
βββββββββββββββββββ
β BRONZE_ORDERS β Raw ingestion (JSON in VARIANT)
β (Raw Layer) β
ββββββββββ¬βββββββββ
β
Script 1: Parse JSON
β
βΌ
βββββββββββββββββββ
β SILVER_ORDERS β Cleaned structured data
β (Clean Layer) β
ββββββββββ¬βββββββββ
β
Script 2: Aggregate
β
βΌ
βββββββββββββββββββββββββββ
β GOLD_CUSTOMER_SUMMARY β Business metrics
β (Business Layer) β
βββββββββββββββββββββββββββ
β° Automate with Snowflake Tasksβ
Run the medallion pipeline automatically on a schedule using Snowflake Tasks with Snowpark stored procedures.
SQL Setup: Create Stored Proceduresβ
-- Create stored procedure for Bronze β Silver
CREATE OR REPLACE PROCEDURE DEMO_DB.DEMO_SCHEMA.BRONZE_TO_SILVER()
RETURNS STRING
LANGUAGE PYTHON
RUNTIME_VERSION = '3.9'
PACKAGES = ('snowflake-snowpark-python')
HANDLER = 'main'
AS
$$
import snowflake.snowpark as snowpark
from snowflake.snowpark.functions import col, to_date
def main(session: snowpark.Session):
bronze_df = session.table("BRONZE_ORDERS")
silver_df = bronze_df.select(
col("ORDER_ID"),
col("RAW_DATA")["customer"].cast("string").alias("CUSTOMER_NAME"),
col("RAW_DATA")["amount"].cast("decimal(10, 2)").alias("ORDER_AMOUNT"),
to_date(col("RAW_DATA")["order_date"]).alias("ORDER_DATE")
)
silver_df.write.save_as_table("SILVER_ORDERS", mode="overwrite")
return "β Bronze β Silver completed"
$$;
-- Create stored procedure for Silver β Gold
CREATE OR REPLACE PROCEDURE DEMO_DB.DEMO_SCHEMA.SILVER_TO_GOLD()
RETURNS STRING
LANGUAGE PYTHON
RUNTIME_VERSION = '3.9'
PACKAGES = ('snowflake-snowpark-python')
HANDLER = 'main'
AS
$$
import snowflake.snowpark as snowpark
from snowflake.snowpark.functions import col, sum as spark_sum, count, min as spark_min, max as spark_max
def main(session: snowpark.Session):
silver_df = session.table("SILVER_ORDERS")
gold_df = silver_df.group_by("CUSTOMER_NAME").agg(
count("ORDER_ID").alias("TOTAL_ORDERS"),
spark_sum("ORDER_AMOUNT").alias("TOTAL_SPENT"),
spark_min("ORDER_DATE").alias("FIRST_ORDER_DATE"),
spark_max("ORDER_DATE").alias("LAST_ORDER_DATE")
)
gold_df.write.save_as_table("GOLD_CUSTOMER_SUMMARY", mode="overwrite")
return "β Silver β Gold completed"
$$;
SQL Setup: Create Tasks with Dependenciesβ
-- Create TASK 1: Bronze β Silver (runs every 10 minutes)
CREATE OR REPLACE TASK DEMO_DB.DEMO_SCHEMA.TASK_BRONZE_TO_SILVER
WAREHOUSE = COMPUTE_WH
SCHEDULE = '10 MINUTE'
AS
CALL DEMO_DB.DEMO_SCHEMA.BRONZE_TO_SILVER();
-- Create TASK 2: Silver β Gold (depends on Task 1)
CREATE OR REPLACE TASK DEMO_DB.DEMO_SCHEMA.TASK_SILVER_TO_GOLD
WAREHOUSE = COMPUTE_WH
AFTER DEMO_DB.DEMO_SCHEMA.TASK_BRONZE_TO_SILVER
AS
CALL DEMO_DB.DEMO_SCHEMA.SILVER_TO_GOLD();
-- Enable root task (child tasks auto-enable)
ALTER TASK DEMO_DB.DEMO_SCHEMA.TASK_BRONZE_TO_SILVER RESUME;
-- Verify task status
SELECT TASK_NAME, STATE, SCHEDULE, NEXT_SCHEDULED_TIME
FROM TABLE(INFORMATION_SCHEMA.TASK_HISTORY());
Task Automation Flowβ
βββββββββββββββββββββββββββββββββββ
β Scheduled: Every 10 minutes β
β TASK_BRONZE_TO_SILVER β
β (Runs: BRONZE_TO_SILVER proc) β
ββββββββββββββββββ¬βββββββββββββββββ
β
(Task Dependency)
β
βΌ
βββββββββββββββββββββββββββββββββββ
β Triggered after Task 1 completesβ
β TASK_SILVER_TO_GOLD β
β (Runs: SILVER_TO_GOLD proc) β
βββββββββββββββββββββββββββββββββββ
Key Points:
- β Tasks run on schedule (10 minutes in this example)
- β
Task 2 depends on Task 1 completion (
AFTERclause) - β No manual intervention needed
- β Stored procedures encapsulate Snowpark logic
- β
Monitor task history with
INFORMATION_SCHEMA.TASK_HISTORY()
π Task Management Commandsβ
-- View all tasks
SHOW TASKS IN SCHEMA DEMO_DB.DEMO_SCHEMA;
-- View task history
SELECT * FROM TABLE(INFORMATION_SCHEMA.TASK_HISTORY(TASK_NAME => 'TASK_BRONZE_TO_SILVER'));
-- Suspend a task
ALTER TASK DEMO_DB.DEMO_SCHEMA.TASK_BRONZE_TO_SILVER SUSPEND;
-- Resume a task
ALTER TASK DEMO_DB.DEMO_SCHEMA.TASK_BRONZE_TO_SILVER RESUME;
-- Drop a task
DROP TASK DEMO_DB.DEMO_SCHEMA.TASK_BRONZE_TO_SILVER;
π Key Conceptsβ
| Concept | Details |
|---|---|
| Session | Automatically passed to main(session) in worksheets |
| Lazy Execution | Operations queued until .show() or .collect() |
| Concatenation | Use concat(col(), lit()) β NOT string + operator |
| JSON Parsing | Use bracket notation: col("RAW_DATA")["field_name"] |
| Type Conversion | Cast with .cast("data_type") or to_date(), to_timestamp() |
| Aggregation | Use group_by() with aggregate functions: sum(), count(), min(), max() |
| Tasks | Automate stored procedures on schedule with dependencies |
| Return Value | Return DataFrame to display in Results tab |
β οΈ Common Mistakesβ
- β String concatenation with
+β Useconcat()or|| - β Creating a new session β Session is provided automatically
- β Hardcoding credentials β Use current session
- β Not returning a DataFrame β Won't display in Results
π More Resourcesβ
π― Summaryβ
Snowpark lets you write Python code that runs directly in Snowflake. The main(session) pattern in Python worksheets is the standardβno session creation, no hardcoding. Just write your transformations and Snowflake executes them efficiently.