Skip to main content

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:

  1. Switch to DEMO_DB.DEMO_SCHEMA context
  2. Create a Python Worksheet in Snowflake
  3. Copy the script above
  4. Click Run β€” session is passed automatically
  5. 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:

  1. Set context to DEMO_DB.DEMO_SCHEMA
  2. Create Python Worksheet
  3. Copy Script 1
  4. 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:

  1. Set context to DEMO_DB.DEMO_SCHEMA
  2. Create Python Worksheet
  3. Copy Script 2
  4. 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 (AFTER clause)
  • βœ… 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​

ConceptDetails
SessionAutomatically passed to main(session) in worksheets
Lazy ExecutionOperations queued until .show() or .collect()
ConcatenationUse concat(col(), lit()) β€” NOT string + operator
JSON ParsingUse bracket notation: col("RAW_DATA")["field_name"]
Type ConversionCast with .cast("data_type") or to_date(), to_timestamp()
AggregationUse group_by() with aggregate functions: sum(), count(), min(), max()
TasksAutomate stored procedures on schedule with dependencies
Return ValueReturn DataFrame to display in Results tab

⚠️ Common Mistakes​

  • ❌ String concatenation with + β†’ Use concat() 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.