Automatically generate sources.yml, staging models, and schema.yml using dbt codegen

2024-12-03 dbt

codegen is an official package that provides macros for automatically generating various files. It is recommended in the Best practice guides.

Model Layering in dbt’s Best Practice Guides - sambaiz-net

Add it to packages.yml and install using dbt deps.

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

You can invoke macros using dbt run-operation.

generate_source

Generates sources.yml referenced by staging models.

$ dbt run-operation generate_source --args 'schema_name: dbttest'
...
sources:
  - name: dbttest
    tables:
      - name: my_first_dbt_model
      - name: my_second_dbt_model

generate_base_model

Generate a base for staging model that renames the source.

$ dbt run-operation generate_base_model --args '{"source_name": "dbttest", "table_name": "my_first_dbt_model"}'
...
with source as (

    select * from {{ source('dbttest', 'my_first_dbt_model') }}

),

renamed as (

    select
        id

    from source

)

select * from renamed

generate_model_yaml

Generates schema.yml from models that have already executed dbt run.

$ dbt run-operation generate_model_yaml --args '{"model_names": ["my_first_dbt_model", "my_second_dbt_model"]}'
...
models:
  - name: my_first_dbt_model
    description: ""
    columns:
      - name: id
        data_type: int64
        description: ""

  - name: my_second_dbt_model
    description: ""
    columns:
      - name: id
        data_type: int64
        description: ""

Reference

dbtでmodelやsourceのベースとなるコードを自動で生成してくれるpackage「codegen」を試してみた #dbt | DevelopersIO