Unsupported features and limitations for hybrid tables

The following guidance on unsupported features and limitations applies to hybrid tables, and is subject to change.

Unsupported features

Hybrid tables do not support:

Limitations

Cloning

Although cloning is not supported for hybrid tables, you can clone databases and schemas that contain hybrid tables by using the IGNORE HYBRID TABLES parameter in the CREATE <object> … CLONE statement.

Clouds and regions

Hybrid tables are available in all commercial Amazon Web Services (AWS) regions.

Note the following restrictions:

  • Hybrid tables are not available in Azure or Google Cloud Platform (GCP).

  • Hybrid tables are not available in U.S. SnowGov Regions.

  • Hybrid tables are not supported in trial accounts.

  • If you are a Virtual Private Snowflake (VPS) customer, contact Snowflake Support to inquire about enabling hybrid tables for your account.

Clustering keys

Clustering keys are not supported in hybrid tables. For more information, see Clustering Keys & Clustered Tables. Data in hybrid tables is ordered by primary key.

Consistency

By default, hybrid tables use a session-based consistency model where read operations in the session return the latest data from write operations in the same session. There might be some staleness (less than 100ms) for changes made outside of the session. To avoid staleness, set READ_LATEST_WRITES = true at the statement or session level. Note that this might incur some latency overhead of a few milliseconds.

Constraints

While primary key, unique, and foreign key constraints are enforced in hybrid tables, the following limitations apply:

  • Constraints can only be defined at table creation.

  • Constraints are enforced at the row level.

  • Constraints are not enforced at the statement or transaction level (that is, deferred constraints).

  • You cannot alter a column to be unique.

The following additional limitations apply to foreign keys:

  • Foreign key constraints are supported only among hybrid tables that belong to the same database.

  • The referenced table from a foreign key constraint cannot be truncated as long as the foreign key relationship exists.

  • Foreign key constraints do not support partial matching.

  • Foreign key constraints do not support deferrable behavior.

  • Foreign key constraints only support RESTRICT and NO ACTION behaviors for DELETE and UPDATE operations.

COPY

When you load a hybrid table with the COPY INTO command, ABORT_STATEMENT is the only option that is supported for ON_ERROR. Setting ON_ERROR=SKIP_FILE returns an error. For more information, see Loading data.

Data size

You are limited to storing 1 TB of data in hybrid tables per Snowflake database. See Quotas and Throttling for more information.

Data types not supported in indexes

Columns with geospatial data types (GEOGRAPHY and GEOMETRY), semi-structured data types (ARRAY, OBJECT, VARIANT), and vector data types (VECTOR) are not supported as either PRIMARY KEY columns (which are automatically indexed) or explicitly indexed columns. (Hybrid table columns support these data types as long as the columns are not indexed.)

The TIMESTAMP_TZ data type (or a TIMESTAMP data type that resolves to TIMESTAMP_TZ) is not supported for columns that are indexed using UNIQUE, PRIMARY KEY, and FOREIGN KEY constraints. However, TIMESTAMP_TZ is supported for secondary indexes.

See also Secondary indexes.

DML commands

When using DML commands to change a small number of rows, optimize performance by using INSERT, UPDATE, or DELETE statements instead of MERGE.

Loading data

The CREATE TABLE … AS SELECT command is recommended for bulk-loading data into a hybrid table. This command is optimized for performance, and is approximately 10 times faster than other commands, such as COPY or INSERT INTO … SELECT, when you are loading millions of records.

See Loading data for more information.

Native applications

You can include hybrid tables in a Snowflake Native App. However, hybrid tables cannot be shared from the provider to the consumer. Native Apps can create hybrid tables in the consumer account, and they can read from and write to those hybrid tables. You can also expose hybrid tables to application roles so that they can be queried directly by consumer users.

You cannot create a hybrid table in a provider account, nor can you include that hybrid table in a view that is shared through the Native App.

Periodic rekeying

You cannot use hybrid tables if your Snowflake account is enabled to use periodic rekeying. If periodic rekeying is enabled in your account and you want to use hybrid tables, you must use an ALTER ACCOUNT command to set the PERIODIC_DATA_REKEYING parameter to FALSE.

Persisted query results

Queries against hybrid tables do not use the results cache, as defined with the USE_CACHED_RESULT parameter. See Using Persisted Query Results.

Quotas and throttling

