Build an E-Commerce Data Pipeline

Design a 3-layer ELT pipeline in Snowflake with automated Streams and Tasks.

Introduction

30 Second Summary

Every online store generates a flood of order data. Before anyone can answer questions like "what's our best-selling category?" or "which day had the highest revenue?", that raw data needs to be cleaned, organized, and summarized.

In this project, you will build a complete data pipeline inside Snowflake that ingests raw e-commerce order data from a CSV file, transforms it into analytics-ready tables, and automates future data loads using Streams and Tasks.

What You'll Build

A three-layer data pipeline in Snowflake that takes raw CSV order data through cleaning, transformation, and summarization stages, with built-in automation that detects and processes new data arrivals.

By the end of this project, you'll have:

  • A Snowflake database with three schemas representing the medallion architecture (RAW, STAGING, ANALYTICS) and raw data loaded from CSV files.
  • Transformed staging tables with proper data types, computed columns, and data quality validation.
  • An automated pipeline using Streams and Tasks that detects new raw data and transforms it without manual intervention.
  • Secret Mission: Build a "Top Customers" leaderboard using window functions and chain dependent Tasks together.

Are there any prerequisites?

You should be comfortable with basic SQL concepts like SELECT, INSERT, GROUP BY, and JOIN. No Snowflake experience is needed.

Set Up Your Snowflake Free Trial

Before you can build a data pipeline, you need a place to run it. Snowflake is a cloud data warehouse that handles storage, compute, and transformation all in one platform.

In this step, you will create a free Snowflake trial account, explore the web interface where you will write SQL, and confirm everything is working with a quick test query.

In this step, get ready to:
  • Sign up for a Snowflake free trial account.
  • Open Snowsight and create a SQL worksheet.
  • Verify your account is working with a test query.
Sign up for a Snowflake free trial

Snowflake offers a 30-day free trial with $400 in credits. No credit card is required.

  • Open your browser and go to signup.snowflake.com.
  • Fill in your first name, last name, email address, company name, and job title.
  • Click Sign up.

What do the signup options mean?

During signup, you will be asked to choose a cloud provider, a region, and an edition. Select AWS as your cloud provider (any provider works, but AWS is recommended for this project). Choose a region close to your physical location to reduce latency.

For edition, select Enterprise. This gives you access to all the features used in this project. The edition selection does not affect your trial cost.

  • Select AWS as your cloud provider.
  • Select a region close to your location.
  • Select Enterprise as your Snowflake edition.
  • Complete the signup process and check your email for an activation link from Snowflake.
  • Click the activation link in your email and set your username and password when prompted.

Use a personal email

If your work email has strict security policies, consider using a personal email for the trial. This avoids potential issues with corporate firewalls or email filters blocking the activation link.

Log into Snowsight and open a worksheet

Snowsight is Snowflake's browser-based web interface. This is where you will write and run all your SQL for this project.

  • After activating your account, Snowsight opens automatically in your browser. If you need to sign in again later, go to app.snowflake.com.
  • In the left navigation menu, select Projects.
  • Select Worksheets.
  • Click the + button and select SQL Worksheet.

A blank worksheet opens with today's date and time as the default title. This is your SQL editor for the rest of the project.

What is a SQL worksheet?

A worksheet is a code editor inside Snowsight where you write and execute SQL statements. You can have multiple worksheets open in tabs, and your work is saved automatically between sessions.

Don't see Projects in the navigation?

Snowflake recently updated their navigation menu. If your left sidebar looks different, look for a section labeled Projects that contains Worksheets underneath it. You can also use the search bar at the top to search for "Worksheets".

help me find the Worksheets page in Snowsight

Verify your account with a test query

Before building anything, confirm that your Snowflake session is active and your account is set up correctly.

  • In your new SQL worksheet, paste the following query and run it by clicking the blue Run button in the top-right corner of the worksheet (or press Ctrl+Enter):
-- Verify your Snowflake session is active
SELECT CURRENT_ACCOUNT(), CURRENT_ROLE(), CURRENT_USER();

What does this query do?

This query asks Snowflake to return three pieces of information about your current session.

  • CURRENT_ACCOUNT() returns your unique account identifier.
  • CURRENT_ROLE() shows which role you are using (likely ACCOUNTADMIN for a new trial).
  • CURRENT_USER() returns the username you created during signup.
  • Check the results panel below the worksheet. You should see a single row with three values: your account name, your role, and your username.

Seeing an error instead of results?

If you see a message about no warehouse being selected, that is expected. Your trial starts without an active warehouse. The verification query above uses metadata functions that do not require a warehouse to run. Try clicking Run again.

If you see a permissions error, make sure your role is set to ACCOUNTADMIN. Click the role selector in the top-left area of the worksheet and switch to ACCOUNTADMIN.

help me troubleshoot my first Snowflake query

Your Snowflake account is ready to go. Next up, you will create the database, schemas, and warehouse that form the foundation of your data pipeline.

Create the Database, Schemas, and Warehouse

Your Snowflake account is ready. Now you need a place to store and organize your data pipeline.

In this step, you'll create the foundational Snowflake objects that your entire pipeline depends on. You'll set up a database with three schemas following the medallion architecture pattern, plus a virtual warehouse to power your queries.

In this step, get ready to:
  • Create a database and three schemas (RAW, STAGING, ANALYTICS) for the medallion architecture.
  • Create an X-Small virtual warehouse with auto-suspend to keep costs minimal.
  • Verify your objects exist and set the session context.
Create the database and schemas

A data pipeline needs clearly separated layers to keep raw, cleaned, and summarized data apart. The medallion architecture uses three layers: RAW for untouched source data, STAGING for cleaned and typed data, and ANALYTICS for business-ready summaries.

  • In your SQL worksheet, paste the following SQL and run it by selecting Run All from the dropdown menu next to the Run button (or press Ctrl+Alt+Enter):
----------------------------------------------
-- STEP 2: Create Database, Schemas, Warehouse
----------------------------------------------

-- Create the database
CREATE OR REPLACE DATABASE ECOMMERCE_DB;

-- Create three schemas for the medallion architecture
CREATE OR REPLACE SCHEMA ECOMMERCE_DB.RAW;
CREATE OR REPLACE SCHEMA ECOMMERCE_DB.STAGING;
CREATE OR REPLACE SCHEMA ECOMMERCE_DB.ANALYTICS;

What does this SQL do?

  • CREATE OR REPLACE DATABASE ECOMMERCE_DB creates a new database to hold all your pipeline objects. If one already exists with this name, it replaces it.
  • Each CREATE OR REPLACE SCHEMA command creates a named container inside the database. RAW holds untouched source data, STAGING holds cleaned data, and ANALYTICS holds summary tables.
  • Check the results panel at the bottom of the worksheet. You should see a success status message for each statement.

