can dbt indicate primary keys

The problem I’m having

environment: Postgres
when I using “dbt run” to the table,
dbt will delete old table (I already dbt run before and alter PK).
Is there any way to indicate PK in models?

The context of why I’m trying to do this

I using airbyte to sync db table, when data sync it will run dbt and remove old table then create new one.

What I’ve already tried

I know dbt can setting index in model (see: Postgres configurations | dbt Developer Hub)

Do you have v1.5+? Have you tried dbt contract/constraints? dbt’s example yml:

  - name: dim_customers
        enforced: true
      - name: id
        data_type: int
          - type: not_null
          - type: primary_key

Another idea would be to add the needed alter table XXXXX add primary key... statement as a post-hook to your model. Then it’d get executed with every dbt run.

Otherwise, you can indicate the functionality of a PK in the yml by adding unique/not null tests (or dbt_expectations expect_compound_columns_to_be_unique for a multi column PK)

Thanks alyk
Both constraints and post-hook are work!

1 Like

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.