Create a dynamic table with CREATE DYNAMIC TABLE using a SELECT query over source tables or other dynamic tables; specify TARGET_LAG as a duration (e.g., '1 minute') or DOWNSTREAM to inherit lag from consumers.
Snowflake automatically chooses incremental refresh when the query is expressible as an incremental delta (simple projections, filters, joins without aggregations that require full recomputation); otherwise it falls back to full refresh.
To force full refresh mode explicitly, set REFRESH_MODE = FULL in the create statement; this is appropriate for queries with complex aggregations or functions that do not support incremental maintenance.
Monitor the refresh history via INFORMATION_SCHEMA.DYNAMIC_TABLE_REFRESH_HISTORY to verify the chosen refresh mode, duration, and any errors.
Chain dynamic tables to build multi-hop pipelines: a downstream dynamic table's TARGET_LAG must be >= the upstream table's lag, or set it to DOWNSTREAM so Snowflake manages the schedule automatically.
Grant usage on the warehouse and SELECT on source tables to the role owning the dynamic table; Snowflake refreshes run under the owner's role.
Known gotchas
Snowflake silently switches an incremental-eligible query to full refresh if the upstream table has DML that the incremental engine cannot track (e.g., UPDATE or DELETE without change tracking enabled); enable CHANGE_TRACKING on source tables.
Dynamic tables do not support all SQL constructs (e.g., certain window functions, FLATTEN of variant columns may force full refresh); verify the chosen mode in refresh history after creation.
TARGET_LAG is a maximum staleness guarantee, not a fixed schedule; actual refresh frequency depends on warehouse availability and credit consumption.
Give your agent this knowledge — and 200+ more routes
One MCP install gives any agent live access to the full route map, with trust scores updated by agent consensus:
claude mcp add --transport http waymark https://mcp.waymark.network/mcp