[ADAP-633] [Feature] Support Hybrid tables

[b-per]

Is this your first time submitting a feature request?

• I have read the expectations for open source contributors
• I have searched the existing issues, and I could not find an existing issue for this feature
• I am requesting a straightforward extension of existing dbt-snowflake functionality, rather than a Big Idea better suited to a discussion

Describe the feature

We could add support for Unistore/Hybrid tables by allowing users to materialize hybrid tables from dbt.

I couldn't find an official docs page for the feature but there is a blog post talking about it.

The syntax examples shown are CREATE HYBRID TABLE xxx or CREATE OR REPLACE HYBRID TABLE xxx but I don't know if it supports CREATE HYBRID TABLE AS () or not.

Describe alternatives you've considered

People could create their own materialization or update some of the core macros to allow creating those tables.

Who will this benefit?

Snowflake customers who would like to use the Unistore capabilities.

Are you interested in contributing this feature?

No response

Anything else?

No response

[dataders]

I agree this would be awesome to support. Do you have any materialization as to what node type it should be? table? It's own materialization?

Like you said, provide that Dynamic Tables support the standard DDL, the engineering effort to support them is low and this could in theory be supported in 1.7 or 1.8

my questions:

1. what does the ideal workflow/ergonomics for using Hybrid tables look like?
2. any idea what other settings might be needed?
3. do other data platforms offer a similar feature that would justify including a default implementation in core that adapters may override?

If someone is interested to help out with this, I'd suggest a good starting point is to make a custom hybrid_table materialization, based on dbt-snowflake's table materialization.

[github-actions]

This issue has been marked as Stale because it has been open for 180 days with no activity. If you would like the issue to remain open, please comment on the issue or else it will be closed in 7 days.

[b-per]

Commenting to remove the stale tag! I think it would still be useful.

[dwreeves]

Bumping this as hybrid tables are now in public preview, and this is something we want.

This will involve a little more elbow grease for the DDL statement than with transient vs permanent tables.

[migeraghty]

Bumping this again as hybrid tables are a perfect use case for our DBT snapshotting processes. We also want the ability to enforce primary key and foreign key constraints, and this should in theory be very low lift to add in DBT.

[GiorgiModebadzeW]

Usecase:
At docker we want to use hybrid tables as reverse etl backend for our user facing application- where we share some data with clients about docker usage. etc.
because snowflakes original tables are not set for high concurrency read requests we want to utilize hybrid tables as the replacement of postgres and avoid copying data from snowflake tables (generated by dbt) to postgres.

Materialization:
I think supporting both normal table and incremental load for hybrid tables would be ideal usecase for us. So having two new materialization type: hybrid_incremental and hybrid_table would be great. we could even avoid creating new materialization and create new type for snowflake adapter which by default would be normal and if can be specified as hybrid:

{{ config(materialized='table',
   type='hybrid'
 ) }}

{{ config(materialized='incremental',
   type='hybrid'
 ) }}

thank you and looking forward to this