CREATE INTERACTIVE TABLE¶

Syntax¶

CREATE [ OR REPLACE ] INTERACTIVE TABLE [ IF NOT EXISTS ] <table_name>
  CLUSTER BY ( <expr> [ , <expr> , ... ] )
  [ TARGET_LAG = '<num> { seconds | minutes | hours | days }' ]
  [ WAREHOUSE = <warehouse_name> ]
  [ COPY GRANTS ]
  [ COMMENT = '<string_literal>' ]
  AS <query>

Required parameters¶

table_name

Specifies the identifier (i.e. name) for the interactive table; must be unique for the schema in which the table is created.

In addition, the identifier must start with an alphabetic character and cannot contain spaces or special characters unless the entire identifier string is enclosed in double quotes (e.g. "My object"). Identifiers enclosed in double quotes are also case-sensitive.

For more details, see Identifier requirements.

CLUSTER BY ( expr [ , expr , ... ] )

Required. Specifies one or more columns or column expressions in the table as the clustering key. Choose clustering columns that are used in the WHERE clauses of your most time-critical queries, as this significantly affects query performance.

For more details about choosing effective clustering keys, see Clustering Keys & Clustered Tables.

AS query

Required. Specifies the SELECT statement that populates the interactive table. This query must be specified last in the CREATE INTERACTIVE TABLE statement, regardless of other parameters included.

The query follows CREATE TABLE AS SELECT (CTAS) patterns and defines the data and schema for the interactive table.

Optional parameters¶

OR REPLACE

Specifies to replace the interactive table if it already exists in the schema. This is equivalent to using DROP TABLE on the existing table and then creating a new table with the same name.

IF NOT EXISTS

Specifies to create the interactive table only if it does not already exist in the schema. If a table with the same name already exists, the statement succeeds without creating a new table.

Note

The OR REPLACE and IF NOT EXISTS clauses are mutually exclusive and cannot both be used in the same statement.

TARGET_LAG = 'num { seconds | minutes | hours | days }'

Specifies the maximum lag time for automatic refresh of the interactive table. When specified, the interactive table becomes a dynamic interactive table that automatically refreshes to stay within the specified lag time of the source data.

• The minimum value is 60 seconds (1 minute).

• If no unit is specified, the number represents seconds.

• If TARGET_LAG is not specified, the table is created as a static interactive table.

When TARGET_LAG is specified, the WAREHOUSE parameter is also required.

WAREHOUSE = warehouse_name

Required when TARGET_LAG is specified. Specifies the standard warehouse used for refresh operations when TARGET_LAG is set. This must be a standard warehouse, not an interactive warehouse.

COPY GRANTS

Specifies to retain the access privileges from the original table when replacing an interactive table using CREATE OR REPLACE INTERACTIVE TABLE.

The parameter copies all privileges, except OWNERSHIP, from the existing table to the new table. By default, the role that executes the CREATE INTERACTIVE TABLE statement owns the new table.

COMMENT = 'string_literal'

Specifies a comment for the interactive table.

Access control requirements¶

A role used to execute this operation must have the following privileges at a minimum:

For instructions on creating a custom role with a specified set of privileges, see Creating custom roles.

For general information about roles and privilege grants for performing SQL actions on securable objects, see Overview of Access Control.

Usage notes¶

• Interactive tables must be created using a standard warehouse, not an interactive warehouse.

• The CLUSTER BY clause is required for all interactive tables and significantly affects query performance. Choose clustering columns carefully based on your most common WHERE clause patterns.

• Interactive tables can only be queried through interactive warehouses. To query an interactive table, you must:

  1. Create an interactive warehouse

  2. Associate the interactive table with the interactive warehouse using ALTER WAREHOUSE … ADD TABLES

  3. Resume the interactive warehouse

  4. Use the interactive warehouse to query the interactive table

• Interactive tables support a limited set of SQL operations compared to standard tables:

  • SELECT statements with WHERE clauses are optimized

  • Simple GROUP BY operations are supported

  • JOIN operations are currently not supported

  • DML operations (INSERT, UPDATE, DELETE) are not supported

  • Complex query operations may have limited performance benefits

• Dynamic interactive tables (with TARGET_LAG) automatically refresh using the specified standard warehouse. The lag time balances data freshness with compute costs.

• Static interactive tables require manual refresh to update with new data from source tables.

• Interactive tables store additional metadata and index information to accelerate queries, but this is compressed and has minimal impact on storage size.

Variant syntax: Static interactive table¶

Creates a static interactive table that is populated once from the source query:

CREATE [ OR REPLACE ] INTERACTIVE TABLE <table_name>
  CLUSTER BY ( <expr> [ , <expr> , ... ] )
  [ COPY GRANTS ]
  [ COMMENT = '<string_literal>' ]
  AS <query>

Static interactive tables do not automatically refresh and require manual updates to reflect changes in source data.

Variant syntax: Dynamic interactive table¶

Creates a dynamic interactive table that automatically refreshes based on the specified lag time:

CREATE [ OR REPLACE ] INTERACTIVE TABLE <table_name>
  CLUSTER BY ( <expr> [ , <expr> , ... ] )
  TARGET_LAG = '<num> { seconds | minutes | hours | days }'
  WAREHOUSE = <warehouse_name>
  [ COPY GRANTS ]
  [ COMMENT = '<string_literal>' ]
  AS <query>

Dynamic interactive tables automatically refresh to stay within the specified TARGET_LAG of the source data, using the specified standard warehouse for refresh operations.

Examples¶

The following examples show different ways that you can create interactive tables, along with specifying the source of their data and how to refresh the data.

CREATE INTERACTIVE TABLE orders_interactive
  CLUSTER BY (customer_id, order_date)
  COMMENT = 'Interactive table for real-time order analytics'
AS
  SELECT customer_id, order_date, product_id, quantity, total_amount
  FROM orders_staging
  WHERE order_date >= '2024-01-01';

CREATE INTERACTIVE TABLE sales_summary_interactive
  CLUSTER BY (region, product_category)
  TARGET_LAG = '5 minutes'
  WAREHOUSE = refresh_warehouse
  COMMENT = 'Real-time sales dashboard data'
AS
  SELECT
    region,
    product_category,
    SUM(sales_amount) as total_sales,
    COUNT(*) as transaction_count,
    AVG(sales_amount) as avg_sale
  FROM sales_data
  GROUP BY region, product_category;

CREATE INTERACTIVE TABLE customer_analytics_interactive
  CLUSTER BY (customer_tier, region, signup_date)
  TARGET_LAG = '10 minutes'
  WAREHOUSE = analytics_warehouse
AS
  SELECT
    customer_id,
    customer_tier,
    region,
    signup_date,
    total_orders,
    lifetime_value,
    last_order_date
  FROM customer_metrics
  WHERE customer_tier IN ('GOLD', 'PLATINUM', 'DIAMOND');

CREATE OR REPLACE INTERACTIVE TABLE product_performance_interactive
  CLUSTER BY (category, brand, launch_date)
  TARGET_LAG = '2 minutes'
  WAREHOUSE = fast_refresh_warehouse
  COPY GRANTS
AS
  SELECT
    product_id,
    category,
    brand,
    launch_date,
    units_sold,
    revenue,
    customer_rating
  FROM product_sales_view
  WHERE launch_date >= DATEADD('month', -6, CURRENT_DATE());