[Feature] Add Constraints to Seeds

[cboethigtrellance]

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

As of DBT Core 1.8 Model contracts and constraints are not supported for seeds

As requested in another feature requesting contracts for seeds there was no reason given other than setting data types for the columns which the column types config already provides.

Currently with seeds there are no ways to define constraints. Most data platforms do not ENFORCE constraints specified in the documentation. Like models, I believe seeds should be able to specify constraints such as primary & surrogate key and not null.

Adding constraints to a seed file could either follow the standard of model contracts

seeds:
  - name: seed_name
    config:
      schema: seed_schema
      tags: seed
      column_types:
        seed_id: int
      contract:
        enforced: true
    columns:
      - name: seed_id
        constraints:
         - type: primary_key

Or allow the configuration of constraints outside the declaration of a contract

seeds:
  - name: seed_name
    config:
      schema: seed_schema
      tags: seed
      column_types:
        seed_id: int
      constraints:
        seed_id: [primary_key, not_null]

This could apply at the table level, as well as the column level

Describe alternatives you've considered

No response

Who will this benefit?

This will benefit seeds by allowing defined constraints on individual columns or the table itself for data governance to determine relationships between primary, surrogate keys and not nullable columns because seeds are materialized as tables

Are you interested in contributing this feature?

No response

Anything else?

No response

[dbeatty10]

Thanks for proposing this @cboethigtrellance!

We're planning to continue supporting constraints only for models at this time (in conjunction with model contracts) and not extend them to other resource types like seeds or snapshots. So I'm going to close this as "not planned". See below for a couple alternatives instead.

dbt_constraints package

If your database is Snowflake, PostgreSQL, Oracle, Redshift, or Vertica, the dbt_constraints would be something to take a look at. (If you are not using one of those databases, see here for notes how to add support for your database.)

Example:

packages.yml

packages:
  - package: Snowflake-Labs/dbt_constraints
    version: [">=1.0.0", "<1.1.0"]

seeds:
  - name: seed_name
    columns:
      - name: id
        tests:
          - not_null

Install the package and execute the seeds (which will automatically add constraint based off the not_null test above):

dbt deps
dbt seed

Post hook to add constraints

Alternatively, you can use a post-hook to add constraints as well.

Example:

dbt_project.yml

seeds:
  my_project:
    seed_name:
      +post-hook:
        - "alter table {{ this }} alter column id set not null"