# 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.

> **AUTO resolves to FULL here**
>
> `refresh_mode` is `AUTO` for foundation and cohort DTs, but for CGDB query shapes AUTO resolves to **FULL** refresh — change tracking is not supported on a FULL-refresh DT without an `IMMUTABLE` constraint. Do not assume incremental refresh without confirming via the freshness queries below.

## 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.

> **Downstream cannot be faster than upstream**
>
> Snowflake forbids a downstream DT from having a smaller `target_lag` than the largest lag among its dependencies. The DEID DTs that depend on 1-day PHI foundation DTs must also be at 1 day.

## 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 run` or `ALTER DYNAMIC TABLE … REFRESH` returns “No new data”. This applies to rollbacks too — verify with `GET_DDL()`.
- **Views wrapping DTs cannot be DT sources.** Convert the wrapping view to a Dynamic Table.
- **The `_fivetran_deleted` filter is mandatory.** A missing filter once produced a 380K-row leak that cascaded to 111 downstream models.

## How to check freshness

```sql
-- When did a specific DT last refresh?
SELECT name, refresh_version, data_timestamp
FROM TABLE(INFORMATION_SCHEMA.DYNAMIC_TABLE_REFRESH_HISTORY())
WHERE name = 'BASE_RENASIGHT'
ORDER BY data_timestamp DESC
LIMIT 5;

-- Find suspended DTs (a SUSPENDED DT in PROD is high-severity)
SELECT name, schema_name, scheduling_state, last_suspended_on
FROM INFORMATION_SCHEMA.DYNAMIC_TABLES
WHERE scheduling_state = 'SUSPENDED'
ORDER BY last_suspended_on DESC;
```