Surrogaattiavaimien mallintaminen

Miksi ei Bronze-kerroksessa?

Bronze-data on raakaa: siellä voi olla duplikaattirivejä, puuttuvia arvoja ja saman entiteetin useita versioita peräkkäin. Jos surrogaatti luodaan Bronze-kerroksessa, sama asiakas saa useamman SK:n ennen kuin data on puhdistettu. Tämä tarkoittaa, että Silver-kerroksessa pitää joka tapauksessa yhdistää nämä avaimet uudelleen — eli Bronze-tason SK oli turha välivaihe. Luodaan avain kerran, oikeassa paikassa.

Miksi ei Gold-kerroksessa?

Gold-kerroksessa voi olla useita erillisiä tauluja tai aggregointeja, jotka kaikki tarvitsevat saman asiakasdimension. Jos SK luodaan vasta Gold-tasolla, jokainen Gold-taulu luo omat avaimensa — ja ne eivät täsmää toisiinsa. Silver-tasolla luotu SK on se yhteinen nimittäjä, johon kaikki Gold-tasot voivat luottaa.

Esimerkki: AsiakasDimensio — miltä mapping-taulu näyttää käytännössä

Lähdejärjestelmä käyttää asiakasnumerona merkkijonoa FI-VVVV-NNNNN. Viiden latauserän jälkeen silver.LookupAsiakasAvain näyttää tältä — IDENTITY on generoinut SK:t automaattisesti lisäysjärjestyksessä:

Mapping-tauluun liitytään dimensiolatauksessa NK:n perusteella. Tuloksena silver.DimAsiakas käyttää ainoastaan INT-surrogaattiavainta — VARCHAR-tunnus jää attribuutiksi debug-käyttöön:

SQL-koodi joka toteuttaa tämän inkrementaalisesti — ensin mapping-taulun luonti, sitten kaksi vaihetta jokaisessa latauserässä. IDENTITY(1,1) CREATE TABLE:ssa tarkoittaa: ensimmäinen luku on lähtöarvo (1 = aloitetaan ykkösestä), toinen luku on askel (1 = kasvatetaan yhdellä aina kun rivi lisätään). Tietokanta generoi AsiakasAvain-arvon automaattisesti — INSERT-lauseessa ei tarvitse mainita AsiakasAvain-saraketta lainkaan. Ensimmäinen asiakas saa arvon 1, toinen 2, kolmas 3, ja niin edelleen ikuisesti kasvavana sarjana.

-- 1. Mapping-taulun luonti (kerran)
CREATE TABLE silver.LookupAsiakasAvain (
    AsiakasAvain  INT         NOT NULL IDENTITY(1,1),
    AsiakasTunnus VARCHAR(50) NOT NULL,
    LuontiAika    DATETIME2   NOT NULL DEFAULT SYSDATETIME(),
    CONSTRAINT PK_LookupAsiakas PRIMARY KEY (AsiakasAvain),
    CONSTRAINT UQ_LookupAsiakas_NK UNIQUE (AsiakasTunnus)
);

-- 2. Lisää uudet NK:t — IDENTITY generoi SK:n automaattisesti
--    (ajetaan joka latauserässä ennen dimension päivitystä)
INSERT INTO silver.LookupAsiakasAvain (AsiakasTunnus)
SELECT DISTINCT s.AsiakasTunnus
FROM   bronze.Staging_Asiakas s
WHERE  NOT EXISTS (
    SELECT 1
    FROM   silver.LookupAsiakasAvain l
    WHERE  l.AsiakasTunnus = s.AsiakasTunnus
);

-- 3. Päivitä Silver-dimensio (SCD Type 1: ylikirjoita muuttunut data)
MERGE silver.DimAsiakas AS target
USING (
    SELECT l.AsiakasAvain,
           s.AsiakasTunnus,
           s.AsiakasNimi,
           s.Segmentti,
           s.Maa
    FROM   bronze.Staging_Asiakas       s
    JOIN   silver.LookupAsiakasAvain    l
               ON l.AsiakasTunnus = s.AsiakasTunnus
) AS source ON target.AsiakasAvain = source.AsiakasAvain
WHEN MATCHED THEN
    UPDATE SET
        AsiakasNimi = source.AsiakasNimi,
        Segmentti   = source.Segmentti,
        Maa         = source.Maa
