Snowflake interactive tables and interactive warehouses¶

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;

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

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

ALTER INTERACTIVE TABLE ``my_dynamic_interactive_table`` REFRESH.

Creating an interactive warehouse¶

After you create an interactive table, querying that table with optimal performance 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, it remains in a suspended state until you resume it. You can configure auto-suspend and auto-resume for interactive warehouses. The minimum auto-suspend interval for an interactive warehouse is 24 hours (86400 seconds). For details, see Resuming and suspending an interactive warehouse.

Resuming and suspending an interactive 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. Warming speed is based on the size of the data and the warehouse size. A XS warehouse warms roughly at 300-400MB/s. The bigger the table, the longer the cache warming time. Larger warehouses warms faster.

The following command suspends an interactive warehouse:

ALTER WAREHOUSE interactive_demo SUSPEND;

CREATE INTERACTIVE WAREHOUSE interactive_demo
  WAREHOUSE_SIZE = 'XSMALL'
  AUTO_SUSPEND = 86400
  AUTO_RESUME = TRUE;

ALTER WAREHOUSE interactive_demo SET
  AUTO_SUSPEND = 86400
  AUTO_RESUME = TRUE;

Adding an interactive table to an interactive warehouse¶

To get optimal query performance for an interactive table, you should use an interactive warehouse.

Before you query an interactive table from an interactive warehouse, you can add interactive tables to the interactive warehouse. Adding a table to an interactive warehouse indicates to Snowflake that this table need to be maintained in the data cache. 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);

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.

This action starts the data cache-warming process. Cache warming time is based on the size of the data and the warehouse size. A XS warehouse warms roughly at 300-400MB/s. The bigger the table, the longer the cache warming time. Larger warehouses warms faster.

Warming process do not block the warehouse from accepting new queries. Priority of warming is:

1. User issued queries

2. Newly added micropartitions through auto-refresh or other means of data ingestion

3. Any existing data

While not strictly required, we strongly recommend you to add tables you use frequently to your interactive warehouse. This will help you achieve lower latency and higher concurrency.

Because cache warming depends on your queries, the best way to monitor whether the cache is warm is to review the remote read percentage in Snowsight Query Profile. For programmatic access to query operator statistics, see GET_QUERY_OPERATOR_STATS. In ideal execution scenarios, low-latency queries should have a remote read percentage of 0%.

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.

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

Automatically handling statement timeouts¶

In order to optimize for high concurrency queries on interactive warehouses, Snowflake automatically cancels queries after 5 seconds. You have an option to specify a fallback warehouse to allow automatic re-run of timed-out queries. By offloading timed-out queries to a secondary compute resource, you ensure that temporary resource contention or large scans do not block the interactive user experience.

This fallback behavior is transparent to the client issuing the query, it behaves as an internal retry mechanism.

Configuring a fallback warehouse provides an automated retry mechanism for these canceled queries that exceed 5 seconds. When a fallback warehouse is added to an interactive warehouse, any query that exceeds the timeout threshold on the primary warehouse is automatically cancelled and retried on the designated fallback warehouse.

• Timeout Threshold: The STATEMENT_TIMEOUT_IN_SECONDS for queries on Interactive Warehouses are fixed at a default and maximum of 5 seconds. If a query does not complete within this window, then Snowflake automatically retries the query on the fallback warehouse.

• Retry Logic: When a retry on fallback warehouse occurs, the failed query time shows up in fault_handling_time in query profile.

• Warehouse State: The fallback warehouse must be started (or set to auto-resume) to accept the retried query. Standard credit consumption applies to the fallback warehouse once it is active.

• RBAC requirements: To query with fallback support, the querying role must have USAGE on both the interactive warehouse and its fallback warehouse. To set a fallback warehouse, the administrator role must have ALTER WAREHOUSE on the interactive warehouse and USAGE on the fallback warehouse.

To set a fallback warehouse, you can use the following command:

