Pre-baked tables: materialized views, dynamic & interactive tables
A plain view redoes its SQL on every query. These three objects do the work ahead of time and keep themselves fresh, so the read stays cheap.
Ismail's last lever is to do the heavy work before anyone asks for it. A standard view is just a saved SELECT. Every time someone queries it, the warehouse runs the whole thing from scratch. That's fine for light work, but a dashboard hitting the same heavy aggregation hundreds of times a day pays for it hundreds of times. Snowflake gives you three objects that compute the answer in advance and maintain it for you. They aren't rivals you pick between: each does a different job, and each usually reads from your ordinary tables or dbt marts.
Three jobs, three objects
Prepare
Dynamic table. Joins and cleans, keeps itself fresh to a lag you set. Reads tables and other dynamic tables.
Pre-aggregate
Materialized view. One heavy GROUP BY on one table, kept in sync serverless. Enterprise edition.
Serve
Interactive table. Hundreds of selective lookups a minute, sub-second. Reads any table, often a dbt mart.
They don't chain freely, so don't design a pipeline that assumes they do: a dynamic table can't read from a materialized view, and you can't build a dynamic table, a stream or a standard materialized view on top of an interactive table. Pick the one object that fits the job, on top of tables you already have.
Materialized view: the answer, pre-calculated
Take a heavy aggregation and add one keyword. Snowflake runs the GROUP BY once, physically stores the result in its own micro-partitions, and then keeps it in sync on its own. As rows are inserted, updated or deleted in the base table, a hidden serverless engine updates the result in the background. No Airflow DAG, no cron, no warehouse of yours running.
-- a plain view recomputes this on every dashboard load CREATE MATERIALIZED VIEW regional_sales_ SELECT region, count(*) AS orders, sum(amount) AS revenue FROM raw_sales GROUP BY region; -- one word, MATERIALIZED, changes everything behind itmv AS
You don't pay compute on every read anymore, you pay storage for the saved result plus the serverless credits to keep it fresh (visible in materialized_). So there's a golden question: is the read saving worth the maintenance cost?
Two details make it nicer than it looks. Queries are never stale: if maintenance is behind, Snowflake fills the gap from the base table at query time. And the optimizer can use the view on its own: a dashboard that still queries raw_sales with the same GROUP BY can be rewritten to read the materialized view, without anyone changing the dashboard. The limits are strict, though: one table, no joins, no window functions, no non-deterministic functions, and Enterprise edition only.
Use it
Avoid it
Dynamic table: a pipeline that maintains itself
When you need joins, cleaning, or several steps chained together, that's a dynamic table. You write the final SELECT (joins and all), then add two things a view doesn't have: a TARGET_LAG (how fresh the data must stay) and a WAREHOUSE (what does the work). You declare what you want and how fresh, not how to schedule it.
CREATE DYNAMIC TABLE clean_sales_ TARGET_LAG = '1 hour' -- "never let this be more than 1h stale" WAREHOUSE = transform_wh -- the engine that refreshes it AS SELECT s.order_id, c.dt customer_ FROM raw_sales s JOIN customer_dim c ON s.name, s.amount customer_ WHERE s.status = 'VALID';id = c. customer_ id
Instead of rebuilding the whole table on a rigid schedule, Snowflake watches the inputs and does an incremental refresh: it processes only the rows that changed since last time and merges them in. Same freshness, a fraction of the compute. Chain several dynamic tables and Snowflake works out the dependency order for you, the same way dbt's ref() graph does.
Incremental isn't guaranteed, and that's the detail that surprises people on the bill. REFRESH_MODE defaults to AUTO: if the query uses something Snowflake can't refresh incrementally (some window functions, non-deterministic functions, certain outer join shapes), it quietly falls back to a FULL rebuild every time. Check the refresh mode Snowflake picked with SHOW DYNAMIC TABLES, and set REFRESH_MODE = INCREMENTAL explicitly when you want creation to fail rather than silently rebuild in full. In a chain, give the intermediate tables TARGET_LAG = DOWNSTREAM: they refresh only when a table after them needs fresh data.
Use it
Avoid it
Interactive table: a serving counter built for crowds
Materialized views and dynamic tables prepare the data. An interactive table serves it. Standard warehouses are built for big analytical scans, so when fifty people each filter a dashboard their own way, they generate fifty unique queries, the result cache misses, and they queue behind one warehouse, staring at a spinner. Picture two hundred sales reps each pulling their own customer list, all day. An interactive table takes a different path: it's a different kind of table queried through a different kind of compute, an interactive warehouse, tuned in memory and indexing for selective lookups at high concurrency.
Use it
Avoid it
-- created on a standard warehouse; CLUSTER BY is mandatory CREATE INTERACTIVE TABLE customer_lookup CLUSTER BY (sales_ TARGET_LAG = '5 minutes' -- optional: keep it fresh from the source WAREHOUSE = transform_wh AS SELECT * FROM analytics.rep_ id) -- the column the dashboards filter on marts. dim_ customer;
The fine print matters more here than usual:
- The cluster key is the whole point. It's required at creation, and lookups are instant only when the query filters on it.
- No row-level changes.
UPDATEandDELETEaren't allowed. You either reload it withINSERT OVERWRITE, or give it aTARGET_LAGand let it refresh from its source, like a dynamic table. - Queries must be quick. The interactive warehouse caps each statement at 5 seconds. A query that runs longer is re-run on a standard fallback warehouse you configure, so it still answers, just not at interactive speed.
The table alone doesn't make anything fast: it needs an interactive warehouse to serve it, with the table attached so its cache is warmed for it.
CREATE INTERACTIVE WAREHOUSE serve_wh WAREHOUSE_SIZE = 'XSMALL' AUTO_SUSPEND = 86400 -- the minimum: 24 hours AUTO_RESUME = TRUE; ALTER WAREHOUSE serve_wh ADD TABLES (customer_ ALTER WAREHOUSE serve_wh SET FALLBACK_lookup); WAREHOUSE = bi_wh; -- for queries over 5 s
Point the dashboard's connection at serve_wh, and keep everything else on standard warehouses.
Picking the right one
The cost logic of the whole chain in one line: materialized views refresh with serverless credits, dynamic tables refresh on a warehouse you size, interactive tables bill for being ready all day. Each step trades refresh economics for read latency, so pick per table, not by fashion.
Building them the dbt way, not by hand
You met dbt materializations back in part 2. One of these Snowflake objects is just one more entry on that same list: you don't hand-write the DDL, you set a config and dbt builds and maintains the object.
-- models/marts/ {{ config( materialized=clean_ sales. sql, a dynamic table, the dbt way 'dynamic_ snowflake_table', warehouse= target_'transform_ wh', lag= ) }} select ... -- your joins and cleaning here'1 hour'
That's the one dbt gives you on Snowflake. dbt-snowflake has no materialized_view materialization: if you want a Snowflake materialized view, create it in a post_hook or outside dbt, or ask whether a dynamic table does the job. Interactive tables have no built-in materialization either: create them in Snowflake, downstream of what dbt builds.
Use case: retiring the hourly Airflow job
IsmaFrost's ops team wanted order status no more than 15 minutes stale. The first version was an Airflow DAG rebuilding the whole clean_orders table every hour: 12 minutes of Medium warehouse per run, 24 runs a day, and the data was still up to an hour old between runs. Rewritten as a dynamic table with TARGET_LAG = '15 minutes', Snowflake refreshes incrementally, touching only the orders that changed. Be honest about the bill, though: meeting a 15-minute lag means around 96 refreshes a day, and each wakes the warehouse for at least its 60-second minimum. On a dedicated XS with a 60-second auto-suspend, the minimum per refresh plus the idle tail before each suspend comes to roughly 2 to 4 credits a day, against about 19 for the hourly Medium rebuild (24 × 12 minutes × 4 credits an hour). Fresher data, a fifth to a tenth of the credits, and one less DAG for Thomas, the guide's Airflow engineer, to babysit. The rule it illustrates: when the transform lives entirely inside Snowflake and the requirement is a freshness target, declare the lag instead of scheduling the rebuild.

