Help requested - constraints not appearing in generated sql?

[piekobicha]

The problem I’m having

Using dbt-core 1.8.0 and dbt-bigquery 1.8.2, I’m trying to enable a single-column primary key on table A and a foreign key on table B referring back to the PK on table A. I know there are risks associated with BQ not enforcing PKs, but the benefits should hopefully outweigh the risks.

I’m definitely a new dbt user. I’ve been reading docs and searching online and can’t seem to figure out why constraints aren’t showing up in the generated SQL. I don’t see any compile-time warnings or errors, but whatever I’ve tried the generated SQL doesn’t reflect the added configuration.

The context of why I’m trying to do this

PK/FK relationships were added to BQ recently and I would like to take advantage of them to optimize queries. It looks like dbt-bigquery also supports defining these relationships, although I can’t seem to figure out how to make it work.

What I’ve already tried

Keeping things simple, I want everything to be a table (no views etc). In dbt_project.yml:

models:
  my_schema:
    +materialized: table

In models/schema.yml - with models/table_A.sql and models/table_B.sql files.

models:
  - name: table_A
    config:
      contract:
        enforced: false
    constraints:
      - type: primary_key
        columns: [A_id]
    columns:
      - name: A_id
        data_type: STRING

  - name: table_B
    config:
      contract:
        enforced: false
    constraints:
      - type: foreign_key
        columns: [A_id]
        expression: "schema.table_B (A_id)"
    columns:
      - name: A_id
        data_type: STRING

I have “contract: enforced” set to “false” since BQ doesn’t actually enforce constraints - I think that should be right?

I also tried adding constraints directly in the SQL file, although the syntax seems off - in schema.yml constraints are not child of config.

{{
    config(
        constraints={
            'type': 'primary_key',
            'columns': ['A_id']
        }
    )
}}

Last effort would be to add a post-hook to add constraints after the tables are created, but I’m trying to learn how to do this the “right” way… The output of “debug” mode doesn’t tell me a lot, is there a command that does something like dump the fully parsed config taking into account all schema/property/dbt_project and SQL files? Maybe this would help me figure out where all my configuration options are going that seem to not be getting picked up.

Any help appreciated. Thank you!

[a_slack_user]

I believe you need to set contract enforcement (which is a dbt feature, nothing to do with the database) to true for constraints to work. This does enable some other features of contracts that you may not want.

_{Note: @Mike Stanley originally posted this reply in Slack. It might not have transferred perfectly.}

[piekobicha]

thank you!!

PK and FK are generated if I explicitly set config.contract.enforced = true for each table. Doesn’t seem to work if I omit it (maybe user error on my part?). This has the unwanted side effect of forcing me to enumerate every column in the model declaration, but it looks like there are tools to help auto-generate the yaml from the model.

Reading the docs for “enforced”, it does seem to conflate “[ensuring] that your model’s returned dataset exactly matches the attributes you have defined in yaml” with “generate constraints.” Either way I was misunderstanding it the first time, so again thank you!

[a_slack_user]

Hello, does it work in incremental models? Or do we have to full refresh to recreate with the enforced constraint?

_{Note: @David Boudart originally posted this reply in Slack. It might not have transferred perfectly.}