Building ETL Pipeline with Snowpark

Read Time:2 Minute, 11 Second

In today’s data-driven world, organizations demand powerful tools to transform, analyze, and present their data seamlessly. Snowflake’s Snowpark is a game-changing feature that enables data engineers and analysts to write scalable data transformation workflows directly within Snowflake using Python, Java, or Scala. In this blog, we’ll explore Building an ETL Pipeline with Snowpark by simulating a scenario where commerce data flows through distinct data layers—RAW, SILVER, and GOLDEN. These tables form the foundation for insightful analytics and robust business intelligence.

Imagine a rapidly growing e-commerce company, which processes thousands of transactions daily. They need to:

Consolidate raw data from orders, customers, and products.
Enrich and clean data for downstream analytics.
Generate dimensional tables for customers and products.
Create a fact table to summarize daily sales.
Develop a VIEW in Semantic Layer.

To address these challenges, Company implements a three-layer architecture:

RAW Layer: Stores ingested data directly from source systems without transformations.
SILVER Layer: Cleansed and enriched data prepared for analytical processing.
GOLDEN Layer: Curated tables (dimensions and facts) designed for analytics and reporting.
SEMANTIC Layer: Aggregated View in Semantic layer on top of GOLDEN layer tables.

Snowpark acts as the backbone for these transformations, enabling the entire process seamlessly within Snowflake.

Step 1: Loading RAW Tables

Step 1: Loading RAW Tables

In the RAW layer, data from operational systems is ingested as-is. The data resides in three tables:

RAW_ORDERS: Captures order details.
RAW_CUSTOMERS: Stores customer information.
RAW_PRODUCTS: Holds product catalog data.

Step 2: Enriching and Cleaning Data in the SILVER Layer

Joining with customers and products to fetch relevant details.
Calculating the total order value as quantity * price.
Creating a delivery status flag based on the order’s current status.

The cleaned and enriched data is saved into the SILVER layer for further processing:

enriched_orders.write.mode("append").save_as_table("SILVER_DB.PUBLIC.SILVER_SALES")

Step 3: Building Dimensional Tables in the GOLDEN Layer

DIM_CUSTOMERS:

DIM_PRODUCTS:

FACT_SALES:

Semantic VIEW

Snowflake Tables:

With Snowpark and Snowflake, we’ve:

Transformed raw data into actionable insights.
Built clean, enriched datasets in the SILVER layer.
Created dimensional tables (DIM_CUSTOMERS, DIM_PRODUCTS) and a fact table (FACT_SALES) in the GOLDEN layer.