[Feature] Allow snapshots to have unique_key as a list

[AGPapa]

Is this your first time submitting a feature request?

• I have read the expectations for open source contributors
• I have searched the existing issues, and I could not find an existing issue for this feature
• I am requesting a straightforward extension of existing dbt functionality, rather than a Big Idea better suited to a discussion

Describe the feature

This a clone of dbt-labs/dbt-adapters#181

Right now the 'unique_key' for a snapshot must be a single column or an expression. If a user needs a combination of columns to be unique then they can use the expression to concatenate multiple columns together how they like.

Ideally users would be able to enter a list of columns, just like they can do for incremental models.

Describe alternatives you've considered

I considered adding additional parameters instead of changing the existing unique_key parameter. I think using the existing parameter is best because it's the most consistent with how incremental tables work.

Who will this benefit?

Snapshot users who work in Postgres or Redshift can take advantage of performance optimizations in their databases with this change.

There are performance benefits from using multiple columns rather than an expression. In Postgres users would be able to pass in partition keys or indexed columns to speed up their snapshots, without needing to make an additional expression index. In Redshift users can pass in a separate distribution key to also get performance benefits.

These benefits can't be realized unless dbt allows the unique key to natively be a combination of columns, rather than a concatenation/combination expression.

Are you interested in contributing this feature?

Yes, I have a PR in dbt-adapters and in dbt-core coming shortly

Anything else?

No response

[graciegoheen]

Thanks so much for opening - agree that ideally users would be able to enter a list of columns for unique_key for snapshots, just like they can do for incremental models.

Will queue up your PRs for review from our team

[ikhudur]

@graciegoheen Any chance the PR will be merged soon?

[hope-wat]

@graciegoheen can we call this one done since it moved over to adapters and was pulled into main

[FishtownBuildBot]

Opened a new issue in dbt-labs/docs.getdbt.com: dbt-labs/docs.getdbt.com#6343