Enterprise Data Pipeline in Snowflake

Build a three-tier medallion data pipeline using Snowflake Dynamic Tables.

Introduction

30 Second Summary

Every time you order from a food truck, that transaction becomes a row of data. For a company running hundreds of trucks, millions of orders pile up fast.

In this project, you will build a three-tier data pipeline in Snowflake using Dynamic Tables. You'll ingest nearly 1 billion rows of food truck order data, transform it through multiple layers, and monitor how changes flow through the pipeline automatically.

What You'll Build

A multi-tier enterprise data pipeline that ingests raw order data from a fictitious food truck company called Tasty Bytes, enriches and joins it through Dynamic Tables, and pre-aggregates business metrics for analytics dashboards.

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

  • A three-tier medallion architecture pipeline (Raw, Curated, Analytics) built with five Dynamic Tables that Snowflake refreshes automatically.
  • A stored procedure that generates synthetic orders so you can test how changes propagate through every tier of the pipeline.
  • Production-grade monitoring queries that report refresh status, duration, and whether each refresh was incremental or full.
  • Secret Mission: Build the same transformation using the traditional Streams + Tasks pattern side-by-side with your Dynamic Table to compare both approaches.

Are there any prerequisites?

You should be comfortable with SQL (joins, CTEs, aggregations) and have a basic understanding of data warehousing concepts. Everything runs in the Snowflake web UI, so you don't need to install anything on your computer.

Before We Start

Before We Start...

Set Up Your Snowflake Environment

Every data pipeline needs infrastructure before any data can flow. You need a database to organize the data, a warehouse to process it, and tables to hold the raw source records.

In this step, you'll set up your Snowflake environment from scratch. You'll create a database, schemas, a warehouse, and three raw tables loaded with nearly 1 billion rows of food truck order data.

In this step, get ready to:
  • Open a SQL worksheet in Snowsight and set your role to ACCOUNTADMIN.
  • Create a dedicated role, database, schemas, and warehouse for the pipeline.
  • Create raw tables and load nearly 1 billion rows of data from a public S3 bucket.
Open a SQL worksheet in Snowsight

Snowsight is Snowflake's web-based interface where you write and run SQL. All of your pipeline work happens inside SQL worksheets here.

  • Log into your Snowflake trial account at the URL you received in your sign-up email.
  • In the left navigation menu, click Projects.
  • Click Worksheets.
  • Click the + button in the top-right corner of the Worksheets page.
  • Select SQL Worksheet.
  • In the top-right area of the worksheet, find the context selector (a row of dropdowns). Click the role dropdown and select ACCOUNTADMIN.

What is ACCOUNTADMIN?

ACCOUNTADMIN is the most powerful built-in role in Snowflake. You need it to create other roles, databases, and warehouses. You'll switch to a less-privileged role once the initial setup is done.

Running SQL in Snowsight

Throughout this project, you'll paste SQL into this worksheet and run it. To run code, highlight the lines you want to execute, then click the Run button (the play icon) in the top-right corner of the worksheet.

The keyboard shortcut is Cmd+Enter (macOS) or Ctrl+Enter (Windows) to run the selected code.

Create the database, schemas, and warehouse

Before you can store any data, you need three things: a role to own the pipeline objects, a database to organize them, and schemas to separate raw source data from transformed analytics data.

  • Create the lab_role, tasty_bytes_db database, and two schemas by pasting this SQL into your worksheet and running it:
USE ROLE ACCOUNTADMIN;

-- Create a dedicated role for the lab
CREATE ROLE IF NOT EXISTS lab_role;
GRANT ROLE lab_role TO ROLE ACCOUNTADMIN;

-- Create database with raw and analytics schemas
CREATE DATABASE IF NOT EXISTS tasty_bytes_db;
GRANT OWNERSHIP ON DATABASE tasty_bytes_db TO ROLE lab_role COPY CURRENT GRANTS;

USE ROLE lab_role;
USE DATABASE tasty_bytes_db;

CREATE SCHEMA IF NOT EXISTS raw;
CREATE SCHEMA IF NOT EXISTS analytics;

What does this code do?

  • lab_role is a dedicated role that owns all pipeline objects. This keeps your pipeline separate from the default ACCOUNTADMIN role.
  • tasty_bytes_db is the database that houses everything. Granting ownership to lab_role means all objects inside are managed by that role.
  • The raw schema stores source data. The analytics schema stores transformed data. This two-schema separation is the foundation of a medallion architecture.
  • After the statements finish, you should see Statement executed successfully messages in the results panel at the bottom of the worksheet.

Seeing an 'insufficient privileges' error?

Make sure the context selector in the top-right of your worksheet shows ACCOUNTADMIN as the active role. The first line of the SQL sets it explicitly, but the UI context must also allow it.

Help me fix a privileges error in Snowsight

  • Create the tasty_bytes_wh warehouse and grant permissions to lab_role by adding this SQL below your existing code and running it:
-- Create warehouse
USE ROLE ACCOUNTADMIN;
CREATE WAREHOUSE IF NOT EXISTS tasty_bytes_wh
  WAREHOUSE_SIZE = 'X-SMALL'
  AUTO_SUSPEND = 60
  AUTO_RESUME = TRUE
  INITIALLY_SUSPENDED = TRUE;

GRANT USAGE ON WAREHOUSE tasty_bytes_wh TO ROLE lab_role;
GRANT OPERATE ON WAREHOUSE tasty_bytes_wh TO ROLE lab_role;

USE ROLE lab_role;
USE DATABASE tasty_bytes_db;
USE WAREHOUSE tasty_bytes_wh;

What does this code do?

  • tasty_bytes_wh is an X-Small warehouse, the smallest (and cheapest) compute size in Snowflake.
  • AUTO_SUSPEND = 60 means the warehouse shuts down after 60 seconds of inactivity to save credits. AUTO_RESUME = TRUE starts it back up automatically when a query needs compute.
  • The GRANT USAGE and GRANT OPERATE statements let lab_role use and control the warehouse.
  • The final USE statements set your active role, database, and warehouse. All subsequent commands run in this context.
  • You should see Statement executed successfully in the results panel. The context selector at the top of your worksheet should now show LAB_ROLE as the active role.

Warehouse creation failed?

If you see a privilege error, make sure the previous code block ran successfully first. The USE ROLE ACCOUNTADMIN line at the top of this block switches back to the admin role, which is required to create warehouses.

Help me fix a warehouse creation error in Snowflake

Create raw tables and load data from S3

The pipeline processes data from a fictitious food truck company called Tasty Bytes. The source data lives in a public S3 bucket hosted by Snowflake, so you don't need an AWS account to access it.

You'll create three raw tables to hold the data, then load nearly 1 billion rows of CSV files directly from S3.

  • Define the three raw tables by adding this SQL below your existing code and running it:
-- Create raw tables
CREATE OR REPLACE TABLE raw.order_header (
  order_id NUMBER(38,0),
  truck_id NUMBER(38,0),
  location_id NUMBER(19,0),
  customer_id NUMBER(38,0),
  discount_id VARCHAR,
  shift_id NUMBER(38,0),
  shift_start_time TIME,
  shift_end_time TIME,
  order_channel VARCHAR,
  order_ts TIMESTAMP_NTZ,
  served_ts VARCHAR,
  order_currency VARCHAR,
  order_amount NUMBER(38,4),
  order_tax_amount VARCHAR,
  order_discount_amount VARCHAR,
  order_total NUMBER(38,4)
);

CREATE OR REPLACE TABLE raw.order_detail (
  order_detail_id NUMBER(38,0),
  order_id NUMBER(38,0),
  menu_item_id NUMBER(38,0),
  discount_id VARCHAR,
  line_number NUMBER(38,0),
  quantity NUMBER(5,0),
  unit_price NUMBER(38,4),
  price NUMBER(38,4),
  order_item_discount_amount VARCHAR
);

CREATE OR REPLACE TABLE raw.menu (
  menu_id NUMBER(19,0),
  menu_type_id NUMBER(38,0),
  menu_type VARCHAR,
  truck_brand_name VARCHAR,
  menu_item_id NUMBER(38,0),
  menu_item_name VARCHAR,
  item_category VARCHAR,
  item_subcategory VARCHAR,
  cost_of_goods_usd NUMBER(38,4),
  sale_price_usd NUMBER(38,4),
  menu_item_health_metrics_obj VARIANT
);

What do these three tables hold?

  • order_header stores order-level data: customer, truck, location, timestamps, and totals. This is the largest table at around 248 million rows.
  • order_detail stores line items for each order: which menu items were ordered, quantities, and prices. This table has around 673 million rows.
  • menu is a small dimension table with product names, categories, costs, and sale prices.
  • You should see Statement executed successfully for each CREATE TABLE statement. The tables are empty for now.

