Implementazione di un Data Warehouse Dimensionale con Databricks SQL: Parte 2

Man mano che le organizzazioni consolidano i carichi di lavoro di analisi su Databricks, spesso devono adattare le tecniche tradizionali di data warehouse. Questa serie esplora come implementare il modellamento dimensionale—in particolare, gli schemi a stella—su Databricks. Il primo blog si è concentrato sulla progettazione dello schema. Questo blog illustra le pipeline ETL per le tabelle delle dimensioni, inclusi i modelli di Dimensioni a Lenta Variazione (SCD) di Tipo 1 e Tipo 2. La Parte 3 mostra come costruire pipeline ETL per le tabelle dei fatti.

Dimensioni a Lenta Variazione (SCD)

Nell'ultimo blog, abbiamo definito il nostro schema a stella, inclusa una tabella dei fatti e le sue dimensioni correlate. Abbiamo evidenziato in particolare una tabella delle dimensioni, DimCustomer, come mostrato qui (con alcuni attributi rimossi per risparmiare spazio):

Gli ultimi tre campi di questa tabella, ovvero StartDate, EndDate e IsLateArriving, rappresentano metadati che ci aiutano con il versioning dei record. Man mano che il reddito, lo stato civile, la proprietà della casa, il numero di figli a carico o altre caratteristiche di un dato cliente cambiano, vorremo creare nuovi record per quel cliente in modo che fatti come le nostre transazioni di vendita online in FactInternetSales siano associati alla corretta rappresentazione di quel cliente. La chiave naturale (o business), CustomerAlternateKey, sarà la stessa per tutti questi record, ma i metadati differiranno, permettendoci di conoscere il periodo per il quale quella versione del cliente era valida, così come la chiave surrogata, CustomerKey, permettendo ai nostri fatti di collegarsi alla versione corretta.

NOTA: Poiché la chiave surrogata è comunemente usata per collegare fatti e dimensioni, le tabelle delle dimensioni sono spesso raggruppate (clustered) in base a questa chiave. A differenza dei database relazionali tradizionali che utilizzano indici b-tree su record ordinati, Databricks implementa un metodo di clustering unico noto come liquid clustering. Sebbene i dettagli del liquid clustering siano al di fuori dello scopo di questo blog, utilizziamo costantemente la clausola CLUSTER BY sulla chiave surrogata delle nostre tabelle delle dimensioni durante la loro definizione per sfruttare efficacemente questa funzionalità.

Questo modello di versioning dei record delle dimensioni al variare degli attributi è noto come modello di Dimensione a Lenta Variazione di Tipo 2 (o semplicemente SCD di Tipo 2). Il modello SCD di Tipo 2 è preferito per la registrazione dei dati delle dimensioni nella metodologia dimensionale classica. Tuttavia, esistono altri modi per gestire le modifiche nei record delle dimensioni.

Uno dei modi più comuni per gestire i valori delle dimensioni che cambiano è aggiornare i record esistenti sul posto. Viene creata una sola versione del record, in modo che la chiave business rimanga l'identificatore unico per il record. Per varie ragioni, non ultime le prestazioni e la coerenza, implementiamo comunque una chiave surrogata e colleghiamo i nostri record dei fatti a queste dimensioni tramite tali chiavi. Tuttavia, i campi metadati StartDate e EndDate che descrivono gli intervalli di tempo durante i quali un dato record di dimensione è considerato attivo non sono necessari. Questo è noto come modello SCD di Tipo 1. La dimensione Promotion nel nostro schema a stella fornisce un buon esempio di implementazione di una tabella delle dimensioni di Tipo 1:

Ma che dire del campo metadati IsLateArriving visto nella dimensione Customer di Tipo 2 ma assente dalla dimensione Promotion di Tipo 1? Questo campo viene utilizzato per contrassegnare i record come in arrivo in ritardo. Un record in arrivo in ritardo è un record per il quale la chiave business compare durante un ciclo ETL dei fatti, ma non esiste alcun record per quella chiave individuato durante l'elaborazione precedente delle dimensioni. Nel caso delle SCD di Tipo 2, questo campo viene utilizzato per indicare che quando i dati per un record in arrivo in ritardo vengono osservati per la prima volta in un ciclo ETL delle dimensioni, il record dovrebbe essere aggiornato sul posto (proprio come in un modello SCD di Tipo 1) e quindi versionato da quel momento in poi. Nel caso delle SCD di Tipo 1, questo campo non è necessario perché il record verrà comunque aggiornato sul posto.

