Skip to main content

The dbt-codegen Package

One of the most tedious parts of Data Engineering is writing repetitive boilerplate code.

If your Indian telecom company introduces a new third-party CRM system with 50 raw tables, creating the sources.yml file and writing the 50 stg_ models (with every single column listed out for renaming) could take you three days of mindless typing.

Because dbt is programmatic, we can completely automate this process. The open-source community created the dbt-codegen package to write dbt code for you.

Topics Covered​

  • Installing the Package
  • Automatically Generating Source YAML
  • Automatically Generating Base Models
  • Automatically Generating Model YAML

1. Installing the Package​

Like all dbt packages, you install codegen by adding it to your packages.yml file and running dbt deps.

# packages.yml
packages:
- package: dbt-labs/codegen
version: [">=0.12.0", "<0.13.0"] # Always check dbt Hub for the latest version!

(Run dbt deps in your terminal to install the package).

2. Automating Source YAML Generation​

Imagine you have a schema in Snowflake called RAW_CRM containing tables like users, interactions, and billing. Instead of typing out the sources.yml by hand, you can run a run-operation macro.

A run-operation allows you to execute a Jinja macro directly from the terminal without attaching it to a specific model.

# Ask codegen to generate the sources YAML for the RAW_CRM schema
dbt run-operation generate_source --args '{"schema_name": "RAW_CRM", "database_name": "BEEMS_PROD"}'

The Output: The macro will query Snowflake's information schema, grab all the tables and columns, and print perfectly formatted YAML directly to your terminal screen. All you do is copy and paste it into your sources.yml file!

3. Automating Base (Staging) Models​

Once you have your sources.yml, you need to write the stg_users.sql model. Usually, you type SELECT column1, column2... by hand.

With codegen, you can generate the exact SQL for a staging model instantly.

# Ask codegen to write the SQL for the 'users' table from the 'crm' source
dbt run-operation generate_base_model --args '{"source_name": "crm", "table_name": "users"}'

The Output: The terminal will print out perfectly formatted SQL, including the config() block and the source() function.

WITH source AS (
SELECT * FROM {{ source('crm', 'users') }}
),

renamed AS (
SELECT
id as user_id,
first_name,
last_name,
phone_number,
created_at
FROM source
)

SELECT * FROM renamed

You copy this output, save it as stg_users.sql, rename any columns you want inside the renamed CTE, and you are done in 10 seconds!

4. Automating Model YAML (Tests & Descriptions)​

After you build your fct_orders.sql table, you need to add it to a schema.yml file to configure tests (like unique and not_null) and descriptions.

If fct_orders has 30 columns, typing out all 30 columns in YAML is agonizing.

# Ask codegen to generate the schema YAML for your fact table
dbt run-operation generate_model_yaml --args '{"model_names": ["fct_orders"]}'

The Output: It will print the exact YAML structure for your model, listing every single column. You simply copy it into your schema.yml and add your descriptions and tests.

Summary​

  • The dbt-codegen package writes boilerplate code for you, saving days of manual typing.
  • You execute its macros from the terminal using dbt run-operation.
  • generate_source reads your database schema and prints a perfectly formatted sources.yml.
  • generate_base_model looks at a source table and prints a perfectly formatted stg_ model in SQL.
  • generate_model_yaml looks at your models and prints a schema definition with all columns pre-populated.