Table creation failed?

Make sure the previous code block ran successfully and that your context selector shows LAB_ROLE as the active role and TASTY_BYTES_DB as the active database.

Help me fix a table creation error in Snowflake

  • Create a file format, an external stage pointing to the public S3 bucket, and load the data by adding this SQL and running it:
-- Create file format and external stage
CREATE OR REPLACE FILE FORMAT raw.csv_ff
  TYPE = 'CSV';

CREATE OR REPLACE STAGE raw.tasty_bytes_stage
  URL = 's3://sfquickstarts/tastybytes/'
  FILE_FORMAT = raw.csv_ff;

-- Load data from S3 (this may take several minutes)
COPY INTO raw.menu FROM @raw.tasty_bytes_stage/raw_pos/menu/;
COPY INTO raw.order_header FROM @raw.tasty_bytes_stage/raw_pos/order_header/;
COPY INTO raw.order_detail FROM @raw.tasty_bytes_stage/raw_pos/order_detail/;

-- Verify data loaded
SELECT 'order_header' AS table_name, COUNT(*) AS row_count FROM raw.order_header
UNION ALL
SELECT 'order_detail', COUNT(*) FROM raw.order_detail
UNION ALL
SELECT 'menu', COUNT(*) FROM raw.menu;

This load takes 5-10 minutes

The COPY INTO statements are loading nearly 1 billion rows from S3 into your tables. With an X-Small warehouse, this takes 5-10 minutes. Let it run without interrupting.

What does this code do?

  • csv_ff tells Snowflake the incoming files are CSV format.
  • The external stage points to a public S3 bucket at s3://sfquickstarts/tastybytes/. No AWS credentials are needed because the bucket is publicly accessible.
  • COPY INTO loads each table's data from the stage. The dataset totals nearly 1 billion rows across the three tables.
  • The final SELECT COUNT(*) query verifies that each table received data by showing a row count.

When the load finishes, the final verification query runs automatically. You should see a result table with three rows showing the row counts for each table.

  • Confirm that order_header shows roughly 248 million rows, order_detail shows roughly 673 million rows, and menu shows a smaller count.

Seeing zero rows or a load error?

Check that the stage URL is exactly s3://sfquickstarts/tastybytes/ with the trailing slash. A typo in the URL or the file paths (raw_pos/menu/, etc.) causes the load to silently skip files.

If the query timed out, try running just the COPY INTO statements one at a time. Start with raw.menu (the smallest table) to confirm the stage connection works.

Help me fix a COPY INTO error in Snowflake

✔️ Awesome, I've got everything!

Great. Make sure your worksheet still has all the SQL you've run so far. You'll keep adding to it in the next step.

ⓧ I'd like to double check the full code

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

-- =============================================================================
-- STEP 1: Set Up Your Snowflake Environment
-- =============================================================================

USE ROLE ACCOUNTADMIN;

-- Create a dedicated role for the lab
CREATE ROLE IF NOT EXISTS lab_role;
GRANT ROLE lab_role TO ROLE ACCOUNTADMIN;

-- Create database with raw and analytics schemas
CREATE DATABASE IF NOT EXISTS tasty_bytes_db;
GRANT OWNERSHIP ON DATABASE tasty_bytes_db TO ROLE lab_role COPY CURRENT GRANTS;

USE ROLE lab_role;
USE DATABASE tasty_bytes_db;

CREATE SCHEMA IF NOT EXISTS raw;
CREATE SCHEMA IF NOT EXISTS analytics;

-- Create warehouse
USE ROLE ACCOUNTADMIN;
CREATE WAREHOUSE IF NOT EXISTS tasty_bytes_wh
  WAREHOUSE_SIZE = 'X-SMALL'
  AUTO_SUSPEND = 60
  AUTO_RESUME = TRUE
  INITIALLY_SUSPENDED = TRUE;

GRANT USAGE ON WAREHOUSE tasty_bytes_wh TO ROLE lab_role;
GRANT OPERATE ON WAREHOUSE tasty_bytes_wh TO ROLE lab_role;

USE ROLE lab_role;
USE DATABASE tasty_bytes_db;
USE WAREHOUSE tasty_bytes_wh;

-- Create raw tables
CREATE OR REPLACE TABLE raw.order_header (
  order_id NUMBER(38,0),
  truck_id NUMBER(38,0),
  location_id NUMBER(19,0),
  customer_id NUMBER(38,0),
  discount_id VARCHAR,
  shift_id NUMBER(38,0),
  shift_start_time TIME,
  shift_end_time TIME,
  order_channel VARCHAR,
  order_ts TIMESTAMP_NTZ,
  served_ts VARCHAR,
  order_currency VARCHAR,
  order_amount NUMBER(38,4),
  order_tax_amount VARCHAR,
  order_discount_amount VARCHAR,
  order_total NUMBER(38,4)
);

CREATE OR REPLACE TABLE raw.order_detail (
  order_detail_id NUMBER(38,0),
  order_id NUMBER(38,0),
  menu_item_id NUMBER(38,0),
  discount_id VARCHAR,
  line_number NUMBER(38,0),
  quantity NUMBER(5,0),
  unit_price NUMBER(38,4),
  price NUMBER(38,4),
  order_item_discount_amount VARCHAR
);

CREATE OR REPLACE TABLE raw.menu (
  menu_id NUMBER(19,0),
  menu_type_id NUMBER(38,0),
  menu_type VARCHAR,
  truck_brand_name VARCHAR,
  menu_item_id NUMBER(38,0),
  menu_item_name VARCHAR,
  item_category VARCHAR,
  item_subcategory VARCHAR,
  cost_of_goods_usd NUMBER(38,4),
  sale_price_usd NUMBER(38,4),
  menu_item_health_metrics_obj VARIANT
);

-- Create file format and external stage
CREATE OR REPLACE FILE FORMAT raw.csv_ff
  TYPE = 'CSV';

CREATE OR REPLACE STAGE raw.tasty_bytes_stage
  URL = 's3://sfquickstarts/tastybytes/'
  FILE_FORMAT = raw.csv_ff;

-- Load data from S3 (this may take several minutes)
COPY INTO raw.menu FROM @raw.tasty_bytes_stage/raw_pos/menu/;
COPY INTO raw.order_header FROM @raw.tasty_bytes_stage/raw_pos/order_header/;
COPY INTO raw.order_detail FROM @raw.tasty_bytes_stage/raw_pos/order_detail/;

-- Verify data loaded
SELECT 'order_header' AS table_name, COUNT(*) AS row_count FROM raw.order_header
UNION ALL
SELECT 'order_detail', COUNT(*) FROM raw.order_detail
UNION ALL
SELECT 'menu', COUNT(*) FROM raw.menu;

Your Snowflake environment is ready with nearly 1 billion rows of food truck data. Next up, you'll build five Dynamic Tables that transform this raw data into analytics-ready outputs.

Build the Three-Tier Dynamic Table Pipeline

Your Snowflake environment is loaded with nearly 1 billion rows of Tasty Bytes data across three raw tables. The raw data holds everything the business needs to understand food truck operations, but it is not in a form that analysts can easily query.

In this step, you'll transform that raw data into analytics-ready outputs using Dynamic Tables. You'll build five tables across three tiers following the medallion architecture pattern: enrichment, fact building, and aggregation.

What is the medallion architecture?

The medallion architecture organizes data into three tiers. The Raw tier holds source data exactly as it arrived. The Curated tier cleans and enriches that data. The Analytics tier pre-computes metrics that business users query directly.

Each tier builds on the one before it. Keeping them separate makes the pipeline easier to debug, test, and extend over time.

In this step, get ready to:
  • Create two Tier 1 Dynamic Tables that enrich raw order and menu data.
  • Create a Tier 2 fact table that joins the enriched data together.
  • Create two Tier 3 aggregation tables that pre-compute daily and product metrics.
Create the Tier 1 enriched tables

The first tier of the pipeline enriches raw data by adding calculated columns and cleaning up data types. You'll create two Dynamic Tables: one for order headers and one for order line items joined with menu details.

What is a Dynamic Table?

A Dynamic Table is a Snowflake table defined by a SQL SELECT query. Snowflake automatically keeps the table up to date when the source data changes. You don't write any scheduling or refresh logic yourself.