Seeing a permissions error?

Make sure your role is set to ACCOUNTADMIN or SYSADMIN. You can check the role selector in the top-left of your worksheet. Trial accounts default to ACCOUNTADMIN, which has full privileges.

Help me fix a permissions error when creating my database.

Create the virtual warehouse

In Snowflake, a virtual warehouse provides the compute power that runs your queries. It does not store data. You only pay for the time it is actively running.

  • Add the following SQL below your existing code in the same worksheet, then select all of it and run it:
-- Create a small warehouse for minimal credit usage
CREATE OR REPLACE WAREHOUSE ECOMMERCE_WH WITH
    WAREHOUSE_SIZE = 'X-SMALL'
    AUTO_SUSPEND = 120
    AUTO_RESUME = TRUE
    INITIALLY_SUSPENDED = TRUE;

What does this SQL do?

  • WAREHOUSE_SIZE = 'X-SMALL' picks the smallest (and cheapest) compute size, consuming 1 credit per hour of active use.
  • AUTO_SUSPEND = 120 automatically pauses the warehouse after 120 seconds of inactivity, so you stop burning credits when idle.
  • AUTO_RESUME = TRUE means the warehouse starts itself again the moment you run a query. No manual restart needed.
  • INITIALLY_SUSPENDED = TRUE creates the warehouse in a paused state so it does not consume credits until your first query actually needs it.
  • Check the results panel. You should see a status message confirming the warehouse was created successfully.

Why X-Small?

X-Small warehouses consume 1 credit per hour with per-second billing (60-second minimum). For this project, your total compute usage will be well under 1 credit. The auto-suspend setting ensures you are never paying for idle time.

Warehouse not appearing?

Double check that the SQL ran without errors. If you see a syntax error, make sure the WITH keyword appears after ECOMMERCE_WH and each parameter is on its own line.

Help me troubleshoot my CREATE WAREHOUSE statement.

Verify your objects and set the session context

Before moving on, you need to tell Snowflake which warehouse and database to use for this session. Then you can confirm everything was created correctly.

  • Add the following SQL below your existing code and run it:
-- Set the warehouse for this session
USE WAREHOUSE ECOMMERCE_WH;
USE DATABASE ECOMMERCE_DB;

-- Verify objects
SHOW SCHEMAS IN DATABASE ECOMMERCE_DB;
SHOW WAREHOUSES LIKE 'ECOMMERCE_WH';

What does this SQL do?

  • USE WAREHOUSE and USE DATABASE set your session context. All future queries in this worksheet will use ECOMMERCE_WH for compute and ECOMMERCE_DB as the default database.
  • SHOW SCHEMAS IN DATABASE ECOMMERCE_DB lists all schemas in your database. You should see RAW, STAGING, ANALYTICS, plus two default schemas (INFORMATION_SCHEMA and PUBLIC).
  • SHOW WAREHOUSES LIKE 'ECOMMERCE_WH' confirms your warehouse exists and shows its configuration (size, auto-suspend, state).
  • Check the results panel for the SHOW SCHEMAS output. You should see a table listing at least 5 schemas: ANALYTICS, INFORMATION_SCHEMA, PUBLIC, RAW, and STAGING.
  • Click on the SHOW WAREHOUSES result. You should see ECOMMERCE_WH listed with size X-Small and auto_suspend set to 120.

Only seeing PUBLIC and INFORMATION_SCHEMA?

If your three custom schemas are missing, the CREATE SCHEMA statements may not have run. Scroll up in your worksheet and make sure all statements ran (look for a green checkmark next to each). Re-run the full worksheet with Run All.

Help me figure out why my schemas are missing.

✔️ Awesome, I've got everything!

Great. Double check that your worksheet is saved (Snowsight auto-saves every 3 seconds) and move on to the next step.

ⓧ I'd like to double check the full code

Here is the complete SQL you should have in your worksheet at this point:

----------------------------------------------
-- STEP 2: Create Database, Schemas, Warehouse
----------------------------------------------

-- Create the database
CREATE OR REPLACE DATABASE ECOMMERCE_DB;

-- Create three schemas for the medallion architecture
CREATE OR REPLACE SCHEMA ECOMMERCE_DB.RAW;
CREATE OR REPLACE SCHEMA ECOMMERCE_DB.STAGING;
CREATE OR REPLACE SCHEMA ECOMMERCE_DB.ANALYTICS;

-- Create a small warehouse for minimal credit usage
CREATE OR REPLACE WAREHOUSE ECOMMERCE_WH WITH
    WAREHOUSE_SIZE = 'X-SMALL'
    AUTO_SUSPEND = 120
    AUTO_RESUME = TRUE
    INITIALLY_SUSPENDED = TRUE;

-- Set the warehouse for this session
USE WAREHOUSE ECOMMERCE_WH;
USE DATABASE ECOMMERCE_DB;

-- Verify objects
SHOW SCHEMAS IN DATABASE ECOMMERCE_DB;
SHOW WAREHOUSES LIKE 'ECOMMERCE_WH';

Your database structure is ready. Next up, you'll create sample e-commerce data and load it into Snowflake through an internal stage.

Create the CSV Data and Load It Into Snowflake

Your database structure is in place with three schemas ready and waiting for data. Now it's time to feed the pipeline its first batch of records.

Every data pipeline starts with raw source data. In this step, you'll create a sample CSV file of e-commerce orders, upload it into a Snowflake internal stage, and load it into a raw table using the COPY INTO command.

In this step, get ready to:
  • Create a sample e-commerce orders CSV file with 20 rows of data.
  • Upload the CSV into a Snowflake internal stage via the Snowsight UI.
  • Load the staged data into a raw table using COPY INTO.
Create your sample CSV data file

Before you can load data into Snowflake, you need a source file. You'll create a small e-commerce orders dataset that simulates real order data with customers, products, and transactions.

  • Press Cmd+Space (macOS) or the Windows key (Windows) to open your search bar.
  • Type Notepad and press Enter to open it.
  • Copy and paste the following CSV data into your empty Notepad window:
