How to add a primary key constraints to a table using MATE

A primary key (PK) is a column in a database that uniquely identifies each row, and it cannot have null values. Having only one primary key column for each table or view in your database is essential.

A primary key constraint is a rule or condition that ensures the uniqueness and integrity of a primary key column or a set of columns within a database table. You can define primary key constraints during the initial creation of a table or add them afterward using an ALTER TABLE statement. A table can have only one primary key constraint. However, a primary key can consist of multiple columns if necessary. You can specify a primary key constraint for a table and define it on multiple columns.

In the Modelling and Transformation Engine (MATE), SQL models use SQL SELECT statements to define data transformations. These statements are then stored in SQL model files.

One approach to adding primary keys to a table is using dbt post-hooks. Add the post-hook code snippet to your dbt model. For example:

/dataops/modelling/models/sample-post-hook.sql

{{ config(post_hook='ALTER TABLE {{ this.database }}.{{ this.schema }}.{{ this.name }} ADD PRIMARY KEY (ACCOUNTID);') }}

SELECT ACCOUNTID, NAME, ...
FROM ACCOUNTS

A second approach is by adding the dbt constraints package that automatically creates referential constraints. Install the dbt constraints package in the packages.yml file in the project's dataops/modelling directory to generate the constraint in the DDL from the tests you have specified in your project. See Installing additional MATE packages for more information.

Finally, you can also convert the ALTER statement into a dbt macro that works with all the materializations. Some example

{% macro add_primary_key(pk_column_name) %}
  {% set materialization_map = {"table": "table", "view": "view", "incremental": "table"} %}

  {# config.get("materialized") is grabbing the materialized value for the model itself either from dbt_project.yml or from the config of the model #}
  {% set materialization = materialization_map[config.get("materialized")]%}

  {%- set alter_sql -%}
    alter {{materialization}} {{ this.database }}.{{ this.schema }}.{{ this.name }} add primary key {{ pk_column_name }};
  {%- endset -%}
{% endmacro %}