The TARGET_LAG parameter tells Snowflake how fresh the data needs to be. A value of '12 hours' means Snowflake guarantees the table is never more than 12 hours behind its source. You define what the table looks like, and Snowflake handles the rest.

  • Create a new SQL worksheet by clicking the + button at the top of the **Worksheets** tab and selecting SQL Worksheet.
  • Set your worksheet context by running these statements:
USE ROLE lab_role;
USE DATABASE tasty_bytes_db;
USE WAREHOUSE tasty_bytes_wh;

Running individual statements

Place your cursor inside a statement and press Cmd+Enter (macOS) or Ctrl+Enter (Windows) to run just that statement. You can also highlight multiple statements and press the same shortcut to run them together.

  • Create the first enriched Dynamic Table, orders_enriched, by running this SQL:
-- Tier 1: Enriched order headers
CREATE OR REPLACE DYNAMIC TABLE analytics.orders_enriched
  TARGET_LAG = '12 hours'
  WAREHOUSE = tasty_bytes_wh
  AS
SELECT
  order_id,
  truck_id,
  customer_id,
  order_channel,
  order_ts AS order_timestamp,
  DATE(order_ts) AS order_date,
  DAYNAME(order_ts) AS day_name,
  HOUR(order_ts) AS order_hour,
  order_amount,
  order_total,
  TRY_TO_NUMBER(order_discount_amount, 10, 2) AS order_discount_amount,
  CASE
    WHEN discount_id IS NOT NULL AND discount_id != '' THEN TRUE
    ELSE FALSE
  END AS has_discount
FROM tasty_bytes_db.raw.order_header
WHERE order_id IS NOT NULL
  AND order_ts IS NOT NULL;

What does this code do?

  • TARGET_LAG = '12 hours' tells Snowflake to keep this table within 12 hours of the source data. Snowflake decides when and how to refresh.
  • DATE(order_ts), DAYNAME(order_ts), and HOUR(order_ts) extract temporal dimensions so downstream tables can group by date, day of week, or hour without recalculating.
  • TRY_TO_NUMBER(order_discount_amount, 10, 2) safely converts the discount string to a number. If the value is empty or invalid, it returns NULL instead of failing.
  • The has_discount flag makes it easy for analysts to filter or count discounted orders without parsing raw discount IDs.
  • Run the statement and wait for it to complete.

The first refresh processes all existing rows in order_header (around 248 million). This may take a few minutes on the X-Small warehouse. You should see a success message in the results panel once it finishes.

Statement taking too long or failing?

  • If the statement seems stuck, give it up to 5 minutes. The X-Small warehouse is processing hundreds of millions of rows.
  • If you see an "object does not exist" error, check that you ran the three USE statements at the top of this worksheet and that the raw tables were loaded successfully in Step 1.
  • If you see an "insufficient privileges" error, confirm you are using lab_role by checking the context selector in the top-right of the worksheet.

Help me debug my Dynamic Table creation error in Snowflake.

  • Create the second enriched Dynamic Table, order_items_enriched, by running this SQL:
-- Tier 1: Enriched order items with menu details
CREATE OR REPLACE DYNAMIC TABLE analytics.order_items_enriched
  TARGET_LAG = '12 hours'
  WAREHOUSE = tasty_bytes_wh
  AS
SELECT
  od.order_detail_id,
  od.order_id,
  od.line_number,
  od.menu_item_id,
  m.menu_item_name,
  m.item_category,
  m.item_subcategory,
  m.truck_brand_name,
  m.menu_type,
  od.quantity,
  od.unit_price,
  od.price AS line_total,
  m.cost_of_goods_usd,
  m.sale_price_usd,
  (od.unit_price - m.cost_of_goods_usd) AS unit_profit,
  (od.unit_price - m.cost_of_goods_usd) * od.quantity AS line_profit,
  CASE
    WHEN od.unit_price > 0 THEN
      ROUND(((od.unit_price - m.cost_of_goods_usd) / od.unit_price) * 100, 2)
    ELSE 0
  END AS profit_margin_pct,
  TRY_TO_NUMBER(od.order_item_discount_amount, 10, 2) AS line_discount_amount,
  CASE
    WHEN od.discount_id IS NOT NULL AND od.discount_id != '' THEN TRUE
    ELSE FALSE
  END AS has_discount
FROM tasty_bytes_db.raw.order_detail od
INNER JOIN tasty_bytes_db.raw.menu m
  ON od.menu_item_id = m.menu_item_id
WHERE od.order_id IS NOT NULL
  AND od.menu_item_id IS NOT NULL;

What does this code do?

  • The INNER JOIN combines each order detail line with its matching menu item. This pulls in product names, categories, and cost-of-goods data that only exists in the menu table.
  • unit_profit and line_profit calculate the margin on each item. profit_margin_pct expresses profit as a percentage of the selling price.
  • This table is the heaviest in the pipeline because it joins order_detail (around 673 million rows) with the menu dimension table.
  • Run the statement and wait for the initial refresh to complete. This one processes even more rows than the first, so expect it to take a few minutes.

You should see a success message once the refresh finishes. Both Tier 1 tables are now populated and will automatically stay fresh within 12 hours of their source data.

Seeing an error on order_items_enriched?

  • A "table does not exist" error on raw.menu means the menu data was not loaded in Step 1. Go back and run the COPY INTO raw.menu statement from your Step 1 worksheet.
  • If the refresh completes but seems slow, the X-Small warehouse is handling a 673-million-row join. This is expected behavior.

Help me troubleshoot my order_items_enriched Dynamic Table.

Create the Tier 2 fact table

Tier 2 combines the two enriched tables into a single comprehensive view. Every row represents one order line item with full order context, product details, and profit calculations attached.

This table uses a different refresh strategy. Instead of a time-based lag, it uses DOWNSTREAM, which means it only refreshes when a table that depends on it needs fresh data.

What does DOWNSTREAM mean?

When TARGET_LAG = 'DOWNSTREAM', Snowflake uses a pull-based refresh model. The table sits idle until a downstream consumer triggers a refresh. In your pipeline, the Tier 3 aggregation tables drive the entire refresh chain.

This avoids wasting compute on intermediate tables that nobody queries directly. Snowflake only does work when the final output actually needs fresher data.

  • Create the order_fact Dynamic Table by running this SQL:
-- Tier 2: Fact table joining orders and items
CREATE OR REPLACE DYNAMIC TABLE analytics.order_fact
  TARGET_LAG = 'DOWNSTREAM'
  WAREHOUSE = tasty_bytes_wh
  AS
SELECT
  o.order_id,
  o.truck_id,
  o.customer_id,
  o.order_channel,
  o.order_timestamp,
  o.order_date,
  o.day_name,
  o.order_hour,
  o.order_amount,
  o.order_total,
  o.order_discount_amount AS order_level_discount,
  o.has_discount AS order_has_discount,
  oi.order_detail_id,
  oi.line_number,
  oi.menu_item_id,
  oi.menu_item_name,
  oi.item_category,
  oi.item_subcategory,
  oi.truck_brand_name,
  oi.menu_type,
  oi.quantity,
  oi.unit_price,
  oi.line_total,
  oi.cost_of_goods_usd,
  oi.sale_price_usd,
  oi.unit_profit,
  oi.line_profit,
  oi.profit_margin_pct,
  oi.line_discount_amount,
  oi.has_discount AS line_has_discount
FROM analytics.orders_enriched o
INNER JOIN analytics.order_items_enriched oi
  ON o.order_id = oi.order_id;

What does this code do?

  • This table joins orders_enriched (order-level data) with order_items_enriched (line-item data) on order_id. The result is a denormalized fact table where every row is one order line with full context.
  • Notice it reads from the Tier 1 Dynamic Tables, not from the raw tables. Snowflake tracks these dependencies automatically and knows which tables feed into which.
  • The column aliases order_level_discount and order_has_discount distinguish order-level discounts from line-item discounts, since both are now in the same table.
  • Run the statement and wait for it to complete.

You should see a success message. You won't query this table directly because it serves as the foundation for the Tier 3 aggregation tables you'll create next.

Seeing an "object does not exist" error?

  • This table references analytics.orders_enriched and analytics.order_items_enriched. If either Tier 1 table was not created successfully, this statement will fail.
  • Go back and re-run the Tier 1 CREATE statements. Make sure each one completed with a success message before moving on.

Help me fix my order_fact Dynamic Table creation.

Create the Tier 3 aggregated metrics

Tier 3 is the analytics layer. These tables pre-compute the metrics that business users and dashboards query directly. Pre-aggregating means analysts get fast results without running expensive calculations on billions of rows every time.

  • Create the daily_business_metrics Dynamic Table by running this SQL:
