DYNAMIC TABLE

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

Example:

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

• text (str) - SQL query text

• target_lag (str) - "X seconds / minutes / hours / days" or "downstream"

• warehouse (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
  • columns (list)

    • {items} (ident) - optional reference column names defining "entity"

• masking_policies (list)

  • {items} (dict)

    • policy_name (ident) - name of MASKING POLICY
    • columns (list)

      • {items} (ident) - reference column names

• projection_policies (list)

  • {items} (dict)

    • policy_name (ident) - name of PROJECTION POLICY
    • column (ident) - reference column name

• row_access_policy (dict)

  • policy_name (ident) - name of ROW ACCESS POLICY
  • 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.
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 !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 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
• SHOW DYNAMIC TABLES
• Parser & JSON Schema (GitHub)