Identity or Auto Increment columns in Snowflake. How to handle them while migrating

You might have heavily used Identity columns and when you want to migrate your data to Snowflake, Tada! you face the below challenges in Snowflake. 

• Consider a scenario, 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.
• Then, i will need to copy these tables into snowflake however I need to first insert the existing data (so the identity values stay the same) and then for any new inserts, the sequence will start from the higher value + 1

Steps to Migrating an Identity column data into Snowflake

• Find out the max value of the Identity column column in SQL Server, lets say its 3000
• Create a new sequence in Snowflake. You will want to set your "next value=3001" when you create the SEQUENCE, as you can't alter it later
• Create a new table in Snowflake, and while creating the table, Use the SEQUENCE as your DEFAULT for your Identity column
• Insert the data from the SQL Server to Snowflake. It should insert the data as-it-is without changing the values for the Identity columns. 
• Any new inserts will start to increment from that point

nVector

posted on 09 Dec 19