-- Tier 3: Daily business metrics
CREATE OR REPLACE DYNAMIC TABLE analytics.daily_business_metrics
  TARGET_LAG = 'DOWNSTREAM'
  WAREHOUSE = tasty_bytes_wh
  AS
SELECT
  order_date,
  day_name,
  COUNT(DISTINCT order_id) AS total_orders,
  COUNT(DISTINCT truck_id) AS active_trucks,
  COUNT(DISTINCT customer_id) AS unique_customers,
  SUM(quantity) AS total_items_sold,
  SUM(order_total) AS total_revenue,
  ROUND(AVG(order_total), 2) AS avg_order_value,
  SUM(line_total) AS total_line_item_revenue,
  SUM(line_profit) AS total_profit,
  ROUND(AVG(profit_margin_pct), 2) AS avg_profit_margin_pct,
  SUM(CASE WHEN order_has_discount THEN 1 ELSE 0 END) AS orders_with_discount,
  SUM(order_level_discount) AS total_order_discount_amount,
  SUM(line_discount_amount) AS total_line_discount_amount
FROM analytics.order_fact
GROUP BY order_date, day_name;

What does this code do?

  • This table aggregates the entire order_fact table into one row per day. Each row contains revenue, profit, order counts, and discount metrics for that date.
  • COUNT(DISTINCT order_id) counts unique orders, not line items. An order with 5 items still counts as 1 order.
  • This is the kind of table a BI dashboard would query directly. An analyst can ask "What was Tuesday's revenue?" and get an instant answer.
  • Run the statement and wait for it to complete.

You won't see results yet. The next Dynamic Table completes the pipeline, and then you'll verify everything end-to-end.

  • Create the product_performance_metrics Dynamic Table by running this SQL:
-- Tier 3: Product performance metrics
CREATE OR REPLACE DYNAMIC TABLE analytics.product_performance_metrics
  TARGET_LAG = 'DOWNSTREAM'
  WAREHOUSE = tasty_bytes_wh
  AS
SELECT
  menu_item_id,
  menu_item_name,
  item_category,
  item_subcategory,
  truck_brand_name,
  menu_type,
  COUNT(DISTINCT order_id) AS order_count,
  SUM(quantity) AS total_units_sold,
  SUM(line_total) AS total_revenue,
  SUM(line_profit) AS total_profit,
  ROUND(AVG(unit_price), 2) AS avg_unit_price,
  ROUND(AVG(profit_margin_pct), 2) AS avg_profit_margin_pct,
  AVG(cost_of_goods_usd) AS avg_cogs,
  AVG(sale_price_usd) AS standard_sale_price,
  SUM(line_total) / NULLIF(SUM(quantity), 0) AS revenue_per_unit,
  SUM(line_profit) / NULLIF(SUM(quantity), 0) AS profit_per_unit
FROM analytics.order_fact
GROUP BY
  menu_item_id,
  menu_item_name,
  item_category,
  item_subcategory,
  truck_brand_name,
  menu_type;

What does this code do?

  • This table aggregates by menu item instead of by date. Each row represents one product with its total sales, profit, and per-unit economics.
  • NULLIF(SUM(quantity), 0) prevents division-by-zero errors when computing revenue_per_unit and profit_per_unit. If quantity is zero, the result is NULL instead of an error.
  • Run the statement and wait for it to complete.

All five Dynamic Tables are now created. Time to verify that data flows all the way through the pipeline.

  • Verify the daily business metrics by running this query:
-- Verify pipeline: check aggregated data
SELECT order_date, total_orders, total_revenue, total_profit
FROM analytics.daily_business_metrics
ORDER BY order_date DESC
LIMIT 5;

You should see five rows showing the most recent dates with order counts, revenue, and profit values. If the results panel shows data, your pipeline is working end-to-end.

  • Verify the product performance metrics by running this query:
SELECT menu_item_name, item_category, total_units_sold, total_revenue, total_profit
FROM analytics.product_performance_metrics
ORDER BY total_revenue DESC
LIMIT 10;

You should see ten rows showing the top-selling menu items ranked by revenue. Each row includes the item name, category, units sold, and profit.

Seeing zero rows or NULL values?

  • If you see zero rows, the initial refresh for one or more upstream Dynamic Tables may not have completed yet. Wait a few minutes and re-run the query.
  • If revenue and profit columns are all NULL, check that the order_items_enriched table was created successfully. The profit calculations depend on the menu join in Tier 1.

Help me debug why my Dynamic Table pipeline returns no data.

  • In the left navigation menu, click Transformation.
  • Click Dynamic tables.
  • Click on order_fact in the list to open its detail view.

You should see a dependency graph showing order_fact in the center with its two upstream Tier 1 tables and two downstream Tier 3 tables connected. This graph is how Snowflake visualizes the entire pipeline and its automatic dependency tracking.

✔️ Awesome, I've got everything!

Make sure you have all five Dynamic Tables returning data and that you can see the dependency graph in the Snowsight UI.

ⓧ I'd like to double check the full code

Here is the full SQL for this step. Compare it with what you have in your worksheet.

-- =============================================================================
-- STEP 2: Build the Three-Tier Dynamic Table Pipeline
-- =============================================================================

USE ROLE lab_role;
USE DATABASE tasty_bytes_db;
USE WAREHOUSE tasty_bytes_wh;

-- Tier 1: Enriched order headers
CREATE OR REPLACE DYNAMIC TABLE analytics.orders_enriched
  TARGET_LAG = '12 hours'
  WAREHOUSE = tasty_bytes_wh
  AS
SELECT
  order_id,
  truck_id,
  customer_id,
  order_channel,
  order_ts AS order_timestamp,
  DATE(order_ts) AS order_date,
  DAYNAME(order_ts) AS day_name,
  HOUR(order_ts) AS order_hour,
  order_amount,
  order_total,
  TRY_TO_NUMBER(order_discount_amount, 10, 2) AS order_discount_amount,
  CASE
    WHEN discount_id IS NOT NULL AND discount_id != '' THEN TRUE
    ELSE FALSE
  END AS has_discount
FROM tasty_bytes_db.raw.order_header
WHERE order_id IS NOT NULL
  AND order_ts IS NOT NULL;

-- Tier 1: Enriched order items with menu details
CREATE OR REPLACE DYNAMIC TABLE analytics.order_items_enriched
  TARGET_LAG = '12 hours'
  WAREHOUSE = tasty_bytes_wh
  AS
SELECT
  od.order_detail_id,
  od.order_id,
  od.line_number,
  od.menu_item_id,
  m.menu_item_name,
  m.item_category,
  m.item_subcategory,
  m.truck_brand_name,
  m.menu_type,
  od.quantity,
  od.unit_price,
  od.price AS line_total,
  m.cost_of_goods_usd,
  m.sale_price_usd,
  (od.unit_price - m.cost_of_goods_usd) AS unit_profit,
  (od.unit_price - m.cost_of_goods_usd) * od.quantity AS line_profit,
  CASE
    WHEN od.unit_price > 0 THEN
      ROUND(((od.unit_price - m.cost_of_goods_usd) / od.unit_price) * 100, 2)
    ELSE 0
  END AS profit_margin_pct,
  TRY_TO_NUMBER(od.order_item_discount_amount, 10, 2) AS line_discount_amount,
  CASE
    WHEN od.discount_id IS NOT NULL AND od.discount_id != '' THEN TRUE
    ELSE FALSE
  END AS has_discount
FROM tasty_bytes_db.raw.order_detail od
INNER JOIN tasty_bytes_db.raw.menu m
  ON od.menu_item_id = m.menu_item_id
WHERE od.order_id IS NOT NULL
  AND od.menu_item_id IS NOT NULL;

-- Tier 2: Fact table joining orders and items
CREATE OR REPLACE DYNAMIC TABLE analytics.order_fact
  TARGET_LAG = 'DOWNSTREAM'
  WAREHOUSE = tasty_bytes_wh
  AS
SELECT
  o.order_id,
  o.truck_id,
  o.customer_id,
  o.order_channel,
  o.order_timestamp,
  o.order_date,
  o.day_name,
  o.order_hour,
  o.order_amount,
  o.order_total,
  o.order_discount_amount AS order_level_discount,
  o.has_discount AS order_has_discount,
  oi.order_detail_id,
  oi.line_number,
  oi.menu_item_id,
  oi.menu_item_name,
  oi.item_category,
  oi.item_subcategory,
  oi.truck_brand_name,
  oi.menu_type,
  oi.quantity,
  oi.unit_price,
  oi.line_total,
  oi.cost_of_goods_usd,
  oi.sale_price_usd,
  oi.unit_profit,
  oi.line_profit,
  oi.profit_margin_pct,
  oi.line_discount_amount,
  oi.has_discount AS line_has_discount
