Skip to content

Extending dbt Models

Step-by-step guide to adding a new Gold model to Konsolidat.

Adding a Gold Model

1. Create the SQL File

Create dbt_project/models/gold/gold_your_model.sql:

{{
    config(
        materialized='table',
        tags=['gold'],
        **epm_config(order_by='(data_area_id, fiscal_year, fiscal_period, main_account)')
    )
}}

select
    tb.data_area_id,
    tb.fiscal_year,
    tb.fiscal_period,
    tb.main_account,
    {{ dim_select('tb.') }},
    {{ measure_passthrough('tb.') }}
from {{ ref('gold_trial_balance') }} as tb
where tb.fiscal_period between 1 and 12

Key points: - Use epm_config() for ClickHouse engine settings - Use dim_select() / dim_group_by() for dimension columns - Use measure_passthrough() for standard measures - Reference upstream models with {{ ref('model_name') }}

2. Add YAML Documentation

Add an entry to dbt_project/models/gold/_gold__models.yml:

  - name: gold_your_model
    description: "What this model does"
    columns:
      - name: data_area_id
        description: "Legal entity identifier"
        tests:
          - not_null
      - name: your_key_column
        description: "Description of this column"

3. Add Tests

Create dbt_project/tests/assert_your_condition.sql:

-- Describe what this test checks
select *
from {{ ref('gold_your_model') }}
where some_condition_that_should_never_be_true

dbt tests return rows that violate the assertion. Zero rows = pass.

Use {{ config(severity='warn') }} for non-blocking tests.

4. Build and Test

dbt run --select gold_your_model    # Build just this model
dbt test --select gold_your_model   # Run tests
dbt build --select gold_your_model  # Both in one command

Adding a Seed-Driven Model

If your model needs reference data:

1. Create the Seed CSV

Add dbt_project/seeds/your_reference_data.csv:

column_a,column_b,column_c
value1,value2,value3

2. Configure Column Types (Optional)

In dbt_project.yml, add type overrides:

seeds:
  konsolidat:
    your_reference_data:
      +column_types:
        column_a: String
        column_b: Decimal(18,2)

3. Reference in Your Model

from {{ ref('your_reference_data') }} as ref_data

4. Load the Seed

dbt seed --select your_reference_data

Patterns to Follow

Aggregation Model

select
    data_area_id,
    fiscal_year,
    fiscal_quarter,
    main_account,
    {{ dim_select() }},
    sum(period_net_amount) as quarter_net_amount
from {{ ref('gold_trial_balance') }} as tb
inner join {{ ref('gold_period_hierarchy') }} as ph
    on tb.fiscal_period = ph.fiscal_period
group by
    data_area_id,
    fiscal_year,
    fiscal_quarter,
    main_account,
    {{ dim_group_by() }}

Join Model (e.g., Variance)

select
    {{ dim_coalesce('a', 'b') }},
    a.amount as actual_amount,
    b.amount as budget_amount,
    a.amount - b.amount as variance_abs
from {{ ref('gold_trial_balance') }} as a
full outer join {{ ref('gold_spread_budget') }} as b
    on a.data_area_id = b.data_area_id
    and a.fiscal_year = b.fiscal_year
    and a.fiscal_period = b.fiscal_period
    and a.main_account = b.main_account
    {{ dim_join_on('a', 'b') }}

Window Function Model (e.g., YTD)

select
    *,
    sum(period_net_amount) over (
        partition by data_area_id, fiscal_year, main_account,
        {{ dim_partition_by() }}
        order by fiscal_period
    ) as ytd_net_amount
from {{ ref('gold_trial_balance') }}

Consolidation Layer (Empty Dims)

For non-entity layers (IC, CTA), use dim_empty_strings():

select
    consolidation_group,
    'ic_elimination' as adjustment_type,
    '' as data_area_id,
    fiscal_year,
    fiscal_period,
    main_account,
    {{ dim_empty_strings() }},
    elimination_amount as amount
from {{ ref('gold_ic_eliminations') }}

Making a Model API-Queryable

To expose a new model through the =EPM() function:

  1. Add the table to SCENARIO_TABLES in konsol/api.py
  2. Add allowed measures to ALLOWED_MEASURES
  3. The API's _batch_query_clickhouse() will automatically handle it

See Extending the API for details.

Next Steps