order_id,customer_id,customer_name,product_name,category,quantity,unit_price,order_date,status
1,101,Alice Johnson,Laptop Pro 15,Electronics,1,1299.99,2024-01-15,completed
2,102,Bob Smith,Running Shoes X1,Sports,2,89.99,2024-01-15,completed
3,103,Carol White,Organic Coffee Beans,Food,3,24.99,2024-01-16,completed
4,101,Alice Johnson,Wireless Mouse,Electronics,1,49.99,2024-01-16,completed
5,104,David Brown,Yoga Mat Premium,Sports,1,45.00,2024-01-17,pending
6,105,Eva Martinez,Python Cookbook,Books,1,39.99,2024-01-17,completed
7,102,Bob Smith,Basketball,Sports,1,29.99,2024-01-18,completed
8,106,Frank Lee,Desk Lamp LED,Home,2,34.99,2024-01-18,completed
9,103,Carol White,Green Tea Set,Food,1,18.50,2024-01-19,completed
10,107,Grace Kim,Bluetooth Speaker,Electronics,1,79.99,2024-01-19,shipped
11,101,Alice Johnson,USB-C Hub,Electronics,2,35.99,2024-01-20,completed
12,108,Henry Park,Resistance Bands,Sports,1,22.99,2024-01-20,completed
13,104,David Brown,Meditation Cushion,Home,1,55.00,2024-01-21,completed
14,109,Iris Chen,Data Science Handbook,Books,1,49.99,2024-01-21,completed
15,105,Eva Martinez,Stainless Water Bottle,Home,2,28.99,2024-01-22,shipped
16,110,Jack Wilson,Gaming Keyboard,Electronics,1,149.99,2024-01-22,completed
17,102,Bob Smith,Protein Powder,Food,1,39.99,2024-01-23,completed
18,106,Frank Lee,Plant Pot Set,Home,3,15.99,2024-01-23,pending
19,107,Grace Kim,Portable Charger,Electronics,1,44.99,2024-01-24,completed
20,103,Carol White,Trail Mix Variety,Food,2,12.99,2024-01-24,completed

What does this data contain?

This CSV has 20 e-commerce orders with 9 columns covering the essentials of a transaction record.

  • order_id and customer_id are unique identifiers for each order and customer.
  • category groups products into Electronics, Sports, Food, Books, and Home.
  • quantity and unit_price will let you calculate revenue later.
  • status includes completed, shipped, and pending orders for filtering.
  • In Notepad, click File in the top menu bar.
  • Click Save As.
  • Navigate to your Desktop folder in the left panel.
  • In the File name field, type ecommerce_orders.csv.
  • Change the Save as type dropdown to All Files (*.*).
  • Click Save.

Why change Save as type?

By default, Notepad saves files as .txt. Changing to All Files ensures your file keeps the .csv extension instead of becoming ecommerce_orders.csv.txt.

You should now see ecommerce_orders.csv on your Desktop.

Don't see the file on your Desktop?

  • Make sure you selected Desktop as the save location, not Documents or another folder.
  • Check that the Save as type was set to All Files before saving. If it saved as .txt, delete the file, reopen Notepad, and save again with the correct settings.

Help me find my saved CSV file on Windows.

Create the file format, stage, and upload your CSV

Snowflake needs two things before it can ingest your CSV: a file format object that describes how to parse the file, and a stage that acts as a landing zone for uploaded files.

  • In Snowsight, select Projects in the navigation menu, then Worksheets.
  • Select + and then SQL Worksheet to create a new worksheet for this step.
  • Create the file format and internal stage by pasting and running the following SQL:
----------------------------------------------
-- STEP 3: Create Stage, File Format, Load Data
----------------------------------------------

USE SCHEMA ECOMMERCE_DB.RAW;

-- Create a file format for CSV files
CREATE OR REPLACE FILE FORMAT csv_format
    TYPE = 'CSV'
    FIELD_OPTIONALLY_ENCLOSED_BY = '"'
    SKIP_HEADER = 1
    NULL_IF = ('NULL', 'null', '');

-- Create a named internal stage
CREATE OR REPLACE STAGE orders_stage
    FILE_FORMAT = csv_format;

What do these objects do?

  • csv_format tells Snowflake how to read your CSV: skip the header row, handle quoted fields, and treat empty strings as NULL.
  • orders_stage is an internal stage. Think of it as a temporary holding area inside Snowflake where you upload files before loading them into tables.
  • Select all the SQL and click the Run button (or press Ctrl+Enter) to execute.
  • You should see success messages confirming each statement completed.

Now you need to upload your CSV file to the stage you just created.

  • In the Snowsight navigation menu, select Ingestion.
  • Select Add data.
  • Click Load files into a Stage.
  • Select ECOMMERCE_DB as your database.
  • Select RAW as your schema.
  • Select ORDERS_STAGE as the stage.
  • Click Upload and browse to your Desktop to select ecommerce_orders.csv.
  • Confirm the upload completes successfully.

Why upload through the UI instead of a command?

Snowsight lets you upload files up to 250 MB directly to named internal stages from your browser. This avoids installing a separate command-line tool (SnowSQL) just to transfer a small file.

  • Switch back to your SQL worksheet and verify the upload by running:
-- After uploading the CSV via Snowsight UI, list files to confirm
LIST @orders_stage;

You should see one row listing ecommerce_orders.csv with its file size.

LIST shows no files?

  • Make sure you ran USE SCHEMA ECOMMERCE_DB.RAW; before running LIST. The stage lives in the RAW schema.
  • Go back to Ingestion and confirm you selected ORDERS_STAGE in the RAW schema of ECOMMERCE_DB during upload.

Help me troubleshoot my stage file upload in Snowsight.

Load staged data into a raw table

With the file sitting in your stage, you can now create a destination table and load the data into it. The raw table uses all STRING columns intentionally. You'll cast them to proper types in the next step.

  • Create the raw orders table by adding and running this SQL in your worksheet:
-- Create the raw orders table
CREATE OR REPLACE TABLE raw_orders (
    order_id STRING,
    customer_id STRING,
    customer_name STRING,
    product_name STRING,
    category STRING,
    quantity STRING,
    unit_price STRING,
    order_date STRING,
    status STRING
);

Why are all columns STRING?

In a medallion architecture, the raw layer stores data exactly as it arrived. No type casting, no transformations. This means you never lose data due to parsing errors at load time.

The staging layer (next step) is where you'll cast strings into proper types like INTEGER and DATE.

  • Run the statement. You should see a success message confirming the table was created.
  • Load data from the stage into your new table by running this SQL:
-- Load data from stage into raw table
COPY INTO raw_orders
    FROM @orders_stage/ecommerce_orders.csv
    FILE_FORMAT = (FORMAT_NAME = csv_format)
    ON_ERROR = 'CONTINUE';

What does COPY INTO do?

  • COPY INTO bulk-loads data from a stage into an existing table.
  • FROM @orders_stage/ecommerce_orders.csv points to the specific file in your stage.
  • ON_ERROR = 'CONTINUE' tells Snowflake to skip bad rows instead of aborting the entire load. For clean data like ours, it's a safety net.
  • Verify the load succeeded by running these queries:
