Snowflake Interactive Tables & Warehouses –

-

Snowflake Interactive Tables & Warehouses –

Published: None

Source: https://www.linkedin.com/pulse/snowflake-interactive-tables-warehouses-arabinda-mohapatra-jcu0f?trackingId=xP9nN9ecSTuF1gxQ9dl6jQ%3D%3D

Snowflake Interactive Tables & Warehouses –

Arabinda Mohapatra

Arabinda Mohapatra

Running Kafka streams after dark, diving into genetic code by daylight, and wrestling with Databricks and Tableflow in every spare moment—sleep is optional

Snowflake’s Interactive Tables and Interactive Warehouses are designed for low-latency, high-concurrency workloads—think dashboards, APIs, and real-time analytics. They’re generally available in select AWS regions and represent a new class of objects in Snowflake.

  • Views → flexible but slow
  • Materialized Views → fast but static

Now, Snowflake introduces Interactive Tables & Warehouses – purpose-built for sub-second, high-concurrency workloads.

Article content


Interactive Tables

  • Specialized table type optimized for fast, selective queries.
  • Best suited for queries with WHERE clauses and small GROUP BYs.
  • Require a CLUSTER BY clause at creation to align with query filters.
  • Support auto-refresh via TARGET_LAG to keep data nearly real-time.
  • Limitations: no UPDATE/DELETE, no replication, no fail-safe, limited ALTER TABLE operations.

Interactive Warehouses

  • Compute layer tuned for sub-second query performance.
  • Always-on by design (no auto-suspend).
  • Optimized for continuous workloads with many concurrent queries.
  • Query timeout defaults to 5 seconds—not suitable for long-running queries.
  • Can be multi-cluster, but no auto-scaling.

How They Differ from Materialized Views

Article content

Limitations of Interactive Warehouses

Limitations of Snowflake Interactive Warehouses & Interactive Tables

Snowflake’s Interactive Warehouses and Interactive Tables are powerful for real-time, high-concurrency workloads, but they come with specific limitations due to their architecture.

Limitations of Interactive Warehouses

Query Timeout:

  • SELECT queries have a strict 5-second timeout.
  • You can reduce the timeout but cannot increase it.
  • Long-running queries are not supported.

Always-On Behavior:

  • Warehouses don’t auto-suspend when idle.
  • Manual suspension is possible, but resuming causes latency.

Table Compatibility:

  • Can only query interactive tables. To query standard tables, you must switch warehouses.

Scaling:

  • Multi-cluster warehouses don’t auto-scale. MIN_CLUSTER_COUNT and MAX_CLUSTER_COUNT must be equal.

Unsupported Features:

  • No stored procedure calls (CALL). No ->> pipe operator (relies on stored procedures). No replication or failover support.

Limitations of Interactive Tables

DML Restrictions:

  • No UPDATE or DELETE. Only INSERT OVERWRITE is supported.

Replication & Recovery:

  • Not included in replication or failover groups. No Fail-safe recovery (but Time Travel is supported).

Query Insights:

  • Query insights are not collected for interactive tables.

Search Optimization:

  • Search optimization service is not supported.

Unsupported Operations:

  • Cannot be used as a source for materialized views.
  • Limited ALTER TABLE support (only rename, comments, masking/join/aggregation/row access policies, storage lifecycle policies).
  • No streams or dynamic tables based on interactive tables.
  • No RESAMPLE clause support.
  • Time Travel retention period cannot be set directly; it inherits from schema/database/account.

Refer-

https://docs.snowflake.com/en/user-guide/interactive





Comments

Post a Comment