WHEN NOT MATCHED THEN
    INSERT (AsiakasAvain, AsiakasTunnus, AsiakasNimi, Segmentti, Maa)
    VALUES (source.AsiakasAvain, source.AsiakasTunnus,
            source.AsiakasNimi, source.Segmentti, source.Maa);

Vaihe 2 lisää mapping-tauluun vain ne rivit joiden NK ei siellä vielä ole — olemassa olevat asiakkaat saavat saman SK:n kuin aiemmassa erässä. Vaihe 3 päivittää dimension: MATCHED-haara ylikirjoittaa muuttuneet attribuutit, NOT MATCHED -haara lisää kokonaan uudet rivit.

Esimerkki: SCD Type 2 — asiakas vaihtaa segmenttiä

Matti Meikäläinen (NK: FI-2024-00001, nykyinen SK: 1) siirtyy segmentistä Pienasiakas segmenttiin Avainasiakas 15.6.2026. SCD Type 2 -mallinnuksessa tämä ei ylikirjoita vanhaa riviä — se sulkee sen ja luo uuden rivin uudella SK:lla.

Ennen muutosta:

Latauserän jälkeen (15.6.2026): Vanha rivi suljetaan, uusi SK (7) generoidaan IDENTITY:llä ja uusi dimensiorivi lisätään uudella segmentillä.

FactMyynti-rivit ennen 15.6.2026 viittaavat AsiakasAvaimeen 1 → segmentti on edelleen Pienasiakas. Myyntirivit 15.6.2026 alkaen viittaavat AsiakasAvaimeen 7 → segmentti on Avainasiakas. Sama asiakas, sama NK — mutta kaksi erillistä historiaa.

SCD Type 2 -mapping-taulussa ei voi käyttää UNIQUE-rajoitetta AsiakasTunnukselle, koska sama NK esiintyy useita kertoja. CREATE TABLE eroaa SCD Type 1 -versiosta juuri tässä:

-- SCD Type 2 -mapping-taulu: sama NK voi esiintyä useasti
CREATE TABLE silver.LookupAsiakasAvain (
    AsiakasAvain  INT         NOT NULL IDENTITY(1,1),
    AsiakasTunnus VARCHAR(50) NOT NULL,
    AlkuPvm       DATE        NOT NULL,
    LoppuPvm      DATE        NOT NULL DEFAULT '9999-12-31',
    OnkoNykyinen  BIT         NOT NULL DEFAULT 1,
    CONSTRAINT PK_LookupAsiakas PRIMARY KEY (AsiakasAvain)
    -- Ei UNIQUE(AsiakasTunnus) — sama NK saa useamman SK:n eri versioille
);

-- Vaihe 1: Sulje vanha rivi mapping-taulusta
--          (ennen uuden SK:n luontia, jotta tuore rivi ei sulkeudu heti)
UPDATE l
SET    LoppuPvm     = DATEADD(day, -1, CAST(GETDATE() AS DATE)),
       OnkoNykyinen = 0
FROM   silver.LookupAsiakasAvain l
JOIN   silver.DimAsiakas         d  ON d.AsiakasTunnus = l.AsiakasTunnus
                                    AND d.OnkoNykyinen  = 1
JOIN   bronze.Staging_Asiakas    s  ON s.AsiakasTunnus = l.AsiakasTunnus
WHERE  d.Segmentti <> s.Segmentti
  AND  l.OnkoNykyinen = 1;

-- Vaihe 2: Luo uusi SK muuttuneelle riville
INSERT INTO silver.LookupAsiakasAvain (AsiakasTunnus, AlkuPvm)
SELECT DISTINCT s.AsiakasTunnus, CAST(GETDATE() AS DATE)
FROM   bronze.Staging_Asiakas s
JOIN   silver.DimAsiakas      d
    ON d.AsiakasTunnus = s.AsiakasTunnus
   AND d.OnkoNykyinen  = 1