-- Verify the load
SELECT COUNT(*) AS row_count FROM raw_orders;
SELECT * FROM raw_orders LIMIT 10;

The first query should return 20 as the row count. The second query shows you the first 10 rows of raw data with all columns populated.

Row count shows 0?

  • Run LIST @orders_stage; again to confirm the file is still there. If it's missing, re-upload it.
  • Check that your COPY INTO statement references the exact filename ecommerce_orders.csv. A typo in the filename means Snowflake can't find the file.
  • Look at the COPY INTO output. It shows how many rows were loaded and how many had errors. If all rows errored, the file format might not match the CSV structure.

Help me debug why COPY INTO loaded 0 rows from my stage.

✔️ Awesome, I've got everything!

Great. Double check you've saved your worksheet.

ⓧ I'd like to double check the full code

Here's the complete SQL for this step. Compare it against what you have in your worksheet:

----------------------------------------------
-- STEP 3: Create Stage, File Format, Load Data
----------------------------------------------

USE SCHEMA ECOMMERCE_DB.RAW;

-- Create a file format for CSV files
CREATE OR REPLACE FILE FORMAT csv_format
    TYPE = 'CSV'
    FIELD_OPTIONALLY_ENCLOSED_BY = '"'
    SKIP_HEADER = 1
    NULL_IF = ('NULL', 'null', '');

-- Create a named internal stage
CREATE OR REPLACE STAGE orders_stage
    FILE_FORMAT = csv_format;

-- After uploading the CSV via Snowsight UI, list files to confirm
LIST @orders_stage;

-- Create the raw orders table
CREATE OR REPLACE TABLE raw_orders (
    order_id STRING,
    customer_id STRING,
    customer_name STRING,
    product_name STRING,
    category STRING,
    quantity STRING,
    unit_price STRING,
    order_date STRING,
    status STRING
);

-- Load data from stage into raw table
COPY INTO raw_orders
    FROM @orders_stage/ecommerce_orders.csv
    FILE_FORMAT = (FORMAT_NAME = csv_format)
    ON_ERROR = 'CONTINUE';

-- Verify the load
SELECT COUNT(*) AS row_count FROM raw_orders;
SELECT * FROM raw_orders LIMIT 10;

Your raw layer now has real data sitting in it. Next up, you'll transform this raw data into clean, properly typed tables in the staging layer.

Transform Raw Data into the Staging Layer

Your raw data is loaded, but every column is a STRING. That means you can't do date math, calculate totals, or reliably filter anything.

In this step, you'll build the staging layer of your medallion architecture. You'll cast strings into proper types, add a computed column for revenue, and run quality checks to make sure your data is clean before it reaches the analytics layer.

In this step, get ready to:
  • Create a staging table with proper data types and computed columns.
  • Run data quality checks for NULLs and duplicates.
  • Verify column types and confirm clean data with a filtered query.
Create the staging table with CTAS

CREATE TABLE AS SELECT (CTAS) lets you build a new table directly from a query. Instead of creating the table structure first and loading data separately, you define the transformations inline and Snowflake figures out the column types from your expressions.

  • In your SQL worksheet in Snowsight, create a new worksheet by clicking the + button next to your existing worksheet tab.
  • Paste the following SQL to switch to the STAGING schema and create the transformed table:
----------------------------------------------
-- STEP 4: Transform Raw Data into Staging
----------------------------------------------

USE SCHEMA ECOMMERCE_DB.STAGING;

-- Create staging table with proper data types and transformations
CREATE OR REPLACE TABLE stg_orders AS
SELECT
    raw.order_id::INTEGER AS order_id,
    raw.customer_id::INTEGER AS customer_id,
    TRIM(raw.customer_name) AS customer_name,
    TRIM(raw.product_name) AS product_name,
    TRIM(raw.category) AS category,
    raw.quantity::INTEGER AS quantity,
    raw.unit_price::DECIMAL(10,2) AS unit_price,
    TRY_TO_DATE(raw.order_date, 'YYYY-MM-DD') AS order_date,
    UPPER(TRIM(raw.status)) AS status,
    (raw.quantity::INTEGER * raw.unit_price::DECIMAL(10,2)) AS total_price
FROM ECOMMERCE_DB.RAW.raw_orders raw;

What does this code do?

This CTAS query reads every row from the raw table and transforms it on the way into the new staging table.

  • ::INTEGER and ::DECIMAL(10,2) cast STRING columns into numeric types so you can do math on them.
  • TRY_TO_DATE safely converts the date string. If a value can't be parsed, it returns NULL instead of throwing an error.
  • TRIM removes any leading or trailing whitespace. UPPER standardizes the status column to uppercase so "completed" and "Completed" are treated the same.
  • total_price is a computed column that multiplies quantity by unit price, giving you revenue per line item.
  • Select all the SQL and click the Run button (or press Ctrl+Enter).
  • You should see a success message confirming the table was created. The results panel shows "Table STG_ORDERS successfully created."

Seeing an error about the schema or table?

  • Make sure the USE SCHEMA ECOMMERCE_DB.STAGING; line runs before the CREATE statement. Run it separately first if needed.
  • If you see a "does not exist" error for raw_orders, confirm the full path ECOMMERCE_DB.RAW.raw_orders is in your query. The staging schema can't see the raw schema's tables without the full path.

Still stuck? Ask for help.

Run data quality checks

A staging table is only useful if the data inside it is trustworthy. Before building analytics on top of it, you need to confirm there are no NULL values in critical columns and no duplicate order IDs.

  • Add the following queries below your CTAS statement in the same worksheet and run them:
-- Data quality checks
SELECT
    COUNT(*) AS total_rows,
    COUNT(*) - COUNT(order_id) AS null_order_ids,
    COUNT(*) - COUNT(order_date) AS null_dates,
    COUNT(*) - COUNT(total_price) AS null_prices
FROM stg_orders;

-- Check for duplicates
SELECT order_id, COUNT(*) AS cnt
FROM stg_orders
GROUP BY order_id
HAVING cnt > 1;

What do these checks tell you?

  • The first query counts how many rows have NULL in critical columns. The formula COUNT(*) - COUNT(column) works because COUNT(column) skips NULLs while COUNT(*) counts all rows.
  • The second query groups by order_id and uses HAVING cnt > 1 to surface any duplicates. An empty result means all order IDs are unique.
  • Select just the first quality check query and click Run.
  • You should see total_rows = 20 and zeros for all the null columns.
  • Select and run the duplicate check query.
  • You should see an empty result set, meaning no duplicate order IDs exist.

Seeing NULLs where you expected zeros?

  • If null_dates is non-zero, check whether your CSV has any blank date values. The TRY_TO_DATE function returns NULL for values it can't parse.
  • If you see NULLs in null_prices, that means the cast of quantity or unit_price to a number failed for some rows. Go back and check your CSV for any non-numeric values in those columns.

