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

  • text (str) - SQL query text

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

  • warehouse (ident) - warehouse used to refresh dynamic table

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

Links

PreviousDATABASENextEVENT TABLE

Last updated