Snowflake Dynamic Tables and Materialized Views

Snowflake gives you two powerful tools for pre-computing and storing query results so that downstream users and applications get fast answers without running expensive queries every time. These tools are Dynamic Tables and Materialized Views. Both store pre-computed data, but they work differently and solve different problems. This page explains both from the ground up.

The Core Problem They Solve

Imagine a company's dashboard that shows the total sales per region, updated every hour. Without any caching or pre-computation, every time someone opens the dashboard, Snowflake scans millions of rows, joins several tables, applies filters, and computes totals. When 200 employees open the dashboard at 9 AM, that expensive query runs 200 times simultaneously.

The solution: compute the result once, store it, and serve the stored result to everyone. When fresh data arrives, recompute only what changed. Dynamic Tables and Materialized Views both do this, but they have different strengths.

What Is a Materialized View?

A Materialized View is a saved snapshot of a query result. It looks exactly like a regular table but its data comes from a query you define. Snowflake keeps the materialized view fresh automatically by refreshing it when the underlying base table changes.

Real-World Diagram: The Printed Report

Think of a printed weekly sales report sitting on a manager's desk. Someone prepared it in advance using data from the database. The manager reads it instantly without waiting for the database to process anything. When a new week starts, someone prints a fresh report. A Materialized View works the same way: pre-prepared data ready to read instantly, refreshed when conditions change.

Creating a Materialized View

CREATE OR REPLACE MATERIALIZED VIEW mv_daily_sales_summary AS
SELECT
  sale_date,
  region,
  product_category,
  SUM(revenue) AS total_revenue,
  COUNT(DISTINCT order_id) AS total_orders,
  AVG(revenue) AS avg_order_value
FROM orders
WHERE status = 'COMPLETED'
GROUP BY sale_date, region, product_category;

Once created, any query against mv_daily_sales_summary reads the pre-computed result, not the original orders table. Snowflake handles background refresh when the orders table receives new data.

Querying a Materialized View

-- This query reads from the pre-computed materialized view
-- Fast even if the orders table has billions of rows
SELECT region, SUM(total_revenue) AS revenue
FROM mv_daily_sales_summary
WHERE sale_date = CURRENT_DATE
GROUP BY region
ORDER BY revenue DESC;

Materialized View Limitations

Materialized Views in Snowflake have important restrictions you must understand before designing your solution:

• They can only read from a single base table. JOINs across multiple tables are not supported inside a materialized view definition.
• They do not support non-deterministic functions like CURRENT_TIMESTAMP or RANDOM().
• They cannot contain subqueries in the SELECT or WHERE clause.
• They support HAVING clauses and aggregations but not window functions in older Snowflake versions.
• Changes to the base table trigger an automatic but asynchronous refresh, so there can be a short lag.

What Is a Dynamic Table?

A Dynamic Table is a newer and more powerful feature in Snowflake. It automatically refreshes itself based on a defined target lag (how stale you are willing to accept the data to be). Unlike Materialized Views, Dynamic Tables support complex SQL including JOINs across multiple tables, window functions, CTEs, and subqueries.

Real-World Diagram: The Digital Dashboard Board

Picture the departure board at an airport. It does not show flights from three hours ago. It continuously updates itself based on a feed of live flight data. You set a rule: "refresh this board every 5 minutes." The board handles its own updates. A Dynamic Table is exactly this: you define the query and set the refresh lag, and Snowflake keeps the result current automatically.

Creating a Dynamic Table

CREATE OR REPLACE DYNAMIC TABLE dt_customer_order_summary
TARGET_LAG = '10 minutes'
WAREHOUSE = COMPUTE_WH
AS
SELECT
  c.customer_id,
  c.customer_name,
  c.region,
  o.order_year,
  o.order_month,
  COUNT(o.order_id) AS total_orders,
  SUM(o.revenue) AS total_spent,
  MAX(o.order_date) AS last_order_date
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
JOIN order_items oi ON o.order_id = oi.order_id
WHERE o.status = 'COMPLETED'
GROUP BY 1, 2, 3, 4, 5;

The key difference from a Materialized View is visible here: this query joins three tables (customers, orders, order_items). A Materialized View cannot do this. A Dynamic Table handles it easily.

TARGET_LAG = '10 minutes' tells Snowflake: keep this result no more than 10 minutes behind the source data. Snowflake automatically refreshes it to meet this target.

Dynamic Table Refresh Modes

Snowflake refreshes Dynamic Tables in two ways:

Incremental Refresh

Snowflake figures out what changed in the source tables and computes only the new or changed rows. This is very efficient. Think of it like editing only the changed paragraphs in a document instead of rewriting the whole thing.

Full Refresh

When incremental refresh is not possible (because the query is too complex for change tracking), Snowflake recomputes the entire result from scratch. This uses more compute but still happens automatically.

-- Check what refresh mode Snowflake chose for your dynamic table
SELECT name, refresh_mode, refresh_mode_reason
FROM information_schema.dynamic_tables
WHERE name = 'DT_CUSTOMER_ORDER_SUMMARY';

Setting and Adjusting Target Lag

-- Create with a 1-hour lag
CREATE OR REPLACE DYNAMIC TABLE dt_hourly_kpis
TARGET_LAG = '1 hour'
WAREHOUSE = REPORTING_WH
AS
SELECT ...;

-- Change the lag after creation
ALTER DYNAMIC TABLE dt_hourly_kpis SET TARGET_LAG = '30 minutes';

-- Manually trigger a refresh immediately
ALTER DYNAMIC TABLE dt_hourly_kpis REFRESH;

Shorter lag means fresher data but higher compute costs. Longer lag means lower cost but potentially stale data. You choose the right trade-off for each use case.

Chaining Dynamic Tables

