Fabric | dbt – Jak Modeluju Dimenzní Gold Tabulky (SCD2) v Data Projektech?

Obsah

1. Co se řeší v Gold vrstvě a jsou pro nás priority?

2. Implementace v dbt – inkrementální a full refresh přepočet

4. Generování surrogate klíčů pomocí vlastního makra – SHA2_256

V předchozím článku – Fabric | dbt – Slowly changing dimension (SCD 2) – Snapshots a Check Strategie v dbt s příkladem jsme si ukázali, jak v prostředí Fabric a pomocí nástroje dbt implementovat historizaci dimenzních dat v Silver medailonu prostřednictvím snapshotů. Tyto snapshoty máme připraveny a zajišťují nám kompletní historii změn dimenzí (pomocí atributů valid_from a valid_to). Ve vrstvě Gold však potřebujeme tato data dále upravit tak, aby:

Dim tabulky byly plně připraveny pro reportingové účely
Zpracování bylo rychlé a bezpečné
Datový model byl odolný proti chybám lidského faktoru při práci s modelem

Co se řeší v Gold vrstvě a jsou pro nás priority?

V gold vrstvě budeme vytvářet dimenzní tabulky – je to poměrně pracné, ale odměnou nám bude slušný a odolný datový model.

vytvoření pěkně naformátovaných sql skriptů
vytvoření jednoznačných surrogate klíčů pomocí deterministických metod – HASHBYTES(‘SHA2_256’) funkce
vyvoření dummy values (SK -1 dimenzí zajišťujících možnost INNER JOINs na celém modelu)
nastavení sentinelových (strážných) hodnot pro časovou platnost dimenzí
převod raw dat ze silveru do finálního formátu (casty, transformace)
řízení inkrementálního přepočtu dat při běžných updatech a možnost plného refresh přepočtu (full refresh).

Tímto způsobem vznikají chytré dimenzní tabulky, které lze bezpečně provozovat. Díky dummy záznamům nemusí juniornější kolegové a power useři přemýšlet o nad typy při spojování tabulek (LEFT/RIGHT/…) a může efektivně spojovat s faktové tabulky v rámci Gold modelu INNER joinem.

Implementace v dbt – inkrementální a full refresh přepočet

Níže uvedený skript představuje implementaci

dimenze d_aw_address (destination)
z historizovaného snapshotu st_aw_address (source)

Využívá strategii incremental s režimem delete+insert, což znamená, že při každém běhu jsou nejprve odstraněny záznamy odpovídající změnám a následně vloženy nové verze. Pokud je spuštěno dbt standardním způsobem, tak je aktivní incremental kód ve WHERE podmínce – ze Silveru se načtou pouze záznamy, které se změnily od poslední změny Gold tabulky (dbt_updated_at).

Strategie delete+insert se mi jeví z hlediska integrity dat bezpečnější a odolnější proti kolizím oproti strategii merge.

Kód níže (tabulka s adresami z AdventureWorks):

{{ config(
    materialized='incremental',
    unique_key='sk_aw_address',
    incremental_strategy='delete+insert',
    schema='03_gold',
    alias='d_aw_address'
) }}

