Overview

Prerequisites

What You'll Learn

What You'll Need

What You'll Build

Open Snowflake CoCo

Set Up the Environment

Using ACCOUNTADMIN, create a role called lab_role and grant it CREATE WAREHOUSE
and CREATE DATABASE privileges on the account. Then switch to lab_role and:
- Create a database called tasty_bytes_db with two schemas: raw and analytics
- Create a 2XL standard warehouse called tasty_bytes_wh with 60s auto-suspend
  and auto-resume enabled
- Create these tables in tasty_bytes_db.raw:
  - order_header: columns for order_id, truck_id, location_id, customer_id,
    discount_id, shift_id, shift_start_time, shift_end_time, order_channel,
    order_ts (TIMESTAMP_NTZ), served_ts, order_currency, order_amount (NUMBER),
    order_tax_amount, order_discount_amount, order_total (NUMBER)
  - order_detail: columns for order_detail_id, order_id, menu_item_id,
    discount_id, line_number, quantity, unit_price, price, order_item_discount_amount
  - menu: columns for menu_id, menu_type_id, menu_type, truck_brand_name,
    menu_item_id, menu_item_name, item_category, item_subcategory,
    cost_of_goods_usd, sale_price_usd, menu_item_health_metrics_obj (VARIANT)
- Create a CSV file format in tasty_bytes_db.public
- Create an external stage tasty_bytes_db.raw.tasty_bytes_stage pointing to
  s3://sfquickstarts/tasty-bytes-builder-education/ using the CSV file format
- Load all three tables using COPY INTO from the stage subdirectories:
  raw_pos/order_header/, raw_pos/order_detail/, raw_pos/menu/

Note: When CoCo asks for permission to execute SQL, you'll see an approval prompt with three options:

• Allow Once — approves only this specific statement
• Allow CREATE — approves all CREATE statements for the current session
• Always Allow CREATE — permanently allows CREATE statements without prompting in future sessions

Note: If you encounter permission errors, ensure you're running as ACCOUNTADMIN when creating the role and granting privileges.

Understanding Dynamic Tables

Build the Pipeline

Build a 3-tier dynamic table pipeline in tasty_bytes_db.analytics using
warehouse tasty_bytes_wh:

Use INITIALIZE = 'ON_SCHEDULE' for all dynamic tables.

Tier 1 - Enrichment (TARGET_LAG = DOWNSTREAM):
- orders_enriched: From tasty_bytes_db.raw.order_header. Include order_id,
  truck_id, customer_id, order_channel. Add temporal dimensions: order_date,
  day_name (DAYNAME), order_hour (HOUR), and order_ts as order_timestamp.
  Include order_amount and order_total. Cast order_discount_amount to NUMBER(10,2)
  using TRY_TO_NUMBER. Add a has_discount boolean (true when discount_id is not
  null and not empty string). Filter out null order_id and null order_ts.

- order_items_enriched: Join tasty_bytes_db.raw.order_detail with
  tasty_bytes_db.raw.menu on menu_item_id. Include order_detail_id, order_id,
  line_number, menu_item_id, menu_item_name, item_category, item_subcategory,
  truck_brand_name, menu_type, quantity, unit_price, price as line_total,
  cost_of_goods_usd, sale_price_usd. Calculate unit_profit (unit_price minus
  cost_of_goods_usd), line_profit (unit_profit times quantity), and
  profit_margin_pct (rounded to 2 decimals, handle zero unit_price). Cast
  order_item_discount_amount to NUMBER(10,2) as line_discount_amount. Add
  has_discount flag. Filter out null order_id and null menu_item_id.

Tier 2 - Fact Table (TARGET_LAG = DOWNSTREAM):
- order_fact: Inner join orders_enriched (alias o) with order_items_enriched
  (alias oi) on order_id. Include all columns from both. Rename
  o.order_discount_amount as order_level_discount and o.has_discount as
  order_has_discount, oi.has_discount as line_has_discount.

Tier 3 - Aggregated Metrics (TARGET_LAG = 1 hour):
- daily_business_metrics: Aggregate order_fact by order_date and day_name.
  Include count distinct order_id (total_orders), truck_id (active_trucks),
  customer_id (unique_customers), sum quantity (total_items_sold), sum
  order_total (total_revenue), avg order_total (avg_order_value), sum
  line_total (total_line_item_revenue), sum line_profit (total_profit),
  avg profit_margin_pct (avg_profit_margin_pct), count orders with discount,
  sum discount amounts.

- product_performance_metrics: Aggregate order_fact by menu_item_id,
  menu_item_name, item_category, item_subcategory, truck_brand_name, menu_type.
  Include count distinct order_id (order_count), sum quantity (total_units_sold),
  sum line_total (total_revenue), sum line_profit (total_profit), avg unit_price,
  avg profit_margin_pct, avg cost_of_goods_usd (avg_cogs), avg sale_price_usd
  (standard_sale_price), revenue_per_unit, profit_per_unit.

