How to apply column-level constraints to incremental models without full-refresh?

Help
, ,
1

The problem I’m having

I have an incremental model materialized as a table with column-level constraints defined in my schema.yml:

models:
  - name: my_incremental_model
    config:
      contract:
        enforced: true
    columns:
      - name: id
        data_type: bigint
        constraints:
          - type: primary_key
      - name: related_id
        data_type: bigint
        constraints:
          - type: foreign_key
            to: ref('other_table')
            to_columns: [id]

When I run dbt run on this incremental model, the constraints aren’t being applied to the existing table - they only get created during the initial table creation.

The context of why I’m trying to do this

What are the best practices for ensuring that new constraints get applied to an already materialized incremental model?

  • Is there a way to get dbt to apply these column-level constraints on each run without doing a --full-refresh?
  • Or do I need to use post-hook to manually add the constraints after each incremental run?

dbt-core and dbt-postgres versions I’m using

    "dbt-core>=1.9.3",
    "dbt-postgres>=1.9.0",
2

What about creating PK, FK, constraints,… on the database level? If your database engine supports that, then you can just create PK, FK when creating a table in the database (I think PostgreSQL fully supports it). Then when inserting/updating new data, if they violate the constraints, an error will be thrown.

In the end, dbt just translates your model file to pure SQL and sends it to the database engine to run it, nothing fancy, you can even run dbt-compile to get pure SQL from a data model, then run it manually. So if your normal insert/update query works with the constraint you created, then dbt will work