WITH snapshot_table AS (
	SELECT 
        {{ generate_custom_surrogate_key(['driving.source_businesskey', 'driving.source_system', 'driving.dbt_valid_from']) }} AS sk_aw_address,
        {{ generate_custom_surrogate_key(['driving.source_businesskey', 'driving.source_system']) }} AS sk_aw_address_master,
        driving.source_system                                                   AS dbt_id_sourcesystem,
        driving.source_businesskey                                              AS dbt_id_business_key,
        CAST(
            CASE 
                WHEN driving.dbt_valid_to IS NULL THEN 1
                ELSE 0
            END AS BIT)                                                 	AS dbt_current_flag,
        CAST(driving.dbt_valid_from AS DATE)                                    AS dbt_valid_from,
        CAST(COALESCE(driving.dbt_valid_to, '3000-12-31') AS DATE)              AS dbt_valid_to,
        CAST(driving.dbt_valid_from AS DATE)                                    AS mod_valid_from, 
        CAST(CASE
                WHEN driving.dbt_valid_to IS NULL 
                THEN '3000-12-31'
             ELSE DATEADD(DAY, -1, driving.dbt_valid_to)
        END AS DATE)                                                            AS mod_valid_to,
		CAST(driving.dbt_updated_at AS DATE)                            AS dbt_updated_at,
        CAST(ModifiedDate AS DATE)                                              AS sk_modifieddate,
        City                                                                    AS city,
        AddressLine1                                                            AS address_line_1,
        AddressLine2                                                            AS address_line_2,
        StateProvince                                                           AS state_province,
        PostalCode                                                              AS postal_code,
        CountryRegion                                                           AS country_region
	FROM {{ ref('st_aw_address') }} driving
    WHERE 1=1
    {% if is_incremental() %}
        AND driving.dbt_updated_at > (
        SELECT MAX(dbt_updated_at)
        FROM {{ this }}
        WHERE dbt_id_business_key != 'N/A'
        )
    {% endif %}

    UNION ALL

	SELECT 
        -1                                                                      AS skh_aw_address,
        'N/A'                                                                   AS dbt_id_sourcesystem,
        'N/A'                                                                   AS dbt_id_business_key,
        1                                                                       AS dbt_current_flag,
        CAST('1900-01-01' AS DATE)                                              AS dbt_valid_from,
        CAST('3000-12-31' AS DATE)                                              AS dbt_valid_to,
        CAST('1900-01-01' AS DATE)                                              AS mod_valid_from,
        CAST('3000-12-31' AS DATE)                                              AS mod_valid_to,
		CAST('1900-01-01' AS DATE)                                      AS dbt_updated_at,
        CAST('1900-01-01' AS DATE)                                              AS skh_modifieddate,
        'N/A'                                                                   AS city,
        'N/A'                                                                   AS address_line_1,
        'N/A'                                                                   AS address_line_2,
        'N/A'                                                                   AS state_province,
        'N/A'                                                                   AS postal_code,
        'N/A'                                                                   AS country_region
)
SELECT 
        sk_aw_address,
        dbt_id_sourcesystem,
        dbt_id_business_key,
        dbt_current_flag,
        dbt_valid_from,
        dbt_valid_to,
        mod_valid_from,
        mod_valid_to,
        dbt_updated_at,
        sk_modifieddate,
        city,
        address_line_1,
        address_line_2,
        state_province,
        postal_code,
        country_region
FROM snapshot_table
WHERE 1=1

Popis logiky modelu dimenzní tabulky

Model d_aw_address vytváří finální dimenzi adres z historických snapshotů uložených ve vrstvě Silver. Základní principy implementace jsou následující:

Surrogate klíče – Primární klíč sk_aw_address je generován na základě kombinace business klíče, systému původu a počáteční platnosti (dbt_valid_from). Tato troj-kombinace zajišťuje jednoznačnost každé historické verze v jakékoliv tabulce – protože máme korektně připravena metadata viz. Fabric – Bronze: Datová Akvizice do Delta Tabulek přes pipeline (notebook)
Aktuální záznam – Je označen příznakem dbt_current_flag = 1. Tento příznak se určuje podle toho, zda hodnota dbt_valid_to je prázdná (NULL) – v tom případě se jedná o poslední známou verzi záznamu.
Sentinel values – Pro záznamy s neuzavřeným intervalem je hodnota dbt_valid_to nahrazena technickou hodnotou 3000-12-31. Analogicky se používá 1900-01-01 jako technický začátek.
Inkrementální režim – Pomocí podmínky is_incremental() se při běžném běhu modelu načítají pouze nové nebo změněné záznamy na základě sloupce dbt_updated_at, který generuje dbt. Tím je zajištěno, že se do skriptu dostanou záznamy se Silver, u kterých došlo k nějaké aktuální změně.
Full refresh režim – Při plném přepočtu (dbt run --full-refresh) se model kompletně přegeneruje, čímž se eliminuje riziko nekonzistencí po větších změnách ve zdrojích.
dummy record “N/A” – V závěrečné části skriptu se přidává záznam s hodnotami “N/A” – tento technický záznam slouží jako náhradní hodnota pro umělé cizí klíče.

