Snowflake Dynamic Tables: Declarative Incremental Pipelines vs Materialized Views

snowflake dynamic tables changed the shape of every Snowflake pipeline conversation in 2024-2026 — the moment you can declare a SELECT plus a TARGET_LAG and let the platform handle refresh, the imperative Streams+Tasks DAG that every data team grudgingly maintained for five years suddenly looks like assembly code. Dynamic Tables are the first declarative pipeline primitive Snowflake shipped that actually competes with dbt's incremental model — but they run inside the warehouse, with no separate orchestrator and no separate state store. The DDL is a one-liner; the production surface is anything but.

This guide is the senior-DE comparison you wished existed the first time an interviewer asked "when do dynamic tables beat snowflake materialized views, and when does the MV still win?" or "what stops a DT from doing an incremental refresh and forces it into full refresh?" It walks through the four axes that decide every DT design — lag, refresh mode, supported SQL, cost — covers the materialized view vs dynamic table decision lattice, unpacks the target lag and snowflake refresh mechanics that most engineers get subtly wrong on a DT chain, and ends with the production runbook senior interviewers expect when you mention DTs on a resume. Each section pairs a teaching block with a Solution-Tail interview answer — code, a step-by-step trace, an output table, then a concept-by-concept breakdown of why it works.

When you want hands-on reps immediately after reading, drill the SQL practice library →, rehearse on join problems → for the incremental-join surface, and sharpen the warehouse-design axis with the ETL practice drills →.

---

On this page

• Why Dynamic Tables landed in 2023-2026
• Dynamic Tables architecture
• Materialized Views vs Dynamic Tables
• Incremental refresh supported SQL surface
• Production patterns and senior interview signals
• Cheat sheet — Dynamic Tables recipes
• Frequently asked questions
• Practice on PipeCode

---

1. Why Dynamic Tables landed in 2023-2026

snowflake dynamic tables are the declarative answer to imperative Streams+Tasks DAGs — you declare a SELECT and a lag, Snowflake handles the refresh

The one-sentence invariant: a Dynamic Table is a SELECT statement plus a TARGET_LAG plus a warehouse, and the Snowflake scheduler decides when to run the refresh so the output is never staler than the lag promise. Every other consequence — refresh mode, supported SQL, cost attribution, dependency-graph behaviour — falls out of that single declarative contract. Once you internalise "declare the lag, the platform owns the schedule," the entire dynamic tables interview surface collapses to a sequence of consequences from that one architectural choice.

Three axes interviewers actually probe.

• Lag. TARGET_LAG = '5 minutes' is a freshness ceiling, not a guarantee. The scheduler picks refresh start times so that the output, at any query moment, is no older than the lag. If the underlying refresh takes 90 seconds, the scheduler aims for refresh start every ~3.5 minutes; if it takes 6 minutes, you've broken your own SLA and Snowflake will warn you in DYNAMIC_TABLE_REFRESH_HISTORY. Senior engineers think in terms of lag budget, not refresh cadence.
• Refresh mode. INCREMENTAL only re-processes the rows that changed since the last refresh; FULL re-runs the SELECT against the entire source every time. AUTO picks incremental when the SQL is in the allowed surface and falls back to full when it isn't. The single biggest production failure mode is a DT that silently flipped to FULL after someone added a window function, ballooning the warehouse bill 50x.
• Cost. DT refresh consumes warehouse credits like any other query. The credits land on whatever warehouse the DT was created with. There is no "serverless" mode (yet) — you pay for compute on a real warehouse, and if your refresh runs every 60 seconds across 30 DTs, the warehouse never auto-suspends. Pricing a DT chain is a real exercise that interviewers expect you to think about.

The declarative shift — same word, different mental model.

• Streams+Tasks is imperative. You write a STREAM over a source, a TASK that runs every N minutes, and a MERGE body inside the task. You own the change-detection, the windowing, the deduplication, and the retry. You also own the entire DAG — task dependencies, scheduling, failure handling, the lot.
• Dynamic Tables are declarative. You write the SELECT you'd want against a fresh source. Snowflake computes the change set, runs the merge, manages the schedule, and propagates downstream. You own the SELECT and the lag. The platform owns everything else.
• The mental shift mirrors dbt incremental vs raw SQL macros — except DTs ship inside the warehouse, with no orchestrator and no state catalog.

The 2026 reality — what changed since 2023 GA.

• GA in April 2023 — the first release shipped INCREMENTAL refresh for a limited SQL surface and FULL refresh for everything else. Cross-account share was unsupported.
• 2024 maturity — the allowed-SQL surface grew to cover most equi-joins, LEFT joins, GROUP BY, and the bulk of common aggregations. Refresh history became queryable through INFORMATION_SCHEMA.DYNAMIC_TABLE_REFRESH_HISTORY.
• 2025 cross-account share + downstream refresh — you can now CREATE SHARE a Dynamic Table and have it refresh on the consumer side; the upstream account is the source of truth for the SELECT, the downstream pays for the warehouse credits to refresh.
• AUTO mode is the default in 2026 — Snowflake's docs nudge every new DT toward REFRESH_MODE = 'AUTO' and only recommend explicit INCREMENTAL when you want a hard error instead of a silent fallback.

What interviewers listen for.

• Do you say "declare the SELECT, declare the lag, the scheduler handles refresh" in the first sentence? — senior signal.
• Do you describe lag as "a freshness ceiling, not a cadence"? — required answer.
• Do you mention "AUTO mode silently falls back to FULL when the SQL is out of the incremental surface" unprompted? — senior signal.
• Do you push back on "DTs replace Materialized Views" with "they overlap on aggregate views, but MVs win on single-table pre-aggregations because the maintenance is continuous and cheap"? — senior signal.

Worked example — five-line DT vs a Streams+Tasks DAG for the same aggregate

Detailed explanation. The classic Snowflake pipeline — keep a daily_order_totals table fresh within ~5 minutes of every new order in orders — has two canonical implementations. The Streams+Tasks version needs a stream, a task, a MERGE, and a schedule. The Dynamic Table version is one DDL. Both produce the same row, and the freshness contract is the same, but the surface area you own as the engineer is wildly different.

Question. Write the two implementations side by side for a daily_order_totals table that stays fresh within 5 minutes of any change to orders. Highlight the lines of code you own in each version, where the change-detection lives, and where the schedule is encoded.

Input.

order_id	order_ts	customer_id	amount
1	2026-06-22 09:05:00	c1	50
2	2026-06-22 09:30:00	c2	30
3	2026-06-22 10:15:00	c1	75
4	2026-06-22 11:10:00	c3	20

Code.

-- Streams + Tasks (imperative, ~25 lines you own)
CREATE OR REPLACE STREAM orders_stream ON TABLE orders
  SHOW_INITIAL_ROWS = TRUE;

CREATE OR REPLACE TASK refresh_daily_order_totals
  WAREHOUSE = etl_wh
  SCHEDULE  = '5 MINUTE'
AS
MERGE INTO daily_order_totals tgt
USING (
    SELECT DATE_TRUNC('day', order_ts) AS order_date,
           SUM(amount)                  AS total
    FROM orders_stream
    GROUP BY 1
) src
  ON tgt.order_date = src.order_date
WHEN MATCHED     THEN UPDATE SET total = tgt.total + src.total
WHEN NOT MATCHED THEN INSERT (order_date, total) VALUES (src.order_date, src.total);

ALTER TASK refresh_daily_order_totals RESUME;

-- Dynamic Table (declarative, 5 lines you own)
CREATE OR REPLACE DYNAMIC TABLE daily_order_totals
  TARGET_LAG  = '5 minutes'
  WAREHOUSE   = etl_wh
AS
SELECT DATE_TRUNC('day', order_ts) AS order_date,
       SUM(amount)                  AS total
FROM orders
GROUP BY 1;

Step-by-step explanation.

1. The Streams+Tasks version creates a STREAM over orders that tracks insert/update/delete deltas since last consumption. You own the stream's life-cycle (SHOW_INITIAL_ROWS, advancing the offset).
2. The TASK runs every 5 minutes; you wrote the cron-like SCHEDULE = '5 MINUTE'. You also wrote the MERGE body that knows the stream is incremental and that the target needs an additive update. Get the MERGE clause wrong (e.g., UPDATE SET total = src.total instead of tgt.total + src.total) and the aggregate silently drifts.
3. The Dynamic Table version writes the SELECT against the source — not against the change set — because the scheduler computes the change set for you. You don't see a stream object; you don't write a MERGE; you don't manage retries.
4. The TARGET_LAG = '5 minutes' says "queries against daily_order_totals should never see data older than 5 minutes ago." Snowflake decides whether that means refreshing every 90 seconds or every 4 minutes 30 seconds based on actual refresh duration.
5. Both pipelines produce the same row set in daily_order_totals. The DT version is five lines and one DDL; the Streams+Tasks version is roughly 25 lines and three objects (stream, task, target). The first time someone wants to change the SELECT (add a customer dimension), the DT version is a single CREATE OR REPLACE DYNAMIC TABLE; the imperative version becomes a multi-step migration.

Output.

order_date	total
2026-06-22	175

Rule of thumb. If the SELECT lives entirely on the incremental surface (filters, projections, equi-joins, GROUP BY, aggregates), reach for a Dynamic Table first. The Streams+Tasks pattern only earns its complexity when you need imperative logic — calling stored procs, hitting an external function, doing row-level branching — that a SELECT cannot express.

Worked example — the freshness contract is a ceiling, not a cadence

Detailed explanation. A frequent misconception: junior engineers read TARGET_LAG = '5 minutes' and assume the table refreshes every 5 minutes. It doesn't. The lag is a freshness ceiling: at any moment a query runs, the data must be no older than 5 minutes since the most recent committed source change. The scheduler chooses refresh times to keep that promise. The math is subtle and interviewers love to probe it.

Question. A DT with TARGET_LAG = '5 minutes' is built on a source that receives a new commit every minute, and each refresh takes ~30 seconds to run. How often does the scheduler refresh, and what would change if the refresh suddenly slowed to 4 minutes per run?

Input.

Parameter	Value
TARGET_LAG	5 minutes
Source commit frequency	1 / minute
Refresh duration (case A)	30 seconds
Refresh duration (case B)	4 minutes

Code.

-- Inspect refresh cadence + duration
SELECT name,
       refresh_action,
       refresh_start_time,
       refresh_end_time,
       DATEDIFF('second', refresh_start_time, refresh_end_time) AS refresh_sec,
       LAG(refresh_start_time) OVER (PARTITION BY name ORDER BY refresh_start_time) AS prev_start,
       DATEDIFF('second',
                LAG(refresh_start_time) OVER (PARTITION BY name ORDER BY refresh_start_time),
                refresh_start_time) AS gap_sec
FROM   TABLE(INFORMATION_SCHEMA.DYNAMIC_TABLE_REFRESH_HISTORY())
WHERE  name = 'DAILY_ORDER_TOTALS'
ORDER  BY refresh_start_time DESC
LIMIT  20;

Step-by-step explanation.

1. Snowflake's scheduler treats TARGET_LAG as a max-age contract. To keep data fresh under 5 minutes, it picks a refresh start cadence so that (refresh_start_gap + refresh_duration + scheduler_buffer) <= TARGET_LAG.
2. Case A — 30s refresh, 1/min commits. The scheduler aims for roughly 4-minute gaps between refresh starts (4 min gap + 30s run + scheduler slack ≈ 5 min). Cadence ≈ once every ~4 minutes.
3. Case B — 4-minute refresh, 1/min commits. The scheduler has almost no headroom: 4 min run alone consumes most of the lag budget. It will start the next refresh roughly back-to-back, which keeps the warehouse pegged 100% of the time and still risks lag breach.
4. When the refresh duration exceeds the TARGET_LAG, Snowflake flags the DT as out of compliance in DYNAMIC_TABLE_REFRESH_HISTORY (SCHEDULING_STATE column) and the lag actually realised on the table climbs above the target. You'll see this as an UPSTREAM_NOT_FRESH or EXCEEDED_LAG annotation.
5. The cure for case B is not "lower the TARGET_LAG"; it's "speed up the refresh" — bigger warehouse, narrower SELECT, tighter clustering on the source — or "accept a higher TARGET_LAG that matches reality."

Output.

Case	refresh_duration	TARGET_LAG	scheduler_gap (approx)	actual lag
A	30 s	5 min	~4 min	≈ 4 min 30 s
B	4 min	5 min	back-to-back	5 min+ (breach)