Help me understand my data quality results.

Verify column types and sample the data

The final verification confirms that Snowflake assigned the correct data types to each column and that filtered queries work as expected on the typed data.

  • Add these verification queries below your quality checks and run them:
-- Verify types and sample data
DESCRIBE TABLE stg_orders;
SELECT * FROM stg_orders WHERE order_date >= '2024-01-01' LIMIT 10;

What should I see?

  • DESCRIBE TABLE shows each column's name, type, and nullability. You should see NUMBER(38,0) for integers, NUMBER(10,2) for decimals, and DATE for order_date.
  • The filtered SELECT proves that date comparison works. All 20 rows should appear because every order is from January 2024.
  • The status column should show values like COMPLETED, PENDING, and SHIPPED in all caps.
  • Select the DESCRIBE TABLE stg_orders; line and click Run.
  • Confirm you see DATE next to order_date and NUMBER(10,2) next to unit_price and total_price.
  • Select the filtered SELECT query and click Run.
  • Confirm that the status column values are all uppercase and the total_price column has calculated values.

Status column not uppercase?

  • If status values are still lowercase, go back to your CTAS query and confirm you have UPPER(TRIM(raw.status)) rather than just TRIM(raw.status).
  • Re-run the full CTAS from the top (the CREATE OR REPLACE keyword safely overwrites the existing table).

Help me fix my staging table transformations.

✔️ Awesome, I've got everything!

Great. Double check you have all the SQL saved in your worksheet.

ⓧ I'd like to double check the full code

Here is the complete 03_transform_staging.sql file:

----------------------------------------------
-- STEP 4: Transform Raw Data into Staging
----------------------------------------------

USE SCHEMA ECOMMERCE_DB.STAGING;

-- Create staging table with proper data types and transformations
CREATE OR REPLACE TABLE stg_orders AS
SELECT
    raw.order_id::INTEGER AS order_id,
    raw.customer_id::INTEGER AS customer_id,
    TRIM(raw.customer_name) AS customer_name,
    TRIM(raw.product_name) AS product_name,
    TRIM(raw.category) AS category,
    raw.quantity::INTEGER AS quantity,
    raw.unit_price::DECIMAL(10,2) AS unit_price,
    TRY_TO_DATE(raw.order_date, 'YYYY-MM-DD') AS order_date,
    UPPER(TRIM(raw.status)) AS status,
    (raw.quantity::INTEGER * raw.unit_price::DECIMAL(10,2)) AS total_price
FROM ECOMMERCE_DB.RAW.raw_orders raw;

-- Data quality checks
SELECT
    COUNT(*) AS total_rows,
    COUNT(*) - COUNT(order_id) AS null_order_ids,
    COUNT(*) - COUNT(order_date) AS null_dates,
    COUNT(*) - COUNT(total_price) AS null_prices
FROM stg_orders;

-- Check for duplicates
SELECT order_id, COUNT(*) AS cnt
FROM stg_orders
GROUP BY order_id
HAVING cnt > 1;

-- Verify types and sample data
DESCRIBE TABLE stg_orders;
SELECT * FROM stg_orders WHERE order_date >= '2024-01-01' LIMIT 10;

Your staging layer is clean, typed, and verified. Next up, you'll use this reliable data to build analytics summary tables that answer real business questions.

Build Analytics Summary Tables

Your staging layer now holds clean, properly typed data. The next question is: what does this data actually tell us about the business?

In this step, you'll build the final layer of the medallion architecture. You'll create analytics summary tables that aggregate order data into daily revenue trends and category performance rankings. These are the tables a business analyst or dashboard would query directly.

In this step, get ready to:
  • Create a daily revenue summary table with aggregated order metrics.
  • Create a category performance summary table ranking product categories.
  • Run a combined insight query to find the best-selling category on the highest revenue day.
Create the daily revenue summary table

The ANALYTICS schema is where business-facing data lives. Your first summary table will group orders by date so you can spot revenue trends over time.

  • In your SQL worksheet, switch to the ANALYTICS schema and create the daily revenue table by running this code:
USE SCHEMA ECOMMERCE_DB.ANALYTICS;

-- Daily revenue summary
CREATE OR REPLACE TABLE daily_revenue AS
SELECT
    s.order_date,
    COUNT(*) AS total_orders,
    SUM(s.total_price) AS total_revenue,
    AVG(s.total_price) AS avg_order_value
FROM ECOMMERCE_DB.STAGING.stg_orders s
GROUP BY s.order_date
ORDER BY s.order_date;

What does this code do?

This uses CREATE TABLE AS SELECT (CTAS) to build a new table from a query result. The query groups all orders by their order_date.

  • COUNT(*) counts how many orders were placed each day.
  • SUM(s.total_price) adds up all revenue for that day.
  • AVG(s.total_price) calculates the average order value per day.
  • Select all the code and click the blue play button (or press Ctrl+Enter) to run it.
  • You should see a confirmation that the table was created successfully.

Seeing an error about the schema or table?

  • Make sure you included the USE SCHEMA ECOMMERCE_DB.ANALYTICS; line at the top. Without it, Snowflake tries to create the table in whatever schema was previously active.
  • Check that the fully qualified reference ECOMMERCE_DB.STAGING.stg_orders is spelled exactly as shown. A typo in the schema or table name will cause a "does not exist" error.

Help me debug my daily_revenue table creation in Snowflake.

Create the category performance summary table

Your second analytics table will rank product categories by sales volume and revenue. This answers the question: which product categories drive the most business?

  • Add the following code below your previous query in the worksheet, then run it:
-- Category performance summary
CREATE OR REPLACE TABLE category_performance AS
SELECT
    s.category,
    SUM(s.quantity) AS total_quantity_sold,
    SUM(s.total_price) AS total_revenue,
    COUNT(*) AS number_of_orders
FROM ECOMMERCE_DB.STAGING.stg_orders s
GROUP BY s.category
ORDER BY total_revenue DESC;

What does this code do?

This groups all orders by their category column (Electronics, Sports, Food, etc.) and calculates three metrics for each category.

  • total_quantity_sold sums up the number of individual items sold in that category.
  • total_revenue sums the total_price across all orders in that category.
  • ORDER BY total_revenue DESC puts the highest-earning category at the top.
  • Select the new code and run it with the play button or Ctrl+Enter.
  • You should see a confirmation that the category_performance table was created.

Table not created?

  • If you see an error about stg_orders not existing, confirm you're referencing the full path ECOMMERCE_DB.STAGING.stg_orders (not just stg_orders without the schema prefix).
  • Make sure you only selected the category_performance code block before running. Running the entire worksheet will re-execute earlier statements too.

