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


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

comment: Number of airports by timezone


  • 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)

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.

Last updated