One of the most powerful features of Dynamic Tables is the ability to chain them. One Dynamic Table can read from another Dynamic Table. This creates a pipeline of transformations, similar to staging tables in a data warehouse.

Diagram: The Water Treatment Plant

Water flows through multiple treatment stages: raw intake → sediment filter → chemical treatment → final purification → clean output. Each stage does one job and passes the result to the next. A chained Dynamic Table pipeline works the same way.

-- Stage 1: Clean raw events
CREATE OR REPLACE DYNAMIC TABLE dt_clean_events
TARGET_LAG = '5 minutes'
WAREHOUSE = COMPUTE_WH
AS
SELECT
  event_id,
  user_id,
  event_type,
  LOWER(TRIM(page_url)) AS page_url,
  event_timestamp::TIMESTAMP_NTZ AS event_time
FROM raw_clickstream_events
WHERE event_id IS NOT NULL
  AND user_id IS NOT NULL;

-- Stage 2: Aggregate cleaned events (reads from Stage 1)
CREATE OR REPLACE DYNAMIC TABLE dt_user_session_summary
TARGET_LAG = '10 minutes'
WAREHOUSE = COMPUTE_WH
AS
SELECT
  user_id,
  DATE(event_time) AS session_date,
  COUNT(*) AS total_events,
  COUNT(DISTINCT page_url) AS unique_pages_visited,
  MIN(event_time) AS session_start,
  MAX(event_time) AS session_end
FROM dt_clean_events
GROUP BY user_id, DATE(event_time);

-- Stage 3: Final reporting layer (reads from Stage 2)
CREATE OR REPLACE DYNAMIC TABLE dt_daily_engagement_report
TARGET_LAG = '15 minutes'
WAREHOUSE = REPORTING_WH
AS
SELECT
  session_date,
  COUNT(DISTINCT user_id) AS active_users,
  AVG(total_events) AS avg_events_per_user,
  AVG(unique_pages_visited) AS avg_pages_per_user
FROM dt_user_session_summary
GROUP BY session_date
ORDER BY session_date DESC;

When new raw events arrive, Snowflake refreshes Stage 1, then Stage 2, then Stage 3 automatically. Your reporting query hits Stage 3 and gets fresh, clean, aggregated data.

Monitoring Dynamic Tables

-- Check the refresh history of a dynamic table
SELECT *
FROM information_schema.dynamic_table_refresh_history
WHERE name = 'DT_CUSTOMER_ORDER_SUMMARY'
ORDER BY refresh_start_time DESC
LIMIT 20;

-- See all dynamic tables in the current database
SELECT name, target_lag, refresh_mode, scheduling_state
FROM information_schema.dynamic_tables
ORDER BY name;

Comparing Dynamic Tables and Materialized Views Side by Side

Diagram: The Specialized Tool vs The Swiss Army Knife

A Materialized View is like a specialized screwdriver: excellent for one specific job (single-table aggregations) and very efficient at that job. A Dynamic Table is like a Swiss Army knife: handles many different jobs (multi-table joins, complex transformations, chaining) and adapts to more situations.

Automatic Query Rewriting with Materialized Views

One unique advantage of Materialized Views is automatic query rewriting. When you run a query against the base table that matches the pattern of a materialized view, Snowflake automatically redirects the query to the materialized view — even if you did not reference the view in your SQL.

-- You write this query against the original orders table:
SELECT sale_date, region, SUM(revenue)
FROM orders
WHERE status = 'COMPLETED'
GROUP BY sale_date, region;

-- Snowflake internally rewrites it to:
SELECT sale_date, region, total_revenue
FROM mv_daily_sales_summary;
-- This happens transparently - the user sees faster results without changing their query

This feature means existing reports and dashboards get faster automatically after you create the materialized view, without anyone needing to update their SQL.

When to Use Each Feature

Use a Materialized View when:

• Your query reads from a single table
• You want existing queries to automatically get faster without changes
• You need simple aggregations like SUM, COUNT, AVG per group
• The base table updates frequently and you want automatic incremental refresh

Use a Dynamic Table when:

• Your transformation involves JOINs across multiple tables
• You want to build a multi-stage data transformation pipeline
• You need window functions, CTEs, or complex subqueries
• You want predictable refresh timing (every 5 minutes, every hour)
• You are replacing complex ETL pipelines with SQL-based pipelines inside Snowflake

Dropping and Managing These Objects

-- Pause a dynamic table (stop automatic refreshes)
ALTER DYNAMIC TABLE dt_customer_order_summary SUSPEND;

-- Resume a paused dynamic table
ALTER DYNAMIC TABLE dt_customer_order_summary RESUME;

-- Drop a dynamic table
DROP DYNAMIC TABLE IF EXISTS dt_customer_order_summary;

-- Drop a materialized view
DROP MATERIALIZED VIEW IF EXISTS mv_daily_sales_summary;

-- Check if a materialized view is up to date
SHOW MATERIALIZED VIEWS LIKE 'MV_DAILY_SALES_SUMMARY';

Key Points Summary

• Materialized Views pre-compute query results from a single table and serve fast reads. Snowflake automatically rewrites queries to use them.
• Dynamic Tables pre-compute results from complex multi-table queries and refresh themselves based on a user-defined target lag.
• Dynamic Tables support JOINs, CTEs, window functions, and chaining. Materialized Views do not.
• Chained Dynamic Tables build end-to-end data pipelines entirely in SQL, replacing traditional ETL tools.
• TARGET_LAG controls how fresh a Dynamic Table's data stays. Shorter lag costs more; longer lag costs less.
• Snowflake uses incremental refresh when possible to update only changed rows, making refreshes efficient.
• Use SUSPEND and RESUME to pause Dynamic Table refreshes during maintenance windows or cost-saving periods.