Help me fix my category_performance table creation.

Verify your analytics tables and run a combined insight query

Both analytics tables are now in place. Time to query them and answer a real business question: what was the best-selling category on the day with the highest total revenue?

  • Add the following verification and insight queries to your worksheet, then run them:
-- Verify analytics tables
SELECT * FROM daily_revenue ORDER BY total_revenue DESC LIMIT 5;
SELECT * FROM category_performance;

-- Combined insight: best category on highest revenue day
SELECT
    dr.order_date,
    dr.total_revenue AS day_revenue,
    cp.category,
    cp.total_revenue AS category_revenue
FROM daily_revenue dr
CROSS JOIN category_performance cp
WHERE dr.total_revenue = (SELECT MAX(total_revenue) FROM daily_revenue)
ORDER BY cp.total_revenue DESC
LIMIT 1;

What does the combined insight query do?

This query uses a CROSS JOIN to pair every row in daily_revenue with every row in category_performance. Then it filters to only the day with the highest revenue using a subquery.

The final ORDER BY ... DESC LIMIT 1 picks the top-earning category on that day. This is the kind of quick analytical question that summary tables make easy to answer.

  • Run each SELECT statement one at a time by highlighting it and pressing Ctrl+Enter.
  • For the first query, you should see 5 rows showing dates ordered by highest revenue first.
  • For the second query, you should see 5 rows (one per category: Electronics, Sports, Food, Home, Books) ordered by revenue.
  • For the combined insight query, you should see a single row showing the top category on the highest revenue day.

Getting zero rows or unexpected results?

  • If you see zero rows from daily_revenue or category_performance, the tables may not have been created correctly. Re-run the CTAS statements from the earlier substeps.
  • Make sure your session is still using the ANALYTICS schema. Run USE SCHEMA ECOMMERCE_DB.ANALYTICS; before the SELECT queries if needed.

Help me troubleshoot my analytics queries in Snowflake.

✔️ Awesome, I've got everything!

Great. Double check you've saved your worksheet with all the analytics SQL.

ⓧ I'd like to double check the full code

Here is the complete 04_analytics.sql code. Compare your worksheet against this:

----------------------------------------------
-- STEP 5: Build Analytics Summary Tables
----------------------------------------------

USE SCHEMA ECOMMERCE_DB.ANALYTICS;

-- Daily revenue summary
CREATE OR REPLACE TABLE daily_revenue AS
SELECT
    s.order_date,
    COUNT(*) AS total_orders,
    SUM(s.total_price) AS total_revenue,
    AVG(s.total_price) AS avg_order_value
FROM ECOMMERCE_DB.STAGING.stg_orders s
GROUP BY s.order_date
ORDER BY s.order_date;

-- Category performance summary
CREATE OR REPLACE TABLE category_performance AS
SELECT
    s.category,
    SUM(s.quantity) AS total_quantity_sold,
    SUM(s.total_price) AS total_revenue,
    COUNT(*) AS number_of_orders
FROM ECOMMERCE_DB.STAGING.stg_orders s
GROUP BY s.category
ORDER BY total_revenue DESC;

-- Verify analytics tables
SELECT * FROM daily_revenue ORDER BY total_revenue DESC LIMIT 5;
SELECT * FROM category_performance;

-- Combined insight: best category on highest revenue day
SELECT
    dr.order_date,
    dr.total_revenue AS day_revenue,
    cp.category,
    cp.total_revenue AS category_revenue
FROM daily_revenue dr
CROSS JOIN category_performance cp
WHERE dr.total_revenue = (SELECT MAX(total_revenue) FROM daily_revenue)
ORDER BY cp.total_revenue DESC
LIMIT 1;

Your analytics layer is complete. You now have a full three-layer pipeline: raw data flows into clean staging, and staging feeds into business-ready summaries. Next up, you'll automate this entire flow so new data gets processed without lifting a finger.

Automate with Streams and Tasks

Your pipeline works, but right now it requires you to manually re-run every transformation whenever new data arrives. In a production environment, new orders land in the raw layer continuously, and nobody wants to sit there re-running SQL by hand.

Snowflake solves this with two built-in features: Streams (which detect new data) and Tasks (which run SQL on a schedule). Together, they turn your manual pipeline into an automated one.

In this step, get ready to:
  • Create a Stream to detect new data in the raw table.
  • Build a Task that automatically transforms new data into staging.
  • Test the automation end-to-end with sample data.
Create a stream on the raw orders table

A Stream in Snowflake is a change data capture object. It watches a table and records which rows were inserted, updated, or deleted since the last time it was consumed.

Streams don't store copies of the data. They just track an offset, which makes them lightweight and fast.

  • In your SQL worksheet, create a stream on the raw orders table and verify it starts empty by running this code:
USE SCHEMA ECOMMERCE_DB.RAW;

-- Create a stream to track new inserts into raw_orders
CREATE OR REPLACE STREAM raw_orders_stream ON TABLE raw_orders;

-- Verify stream is empty (no changes since creation)
SELECT * FROM raw_orders_stream;

What does this code do?

CREATE OR REPLACE STREAM raw_orders_stream ON TABLE raw_orders attaches a stream to your raw_orders table. From this point forward, any rows inserted into that table will be captured by the stream.

The SELECT * FROM raw_orders_stream query confirms the stream is empty right now. No changes have happened since you created it, so there's nothing to show yet.

  • Run each statement (select each statement and click the play button, or use Ctrl+Enter to run the selected query).
  • The SELECT query should return zero rows, confirming the stream is empty.

Why heartbeats instead of cron?

Traditional pipelines use cron jobs that run at fixed intervals regardless of whether new data exists. Snowflake Streams flip this: the pipeline only fires when there's actually something new to process.

This means zero wasted compute on empty runs, and new data gets picked up within your Task's schedule interval automatically.

Seeing an error on CREATE STREAM?

  • Make sure you ran USE SCHEMA ECOMMERCE_DB.RAW; first. The stream must be created in the same schema as the table it watches.
  • Confirm that raw_orders still exists by running SHOW TABLES IN SCHEMA ECOMMERCE_DB.RAW;.
  • Help me debug my stream creation error.
Create a task to transform new data automatically

A Task is Snowflake's built-in scheduler. It checks a condition on a schedule and runs SQL when that condition is true.

Your task will use SYSTEM$STREAM_HAS_DATA to check the stream every minute. If new rows exist, it runs a MERGE statement to upsert them into the staging table.

  • Create the task, then resume it by running this code:
