Snowflake - add sequence to an existing column

Question

I have to migrate a SQL server database to Snowflake, most of my dimension tables have an identity column as PK, these columns are then referenced across multiple facts tables.

I am planning on copying these tables in snowflake however I need to first insert the existing data (so the identity values stay the same) and then alter my tables to add a sequence to my PK, the sequence will start from the higher value + 1.

I am a bit stuck as it does not seem to be possible to alter an existing column and add a sequence to it, is there any work around or best practice I should be following?

Cheers

Answer 1

You'll want to create a SEQUENCE and then reference that when you create the table with the SEQUENCE as your DEFAULT for your primary key. This is in the documentation here:

https://docs.snowflake.net/manuals/sql-reference/sql/create-sequence.html https://docs.snowflake.net/manuals/sql-reference/sql/create-table.html#optional-parameters

And then load your data with the data specified for that column, so that it'll populate with the current values. You will want to set your "next value" when you create the SEQUENCE, as you can't alter it later.