Add support for identity columns

[flvndh]

Describe the feature

Now that identity columns are GA, it would be great to be able to specify the identity column as part of the model configuration.

Describe alternatives you've considered

• monotonically_increasing_id()
• row_number()
• rank OVER
• hash()
• md5()

Who will this benefit?

Every dbt-databricks user who wants to generate surrogate keys.

Are you interested in contributing this feature?

Why not 😊

[mi-volodin]

I would vote for this. As it is a natural thing to use while creating DWH cores with entity-oriented normalized storage.

[github-actions]

This issue has been marked as Stale because it has been open for 180 days with no activity. If you would like the issue to remain open, please remove the stale label or comment on the issue.

[bayees]

Bump. Still relevant

[skywalker0618]

Bump, this will be very helpful.

[mi-volodin]

@flvndh have you found the way to circumvent this for now?

Like using contract: [enforced: true] plus bigint generated [always|by default] as IDENTITY as a type?

I think I can look into it, but I want to collect expectations.

UPD. @bayees @skywalker0618 same question for you.

[mi-volodin]

Looked into the code. Seems the answer is "no".

Enforce contract only asserts the column type, but not use the type in table creation (sad).
So making an identity support looks like a nightmare for now...

1. I found no way to gracefully add identity into an existing table. Delta lake doesn't support this (maybe I am wrong). Though that would be the current "dbt style" of handling such situations - materialize first, then adjust properties.
2. There's either no way to make the identity column a part of a "SQL" model, because it is required for Delta (as for many other datastores) that identity column is omitted from insert statement.

So I see two options:

1. Break the "dbt style" here and make normal CREATE TABLE (...) with column specification from the yaml file. That would be actually so cool 🔥 that I wonder what prevented us from having this before. Specifying table by data doesn't always work.
2. Do the current workaround for that kind of things. Since it happen on first materialization only: create a copy of the table with all properties, but + identity column, copy the data. Sounds not really smart, but if I understand correctly, that is exactly what happens today with some schema changes and migrations.

[misja-pronk]

bump

[alaturqua]

+1

[oberalppaolof]

I support this feature!

[g-zukowski]

Agree, it would be helpful to set up IDENTITY column in the model.

[adamgrbac]

Would this now be possible with materialization v2? Previously it seemed like the constraint was the usage of CTAS, however with the v2 initial create table step and subsequent insert, this should be able to be configured with an extra column config right? This would be a huge win for creating surrogate keys in our star schemas.