Rule of thumb. A safe TARGET_LAG is roughly 3x your typical refresh duration. If the refresh takes 60 seconds, a 3-minute lag is comfortable. Going tighter than 2x is asking the scheduler to live with no slack.

Worked example — when "AUTO refresh mode" hides a silent regression

Detailed explanation. The most common DT production bug in 2026 is the silent flip from incremental to full. A developer adds a QUALIFY clause or a window function to a DT's SELECT. The DDL compiles. Tests pass. Two weeks later the bill is up 30x because the DT, configured with REFRESH_MODE = 'AUTO', fell back to full refresh and is now re-aggregating a billion rows every 5 minutes.

Question. Show two versions of a DT's SELECT — one that stays on incremental, one that falls back to full — and the single query that catches the regression.

Input.

Metric	Incremental	Full fallback
Refresh credit / run	0.01	8.2
Refresh frequency	every 4 min	every 4 min
Daily credit burn	~3.6	~2,950

Code.

-- Version A — incremental-safe SELECT
CREATE OR REPLACE DYNAMIC TABLE customer_recent_totals
  TARGET_LAG = '5 minutes'
  WAREHOUSE  = etl_wh
  REFRESH_MODE = 'AUTO'
AS
SELECT customer_id, SUM(amount) AS total_amount
FROM   orders
WHERE  order_ts >= DATEADD('day', -7, CURRENT_DATE)
GROUP  BY 1;

-- Version B — adds a window function, silently falls back to FULL
CREATE OR REPLACE DYNAMIC TABLE customer_recent_totals
  TARGET_LAG = '5 minutes'
  WAREHOUSE  = etl_wh
  REFRESH_MODE = 'AUTO'
AS
SELECT customer_id,
       SUM(amount)                                                AS total_amount,
       RANK() OVER (ORDER BY SUM(amount) DESC)                    AS amount_rank
FROM   orders
WHERE  order_ts >= DATEADD('day', -7, CURRENT_DATE)
GROUP  BY 1;

-- Catch the regression
SELECT name, refresh_mode, refresh_action, refresh_trigger
FROM   TABLE(INFORMATION_SCHEMA.DYNAMIC_TABLE_REFRESH_HISTORY())
WHERE  refresh_action = 'REINITIALIZE'   -- the smoking gun
   OR  refresh_mode   = 'FULL'
ORDER  BY refresh_start_time DESC;

Step-by-step explanation.

1. Version A's SELECT is a textbook incremental shape — a WHERE filter, a GROUP BY, and a SUM. Each refresh re-aggregates only the rows that changed since the last refresh, which on a high-throughput source is a small fraction of the data.
2. Version B adds RANK() OVER (...) — a window function. Window functions are not on the incremental surface in 2026; the AUTO mode quietly downgrades the DT to FULL refresh.
3. The DDL succeeds. The DT continues to be queryable. The output is correct. The only visible symptom is the credit bill. By the time anyone notices, you've burned thousands of credits.
4. The catch query inspects DYNAMIC_TABLE_REFRESH_HISTORY. refresh_mode = 'FULL' on a DT you expected to be incremental is the smoking gun; refresh_action = 'REINITIALIZE' also flags the same condition (the DT had to rebuild from scratch).
5. The fix is to either (a) move the windowing to a downstream view or DT that reads the incremental aggregate, or (b) configure the DT with REFRESH_MODE = 'INCREMENTAL' so a non-incremental SELECT errors at create time instead of degrading silently.

Output.

Version	refresh_mode realised	credit burn / day
A (filter + GROUP BY)	INCREMENTAL	~3.6
B (filter + GROUP BY + RANK)	FULL	~2,950

Rule of thumb. Use REFRESH_MODE = 'INCREMENTAL' (not AUTO) on every DT in critical paths. A loud failure at create time is better than a quiet credit-fire in production. Use AUTO only on prototypes where convenience matters more than cost.

Senior interview question on Dynamic Tables choice

A senior interviewer often opens with: "You're rebuilding a Streams+Tasks pipeline that maintains a customer-360 aggregate. Walk me through how you decide whether to convert it to Dynamic Tables, what the lag SLA looks like, and what you'd watch in production for the first month."

Solution Using a 4-question DT-readiness framework

Decision framework — convert Streams+Tasks to a Dynamic Table?

1. Can the entire MERGE body be written as a single SELECT against the source?
   - Yes  → DT eligible
   - No (imperative branching, stored proc calls, external functions) → keep Streams+Tasks

2. Does the SELECT stay on the incremental surface?
   - Yes (filters, projections, equi-joins, LEFT joins, GROUP BY, aggregates) → AUTO/INCREMENTAL ok
   - No (window functions, certain UDFs, OUTER joins on non-key columns) → expect FULL fallback

3. What's the freshness SLA?
   - <= 1 minute      → DT is tight; size the warehouse for the typical refresh duration
   - 1-15 minutes     → DT sweet spot
   - > 15 minutes     → DT works fine; consider DOWNSTREAM lag mode

4. What's the cost target?
   - Forecast credits = (refresh_duration_seconds / 3600) * credits_per_hour * refreshes_per_day
   - If forecast > 1.5x the Streams+Tasks bill, look at warehouse sizing or REFRESH_MODE.

Step-by-step trace.

Pipeline	Q1 SELECT-able?	Q2 surface	Q3 SLA	Q4 cost	Picked
Customer-360 aggregate (filter+GROUP BY+JOIN)	yes	incremental	5 min	1.1x baseline	Dynamic Table
Risk-scoring stored proc	no	n/a	1 min	n/a	Streams+Tasks
Daily snapshot with QUALIFY top-100	yes	full-only	30 min	2.3x baseline	DT with INCREMENTAL to fail loud, or stay on Streams+Tasks
Real-time clickstream agg (sub-second)	yes	incremental	< 30 sec	DT can't hit	Streams+Tasks (or move to Snowpipe Streaming)

After the 4-question pass, the DT choice is usually unambiguous. The remaining 5% — where DT and Streams+Tasks both work — defaults to whatever the team prefers to maintain, and DT wins on lines of code every time.

Output:

Approach	When it wins
Dynamic Tables	SELECT-expressible logic, incremental surface, 1-15 min lag, single source of truth for the SELECT
Streams+Tasks	Imperative logic in the MERGE body, sub-second lag, calls into stored procs / external functions, fine-grained retry control
dbt incremental	Already on dbt; want CI/CD, version control, Jinja macros, and the DAG to live outside the warehouse

Why this works — concept by concept:

• Declarative vs imperative — the structural axis — every other consequence (refresh mode, surface, scheduling, dependency graph) follows from whether you own the what or the how. Asking "can this be one SELECT?" first short-circuits a lot of false choices.
• Incremental surface is a hard contract — the AUTO fallback is a feature, not a bug, but it hides cost. The senior engineer always pins REFRESH_MODE = 'INCREMENTAL' in critical paths so violations error loudly.
• Lag is a ceiling — TARGET_LAG is the freshness contract, not a cadence. Plan the warehouse size around refresh duration, then size the lag at ~3x duration.
• Cost lives in refresh frequency × refresh size — incremental keeps refresh size small; AUTO mode can silently invert that ratio. Catch it with DYNAMIC_TABLE_REFRESH_HISTORY from day one.
• Cost — DT refresh is O(delta) on incremental and O(source) on full; a wrong refresh mode flips the cost class. Plan for this in the design review, not after the bill arrives.

SQL
Topic — sql
Snowflake SQL practice library

Practice →

ETL Topic — etl ETL pipeline design problems

Practice →

---

2. Dynamic Tables architecture

A Dynamic Table is a SELECT + TARGET_LAG + warehouse — and the Snowflake scheduler is the only moving part you don't own

The mental model in one line: a Dynamic Table is a managed materialised result of a SELECT statement whose freshness is bounded by a TARGET_LAG, refreshed by a warehouse you nominate, on a schedule the Snowflake scheduler picks. Once you say that out loud, every snowflake refresh interview question becomes a deduction from "the platform owns the schedule, you own the SELECT and the freshness contract."

The four DDL knobs that decide everything.