NOTA: Il Kimball Group riconosce ulteriori pattern SCD, la maggior parte dei quali sono variazioni e combinazioni dei pattern di Tipo 1 e Tipo 2. Poiché gli SCD di Tipo 1 e Tipo 2 sono i più frequentemente implementati tra questi pattern e le tecniche utilizzate con gli altri sono strettamente correlate a quelle impiegate con questi, stiamo limitando questo blog solo a questi due tipi di dimensioni. Per maggiori informazioni sugli otto tipi di SCD riconosciuti dal Kimball Group, si prega di consultare la sezione Slowly Changing Dimension Techniques di questo documento.

Implementazione del Pattern SCD di Tipo 1

Con i dati aggiornati sul posto, il pattern di workflow SCD di Tipo 1 è il più semplice tra i pattern ETL bidimensionali. Per supportare questi tipi di dimensioni, semplicemente:

1. Estraiamo i dati richiesti dal nostro sistema/i operativo/i
2. Eseguiamo tutte le operazioni di pulizia dei dati necessarie
3. Confrontiamo i nostri record in arrivo con quelli già presenti nella tabella delle dimensioni
4. Aggiorniamo tutti i record esistenti in cui gli attributi in arrivo differiscono da quanto già registrato
5. Inseriamo tutti i record in arrivo che non hanno un record corrispondente nella tabella delle dimensioni

Per illustrare un'implementazione SCD di Tipo 1, definiremo l'ETL per il popolamento continuo della tabella DimPromotion.

Fase 1: Estrazione dei dati da un sistema operativo

Il nostro primo passo è estrarre i dati dal nostro sistema operativo. Poiché il nostro data warehouse è modellato sul database di esempio AdventureWorksDW fornito da Microsoft, stiamo utilizzando il database di esempio AdventureWorks (OLTP), strettamente associato, come nostra fonte. Questo database è stato distribuito su un'istanza di Azure SQL Database e reso accessibile all'interno del nostro ambiente Databricks tramite una query federata. L'estrazione è quindi facilitata da una semplice query (con alcuni campi redatti per risparmiare spazio), con i risultati della query persistiti in una tabella nel nostro schema di staging (reso accessibile solo agli ingegneri dei dati nel nostro ambiente tramite impostazioni di permesso non mostrate qui). Questo è solo uno dei molti modi in cui possiamo accedere ai dati del sistema sorgente in questo ambiente:

Fase 2: Confronto dei record in arrivo con quelli nella tabella

Supponendo di non avere ulteriori passaggi di pulizia dei dati da eseguire (che potremmo implementare con un'istruzione UPDATE o un'altra istruzione CREATE TABLE AS), possiamo quindi affrontare le operazioni di aggiornamento/inserimento dei dati della dimensione in un unico passaggio utilizzando un'istruzione MERGE, facendo corrispondere i nostri dati di staging e i dati della dimensione sulla chiave di business:

Una cosa importante da notare riguardo all'istruzione, così come è stata scritta qui, è che aggiorniamo tutti i record esistenti quando viene trovata una corrispondenza tra i dati della tabella di staging e quelli della tabella delle dimensioni pubblicata. Potremmo aggiungere criteri aggiuntivi alla clausola WHEN MATCHED per limitare gli aggiornamenti a quelle istanze in cui un record in staging ha informazioni diverse da quelle trovate nella tabella delle dimensioni, ma dato il numero relativamente piccolo di record in questa particolare tabella, abbiamo scelto di impiegare la logica relativamente più snella mostrata qui. (Useremo la logica aggiuntiva WHEN MATCHED con DimCustomer, che contiene molti più dati.)

Il pattern SCD di Tipo 2

Il pattern SCD di Tipo 2 è un po' più complesso. Per supportare questi tipi di dimensioni, dobbiamo:

