I tried out the fact that the SCHEDULER attribute of Dynamic Table can now be natively controlled with the scheduler config in dbt-snowflake

This is Kawabata.

Previously, controlling the SCHEDULER attribute of Dynamic Tables in dbt required a workaround using pre_hook/post_hook, but since the dbt-snowflake adapter now natively supports the scheduler config key, I decided to actually verify this.

https://docs.snowflake.com/en/user-guide/dynamic-tables/dbt

https://docs.getdbt.com/reference/resource-configs/snowflake-configs?version=2.0&name=Fusion#dynamic-tables

【Update】
I was selected as a finalist in the "RISING COMMUNITY LEADER OF THE YEAR" category, APJ region, at the Snowflake Community Awards.
Please see the link below for details.

https://dev.classmethod.jp/articles/snowflake-community-awards-finalist-activities-review/

Background & Challenges

Previously, I wrote an article titled Trying Out the SCHEDULER Attribute of Dynamic Tables. Setting SCHEDULER = DISABLE stops Snowflake's automatic scheduler from performing TARGET_LAG-based auto-refresh, so the table won't be refreshed until an external orchestrator like dbt or Airflow explicitly calls ALTER DYNAMIC TABLE ... REFRESH.

-- Raw SQL example with SCHEDULER = DISABLE
CREATE OR REPLACE DYNAMIC TABLE KAWABATA_MART_DB.PUBLIC.DT_ORDERS_SCHEDULER_DISABLED
  WAREHOUSE = COMPUTE_WH
  REFRESH_MODE = FULL
  INITIALIZE = ON_CREATE
  SCHEDULER = DISABLE
  AS
    SELECT o.ID AS ORDER_ID
    FROM KAWABATA_MART_DB.KAWABATA_RAW.RAW_ORDERS o;

However, as of the time the article was written on March 31, 2026, the dbt-snowflake adapter (v1.9.2) did not natively support the SCHEDULER attribute, and target_lag remained mandatory, causing ALTER statement conflicts when running dbt run directly. At the time, the workaround was to use pre_hook to temporarily set SCHEDULER = ENABLE, then post_hook to manually refresh and then switch back to SCHEDULER = DISABLE.

-- models/intermediate/int_order_items_dynamic_table.sql (workaround from the old article)
{{
    config(
        materialized='dynamic_table',
        snowflake_warehouse='COMPUTE_WH',
        target_lag='DOWNSTREAM',
        pre_hook=[
            "ALTER DYNAMIC TABLE IF EXISTS {{ this }} SET SCHEDULER = ENABLE TARGET_LAG = DOWNSTREAM"
        ],
        post_hook=[
            "ALTER DYNAMIC TABLE {{ this }} REFRESH",
            "ALTER DYNAMIC TABLE {{ this }} SET SCHEDULER = DISABLE"
        ]
    )
}}
SELECT
    i.ITEM_ID,
    i.ORDER_ID
FROM {{ ref('stg_items') }} i
JOIN {{ ref('stg_orders') }} o
  ON i.ORDER_ID = o.ORDER_ID;

Since this configuration forcibly rewrote the SCHEDULER attribute via hooks, 2–3 extra ALTER statements were issued on every dbt run, and if the hook execution order was wrong, unintended refreshes could occur, making it difficult to manage. The previous article also concluded that "the right approach is to wait for official dbt adapter support," so I was curious to see if support had actually been added.

Technical Approach

After checking the official Snowflake documentation, I confirmed that the scheduler key has been officially added to the materialized='dynamic_table' config, allowing ENABLE/DISABLE to be specified directly without hooks.

-- models/marts/dt_orders_scheduler_demo.sql (new syntax)
{{ config(
    materialized='dynamic_table',
    snowflake_warehouse='COMPUTE_WH',
    refresh_mode='FULL',
    scheduler='DISABLE'
) }}

SELECT
    o.ID AS ORDER_ID,
    o.CUSTOMER AS CUSTOMER_ID
FROM {{ source('ecom', 'raw_orders') }} o

In dbt Dynamic Table models, omitting target_lag or specifying scheduler: DISABLE switches to a dbt/external orchestrator-managed refresh approach that does not use Snowflake's automatic scheduling. When SCHEDULER = DISABLE, TARGET_LAG cannot be defined, so target_lag should be removed from the config.

