# 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
* <mark style="background-color:red;">**target\_lag**</mark> (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)


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://docs.snowddl.com/basic/yaml-configs/dynamic-table.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.