Strategic Cascade Tool

strategic_cascademainModels stale · last build 1 Jul 2025

kpi_cascade_snapshot

EditorCompiledLineageDocumentationPreviewmodels/gold/kpi_cascade_snapshot.sql

{{ config(
    materialized='incremental',
    unique_key='kpi_period_sk',
    schema='gold'
) }}

-- District KPI attainment snapshot
with actuals as (
    select * from {{ ref('int_kpi_actuals') }}
),
districts as (
    select * from {{ ref('stg_districts') }}
)

select
    md5(d.district_id::text || a.period)    as kpi_period_sk,
    d.district_name,
    d.region,
    a.period,
    a.kpi_code,
    a.actual_value,
    a.target_value,
    round(
        a.actual_value / nullif(a.target_value, 0) * 100, 1
    )                                        as attainment_pct,
    case
        when a.actual_value >= a.target_value then 'meeting'
        when a.actual_value >= a.target_value * 0.9 then 'approaching'
        else 'breach'
    end                                      as kpi_status,
    current_timestamp                        as _materialized_at

from actuals a
join districts d on d.district_id = a.district_id

{% if is_incremental() %}
  where a.period >= (select max(period) from {{ this }})
{% endif %}

Run console

[2025-07-01 02:00:11] dbt build --project strategic_cascade
[02:00:12] Found 44 models, 18 sources, 31 tests
[02:00:19] OK incremental model gold.kpi_cascade_snapshot [SUCCESS 7.1s]
[02:00:20] PASS=44 WARN=0 ERROR=0 — no run since.