Design & Architecture
Dynamic Tables & Refresh Behavior
A Dynamic Table is Snowflake’s declarative incremental-materialization object: write a SELECT, set a target lag, and Snowflake keeps it in sync with its sources — no orchestrator. The dbt project builds the foundation and mart layer this way. The refresh behavior has sharp edges.
What Dynamic Tables are
Advantages over a hand-orchestrated ETL job:
- No orchestration — Snowflake schedules the refresh against the target lag.
- Declarative — you define what the data should look like, not how to update it.
- Chain-aware — downstream DTs refresh after upstream dependencies settle.
Configuration
| Setting | Value | Notes |
|---|---|---|
| Target lag | 24 hours | Maximum staleness before Snowflake triggers a refresh. |
| Refresh mode | AUTO | Snowflake picks the strategy; resolves to FULL for CGDB query shapes. DEID overrides to FULL explicitly. |
| Managed by | materialized='dynamic_table' | Inherited from dbt_project.yml directory config. Models must not override. |
| Clustering | Per-model | Set in each model’s config(), usually cluster_by=['casefile_id']. |
The refresh chain
When source data changes, refreshes cascade automatically down the chain. Fivetran continuously replicates MySQL changes to the source share with change tracking; staging views resolve at query time; foundation, cohort, and dashboard DTs refresh against their target lag.
Refresh behaviors to know
- Schema changes cause auto-suspension. When upstream source tables change schema, staging views with
SELECT *become stale and downstream DTs auto-suspend. Fix:dbt run -s affected_staging_model+. - Adding columns or changing query structure requires a full refresh. A plain
dbt runorALTER DYNAMIC TABLE … REFRESHreturns “No new data”. This applies to rollbacks too — verify withGET_DDL(). - Views wrapping DTs cannot be DT sources. Convert the wrapping view to a Dynamic Table.
- The
_fivetran_deletedfilter is mandatory. A missing filter once produced a 380K-row leak that cascaded to 111 downstream models.
How to check freshness
-- When did a specific DT last refresh?SELECT name, refresh_version, data_timestampFROM TABLE(INFORMATION_SCHEMA.DYNAMIC_TABLE_REFRESH_HISTORY())WHERE name = 'BASE_RENASIGHT'ORDER BY data_timestamp DESCLIMIT 5;
-- Find suspended DTs (a SUSPENDED DT in PROD is high-severity)SELECT name, schema_name, scheduling_state, last_suspended_onFROM INFORMATION_SCHEMA.DYNAMIC_TABLESWHERE scheduling_state = 'SUSPENDED'ORDER BY last_suspended_on DESC;