Snowflake Dynamic Tables — Expert Interview Questions

Q: What are Dynamic Tables and how do they fundamentally differ from materialized views and streams/tasks?

Dynamic Tables are declaratively defined transformations: you write a SELECT query, set a TARGET_LAG (e.g., '5 minutes'), and Snowflake automatically manages refreshes to keep results within that lag. Unlike materialized views: DTs support complex queries (joins, aggregations, CTEs, multiple source tables), window functions, and chaining (DT reading from another DT). Unlike streams/tasks: you don't write imperative MERGE/INSERT logic — Snowflake figures out the incremental refresh strategy automatically. DTs use the same micro-partition-level change tracking as streams but abstract away the complexity. They're essentially "dbt models that auto-refresh themselves."

Q: Explain TARGET_LAG semantics. What does "5 minutes" actually guarantee?

TARGET_LAG = '5 minutes' means the data in the dynamic table will be at most 5 minutes behind the source tables. It's an upper bound, not a schedule. Snowflake's scheduler determines the optimal refresh frequency based on: (1) how fast source data changes, (2) the cost of refreshing, (3) the configured lag. If source data hasn't changed, no refresh runs. If source changes frequently, Snowflake may refresh more often than every 5 minutes. DOWNSTREAM means "inherit the lag from the farthest downstream consumer" — useful for chained DTs where only the final table has a business SLA. Important: TARGET_LAG is best-effort — under heavy system load, actual lag may temporarily exceed the target.

Q: How does Snowflake determine whether to do a full refresh or incremental refresh on a Dynamic Table?

Snowflake automatically analyzes the DT's query to determine if incremental refresh is possible. Incremental refresh works when: the query involves simple filters, joins, aggregations, UNION ALL, and projections that can be decomposed into delta operations. Full refresh is required when: the query uses non-deterministic functions (RANDOM(), CURRENT_TIMESTAMP), certain complex window functions, LIMIT/OFFSET, or recursive CTEs. You can check the refresh mode with: SELECT refresh_mode FROM INFORMATION_SCHEMA.DYNAMIC_TABLES. If a DT falls back to full refresh, it recomputes the entire result set — this is critical for cost planning on large tables.

Q: You have a 3-layer DT pipeline: bronze_dt → silver_dt → gold_dt. How does lag propagate?

If gold_dt has TARGET_LAG = '10 minutes', and silver_dt and bronze_dt both have TARGET_LAG = DOWNSTREAM, then Snowflake works backwards: gold needs data within 10 min, so silver must refresh often enough that gold can meet its SLA, and bronze must refresh often enough for silver. The system optimizes the entire chain holistically — it doesn't naively set each layer to 10/3 minutes. If you set explicit lags (bronze=2min, silver=5min, gold=10min), the actual end-to-end lag could be up to 17 minutes (sum of worst cases). For predictable end-to-end lag, use DOWNSTREAM on intermediate layers and set the target only on the final consumer-facing table.

Q: What happens to a Dynamic Table when you ALTER TABLE ADD COLUMN on one of its source tables?

If the DT query uses SELECT * from the source, the new column automatically appears after the next refresh. If the DT uses explicit column names, the new column is ignored (no impact). If you DROP a column that the DT references, the next refresh fails and the DT enters an error state. You can check DT health with SHOW DYNAMIC TABLES or DYNAMIC_TABLE_REFRESH_HISTORY(). To fix: ALTER the DT query to remove the dropped column reference. Critical: unlike regular tables, you cannot ALTER a DT's columns directly — you must change the underlying SELECT query using ALTER DYNAMIC TABLE ... SET QUERY = '...'.

Q: When would you choose Dynamic Tables over dbt + Airflow? When would you NOT?

Choose DTs when: (1) you need continuous/near-real-time refresh without managing orchestration, (2) the pipeline is a straightforward chain of SQL transformations, (3) your team doesn't have Airflow/dbt expertise. Choose dbt + Airflow when: (1) you need complex branching logic, conditional execution, or cross-system dependencies, (2) you need source freshness checks, test-after-build, and documentation, (3) you need precise control over execution order and retry logic, (4) transformations involve non-SQL logic (Python, APIs), (5) you want CI/CD with slim builds and manifest diffs. Many teams use both: DTs for simple real-time aggregations, dbt for complex analytical models.

Q: How do you monitor and troubleshoot Dynamic Table refresh failures?

Key tools: (1) DYNAMIC_TABLE_REFRESH_HISTORY() — shows each refresh attempt, status, duration, rows inserted/deleted, and error messages. (2) DYNAMIC_TABLE_GRAPH_HISTORY() — shows the entire DT pipeline execution. (3) SHOW DYNAMIC TABLES — shows current state, last refresh, scheduling status. (4) ALERT objects — create alerts on DT refresh failures. For troubleshooting: check if refresh_mode changed from INCREMENTAL to FULL (query complexity issue), check data_timestamp vs current time for lag violations, and correlate refresh failures with QUERY_HISTORY for the underlying SQL error. Common failure: source table recreation (CREATE OR REPLACE) breaks the DT — use ALTER TABLE instead.

Q: A Dynamic Table gold_summary with 500M rows is refreshing every 2 minutes via full refresh, costing $X/hour. How do you optimize?

(1) Investigate why it's full refresh — check refresh_mode. Common causes: non-deterministic functions, complex window functions, LIMIT. Rewrite the query to be incremental-compatible. (2) Increase TARGET_LAG if the business allows — 10 minutes instead of 2 reduces refresh frequency by 5x. (3) If the DT aggregates a large table, consider a two-layer approach: a narrow DT that filters first (incremental-friendly), then the aggregation DT on top. (4) Check if a materialized view works for your use case (single-table, simpler query) — MVs are more cost-efficient for simple aggregations. (5) Add CLUSTER BY on the source table to improve pruning during refresh. (6) Use a dedicated warehouse (WAREHOUSE = wh) sized appropriately for the refresh workload.

Can Dynamic Tables replace streams and tasks entirely?

For SQL-only CDC pipelines (source → transform → target), yes — DTs are simpler and less error-prone. However, tasks can execute stored procedures (Python, JavaScript), call external functions, and have complex conditional logic (WHEN clauses). Streams + tasks remain necessary for: non-SQL logic, calling external APIs, complex branching, and pipelines requiring explicit transaction control.

What is the minimum TARGET_LAG for a Dynamic Table?

The minimum TARGET_LAG is 1 minute (or DOWNSTREAM to inherit from consumers). There's no sub-minute option — for lower latency, use Snowpipe Streaming or Kafka. In practice, most production DTs use 5-15 minute lag as the sweet spot between freshness and cost.