I'm trying to create a hybrid table in Snowflake using the following syntax:
CREATE HYBRID TABLE FOO
(
ID NUMBER(10,0) PRIMARY KEY NOT NULL AUTOINCREMENT START 1 INCREMENT 1,
NAME VARCHAR(8) NULL
);
This produces an error
[0A000][391411] This feature is not supported for hybrid tables: 'TRANSIENT_DATA'
Chat GPT o3-mini-high speculates that auto-incrementing columns require transient data structures under the hood, and that I need to use sequences or application logic to achieve this effect; however, that seems overly complicated, and the syntax I'm using is taken straight from Snowflake's own documentation.
Can anyone refute the AI here and come up with a better solution?
You try to create a Hybrid table on a transient database. A transient databases do not have a Fail-safe period so they do not incur additional storage costs once they leave Time Travel; however, this means they are also not protected by Fail-safe in the event of a data loss.
https://docs.snowflake.com/en/sql-reference/sql/create-database#optional-parameters
Snowflake Hybrid tables do not support transient databases. You need to create (and use) a permanent database.