• AS SELECT ... — the body of the DT. Everything the consumer can query is computed from this SELECT. It is the only place where your business logic lives.
• TARGET_LAG — the freshness ceiling. Accepts a time interval ('1 minute', '30 seconds', '1 hour') or the special value DOWNSTREAM (refresh only when a downstream DT or query demands fresh data).
• WAREHOUSE — the compute the scheduler uses to run the refresh. Sized normally; auto-suspend / auto-resume behaves the same as any other workload on that warehouse.
• REFRESH_MODE — INCREMENTAL (error if the SQL isn't incremental-safe), FULL (always rebuild from source), or AUTO (try incremental, fall back to full). Default is AUTO.

The scheduler — the moving part you don't own.

• Refresh start times — chosen by the Snowflake scheduler so that the realised lag stays under TARGET_LAG. Refreshes do not run on a fixed cron.
• Concurrent refresh budget — a DT will not run two refreshes concurrently. If the previous refresh is still running when the next one is due, the scheduler waits.
• Refresh history — every start, end, action (INCREMENTAL, REINITIALIZE, NO_DATA), and outcome is logged in INFORMATION_SCHEMA.DYNAMIC_TABLE_REFRESH_HISTORY. This view is the senior engineer's truth source for everything.
• Manual override — ALTER DYNAMIC TABLE x REFRESH forces an immediate refresh. Useful for debugging; never in steady-state production.

Incremental vs full vs auto — the lifecycle.

• Incremental — the scheduler computes the change set on every input (new + updated + deleted rows since the last refresh) and re-aggregates only what changed. Cost is O(delta).
• Full — the scheduler ignores the change set and re-runs the SELECT against the full source. Cost is O(source). The DT is functionally equivalent to a scheduled CREATE OR REPLACE TABLE x AS SELECT ....
• Auto — Snowflake plans the refresh. If the SELECT plan supports incremental change tracking, it runs incremental; if not, it runs full. The first refresh is always full (it has to build the initial state).

The dependency graph — DT-on-DT-on-DT.

• A DT can read another DT. The scheduler treats the downstream DT's refresh as needing the upstream DT's data to be fresh first.
• Downstream lag is additive in the worst case. A chain of three DTs, each with TARGET_LAG = '1 minute', can present an end-to-end freshness ceiling of ~3 minutes because the downstream refresh has to wait for the upstream.
• The DOWNSTREAM lag mode flips this: an upstream DT marked TARGET_LAG = DOWNSTREAM only refreshes when a downstream DT (or a directly-querying user, if configured) needs fresh data. Useful for keeping mid-graph DTs lazy.

Cost model — DT refresh credits.

• DT refresh consumes warehouse credits at the warehouse's compute rate. There is no special "DT pricing tier".
• The amount of compute used is visible in DYNAMIC_TABLE_REFRESH_HISTORY's STATISTICS column and the per-query QUERY_HISTORY view.
• High-frequency DTs (sub-minute lag) typically need a dedicated warehouse to avoid noisy-neighbour effects from interactive queries — and to make cost attribution easier.

Common interview probes on architecture.

• "What does TARGET_LAG actually guarantee?" — the realised lag (now minus the most-recent committed source change) stays under the value.
• "What happens when refresh duration exceeds TARGET_LAG?" — Snowflake flags it in refresh history; lag is breached until the next refresh catches up.
• "What is the difference between INCREMENTAL, FULL, and AUTO?" — strict mode, full rebuild, planner picks. AUTO defaults to incremental when supported.
• "How does DT-on-DT propagation work?" — downstream refresh waits for upstream freshness; lag composes through the chain.

Worked example — minimal DT DDL and first refresh

Detailed explanation. The simplest possible DT — a freshness-tracked count of orders per customer — exercises every architectural knob. Walking through the lifecycle from CREATE to the first refresh teaches you what the scheduler is actually doing, and what shows up in DYNAMIC_TABLE_REFRESH_HISTORY.

Question. Create a DT customer_order_count with TARGET_LAG = '1 minute', then describe the lifecycle of the first refresh and the state visible in refresh history.

Input — orders.

order_id	customer_id	order_ts
1	c1	2026-06-22 09:00:00
2	c2	2026-06-22 09:01:00
3	c1	2026-06-22 09:02:00

Code.

CREATE OR REPLACE DYNAMIC TABLE customer_order_count
  TARGET_LAG = '1 minute'
  WAREHOUSE  = etl_wh
  REFRESH_MODE = 'INCREMENTAL'
AS
SELECT customer_id,
       COUNT(*) AS order_count
FROM   orders
GROUP  BY customer_id;

-- Inspect lifecycle
SELECT name,
       refresh_action,
       refresh_trigger,
       refresh_start_time,
       refresh_end_time,
       statistics
FROM   TABLE(INFORMATION_SCHEMA.DYNAMIC_TABLE_REFRESH_HISTORY())
WHERE  name = 'CUSTOMER_ORDER_COUNT'
ORDER  BY refresh_start_time;

Step-by-step explanation.

1. CREATE OR REPLACE DYNAMIC TABLE registers the DT in Snowflake's metadata. No data is materialised yet. The DT is queryable as soon as the initial refresh completes.
2. The first refresh action is always REINITIALIZE regardless of REFRESH_MODE — Snowflake has to build the initial state from the entire source. The action column will show REINITIALIZE and refresh_trigger = 'CREATION'.
3. Once the initial refresh finishes, subsequent refreshes (action = INCREMENTAL, trigger = SCHEDULED) re-aggregate only the rows changed since the last refresh.
4. The statistics column is a JSON blob with numInsertedRows, numUpdatedRows, numDeletedRows. On the first refresh the inserted count equals the source row count; on subsequent refreshes the numbers are tiny.
5. If no rows changed between two refresh intervals, the scheduler logs a NO_DATA action — the DT didn't run any compute. This is the cost-efficient steady state for low-change sources.

Output (refresh_history projection).

name	refresh_action	refresh_trigger	inserted	updated	deleted
CUSTOMER_ORDER_COUNT	REINITIALIZE	CREATION	2	0	0
CUSTOMER_ORDER_COUNT	INCREMENTAL	SCHEDULED	0	1	0
CUSTOMER_ORDER_COUNT	NO_DATA	SCHEDULED	0	0	0

Rule of thumb. Always check DYNAMIC_TABLE_REFRESH_HISTORY right after creating a DT in a new environment. The first INCREMENTAL row confirms that AUTO didn't silently fall back to FULL; the first NO_DATA row confirms the scheduler is honouring change-set emptiness instead of refreshing on a fixed cron.

Worked example — DT-on-DT lag propagation

Detailed explanation. A common production design — orders_clean is a Dynamic Table that filters and normalises orders_raw; customer_totals is a Dynamic Table that aggregates orders_clean. Both have TARGET_LAG = '1 minute'. What does a query against customer_totals actually see, freshness-wise?

Question. Build a two-step DT chain (orders_raw → orders_clean → customer_totals), each with TARGET_LAG = '1 minute', and describe the realised freshness of customer_totals at query time.

Input.

Object	TARGET_LAG	Typical refresh duration
orders_clean	1 min	20 s
customer_totals	1 min	15 s

Code.

CREATE OR REPLACE DYNAMIC TABLE orders_clean
  TARGET_LAG = '1 minute'
  WAREHOUSE  = etl_wh
AS
SELECT order_id,
       customer_id,
       order_ts,
       UPPER(currency) AS currency,
       amount
FROM   orders_raw
WHERE  status = 'COMPLETED';

CREATE OR REPLACE DYNAMIC TABLE customer_totals
  TARGET_LAG = '1 minute'
  WAREHOUSE  = etl_wh
AS
SELECT customer_id,
       SUM(amount) AS total
FROM   orders_clean
GROUP  BY customer_id;

-- Inspect chain dependencies
SELECT name, target_lag, refresh_mode, scheduling_state
FROM   INFORMATION_SCHEMA.DYNAMIC_TABLES
WHERE  name IN ('ORDERS_CLEAN', 'CUSTOMER_TOTALS');

Step-by-step explanation.

1. Snowflake treats customer_totals as having a dependency on orders_clean. When the scheduler plans a refresh for customer_totals, it first ensures orders_clean is fresh enough.
2. The realised freshness ceiling for customer_totals at query time is bounded by the upstream's lag plus the downstream's lag. With each at 1 minute, the worst-case end-to-end staleness is roughly 2 minutes.
3. If orders_clean is mid-refresh when customer_totals becomes due, the downstream refresh waits — increasing the gap between refresh starts and consuming the lag budget.
4. The DOWNSTREAM lag mode flips the model: setting orders_clean to TARGET_LAG = DOWNSTREAM means orders_clean only refreshes when customer_totals needs fresh data. Useful when you want a single freshness contract at the leaf of the graph rather than at every node.
5. The SCHEDULING_STATE column in INFORMATION_SCHEMA.DYNAMIC_TABLES reveals upstream / downstream relationships and any current scheduling problems (UPSTREAM_NOT_FRESH, EXCEEDED_LAG).

Output (worst-case freshness at query time).

Query target	TARGET_LAG (declared)	Realised ceiling (chain)
orders_clean	1 min	1 min
customer_totals	1 min	~2 min (upstream + own)

Rule of thumb. Sum the upstream TARGET_LAGs to get the worst-case realised lag at the leaf. If the leaf SLA is "data within 5 minutes," budget each upstream DT at no more than 5min / depth — or move the entire upper graph to TARGET_LAG = DOWNSTREAM.

Worked example — switching between INCREMENTAL, FULL, and AUTO

Detailed explanation. A DT's REFRESH_MODE can change after creation only by CREATE OR REPLACE, but the realised refresh action (in the history view) can vary refresh-by-refresh when the mode is AUTO. Understanding what each mode does on a SELECT that is on the borderline of the incremental surface is the difference between a predictable bill and a 30x surprise.

Question. Take a DT whose SELECT includes a GROUP BY (incremental-safe) and a QUALIFY ROW_NUMBER() OVER ... (not incremental). Show what each REFRESH_MODE does at create time and over the next refresh.

Input.

REFRESH_MODE	At CREATE	First refresh	Subsequent refreshes
INCREMENTAL	error	n/a	n/a
FULL	success	FULL	FULL
AUTO	success	FULL	FULL (falls back)

Code.

-- Will ERROR at create time: SELECT is not incremental-safe
CREATE OR REPLACE DYNAMIC TABLE top_orders_per_customer
  TARGET_LAG = '5 minutes'
  WAREHOUSE  = etl_wh
  REFRESH_MODE = 'INCREMENTAL'
AS
SELECT customer_id, order_id, amount
FROM   orders
QUALIFY ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY amount DESC) <= 3;

-- Will succeed; every refresh is FULL
CREATE OR REPLACE DYNAMIC TABLE top_orders_per_customer
  TARGET_LAG = '5 minutes'
  WAREHOUSE  = etl_wh
  REFRESH_MODE = 'FULL'
AS
SELECT customer_id, order_id, amount
FROM   orders
QUALIFY ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY amount DESC) <= 3;

Step-by-step explanation.

1. With REFRESH_MODE = 'INCREMENTAL', Snowflake plans the SELECT and refuses to create the DT if the plan needs full refresh. The error is loud and prevents the silent fallback bug.
2. With REFRESH_MODE = 'FULL', every refresh re-runs the SELECT against the full source. Cost is predictable but high — useful for small-source DTs where incremental savings would be marginal anyway.
3. With REFRESH_MODE = 'AUTO', the create succeeds. The scheduler picks incremental when the plan supports it, full otherwise. For the QUALIFY ROW_NUMBER SELECT, every refresh is full; the cost is the same as explicit FULL but you don't know unless you look at history.
4. Changing REFRESH_MODE after creation requires CREATE OR REPLACE. Snowflake will rebuild the DT's initial state from scratch as the next refresh action.
5. The cost difference between INCREMENTAL and FULL on a typical aggregate DT is 10-100x. Picking INCREMENTAL mode on critical-path DTs is the cheapest production insurance you can buy.

Output.

REFRESH_MODE	Behaviour on borderline SELECT
INCREMENTAL	DDL errors at create time — visible, blocking, gated
FULL	Always full — high cost, predictable
AUTO	Silent fallback to full — hidden cost, requires monitoring

Rule of thumb. Choose INCREMENTAL for every DT in a critical path, FULL when you know the source is tiny and incremental is wasted effort, and AUTO only on prototypes. Add a periodic alert that watches refresh_mode = 'FULL' in history on any DT you expected to be incremental.

Senior interview question on architecture

A senior interviewer might ask: "Walk me through how you'd design a Dynamic Tables chain for a 'fresh customer-360' table that has to be within 5 minutes of every source change, given six upstream tables. How do you set TARGET_LAG across the chain, how do you size warehouses, and how do you catch lag breaches before alarms fire?"

Solution Using a lag-budget + warehouse-isolation pattern

-- Layer 1: cleaned-up DTs over each raw source (incremental-safe)
CREATE OR REPLACE DYNAMIC TABLE orders_clean
  TARGET_LAG   = '1 minute'
  WAREHOUSE    = dt_wh_clean
  REFRESH_MODE = 'INCREMENTAL'
AS SELECT ... FROM orders_raw WHERE status = 'COMPLETED';

CREATE OR REPLACE DYNAMIC TABLE customers_clean
  TARGET_LAG   = '1 minute'
  WAREHOUSE    = dt_wh_clean
  REFRESH_MODE = 'INCREMENTAL'
AS SELECT ... FROM customers_raw;

-- Layer 2: joined fact aggregates (still incremental-safe equi-join + GROUP BY)
CREATE OR REPLACE DYNAMIC TABLE customer_order_facts
  TARGET_LAG   = '2 minutes'
  WAREHOUSE    = dt_wh_join
  REFRESH_MODE = 'INCREMENTAL'
AS
SELECT c.customer_id,
       c.tier,
       SUM(o.amount)  AS total_amount,
       COUNT(*)        AS order_count
FROM   customers_clean c
JOIN   orders_clean    o USING (customer_id)
GROUP  BY c.customer_id, c.tier;

-- Layer 3: customer-360 leaf with the SLA
CREATE OR REPLACE DYNAMIC TABLE customer_360
  TARGET_LAG   = '5 minutes'
  WAREHOUSE    = dt_wh_leaf
  REFRESH_MODE = 'INCREMENTAL'
AS
SELECT f.*, p.last_session_at
FROM   customer_order_facts f
LEFT JOIN customer_profile_clean p USING (customer_id);

-- Lag-breach alert
CREATE OR REPLACE TASK dt_lag_breach_alert
  WAREHOUSE = monitor_wh
  SCHEDULE  = '5 MINUTE'
AS
INSERT INTO ops.dt_lag_breaches
SELECT current_timestamp, name, scheduling_state, target_lag_sec, mean_lag_sec
FROM   INFORMATION_SCHEMA.DYNAMIC_TABLE_GRAPH_HISTORY()
WHERE  mean_lag_sec > target_lag_sec;

Step-by-step trace.

Layer	DT	TARGET_LAG	Warehouse	Realised ceiling
1	orders_clean	1 min	dt_wh_clean	1 min
1	customers_clean	1 min	dt_wh_clean	1 min
1	customer_profile_clean	1 min	dt_wh_clean	1 min
2	customer_order_facts	2 min	dt_wh_join	~3 min (1+2)
3	customer_360	5 min	dt_wh_leaf	~5 min (1+2+5? no, capped by leaf SLA)

The lag budget is distributed so that the realised ceiling at the leaf stays under 5 minutes even with chain composition. Each layer gets a dedicated warehouse so that a slow refresh on one layer doesn't queue behind another. The breach alert checks DYNAMIC_TABLE_GRAPH_HISTORY every 5 minutes and fires when realised mean lag exceeds the target.

Output:

Metric	Before (Streams+Tasks)	After (DT chain)
Lines of SQL owned	~250	~40
Objects to monitor	18 (streams + tasks + targets)	4 DTs
End-to-end freshness ceiling	6-8 min (cron-driven)	~5 min (declarative)
Alert mechanism	task failure logs	GRAPH_HISTORY lag query

Why this works — concept by concept:

• Lag-budget design — each upstream layer takes a slice of the leaf SLA; you never let a single DT carry the entire freshness contract because chain composition is additive in the worst case.
• Warehouse isolation — separating the three layers onto three warehouses lets cost attribution and queue contention be diagnosed at a glance; cleanup-layer load can't starve the leaf-layer refresh.
• Incremental as a hard contract — REFRESH_MODE = 'INCREMENTAL' everywhere fails loud if anyone slips a window function into the SELECT, replacing a 50x credit fire with a 5-minute pull request rejection.
• Lag-breach alerting via graph history — DYNAMIC_TABLE_GRAPH_HISTORY reports realised vs declared lag per node; that's a real signal, unlike "task completed successfully" which never catches silent staleness.
• Cost — refresh cost is O(delta) on every layer; warehouse credits scale with change volume, not source size. Doubling the source rarely doubles the bill if the change-set ratio stays the same.

