Is there an option to leave the default identity/autoincrement column

[Xiaoming-lw]

Currently the column with a default value like IDENTITY START 1 INCREMENT 1 ORDER is changed to a sequence. And the corresponding DDL would be a CREATE OR REPLACE TABLE .. COPY … statement. This DDL is costly.

In most cases the ID column would be keep stable. It is likely that other columns would be changed, and an alter statement is sufficient for the operation. However, if there is an identity column inside the table, the replace would always happen.

So, is there an option to leave the default identity/autoincrement column as it is, rather than replacing it with a sequence?

Thank you!

[littleK0i]

Interesting question.

Standard "autoincrement" is actually unsafe. It creates a hidden sequence, which is attached to table. When you eventually have to run CREATE OR REPLACE TABLE to apply some other change, this hidden sequence is re-created as well. "START" value can be specified manually to preserve sequence offset.

But if some additional rows are inserted into table in the moment between generation and actual execution of CREATE OR REPLACE TABLE command, sequence offset may go back in time. And you may end up with duplicates in autoincrement column.

Explicitly created SEQUENCE object does not have this problem. Table is re-created independently from SEQUENCE, so offset is preserved correctly.

That's why SnowDDL explicitly insists on using SEQUENCE instead of "autoincrement".

---

Unless Snowflake provides a way to safely replace tables with "autoincrement" columns, implementing it does not make much sense. But Snowflake allows to change DEFAULT value for columns to point from one sequence to another. It definitely works for proper sequence, but maybe it can also work when switching from implicit to explicit sequence.

If it works, we might be able to create sequence objects and switch existing tables without REPLACE. I'll test it today.

[Xiaoming-lw]

Thank you, make sense, the identity column is reset by the create ... copy , and a duplicate id is produced.

[littleK0i]

Did some tests. And switching from implicit sequence to explicit sequence does not work. On top of that, it does not seem to be possible to obtain current value of implicit sequence AND use it in CREATE OR REPLACE TABLE in the same statement. Maybe there is a hidden way to do it, but I am currently not aware of it.

So.. the best case scenario for you would be converting everything to explicit sequences. In the long term it seems the only sensible option.

As a temporary solution, you may try to set default parameter directly. It should prevent generation of CREATE OR REPLACE command due to default mismatch. But if REPLACE is generated for any other reason, you're still going to experience auto increment reset.

For converter, take this: https://github.com/littleK0i/SnowDDL/blob/master/snowddl/converter/table.py#L125-L140

And replace with something like:

            if c["default"]:
                if str(c["default"]).upper().endswith(".NEXTVAL"):
                    col["default_sequence"] = self._normalise_name_with_prefix(str(c["default"])[:-8])
                else:
                    col["default"] = str(c["default"])

In converted YAML config for table it should look like this:

columns:
  id:
    type: NUMBER(38,0)
    default: "IDENTITY START 1 INCREMENT 1 ORDER"

[Xiaoming-lw]

Looks like it should be a best practice of using snowflake: do not create an autoincrement identity column . :)