Feature Request: Support for Snowflake Hybrid Tables for State Management

[lgwacker]

Currently, the SQLMesh documentation advises against using Snowflake for storing state due to its historical limitations with transactional workloads, which can lead to slow state syncs and plan applications. However, Snowflake has introduced Hybrid Tables, which are optimized for transactional use cases and offer row-level locking and faster performance for single-row operations.

This makes Hybrid Tables an ideal candidate for managing SQLMesh's state, but SQLMesh does not currently have a mechanism to create state tables using this feature. The only way to achieve this is by manually forking the repository and modifying the SnowflakeEngineAdapter, which is not a sustainable solution.

Describe the solution you'd like

I propose adding a configuration option that allows users to specify that SQLMesh state tables in Snowflake should be created as HYBRID TABLEs. This could be a setting in the Snowflake connection profile within config.yaml or a similar configuration file.

For example, the configuration could look something like this:

gateways:
  snowflake_dev:
    connection:
      type: snowflake
      user: ...
      # New option
      use_hybrid_tables_for_state: true
    state_schema: sqlmesh

When this option is enabled, the SnowflakeEngineAdapter would generate CREATE HYBRID TABLE statements for all tables created within the specified state_schema.

Describe alternatives you've considered

The primary alternative is to fork the SQLMesh repository and modify the _create_table method in sqlmesh/core/engine_adapter/snowflake.py to check if the table being created is a state table and, if so, prepend the HYBRID keyword to the CREATE TABLE statement. This is not ideal because it requires maintaining a custom version of SQLMesh and manually merging changes from the upstream repository.

Additional context

Adding native support for Snowflake Hybrid Tables would be a significant enhancement for SQLMesh users on Snowflake. It would likely improve the performance and reliability of state management, reduce the latency of state syncs, and align SQLMesh with the latest capabilities of the Snowflake platform. This would make Snowflake a more viable and officially supported option for hosting the SQLMesh state.

Thank you for considering this feature request.