What’s the prob, here?
Resetting or “reseeding” an identity column should be a trivial task for data engineers. You’ll most likely be wanting to reset your identity columns after a truncate (you know, to avoid the PK of 18758392758 after only day 27 of your nightly trunc and reloads) … and discover that Snowflake doesn’t actually reset that $%&@ seed.
It’s worth calling out that Snowflake creates Autoincrement / Identity columns as sequences behind the scenes. However, they do not provide a way to modify or re-create that sequence to reseed it. So we’re left with some work arounds.
Hopefully this saves you a few hours of research.
What’s my options?
1.) TRUNCATE your identity column. Live with your giant Snowflake identity surrogate key. Move on.
The snowflake equivalent to an integer column is a NUMBER(38,0). So 38 decimal places should allow plenty of growth. Just not ideal.
2.) Use a sequence.
Since an Identity column is just a sequence, the natural solution is to just create your own sequence with a naming convention of the table. Unlike the sequence that the Identity column creates, a user defined identity can (kinda) be reset .. with a little help of drop and recreate logic.
There’s a big problem with this approach, however. The sequence that is tied to the table is hard coded to the database that it was created in. The problem this creates is when you clone your database to create other environments (ie. DEV_DEMO, TEST_DEMO, etc), the sequence is still tied to your original source database. Not ideal for maintaining multiple environments within your account.
3.) Recreate the table.
The best option is to ultimately recreate the table. Because foreign keys aren’t enforced in Snowflake, recreating your tables won’t cause annoying dependency issues.
What’s the catch?
Maintaining DDL scripts to create your tables after each truncate just isn’t realistic. And dynamic SQL isn’t yet available in Snowflake.
The solution is to create a generic “execute SQL” stored proc, and dynamically pass in the current DDL from the get_ddl function. This mimics dynamic SQL. Here’s the code.
End code is easy and allows for safely cloning tables & databases without dependencies on sequences across objects.
The one downside to this approach is re-creating a table requires you to re-create permissions associated with that table. You can generally get around this with using “ON FUTURE” for setting permissions. That way your permissions will automatically be re-mapping on a drop and re-create.
Conclusion
This was way too long for something that should be an easy task. Hopefully Snowflake fixes this soon.
Data Five!