FROM analytics.orders_enriched o
INNER JOIN analytics.order_items_enriched oi
  ON o.order_id = oi.order_id;

-- Tier 3: Daily business metrics
CREATE OR REPLACE DYNAMIC TABLE analytics.daily_business_metrics
  TARGET_LAG = 'DOWNSTREAM'
  WAREHOUSE = tasty_bytes_wh
  AS
SELECT
  order_date,
  day_name,
  COUNT(DISTINCT order_id) AS total_orders,
  COUNT(DISTINCT truck_id) AS active_trucks,
  COUNT(DISTINCT customer_id) AS unique_customers,
  SUM(quantity) AS total_items_sold,
  SUM(order_total) AS total_revenue,
  ROUND(AVG(order_total), 2) AS avg_order_value,
  SUM(line_total) AS total_line_item_revenue,
  SUM(line_profit) AS total_profit,
  ROUND(AVG(profit_margin_pct), 2) AS avg_profit_margin_pct,
  SUM(CASE WHEN order_has_discount THEN 1 ELSE 0 END) AS orders_with_discount,
  SUM(order_level_discount) AS total_order_discount_amount,
  SUM(line_discount_amount) AS total_line_discount_amount
FROM analytics.order_fact
GROUP BY order_date, day_name;

-- Tier 3: Product performance metrics
CREATE OR REPLACE DYNAMIC TABLE analytics.product_performance_metrics
  TARGET_LAG = 'DOWNSTREAM'
  WAREHOUSE = tasty_bytes_wh
  AS
SELECT
  menu_item_id,
  menu_item_name,
  item_category,
  item_subcategory,
  truck_brand_name,
  menu_type,
  COUNT(DISTINCT order_id) AS order_count,
  SUM(quantity) AS total_units_sold,
  SUM(line_total) AS total_revenue,
  SUM(line_profit) AS total_profit,
  ROUND(AVG(unit_price), 2) AS avg_unit_price,
  ROUND(AVG(profit_margin_pct), 2) AS avg_profit_margin_pct,
  AVG(cost_of_goods_usd) AS avg_cogs,
  AVG(sale_price_usd) AS standard_sale_price,
  SUM(line_total) / NULLIF(SUM(quantity), 0) AS revenue_per_unit,
  SUM(line_profit) / NULLIF(SUM(quantity), 0) AS profit_per_unit
FROM analytics.order_fact
GROUP BY
  menu_item_id,
  menu_item_name,
  item_category,
  item_subcategory,
  truck_brand_name,
  menu_type;

-- Verify pipeline: check aggregated data
SELECT order_date, total_orders, total_revenue, total_profit
FROM analytics.daily_business_metrics
ORDER BY order_date DESC
LIMIT 5;

SELECT menu_item_name, item_category, total_units_sold, total_revenue, total_profit
FROM analytics.product_performance_metrics
ORDER BY total_revenue DESC
LIMIT 10;

Your three-tier pipeline is live. Five Dynamic Tables, zero scheduling logic, and Snowflake manages the entire dependency chain. Next up, you'll put the pipeline to the test by generating new data and watching incremental refresh in action.

Test Incremental Refresh with Synthetic Data

Your five Dynamic Tables are live, but so far they've only processed the original historical data. In a real enterprise pipeline, new data arrives constantly, and the system needs to handle those changes without recomputing everything from scratch.

In this step, you'll create a stored procedure that generates realistic synthetic orders. Then you'll insert them into the raw tables, trigger a manual refresh, and confirm that Snowflake processes only the new rows.

In this step, get ready to:
  • Create a stored procedure that generates realistic test data from existing orders.
  • Insert 500 synthetic orders and manually trigger a refresh across all pipeline tiers.
  • Verify that Dynamic Tables used incremental refresh instead of full recomputation.
Create a stored procedure to generate test data

To test incremental refresh, you need new rows in the raw tables. Instead of loading another batch from S3, you'll build a procedure that samples existing orders and creates realistic variations with randomized prices and today's date.

  • Confirm your session context by running these statements at the top of your SQL Worksheet:
USE ROLE lab_role;
USE DATABASE tasty_bytes_db;
USE WAREHOUSE tasty_bytes_wh;
  • Create the generate_demo_orders stored procedure by running this SQL:
-- Create stored procedure to generate synthetic orders
CREATE OR REPLACE PROCEDURE raw.generate_demo_orders(num_rows INTEGER)
RETURNS STRING
LANGUAGE SQL
AS
$$
DECLARE
  orders_before INTEGER;
  orders_after INTEGER;
  orders_inserted INTEGER;
  details_before INTEGER;
  details_after INTEGER;
  details_inserted INTEGER;
BEGIN
  SELECT COUNT(*) INTO :orders_before FROM tasty_bytes_db.raw.order_header;
  SELECT COUNT(*) INTO :details_before FROM tasty_bytes_db.raw.order_detail;

  CREATE OR REPLACE TEMPORARY TABLE new_orders AS
  SELECT
    (1000000 + UNIFORM(1, 999999, RANDOM()))::NUMBER(38,0) AS new_order_id,
    oh.order_id AS original_order_id,
    oh.truck_id,
    oh.location_id,
    oh.customer_id,
    oh.discount_id,
    oh.shift_id,
    oh.shift_start_time,
    oh.shift_end_time,
    oh.order_channel,
    DATEADD('day', DATEDIFF('day', oh.order_ts, CURRENT_DATE()), oh.order_ts) AS order_ts,
    oh.served_ts,
    oh.order_currency,
    oh.order_amount * (0.8 + UNIFORM(0, 0.4, RANDOM())) AS order_amount,
    oh.order_tax_amount,
    oh.order_discount_amount,
    oh.order_total * (0.8 + UNIFORM(0, 0.4, RANDOM())) AS order_total
  FROM tasty_bytes_db.raw.order_header oh
  WHERE oh.order_id IS NOT NULL
  ORDER BY RANDOM()
  LIMIT :num_rows;

  INSERT INTO tasty_bytes_db.raw.order_header (
    order_id, truck_id, location_id, customer_id, discount_id, shift_id,
    shift_start_time, shift_end_time, order_channel, order_ts, served_ts,
    order_currency, order_amount, order_tax_amount, order_discount_amount,
    order_total
  )
  SELECT
    new_order_id, truck_id, location_id, customer_id, discount_id, shift_id,
    shift_start_time, shift_end_time, order_channel, order_ts, served_ts,
    order_currency, order_amount, order_tax_amount, order_discount_amount,
    order_total
  FROM new_orders;

  INSERT INTO tasty_bytes_db.raw.order_detail (
    order_detail_id, order_id, menu_item_id, discount_id, line_number,
    quantity, unit_price, price, order_item_discount_amount
  )
  SELECT
    (2000000 + UNIFORM(1, 9999999, RANDOM()))::NUMBER(38,0) AS order_detail_id,
    no.new_order_id AS order_id,
    od.menu_item_id,
    od.discount_id,
    od.line_number,
    od.quantity,
    od.unit_price * (0.8 + UNIFORM(0, 0.4, RANDOM())) AS unit_price,
    od.price * (0.8 + UNIFORM(0, 0.4, RANDOM())) AS price,
    od.order_item_discount_amount
  FROM new_orders no
  INNER JOIN tasty_bytes_db.raw.order_detail od
    ON no.original_order_id = od.order_id;

  SELECT COUNT(*) INTO :orders_after FROM tasty_bytes_db.raw.order_header;
  SELECT COUNT(*) INTO :details_after FROM tasty_bytes_db.raw.order_detail;

  orders_inserted := :orders_after - :orders_before;
  details_inserted := :details_after - :details_before;

  DROP TABLE IF EXISTS new_orders;

  RETURN 'Successfully generated ' || orders_inserted::STRING || ' new orders with ' ||
         details_inserted::STRING || ' line items. Total orders: ' || orders_after::STRING;
END;
$$;

What does this procedure do?

  • Samples num_rows random orders from order_header and creates new variants with unique IDs.
  • Randomizes prices by plus or minus 20% and sets the timestamp to today's date so new orders are clearly distinguishable.
  • Generates matching order_detail line items for each new order to maintain referential integrity between the two raw tables.
  • Returns a summary string reporting how many orders and line items were created.
  • You should see a success message confirming Procedure GENERATE_DEMO_ORDERS successfully created.