Generování surrogate klíčů pomocí vlastního makra – SHA2_256

Pro zajištění jednoznačnosti každé historické verze dimenze používáme vlastní makro pro generování surrogate klíčů. Toto makro umožňuje flexibilně kombinovat libovolný seznam sloupců a vytvořit z něj hash, který slouží jako primární klíč a master klíč.

{% raw %}{% macro generate_custom_surrogate_key(list_of_columns) %}
{%- set concatenated_string -%}
{%- for column in list_of_columns -%}
CAST({{ column }} AS VARCHAR(MAX))
{%- if not loop.last -%} + '_' + {%- endif -%}
{%- endfor -%}
{%- endset -%}
HASHBYTES('SHA2_256', {{ concatenated_string }})
{% endmacro %}{% endraw %}

Popis makra

Flexibilní vstup – Makro přijímá seznam sloupců, které mají být zahrnuty do surrogate klíče
Konverze na text – Každý sloupec je převeden na VARCHAR(MAX), aby bylo možné bezpečně kombinovat hodnoty různých datových typů (datové typy v Bronze a Silver vrstvě máme převedeny na STRING abychom zamezili problémum při převodech mezi datovými typy)
Oddělení hodnot – Hodnoty jsou spojeny znakem podtržítka, čímž se eliminuje možnost kolize mezi různými kombinacemi hodnot
Hashování – Spojený řetězec je zpracován funkcí HASHBYTES(‘SHA2_256’), která generuje 256bitový hash sloužící jako surrogate klíč

V příkladu modelu d_aw_address používáme makro k vytvoření surrogate klíčů pro historické i aktuální záznamy:

{{ generate_custom_surrogate_key(['driving.source_businesskey', 'driving.source_system', 'driving.dbt_valid_from']) }} AS sk_aw_address

Tímto způsobem je každá verze adresy jednoznačně identifikována kombinací business klíče, systému původu a počáteční platnosti záznamu. Na základě této troj-kombinace by neměla v žádné tabulce v celém našem modelu vzniknout žádná duplicita.

Druhé volání makra zajišťuje stabilní klíč pro master záznam (historické verze se stejným business key), který má význam zejména v modelech SCD 1: {{ generate_custom_surrogate_key(['driving.source_businesskey', 'driving.source_system']) }} AS sk_aw_address_master

Výhody použití makra

Konzistence klíčů napříč všemi modely.
Minimalizace rizika kolizí historických záznamů.
Flexibilní přizpůsobení vstupních sloupců pro různé dimenze.
Jednoduchá údržba – změna logiky generování klíče vyžaduje úpravu pouze makra.

Zásadní výhoda u SCD 1 – Díky deterministickému generování cizích klíčů odpadá potřeba fyzických joinů na dimenze při tvorbě faktových tabulek (nebo jiných dimenzních tabulek s cizími klíči). Tím se eliminuje datová závislost mezi dimenzemi a fakty a modely mohou být zpracovávány asynchronně/paralelně, bez nutnosti jejich striktní orchestrace v rámci běhu dbt. Samotný dependency graph dbt (ref()) lze tak omezit pouze na logické vazby, nikoli na pořadí běhu.

Závěr

Implementace SCD Typ 2 pomocí snapshotů ve vrstvě Silver a jejich následné rozšíření do vrstvy Gold prostřednictvím inkrementálního modelu v dbt představuje osvědčený a stabilní způsob správy historických dimenzí. Tento přístup zajišťuje plnou kontrolu nad časovou validitou, jednoznačnou identifikaci jednotlivých historických verzí a zároveň efektivní inkrementální reloady bez nutnosti kompletního rebuildování datového skladu při každé změně.

Generování surrogate klíčů pomocí hashů je osvědčený přístup, který přináší navíc zásadní výhody u dimenzí typu SCD1, kdy zcela odpadá potřeba orchestrace závislostí a umělé cizí klíče je možné generovat bez závislosti na zdrojové tabulce.

5/5 - (1 vote)