Snowflake interactive tables and interactive warehouses¶

Limitations of interactive warehouses¶

• Snowflake interactive warehouses don’t support long-running queries. The query timeout for SELECT commands defaults to five seconds. After five seconds, the query is canceled. You can reduce the query timeout value but you can’t increase it. Other kinds of commands, such as SHOW and INSERT OVERWRITE, aren’t subject to the five-second timeout interval.

  Interactive warehouses aren’t intended for use with long-running queries. If a query consistently times out, that’s a signal that it might not be suitable for use with interactive warehouses. Otherwise, you need to apply some of the performance tuning techniques to reduce the time to less than five seconds.

• An interactive warehouse is always up and running by design. It doesn’t automatically suspend when idle. Although you can manually suspend an interactive warehouse, expect significant query latency when you resume the warehouse.

• You can’t query standard Snowflake tables from an interactive warehouse. To query both standard tables and interactive tables in the same session, run USE WAREHOUSE to switch to the appropriate warehouse type depending on the type of table.

• If an interactive warehouse is a multi-cluster warehouse, it doesn’t auto-scale. In a multi-cluster interactive warehouse, always keep MIN_CLUSTER_COUNT and MAX_CLUSTER_COUNT set to the same value.

• You can’t run CALL commands to call stored procedures in an interactive warehouse.

• You can’t use the ->> pipe operator. This operator uses stored procedures behind the scenes.

• Interactive warehouses currently don’t support replication. They aren’t included in failover groups and replication groups.

Limitations of interactive tables¶

• Interactive tables don’t support the following features:

  • Data manipulation language (DML) commands such as UPDATE and DELETE. The only DML that you can perform is INSERT OVERWRITE.

  • Replication. They aren’t included in failover groups and replication groups.

• Query insights are currently not collected or available for queries executing on interactive tables.

• You can’t perform the following operations:

  • Use an interactive table as the source for a materialized view.

  • Modify properties of an interactive table by using ALTER TABLE clauses such as ADD COLUMN or REMOVE COLUMN. The only ALTER TABLE change that you can make is to rename the table.

  • Use data masking policies with an interactive table.

  • Use join policies with an interactive table.

  • Use aggregation policies with an interactive table.

  • Use row access policies with an interactive table.

  • Use streams with an interactive table.

  • Create a dynamic table with an interactive table as a base table.

  • Use the RESAMPLE clause for queries on an interactive table.

Creating an interactive table¶

Table creation follows the standard CTAS (CREATE TABLE AS SELECT) syntax, with the additional INTERACTIVE keyword that defines the table type.

The CREATE INTERACTIVE TABLE command also requires a CLUSTER BY clause. Specify one or more columns in the CLUSTER BY clause to match the WHERE clauses in your most time-critical queries. The columns you specify in the CLUSTER BY clause can significantly affect the performance of queries on the interactive table. Therefore, choose the clustering columns carefully. For more information about choosing the best clustering columns, see Clustering Keys & Clustered Tables.

The following command creates an interactive table containing the same columns and data as a standard table. The CLUSTER BY clause refers to a column named id from the source table.

CREATE INTERACTIVE TABLE
  IF NOT EXISTS orders
  CLUSTER BY (id)
AS
  SELECT * FROM demoSource;

Specifying auto-refresh for an interactive table¶

To make an interactive table automatically refresh using data from some other table, specify the TARGET_LAG clause with an interval. When you specify TARGET_LAG, you must also specify the WAREHOUSE clause and the name of a standard warehouse that Snowflake will use to perform refresh operations.

The time interval for the TARGET_LAG clause lets you specify the maximum lag in terms of some number of seconds, minutes, hours, or days:

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

If you don’t specify a unit, the number represents seconds. The minimum value is 60 seconds, or 1 minute.

For example, the following CREATE INTERACTIVE TABLE statement defines a dynamic interactive table that lags no more than 20 minutes behind a specified source table, and uses a standard warehouse named my_standard_warehouse to perform refresh operations:

CREATE INTERACTIVE TABLE my_dynamic_interactive_table
  CLUSTER BY (c1, c2)
  TARGET_LAG = '20 minutes'
  WAREHOUSE = my_standard_warehouse
AS SELECT c1, SUM(c2) FROM my_source_table GROUP BY c1;

For more information about choosing an appropriate lag time that balances costs and freshness of data, see Determine the optimal target lag for a dynamic table. Similar considerations apply to interactive tables as to dynamic tables.

Creating an interactive warehouse¶

After you create an interactive table, querying that table requires an interactive warehouse. Specify the keyword INTERACTIVE in the CREATE WAREHOUSE or CREATE OR REPLACE WAREHOUSE command.

Optionally, you can specify a TABLES clause with a comma-separated list of interactive table names. Using that clause immediately associates those interactive tables with the interactive warehouse.

The following command creates an interactive warehouse that’s associated with the interactive table named orders. In this case, you can immediately run a USE WAREHOUSE command for the interactive warehouse, and begin running queries for the interactive table:

CREATE OR REPLACE INTERACTIVE WAREHOUSE interactive_demo
  TABLES (orders)
  WAREHOUSE_SIZE = 'XSMALL';

The following command creates an interactive warehouse with no associated interactive tables. In this case, you run ALTER WAREHOUSE commands afterward to associate interactive tables with the interactive warehouse:

CREATE or REPLACE INTERACTIVE WAREHOUSE interactive_demo
  WAREHOUSE_SIZE = 'XSMALL';

