feat(snowflake): add interactive table materialization support - #1798
feat(snowflake): add interactive table materialization support#1798jimmyxie-figma wants to merge 16 commits into
Conversation
Add support for Snowflake Interactive Tables as a new materialization type (`materialized='interactive_table'`), addressing the GA of Snowflake's interactive analytics feature. Interactive tables are optimized for low-latency, high-concurrency queries when used with interactive warehouses. They support both static (one-time populate) and dynamic (auto-refresh via TARGET_LAG) variants. Key changes: - New `InteractiveTable` relation type and config dataclass - CREATE/REPLACE/DROP SQL macros for interactive table DDL - Materialization macro with create/replace/no-op routing - Detection via `is_interactive` column in SHOW OBJECTS/TABLES - Catalog integration for `dbt docs` - 20 unit tests for config parsing and changeset detection Since Snowflake has no ALTER INTERACTIVE TABLE, all config changes require CREATE OR REPLACE (full refresh). Refs: dbt-labs#1780 Made-with: Cursor
|
Thanks for your pull request, and welcome to our community! We require contributors to sign our Contributor License Agreement and we don't seem to have your signature on file. Check out this article for more information on why we have a CLA. In order for us to review and merge your code, please submit the Individual Contributor License Agreement form attached above above. If you have questions about the CLA, or if you believe you've received this message in error, please reach out through a comment on this PR. CLA has not been signed by users: @jimmyxie-figma |
The is_dynamic check in _parse_list_relations_result only matched 'Y', while the adjacent is_interactive and is_iceberg checks accepted both 'Y' and 'YES'. This inconsistency could cause dynamic tables to be misidentified as regular tables if Snowflake returns 'YES'. Made-with: Cursor
The get_create_interactive_table_as_sql macro accepted sql as a parameter but ignored it, using the compiled_code global instead. This is fragile and inconsistent with the replace macro. Now passes the sql parameter through to the inner macro. Made-with: Cursor
1. describe_interactive_table: filter SHOW TABLES results to exact name match, since LIKE uses pattern matching and can return multiple rows. 2. target_lag removal detection: remove the `is not None` guard so that switching from dynamic to static (removing target_lag) is properly detected as a config change requiring CREATE OR REPLACE. 3. catalog.sql: remove is_interactive reference from information_schema query. Per Snowflake docs, is_interactive only exists in SHOW TABLES output (BCR-2165), not in information_schema.tables. The column reference would cause the catalog query to fail. 4. Remove unused Union import from interactive_table.py. 5. Always include 'text' in describe_interactive_table base_columns since parse_relation_results depends on it for the query field. 6. Add unit test for target_lag removal change detection. Made-with: Cursor
In _parse_list_relations_result, the interactive table and dynamic table type assignments used self.Relation.InteractiveTable/DynamicTable which are classproperties returning plain str values. This created a type inconsistency with the SnowflakeRelationType enum returned by get_relation_type on line 345. Now uses SnowflakeRelationType enum members directly, matching the Optional[SnowflakeRelationType] type annotation on the relation's type field. Made-with: Cursor
Made-with: Cursor
GitHub Actions
Inactive
There was a problem hiding this comment.
Pull request overview
Adds a new Snowflake interactive_table materialization to dbt-snowflake, including relation/config plumbing, DDL macros, metadata detection for catalog/docs, and unit tests.
Changes:
- Introduces
SnowflakeRelationType.InteractiveTableplus interactive-table config parsing + changeset detection. - Adds interactive table DDL + materialization macros (create/replace/drop/describe) and wires them into generic relation macros.
- Extends adapter metadata/caching to recognize interactive tables and adds unit tests + changelog entry.
Reviewed changes
Copilot reviewed 19 out of 19 changed files in this pull request and generated 4 comments.
Show a summary per file
| File | Description |
|---|---|
| dbt-snowflake/tests/unit/test_renamed_relations.py | Adds InteractiveTable to renameable relation types test. |
| dbt-snowflake/tests/unit/test_interactive_table_config.py | New unit tests for config parsing/validation and changeset detection. |
| dbt-snowflake/tests/unit/test_alter_relation_comment_macro.py | Extends mock relation to include is_interactive_table. |
| dbt-snowflake/src/dbt/include/snowflake/macros/relations/replace.sql | Routes replace SQL generation for interactive tables. |
| dbt-snowflake/src/dbt/include/snowflake/macros/relations/interactive_table/replace.sql | Implements CREATE OR REPLACE INTERACTIVE TABLE DDL macro. |
| dbt-snowflake/src/dbt/include/snowflake/macros/relations/interactive_table/rename.sql | Adds rename macro for interactive tables. |
| dbt-snowflake/src/dbt/include/snowflake/macros/relations/interactive_table/drop.sql | Adds drop macro for interactive tables. |
| dbt-snowflake/src/dbt/include/snowflake/macros/relations/interactive_table/describe.sql | Adds describe macro calling adapter method. |
| dbt-snowflake/src/dbt/include/snowflake/macros/relations/interactive_table/create.sql | Implements CREATE INTERACTIVE TABLE AS DDL macro. |
| dbt-snowflake/src/dbt/include/snowflake/macros/relations/drop.sql | Routes drop SQL generation for interactive tables. |
| dbt-snowflake/src/dbt/include/snowflake/macros/relations/create.sql | Routes create SQL generation for interactive tables. |
| dbt-snowflake/src/dbt/include/snowflake/macros/materializations/interactive_table.sql | New interactive_table materialization with config-change behavior. |
| dbt-snowflake/src/dbt/include/snowflake/macros/adapters.sql | Treats interactive tables like tables for comment/column comment macros. |
| dbt-snowflake/src/dbt/adapters/snowflake/relation.py | Adds InteractiveTable type helpers and interactive config changeset detection. |
| dbt-snowflake/src/dbt/adapters/snowflake/relation_configs/policies.py | Adds InteractiveTable enum member. |
| dbt-snowflake/src/dbt/adapters/snowflake/relation_configs/interactive_table.py | New config dataclass + changeset types for interactive tables. |
| dbt-snowflake/src/dbt/adapters/snowflake/relation_configs/init.py | Exports interactive-table config classes. |
| dbt-snowflake/src/dbt/adapters/snowflake/impl.py | Adds interactive-table detection in listing/catalog + implements describe_interactive_table. |
| dbt-snowflake/.changes/unreleased/Features-20260323-100000.yaml | Adds changelog entry for the new feature. |
💡 Add Copilot custom instructions for smarter, more guided reviews. Learn how to get started.
| {% if existing_relation.is_dynamic_table and target_relation.is_dynamic_table %} | ||
| {{ snowflake__get_replace_dynamic_table_sql(target_relation, sql) }} | ||
|
|
||
| {% elif target_relation.is_interactive_table %} |
There was a problem hiding this comment.
snowflake__get_replace_sql routes to the interactive-table replace DDL based only on target_relation.is_interactive_table. This means a type change (e.g., existing plain table/view -> interactive table) will also use CREATE OR REPLACE INTERACTIVE TABLE ... instead of the default replace flow, whereas dynamic tables only use the in-place CREATE OR REPLACE path when both existing and target are dynamic. To keep behavior consistent and avoid relying on cross-type replace semantics, gate this branch on existing_relation.is_interactive_table and target_relation.is_interactive_table and fall back to default__get_replace_sql otherwise.
| {% elif target_relation.is_interactive_table %} | |
| {% elif existing_relation.is_interactive_table and target_relation.is_interactive_table %} |
There was a problem hiding this comment.
The dynamic table pattern requires both sides because it has ALTER support and needs to distinguish between replace DT with DT vs replace something else with DT. But interactive tables have no ALTER, so CREATE OR REPLACE INTERACTIVE TABLE is always the correct DDL regardless of the existing type.
Consider the scenario: existing is a view, target is an interactive table (user changed materialized from 'view' to 'interactive_table'). The materialization correctly calls get_replace_sql because not existing_relation.is_interactive_table is true.
Current code (only checks target): Routes to snowflake__get_replace_interactive_table_sql, which generates CREATE OR REPLACE INTERACTIVE TABLE .... This works -- Snowflake's CREATE OR REPLACE atomically drops the old object and creates the new one.
Copilot suggestion (checks both), The branch wouldn't match (existing view is not interactive), so it falls to default__get_replace_sql, which would generate standard CREATE OR REPLACE TABLE/VIEW DDL, not a valid CREATE OR REPLACE INTERACTIVE TABLE. The model would fail.
| columns = ["database_name", "schema_name", "name", "kind", "is_dynamic", "is_iceberg"] | ||
| schema_objects = schema_objects.rename( | ||
| column_names=[col.lower() for col in schema_objects.column_names] | ||
| ) | ||
| return [self._parse_list_relations_result(obj) for obj in schema_objects.select(columns)] | ||
| available_columns = [c.lower() for c in schema_objects.column_names] | ||
| has_is_interactive = "is_interactive" in available_columns | ||
| if has_is_interactive: | ||
| columns.append("is_interactive") | ||
| return [ | ||
| self._parse_list_relations_result(obj, has_is_interactive=has_is_interactive) | ||
| for obj in schema_objects.select(columns) | ||
| ] | ||
|
|
||
| def _parse_list_relations_result(self, result: "agate.Row") -> SnowflakeRelation: | ||
| database, schema, identifier, relation_type, is_dynamic, is_iceberg = result | ||
| def _parse_list_relations_result( | ||
| self, result: "agate.Row", has_is_interactive: bool = False | ||
| ) -> SnowflakeRelation: | ||
| if has_is_interactive: | ||
| database, schema, identifier, relation_type, is_dynamic, is_iceberg, is_interactive = ( | ||
| result | ||
| ) | ||
| else: | ||
| database, schema, identifier, relation_type, is_dynamic, is_iceberg = result | ||
| is_interactive = None | ||
|
|
||
| try: | ||
| relation_type = self.Relation.get_relation_type(relation_type.lower()) | ||
| except ValueError: | ||
| relation_type = self.Relation.External | ||
|
|
||
| if relation_type == self.Relation.Table and is_dynamic == "Y": | ||
| relation_type = self.Relation.DynamicTable | ||
| if relation_type == self.Relation.Table and is_interactive in ("Y", "YES"): | ||
| relation_type = SnowflakeRelationType.InteractiveTable | ||
| elif relation_type == self.Relation.Table and is_dynamic in ("Y", "YES"): | ||
| relation_type = SnowflakeRelationType.DynamicTable |
There was a problem hiding this comment.
list_relations_without_caching() only looks for and selects an is_interactive column, and _parse_list_relations_result() only treats values in ("Y", "YES") as truthy. If Snowflake exposes interactive-table metadata under a different column name (e.g. is_adaptive per the referenced release notes) or as a boolean (true/false), interactive tables won't be detected correctly. Consider checking for alternate column names/representations and mapping them into a single is_interactive flag before parsing.
|
@jimmyxie-figma thanks for the contribution! Take a look at Copilot's suggestions at first glance they're correct. Also please run our code formatter via: |
Made-with: Cursor
Refactor interactive flag detection to handle both `is_interactive` and `is_adaptive` column names from Snowflake metadata. Fix mypy union-attr error on nullable identifier by guarding with `or ""`. Add quoting-aware identifier matching in describe_interactive_table. Add unit tests for the new flag detection helpers. Made-with: Cursor
…ined error Annotate the relation_configs class variable with ClassVar[Dict[str, Type[SnowflakeRelationConfigBase]]] so mypy recognizes that dict values have from_relation_config(). Made-with: Cursor
GitHub Actions
Inactive
There was a problem hiding this comment.
Pull request overview
Copilot reviewed 19 out of 19 changed files in this pull request and generated no new comments.
💡 Add Copilot custom instructions for smarter, more guided reviews. Learn how to get started.
|
@jimmyxie-figma There are a few references here to "is_adaptive" which is completely irrelevant. It's actually a warehouse indicator and not a table indicator. They should be removed. |
… table indicator Simplify interactive table detection to only check the is_interactive column. Remove _INTERACTIVE_TABLE_COLUMN_NAMES, _TRUTHY_FLAG_VALUES, _is_interactive_flag(), _get_interactive_flag() helpers and their tests. Made-with: Cursor
…n_results Interactive tables always require cluster_by. If Snowflake metadata returns an empty or sentinel value, raise a CompilationError rather than silently setting cluster_by to None, which would violate the str type annotation on the dataclass field. Made-with: Cursor
|
LGTM |
GitHub Actions
Inactive
|
Will this work with |
No, this won’t work with dbt docs generate. The issue is that Snowflake’s The only reliable way to identify interactive tables today is through So if we want docs support here, we’d probably need catalog macro changes like the ones you linked. And if Snowflake ever adds |
|
Hey @colin-rogers-dbt, is having that column a blocker? Or can this be taken as a follow up? I can check with the team to see if this can be added. But in the mean time, can we unblock this PR if that works? |
|
Hey @colin-rogers-dbt, any luck getting to this? |
|
While I wouldn't consider @sfc-gh-kasher I think we can proceed with this in the short term as a known, if serious, limitation but we'll want to address prior to release. Adding to |
|
@jimmyxie-figma sorry should have caught this earlier but can you add some functional tests here? Specifically adding an interactive table test case here: |
I'll work with the team to get this added soon. Thanks for the flexibility. Let's get this out and we can do this as a fast follow as you mentioned.
@colin-rogers-dbt, just to confirm, are you open to releasing this with the current version with the known limitation while we work internally to get this support for information_schema.tables? I have already started those conversation so I am hoping to make progress on that pretty soon. |
yes, I think that should be fine in the short term. |
GitHub Actions
Inactive
|
Thanks @colin-rogers-dbt, can you review the PR in that case? If you're not the right person, can you get this reviewed by the team? |
@sfc-gh-kasher was waiting on this comment but it might have gotten lost in the shuffle of our other convo. |
There was a problem hiding this comment.
Pull request overview
Copilot reviewed 19 out of 19 changed files in this pull request and generated 1 comment.
Comments suppressed due to low confidence (1)
dbt-snowflake/src/dbt/include/snowflake/macros/adapters.sql:118
snowflake__alter_column_commentnow treats interactive tables astable(same as dynamic tables), but there’s no unit test coverage validating this new branch. Consider adding a macro test that setsrelation.is_interactive_table = Trueand asserts the generatedalter table ... alter ...SQL uses the correct relation type/prefix.
{% macro snowflake__alter_column_comment(relation, column_dict) -%}
{% set existing_columns = adapter.get_columns_in_relation(relation) | map(attribute="name") | list %}
{% if relation.is_dynamic_table or relation.is_interactive_table -%}
{% set relation_type = "table" %}
{% else -%}
{% set relation_type = relation.type %}
{% endif %}
💡 Add Copilot custom instructions for smarter, more guided reviews. Learn how to get started.
| {% macro snowflake__alter_relation_comment(relation, relation_comment) -%} | ||
| {%- if relation.is_dynamic_table -%} | ||
| {%- set relation_type = 'dynamic table' -%} | ||
| {%- elif relation.is_interactive_table -%} | ||
| {%- set relation_type = 'table' -%} | ||
| {%- else -%} | ||
| {%- set relation_type = relation.type -%} |
There was a problem hiding this comment.
The new interactive-table branch in snowflake__alter_relation_comment isn’t covered by the existing macro unit tests (this file has tests for regular/dynamic/iceberg paths only). Add a test case with relation.is_interactive_table = True to assert the expected comment on table ... SQL so the new conditional path is exercised and guarded against regressions.
Hey @jimmyxie-figma, did you get a chance to check the above? Is there anything more that we need to add? @colin-rogers-dbt, what would be the next steps? Do we need those tests? |
|
Ran the functional tests I added in jimmyxie-figma#1 against a real Snowflake account. 5/10 pass, 5/10 fail — all failures share the same root cause, which looks like a real bug in this PR: FailureOn the second Root causeIn base_columns = ["name", "schema_name", "database_name", "text", "cluster_by"]
...
selected = exact_match.select(base_columns)But Similarly, ImpactAny interactive_table model succeeds on first run, then fails on every subsequent run (config-change detection, no-op, What passesAll first-run paths:
What failsAll second-run paths:
Happy to help patch |
Thanks @colin-rogers-dbt, that would be super helpful if you can fix that. |
|
Hey @jimmyxie-figma . I looked at the code and it looks like there's one more issue wrt the the Problem The function Assessment Snowflake doesn't expose
Solution Until we're able to get |
|
Hey @jimmyxie-figma - picked this up to validate against a Snowflake account now that 1 - describe_interactive_table in dbt-snowflake/src/dbt/adapters/snowflake/impl.py — switch to SHOW INTERACTIVE TABLES SHOW TABLES returns neither target_lag nor warehouse for interactive tables, and it also lacks schema_name, database_name, and text. SHOW INTERACTIVE TABLES returns all of them (verified against a live account): show_sql = f"show interactive tables like '{relation.identifier}' in schema {database}.{schema}" 2 - Column name is refresh_warehouse, not warehouse SHOW INTERACTIVE TABLES exposes the refresh warehouse as refresh_warehouse. Two spots to update: impl.py ~L702: if "refresh_warehouse" in available_columns: base_columns.append("refresh_warehouse") 3 - cluster_by is returned wrapped in parens — needs normalization Snowflake stores cluster_by='id' as '(id)' in the metadata output. Without stripping, every re-run sees 'id' != '(id)' and triggers a spurious CREATE OR REPLACE. In parse_relation_results: cluster_by_val = str(cluster_by_raw).strip() 4 - Functional tests @colin-rogers-dbt already wrote a functional test suite in jimmyxie-figma#1. I ran it against a real Snowflake account on top of the fixes above — all 10 tests pass. Out of scope but worth noting: interactive tables are most useful when attached to an interactive warehouse via ALTER WAREHOUSE … ADD TABLES (…). The current materialization doesn't do this, so users get an interactive table without the low-latency cache benefits. Suggest tracking that as a follow-up PR with a dedicated interactive_warehouse config and its own functional test. |
|
Hey @jimmyxie-figma, would you be able to help with the above pointers? |
Add support for Snowflake Interactive Tables as a new materialization type (
materialized='interactive_table'), addressing the GA of Snowflake's interactive analytics feature.Interactive tables are optimized for low-latency, high-concurrency queries when used with interactive warehouses. They support both static (one-time populate) and dynamic (auto-refresh via TARGET_LAG) variants.
Key changes:
InteractiveTablerelation type and config dataclassis_interactivecolumn in SHOW OBJECTS/TABLESdbt docsSince Snowflake has no ALTER INTERACTIVE TABLE, all config changes require CREATE OR REPLACE (full refresh).
Refs: #1780
Made-with: Cursor
resolves #1780
docs dbt-labs/docs.getdbt.com/#
Problem
Solution
Checklist