Introducing Interactive Analytics, Powered by Interactive Tables and Warehouses

In today's data-driven environment, the ability to perform real-time, interactive analysis is crucial for both users and applications. Users expect prompt responses as they navigate dashboards, drill down into details and conduct in-depth investigations. This isn't just about simple lookups; it's about performing meaningful analysis on fresh data. Similarly, data APIs require stringent latency SLAs, as downstream applications are critically dependent on their responsiveness. This is especially prescient with the rise of AI use cases, as AI agents will also use these data APIs. Both of these workloads depend on relatively short latencies and high concurrency on analytical queries.

Interactive tables and interactive warehouses

We're thrilled to introduce a powerful new duo: Interactive tables and interactive warehouses. Designed to work in tandem, this specialized pair elevates your interactive analytics experience by delivering consistent, sub-second latency queries on Snowflake.

Interactive tables are a new type of Snowflake table, specialized for low-latency, interactive queries.

Interactive warehouses are a new type of warehouse that’s optimized for low-latency, interactive workloads. Interactive warehouses leverage additional optimizations in the underlying interactive tables in order to accelerate queries. Unlike regular warehouses, which may be frequently suspended or resumed, this type of warehouse is optimized to run continuously, serving high volumes of concurrent queries.

How they work together:

Interactive tables are created from other tables, either via Create-Table-As-Select (CTAS) or defined to automatically and incrementally refresh the data, similar to Dynamic Tables. Once created, interactive tables need to be added to one or more interactive warehouses.

An interactive table can be added to multiple interactive warehouses.
An interactive warehouse can have multiple interactive tables added to it.

When an Interactive Table is added to an Interactive Warehouse, the Interactive Warehouse warms a cache of the Interactive Table data files on the Interactive Warehouse nodes. This operation is asynchronous, and the duration is dependent on the size of the table. During that time, however, queries can still be processed, but the query latency may be longer until the cache is fully warmed. In general, even for cache misses, performance on interactive warehouses is likely to be better than standard warehouses.

It is important to note that Interactive Warehouses can only access the Interactive Tables explicitly added to them. Furthermore, Interactive Warehouses are designed solely for reading data from Interactive Tables. Updates to Interactive Tables occur through other mechanisms, such as automatic refresh, INSERT OVERWRITE statements, or CREATE OR REPLACE TABLE operations. Standard Snowflake virtual warehouses can query Interactive Tables; however, they don't offer the improved performance that Interactive Warehouses provide.

Walkthrough

Let's explore the SQL syntax for these new features:

Creating an Interactive Table

First, we create an Interactive Table. The CREATE TABLE statement has been extended to take a new adjective, INTERACTIVE. When you create an Interactive Table, you must supply one or more clustering keys. Clustering keys here serves the same purpose as with standard tables, and the same guidance applies when choosing them. The following example shows creating an Interactive Table with a CTAS statement:

-- Example: Creating an Interactive Table from another Table
CREATE INTERACTIVE TABLE my_interactive_table
  CLUSTER BY (c1, c2)
AS
SELECT * FROM my_source_table;

As mentioned earlier, you can also use the auto-refresh syntax to create an interactive table. The following example shows creating an interactive table with a target refresh interval of 20 minutes, and uses the specified standard warehouse to process the changes in the source table:

-- Example: Creating an Interactive Table with refresh
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;

Creating an Interactive Warehouse

Next, we create an Interactive Warehouse. The CREATE WAREHOUSE statement has been extended to take the new INTERACTIVE adjective, as well. You can specify the warehouse size and other warehouse attributes, but there are some restrictions on Interactive Warehouses:

Interactive warehouses are not automatically suspended or resumed. As such, you cannot set the AUTO_RESUME or AUTO_SUSPEND parameters. You can manually SUSPEND and RESUME interactive warehouses.
Interactive warehouses do not auto-scale, so you cannot set the SCALING_POLICY parameter.
You can create a multicluster interactive warehouse, but since there is no auto-scaling, the MIN_CLUSTER_COUNT must equal the MAX_CLUSTER_COUNT.

The following example shows creating a medium interactive warehouse with one cluster.

-- Example: CREATE INTERACTIVE WAREHOUSE
CREATE INTERACTIVE WAREHOUSE my_interactive_warehouse
  WAREHOUSE_SIZE = 'MEDIUM'
  MIN_CLUSTER_COUNT = 1
  MAX_CLUSTER_COUNT = 1;

Adding/removing Interactive Tables to/from an Interactive Warehouse

The following example demonstrates how to add an Interactive Table to an Interactive Warehouse. You can specify multiple Interactive Tables in the TABLES clause.

-- Example: Register Interactive Table with Interactive Warehouse
ALTER WAREHOUSE my_interactive_warehouse ADD TABLES (my_interactive_table);

This operation returns immediately, and queries against this table can be run on the interactive warehouse. However, the cache of the data files will be warmed in the background asynchronously, and while the cache is being warmed, the query latencies against this table may be longer than once the cache is fully warm.

`ALTER WAREHOUSE REMOVE TABLES ()`

The following example demonstrates how to remove an interactive table from an interactive warehouse. You can specify multiple interactive tables in the TABLES clause.

-- Example: De-register Interactive Table from Interactive Warehouse
ALTER WAREHOUSE my_interactive_warehouse REMOVE TABLES (my_interactive_table);

Things to keep in mind

Interactive tables and interactive warehouses are an exciting addition to the Snowflake platform. There are some things to keep in mind as you explore these new features.

Interactive warehouses can only query the tables that have been explicitly added to them.
Interactive warehouses can only query interactive tables. However, interactive tables can be queried by either standard warehouses or interactive warehouses.
Interactive warehouses only support SELECT statements. Data updates are handled through other avenues (e.g., via the auto-refresh behavior or INSERT OVERWRITE statements).
Not all read queries are currently supported in interactive warehouses. This functionality will evolve over time to support more operations. Refer to the official documentation for the current list of supported operations.
Interactive warehouses cache the data files for interactive tables registered on them. While the cache is warming (e.g., when an interactive table is initially added to the interactive warehouse), queries on that table can be processed, but they may have diminished query latencies as compared to when the cache is fully warmed.
Interactive warehouses are not automatically suspended or resumed. You can manually SUSPEND and RESUME interactive warehouses, or set up TASKS to automate those actions (e.g., on a schedule), as well.
Interactive warehouses do not auto-scale. You can create a multi-cluster interactive warehouse, but since there is no auto-scaling, the MIN_CLUSTER_COUNT must equal the MAX_CLUSTER_COUNT.

Looking forward

We are really excited to share this new feature with the Snowflake community. We have seen some very interesting and compelling uses from our early adopters, and we encourage you to explore these new features and experience the enhanced performance for your interactive workloads!

To get started, check out our new quickstart on interactive tables and interactive warehouses.

This is just the beginning for interactive tables, so stay tuned as we develop more features and capabilities!