About this lab
Fernridge Market is an online marketplace. Every day three seller integrations send their orders, each in a different format: Seller A exports CSV, Seller B sends nested JSON, and Seller C drops Parquet files throughout the day.
In this lab you stage all three files, load the CSV with COPY INTO, land the JSON in a VARIANT column and flatten it into typed columns, and automate the Parquet feed with a Snowpipe that loads on serverless compute. You combine the sellers into one orders table and unload a compressed settlement extract for finance.
Then you build the pipeline that runs every day after loading: a stream captures each insert, update, and delete, a branching task graph turns those changes into an audit history, seller payouts, fraud flags, and an operations digest, and a dynamic table rebuilds the payout summary declaratively. You finish by running both approaches on the same new order and confirming they agree, so you know when to reach for each one.
Lab details
- Level: Intermediate
- Cloud: Snowflake
- Domain: Data Engineering
- Duration: 90 minutes
What you will learn
- Load CSV, nested JSON, and Parquet files from a stage with COPY INTO and the right file formats
- Automate a feed with Snowpipe and confirm what it loaded with COPY_HISTORY
- Unload a query result to a compressed CSV extract
- Capture inserts, updates, and deletes with a stream
- Build a branching task graph with a conditional root, parallel children, and a fan-in task
- Rebuild the same result as a dynamic table and decide when to use each approach
Concepts covered
- Stages and file formats
- Bulk loading and unloading
- Semi-structured data with VARIANT
- Snowpipe
- Change data capture with streams
- Task graphs
- Dynamic tables vs tasks
Technologies used
- Stages and File Formats
- COPY INTO
- Snowpipe
- Snowflake Streams
- Snowflake Tasks
- Dynamic Tables
Prerequisites
- Basic SQL (SELECT, INSERT, GROUP BY)
- A Snowflake trial account with the ACCOUNTADMIN role
- The lab dataset files downloaded from the lab page
Topics
- Snowflake
- Data Ingestion
- Snowpipe
- COPY INTO
- Streams
- Tasks
- Dynamic Tables
- Parquet
- JSON
- SQL