When dbt runs a Dynamic Table model, it creates or alters the Dynamic Table and also performs an explicit refresh via ALTER DYNAMIC TABLE ... REFRESH. Note that while the official documentation states that subsequent dbt run executions will skip the model if the SQL is unchanged, in my verification environment, the refresh was executed even on dbt run without SQL changes (confirmed in the delete propagation verification later). The attribute switching that was previously self-managed via pre_hook/post_hook is now replaced by a single line in the config.

Limitations

Prerequisites

• dbt-snowflake adapter: v1.11.5 or later (minimum requirement for using scheduler config)
• Verification environment: dbt Core 1.11.11, as of July 24, 2026. Execution command: dbt run --select dt_orders_scheduler_demo
• Snowflake: Continuing to use the separately built jaffle-shop environment (JAFFLE_SHOP_DB's DEV/PROD/RAW schemas, JAFFLE_SHOP_WH, DBT_CICD_ROLE) as-is

Preparation

Set CHANGE_TRACKING on the Base Table

-- RAW_ORDERS is created by dbt (seed), so run this with the role that holds OWNERSHIP

ALTER TABLE JAFFLE_SHOP_DB.RAW.RAW_ORDERS SET CHANGE_TRACKING = TRUE;

Note: ALTER TABLE ... SET CHANGE_TRACKING = TRUE requires OWNERSHIP of the target table. In the jaffle-shop environment, RAW.RAW_ORDERS is a table created by dbt's seed under DBT_CICD_ROLE, so run this with that role (or a higher role in the role hierarchy).

Add the dynamic_table model file

Create models/marts/dt_orders_scheduler_demo.sql as a new file and place the model there.

Trying It Out

Run and Verify

Run dbt run with the config set to scheduler='DISABLE'.

dbt run --select dt_orders_scheduler_demo

In dbt run, in addition to the SQL for Dynamic Table configuration changes, ALTER DYNAMIC TABLE ... REFRESH is also issued.

Additionally, use SHOW DYNAMIC TABLES to confirm that the scheduler column shows DISABLE and target_lag is NULL.

SHOW DYNAMIC TABLES LIKE 'DT_ORDERS_SCHEDULER_DEMO' IN DATABASE JAFFLE_SHOP_DB;

Comparing the behavior of ENABLE and DISABLE gives the following:

It was confirmed that switching between Snowflake-managed automatic operation via target_lag and dbt/external orchestrator-managed operation can be done with just a single line change in the config.

Verifying Whether Deletions from the Source Are Reflected

A concern with the scheduler: DISABLE setup is how data changes on the base table side—especially row deletions—are reflected in the Dynamic Table. With dbt incremental models, techniques like delete+insert are needed to propagate deletions, but since a Dynamic Table is an object that declaratively maintains the results of a query SQL, deletions on the source side should be reflected in the query results once a refresh completes successfully.

Another thing to verify is whether a dbt run without SQL changes executes a refresh. The official documentation's description of dbt-managed refresh states that each dbt run creates or alters the Dynamic Table and then issues ALTER DYNAMIC TABLE ... REFRESH, while the model example section also states "Subsequent runs detect if the SQL is unchanged and skip the model entirely." — the behavior described within the same page diverges. Note that "SQL is unchanged" here refers to the dbt model's SELECT statement and config being unchanged, and has nothing to do with data changes in source tables. Let's verify which behavior actually occurs.

First, confirm the row count and target rows before deletion.

-- Check state before deletion
SELECT COUNT(*) AS CNT FROM JAFFLE_SHOP_DB.RAW.RAW_ORDERS;
SELECT COUNT(*) AS CNT FROM JAFFLE_SHOP_DB.DEV.DT_ORDERS_SCHEDULER_DEMO;

DELETE rows from the base table.

-- Delete rows from the source side
DELETE FROM JAFFLE_SHOP_DB.RAW.RAW_ORDERS
WHERE ID IN (
    SELECT ID FROM JAFFLE_SHOP_DB.RAW.RAW_ORDERS LIMIT 10
);

Note: What is being deleted here is the source data loaded by seed. After the verification, it can be restored by re-running the seed command (with --vars '{load_source_data: true}').

At this point, checking the Dynamic Table side shows that since scheduler: DISABLE is set, no automatic refresh has run by Snowflake, and the count remains as it was before deletion.

-- Immediately after DELETE: not yet reflected in the Dynamic Table
SELECT COUNT(*) AS CNT FROM JAFFLE_SHOP_DB.DEV.DT_ORDERS_SCHEDULER_DEMO;

Next, run dbt run without changing the model SQL.

dbt run --select dt_orders_scheduler_demo

-- After dbt run without SQL changes: deletion is reflected
SELECT COUNT(*) AS CNT FROM JAFFLE_SHOP_DB.DEV.DT_ORDERS_SCHEDULER_DEMO;

As a result of the verification, the refresh was executed even on a dbt run without SQL changes, and the deletion from the source was reflected in the Dynamic Table (observed with dbt Core 1.11.11 as of July 24, 2026). Contrary to the model example section's statement that "the model is skipped if SQL is unchanged," the behavior was consistent with the dbt-managed refresh description (issuing REFRESH on each dbt run).

Checking the SQL issued in Query History, this dbt run issued alter dynamic table ... refresh against the existing Dynamic Table, and no CREATE OR REPLACE recreation was performed. This confirms that for models with unchanged SQL, dbt executes only the refresh.

If you want to reflect changes without going through dbt, you can explicitly trigger a refresh from an orchestrator such as Airflow or Snowflake Tasks.

-- Explicit refresh (when running directly from an orchestrator)
ALTER DYNAMIC TABLE JAFFLE_SHOP_DB.DEV.DT_ORDERS_SCHEDULER_DEMO REFRESH;

Note on permissions: Executing ALTER DYNAMIC TABLE ... REFRESH requires OPERATE (or OWNERSHIP) privilege on the target Dynamic Table. For roles dedicated to routine refreshes, consider a least-privilege design using OPERATE rather than granting OWNERSHIP more than necessary.

The behavior before and after deletion is summarized as follows:

Dynamic Tables naturally reflect source-side deletions in query results once a refresh is executed. There is no need to write your own deletion propagation logic as with incremental models. With scheduler: DISABLE, since Snowflake's automatic refresh does not run, only dbt run or an explicit ALTER DYNAMIC TABLE ... REFRESH serves as the refresh trigger — making it easy to confirm the control behavior.

Note: Since the official documentation still contains a note about models being skipped when SQL is unchanged, the behavior may vary depending on the dbt or adapter version. When incorporating this into production operations, it is recommended to verify REFRESH issuance in your own environment's execution logs.

Note: This verification was conducted with refresh_mode='FULL'. For deletion reflection with INCREMENTAL, the prerequisite is that the Dynamic Table's query is eligible for incremental refresh and that change tracking information from upstream CHANGE_TRACKING is available.

Comparison with the Old Workaround

Placing the hook-based implementation from the old article side by side with the new scheduler config specification makes the difference in simplicity clear.

Before (pre_hook/post_hook workaround)

pre_hook=[
    "ALTER DYNAMIC TABLE IF EXISTS {{ this }} SET SCHEDULER = ENABLE TARGET_LAG = DOWNSTREAM"
],
post_hook=[
    "ALTER DYNAMIC TABLE {{ this }} REFRESH",
    "ALTER DYNAMIC TABLE {{ this }} SET SCHEDULER = DISABLE"
]

After (scheduler config)

scheduler='DISABLE'

The implementation that toggled the SCHEDULER attribute back and forth via hooks has been replaced by the single config line scheduler='DISABLE'. Unnecessary state transitions and dependency on execution order caused by hooks are eliminated, improving the maintainability of the definition.

Note: In the current Snowflake, SCHEDULER = DISABLE and TARGET_LAG cannot be used together. The hook-based implementation from the old article (a configuration that switches to SCHEDULER = DISABLE while TARGET_LAG = DOWNSTREAM is still set) was published as verification results from that time, and adopting this configuration newly is not recommended. When using scheduler: DISABLE, please remove target_lag from the config.

Closing Thoughts

I verified the SCHEDULER attribute of Dynamic Tables using dbt's scheduler config. Here is a summary of the issues at the time of the old article and the current state:

As I wrote in the old article, "the right approach is to wait for official support" — and the wait paid off. Since there is no longer a need to forcibly rewrite attributes via hooks, using the scheduler config directly is the recommended approach when managing Dynamic Tables under dbt.

Note that when a SCHEDULER = DISABLE Dynamic Table is manually refreshed on its own, the refresh target is only that Dynamic Table and does not cascade to upstream or downstream Dynamic Tables (with SCHEDULER = ENABLE, a manual refresh cascades upstream, but stops at any table with SCHEDULER = DISABLE). Therefore, when operating a group of Dynamic Tables with dependencies under scheduler: DISABLE, the execution order, retry on failure, and monitoring must be designed on the orchestrator side using dbt, Airflow, or similar tools. Please choose the approach that suits your requirements.

https://docs.snowflake.com/en/user-guide/dynamic-tables/dbt

I hope this article is helpful in some way!