1. Estrarre i dati richiesti dal nostro sistema/i operativo/i
2. Eseguire tutte le operazioni di pulizia dei dati necessarie
3. Aggiornare eventuali record di membri ad arrivo tardivo nella tabella di destinazione
4. Scadere eventuali record esistenti nella tabella di destinazione per i quali vengono trovate nuove versioni in staging
5. Inserire eventuali nuovi record (o nuove versioni) nella tabella di destinazione

Fase 1: Estrazione e pulizia dei dati da un sistema sorgente

Come nel pattern SCD di Tipo 1, i nostri primi passi sono estrarre e pulire i dati dal sistema sorgente. Utilizzando lo stesso approccio di cui sopra, emettiamo una query federata e persistiamo i dati estratti in una tabella nel nostro schema di staging:

Fase 2: Confronto con una tabella delle dimensioni

Con questi dati acquisiti, possiamo ora confrontarli con la nostra tabella delle dimensioni al fine di apportare le modifiche ai dati necessarie. La prima di queste è aggiornare sul posto tutti i record contrassegnati come in arrivo in ritardo dai precedenti processi ETL della tabella dei fatti. Si prega di notare che questi aggiornamenti sono limitati ai record contrassegnati come in arrivo in ritardo e il IsLateArriving flag viene reimpostato con l'aggiornamento in modo che questi record si comportino come normali SCD di Tipo 2 in futuro:

Passaggio 3: Scadenza dei record versionati

La prossima serie di modifiche ai dati consiste nel far scadere tutti i record che devono essere versionati. È importante che il valore EndDate che impostiamo per questi corrisponda al StartDate delle nuove versioni dei record che implementeremo nel passaggio successivo. Per questo motivo, imposteremo una variabile timestamp da utilizzare tra questi due passaggi:

NOTA: A seconda dei dati a vostra disposizione, potreste scegliere di utilizzare un valore EndDate proveniente dal sistema sorgente, nel qual caso non dichiarereste necessariamente una variabile come mostrato qui.

Si prega di notare i criteri aggiuntivi utilizzati nella clausola WHEN MATCHED. Poiché stiamo eseguendo una sola operazione con questa istruzione, sarebbe possibile spostare questa logica nella clausola ON, ma l'abbiamo mantenuta separata dalla logica di corrispondenza principale, dove stiamo confrontando la versione corrente del record della dimensione per chiarezza e manutenibilità.

Come parte di questa logica, stiamo facendo un uso intensivo della funzione equal_null(). Questa funzione restituisce TRUE quando il primo e il secondo valore sono uguali o entrambi NULL; altrimenti, restituisce FALSE. Questo fornisce un modo efficiente per cercare modifiche colonna per colonna. Per maggiori dettagli su come Databricks supporta la semantica NULL, si prega di fare riferimento a questo documento.

A questo punto, tutte le versioni precedenti dei record nella tabella delle dimensioni che sono scadute sono state terminate.

Passaggio 4: Inserimento di nuovi record

Ora possiamo inserire nuovi record, sia quelli veramente nuovi che quelli appena versionati:

Come in precedenza, questo avrebbe potuto essere implementato utilizzando un'istruzione INSERT, ma il risultato è lo stesso. Con questa istruzione, abbiamo identificato tutti i record nella tabella di staging che non hanno un record corrispondente non scaduto nelle tabelle delle dimensioni. Questi record vengono semplicemente inseriti con un valore StartDate coerente con eventuali record scaduti che potrebbero esistere in questa tabella.

Passaggi successivi: implementazione dell'ETL della tabella dei fatti

Con le dimensioni implementate e popolate con i dati, possiamo ora concentrarci sulle tabelle dei fatti. Nella Parte 3, dimostreremo come l'ETL per queste tabelle può essere implementato.

Per saperne di più su Databricks SQL, visita il nostro sito web o leggi la documentazione. Puoi anche dare un'occhiata al tour del prodotto per Databricks SQL. Supponi di voler migrare il tuo data warehouse esistente a un data warehouse serverless ad alte prestazioni con un'ottima esperienza utente e un costo totale inferiore. In tal caso, Databricks SQL è la soluzione — provalo gratuitamente.

(Questo post sul blog è stato tradotto utilizzando strumenti basati sull'intelligenza artificiale) Post originale