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¶
- Clouds and regions
Hybrid tables are only available in Public Preview in select public Amazon Web Services (AWS) regions:
Cloud Region
Cloud Region ID
US West (Oregon)
us-west-2
US East (Ohio)
us-east-2
US East (N. Virginia)
us-east-1
Canada (Central)
ca-central-1
South America (Sao Paulo)
sa-east-1
Europe (London)
eu-west-2
EU (Ireland)
eu-west-1
EU (Paris)
eu-west-3
EU (Stockholm)
eu-north-1
EU (Frankfurt)
eu-central-1
Asia Pacific (Tokyo)
ap-northeast-1
Asia Pacific (Seoul)
ap-northeast-2
Asia Pacific (Osaka)
ap-northeast-3
Asia Pacific (Mumbai)
ap-south-1
Asia Pacific (Singapore)
ap-southeast-1
Asia Pacific (Sydney)
ap-southeast-2
Hybrid tables are not available in Azure or Google Cloud Platform (GCP).
Hybrid tables are not available in SnowGov Regions.
Hybrid tables are not available to 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 belonging 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 forON_ERROR
. SettingON_ERROR=SKIP_FILE
returns an error. For more information, see Loading data.- Data size
You are limited to storing 500GB of data in hybrid tables in your account. See Quotas and Throttling for more information.
- 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.
- Indexes
While secondary indexes are supported, the following index features are not currently supported:
Adding a column to an index.
Creating an index on column(s) that have semi-structured data types (ARRAY, OBJECT, VARIANT).
You cannot modify 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.
- 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 multiple 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
.
- 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. By default, these quotas are enforced on your Snowflake account at the account level, and such quotas are shared by all of the databases in your account.
Quota
Default
Notes
Hybrid storage
500GB
This quota controls how much data you can store in hybrid tables. 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 1,000 operations per second
This quota controls the rate at which you can read from and write to hybrid tables. You should be able to achieve up to 1,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.
If you receive an error or throttling occurs because of a quota limit, modify your workload to stay under the quota limit. If you have a workload that requires a higher storage or throughput quota, contact Snowflake Support to discuss a potential increase to the quota. For workloads with known consistent capacity requirements, you can isolate individual databases and set up quota allocations that are separate from the rest of your Snowflake account.
- Throughput
You can execute up to approximately 1,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.
- 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.
- 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.