Co to jest SCD? To akronim od angielskiego Slowly Changing Dimension czyli wolno zmieniający się wymiar. To nic innego, jak sposób na śledzenia zmian w danych w hurtowniach danych. W zależności od potrzeby stosuje się różne typy SCD.
Wyróżniamy sześć (6) typów SCD.
SCD 0 - ignorujesz zmiany
SCD 1 - nadpisujesz zmiany
SCD 2 - przechowujesz historię dla atrybutów w oddzielnym rekordzie.
SCD 3 - przechowujesz historię dla atrybutów w tym samym rekordzie ale innej kolumnie
SCD 4 - przechowujesz historię zmian w innej tabeli
SCD 6 - to kombinacja 1 + 2 + 3 = 6. Historia przechowywana jest zarówno w osobnej kolumnie jak i rekordzie.
U niektórych autorów znajdziesz również SCD 5 oraz SCD 7. https://www.kimballgroup.com/2013/02/design-tip-152-slowly-changing-dimension-types-0-4-5-6-7/
Najpopularniejsze są typy SCD 1, 2. Na rozmowie o prace, możesz dostać pytanie o SCD3. Tym trzem typom poświęcimy dzisiaj uwagę.
Co to jest SCD - nadpisywanie zmian?
W SCD 1 nadpisujesz zmiany. Tracisz w ten sposób historię. Odzwierciedlasz tylko aktualny stan systemu. W tabeli są zawsze najbardziej aktualne dane.
Zyskujesz natomiast na wydajności i na miejscu zajmowanym przez dane.
Modelując proces możesz się zastanowić:
Jak wykryć dane do aktualizacji? Jak stwierdzić, że ten rekord jest nowy?
Sprawdzanie czy to jest nowy, czy istniejący rekord będzie wykonywał na podstawie klucza biznesowego. Czyli czegoś co unikalnie określa dany rekord. Może to być jedna kolumna albo zbiór kolumn.
Na przykład masz tabelę Pojazd. Co może być kluczem biznesowym? Na przykład Numer Vin.
Albo masz tabelę Pracownik, kluczem biznesowym będzie unikalny numer pracownika.
Dla tabeli Pacjent to może być ID Pacjenta.
Są to dane dostępne w systemie źródłowym a nie tworzone w hurtowni danych.
Przy SCD 1 nie musisz się zastanawiać czy wartości w kolumnach się zmieniły czy nie, możesz je aktualizować jeżeli w danych źródłowych dostaniesz rekord z takim kluczem biznesowym jak już masz w tabeli wymiarów.
Oczywiście możesz wykrywać zmiany i aktualizować tylko te rekordy, które się rzeczywiście zmieniły. Tutaj pytanie co jest bardziej kosztowne wykrywanie zmiany czy aktualizacja rekordów nie zmienionych. Co bardziej obciąża bazę i zjada zasoby? Decyzja należy do Ciebie 🙂
Jeżeli rekordu z danym kluczem biznesowym nie było jeszcze w docelowej tabeli wtedy wstawiasz rekord.
Co to jest SCD - jak wykrywać zmianę?
Przy SCD2 konieczne jest żeby wykryć zmianę. Wiedzieć, czy dany rekord uległ zmianie?
Jest kilka sposobów na wykrywanie zmian w tabeli. Niektóre są szybsze do implementacji ale obciążone ciężarem słabej wydajności. Inne z kolei są szybsze w działaniu a wolniejsze w implementacji.
Na marginesie: ten problem znika jeżeli Twój kod jest generowany. Wtedy masz "automat", który generuje odpowiedni kod.
Wyobraź sobie tabelę D_Pojazd z atrybutami:
| NrVin | Model | Marka | NrRej | Właściciel | DataPrzeglądu | Przebieg | Punkty |
|---|---|---|---|---|---|---|---|
| 1GCHK23244F199207 | Chevrolet | Silverado 2500HD | NG0 1721 | Rafał Gogłoza | 17.06.2022 | 403 981 | 0 |
(Dla dociekliwych, to są tylko niektóre atrybuty tej tabeli)
Kluczem do porównania będzie NrVin, który jest biznesowym kluczem w tabeli. Porównujesz pojedynczo atrybuty. Detekcję zmiany możesz realizować porównując atrybuty do siebie. Porównujesz to co masz zapisane w tabeli z tym co przychodzi. Mam nadzieję, że już widzisz, że to jest niezbyt wydajne rozwiązanie. Przy tabeli ze 100 atrybutami musisz zrobić porównanie 100 razy i to dla jednego rekordu. A co jeżeli tych rekordów masz 100 000?
Detekcję zmian możesz zrealizować też w oparciu o klucz haszujący. Kolumny na których chcesz wykonywać porównanie dodajesz do funkcji haszującej. Na wyjściu dostajesz ciąg znaków.
Przykładowe wywołanie może wyglądać w ten sposób:
HashMD5(NVL(Model,'') || NVL(Marka,'') || … || NVL(LiczbaPunktowKarnych,''))
Zwróć uwagę, że zalecane jest posiadanie ograniczonego zaufania do danych. Za każdym razem, gdy możesz dostać pustą wartość (NULL) wtedy podstawiasz wartość domyślną.
To co jest powyżej to pseudo code. Ta funkcja może być wywoływana w ETL'u albo w SQL'u. Może mieć różne nazwy albo przyjmować inne parametry. Tutaj jest przykładowe zastosowanie kluczy haszujących, dla ustalenia uwagi.
Porównanie:
Stg_Pojazd
| NrVin | HashMdk5 |
|---|---|
| 2FMDK4KC4CBA27842 | 1b6ccb5e577d250cc83624a9e6121656 |
| 1FVHCYDJ85HV14123 | 85c91101747204d1a0d795a69b49a1c5 |
| 1GCHK29U31E237682 | 300bb1780c61d684cb7cb24c849f2259 |
D_Pojazd
| NrVin | HashMdk5 |
|---|---|
| 2FMDK4KC4CBA27842 | 1b6ccb5e577d250cc83624a9e6121656 |
| 1FVHCYDJ85HV14123 | 85c91101747204d1a0d795a69b49a1c5 |
| 1GCHK29U31E237682 | 300bb1780c61d684cb7cb24c849fFFFF |
Trzeci scenariusz:
System źródłowy wysyła do Twojej hurtowni rekordy i oznacza te, które się zmieniły. Czyli masz na przykład DatęAktualizacji w danych wejściowych. Wtedy wiesz że rekord się zmienił i możesz na nim wykonać operacje.
Najprościej jest, gdy system źródłowy wysyła tylko zmodyfikowane dane. Wtedy wiesz, że to co dostałeś na wejściu ma zostać zaktualizowane.
Co to jest SCD - SCD3 dodatkowe atrybuty
Nie spotkałem jeszcze zastosowania SCD3 w praktyce. Pytanie o SCD3 pojawia się natomiast na rozmowach w sprawie pracy.
Teoria jest taka, że śledzisz zmianę tylko wybranych atrybutów dla tabeli D_Pojazd na przykład monitorujesz tylko zmiany właściciela. Wykorzystując strategię SCD3 potrzebujesz dodatkowych kolumn do przechowywania historii.
D_Pojazd
| NrVin | … | Właściciel | Poprzedni_Właściciel |
|---|---|---|---|
| 2FMDK4KC4CBA27842 | Alojzy Pietryka | Marzena Żyłka | |
| 1GCHK29U31E237682 | Tymon Obrotny | Rufus Stokrotka |
Takie podejście ma ewidentną wadę. Nie jest to praktyczne rozwiązanie. Przechowujesz tylko jednego poprzedniego właściciela. Wszelkie inne zmiany są nadpisywane.
Co to jest SCD - SCD2 Sposoby implementacji
Przechowywanie historii zaimplementujesz w tej samej tabeli. Wtedy przydadzą Ci się dodatkowe kolumny do sprawdzenia, kiedy dany rekord był aktywny a który jest aktywny teraz.
To dobre, ale nieoptymalne rozwiązanie.
D_Pojazd
| NrVin | … | AktywneOd | AktywneTo |
|---|---|---|---|
| 2FMDK4KC4CBA27842 | 2022-01-01 | 9999-12-31 | |
| 2FMDK4KC4CBA27842 | 2021-07-01 | 2022-01-01 | |
| 2FMDK4KC4CBA27842 | 2020-06-17 | 2022-07-01 |
W tej tabeli masz trzy wersje tego samego rekordu z ich datami obowiązywania. Wariancją takiego rozwiązania będzie
| NrVin | … | AktywneOd | AktywneTo | CzyAktywny |
|---|---|---|---|---|
| 2FMDK4KC4CBA27842 | 2022-01-01 | 9999-12-31 | A | |
| 2FMDK4KC4CBA27842 | 2021-07-01 | 2022-01-01 | H | |
| 2FMDK4KC4CBA27842 | 2020-06-17 | 2022-07-01 | H |
Wtedy w łatwy sposób możesz wybrać tylko aktywne rekordy.
SELECT * FROM d_pojazd WHERE CzyAktywny = 'A';
Dbanie o wydajność w każdym możliwym momencie to dobra praktyka. Dlatego kolumny aktywne od aktywne do to mogą być dane typu INT tak samo jak flaga czy aktywny może przyjmować wartości binarne 0,1. Wtedy ta tabele wyglądałaby tak:
| NrVin | … | AktywneOd | AktywneTo | CzyAktywny |
|---|---|---|---|---|
| 2FMDK4KC4CBA27842 | ... | 20220101 | 99991231 | 1 |
| 2FMDK4KC4CBA27842 | ... | 20210701 | 20220101 | 0 |
| 2FMDK4KC4CBA27842 | ... | 20200617 | 20220701 | 0 |
Innym rozwiązaniem jest przechowywanie historii w innej tabeli. W tabeli D_Pojazd masz tylko rekordy bieżące. Natomiast w tabeli D_Pojazd_Hist przechowujesz rekordy historyczne.
Co to jest SCD - podsumowanie
Najczęściej spotkasz w swojej pracy SCD1 i SCD2. Możesz zobaczyć różne sposoby implementacji wtedy niektórzy powiedzą, że używasz na przykład SCD4 albo SCD6.
Podczas implementacji warto zwrócić uwagę na późniejszą wydajność. Jakich typów danych używasz w kolumnach "sterujących" (AktywnyOd, AktywnyDo, CzyAktywny)? Jak będziesz potem odpytywał dane?
Warto zadać biznesowi pytanie: Na pewno będziecie potrzebować historii na przykład zmian departamentów? Czy i nawet jak były zmiany to chcą raportować na bieżących wartościach? Czy w tym przypadku będziecie tylko korzystać z danych aktualnych?
Możesz mieć tabele, gdzie masz mało atrybutów albo, gdy tabela jest bardzo szeroka i kolumn jest bardzo dużo. Wtedy warto skorzystać z porównania po kluczu haszującym. Dla spójności, zapewne chcesz mieć wszystkie przepływy zaimplementowane w ten sam sposób. Wszędzie porównywanie po kluczy hashującym. Trudniejsze w utrzymaniu będzie rozwiązanie, gdzie masz zaimplementowane różne sposoby wykrywania zmiany.
Jakie Ty modele SCD stosujesz w hurtowni danych? Jaki masz preferowany sposób implementacji?
Sprawdź się:
- Co to jest SCD?
- Jakie są rodzaje SCD? (To częste pytanie na rozmowie kwalifikacyjnej)
- Jak wykrywać zmianę w danych?
- Jak zaimplementujesz śledzenie zmian w historii?