Review before executing: Verify that TARGET_LAG values match (DOWNSTREAM for Tier 1-2, 1 hour for Tier 3), INITIALIZE = 'ON_SCHEDULE' is set on all tables, join conditions are correct, and the dependency flows raw → Tier 1 → Tier 2 → Tier 3.

Why ON_SCHEDULE? With INITIALIZE = 'ON_SCHEDULE', all 5 CREATE statements return immediately — no waiting for an initial full scan of 1 billion rows. The pipeline is defined but empty. You'll trigger the first load manually in the next step, giving you full control over when the expensive initial refresh happens.

Refresh daily_business_metrics and product_performance_metrics in
tasty_bytes_db.analytics. Snowflake will handle the upstream dependencies.

What to observe: You only triggered Tier 3 — but Snowflake automatically refreshed orders_enriched and order_items_enriched (Tier 1), then order_fact (Tier 2), before completing the Tier 3 aggregations. This is dependency graph management in action.

View Dependency Graph

Show me all dynamic tables in tasty_bytes_db.analytics with their target lag settings

Generate Test Data

Create a stored procedure tasty_bytes_db.raw.generate_demo_orders(num_rows INTEGER)
using SQL language that generates synthetic orders. It should:
- Capture row counts in order_header and order_detail before insertion
- Sample num_rows random existing orders from order_header into a temporary table
- Assign new unique order IDs to avoid conflicts with existing data
- Set timestamps to the current date while preserving the original time-of-day
- Apply random price variation of plus or minus 20% using UNIFORM
- Insert the new orders into order_header
- For each new order, copy the corresponding order_detail records from the
  original order, generating new order_detail_ids and applying the same
  price randomization
- Capture row counts after insertion
- Clean up the temporary table
- Return a summary string showing how many orders and line items were inserted

Review before executing: This is a complex stored procedure — verify it creates a temp table with new_order_id mappings and joins back to order_detail to preserve referential integrity.

Test Incremental Refresh

How many rows are currently in tasty_bytes_db.raw.order_header and
tasty_bytes_db.raw.order_detail?

Call tasty_bytes_db.raw.generate_demo_orders(1000)

Refresh daily_business_metrics and product_performance_metrics in
tasty_bytes_db.analytics.

What to observe: You only issued refresh commands for the Tier 3 tables — but Snowflake automatically cascaded through Tier 2 and Tier 1 first. This is the DOWNSTREAM dependency model at work.

Query tasty_bytes_db.information_schema.dynamic_table_refresh_history() for each
of the 5 dynamic tables in tasty_bytes_db.analytics. Show the most recent
refresh_action, state, refresh_trigger, and duration in seconds for each.

Key insight: The refresh_action column should show INCREMENTAL — Snowflake processed only the 1,000 new orders through the entire pipeline, not the full billion-row dataset. This is what makes Dynamic Tables efficient at scale.

Discussion: Notice that product_performance_metrics shows a FULL refresh. Why? This table aggregates across all products — when a new order arrives for an existing product (e.g., "The King Combo"), the aggregate row for that product must be updated. Snowflake rewrites the affected group rather than appending a new row. Does a full rewrite make sense here? Consider: with 1,000 new rows out of 1 billion, the rewrite only touches the affected product groups — it's still far more efficient than processing all source data.

Query tasty_bytes_db.analytics.daily_business_metrics ordered by order_date
descending, limit 5. Then query tasty_bytes_db.analytics.product_performance_metrics
ordered by total_revenue descending, limit 10.

Monitor Pipeline

Show a monitoring summary for all dynamic tables in tasty_bytes_db.analytics.
For each table, show its name, target_lag, scheduling state, and — from
tasty_bytes_db.information_schema.dynamic_table_refresh_history() — the most
recent refresh_action, state, and duration in seconds. Use a window function
to get only the latest refresh per table.

Create Semantic View and Agent

Create a semantic view called tasty_bytes_semantic_model in TASTY_BYTES_DB.ANALYTICS
over all 5 dynamic tables in the analytics schema: orders_enriched,
order_items_enriched, order_fact, daily_business_metrics, and
product_performance_metrics. Then create a Cortex Agent called tasty_bytes_agent
in TASTY_BYTES_DB.ANALYTICS that uses this semantic view as its Cortex Analyst
tool. Set the display name to "Tasty Bytes Analytics Agent".

Test Your Agent

Clean up

Drop database tasty_bytes_db and warehouse tasty_bytes_wh using lab_role.
Then switch to ACCOUNTADMIN and drop lab_role.

Conclusion and Resources

What You Learned

Related Resources