Can I create an auto-incrementing ID in dbt?

[claire]

TL:DR: No, it’s not possible to create an auto-incrementing ID in dbt.

In fact, auto-incrementing keys in general are a bad idea. The alternative is to generate a surrogate_key using a hash, and test the uniqueness of this key.

Auto-incrementing IDs in the time of ELT

This is good context for anyone that doesn’t come from this background!

A long time (a few years) ago, SQL statements that were used for ETL usually had two parts – the first created the table using DDL (data definition language), and the second inserted/updated/deleted/copied data using DML (data manipulation language).

Here’s an example that should works on Redshift:

create table if not exists analytics.customer (
  customer_key     integer identity(1,1),
  customer_id      integer not null,
  name             varchar(25) not null,
  address          varchar(25) not null,
  created_at       timestamp not null default current_timestamp
);

copy analytics.customer (
  customer_id,
  name,
  address
) from 's3://<your-bucket-name>/load/key_prefix'
credentials 'aws_access_key_id=<Your-Access-Key-ID>;aws_secret_access_key=<Your-Secret-Access-Key>'
;

This approach was often required because the same engineers writing the code to transform data, were also loading it into their warehouse from an S3 bucket. These days, it’s a good idea to use Stitch or Fivetran to get data into your warehouse instead.

In this example there’s an auto-incrementing key in this query (customer_key). Often, an auto-incrementing key was used to create a primary key for a table, especially when there can be duplicate records for the same “natural key” (in this case, if a customer changes address, there will be two records with the same customer_id, but different customer_keys.

Does dbt support auto-incrementing keys?

In a word: no.

Behind the scenes, if you materialize a model as a table, dbt uses a create table as statement to create that table.

Let’s say that you already have your customers table in your warehouse, now you can run a query like this to create the analytics version of customers:

create table analytics.customers as (
    select
        id,
        name,
        address,
        created_at
    from raw_jaffle_shop.customers
)

^ This is kind of a silly example because in all likelihood, you’ll be doing some transformation along the way, but it’s just an example

We can do this because in dbt we assume that data is already loaded into your warehouse, this is why we refer to dbt as being the T in ELT (extract, load, transform) process

The create table as syntax does not support auto-incrementing keys, since you don’t explicitly define the columns. Similarly, it doesn’t support column default values, constraints, or encoding.

Why use the create table as syntax then?

create table as statements are so powerful because they allow you express your transformations as select statements – there’s no need to run two separate DDL & DML statements. In a dbt project, every model is a select – dbt handles wrapping the select statement in the appropriate DDL.

Defining every model as a select enables anyone that knows how to write these queries to own the transformation layer. We frequently see analysts with no prior knowledge of DDL and DML get up and running with dbt really quickly as a result!

Why do you say that auto-incrementing keys are a bad idea?

Auto-incrementing keys make it impossible (or at least very difficult) to maintain separate development and production versions of your transformations. This is because an auto-incrementing key is not idempotent.

If you were to create a development environment (say, a separate schema named dev_claire), and re-run all your transformations, the results of your auto-incrementing key would be different to your production environment. Maintaining a robust development environment is critical for a high-functioning data team, as it allows you to work quickly and test changes before deploying them.

But I need auto-incrementing keys to track versions of a record!

One of the primary use-cases for auto-incrementing keys is to generate a unique, “surrogate key” in a table, where they may be multiple versions of the same “natural key”.

For example:

• Your customer table has a customer_key, which is a unique, auto-incrementing key. This is required because your customer can change address over time.
• Your order table has a foreign key to customers (also customer_key). Since you’re using customer_key, you can tell which version of the customer record was valid when the order was placed.

If you’re using an auto-incrementing key on top of mutable source data, instead consider snapshotting your source data and building models on top of your snapshots.

OK, but I need a primary key for my model, what are my other options?

Yup, I agree with you there! We advocate for having a primary key (i.e. unique and not null) on every model.

Every model should have some “grain” to it, i.e. what one record represents. For example:

• customers has one record per customer
• subscription_periods has one record per subscription, per valid period
• ad_spend_by_campaign_by_day has one record per campaign, per day

We use the surrogate_key macro from dbt-utils to generate a primary key based on the grain of a model. For our ad_spend_by_campaign_by_day example:

select
  {{ dbt_utils.surrogate_key(
      'campaign_name',
      'date_day'
  ) }} as ad_spend_id,
  campaign_name,
  platform,
  date_day,
  spend,
  ...
  

Behind the scenes, this macro hashes the combination of columns, which means that your resulting id will always be the same for a particular record. Or, put another way, the generated ad_spend_id is now idempotent.

So how do I force my surrogate key to be unique/not null?

In DDL statements, when you define a table first, you can add these column constraints to your table definition:

create table if not exists analytics.customer (
  customer_id      integer not null unique,
  ...
)

Not so with the create table as syntax! So how can you enforce this constraint?

(As an aside, did you know that Redshift doesn’t actually enforce uniqueness constraints? Check out the code at the end!)

In dbt, rather than enforcing constraints on the table definition and hoping the transformation fails, we instead state our constraints as assumptions about the results of our select statements, and then test those assumptions. Here’s a test to make sure that our ad_spend_id is unique and not null:

version: 2

models:
  - name: ad_spend_by_campaign_by_day
    columns:
      - name: ad_spend_id
        tests:
          - unique
          - not_null

Behind the scenes, these tests get compiled to queries that return an error if the result > 0.

-- unique test
select count(*)
from (
    select
        ad_spend_id

    from analytics.ad_spend_by_campaign_by_day
    where ad_spend_id is not null
    group by ad_spend_id
    having count(*) > 1
) validation_errors

-- not_null test 
select count(*)
from analytics.ad_spend_by_campaign_by_day
where ad_spend_id is null

We can run these tests when writing code (since we can now maintain a separate development environment thanks to our idempotent primary keys), before merging it, and after our transformations have run in production. Basically – we’re going to find out whenever this assumption doesn’t hold true anymore!

This pattern of testing is much more powerful than column-level constraints, as you can define custom tests for any constraint that can be turned into a query. It’s also much more readable IMO.

Final thoughts

There are some teams using dbt who have tried to add auto-incrementing keys to their dbt models. If you go down this route, implementing dbt will likely feel like paddling upstream. To quote myself ():

dbt is definitely a different approach to data transformation, and it could potentially feel a little unusual as you first get started. We encourage those who are just picking it up to find ways to work with it instead of attempting to hack around it: you may find that your code looks a little different than you’re used to, but we think you’ll be happier for it over the long run.

---

Side notes

This shouldn’t execute on Redshift, yet, it does. Good one Redshift!

$
create table jaffle_shop_dev.test_uniqueness (
  id integer unique
);

insert into jaffle_shop_dev.test_uniqueness (
    select 1
    union all
    select 1
);

select * from jaffle_shop_dev.test_uniqueness;
>>>
| id | 
|----| 
| 1  | 
| 1  |