Autoincrement Table Column Fails on Creation

[nickshffer]

Describe the bug Seems as though you cannot create a column with an autoincrement column.

I have a table that looks like:

# baz.py
columns:
  id:
    type: NUMBER(38,0) NOT NULL
    default: IDENTITY START 1 INCREMENT 1 ORDER
...

...that errors on creation.

I believe the error is caused here because the code assumes you can pass whatever is in the default param as-is, but the Snowflake docs indicate that autoincrement does not follow that pattern as noted here

Note DEFAULT and AUTOINCREMENT are mutually exclusive; only one can be specified for a column.

Expected behavior The table should be created with an autoincrement column.

Attach log

Here is a snippet of stderr

...
2024-05-27 22:42:02.740 - WARNING - Resolved TABLE [FOO.BAR.BAZ]: ERROR
(
    message   =>  SQL compilation error:
syntax error line 3 at position 41 unexpected 'START'.
    errno     =>  1003
    sqlstate  =>  42000
    sfqid     =>  01b49f3e-0604-e22c-000c-f88308578f16
    sql       =>  CREATE TABLE "FOO"."BAR"."BAZ"
(
      "ID" NUMBER(38,0) DEFAULT IDENTITY START 1 INCREMENT 1 ORDER NOT NULL
...

Note that the offending SQL command succeeds if you remove "DEFAULT", as in:

...
"ID" NUMBER(38,0) IDENTITY START 1 INCREMENT 1 ORDER NOT NULL
...

Also, note that baz.py was created by using the built-in table converter; I have not explored other ways to implement the autoincrement column (if there is a better way perhaps this is an issue with the function converter?).

Happy to make a PR if that would be helpful!

[nickshffer]

Self-closing, I missed the note in the docs about "Anonymous auto-increment is not supported."

[littleK0i]

As far as I know, defining sequence explicitly is the only way to preserve its value after CREATE OR REPLACE statements on the main table. Otherwise your autoincrement value might break, especially with high rate of inserts.

So... yes, that was done on purpose.