Seeing a syntax error?

  • Make sure you selected the entire CREATE PROCEDURE block, from CREATE OR REPLACE PROCEDURE all the way through the closing $$; delimiter.
  • Check that your active role is lab_role and database is tasty_bytes_db. You can verify these in the context selector at the top-right of the worksheet.
  • Still stuck? Help me debug a syntax error when creating my generate_demo_orders stored procedure in Snowflake.
Generate orders and trigger refresh

With the procedure ready, you can now generate 500 synthetic orders and push them through the pipeline. You'll manually trigger the refresh so you can observe exactly when each tier processes the changes.

  • Generate 500 synthetic orders by running this statement:
-- Generate 500 new synthetic orders
CALL raw.generate_demo_orders(500);

What does this call do?

This executes the stored procedure you just created with 500 as the input. The procedure samples 500 real orders, creates new versions with randomized prices and today's date, and inserts them (along with their line items) into the raw tables.

  • The result pane should display a summary string like Successfully generated 500 new orders with ... line items.
  • Verify the inserts by running these count queries:
-- Verify new data
SELECT COUNT(*) AS total_orders FROM raw.order_header;
SELECT COUNT(*) AS total_order_details FROM raw.order_detail;
  • Both counts should be higher than the original load totals. The order_header count increases by 500, and order_detail increases by however many line items those 500 orders had.

Seeing zero rows inserted?

  • Confirm you ran the CREATE PROCEDURE statement in the previous substep first. The CALL fails silently if the procedure doesn't exist.
  • Check that the procedure was created in the raw schema by running SHOW PROCEDURES IN SCHEMA raw.
  • Need a hand? Help me troubleshoot why my generate_demo_orders procedure isn't inserting rows.
  • Now trigger a manual refresh across all five Dynamic Tables by running these statements in tier order:
-- Manually trigger refresh across all tiers
ALTER DYNAMIC TABLE analytics.orders_enriched REFRESH;
ALTER DYNAMIC TABLE analytics.order_items_enriched REFRESH;
ALTER DYNAMIC TABLE analytics.order_fact REFRESH;
ALTER DYNAMIC TABLE analytics.daily_business_metrics REFRESH;
ALTER DYNAMIC TABLE analytics.product_performance_metrics REFRESH;

Why refresh in tier order?

Tier 1 tables (orders_enriched and order_items_enriched) read directly from the raw tables, so they need to process the new rows first.

Tier 2 and Tier 3 tables use TARGET_LAG = 'DOWNSTREAM', meaning they only refresh when a downstream consumer requests fresh data. By triggering them manually here, you simulate what would happen automatically on the scheduled TARGET_LAG cycle.

  • Each ALTER statement should complete without errors. The Tier 1 refreshes may take a moment as they process the new data.

Refresh taking a very long time?

  • The Tier 1 refresh should take under a minute for 500 new orders. If it hangs for several minutes, check that your warehouse is running by looking at the context selector in your worksheet.
  • If you see an error about the warehouse being suspended, run ALTER WAREHOUSE tasty_bytes_wh RESUME and try the REFRESH again.
  • Need help? Help me troubleshoot why my Dynamic Table refresh is hanging or failing.
Verify incremental refresh and query results

The real test is whether Snowflake processed only the 500 new orders or recomputed the entire table. You can find out by querying the refresh history for each Dynamic Table.

  • Check the refresh history for the Tier 1 and Tier 2 Dynamic Tables by running these queries:
-- Check refresh history for incremental vs full
SELECT name, refresh_action, state, refresh_start_time, refresh_trigger
FROM TABLE(INFORMATION_SCHEMA.DYNAMIC_TABLE_REFRESH_HISTORY(
  NAME => 'tasty_bytes_db.ANALYTICS.ORDERS_ENRICHED'
))
ORDER BY refresh_start_time DESC LIMIT 5;

SELECT name, refresh_action, state, refresh_start_time, refresh_trigger
FROM TABLE(INFORMATION_SCHEMA.DYNAMIC_TABLE_REFRESH_HISTORY(
  NAME => 'tasty_bytes_db.ANALYTICS.ORDER_ITEMS_ENRICHED'
))
ORDER BY refresh_start_time DESC LIMIT 5;

SELECT name, refresh_action, state, refresh_start_time, refresh_trigger
FROM TABLE(INFORMATION_SCHEMA.DYNAMIC_TABLE_REFRESH_HISTORY(
  NAME => 'tasty_bytes_db.ANALYTICS.ORDER_FACT'
))
ORDER BY refresh_start_time DESC LIMIT 5;

What should I look for in the results?

  • The refresh_action column is the key. The initial creation refresh always shows FULL because Snowflake had to process every existing row.
  • The most recent refresh (the one you just triggered) should show INCREMENTAL. This means Snowflake only processed the 500 new orders instead of recomputing the entire table.
  • The refresh_trigger column should show MANUAL for the refreshes you triggered with ALTER, and CREATION for the initial load.
  • Run each query and check that the most recent row for each table shows INCREMENTAL in the refresh_action column and SUCCEEDED in the state column.

Seeing FULL instead of INCREMENTAL?

  • If the most recent refresh shows FULL, Snowflake may have determined that a full refresh was more efficient for the current data volume. This can happen in certain query patterns.
  • Make sure you're looking at the most recent row (the one with the latest refresh_start_time). The earlier rows from creation will always show FULL.
  • Need help interpreting? Help me understand my Dynamic Table refresh history results in Snowflake.
  • Now query the analytics tables to see your new data flowing through to the final tier by running:
-- View updated metrics from today
SELECT order_date, total_orders, total_items_sold, total_revenue, total_profit
FROM analytics.daily_business_metrics
ORDER BY order_date DESC LIMIT 5;

SELECT menu_item_name, item_category, total_units_sold, total_revenue, total_profit
FROM analytics.product_performance_metrics
ORDER BY total_revenue DESC
LIMIT 10;

What should I see?

The first query shows daily metrics. Today's date should appear at the top of the results with the 500 newly generated orders, their revenue, and profit totals.

The second query shows product performance. The top-selling items should reflect the updated totals now that your synthetic orders have been folded in.

  • Confirm that today's date appears in the daily_business_metrics results with a non-zero total_orders value.

✔️ Awesome, I've got everything!

Great. Double check you've run all the statements in your worksheet before moving on.

ⓧ I'd like to double check the full code

Here is the complete SQL for this step. Compare it against the statements in your worksheet.

-- =============================================================================
-- STEP 3: Test Incremental Refresh with Synthetic Data
-- =============================================================================

USE ROLE lab_role;
USE DATABASE tasty_bytes_db;
USE WAREHOUSE tasty_bytes_wh;

-- Create stored procedure to generate synthetic orders
CREATE OR REPLACE PROCEDURE raw.generate_demo_orders(num_rows INTEGER)
RETURNS STRING
LANGUAGE SQL
AS
$$
DECLARE
  orders_before INTEGER;
  orders_after INTEGER;
  orders_inserted INTEGER;
  details_before INTEGER;
  details_after INTEGER;
  details_inserted INTEGER;
