> For the complete documentation index, see [llms.txt](https://docs.snowddl.com/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://docs.snowddl.com/basic/yaml-configs/dynamic-table.md).

# DYNAMIC TABLE

Config path: `/<database>/<schema>/dynamic_table/<name>.yaml`

Example:

```yaml
text: |-
  SELECT timezone
      , count(*) AS cnt
  FROM ${{ env_prefix }}snowddl_db.bookings.airports_data
  GROUP BY 1
 
target_lag: 1 hour
warehouse: test_wh

comment: Number of airports by timezone
```

## Schema

* **columns** (dict)
  * *{key}* (ident) - column name
  * *{value}* (str) - column comment
* <mark style="background-color:red;">**text**</mark> (str) - SQL query text
* **scheduler** (str) - ENABLE or DISABLE Snowflake scheduler (default: ENABLE)
* **target\_lag** (str) - "X seconds / minutes / hours / days" or "downstream"
* <mark style="background-color:red;">**warehouse**</mark> (ident) - warehouse used to refresh dynamic table
* **refresh\_mode** (str) - AUTO / FULL / INCREMENTAL
* **initialize** (str) - ON\_CREATE / ON\_SCHEDULE
* **cluster\_by** (list)
  * *{items}* (str) - SQL expressions for CLUSTER BY
* **is\_transient** (bool) - make table TRANSIENT
* **retention\_time** (int) - data retention time in days
* **depends\_on** (list)
  * *{items}* (ident) - names of other dynamic tables which this dynamic table depends on
* **comment** (str)

## Policy reference parameters

* **aggregation\_policy** (dict)
  * **policy\_name** (ident) - name of [AGGREGATION POLICY](/basic/yaml-configs/aggregation-policy.md)
  * **columns** (list)
    * *{items}* (ident) - optional reference column names defining "entity"
* **join\_policy** (dict)
  * **policy\_name** (ident) - name of [JOIN POLICY](/basic/yaml-configs/join-policy.md)
  * **columns** (list)
    * *{items}* (ident) - optional allowed join keys
* **masking\_policies** (list)
  * *{items}* (dict)
    * **policy\_name** (ident) - name of [MASKING POLICY](/basic/yaml-configs/masking-policy.md)
    * **columns** (list)
      * *{items}* (ident) - reference column names
* **projection\_policies** (list)
  * *{items}* (dict)
    * **policy\_name** (ident) - name of [PROJECTION POLICY](/basic/yaml-configs/projection-policy.md)
    * **column** (ident) - reference column name
* **row\_access\_policy** (dict)
  * **policy\_name** (ident) - name of [ROW ACCESS POLICY](/basic/yaml-configs/row-access-policy.md)
  * **columns** (list)
    * *{items}* (ident) - reference column names

## Usage notes

1. Only normal tables and event tables can be used in SQL query text. Views are not supported due to SnowDDL [object type execution order](/guides/object-types.md).
2. All tables referred by SQL query text should have change tracking enabled.
3. Schema objects should be referred using fully-qualified identifiers, with `${{ env_prefix }}` placeholder, database name, schema name and object name. It is currently required due to lack of scope during validation of SQL statement.
4. You may use [custom YAML tag](/basic/yaml-tag-include.md) `!include` to store SQL text in a separate file instead of storing it inside YAML.

## Additional privileges

Dynamic tables are executed with "schema owner role" privileges. If you want to access objects in other schemas or use a warehouse, make sure to specify additional owner grant parameters in [SCHEMA](/basic/yaml-configs/schema.md) config. For example:

* `owner_schema_read` - to read objects in other schemas;
* `owner_warehouse_usage` - to use a warehouse;
* `owner_integration` - to access objects in EXTERNAL STAGE linked to STORAGE INTEGRATION;

## Links

* [CREATE DYNAMIC TABLE](https://docs.snowflake.com/en/sql-reference/sql/create-dynamic-table)
* [SHOW DYNAMIC TABLES](https://docs.snowflake.com/en/sql-reference/sql/show-dynamic-tables)
* [Parser & JSON Schema (GitHub)](https://github.com/littleK0i/SnowDDL/blob/master/snowddl/parser/dynamic_table.py)