Course contents

The Analytics Engineer’s Field GuideSnowflake: the engineStep 7: Pre-baked tables

Pre-baked tables

Part 4 · Snowflake · step 7

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_mv AS
SELECT region, count(*) AS orders, sum(amount) AS revenue
FROM raw_sales
GROUP BY region;   -- one word, MATERIALIZED, changes everything behind it

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_view_refresh_history). 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
Heavy, repeated aggregationsThe same SUM/COUNT hit hundreds of times a day.
Slowly-changing base tablesLoaded nightly or hourly, so maintenance runs rarely.
Avoid it
Rapidly-changing tablesStreaming or constant CDC burns serverless credits non-stop.
Anything with a JOINA materialized view is single-table only. A join errors.

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_dt
  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.customer_name, s.amount
FROM raw_sales s
JOIN customer_dim c ON s.customer_id = c.customer_id
WHERE s.status = 'VALID';

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
Joins and cleaningCombine tables, filter bad rows, standardise formats.
Multi-step chainsraw → cleaned → aggregated, refreshed in order.
Avoid it
A simple single-table SUMA materialized view is usually cheaper for that, on Enterprise.
Hundreds of concurrent lookupsIt's a table, any warehouse can read it, but it gets no serving speed-up. Put an interactive table in front.

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
High-concurrency dashboardsHundreds of users filtering at the same instant.
Data-powered apps & AI agentsAnything that expects answers in milliseconds.
Avoid it
Heavy historical analyticsFive-year scans belong on a standard warehouse.
Constant updates and deletesWrong engine for transactional churn.
-- created on a standard warehouse; CLUSTER BY is mandatory
CREATE INTERACTIVE TABLE customer_lookup
  CLUSTER BY (sales_rep_id)          -- the column the dashboards filter on
  TARGET_LAG = '5 minutes'           -- optional: keep it fresh from the source
  WAREHOUSE  = transform_wh
AS SELECT * FROM analytics.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. UPDATE and DELETE aren't allowed. You either reload it with INSERT OVERWRITE, or give it a TARGET_LAG and 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_lookup);
ALTER WAREHOUSE serve_wh SET FALLBACK_WAREHOUSE = bi_wh;  -- for queries over 5 s

Point the dashboard's connection at serve_wh, and keep everything else on standard warehouses.

The cost model flips
A standard warehouse earns its keep by suspending after a minute. An interactive warehouse can't: its auto-suspend can't go below 24 hours, each resume bills at least an hour, and a resume empties the cache it relies on, so it's effectively on all day, every day. Size it for the serving load, keep analytics off it, and don't stand one up for a dashboard three people open on Mondays.

Picking the right one

object
reach for it when
stays fresh by
single table?
materialized view
one heavy aggregation, read a lot
serverless background
yes only
dynamic table
joins, cleaning, multi-step pipelines
incremental, to a TARGET_LAG
joins ok
interactive table
serving many users sub-second
TARGET_LAG refresh, or INSERT OVERWRITE
serves both

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/clean_sales.sql, a dynamic table, the dbt way
{{ config(
    materialized='dynamic_table',
    snowflake_warehouse='transform_wh',
    target_lag='1 hour'
) }}
select ...   -- your joins and cleaning here

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.

The mental model
A plain view recomputes on read. These three pre-compute and self-maintain so the read is cheap. Dynamic tables replace the orchestration you'd otherwise build in Airflow for in-warehouse transforms, materialized views replace a hand-tuned aggregate refresh, and interactive tables replace shipping data out to a separate serving database. Pick the layer that matches the job, and let Snowflake keep it fresh.

Lesson quiz

  1. You must join raw_sales to customer_dim, clean it, and keep it at most an hour stale, with no Airflow DAG. Which object?

    1. An interactive table clustered on customer_id
    2. A dynamic table with TARGET_LAG = '1 hour'
    3. A secure view defined on top of the join
    4. A materialized view kept in sync serverless
  2. Fifty managers filter the same dashboard their own way at 9am, and it crawls. Which object is built for that?

    1. A materialized view of the dashboard's main GROUP BY
    2. A standard view, relying on the result cache
    3. An interactive table, served by an interactive warehouse
    4. A dynamic table with a one-minute TARGET_LAG on the join
  3. Ismail creates a dynamic table with default settings over a query that uses a window function Snowflake can't refresh incrementally. What happens, and how would he have caught it at creation?

    1. Full rebuilds; REFRESH_MODE = INCREMENTAL would fail
    2. It refreshes incrementally and skips the window rows
    3. It refreshes once, then suspends until it's fixed
    4. Creation fails, since AUTO rejects such queries