BEGIN
  SELECT COUNT(*) INTO :orders_before FROM tasty_bytes_db.raw.order_header;
  SELECT COUNT(*) INTO :details_before FROM tasty_bytes_db.raw.order_detail;

  CREATE OR REPLACE TEMPORARY TABLE new_orders AS
  SELECT
    (1000000 + UNIFORM(1, 999999, RANDOM()))::NUMBER(38,0) AS new_order_id,
    oh.order_id AS original_order_id,
    oh.truck_id,
    oh.location_id,
    oh.customer_id,
    oh.discount_id,
    oh.shift_id,
    oh.shift_start_time,
    oh.shift_end_time,
    oh.order_channel,
    DATEADD('day', DATEDIFF('day', oh.order_ts, CURRENT_DATE()), oh.order_ts) AS order_ts,
    oh.served_ts,
    oh.order_currency,
    oh.order_amount * (0.8 + UNIFORM(0, 0.4, RANDOM())) AS order_amount,
    oh.order_tax_amount,
    oh.order_discount_amount,
    oh.order_total * (0.8 + UNIFORM(0, 0.4, RANDOM())) AS order_total
  FROM tasty_bytes_db.raw.order_header oh
  WHERE oh.order_id IS NOT NULL
  ORDER BY RANDOM()
  LIMIT :num_rows;

  INSERT INTO tasty_bytes_db.raw.order_header (
    order_id, truck_id, location_id, customer_id, discount_id, shift_id,
    shift_start_time, shift_end_time, order_channel, order_ts, served_ts,
    order_currency, order_amount, order_tax_amount, order_discount_amount,
    order_total
  )
  SELECT
    new_order_id, truck_id, location_id, customer_id, discount_id, shift_id,
    shift_start_time, shift_end_time, order_channel, order_ts, served_ts,
    order_currency, order_amount, order_tax_amount, order_discount_amount,
    order_total
  FROM new_orders;

  INSERT INTO tasty_bytes_db.raw.order_detail (
    order_detail_id, order_id, menu_item_id, discount_id, line_number,
    quantity, unit_price, price, order_item_discount_amount
  )
  SELECT
    (2000000 + UNIFORM(1, 9999999, RANDOM()))::NUMBER(38,0) AS order_detail_id,
    no.new_order_id AS order_id,
    od.menu_item_id,
    od.discount_id,
    od.line_number,
    od.quantity,
    od.unit_price * (0.8 + UNIFORM(0, 0.4, RANDOM())) AS unit_price,
    od.price * (0.8 + UNIFORM(0, 0.4, RANDOM())) AS price,
    od.order_item_discount_amount
  FROM new_orders no
  INNER JOIN tasty_bytes_db.raw.order_detail od
    ON no.original_order_id = od.order_id;

  SELECT COUNT(*) INTO :orders_after FROM tasty_bytes_db.raw.order_header;
  SELECT COUNT(*) INTO :details_after FROM tasty_bytes_db.raw.order_detail;

  orders_inserted := :orders_after - :orders_before;
  details_inserted := :details_after - :details_before;

  DROP TABLE IF EXISTS new_orders;

  RETURN 'Successfully generated ' || orders_inserted::STRING || ' new orders with ' ||
         details_inserted::STRING || ' line items. Total orders: ' || orders_after::STRING;
END;
$$;

-- Generate 500 new synthetic orders
CALL raw.generate_demo_orders(500);

-- Verify new data
SELECT COUNT(*) AS total_orders FROM raw.order_header;
SELECT COUNT(*) AS total_order_details FROM raw.order_detail;

-- Manually trigger refresh across all tiers
ALTER DYNAMIC TABLE analytics.orders_enriched REFRESH;
ALTER DYNAMIC TABLE analytics.order_items_enriched REFRESH;
ALTER DYNAMIC TABLE analytics.order_fact REFRESH;
ALTER DYNAMIC TABLE analytics.daily_business_metrics REFRESH;
ALTER DYNAMIC TABLE analytics.product_performance_metrics REFRESH;

-- Check refresh history for incremental vs full
SELECT name, refresh_action, state, refresh_start_time, refresh_trigger
FROM TABLE(INFORMATION_SCHEMA.DYNAMIC_TABLE_REFRESH_HISTORY(
  NAME => 'tasty_bytes_db.ANALYTICS.ORDERS_ENRICHED'
))
ORDER BY refresh_start_time DESC LIMIT 5;

SELECT name, refresh_action, state, refresh_start_time, refresh_trigger
FROM TABLE(INFORMATION_SCHEMA.DYNAMIC_TABLE_REFRESH_HISTORY(
  NAME => 'tasty_bytes_db.ANALYTICS.ORDER_ITEMS_ENRICHED'
))
ORDER BY refresh_start_time DESC LIMIT 5;

SELECT name, refresh_action, state, refresh_start_time, refresh_trigger
FROM TABLE(INFORMATION_SCHEMA.DYNAMIC_TABLE_REFRESH_HISTORY(
  NAME => 'tasty_bytes_db.ANALYTICS.ORDER_FACT'
))
ORDER BY refresh_start_time DESC LIMIT 5;

-- View updated metrics from today
SELECT order_date, total_orders, total_items_sold, total_revenue, total_profit
FROM analytics.daily_business_metrics
ORDER BY order_date DESC LIMIT 5;

SELECT menu_item_name, item_category, total_units_sold, total_revenue, total_profit
FROM analytics.product_performance_metrics
ORDER BY total_revenue DESC
LIMIT 10;

You've proven that your pipeline handles changes efficiently. Snowflake detected the 500 new orders and processed only those rows through all five Dynamic Tables. Next up, you'll build monitoring queries to track pipeline health like a production data engineering team.

Monitor Pipeline Health

In the last step, you generated synthetic orders and watched Snowflake process only the changed rows through your entire pipeline. You now have a working three-tier pipeline with proven incremental refresh.

Building a pipeline is only half the job. In this step, you will build production-grade monitoring queries that track refresh status, duration, and data freshness across all five Dynamic Tables.

In this step, get ready to:
  • List all Dynamic Tables and review their metadata.
  • Build a refresh summary dashboard query using a window function.
  • Check pipeline lag metrics and explore the Snowsight monitoring UI.
List all Dynamic Tables and their status

Before diving into detailed queries, you need a quick way to check the overall state of every Dynamic Table in your pipeline. The SHOW DYNAMIC TABLES command gives you a metadata snapshot in one shot.

  • Set the session context and list all Dynamic Tables in the analytics schema by running this code in your SQL worksheet:
-- Set session context
USE ROLE lab_role;
USE DATABASE tasty_bytes_db;
USE WAREHOUSE tasty_bytes_wh;

-- List all Dynamic Tables and their metadata
SHOW DYNAMIC TABLES IN SCHEMA tasty_bytes_db.analytics;

What does this show?

The result set includes one row per Dynamic Table with key metadata columns:

  • target_lag shows how fresh the data should be (12 hours for Tier 1, DOWNSTREAM for Tiers 2 and 3).
  • scheduling_state confirms each table is actively managed by Snowflake.
  • rows and data_timestamp tell you how much data each table holds and when it was last refreshed.
  • Highlight the SHOW DYNAMIC TABLES statement and press Cmd+Enter (macOS) or Ctrl+Enter (Windows) to run it.

You should see 5 rows in the results pane, one for each Dynamic Table in the analytics schema.

Seeing 0 rows or an error?

  • Make sure you ran the USE ROLE lab_role and USE DATABASE tasty_bytes_db statements first. The lab_role owns these tables.
  • Check that the schema name is tasty_bytes_db.analytics with no typos.
  • Why aren't my Dynamic Tables showing up in the SHOW command?
Build a refresh summary dashboard query

SHOW DYNAMIC TABLES gives you the current state, but production monitoring also needs refresh history. The INFORMATION_SCHEMA.DYNAMIC_TABLE_REFRESH_HISTORY() function returns up to 7 days of refresh events across all your Dynamic Tables.

The challenge is that this function returns multiple refreshes per table. You only want the most recent one for each table to build a dashboard-style view.

  • Build a refresh summary dashboard by running this query:
-- Refresh summary dashboard: most recent refresh per Dynamic Table
SELECT
  name,
  refresh_action,
  state,
  refresh_start_time,
  refresh_end_time,
  DATEDIFF('second', refresh_start_time, refresh_end_time) AS refresh_duration_seconds
FROM (
  SELECT name, refresh_action, state, refresh_start_time, refresh_end_time,
         ROW_NUMBER() OVER (PARTITION BY name ORDER BY refresh_start_time DESC) AS rn
  FROM TABLE(INFORMATION_SCHEMA.DYNAMIC_TABLE_REFRESH_HISTORY())
)
WHERE rn = 1
ORDER BY name;

What does this query do?

  • The inner subquery calls DYNAMIC_TABLE_REFRESH_HISTORY() to pull all recent refresh events. It applies ROW_NUMBER() partitioned by table name and ordered by most recent first.
  • WHERE rn = 1 keeps only the latest refresh for each Dynamic Table.
  • DATEDIFF('second', ...) calculates how many seconds each refresh took to complete.
  • refresh_action shows INCREMENTAL (only changed rows processed) or FULL (entire table recomputed). Your most recent refreshes should be INCREMENTAL from the 500 synthetic orders you inserted.
  • Highlight the query and run it.

You should see 5 rows, one per Dynamic Table. The state column should show SUCCEEDED for each table, and the refresh_action column should show INCREMENTAL for the refreshes triggered by your synthetic data insert.

Seeing FULL instead of INCREMENTAL?

  • Snowflake decides at refresh time whether incremental processing is possible. Some query patterns force a full refresh. This is expected for certain table structures.
  • If the state column shows SUCCEEDED, the pipeline is healthy regardless of whether the refresh was incremental or full.
  • Why is my Dynamic Table refresh showing FULL instead of INCREMENTAL?

Why build this as a query?

This is the kind of query a data engineering team would schedule on a cron job or embed in a monitoring dashboard like Grafana or Datadog. Instead of clicking through the UI for each table, one query surfaces the health of the entire pipeline.

Check pipeline lag metrics and explore the UI

