Sign Up
Back to changelog
New
•2 minute read

Snowflake: Interactive Tables

Atlas now manages Snowflake interactive tables, static or refreshed on a target_lag, with the new interactive_table block.

The new interactive_table block declares a Snowflake interactive table, which is created from an as query and clustered by cluster_by. Atlas inspects, diffs, and plans it with CREATE INTERACTIVE TABLE and ALTER INTERACTIVE TABLE. An interactive table is one of two kinds:

  • Static: without target_lag, the table holds a snapshot of the query result taken when it is created.
  • Dynamic: with target_lag, Snowflake refreshes the table from the query within that lag.

Example

The env enables tags, which the example assigns to both interactive tables:

atlas.hcl
env "local" {  url = "snowflake://user:password@account_identifier/DEMO_DB?warehouse=DEMO_WH"  dev = "snowflake://user:password@account_identifier/DEMO_DEV_DB?warehouse=DEMO_WH"  schema {    src = "file://schema/main.hcl"    mode "snowflake" {      tags = true    }  }}

The desired schema declares a source table, a static interactive table, and a dynamic one:

schema/main.hcl
schema "PUBLIC" {}
tag "DATA_DOMAIN" {  schema         = schema.PUBLIC  comment        = "Business domain that owns the data"  allowed_values = ["sales", "marketing", "finance"]}
table "ORDERS" {  schema  = schema.PUBLIC  comment = "Raw orders written by the ingestion pipeline"  column "ORDER_ID" {    type = NUMBER(38)  }  column "CUSTOMER_ID" {    type = NUMBER(38)  }  column "REGION" {    type = VARCHAR(16)  }  column "ORDER_DATE" {    type = DATE  }  column "AMOUNT" {    type = NUMBER(12,2)  }  primary_key {    columns = [column.ORDER_ID]  }}
# Static interactive tableinteractive_table "CUSTOMER_ORDER_LOOKUP" {  schema     = schema.PUBLIC  comment    = "Snapshot of orders for low-latency lookups by customer"  cluster_by = ["CUSTOMER_ID", "ORDER_DATE"]  as         = "SELECT ORDER_ID, CUSTOMER_ID, ORDER_DATE, AMOUNT FROM ORDERS"  depends_on = [table.ORDERS]  column "ORDER_ID" {}  column "CUSTOMER_ID" {    comment = "Customer who placed the order"  }  column "ORDER_DATE" {}  column "AMOUNT" {}  tag {    ref   = tag.DATA_DOMAIN    value = "sales"  }}
# Dynamic interactive tableinteractive_table "DAILY_REVENUE_BY_REGION" {  schema     = schema.PUBLIC  comment    = "Daily revenue per region, refreshed every 5 minutes"  cluster_by = ["REGION", "ORDER_DATE"]  target_lag = "5 minutes"  warehouse  = "DEMO_WH"  as         = "SELECT REGION, ORDER_DATE, SUM(AMOUNT) AS REVENUE, COUNT(*) AS ORDER_COUNT FROM ORDERS GROUP BY REGION, ORDER_DATE"  depends_on = [table.ORDERS]  column "REGION" {    comment = "Sales region"  }  column "ORDER_DATE" {}  column "REVENUE" {}  column "ORDER_COUNT" {}  tag {    ref   = tag.DATA_DOMAIN    value = "sales"  }}

Run the diff:

atlas migrate diff --env local

Atlas writes the migration:

atlas migrate diff
-- Create tag "DATA_DOMAIN"CREATE TAG "PUBLIC"."DATA_DOMAIN" ALLOWED_VALUES 'sales', 'marketing', 'finance' COMMENT = 'Business domain that owns the data';-- Create "ORDERS" tableCREATE TABLE "PUBLIC"."ORDERS" (  "ORDER_ID" NUMBER NOT NULL,  "CUSTOMER_ID" NUMBER NOT NULL,  "REGION" VARCHAR(16) NOT NULL,  "ORDER_DATE" DATE NOT NULL,  "AMOUNT" NUMBER(12, 2) NOT NULL,  CONSTRAINT "PK_ORDERS_ORDER_ID" PRIMARY KEY ("ORDER_ID")) DATA_RETENTION_TIME_IN_DAYS = 1 CHANGE_TRACKING = TRUE COMMENT = 'Raw orders written by the ingestion pipeline';-- Create "CUSTOMER_ORDER_LOOKUP" tableCREATE INTERACTIVE TABLE "PUBLIC"."CUSTOMER_ORDER_LOOKUP" CLUSTER BY (CUSTOMER_ID, ORDER_DATE) COMMENT = 'Snapshot of orders for low-latency lookups by customer' WITH TAG (PUBLIC.DATA_DOMAIN = 'sales') AS SELECT ORDER_ID, CUSTOMER_ID, ORDER_DATE, AMOUNT FROM ORDERS;-- Modify "CUSTOMER_ORDER_LOOKUP" table to set comment on column "CUSTOMER_ID"ALTER INTERACTIVE TABLE "PUBLIC"."CUSTOMER_ORDER_LOOKUP" ALTER COLUMN "CUSTOMER_ID" COMMENT 'Customer who placed the order';-- Create "DAILY_REVENUE_BY_REGION" tableCREATE INTERACTIVE TABLE "PUBLIC"."DAILY_REVENUE_BY_REGION" CLUSTER BY (REGION, ORDER_DATE) TARGET_LAG = '5 minutes' WAREHOUSE = 'DEMO_WH' COMMENT = 'Daily revenue per region, refreshed every 5 minutes' WITH TAG (PUBLIC.DATA_DOMAIN = 'sales') AS SELECT REGION, ORDER_DATE, SUM(AMOUNT) AS REVENUE, COUNT(*) AS ORDER_COUNT FROM ORDERS GROUP BY REGION, ORDER_DATE;-- Modify "DAILY_REVENUE_BY_REGION" table to set comment on column "REGION"ALTER INTERACTIVE TABLE "PUBLIC"."DAILY_REVENUE_BY_REGION" ALTER COLUMN "REGION" COMMENT 'Sales region';

Notes

  • Column type and null are informational only: the query defines them, and the columns are always nullable.
  • Declare the same columns the as query returns.
  • Snowflake does not keep the as query of a static interactive table (one without target_lag), so changing it is detected only when it adds, drops or renames a column.
  • Changes Snowflake cannot apply with ALTER (e.g., cluster_by, columns, or adding or removing target_lag) recreate the table. Recreating a table that was static re-snapshots its data, and its drop cannot be reversed.

Getting Started

Snowflake support is part of Atlas Pro. Run atlas login to get started.

See the interactive_table HCL reference for every attribute and block.

featuresnowflakeinteractive tabledeclarative
Back to changelog