ALTER WAREHOUSE interactive_demo SET FALLBACK_WAREHOUSE = <fallback_warehouse_name>;

To remove a fallback warehouse, you can use the following command:

ALTER WAREHOUSE interactive_demo UNSET FALLBACK_WAREHOUSE;

To view the fallback warehouse for an interactive warehouse, you can use the following command:

SHOW WAREHOUSES like '%interactive_demo%';

and inspect the FALLBACK_WAREHOUSE column.

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, col4;

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());

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.

Snowflake Cortex Code supports automatic clustering key selection for interactive tables. To use it, start Cortex Code and prompt it to suggest a clustering key using the interactive-clustering-key-recommendation skill.

Choosing a size for an interactive warehouse¶

You should start by sizing your warehouse based on the approximate size of the working set in the interactive table. Interactive warehouse will pre-warm the data cache based on tables you’ve attached to the warehouse via ALTER WAREHOUSE ADD TABLES(table_1, table_2, …). command.

For example, if your table is 120TB (compressed), and it represents 1 year worth of data. If you mostly query the last 7 days, then you should size your warehouse as MEDIUM to be able to hold the last 7 days of data. (120TB / 365 days * 7 days = 2.19TB) Interactive warehouses have a range of sizes from XSMALL to 4XLARGE, see the warehouse sizing table for more information.

Once you’ve completed all your queries and layout optimizations, consider scaling your warehouse to meet demand. Choose a minimum number of clusters sufficient to support the expected number of concurrent users, see Multi-cluster warehouses.

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 data and workload to determine the optimal size for your interactive warehouse.

Performance optimization for interactive analytics¶

Interactive analytics is optimized for short-running queries and enforce a default statement timeout of 5s. You might encounter patterns where queries run slower than expected. The following sections describe common problems, how to recognize them, and what to try.

For all of these patterns, your primary diagnostic tool is the Snowsight Query Profile, especially the remote read percentage and the time breakdown between compilation, queuing, and execution. For programmatic access to query operator statistics, see GET_QUERY_OPERATOR_STATS.

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.

Best practices for interactive materialized views¶

Follow these guidelines when you create materialized views using interactive tables:

• Interactive Materialized Views work just like regular materialized views. It must be based on an interactive table.

• Interactive Materialized View and the source interactive table it’s based on must be added to the same interactive warehouse.

• Joins aren’t supported in materialized views, whether interactive or standard. Structure your queries to aggregate or filter from a single base table.

• You can’t use an interactive table or an interactive materialized view as the source for a dynamic table.

• When you’re considering candidates for interactive materialized views, choose aggregation queries that are frequently run and expensive to compute

Task based multi-cluster sizing¶

You can adjust the MIN_CLUSTER_COUNT parameters via scheduled tasks.

-- 1) Task to scale OUT during business hours
CREATE OR REPLACE TASK mcw_scale_out_morning
  WAREHOUSE = my_wh          -- the warehouse that *executes the task*
  SCHEDULE = 'USING CRON 0 8 * * * UTC'   -- 08:00 UTC daily
AS
  ALTER WAREHOUSE my_wh      -- the warehouse you want to change (can be same or different)
    SET
      MIN_CLUSTER_COUNT = 10;  -- optional: ECONOMY or STANDARD

-- 2) Task to scale IN after hours
CREATE OR REPLACE TASK mcw_scale_in_evening
  WAREHOUSE = my_wh
  SCHEDULE = 'USING CRON 0 20 * * * UTC'  -- 20:00 UTC daily
AS
  ALTER WAREHOUSE my_wh
    SET
      MIN_CLUSTER_COUNT = 2;

It’s recommended to set the MAX_CLUSTER_COUNT using auto-scaling policy to acommendate peak concurrency for your workload..

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.

Benchmarking best practices¶

When assessing the performance of interactive tables in a test environment, follow these best practices to avoid inconsistent or misleading results:

• Turn off the query result cache to make the benchmark results consistent between multiple benchmark runs. You can turn off the query result cache at the account, user, and session level by setting the USE_CACHED_RESULT session parameter. That way, the queries only use the table data cache from the interactive warehouse. When you turn result caching on in your production environment, you can expect equal or better performance than in your benchmark testing.

• Because an interactive warehouse takes some time to warm the table data cache, wait for a while after you create or resume an interactive warehouse before testing query performance. This simulates the typical production configuration, where the warehouse remains active for long periods. Snowflake applies optimizations to the cache warming process. Therefore, it’s more efficient to let Snowflake complete this process than to warm the cache yourself by running sample queries.

• When comparing performance of interactive tables against standard Snowflake tables, don’t interleave the queries between standard and interactive tables. Instead, run the full benchmark on standard tables, then run the same tests on interactive tables.

• When doing comparative benchmarks with other database systems, make sure that the clustering columns in your interactive tables match the WHERE clause predicates in your queries. For more information about choosing the best clustering columns, see Clustering Keys & Clustered Tables. In particular, don’t cluster on columns with high cardinality, such as unique IDs or timestamps.

• If your queries are short and simple, you can achieve higher concurrency by setting the MAX_CONCURRENCY_LEVEL parameter to a higher value for your interactive warehouse.

Limitations of interactive warehouses¶

• Snowflake interactive warehouses is optimized for short-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. This is by design to prevent long-running queries from starving the interactive warehouse of resources and degrading the performance of low latency queries. METADATA commands, such as SHOW and INSERT OVERWRITE, aren’t subject to the five-second timeout interval.

• If a query consistently times out, that’s a signal that it might not be suitable for use with interactive warehouses. Commonly, applying some of the performance tuning techniques can help reduce query latency. See Interactive table performance considerations for more details.

• Interactive warehouses support auto-suspend and auto-resume. The minimum auto-suspend interval is 24 hours (86400 seconds). Manual suspension/resume of interactive warehouse is supported with a minimum 1 hour billing period. Expect significant query latency when you resume an interactive warehouse, because the data cache needs to warm up again. For more information, see Resuming and suspending an interactive 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.

• You can add up to a maximum of 10 interactive tables to an interactive warehouse. This is a temporary limitation to prevent overloading of the system. This limit will be increased in the future. If you need to add more than 10 interactive tables, please contact Snowflake Support.

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

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

Limitations of interactive tables¶

• Interactive tables don’t support the following features:

  • Data manipulation language (DML) commands such as UPDATE and DELETE. The recommended workflow is to use auto-refresh interactive tables (i.e. by setting TARGET_LAG) and apply DML to the source table instead. The auto-refresh mechanism is more efficient and cost-effective than using DML on the interactive table. The only DML that you can perform is INSERT OVERWRITE.

  • Fail-safe. This data recovery mechanism isn’t available for interactive tables. However, you can still use Time Travel with interactive tables.

  • Query insights. They currently aren’t collected or available for queries executing on interactive tables to help reduce query execution latency when queries are executed using SDKs. Snowsight queries still produce insights, but at a cost of higher query latency.

• You can’t perform the following operations:

  • Use an interactive table as the source for a standard (non-interactive) materialized view. To create a materialized view on an interactive table, use the INTERACTIVE keyword. See Materialized view support for interactive tables.

  • Modify properties of an interactive table by using ALTER TABLE clauses such as ADD COLUMN or REMOVE COLUMN. ALTER TABLE operations that you can perform include:

    • Renaming the table.

    • Modifying columns to set or unset comments.

    • Setting or unsetting masking policies on columns.

    • Adding or unsetting a masking policy, join policy, aggregation policy, or row access policy on the table.

    • Adding a storage lifecycle policy to the table, or dropping a storage lifecycle policy from the 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.

  • Set the Time Travel retention period using CREATE INTERACTIVE TABLE or ALTER TABLE. Interactive tables inherit the DATA_RETENTION_TIME_IN_DAYS value from their parent schema, database, or account.