After you create an interactive warehouse, the warehouse remains active indefinitely by default. Unlike a traditional warehouse, an interactive warehouse doesn’t include an option to automatically suspend it if it’s idle for some period of time.

Query best practices for interactive warehouses¶

Interactive warehouses are optimized for queries with selective workloads. This means queries with good selectivity see substantially more improvements on performance than other query types.

SELECT col1, col4, AVG(col_x)
  FROM my_table
  GROUP BY col1, col2;

SELECT * FROM my_table;

SELECT col1, col2
  FROM my_table
  WHERE
    col_x IN (1,4,7,8)
    AND event_time >=
      DATEADD(hour, -1, CURRENT_TIMESTAMP());

SELECT col1, col2
  FROM my_table
  WHERE
    event_time >=
      DATEADD(day, -365, CURRENT_TIMESTAMP());

Other complexities such as large joins (such as joining two fact tables), or compute-intensive expressions such as regular expressions, might result in lower concurrency due to higher use of compute resources. See Choosing a size for an interactive warehouse for information about optimizing for those situations.

Data layout best practices for interactive tables¶

Interactive tables follow standard Snowflake best practices for performance. In particular, interactive tables benefit from a well-clustered table, a table that’s sorted based on the same column or columns that you are filtering on. For example, if your query often filters on a TIMESTAMP column such as sale_date, then it makes sense to use that column as the clustering key when creating the interactive table. For example, you might create the interactive table as follows:

CREATE INTERACTIVE TABLE product_sales (<column definitions>) CLUSTER BY (sale_date);

That way, SELECT queries that filter on sale_date can quickly skip all irrelevant data and return results. For example, the following query filters on a date range by testing the sale_date column:

SELECT... WHERE sale_date > '2025-10-24' AND ...

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

Using Search Optimization for point lookups¶

We recommend adding Search Optimization when you perform point lookup queries on your interactive table. Point lookups are queries that filter on a single column to retrieve one or a few rows of data. A good example is WHERE some_id = some_UUID.

Choosing a size for an interactive warehouse¶

Once you’ve completed all your queries and layout optimizations, consider scaling your warehouse to meet demand. Interactive warehouses have a range of sizes from XSMALL to 3XLARGE, as well as Multi-cluster warehouses.

We recommend that you start by sizing your warehouse based on the approximate size of the working data set in the interactive table. The working data set refers to the portion of the data that is frequently queried. For example, if your queries typically only query the last seven days of sales data, the working set is the fraction of the interactive table corresponding to those seven days.

This is because the interactive warehouse utilizes local storage caching. While the data for your entire data set (table) is always accessible, accessing non-cached data does incur higher read latency on the first read.

Choose a warehouse size to fit the needs of your workloads. Experiment with your particular data and workload to determine the optimal size for your interactive warehouse. You can make a multi-cluster warehouse that’s interactive. However, currently the minimum and maximum cluster count must be equal. That is, the interactive multi-cluster warehouse doesn’t automatically scale.

We recommend starting with the following warehouse sizes based on the working data set size.

Adding an interactive table to an interactive warehouse¶

To query an interactive table, you must use an interactive warehouse.

Before you can query the interactive table, you must perform a one-time operation to add the interactive table to the interactive warehouse. Otherwise, you’ll see “object not found” error when running a query against such a table. If you didn’t specify the interactive tables to associate with the interactive warehouses by using the TABLES clause in your CREATE INTERACTIVE WAREHOUSE command, you can do that later by using an ALTER WAREHOUSE command.

The following command associates the orders table with the interactive_demo warehouse. You can specify multiple table names, separated by commas, with the ADD TABLES clause.

ALTER WAREHOUSE interactive_demo ADD TABLES (orders);

This action starts the cache-warming process. That process might take significant time.

If the interactive table is already associated with the interactive warehouse, the command succeeds but has no effect.

You can associate an interactive table with multiple interactive warehouses.

Removing an interactive table from an interactive warehouse¶

You can detach one or more interactive tables from an interactive warehouse by running an ALTER WAREHOUSE command with the DROP TABLES clause.

ALTER WAREHOUSE interactive_demo DROP TABLES (orders, customers);

Resuming and suspending a warehouse¶

The following command resumes an interactive warehouse. You must do this after creating the warehouse, because it’s created in a suspended state:

ALTER WAREHOUSE interactive_demo RESUME;

You also do this to start running queries through the warehouse, if you manually suspended the warehouse.

Queries will be slow while the cache is being warmed after resuming. It might take a few minutes to an hour or so, depending on how much data you have in that table.

The following command suspends an interactive warehouse:

ALTER WAREHOUSE interactive_demo SUSPEND;

You might suspend the warehouse in development and test environments where it won’t be used for many hours. In a production environment, you typically use interactive warehouses for workloads running many concurrent queries 24x7, or where low latency is crucial for queries. Thus, you typically don’t suspend interactive warehouses that you use in production. Snowflake doesn’t automatically suspend interactive warehouses.

Dropping an interactive warehouse¶

You can run the DROP WAREHOUSE command to remove an interactive warehouse entirely. Dropping an interactive warehouse removes the associations between that warehouse and any interactive tables. However, you can still use other interactive warehouses to query those same interactive tables.

Querying an interactive table¶

In your query session, make sure that the warehouse for your current session is an interactive warehouse:

USE WAREHOUSE interactive_demo;

After this, you can query your interactive table normally.