-- Create a task that transforms new raw data into staging
CREATE OR REPLACE TASK transform_raw_to_staging
    WAREHOUSE = ECOMMERCE_WH
    SCHEDULE = '1 minute'
    WHEN SYSTEM$STREAM_HAS_DATA('ECOMMERCE_DB.RAW.RAW_ORDERS_STREAM')
AS
MERGE INTO ECOMMERCE_DB.STAGING.stg_orders t
USING (
    SELECT
        order_id::INTEGER AS order_id,
        customer_id::INTEGER AS customer_id,
        TRIM(customer_name) AS customer_name,
        TRIM(product_name) AS product_name,
        TRIM(category) AS category,
        quantity::INTEGER AS quantity,
        unit_price::DECIMAL(10,2) AS unit_price,
        TRY_TO_DATE(order_date, 'YYYY-MM-DD') AS order_date,
        UPPER(TRIM(status)) AS status,
        (quantity::INTEGER * unit_price::DECIMAL(10,2)) AS total_price
    FROM ECOMMERCE_DB.RAW.raw_orders_stream
    WHERE METADATA$ACTION = 'INSERT'
) s
ON t.order_id = s.order_id
WHEN MATCHED THEN UPDATE SET
    t.customer_name = s.customer_name,
    t.product_name = s.product_name,
    t.category = s.category,
    t.quantity = s.quantity,
    t.unit_price = s.unit_price,
    t.order_date = s.order_date,
    t.status = s.status,
    t.total_price = s.total_price
WHEN NOT MATCHED THEN INSERT (
    order_id, customer_id, customer_name, product_name,
    category, quantity, unit_price, order_date, status, total_price
) VALUES (
    s.order_id, s.customer_id, s.customer_name, s.product_name,
    s.category, s.quantity, s.unit_price, s.order_date, s.status, s.total_price
);

-- Grant execute task privilege (run as ACCOUNTADMIN if needed)
-- GRANT EXECUTE TASK ON ACCOUNT TO ROLE SYSADMIN;

-- Resume the task (tasks start suspended by default)
ALTER TASK transform_raw_to_staging RESUME;

What does this code do?

  • SCHEDULE = '1 minute' tells Snowflake to check the stream every 60 seconds.
  • WHEN SYSTEM$STREAM_HAS_DATA(...) is the guard clause. The task only runs its SQL body when the stream actually has new rows.
  • The MERGE statement applies the same transformations as your earlier CTAS (casting types, computing total_price), but inserts new rows and updates existing ones. This makes it idempotent.
  • WHERE METADATA$ACTION = 'INSERT' filters the stream to only pick up newly inserted rows.
  • ALTER TASK ... RESUME is required because tasks start in a suspended state by default. Without this, the task never fires.
  • Select and run the CREATE OR REPLACE TASK statement first (select from CREATE all the way down to the closing );).
  • Then select and run the ALTER TASK transform_raw_to_staging RESUME; line.
  • You should see a Statement executed successfully message for both.

The commented-out GRANT line

On trial accounts using ACCOUNTADMIN, the execute task privilege is already granted. If you later switch to SYSADMIN and see a permissions error, uncomment and run the GRANT EXECUTE TASK line.

Task creation failed?

  • Make sure you selected the entire statement from CREATE OR REPLACE TASK down to the final );. Partial selection causes syntax errors.
  • Check that your warehouse ECOMMERCE_WH still exists by running SHOW WAREHOUSES LIKE 'ECOMMERCE_WH';.
  • Help me fix my task creation error.
Test the automation end-to-end

The task is now running in the background, checking for new data every minute. To prove it works, you will insert new rows into the raw table and watch them flow through to staging automatically.

  • Simulate a new data load by inserting two test orders into the raw table:
-- Test: Insert new rows to simulate a new data load
INSERT INTO raw_orders VALUES
    ('1001', '501', 'New Customer', 'Wireless Headphones', 'Electronics', '2', '79.99', '2024-03-15', 'completed'),
    ('1002', '502', 'Another Buyer', 'Running Shoes', 'Sports', '1', '129.99', '2024-03-15', 'completed');

What does this code do?

This simulates what would happen in production when new order data lands in your raw layer. The two rows have new order_id values (1001 and 1002) that don't exist in the staging table yet.

  • Run the INSERT statement.
  • You should see a message confirming 2 rows were inserted.
  • Now verify the stream captured those changes, execute the task manually, and confirm the data arrived in staging by running this code:
-- Check the stream captured the changes
SELECT * FROM raw_orders_stream;

-- Wait ~1 minute for the task to run, or execute manually:
EXECUTE TASK transform_raw_to_staging;

-- Verify new data in staging
SELECT * FROM ECOMMERCE_DB.STAGING.stg_orders
WHERE order_id IN (1001, 1002);

-- Check task execution history
SELECT *
FROM TABLE(INFORMATION_SCHEMA.TASK_HISTORY(
    TASK_NAME => 'TRANSFORM_RAW_TO_STAGING',
    SCHEDULED_TIME_RANGE_START => DATEADD('hour', -1, CURRENT_TIMESTAMP())
))
ORDER BY SCHEDULED_TIME DESC;

What does this code do?

  • SELECT * FROM raw_orders_stream shows the two new rows the stream captured, along with metadata columns like METADATA$ACTION (which will say INSERT).
  • EXECUTE TASK transform_raw_to_staging triggers the task immediately instead of waiting for the next scheduled check.
  • The WHERE order_id IN (1001, 1002) query confirms the MERGE worked by finding the new rows in the staging table with proper types and computed columns.
  • The TASK_HISTORY query shows when the task ran and whether it succeeded or failed.
  • Run SELECT * FROM raw_orders_stream; first. You should see 2 rows with METADATA$ACTION showing INSERT.
  • Run EXECUTE TASK transform_raw_to_staging; to trigger the task immediately.
  • Wait about 10 seconds, then run the staging verification query. You should see 2 rows returned with properly typed columns including a total_price of 159.98 for order 1001 and 129.99 for order 1002.
  • Run the TASK_HISTORY query to see your task execution logged with a SUCCEEDED state.

Not seeing the new rows in staging?

  • If your staging query returns 0 rows, the task may not have finished yet. Wait 15-20 seconds after EXECUTE TASK and try the SELECT again.
  • Check the TASK_HISTORY output. If STATE shows FAILED, look at the ERROR_MESSAGE column for details.
  • Confirm the task is resumed by running SHOW TASKS; and checking the state column shows started.
  • Help me debug why my task didn't process the new rows.

✔️ Awesome, I've got everything!

Great. Make sure you've saved all your automation SQL in your worksheet for reference.

ⓧ I'd like to double check the full code

Here is the complete 05_automation.sql file with all the automation code from this step:

----------------------------------------------
-- STEP 6: Automate with Streams and Tasks
----------------------------------------------

