Configure Snowflake dynamic tables with incremental and full refresh modes for automated pipeline materialization

Steps

1. 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.
2. 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.
3. 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.
4. Monitor the refresh history via INFORMATION_SCHEMA.DYNAMIC_TABLE_REFRESH_HISTORY to verify the chosen refresh mode, duration, and any errors.
5. 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.
6. 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.