Refresh history tells you what happened in the past. Lag metrics tell you how well the pipeline is keeping up right now. Snowflake tracks how far behind each Dynamic Table is from the freshest available data.

  • Query lag metrics for your entire pipeline by running this code:
-- Lag metrics across all connected Dynamic Tables
SELECT
  name,
  target_lag_sec,
  mean_lag_sec,
  maximum_lag_sec,
  time_within_target_lag_ratio,
  latest_data_timestamp
FROM TABLE(
  INFORMATION_SCHEMA.DYNAMIC_TABLES(
    NAME => 'tasty_bytes_db.analytics.daily_business_metrics',
    INCLUDE_CONNECTED => TRUE
  )
)
ORDER BY name;

What does this query do?

  • INFORMATION_SCHEMA.DYNAMIC_TABLES() returns lag metrics for Dynamic Tables. Passing INCLUDE_CONNECTED => TRUE uses daily_business_metrics as the anchor and returns every table in the dependency chain.
  • mean_lag_sec and maximum_lag_sec show the average and worst-case delay between source data changes and the Dynamic Table reflecting those changes.
  • time_within_target_lag_ratio is the percentage of time the table met its TARGET_LAG freshness target. A value close to 1.0 means the pipeline is on track.
  • Highlight the query and run it.

You should see 5 rows, one for each connected Dynamic Table in the pipeline. The time_within_target_lag_ratio values should be close to 1.0 since you have been manually triggering refreshes.

Seeing NULL for lag columns?

  • Lag metrics need time to accumulate. If your Dynamic Tables were created very recently, some columns may show NULL. This is normal and will populate after more refresh cycles.
  • Make sure the NAME parameter uses the fully qualified table name tasty_bytes_db.analytics.daily_business_metrics with no typos.
  • Why are my Dynamic Table lag metrics showing NULL?

Snowflake also has a built-in visual monitoring dashboard for Dynamic Tables. Let's explore it.

  • In the left navigation menu, click Transformation.
  • Click Dynamic tables.
  • Click on order_fact from the list.

You should see a dependency graph showing order_fact at the center with its upstream tables (orders_enriched and order_items_enriched) and downstream tables (daily_business_metrics and product_performance_metrics).

What can I see in this UI?

The Snowsight monitoring UI shows refresh history, current status, refresh duration, actual lag time, and rows changed per refresh. This is a visual complement to the SQL-based monitoring queries you just built.

Click on any table in the dependency graph to drill into its individual refresh history. This is useful for quickly spotting which table in the chain is causing a lag bottleneck.

✔️ Awesome, I've got everything!

Great. Double check you have all three queries saved in your worksheet.

ⓧ I'd like to double check the full code

Here is the complete monitoring file. Compare it against your worksheet:

-- =============================================================================
-- STEP 4: Monitor Pipeline Health
-- =============================================================================

USE ROLE lab_role;
USE DATABASE tasty_bytes_db;
USE WAREHOUSE tasty_bytes_wh;

-- List all Dynamic Tables and their metadata
SHOW DYNAMIC TABLES IN SCHEMA tasty_bytes_db.analytics;

-- Refresh summary dashboard: most recent refresh per Dynamic Table
SELECT
  name,
  refresh_action,
  state,
  refresh_start_time,
  refresh_end_time,
  DATEDIFF('second', refresh_start_time, refresh_end_time) AS refresh_duration_seconds
FROM (
  SELECT name, refresh_action, state, refresh_start_time, refresh_end_time,
         ROW_NUMBER() OVER (PARTITION BY name ORDER BY refresh_start_time DESC) AS rn
  FROM TABLE(INFORMATION_SCHEMA.DYNAMIC_TABLE_REFRESH_HISTORY())
)
WHERE rn = 1
ORDER BY name;

-- Lag metrics across all connected Dynamic Tables
SELECT
  name,
  target_lag_sec,
  mean_lag_sec,
  maximum_lag_sec,
  time_within_target_lag_ratio,
  latest_data_timestamp
FROM TABLE(
  INFORMATION_SCHEMA.DYNAMIC_TABLES(
    NAME => 'tasty_bytes_db.analytics.daily_business_metrics',
    INCLUDE_CONNECTED => TRUE
  )
)
ORDER BY name;

Secret mission

Build the Same Transformation with Streams + Tasks

Dynamic Tables handle change data capture automatically. But what if you need more control over how changes are processed? Build the traditional Streams + Tasks pattern side-by-side with your Dynamic Table to understand both approaches and when each one makes sense.

Before you go

Clean Up Your Resources

Decide whether to keep your resources running, pause them to come back later, or delete them entirely.

This project runs in your Snowflake free trial account. Dynamic Tables with a TARGET_LAG schedule trigger periodic warehouse refreshes that consume credits, even when you are not actively using the project.

Cost warning

Your Dynamic Tables are configured to refresh every 12 hours. Each refresh resumes the tasty_bytes_wh warehouse and consumes a small amount of your $400 trial credit balance.

If you are done with this project, choose Pause or Delete to stop credit consumption.

Resources you used:

  • tasty_bytes_db database (contains raw tables, Dynamic Tables, stages, file formats, and stored procedures).
  • tasty_bytes_wh warehouse (X-Small, auto-suspend enabled).
  • lab_role role (custom role with ownership of the database).

Keep everything running

No action needed. Choose this if you are still actively experimenting with the pipeline or plan to continue exploring Dynamic Tables.

  • Your trial includes $400 in credits, and this pipeline uses a small fraction of that balance.
  • The warehouse auto-suspends after 60 seconds of inactivity, so credits are only consumed during active refreshes.
  • Dynamic Tables will continue refreshing on schedule (every 12 hours for Tier 1 tables).

Pause - I'll come back to this later

Suspend the Dynamic Tables to stop automatic refreshes while keeping all your data and pipeline definitions intact. You can resume later without rebuilding anything.

  • Open a SQL worksheet in Snowsight.
  • Suspend each Dynamic Table by running these commands:
ALTER DYNAMIC TABLE tasty_bytes_db.analytics.orders_enriched SUSPEND;
ALTER DYNAMIC TABLE tasty_bytes_db.analytics.order_items_enriched SUSPEND;
ALTER DYNAMIC TABLE tasty_bytes_db.analytics.order_fact SUSPEND;
ALTER DYNAMIC TABLE tasty_bytes_db.analytics.daily_business_metrics SUSPEND;
ALTER DYNAMIC TABLE tasty_bytes_db.analytics.product_performance_metrics SUSPEND;
  • When you are ready to come back, resume each Dynamic Table with:
ALTER DYNAMIC TABLE tasty_bytes_db.analytics.orders_enriched RESUME;
ALTER DYNAMIC TABLE tasty_bytes_db.analytics.order_items_enriched RESUME;
ALTER DYNAMIC TABLE tasty_bytes_db.analytics.order_fact RESUME;
ALTER DYNAMIC TABLE tasty_bytes_db.analytics.daily_business_metrics RESUME;
ALTER DYNAMIC TABLE tasty_bytes_db.analytics.product_performance_metrics RESUME;

Delete - I don't want to use this again

Remove all project resources completely. This drops the database (including all tables, Dynamic Tables, stages, and procedures), the warehouse, and optionally the custom role.

  • Open a SQL worksheet in Snowsight.
  • Run the following cleanup script:
-- Drop all project resources
USE ROLE lab_role;
DROP DATABASE IF EXISTS tasty_bytes_db;
DROP WAREHOUSE IF EXISTS tasty_bytes_wh;

-- Optionally drop the role (requires ACCOUNTADMIN)
-- USE ROLE ACCOUNTADMIN;
-- DROP ROLE IF EXISTS lab_role;
  • To also remove the lab_role, uncomment the last two lines and run them as ACCOUNTADMIN.
  • Once complete, your Snowflake trial account will have no remaining resources from this project.

Mission accomplished

That's a Wrap!

Nice work! You just built an enterprise-grade data pipeline in Snowflake that processes nearly 1 billion rows across three tiers of Dynamic Tables. That is no small thing.

You've learned how to:

  • Built a three-tier medallion architecture pipeline (Raw > Curated > Analytics) with five Dynamic Tables, declarative SQL, and automatic dependency management.
  • Tested incremental refresh by generating synthetic data with a stored procedure and watching changes propagate automatically through all pipeline tiers.
  • Built production-grade monitoring queries using INFORMATION_SCHEMA functions to track refresh status, duration, and lag metrics across the entire pipeline.
  • Secret Mission: Built a Streams + Tasks CDC pipeline side-by-side with Dynamic Tables and compared both approaches.

Ready to quiz yourself?