SQL
Topic — joins
Join problems for the DT incremental surface

Practice →

SQL Topic — aggregation Aggregation problems (DT GROUP BY surface)

Practice →

---

3. Materialized Views vs Dynamic Tables

snowflake materialized views and Dynamic Tables solve adjacent problems — same word "materialised," wildly different SQL surfaces and cost models

The mental model in one line: a Materialized View is a narrow, continuously-maintained pre-aggregation of a single base table; a Dynamic Table is a wide, lag-driven materialisation of an arbitrary SELECT (joins, windows, multi-table aggregates). Once you internalise "MV is single-table and continuous; DT is multi-table and lag-driven," the entire materialized view vs dynamic table interview surface becomes a deduction from that split.

What Snowflake Materialized Views actually are.

• Single base table. MVs can only SELECT from one table — no joins in the MV body. (This is the rule that drives most "MV vs DT" interview answers.)
• Limited expressions. MVs allow projections, filters, simple aggregates (SUM, COUNT, MIN, MAX, AVG), and GROUP BY. They do not allow DISTINCT, HAVING, most window functions, UNION, subqueries, or user-defined functions.
• Continuous background maintenance. Snowflake updates MVs as the base table is written. There is no "refresh" you schedule; the MV is always close to fresh and the staleness is bounded by Snowflake's internal maintenance cadence.
• Automatic query rewrite. Queries against the base table that match the MV's shape get transparently rewritten to read from the MV. This is the killer feature MVs have that DTs do not.
• Serverless cost model. MV maintenance runs on Snowflake's serverless compute, billed at a per-credit rate independent of any warehouse you own.

What Dynamic Tables actually are (recap, mapped against MV axes).

• Arbitrary SELECT. Joins (equi, left, inner), windows (with caveats around incremental), multi-table aggregates, CTEs, subqueries, UDFs.
• Lag-driven refresh. Refreshes happen when the scheduler decides, bounded by TARGET_LAG. Not continuous.
• No automatic query rewrite. Queries against the source tables are not rewritten to read from the DT — you must explicitly query the DT.
• Standard warehouse cost. Refresh runs on a warehouse you nominate, billed at the warehouse's standard credit rate.

The decision lattice — when each wins.

• MV wins on single-table pre-aggregations with high query volume and frequent base-table writes. The automatic query rewrite is free latency for downstream queries; the continuous maintenance is cheaper than re-running a GROUP BY per dashboard query.
• DT wins on multi-table joins, on aggregates that cross tables, on any SELECT that includes a window function (even if it falls back to full), and on pipelines where you want to materialise the SELECT once and have downstream queries read it directly.
• MV beats DT on cost when the base table is small and continuously updated — MV's serverless cost model amortises better than DT's warehouse credits across a low-volume source.
• DT beats MV on flexibility when the SELECT must do anything more interesting than GROUP BY on a single table.

The 2026 reality — Snowflake's positioning.

• Snowflake's docs are clear: MVs are for single-table query acceleration; DTs are for multi-table declarative pipelines. They were never intended to overlap.
• Most teams that adopted DTs in 2024-2025 use MVs alongside DTs — MV underneath for query-rewrite acceleration on the raw source, DT on top to publish a curated aggregate for downstream consumers.
• The most common mistake: rebuilding an MV-shaped workload as a DT because "DT is the new shiny" — paying warehouse credits to refresh a SELECT that an MV would have continuously maintained for less.

Common interview probes on MV vs DT.

• "Why can't Snowflake MVs have joins?" — by design; the maintenance algorithm requires a single base relation for correctness with continuous propagation.
• "What is automatic query rewrite?" — the optimiser rewrites a query against the base table to read from the MV when the shape matches.
• "Which is cheaper — MV or DT — for a daily aggregate on a 100M-row table updated once a day?" — DT, because MV's continuous maintenance burns more credits than a once-a-day DT refresh.
• "Which is cheaper for an hourly aggregate updated every minute?" — MV, because the continuous maintenance amortises tiny per-row cost across the hour.

Worked example — same daily aggregate as MV vs DT

Detailed explanation. A canonical comparison: a daily_order_revenue aggregate on a single orders table. Both MV and DT can express it. The cost, freshness, and operational surface differ. Walking through both implementations side-by-side teaches the trade-off without hand-waving.

Question. Build a daily_order_revenue aggregate two ways — as a Snowflake Materialized View and as a Dynamic Table — and compare the resulting freshness, cost, and queryability.

Input.

order_id	order_ts	amount
1	2026-06-22 09:05:00	50
2	2026-06-22 09:30:00	30
3	2026-06-22 10:15:00	75

Code.

-- Materialized View — single-table, GROUP BY, query rewrite for free
CREATE OR REPLACE MATERIALIZED VIEW daily_order_revenue_mv
AS
SELECT DATE_TRUNC('day', order_ts) AS order_date,
       SUM(amount)                  AS revenue
FROM   orders
GROUP  BY 1;

-- Dynamic Table — same SELECT, lag-driven refresh
CREATE OR REPLACE DYNAMIC TABLE daily_order_revenue_dt
  TARGET_LAG   = '5 minutes'
  WAREHOUSE    = etl_wh
  REFRESH_MODE = 'INCREMENTAL'
AS
SELECT DATE_TRUNC('day', order_ts) AS order_date,
       SUM(amount)                  AS revenue
FROM   orders
GROUP  BY 1;

Step-by-step explanation.

1. The MV is maintained continuously by Snowflake's serverless layer. As each orders insert/update lands, the MV's aggregate is updated. Queries against orders that look like SELECT DATE_TRUNC('day', order_ts), SUM(amount) FROM orders GROUP BY 1 are automatically rewritten to read from the MV.
2. The DT is maintained on the etl_wh warehouse on the lag-driven cadence. Queries against the DT have to explicitly read from daily_order_revenue_dt. Queries against orders are not rewritten.
3. On freshness, the MV is bounded by Snowflake's internal maintenance cadence (typically seconds); the DT is bounded by TARGET_LAG (5 minutes here).
4. On cost, the MV burns serverless credits scaled to base-table churn; the DT burns warehouse credits at refresh time. For a low-volume source, the MV is cheaper; for a high-volume source where you'd run the refresh once and let many downstream queries read the result, the DT can be cheaper per query served.
5. The MV cannot become a starting point for further DTs because it has no TARGET_LAG semantics; the DT can be referenced from other DTs (DT-on-DT chains) — that's the architectural lever the MV doesn't give you.

Output.

Approach	Freshness	Cost model	Query rewrite
Materialized View	seconds	serverless	yes (automatic)
Dynamic Table	TARGET_LAG	warehouse credits	no (explicit reads)

Rule of thumb. Use an MV when you have a single-table aggregate that drives many downstream queries (dashboards, ad-hoc analytics). Use a DT when you need joins, downstream chaining, or explicit freshness contracts that the consumer can rely on.

Worked example — when MV beats DT on cost

Detailed explanation. A subtle interview probe: "When does the MV beat the DT on cost?" The answer is not "always" — DTs can be dramatically cheaper for batch-style aggregates that refresh once a day. But on continuously-updated single-table aggregates, the MV's serverless maintenance is materially cheaper than DT refresh credits.

Question. A daily_user_clicks aggregate on a 100M-row clicks table that receives 1M new clicks per hour. Compare the MV vs DT cost for keeping the aggregate fresh within 60 seconds.

Input.

Parameter	Value
Base table rows	100M
Hourly insert rate	1M / hour
Desired freshness	60 seconds
MV serverless rate	~equiv to XS for incremental row work
DT refresh duration on M-warehouse	~45 seconds

Code.

-- MV: continuously maintained
CREATE OR REPLACE MATERIALIZED VIEW daily_user_clicks_mv
AS
SELECT user_id,
       DATE_TRUNC('day', click_ts) AS click_date,
       COUNT(*)                     AS clicks
FROM   clicks
GROUP  BY 1, 2;

-- DT: refresh every 60s
CREATE OR REPLACE DYNAMIC TABLE daily_user_clicks_dt
  TARGET_LAG   = '60 seconds'
  WAREHOUSE    = etl_wh_m       -- medium warehouse to hit 45s refresh
  REFRESH_MODE = 'INCREMENTAL'
AS
SELECT user_id,
       DATE_TRUNC('day', click_ts) AS click_date,
       COUNT(*)                     AS clicks
FROM   clicks
GROUP  BY 1, 2;

Step-by-step explanation.

1. The MV's serverless cost is approximately proportional to the per-row work done at maintenance time. For 1M inserts per hour, the maintenance work is small — Snowflake's incremental MV algorithm updates only the affected (user_id, click_date) aggregates.
2. The DT refresh runs every ~50-55 seconds on the medium warehouse to honour the 60s lag. A medium warehouse burns ~4 credits/hour while active; even with auto-suspend, the warehouse barely gets a chance to sleep between refreshes.
3. Over 24 hours, the DT chain burns roughly (active_hours) × (warehouse_credits_per_hour) ≈ 4 × 24 ≈ ~96 credits (if it stays warm). The MV burns approximately (serverless_per_row_cost) × (hourly_rate) × 24 — typically far less for this row volume.
4. The crossover point lives where MV serverless cost ≈ DT warehouse cost. As a rule of thumb, MV wins on high-frequency, small-batch aggregates; DT wins on low-frequency, large-batch aggregates.
5. If the SELECT needed to join clicks with users, this MV is illegal (no joins allowed) and the DT is the only option — at which point cost is moot because there is no alternative.

Output.

Approach	Freshness	Approx daily credit burn
Materialized View	seconds	low (proportional to per-row work)
Dynamic Table @ 60s	<= 60 sec	~96 (warehouse stays warm)

Rule of thumb. If your aggregate (a) lives on a single table, (b) has a high-frequency change rate, and (c) needs sub-minute freshness, an MV is usually cheaper and fresher than the equivalent DT.

Worked example — DT wins when joins enter the picture

Detailed explanation. The moment you need to enrich a fact with a dimension — even a tiny one — the MV is off the table. The DT is the only Snowflake-native managed primitive that handles joins for you. The hand-rolled alternative is a STREAM + TASK + MERGE for the same outcome, with significantly more code.

Question. Build a customer_revenue aggregate that joins orders with customers and sums by customer tier. Show that the MV cannot express this and that the DT can.

Input — orders.

customer_id	amount
c1	50
c2	30
c1	75

Input — customers.

customer_id	tier
c1	gold
c2	silver

Code.

-- ILLEGAL — Snowflake MV cannot join two base tables
-- CREATE MATERIALIZED VIEW customer_revenue_mv
-- AS
-- SELECT c.tier, SUM(o.amount) AS revenue
-- FROM   orders o JOIN customers c USING (customer_id)
-- GROUP  BY c.tier;
-- > Materialized views do not support joins.

-- DT — legal, incremental-safe equi-join + GROUP BY
CREATE OR REPLACE DYNAMIC TABLE customer_revenue_dt
  TARGET_LAG   = '5 minutes'
  WAREHOUSE    = etl_wh
  REFRESH_MODE = 'INCREMENTAL'
AS
SELECT c.tier,
       SUM(o.amount) AS revenue
FROM   orders o
JOIN   customers c USING (customer_id)
GROUP  BY c.tier;

Step-by-step explanation.

1. Snowflake refuses to create an MV with a JOIN clause — the error message is explicit. The maintenance algorithm needs a single base relation to incrementally propagate changes.
2. The DT version compiles cleanly. Equi-joins are on the incremental surface, and the GROUP BY is a textbook incremental aggregate.
3. On every refresh, Snowflake computes the change set on both orders and customers, joins the deltas correctly (preserving change semantics across both sides), and updates the materialised result.
4. Because the join is incremental, the refresh cost scales with change volume, not source size. A 100M-row orders table with 1K new orders per refresh costs roughly the same to refresh as a 1M-row orders table with 1K new orders per refresh.
5. If you absolutely need MV semantics on a joined view, the workaround is two MVs (one per base table) feeding a downstream regular view — but you lose the automatic query rewrite, and you might as well use a DT.