WHERE  d.Segmentti <> s.Segmentti;   -- seurattava attribuutti muuttui

-- Vaihe 3: Sulje vanha dimensiorivi
UPDATE d
SET    LoppuPvm     = DATEADD(day, -1, CAST(GETDATE() AS DATE)),
       OnkoNykyinen = 0
FROM   silver.DimAsiakas      d
JOIN   bronze.Staging_Asiakas s ON s.AsiakasTunnus = d.AsiakasTunnus
WHERE  d.Segmentti   <> s.Segmentti
  AND  d.OnkoNykyinen = 1;

-- Vaihe 4: Lisää uusi dimensiorivi uudella SK:lla
INSERT INTO silver.DimAsiakas
       (AsiakasAvain, AsiakasTunnus, AsiakasNimi, Segmentti, Maa, AlkuPvm, LoppuPvm, OnkoNykyinen)
SELECT l.AsiakasAvain,
       s.AsiakasTunnus, s.AsiakasNimi, s.Segmentti, s.Maa,
       CAST(GETDATE() AS DATE), '9999-12-31', 1
FROM   bronze.Staging_Asiakas       s
JOIN   silver.LookupAsiakasAvain    l  ON l.AsiakasTunnus = s.AsiakasTunnus
                                       AND l.OnkoNykyinen  = 1
WHERE  NOT EXISTS (
    SELECT 1 FROM silver.DimAsiakas d WHERE d.AsiakasAvain = l.AsiakasAvain
);

Vaiheet 1 ja 3 sulkevat vanhan version: LoppuPvm = tänään - 1 ja OnkoNykyinen = 0. Sulkeminen tehdään ennen insertiä, jotta vaiheen 2 juuri luoma rivi ei sulkeutuisi heti syntymänsä jälkeen. Vaihe 4 lisää uuden version — IDENTITY antaa sille seuraavan vapaan kokonaisluvun. Faktatauluun ei kosketa yhdelläkään UPDATElla: voimassaoloaikaa luetaan vain latauksessa, kun faktariville valitaan tapahtumahetkellä voimassa ollut SK.

Inkrementaalisen latauksen vaiheet (SCD Type 1):

1. Lataa muuttunut data staging-tauluun. Staging sisältää vain ne rivit jotka ovat muuttuneet tai ovat uusia edellisen latauksen jälkeen (CDC, aikaleima tai versiotunnus).
2. Tunnista uudet NK:t. Vertaa staging-taulun NK:ia mapping-tauluun. Staging-rivit joiden NK ei löydy mapping-taulusta ovat uusia entiteettejä.
3. Luo uudet SK:t. Lisää uudet NK:t mapping-tauluun — tietokanta generoi automaattisesti uuden IDENTITY-arvon eli SK:n. Vanhoille NK:ille ei luoda uutta SK:ta.
4. Liitä SK staging-tauluun. JOIN staging ↔ mapping NK:n perusteella. Nyt jokaisella staging-rivillä on oikea SK.
5. MERGE Silver-dimensioon. Olemassa olevat rivit päivitetään (Type 1: ylikirjoita), uudet rivit lisätään.

Esimerkki: uusi asiakas puolen tunnin erässä

Kello 14:00 Bronze-tauluun saapuu uusi asiakas AsiakasTunnus = "FI-2026-00892". Silver-latausprosessi käynnistyy 14:30:

1. Staging sisältää rivin AsiakasTunnus = "FI-2026-00892".
2. Mapping-taulussa ei ole tätä NK:ta → uusi rivi lisätään, tietokanta generoi AsiakasAvain = 6.
3. Staging-rivi saa SK:n 6 JOIN-operaatiolla.
4. Silver-dimensioon lisätään uusi asiakasrivi avaimella 6.
5. Kello 15:00 saman asiakkaan nimi korjataan lähdejärjestelmässä. Seuraavassa erässä staging sisältää saman NK:n "FI-2026-00892" → mapping-taulu palauttaa 6 → Silver-rivillä päivitetään vain AsiakasNimi, SK pysyy muuttumattomana.