USE SCHEMA ECOMMERCE_DB.RAW;

-- Create a stream to track new inserts into raw_orders
CREATE OR REPLACE STREAM raw_orders_stream ON TABLE raw_orders;

-- Verify stream is empty (no changes since creation)
SELECT * FROM raw_orders_stream;

-- Create a task that transforms new raw data into staging
CREATE OR REPLACE TASK transform_raw_to_staging
    WAREHOUSE = ECOMMERCE_WH
    SCHEDULE = '1 minute'
    WHEN SYSTEM$STREAM_HAS_DATA('ECOMMERCE_DB.RAW.RAW_ORDERS_STREAM')
AS
MERGE INTO ECOMMERCE_DB.STAGING.stg_orders t
USING (
    SELECT
        order_id::INTEGER AS order_id,
        customer_id::INTEGER AS customer_id,
        TRIM(customer_name) AS customer_name,
        TRIM(product_name) AS product_name,
        TRIM(category) AS category,
        quantity::INTEGER AS quantity,
        unit_price::DECIMAL(10,2) AS unit_price,
        TRY_TO_DATE(order_date, 'YYYY-MM-DD') AS order_date,
        UPPER(TRIM(status)) AS status,
        (quantity::INTEGER * unit_price::DECIMAL(10,2)) AS total_price
    FROM ECOMMERCE_DB.RAW.raw_orders_stream
    WHERE METADATA$ACTION = 'INSERT'
) s
ON t.order_id = s.order_id
WHEN MATCHED THEN UPDATE SET
    t.customer_name = s.customer_name,
    t.product_name = s.product_name,
    t.category = s.category,
    t.quantity = s.quantity,
    t.unit_price = s.unit_price,
    t.order_date = s.order_date,
    t.status = s.status,
    t.total_price = s.total_price
WHEN NOT MATCHED THEN INSERT (
    order_id, customer_id, customer_name, product_name,
    category, quantity, unit_price, order_date, status, total_price
) VALUES (
    s.order_id, s.customer_id, s.customer_name, s.product_name,
    s.category, s.quantity, s.unit_price, s.order_date, s.status, s.total_price
);

-- Grant execute task privilege (run as ACCOUNTADMIN if needed)
-- GRANT EXECUTE TASK ON ACCOUNT TO ROLE SYSADMIN;

-- Resume the task (tasks start suspended by default)
ALTER TASK transform_raw_to_staging RESUME;

-- Test: Insert new rows to simulate a new data load
INSERT INTO raw_orders VALUES
    ('1001', '501', 'New Customer', 'Wireless Headphones', 'Electronics', '2', '79.99', '2024-03-15', 'completed'),
    ('1002', '502', 'Another Buyer', 'Running Shoes', 'Sports', '1', '129.99', '2024-03-15', 'completed');

-- Check the stream captured the changes
SELECT * FROM raw_orders_stream;

-- Wait ~1 minute for the task to run, or execute manually:
EXECUTE TASK transform_raw_to_staging;

-- Verify new data in staging
SELECT * FROM ECOMMERCE_DB.STAGING.stg_orders
WHERE order_id IN (1001, 1002);

-- Check task execution history
SELECT *
FROM TABLE(INFORMATION_SCHEMA.TASK_HISTORY(
    TASK_NAME => 'TRANSFORM_RAW_TO_STAGING',
    SCHEDULED_TIME_RANGE_START => DATEADD('hour', -1, CURRENT_TIMESTAMP())
))
ORDER BY SCHEDULED_TIME DESC;

Secret mission

Build a Top Customers Leaderboard and Dependent Task

Your pipeline transforms raw data into clean staging tables automatically. But what if downstream analytics tables could refresh themselves too? In this challenge, you'll use window functions to rank customers by spending and chain a second Task that keeps the leaderboard up to date whenever new data flows through.

Clean Up Your Resources

Clean Up Your Resources

Decide whether to keep your resources running, pause them to come back later, or delete them entirely. This project runs on a Snowflake free trial with $400 in credits. The warehouse auto-suspends when idle, so you only consume credits when tasks fire or you run queries.

Resources you used:

  • ECOMMERCE_DB database (includes all schemas, tables, stages, streams, and tasks).
  • ECOMMERCE_WH warehouse (X-Small, auto-suspends after 120 seconds).
  • ecommerce_orders.csv file on your Desktop.

Keep everything running

No action needed. Choose this if you're still actively experimenting with Snowflake.

  • The warehouse auto-suspends after 120 seconds of inactivity, so you will not burn credits while idle.
  • The transform_raw_to_staging task checks for new data every minute. It only consumes credits when there is new stream data to process.
  • The refresh_top_customers task fires only after the parent task succeeds, so it adds minimal extra usage.
  • Your trial lasts 30 days or until you exhaust the $400 credit balance, whichever comes first.

Pause - I'll come back to this later

Suspend the root task to stop all automation while keeping all your objects intact. The warehouse auto-suspends on its own.

  • Suspend the root task by running this command in your Snowsight worksheet:
ALTER TASK ECOMMERCE_DB.RAW.transform_raw_to_staging SUSPEND;
  • This stops the entire task graph. The dependent refresh_top_customers task will not fire since it relies on the root task completing first.
  • All your tables, stages, streams, and data remain untouched.
  • When you're ready to resume, run ALTER TASK ECOMMERCE_DB.RAW.transform_raw_to_staging RESUME; to restart automation.

Delete - I don't want to use this again

Remove all project resources and stop all credit consumption. This cannot be undone.

  • Drop the database by running this in your Snowsight worksheet:
DROP DATABASE ECOMMERCE_DB;
  • This single command removes all schemas, tables, stages, streams, and tasks inside the database.
  • Drop the warehouse by running:
DROP WAREHOUSE ECOMMERCE_WH;
  • This frees all compute resources and stops all credit consumption.
  • Delete the ecommerce_orders.csv file from your Desktop. Right-click the file and select Delete.

Nice Work!

Nice Work!

You just built a fully automated e-commerce data pipeline in Snowflake. From raw CSV data to clean analytics tables, your pipeline handles ingestion, transformation, and automation end-to-end.

Here's what you accomplished:

  • Created a 3-layer medallion architecture (RAW, STAGING, ANALYTICS) and loaded CSV data through an internal stage using COPY INTO.
  • Transformed raw string data into properly typed staging tables with computed columns, data validation, and business logic.
  • Automated the pipeline with Streams for change detection and Tasks for scheduled execution, so new data flows through the pipeline without manual intervention.
  • Built a Top Customers leaderboard using window functions and chained a dependent Task that refreshes analytics automatically after staging updates.

Ready to quiz yourself?