Output.

tier	revenue
gold	125
silver	30

Rule of thumb. The instant your SELECT needs a join, an MV is off the table. Reach for a DT (or, if the SELECT can't be incremental, a scheduled CREATE OR REPLACE TABLE task).

Senior interview question on the MV vs DT decision

A senior interviewer might ask: "You inherited a Snowflake account with 40 Materialized Views and a mandate to migrate to Dynamic Tables. Walk me through the decision tree per MV — which ones do you migrate, which do you keep, and what's the risk profile of each?"

Solution Using a per-object migration triage

-- 1. Inventory MVs by shape
SELECT v.table_schema  AS schema_name,
       v.table_name    AS mv_name,
       v.text          AS body,
       v.refreshed_on  AS last_refresh,
       v.last_altered  AS last_altered
FROM   information_schema.views v
WHERE  v.table_type = 'MATERIALIZED VIEW';

-- 2. Categorise by join surface
WITH categorised AS (
  SELECT mv_name,
         CASE
            WHEN UPPER(body) LIKE '% JOIN %' THEN 'join_required'
            WHEN UPPER(body) LIKE '%WINDOW(%' OR UPPER(body) LIKE '% OVER %' THEN 'window_required'
            WHEN UPPER(body) LIKE '%DISTINCT%' THEN 'distinct_required'
            ELSE 'single_table_agg'
         END AS shape
  FROM   mv_inventory
)
SELECT shape, COUNT(*) AS mvs
FROM   categorised
GROUP  BY shape;

Step-by-step trace.

MV shape category	Migrate to DT?	Reason
single_table_agg	usually no	MV's serverless maintenance + query rewrite beats a DT refresh on the same SELECT
join_required	(already illegal as an MV — this category should be empty; if it's here, it was a regular view mis-categorised)	—
window_required	yes, but expect FULL refresh fallback in AUTO mode; consider explicit REFRESH_MODE = 'FULL'
distinct_required	yes — distinct is not in MV surface, was probably a workaround

Output:

MV category	Recommendation	Risk
single_table_agg	keep as MV	none
window/distinct	migrate to DT	watch refresh_mode; force INCREMENTAL where possible
MV + downstream task that needs joins	wrap MV + a DT on top	extra cost but preserves query rewrite

Why this works — concept by concept:

• Decision per object, not per platform — "migrate everything to DT" is a bad answer; the MV's automatic query rewrite is a feature DTs cannot replace.
• Shape-based triage — categorising by SELECT shape (single-table, join-required, window-required) maps cleanly onto the MV vs DT decision lattice.
• Cost forecasting — DTs cost warehouse credits per refresh; MVs cost serverless credits per per-row maintenance. Forecast both before flipping.
• Risk control — pin REFRESH_MODE = 'INCREMENTAL' on migrated DTs to fail loud; keep MVs that work because the query-rewrite is invisible operational value.
• Cost — migration moves cost from serverless to warehouse credits; a careless migration can 10x the bill on a high-volume aggregate.

SQL
Topic — aggregation
Aggregation problems (MV/DT pre-agg surface)

Practice →

SQL Topic — joins Join problems for DT-only workloads

Practice →

---

4. Incremental refresh supported SQL surface

The incremental refresh surface is a small allow-list — stay on it and refreshes are O(delta); leave it and AUTO silently flips to FULL

The mental model in one line: incremental refresh in Snowflake supports filters, projections, equality-condition joins (INNER + most LEFT), GROUP BY, and most aggregate functions — anything else falls back to full refresh and pays O(source) cost per run. Once you internalise the allow-list, every "why is my DT going full refresh?" interview becomes a SELECT-by-SELECT audit against that list.

What's on the incremental surface.

• Projections. SELECT col1, col2, expr(col3) ... — any column expression that is deterministic per-row.
• Filters. WHERE clauses on any base column, including comparisons, IN, BETWEEN, IS NULL, and CASE.
• Equi-joins. JOIN ... ON a.x = b.y (INNER) — incremental.
• LEFT joins on most equality conditions. Supported with caveats; the left side is the driver and the right side must be deterministic from the join key.
• GROUP BY + aggregate functions. SUM, COUNT, MIN, MAX, AVG, APPROX_COUNT_DISTINCT. Incremental computes deltas correctly because aggregates are commutative across deltas.
• UNION ALL. Supported (each side's deltas accumulate).
• Sub-selects that resolve to filters or projections. A nested SELECT that only filters or projects is folded into the outer plan and stays incremental.

What falls back to full refresh.

• Window functions. ROW_NUMBER() OVER (...), LAG, LEAD, RANK, DENSE_RANK, running aggregates — none are incremental. The DT will run FULL on every refresh.
• QUALIFY. Often a thin wrapper around a window function; same outcome.
• OUTER joins on non-equality conditions. A FULL OUTER JOIN ... ON a.x BETWEEN b.lo AND b.hi is not incremental; a FULL OUTER JOIN ... ON a.x = b.x may or may not be (test).
• DISTINCT. The deduplication semantics don't survive incremental change accumulation.
• Subqueries in SELECT that reference different base tables. A correlated subquery in the projection list typically forces full refresh.
• Some UDFs. SQL UDFs that read another table inside the body are usually full; scalar SQL UDFs that are pure (just arithmetic on inputs) are usually incremental.
• HAVING with complex predicates. Simple HAVING SUM(...) > N is usually incremental; HAVING referencing window-aggregated columns is full.

How to know — DYNAMIC_TABLE_REFRESH_HISTORY is the truth.

• The refresh_mode and refresh_action columns in DYNAMIC_TABLE_REFRESH_HISTORY reveal the actual runtime behaviour.
• refresh_action = 'INCREMENTAL' confirms the optimiser is computing deltas.
• refresh_action = 'FULL' or 'REINITIALIZE' confirms a full rebuild — either because you set REFRESH_MODE = 'FULL', or because AUTO fell back.
• The STATISTICS JSON column shows numInsertedRows, numUpdatedRows, numDeletedRows — wildly large numbers (close to source row count) every refresh confirm the fallback.

The SHOW DYNAMIC TABLES view.

• Returns one row per DT with target_lag, refresh_mode, scheduling_state, and the SELECT body.
• Combined with DYNAMIC_TABLE_REFRESH_HISTORY(), this is your standing audit query.

Diagnosing why a DT goes full — the standing query.

SELECT name,
       refresh_mode,
       refresh_action,
       refresh_trigger,
       refresh_start_time,
       DATEDIFF('second', refresh_start_time, refresh_end_time) AS sec,
       statistics:numInsertedRows::int AS ins,
       statistics:numUpdatedRows::int  AS upd
FROM   TABLE(INFORMATION_SCHEMA.DYNAMIC_TABLE_REFRESH_HISTORY(
              DATE_RANGE_START => DATEADD('day', -1, CURRENT_TIMESTAMP)))
WHERE  refresh_action IN ('FULL', 'REINITIALIZE')
ORDER  BY refresh_start_time DESC;

Common interview probes on the surface.

• "What SQL constructs force a DT into FULL refresh?" — window functions, QUALIFY, DISTINCT, some OUTER joins, some UDFs.
• "What does refresh_action = 'REINITIALIZE' mean?" — the DT was rebuilt from scratch, usually because the underlying definition or source changed.
• "How do you keep a DT incremental when you need a top-N per group?" — compute the aggregate in an incremental DT and do the top-N in a downstream view (or a downstream DT that runs FULL but on a tiny aggregate).
• "Can you join two DTs and stay incremental?" — yes, if the join is equality and both sides are themselves incremental.

Worked example — rewriting a top-N to keep it incremental

Detailed explanation. A common requirement: top-3 orders per customer by amount. The naive single-SELECT uses QUALIFY ROW_NUMBER() OVER ... and falls back to FULL refresh. A two-step rewrite — incremental aggregate in one DT, top-N in a downstream view or DT — keeps the bulk of the work incremental.

Question. Rewrite a "top-3 orders per customer" DT from a single SELECT with a window to a two-step pipeline that keeps the aggregate incremental.

Input — orders.

customer_id	order_id	amount
c1	o1	50
c1	o2	75
c1	o3	30
c1	o4	20
c2	o5	100
c2	o6	40

Code.

-- BEFORE — single DT, falls back to FULL because of QUALIFY ROW_NUMBER
CREATE OR REPLACE DYNAMIC TABLE top3_orders_per_customer_full
  TARGET_LAG   = '5 minutes'
  WAREHOUSE    = etl_wh
  REFRESH_MODE = 'AUTO'
AS
SELECT customer_id, order_id, amount
FROM   orders
QUALIFY ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY amount DESC) <= 3;

-- AFTER — Step 1 incremental DT keyed by (customer_id, amount, order_id)
CREATE OR REPLACE DYNAMIC TABLE customer_orders_indexed
  TARGET_LAG   = '5 minutes'
  WAREHOUSE    = etl_wh
  REFRESH_MODE = 'INCREMENTAL'   -- fails loud if not incremental
AS
SELECT customer_id, order_id, amount
FROM   orders;

-- AFTER — Step 2 thin downstream view that picks top 3
CREATE OR REPLACE VIEW top3_orders_per_customer AS
SELECT customer_id, order_id, amount
FROM   customer_orders_indexed
QUALIFY ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY amount DESC) <= 3;

Step-by-step explanation.

1. The naive single DT uses QUALIFY ROW_NUMBER. With REFRESH_MODE = 'AUTO', it creates successfully and silently runs FULL on every refresh — the optimiser cannot incrementally maintain the window's ranking under inserts/updates.
2. The rewrite splits the work. Step 1 is a pure projection of the source — trivially incremental, costs almost nothing per refresh because the change set is tiny.
3. Step 2 is a view (not a DT) that applies the window at query time. The window runs over the pre-aggregated, freshness-bounded DT rather than the raw orders source.
4. Because the view runs at query time, every consumer query pays the window cost on the DT's row count — but the DT's row count is dramatically smaller than orders after deduplication (or projection), so the window is cheap.
5. The net effect: the costly refresh is incremental (small per-run delta), and the costly window runs once per consumer query against a small materialised input.

Output (top 3 per customer).

customer_id	order_id	amount
c1	o2	75
c1	o1	50
c1	o3	30
c2	o5	100
c2	o6	40

Rule of thumb. Push windowing to a downstream view, not into the DT body. Keep the DT's SELECT on the incremental surface; let the consumer query handle anything that isn't.

Worked example — incremental-safe equi-join enrichment

Detailed explanation. A canonical analytic shape — enrich orders with customers.tier and aggregate by tier. The whole pipeline stays incremental because both sides of the join are equi-joined and the GROUP BY is a textbook incremental aggregate. Walking through this teaches what "incremental-safe" looks like in practice.

Question. Build a DT that joins orders with customers and aggregates revenue by tier. Show the DDL, the refresh history confirming incremental, and how the change set is computed on a tier update.

Input — orders.

customer_id	amount
c1	50
c2	30
c1	75

Input — customers.

customer_id	tier
c1	gold
c2	silver

Code.

CREATE OR REPLACE DYNAMIC TABLE revenue_by_tier
  TARGET_LAG   = '5 minutes'
  WAREHOUSE    = etl_wh
  REFRESH_MODE = 'INCREMENTAL'
AS
SELECT c.tier,
       SUM(o.amount) AS revenue,
       COUNT(*)       AS order_count
FROM   orders o
JOIN   customers c USING (customer_id)
GROUP  BY c.tier;

-- Confirm incremental
SELECT name, refresh_action, refresh_trigger, statistics:numInsertedRows::int AS ins
FROM   TABLE(INFORMATION_SCHEMA.DYNAMIC_TABLE_REFRESH_HISTORY())
WHERE  name = 'REVENUE_BY_TIER'
ORDER  BY refresh_start_time DESC
LIMIT  5;

Step-by-step explanation.

1. The SELECT lives entirely on the incremental surface: an equi-join + GROUP BY + SUM/COUNT.
2. With REFRESH_MODE = 'INCREMENTAL' set explicitly, the create-time planner verifies the SELECT can be incrementally maintained. If anyone later adds a window function to this DDL, the next CREATE OR REPLACE will fail loud.
3. On every refresh, Snowflake computes the change set on orders and customers independently, joins the change sets correctly across both sides, and applies additive deltas to the materialised aggregate.
4. A subtle case: a customers row updates from tier = 'silver' to tier = 'gold'. The change-set algorithm subtracts the customer's contribution from the silver aggregate and adds it to the gold aggregate. This works only because SUM and COUNT are additively reversible.
5. If you replaced SUM(amount) with MAX(amount), the incremental story would be subtler — a tier reassignment can require recomputing the MAX for both tiers — but Snowflake handles MAX/MIN deltas correctly in 2026.

Output.

tier	revenue	order_count
gold	125	2
silver	30	1

Rule of thumb. Equi-joins + GROUP BY + reversible aggregates (SUM, COUNT) are the gold-standard incremental shape. Build dimensions that join cleanly on a single key; avoid range joins or non-equi conditions in the DT body.

Worked example — the refresh_action = FULL diagnosis

Detailed explanation. A DT was created in AUTO mode six months ago. The bill has crept up over time. A senior interview probe: walk me through the exact query you'd run to figure out which DTs are doing FULL refresh and why.

Question. Write the audit query that lists every DT that did FULL refresh in the last 24 hours, ordered by the credits each one burned.

Input.

DT name	refresh_mode (declared)	refresh_action (observed)	refreshes / day	sec / refresh
revenue_by_tier	INCREMENTAL	INCREMENTAL	288	4
top3_orders_per_cust	AUTO	FULL	288	45
daily_active_users	AUTO	FULL	288	90

Code.

WITH last_24h AS (
  SELECT name,
         refresh_action,
         refresh_mode,
         refresh_start_time,
         DATEDIFF('second', refresh_start_time, refresh_end_time) AS refresh_sec
  FROM   TABLE(INFORMATION_SCHEMA.DYNAMIC_TABLE_REFRESH_HISTORY(
                DATE_RANGE_START => DATEADD('day', -1, CURRENT_TIMESTAMP)))
)
SELECT name,
       refresh_mode,
       COUNT(*)                       AS refreshes,
       SUM(IFF(refresh_action = 'FULL', 1, 0))         AS full_refreshes,
       SUM(IFF(refresh_action = 'INCREMENTAL', 1, 0))  AS incr_refreshes,
       SUM(refresh_sec)                                AS total_sec
FROM   last_24h
GROUP  BY name, refresh_mode
HAVING SUM(IFF(refresh_action = 'FULL', 1, 0)) > 0
ORDER  BY total_sec DESC;

Step-by-step explanation.

1. The CTE pulls every refresh in the last 24 hours from DYNAMIC_TABLE_REFRESH_HISTORY(). The table function accepts a date range to bound the scan.
2. The outer aggregate counts how many refreshes per DT were FULL vs INCREMENTAL, and totals the seconds spent. Sec-spent is a tight proxy for warehouse credits when warehouse size is constant across the DTs.
3. The HAVING clause filters to only DTs that had any FULL refresh in the window — the suspects you actually care about.
4. The ORDER BY by total_sec DESC brings the costliest offenders to the top. The top of the list is your migration backlog.
5. For each offender, the next step is to read the DT body (via GET_DDL or SHOW DYNAMIC TABLES) and find the non-incremental construct (window, QUALIFY, DISTINCT, OUTER join on non-equality).

Output (suspect list).

name	refresh_mode	refreshes	full_refreshes	incr_refreshes	total_sec
daily_active_users	AUTO	288	288	0	25,920
top3_orders_per_cust	AUTO	288	288	0	12,960
revenue_by_tier	INCREMENTAL	288	0	288	1,152

Rule of thumb. Run this query weekly. Any DT that is silently doing FULL in AUTO mode is a bug; either flip it to INCREMENTAL (so the next change errors out) or rewrite the SELECT to stay on the incremental surface.

Senior interview question on the incremental surface

A senior interviewer might ask: "A teammate ships a DT body that includes a LEFT JOIN ... ON a.x = b.x AND a.y BETWEEN b.lo AND b.hi. They say AUTO mode means it'll Just Work. You disagree. Walk me through why, what the production failure mode is, and how you'd rewrite."

Solution Using a surface-audit + split-pipeline rewrite

-- Original — looks innocent, lives in AUTO mode, will run FULL forever
CREATE OR REPLACE DYNAMIC TABLE daily_user_geo_band
  TARGET_LAG   = '15 minutes'
  WAREHOUSE    = etl_wh
  REFRESH_MODE = 'AUTO'
AS
SELECT u.user_id,
       u.signup_ts,
       g.band_name
FROM   users u
LEFT JOIN geo_bands g
       ON u.country_code = g.country_code
      AND u.signup_age BETWEEN g.min_age AND g.max_age;

-- Rewrite — Step 1, incremental equi-join only
CREATE OR REPLACE DYNAMIC TABLE users_with_country_band
  TARGET_LAG   = '15 minutes'
  WAREHOUSE    = etl_wh
  REFRESH_MODE = 'INCREMENTAL'
AS
SELECT u.user_id,
       u.signup_ts,
       u.signup_age,
       g.band_id,
       g.min_age,
       g.max_age,
       g.band_name
FROM   users u
JOIN   geo_bands g
       ON u.country_code = g.country_code;

-- Rewrite — Step 2, thin view that applies the range predicate
CREATE OR REPLACE VIEW daily_user_geo_band AS
SELECT user_id, signup_ts, band_name
FROM   users_with_country_band
WHERE  signup_age BETWEEN min_age AND max_age;

Step-by-step trace.

Step	Construct	Incremental?	Comment
Before	LEFT JOIN with BETWEEN predicate	no	non-equi predicate forces FULL
After-1	INNER JOIN on country_code only	yes	classic incremental equi-join
After-2	Downstream view applies BETWEEN	n/a (view)	predicate runs at query time, no refresh cost

The BETWEEN predicate moves from the DT body (where it would be evaluated on every refresh) to a downstream view (where it's evaluated at query time on a much smaller, already-filtered, incrementally-maintained set).

Output:

Approach	Daily refresh credits	Freshness
Original (FULL)	high (O(users × geo_bands per refresh))	15 min
Rewrite (INCREMENTAL)	low (O(delta))	15 min

Why this works — concept by concept:

• Equi-join is the incremental contract — once you add a non-equality predicate to the join condition, the optimiser cannot incrementally maintain the join.
• Predicate pushdown into a view — non-incremental predicates belong outside the DT body. The view computes them at query time over the incremental aggregate.
• Pinning REFRESH_MODE = 'INCREMENTAL' — explicit mode forces a create-time error on the original; that's the loud failure that prevents the production cost regression.
• Surface audit as a habit — running the refresh_action = 'FULL' query weekly catches everything that slips past the create-time gate (changes to the source schema, optimiser version drift).
• Cost — incremental refresh is O(delta); full is O(source × dimension). On large fact + dimension tables, the cost ratio is routinely 100x or more.

SQL
Topic — joins
Equi-join + range-predicate rewrite problems

Practice →

SQL Topic — sql Snowflake SQL incremental-surface drills

Practice →

---

5. Production patterns and senior interview signals

Lag budgets, dedicated warehouses, refresh-credit attribution, and a Streams+Tasks migration playbook — production DTs aren't just DDL

The mental model in one line: a production Dynamic Table needs a lag budget that survives chain composition, a dedicated warehouse for cost attribution, a refresh-credit monitor, and a migration playbook for the legacy Streams+Tasks DAG it replaces. Every other production lesson — schema-change handling, suspend/resume, cross-account share — is a consequence of those four foundations.

Lag budget design across a DT chain.

• Leaf SLA drives upstream lag. Start with the freshness contract at the leaf (what the consumer needs). Distribute that lag budget across the chain so that the worst-case composed staleness stays under the SLA.
• Sum the chain when planning, divide when scaling. A three-DT chain with each at TARGET_LAG = '1 minute' worst-case presents ~3 minutes of staleness at the leaf. To hit a 5-minute SLA on a four-DT chain, budget each layer at ~1 minute (with slack).
• TARGET_LAG = DOWNSTREAM — set this on intermediate DTs that don't have an independent SLA. They refresh only when a downstream DT needs fresh data, which avoids paying refresh cost on a layer no one is reading directly.

Warehouse strategy.

• One warehouse per DT layer, not per DT. Group DTs by SLA tightness and by source-table churn pattern so the warehouse usage is predictable.
• Sub-minute lag DTs deserve a dedicated warehouse. A 30-second-lag DT cannot afford to queue behind an ad-hoc analyst query. Isolation pays for itself in lag compliance.
• Auto-suspend tuning. Default 600s auto-suspend is usually wrong for DT warehouses — set it to ~60s if the DT runs every few minutes; longer if refreshes are sparse. The trade-off is auto-resume cold-start latency vs idle-credit waste.
• Scaling up vs out. Scale up (WAREHOUSE_SIZE = MEDIUM → LARGE) when a single refresh is too slow; scale out (multi-cluster) when concurrent refreshes are queueing. DT refresh is single-stream per DT, so multi-cluster mostly helps when many DTs share a warehouse.

Refresh-credit attribution.

• DT refresh credits are billed to the warehouse you nominated, but the cause is the DT. Without explicit attribution, a noisy DT can starve other workloads on a shared warehouse.
• The standing attribution query joins DYNAMIC_TABLE_REFRESH_HISTORY() with QUERY_HISTORY on the query ID to get warehouse credits per DT per day.
• For tight cost monitoring, give each DT layer its own warehouse and tag the warehouse with the DT name in your cost dashboard.

Migration: Streams+Tasks → Dynamic Tables.

• Inventory first. List every (STREAM, TASK, target table) trio and the SELECT body of each task's MERGE clause.
• Triage by SELECT-ability. Tasks whose MERGE body is just INSERT/UPDATE ... SELECT ... are direct DT candidates. Tasks with stored proc calls, external functions, or row-level branching stay on Streams+Tasks.
• Migrate in pairs. Build the DT alongside the existing Streams+Tasks pipeline, run both in parallel, diff the outputs hourly. Once parity holds for 7 days, kill the Streams+Tasks objects.
• Save the DDL. Snowflake GET_DDL('table', 'daily_revenue') doesn't render Streams+Tasks DDL fully; export your migration plan to a versioned dbt or Terraform repo so you can roll back if needed.

Schema-change handling on DTs.

• Adding a column to the source: the DT's next refresh picks up the new column if it's in the SELECT (i.e., SELECT * FROM source — generally avoided) or remains unchanged otherwise. Best practice is explicit column projection.
• Dropping a column referenced by the DT: the next refresh fails. Snowflake's RESUME won't recover until the DT body is corrected.
• Changing the DT body: requires CREATE OR REPLACE DYNAMIC TABLE. The next refresh is REINITIALIZE (full rebuild). For huge DTs, this is non-trivial — schedule the change in a maintenance window and consider a warehouse upsize for the rebuild.

Cross-account share + downstream refresh (2025 feature).

• A DT can be added to a share. The consumer account can CREATE DATABASE FROM SHARE ... and read the DT. Refresh runs in the producer account on the producer's warehouse.
• For downstream-paid refresh, the producer marks the DT with TARGET_LAG = DOWNSTREAM and the consumer's queries pull through. The consumer pays for the refresh credits on a warehouse they nominate.

Common interview probes on production.

• "How would you budget TARGET_LAG across a 4-DT chain to hit a 5-minute leaf SLA?" — ~1 minute per layer or use DOWNSTREAM mode for intermediate DTs.
• "How do you attribute refresh cost to a single DT?" — dedicated warehouse + DYNAMIC_TABLE_REFRESH_HISTORY join with QUERY_HISTORY.
• "How do you handle a column drop on a DT's source?" — fix the DT body before the source change lands (or pin the source schema).
• "When does Streams+Tasks still win?" — imperative logic, external function calls, sub-second SLA.

Worked example — lag-budget design across a four-DT chain

Detailed explanation. A senior interviewer hands you a four-DT chain — raw → clean → fact → mart — and a 5-minute SLA at the mart layer. Walk through the lag-budget distribution, the warehouse assignment, and the breach-alert query.

Question. Distribute a 5-minute leaf SLA across a four-DT chain. Assign warehouses, show the DDL pattern, and write the alert that fires when the realised lag exceeds budget.

Input.

Layer	DT name	Source churn	Typical refresh duration
1	orders_raw_clean	1K rows/min	5 s on SMALL
2	orders_fact	5K rows/refresh	12 s on MEDIUM
3	customer_fact	10K rows/refresh	18 s on MEDIUM
4	customer_mart	8K rows/refresh	30 s on LARGE

Code.

CREATE OR REPLACE DYNAMIC TABLE orders_raw_clean
  TARGET_LAG   = '1 minute'
  WAREHOUSE    = dt_wh_l1_clean
  REFRESH_MODE = 'INCREMENTAL'
AS SELECT * FROM orders_raw WHERE status = 'COMPLETED';

CREATE OR REPLACE DYNAMIC TABLE orders_fact
  TARGET_LAG   = '1 minute'
  WAREHOUSE    = dt_wh_l2_fact
  REFRESH_MODE = 'INCREMENTAL'
AS
SELECT order_id, customer_id, DATE_TRUNC('day', order_ts) AS order_date, amount
FROM   orders_raw_clean;

CREATE OR REPLACE DYNAMIC TABLE customer_fact
  TARGET_LAG   = '2 minutes'
  WAREHOUSE    = dt_wh_l3_fact
  REFRESH_MODE = 'INCREMENTAL'
AS
SELECT c.customer_id, c.tier, SUM(o.amount) AS total, COUNT(*) AS orders
FROM   customers c
JOIN   orders_fact o USING (customer_id)
GROUP  BY c.customer_id, c.tier;

CREATE OR REPLACE DYNAMIC TABLE customer_mart
  TARGET_LAG   = '5 minutes'
  WAREHOUSE    = dt_wh_l4_mart
  REFRESH_MODE = 'INCREMENTAL'
AS
SELECT customer_id, tier, total, orders, total / NULLIF(orders, 0) AS avg_order_value
FROM   customer_fact;

-- Lag-breach alert
CREATE OR REPLACE TASK dt_chain_breach_alert
  WAREHOUSE = monitor_wh
  SCHEDULE  = '1 MINUTE'
AS
INSERT INTO ops.dt_lag_breaches
SELECT current_timestamp, name, target_lag_sec, mean_lag_sec, scheduling_state
FROM   TABLE(INFORMATION_SCHEMA.DYNAMIC_TABLE_GRAPH_HISTORY(
              AS_OF => CURRENT_TIMESTAMP))
WHERE  mean_lag_sec > target_lag_sec * 1.2;   -- 20% slack

Step-by-step explanation.

1. Layer 1 (raw clean) is the lightest work — a filter. 1-minute lag is comfortable on a SMALL warehouse with ~5s refresh duration.
2. Layer 2 (orders_fact) is a projection over Layer 1 with a date trunc. 1-minute lag is fine; medium warehouse keeps refresh under 15s.
3. Layer 3 (customer_fact) joins customers with orders_fact and aggregates. 2-minute lag absorbs join+aggregate cost.
4. Layer 4 (customer_mart) is a projection over Layer 3. 5-minute lag is the leaf SLA; the worst-case composed lag through the chain is ~9 minutes if every upstream is at maximum staleness — but typical realised lag is ~3-4 minutes because actual refreshes finish well within target.
5. The alert task runs every minute, reads DYNAMIC_TABLE_GRAPH_HISTORY, and writes a breach row whenever realised mean lag exceeds 1.2x the target. That's your early-warning signal before the leaf SLA goes red.

Output.

Layer	TARGET_LAG	Warehouse	Worst-case realised lag
1	1 min	dt_wh_l1_clean	1 min
2	1 min	dt_wh_l2_fact	2 min (own + upstream)
3	2 min	dt_wh_l3_fact	4 min (own + upstream)
4	5 min	dt_wh_l4_mart	< 5 min (within SLA)

Rule of thumb. Budget upstream lags generously and tighten the leaf to the SLA. Composed lag is additive in the worst case, so the leaf must be less than the sum — Snowflake's scheduler will work to honour both contracts but cannot violate the laws of arithmetic.

Worked example — refresh credit attribution per DT

Detailed explanation. A team complains that the Snowflake bill on the analytics warehouse doubled last month. The DT chain feeding the customer-360 dashboard is the prime suspect, but five other workloads run on the same warehouse. The senior engineer's first move: per-DT credit attribution.

Question. Write the query that attributes warehouse credits to each DT for the last 7 days, joining DYNAMIC_TABLE_REFRESH_HISTORY with QUERY_HISTORY on the refresh query ID.

Input.

DT name	Warehouse	Refreshes / day	Avg refresh sec
customer_mart	analytics_wh	288	30
orders_fact	analytics_wh	288	12
inventory_snapshot	analytics_wh	24	240
churn_features	analytics_wh	24	600

Code.

WITH refresh_runs AS (
  SELECT name                                 AS dt_name,
         refresh_start_time,
         refresh_end_time,
         refresh_action,
         query_id
  FROM   TABLE(INFORMATION_SCHEMA.DYNAMIC_TABLE_REFRESH_HISTORY(
                DATE_RANGE_START => DATEADD('day', -7, CURRENT_TIMESTAMP)))
)
SELECT r.dt_name,
       q.warehouse_name,
       q.warehouse_size,
       COUNT(*)                          AS refreshes,
       SUM(q.credits_used_cloud_services + q.execution_time / 1000.0 / 3600.0
           * CASE q.warehouse_size
                WHEN 'X-Small' THEN 1
                WHEN 'Small'   THEN 2
                WHEN 'Medium'  THEN 4
                WHEN 'Large'   THEN 8
                WHEN 'X-Large' THEN 16
              END)                         AS approx_credits
FROM   refresh_runs r
JOIN   snowflake.account_usage.query_history q
  ON   r.query_id = q.query_id
GROUP  BY r.dt_name, q.warehouse_name, q.warehouse_size
ORDER  BY approx_credits DESC;

Step-by-step explanation.

1. The CTE pulls 7 days of refreshes per DT, including the query_id of the refresh query.
2. The join to account_usage.query_history lifts the warehouse + warehouse_size + execution_time fields for each refresh query.
3. The approx-credit calculation multiplies execution-time-in-hours by the credit-per-hour rate for the warehouse size (XS=1, S=2, M=4, L=8, XL=16; standard Snowflake credit rates).
4. The result is per-DT credit attribution sorted by approximate credits over 7 days. The top of the list is your cost-optimisation target.
5. For deeper attribution, join further to account_usage.warehouse_metering_history for actual warehouse credits, but per-query approx is close enough for first-pass triage.

Output (top suspects).

dt_name	warehouse_name	warehouse_size	refreshes	approx_credits
churn_features	analytics_wh	LARGE	168	224
inventory_snapshot	analytics_wh	LARGE	168	56
customer_mart	analytics_wh	MEDIUM	2016	26.9
orders_fact	analytics_wh	MEDIUM	2016	10.8

Rule of thumb. Per-DT credit attribution is the first metric to land in your cost dashboard. The minute a single DT crosses 20% of the warehouse spend, it deserves its own warehouse — so cost surprises stay local.

Worked example — Streams+Tasks → Dynamic Tables migration

Detailed explanation. A team has a legacy daily_customer_summary Streams+Tasks pipeline — three streams, two tasks, a target table, ~120 lines of SQL. They want to migrate to a DT. The senior playbook: build the DT in parallel, diff outputs, kill the legacy when parity holds.

Question. Walk through the migration plan for a daily_customer_summary Streams+Tasks pipeline to a Dynamic Table. Show the parallel-running configuration and the parity-check query.

Input.

Object	Type	Lines of SQL
stream_orders	STREAM	5
stream_customers	STREAM	5
stream_payments	STREAM	5
task_refresh_summary_part1	TASK	45
task_refresh_summary_part2	TASK	35
daily_customer_summary	TABLE	8 (target)

Code.

-- Phase 1: build the DT alongside the existing pipeline, with a _dt suffix
CREATE OR REPLACE DYNAMIC TABLE daily_customer_summary_dt
  TARGET_LAG   = '5 minutes'
  WAREHOUSE    = dt_wh_migration
  REFRESH_MODE = 'INCREMENTAL'
AS
SELECT c.customer_id,
       c.tier,
       COALESCE(SUM(o.amount), 0)  AS total_orders,
       COALESCE(SUM(p.amount), 0)  AS total_payments,
       COUNT(DISTINCT o.order_id)   AS order_count
FROM   customers c
LEFT JOIN orders   o USING (customer_id)
LEFT JOIN payments p USING (customer_id)
GROUP  BY c.customer_id, c.tier;

-- Phase 2: parity check — diff the DT vs the legacy table, hourly
CREATE OR REPLACE TASK parity_check_daily_customer_summary
  WAREHOUSE = monitor_wh
  SCHEDULE  = '60 MINUTE'
AS
INSERT INTO ops.parity_log
SELECT current_timestamp,
       'daily_customer_summary',
       COUNT(*) AS diff_rows
FROM (
  SELECT customer_id, tier, total_orders, total_payments, order_count FROM daily_customer_summary
  MINUS
  SELECT customer_id, tier, total_orders, total_payments, order_count FROM daily_customer_summary_dt
  UNION ALL
  SELECT customer_id, tier, total_orders, total_payments, order_count FROM daily_customer_summary_dt
  MINUS
  SELECT customer_id, tier, total_orders, total_payments, order_count FROM daily_customer_summary
);

-- Phase 3 (after 7 days of zero-diff): swap consumers, drop legacy
-- ALTER TASK task_refresh_summary_part1 SUSPEND;
-- ALTER TASK task_refresh_summary_part2 SUSPEND;
-- DROP STREAM stream_orders;
-- DROP STREAM stream_customers;
-- DROP STREAM stream_payments;
-- ALTER VIEW daily_customer_summary RENAME TO daily_customer_summary_old;
-- CREATE OR REPLACE VIEW daily_customer_summary AS SELECT * FROM daily_customer_summary_dt;

Step-by-step explanation.

1. Phase 1 builds the DT with the new suffix while the legacy pipeline keeps running. The DT consumes its own warehouse so legacy task scheduling is unaffected.
2. Phase 2 is the parity check — a two-way MINUS over the shared columns. Zero rows in both directions means the DT output is bitwise identical to the legacy table.
3. Run the parity check hourly. The ops.parity_log table accumulates diff counts; any non-zero row is an investigation.
4. After 7 days of zero diffs (and a deliberate fault injection to confirm the parity check catches diffs when they happen), the migration enters Phase 3: suspend the tasks, drop the streams, swap the consumers.
5. The view-rename trick keeps consumer SQL unchanged. Anything querying daily_customer_summary is now reading the DT through a view; future cleanups can rename the DT directly.

Output (migration timeline).

Day	Activity
0	Phase 1 + Phase 2 deployed
1-7	Parity check runs hourly; expect zero diffs
8	Phase 3 — suspend tasks, drop streams, swap consumers
14	Drop legacy target table after 1 week of validation

Rule of thumb. Never replace a Streams+Tasks pipeline with a Dynamic Table in-place. Run them in parallel for at least a week, diff hourly, and only retire the legacy once parity is mechanically proven.

Senior interview question on production readiness

A senior interviewer might ask: "Your team migrated 20 pipelines to Dynamic Tables. The CFO emails: 'the Snowflake bill doubled last quarter, what happened?' You have 30 minutes to triage. Walk me through your investigation, what queries you run, and what you'd present back."

Solution Using a credit-budget audit + refresh-mode regression sweep

-- 1. Per-warehouse DT credit trend, week-over-week
WITH dt_runs AS (
  SELECT name,
         q.warehouse_name,
         q.start_time,
         q.execution_time / 1000.0 / 3600.0 *
           CASE q.warehouse_size
                WHEN 'X-Small' THEN 1
                WHEN 'Small'   THEN 2
                WHEN 'Medium'  THEN 4
                WHEN 'Large'   THEN 8
                WHEN 'X-Large' THEN 16
            END AS credits
  FROM   TABLE(INFORMATION_SCHEMA.DYNAMIC_TABLE_REFRESH_HISTORY(
                DATE_RANGE_START => DATEADD('day', -90, CURRENT_TIMESTAMP))) r
  JOIN   snowflake.account_usage.query_history q
    ON   r.query_id = q.query_id
)
SELECT DATE_TRUNC('week', start_time) AS wk,
       warehouse_name,
       SUM(credits) AS credits
FROM   dt_runs
GROUP  BY 1, 2
ORDER  BY 1, 2;

-- 2. Per-DT FULL refresh regression sweep
SELECT name,
       SUM(IFF(refresh_action = 'FULL', 1, 0)) AS full_count,
       SUM(IFF(refresh_action = 'INCREMENTAL', 1, 0)) AS incr_count,
       MIN(refresh_start_time) AS first_seen,
       MAX(refresh_start_time) AS last_seen
FROM   TABLE(INFORMATION_SCHEMA.DYNAMIC_TABLE_REFRESH_HISTORY(
              DATE_RANGE_START => DATEADD('day', -90, CURRENT_TIMESTAMP)))
GROUP  BY name
HAVING full_count > 0
ORDER  BY full_count DESC;

Step-by-step trace.

Hypothesis	Query	Outcome
Warehouse-level credit increase	per-warehouse weekly	analytics_wh doubled, others flat
A DT silently went FULL	per-DT FULL count	churn_features shows 100% FULL action since 7 weeks ago — coincides with someone adding QUALIFY ROW_NUMBER to body
Lag budget too tight	mean_lag_sec > target_lag	not the issue here; no breach pattern
New DTs added	dt-count over time	4 new DTs but small ones, ~5% of total cost

The smoking gun is churn_features flipping to FULL refresh seven weeks ago, contributing ~60% of the warehouse credit increase. Other DTs are nominal.

Output:

Finding	Cost impact	Remediation
churn_features now FULL	+60% on warehouse	rewrite to incremental-safe (split window into view)
4 new DTs added	+5% on warehouse	accept; aligned with new feature work
Lag breaches	0%	none

Why this works — concept by concept:

• Credit attribution is the diagnosis tool — without per-DT credit numbers, you can only point at the warehouse bill and shrug. With per-DT credits, the offender names itself.
• FULL-refresh sweep — the AUTO-mode silent regression is the single most common cost pathology; weekly sweeps catch it within days of introduction.
• Trend over time — week-over-week credit views show step-changes that point to specific deploys. The seven-week-old change correlates exactly with the cost regression.
• Forced INCREMENTAL on critical DTs — the lesson from this incident is to pin REFRESH_MODE = 'INCREMENTAL' on every DT in the audit so the next regression errors at deploy time.
• Cost — once you have credit attribution, every dollar saved is traceable to a specific DT body change. Without it, every dollar saved is luck.

ETL
Topic — etl
ETL pipeline migration problems

Practice →

SQL
Topic — sql
Snowflake refresh + audit drills

Practice →

---

Cheat sheet — Dynamic Tables recipes

• The five-line DT. CREATE OR REPLACE DYNAMIC TABLE name TARGET_LAG = 'N minutes' WAREHOUSE = wh REFRESH_MODE = 'INCREMENTAL' AS SELECT ... FROM source GROUP BY ...; — the canonical pattern. Pin INCREMENTAL on every critical-path DT to fail loud.
• Lag SLA design. Start at the leaf with the consumer SLA; budget each upstream layer at no more than SLA / chain_depth. Mid-chain DTs with no independent SLA should run TARGET_LAG = DOWNSTREAM so they refresh only when the leaf needs them.
• "Why is my DT going FULL?" runbook. Inspect INFORMATION_SCHEMA.DYNAMIC_TABLE_REFRESH_HISTORY for refresh_action = 'FULL'. Inspect the DT body with SELECT GET_DDL('TABLE', 'your_dt'). Look for window functions, QUALIFY, DISTINCT, non-equi-joins, certain UDFs. Rewrite by splitting the non-incremental construct into a downstream view.
• DT-on-DT graph rule of thumb. Worst-case realised lag at the leaf = sum of all upstream TARGET_LAGs. Set the leaf SLA generously enough to absorb that sum, or use DOWNSTREAM mode on upstream DTs to short-circuit unnecessary refreshes.
• Refresh-cost monitor query. Join DYNAMIC_TABLE_REFRESH_HISTORY with account_usage.query_history on query_id. Sum execution-time-in-hours × warehouse credit rate to get per-DT credits. Run weekly; any DT crossing 20% of warehouse spend earns its own warehouse.
• Refresh-mode regression sweep. Weekly query WHERE refresh_action = 'FULL' AND refresh_mode = 'AUTO' — any row is a silent fallback that needs investigation.
• Streams+Tasks migration playbook. Phase 1: build DT in parallel, suffix _dt. Phase 2: hourly parity check via two-way MINUS. Phase 3 after 7 days zero-diff: suspend tasks, drop streams, rename view to point at the DT.
• DT vs MV cheat. MV for single-table pre-aggregations with high query volume and automatic query rewrite. DT for multi-table joins, lag-driven freshness contracts, and DT-on-DT chains. Migrating MVs to DTs without per-object cost forecasts usually multiplies the bill.
• Schema-change discipline. Use explicit column projection in DT bodies (never SELECT *). Coordinate source schema changes through the DT owner. CREATE OR REPLACE on a large DT triggers a full rebuild — schedule during a maintenance window with an upsized warehouse.
• Cross-account share. CREATE SHARE + add DT. Consumer reads through the share; refresh runs on producer warehouse. For consumer-paid refresh, use TARGET_LAG = DOWNSTREAM so refreshes happen on consumer query pull.
• Warehouse sizing. Size up (SMALL → MEDIUM) when single refresh duration > 1/3 of TARGET_LAG. Size out (multi-cluster) when multiple DTs sharing a warehouse queue refresh starts. Sub-minute lag DTs almost always need a dedicated warehouse.
• DYNAMIC_TABLE_GRAPH_HISTORY. The graph-level view of realised vs declared lag, including upstream/downstream dependencies. The single best query to track end-to-end SLA compliance across a chain.

Frequently asked questions

What are Snowflake Dynamic Tables?

Snowflake Dynamic Tables are a declarative pipeline primitive: you write a SELECT statement and declare a TARGET_LAG, and Snowflake's scheduler handles the rest — change detection, refresh timing, and propagation through downstream Dynamic Tables. The DDL is a one-liner (CREATE OR REPLACE DYNAMIC TABLE name TARGET_LAG = '5 minutes' WAREHOUSE = wh AS SELECT ...), and the output is queryable like any other table. Each refresh runs against a warehouse you nominate, and the refresh can be INCREMENTAL (re-process only changed rows), FULL (rebuild from source every time), or AUTO (planner picks incremental when supported). Dynamic Tables landed at GA in April 2023 and matured through 2024-2026 with cross-account share, downstream lag mode, and an expanded incremental-SQL surface. They replace the imperative STREAM + TASK + MERGE pattern that data teams used to maintain incremental aggregates inside Snowflake, while running entirely inside the warehouse — no separate orchestrator, no separate state catalog.

Dynamic Tables vs Materialized Views — which should I use?

The short answer: MVs for single-table query acceleration; DTs for multi-table declarative pipelines. The longer answer is a decision lattice. A snowflake materialized views object can only SELECT from a single base table, doesn't support joins or window functions, but is continuously maintained by Snowflake's serverless layer and benefits from automatic query rewrite (queries against the base table that match the MV shape get transparently rewritten to read from the MV). A Dynamic Table supports the full SQL surface (joins, windows with caveats, multi-table aggregates), refreshes on a lag-driven schedule, and runs on a warehouse you pay for — but it does not get automatic query rewrite. The materialized view vs dynamic table decision boils down to: if your aggregate is single-table, high-frequency, and used by many downstream queries, an MV is usually cheaper and fresher; if it needs joins, windows, or downstream DT-on-DT chaining, a DT is the only option. Most production systems use both — MVs underneath for query acceleration, DTs on top for curated multi-source aggregates.

Why is my Dynamic Table going to full refresh?

A Dynamic Table flips to full refresh when its SELECT contains a construct that isn't on the incremental refresh allow-list. The 2026 incremental surface covers projections, filters, equi-joins (INNER, most LEFT), GROUP BY, and reversible aggregates (SUM, COUNT, MIN, MAX, AVG). It does not cover window functions (ROW_NUMBER, LAG, LEAD, RANK), QUALIFY, DISTINCT, OUTER joins on non-equality conditions, most correlated subqueries, and many UDFs that read other tables. With REFRESH_MODE = 'AUTO', an unsupported construct causes silent fallback to FULL — the DDL succeeds, the DT runs, but every refresh costs O(source) instead of O(delta). Diagnose by querying INFORMATION_SCHEMA.DYNAMIC_TABLE_REFRESH_HISTORY() for refresh_action = 'FULL' and reading the DT body via GET_DDL. The fix is usually to split the non-incremental construct into a downstream view — keep the incremental aggregate in the DT, apply the window at query time. Pin REFRESH_MODE = 'INCREMENTAL' on critical-path DTs so future regressions error at create time instead of degrading silently.

How do I set TARGET_LAG correctly?

TARGET_LAG is a freshness ceiling, not a refresh cadence. Setting TARGET_LAG = '5 minutes' tells Snowflake "queries against this DT should never see data older than 5 minutes since the most recent committed source change"; the scheduler picks refresh times to honour that contract. The practical rule: set TARGET_LAG to roughly 3x your typical refresh duration. If the refresh takes 60 seconds, a 3-minute lag is comfortable; tighter than 2x leaves the scheduler no slack and you'll see lag breaches when refresh duration spikes. On a DT chain, remember that worst-case realised lag at the leaf is the sum of all upstream TARGET_LAGs — budget each layer at SLA / chain_depth, or use TARGET_LAG = DOWNSTREAM on intermediate DTs so they refresh only when the leaf needs them. Monitor realised lag via DYNAMIC_TABLE_GRAPH_HISTORY and alert on mean_lag_sec > target_lag_sec * 1.2 — that 20% slack catches lag drift before it turns into an SLA miss.

Dynamic Tables vs Streams+Tasks vs dbt — when does each win?

Use Dynamic Tables when the entire transformation can be expressed as a SELECT against the source, the SELECT lives on the incremental surface, and the freshness SLA is 1-15 minutes. The DDL is five lines and Snowflake owns the scheduler; the migration from Streams+Tasks usually cuts code volume by 5x. Use Streams+Tasks when the transformation needs imperative logic — calls into stored procedures, external functions, row-level branching, or sub-second SLAs that DTs can't hit. Streams+Tasks gives you fine-grained MERGE control and runs at whatever cron you set, at the cost of owning the entire DAG yourself. Use dbt incremental models when you want CI/CD, version control, Jinja macros, and the DAG visible outside the warehouse. dbt runs on its own scheduler, against any warehouse, and integrates with your git workflow; the cost is an extra orchestrator (dbt Cloud or your own). The 2026 reality is that most teams use all three: dbt for the build/test/deploy surface, DTs for declarative incremental aggregates inside the warehouse, and Streams+Tasks for the residual imperative work that nothing else replaces.

Can I cross-account share a Dynamic Table?

Yes — since 2025, you can add a Dynamic Table to a SHARE and have the consumer account read it through CREATE DATABASE FROM SHARE .... The refresh runs in the producer account on the producer's warehouse by default; the consumer reads the materialised result. Two modes matter. Producer-paid refresh is the default: the DT refreshes on the producer's schedule, paid by the producer, and the consumer reads the latest. Consumer-paid refresh uses TARGET_LAG = DOWNSTREAM on the producer DT: the refresh only happens when the consumer queries the DT (or a downstream DT depending on it), and the warehouse credits land on the consumer's account. Cross-account share is the cleanest way to publish a curated DT product to other Snowflake accounts (subsidiaries, customers, partner orgs) without copying data or running ETL across account boundaries. The trade-off is that the producer owns the SELECT body and freshness contract; the consumer only chooses how aggressively to pull. Schema changes need coordination — a CREATE OR REPLACE on a shared DT triggers a full rebuild and propagates to every consumer on their next query.

Practice on PipeCode

• Drill the SQL practice library → for the GROUP BY + aggregate surface that keeps DTs incremental.
• Rehearse on join problems → for the equi-join shapes that survive incremental refresh.
• Sharpen the aggregation library → for SUM/COUNT/MIN/MAX patterns that DT incremental supports natively.
• Stack the ETL practice drills → for the end-to-end pipeline-design probes interviewers ask after the DT walkthrough.
• For the broader surface, read the top data engineering interview questions →.
• Layer the prerequisites with the only 5 skills you need to become a data engineer →.
• For ETL system design specifically, work through the ETL system design course →.
• Sharpen the Spark axis with the Apache Spark internals course →.

Lock in Dynamic Tables muscle memory

Docs explain the DDL. PipeCode drills explain the decision — when MV beats DT, when full refresh hides a bug, when downstream lag breaks the SLA. Pipecode.ai is Leetcode for Data Engineering — pattern-first practice tuned for the production trade-offs senior data engineers actually face.

Practice SQL problems →
Practice ETL problems →