[Feature] dbt manage multiple fields as primary key

[salimmoulouel]

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-bigquery functionality, rather than a Big Idea better suited to a discussion

Describe the feature

BigQuery's Unique Approach to Primary Keys

In BigQuery, primary keys aren't typically defined directly within the table schema as in traditional databases. Instead, a separate constraint is utilized to establish a composite primary key after the table creation process.

Since dbt usually adds "PRIMARY KEY" after each column name for primary keys, this method isn't compatible with BigQuery. To address this, we need to append the following clause at the end of the table declaration:
PRIMARY KEY (field1, field2, ...)

I have an idea for a potential solution, but before implementing it, I need assurance of timely code review. I haven't received any feedback on my last contribution, which can be found here: #1122.

Describe alternatives you've considered

No response

Who will this benefit?

No response

Are you interested in contributing this feature?

yes, write the adapted code

Anything else?

No response

[dbeatty10]

I don't think we'd be likely to convert primary key declarations from the column level to the model level for dbt-bigquery.

If we did, we'd probably do it within dbt-core so it applied to all adapters and not just dbt-bigquery.

So for now, I'm going to close in this favor of discussing the possibility of making an update to dbt-docs instead:

• [Feature] Display the model-level constraints in the documentation generated by dbt dbt-docs#507

I'll see you over in dbt-labs/dbt-docs#507 to discuss further!