Primary Key Inference

Datafold supports composite primary keys, meaning that you can assign multiple columns that make up the primary key together.

Primary key inference

Datafold looks for the primary key of each dbt model in this order and uses the first match:

Model-level meta with the primary key tag
Column-level meta with the primary key tag
Column-level tags
dbt uniqueness tests
The unique_key config (for incremental models and snapshots)
Column-name guesses: id, pk, pkey, or <model_name>_id
dbt primary_key constraints

The primary key tag defaults to primary-key. You can change it with the Primary key tag setting in your dbt CI integration.

Metadata

The first option is setting the primary-key key in the dbt metadata. There are several ways to configure this in your dbt project using either the meta key in a yaml file or a model-specific config block. You can list the primary key columns at the model level:

models:
  - name: sales
    meta:
      primary-key: [order_no, order_line]

Or mark each column:

models:
  - name: users
    columns:
      - name: user_id
        meta:
          primary-key: true
    ## for compound primary keys, set all parts of the key as a primary-key ##
    # - name: company_id
    #   meta:
    #     primary-key: true

Inferred

If the primary key isn’t provided explicitly, Datafold will try to infer a primary key from dbt’s uniqueness tests. If you have a single column uniqueness test defined, it will use this column as the PK.

models:
  - name: users
    columns:
      - name: user_id
        tests:
          - unique

Also, model-level uniqueness tests can be used for inferring the PK.

models:
  - name: sales
    columns:
      - name: col1
      - name: col2
      ...
    tests:
      - unique:
          column_name: "col1 || col2"
          # or
          column_name: "CONCAT(col1, col2)"
      # we also support dbt_utils unique_combination_of_columns test
      - dbt_utils.unique_combination_of_columns:
          combination_of_columns:
            - order_no
            - order_line

Keep in mind that this is a failover mechanism. If you change the uniqueness test, this will also impact the way Datafold performs the diff. If no uniqueness test is found, Datafold uses the unique_key config of incremental models and snapshots, then columns named id, pk, pkey, or <model_name>_id.

INTRODUCTION

DATA PLATFORM MIGRATIONS

DATA DIFFS

CI/CD TESTING

DATA MONITORS

DATA EXPLORER

INTEGRATIONS

CLASSIC DEPLOYMENT

SECURITY

SUPPORT

Primary Key Inference