Skip to main content

Snowflake: Hybrid Tables

info

Supported by: Snowflake

Depot can back an object schema with a Snowflake hybrid table instead of a standard table. Hybrid tables use row-based storage with an enforced primary key, which suits workloads that need fast single-row lookups and writes.

Enabling hybrid tables​

Set the snowflake.sql.tableType extension on the object schema to HYBRID. When the extension is absent the table type defaults to STANDARD (the existing behaviour, a regular Snowflake table).

PetInventory:
  type: object
  extensions:
    snowflake.sql.tableType: HYBRID
  properties:
    name:
      type: string
    count:
      type: integer

This emits a CREATE HYBRID TABLE statement instead of CREATE TABLE.

Primary keys​

Hybrid tables require a primary key, which Depot adds automatically:

  • object tables use id;
  • history tables use id + version (an id is not unique across history versions).

The constraint is named ${tableName}_PK. When a schema is HYBRID and history is enabled, the history table is created as a hybrid table too.

Switching table type​

Snowflake cannot change a table's type in place, so switching a schema between STANDARD and HYBRID requires a version bump. Depot performs the switch by recreating the table: it creates a new table of the target type, copies the object and history rows across (preserving version numbers and the full history), and soft-deletes the old table. The recreate is migration-aware, so the same version bump may also add or remove columns and use a custom expression migration to populate new columns.

Limitations​

  • Hybrid tables are not supported for Iceberg / S3 Tables datasets; declaring HYBRID on such a dataset is rejected at deploy time.
  • A new required column added during a type switch must be given a value by a migration expression (otherwise the migration is rejected); make the column nullable if no value is available.