Your usage of hybrid tables is restricted by quotas in order to ensure equitable availability of shared resources, ensure consistent quality of service, and reduce spikes in usage.

Quota

Default

Notes

Hybrid storage

1 TB per Snowflake database

This quota controls how much data you can store in hybrid tables. This limit applies only to active hybrid table data in the row store; it does not apply to object storage. If you exceed the storage quota, write operations that add data to any hybrid tables are temporarily blocked until you bring your hybrid storage consumption back under quota by deleting data.

Hybrid table requests

Approximately 2,000 operations per second, per Snowflake database

This quota controls the rate at which you can read from and write to hybrid tables. You should be able to achieve up to 2,000 operations per second against hybrid tables for a balanced workload consisting of 80% point reads and 20% point writes. To monitor throttling, see the example in AGGREGATE_QUERY_HISTORY view.

Databases that contain hybrid tables

100 total per Snowflake account, and no more than 10 databases added within a one-hour window

This quota controls how many databases within your Snowflake account may contain hybrid tables. If you exceed this quota, you will be unable to create a hybrid table in a new database without dropping all hybrid tables from an existing database. If necessary, you can request help from Snowflake Support to increase the quota.

Throttling can be caused by a combination of factors that result in too many read and write requests being sent to the hybrid table storage provider:

  • Too many read requests can occur because of poorly optimized queries or because of a large, aggressive workload with very high query concurrency.

  • Too many write requests can occur because the bulk-load path wasn’t chosen when a table was loaded or because the workload consists of too many concurrent write operations.

If you receive an error or throttling occurs because of a quota limit, contact your system administrator or DBA to look into the overall Unistore workload; possibly it can be modified to avoid exceeding the quota. DBAs can contact Snowflake Support to evaluate query performance and quota usage. For some workloads, you might need to initiate a quota increase by requesting help from the support team.

Replication

Replication of hybrid tables is currently not supported.

Secondary indexes

The following secondary index features are not supported:

  • Adding a column to an index.

  • Modifying an index on an existing hybrid table.

To use a secondary index on a hybrid table, you must use a role that is granted the SELECT privilege on the table. If you only have access to objects other than the hybrid table itself, you will not be able to use secondary indexes.

TIMESTAMP columns are supported. However, comparisons (for example, WHERE predicates) against TIMESTAMP columns that are part of a composite key in a base table or index are not currently supported and cause an error.

For more information about secondary indexes, see Adding indexes to a hybrid table.

Throughput

You can execute up to approximately 2,000 operations per second against hybrid tables in your account for a balanced 80%/20% read/write workload. If you exceed this limit, Snowflake might reduce your throughput. See Quotas and Throttling for more information.

Time Travel

Time Travel queries are supported against hybrid tables with the following limitations:

  • Only the TIMESTAMP parameter is supported in the AT clause. The OFFSET, STATEMENT, and STREAM parameters are not supported.

    • The value of the TIMESTAMP parameter must be the same for all tables that belong to the same database. If the tables belong to different databases, different TIMESTAMP values may be used.

    • Cloning is not supported in general for hybrid tables so you cannot specify the AT clause in CREATE TABLE … CLONE.

  • The BEFORE clause is not supported.

  • The UNDROP TABLE command, which depends on Time Travel, is not supported.

Transactions

For hybrid tables, the transaction scope is the database in which the hybrid table resides. All the hybrid tables referenced in a transaction must reside in the same database; standard Snowflake tables referenced in the same transaction may reside in different databases.

Transient schemas and databases

You cannot create hybrid tables that are temporary or transient. In turn, you cannot create hybrid tables within transient schemas or databases.

Tri-Secret Secure

You cannot use hybrid tables if your Snowflake account is enabled to use Tri-Secret Secure. Prior to using hybrid tables, verify whether your Snowflake account is enabled for Tri-Secret Secure by contacting Snowflake Support.

UNDROP

UNDROP is not supported for hybrid tables. Additionally:

  • UNDROP SCHEMA and UNDROP DATABASE commands succeed for entities that contain hybrid tables, but those hybrid tables and their associated constraints and indexes cannot be restored.

  • The DELETED column in TABLES view displays the time of deletion as the UNDROP time of the parent entity.

  • ACCESS_HISTORY view contains an entry for DROP/UNDROP of the parent entity, but no entries for hybrid tables.

Language: English