Using Codegen in dbt to Save Time Writing SQL and YAML

When working with dbt, I found that a lot of time can be spent writing repetitive code, particularly when creating source definitions, staging models and YAML files. Although none of these tasks are that complicated, they can become quite time consuming when working with multiple tables containing lots of columns.

I came across the dbt-codegen package during my data engineering training, which offers a quicker way of doing this. Rather than manually writing out column names and source definitions, codegen uses macros to generate the code for us.

What is dbt-codegen?

Codegen is a package of macros that can automatically generate SQL and YAML code based on the tables and models in your dbt project.

There are three useful macros that I will go into detail later in:

• generate_source: Generates YAML source definitions from tables in your database.
• generate_base_model: Generates the SQL for a base model, including the column names.
• generate_model_yaml: Generates YAML documentation containing the columns in your dbt models.

Installing Codegen

Before we can use codegen, we need to install the package in our dbt project.

Add the following to your packages.yml file:

packages:
  - package: dbt-labs/codegen
    version: 0.13.0

Then run the following command in your terminal:

dbt deps

This installs the package and makes its macros available within our project.

1. Generating Source YAML

Let's imagine we have a database containing three raw tables: customers, orders and payments, all within a schema called raw_jaffle_shop.

Normally, if we wanted to define these tables as sources in dbt, we would need to manually create a YAML file listing each table.

However, codegen can generate this for us using the generate_source macro:

dbt run-operation codegen.generate_source \
  --args '{"schema_name": "raw_jaffle_shop"}'

This generates YAML similar to the following:

version: 2

sources:
  - name: raw_jaffle_shop
    tables:
      - name: customers
      - name: orders
      - name: payments

We can then copy the output into our source YAML file.

If we also wanted to include the column names, we can simply add another argument:

dbt run-operation codegen.generate_source \
  --args '{"schema_name": "raw_jaffle_shop", "generate_columns": true}'

This will generate the source definitions along with the column names and their data types.

This is useful when dealing with large tables, as it removes the need to manually look up and type every column name.

2. Generating a Base Model

Another useful feature of codegen is the ability to automatically generate the SQL for a base model.

For example, let's say we want to create a model using our customers source table.

Without codegen, we might write something like this:

with source as (

    select *
    from {{ source('raw_jaffle_shop', 'customers') }}

),

renamed as (

    select
        id,
        first_name,
        last_name,
        email

    from source

)

select *
from renamed

Although this is relatively straightforward, you may have to do the same for a table containing 50 or 100 columns.

Instead, we can use the generate_base_model macro:

dbt run-operation codegen.generate_base_model \
  --args '{"source_name": "raw_jaffle_shop", "table_name": "customers"}'

Codegen automatically identifies the columns in the source table and produces the SQL structure for us.

We can then copy this into our model and make any changes, such as renaming columns, casting data types or adding transformations.

However, it is good to remember that codegen generates the starting point, rather than a finished model. We still need to decide how the data should be cleaned and transformed.

3. Generating Model YAML

Finally, codegen can also help with documenting our models.

When creating YAML files, we usually need to list the model name and its columns, before adding descriptions and tests.

For example, if we have created a model called stg_customers, we can run:

dbt run-operation codegen.generate_model_yaml \
  --args '{"model_names": ["stg_customers"]}'

This generates something similar to:

version: 2

models:
  - name: stg_customers
    description: ""
    columns:
      - name: id
        data_type: integer
        description: ""
      - name: first_name
        data_type: varchar
        description: ""
      - name: last_name
        data_type: varchar
        description: ""
      - name: email
        data_type: varchar
        description: ""

Instead of manually writing out each column, codegen does this for us and leaves placeholders for the descriptions.

We can then add our own descriptions and data tests where necessary.

An additional useful feature is the upstream_descriptions argument. Setting this to true allows codegen to reuse existing column descriptions from upstream models and sources where the column names match.

dbt run-operation codegen.generate_model_yaml \
  --args '{"model_names": ["stg_customers"], "upstream_descriptions": true}'

This can be helpful when the same columns are used across several models, as it reduces the amount of documentation we need to rewrite.

Why Use Codegen?

The main benefit of codegen is simply that it saves time.

When working with a small number of tables, manually writing SQL and YAML isn't too much of a problem. However, as a dbt project grows, the amount of repetitive code can quickly increase.

Codegen makes it much easier to set up new models and sources without having to write everything from scratch.

It is important to remember that the generated code still needs reviewing, especially when it comes to documentation, tests and transformations. However, by automating much of the initial setup, we can spend more time on the actual logic behind our models.

For me, codegen is a useful example of how a relatively simple dbt package can make the development process much more efficient.

You can find the full package documentation here.