Implémentation d'un entrepôt de données dimensionnel avec Databricks SQL : Partie 2

Alors que les organisations consolident leurs charges de travail analytiques sur Databricks, elles doivent souvent adapter les techniques traditionnelles d'entrepôt de données. Cette série explore comment implémenter la modélisation dimensionnelle — spécifiquement, les schémas en étoile — sur Databricks. Le premier article de blog était axé sur la conception de schémas. Cet article de blog présente les pipelines ETL pour les tables de dimensions, y compris les modèles de dimensions à évolution lente (SCD) de type 1 et de type 2. La partie 3 vous montre comment construire des pipelines ETL pour les tables de faits.

Dimensions à évolution lente (SCD)

Dans le dernier article de blog, nous avons défini notre schéma en étoile, y compris une table de faits et ses dimensions associées.  Nous avons particulièrement mis en évidence une table de dimensions, DimCustomer, comme illustré ici (avec certains attributs supprimés pour des raisons d'espace) :

Les trois derniers champs de cette table, i.e., StartDate, EndDate et IsLateArriving, représentent des métadonnées qui nous aident à versionner les enregistrements.  Lorsque le revenu, le statut marital, la propriété du logement, le nombre d'enfants à la maison ou d'autres caractéristiques d'un client donné changent, nous voudrons créer de nouveaux enregistrements pour ce client afin que les faits tels que nos transactions de vente en ligne dans FactInternetSales soient associés à la bonne représentation de ce client.  La clé naturelle (également appelée clé métier), CustomerAlternateKey, sera la même pour ces enregistrements, mais les métadonnées différeront, nous permettant de connaître la période pendant laquelle cette version du client était valide, tout comme la clé substitut, CustomerKey, permettant à nos faits de se lier à la bonne version.  

NOTE : Étant donné que la clé substitut est couramment utilisée pour lier les faits et les dimensions, les tables de dimensions sont souvent groupées sur cette clé. Contrairement aux bases de données relationnelles traditionnelles qui utilisent des index b-tree sur des enregistrements triés, Databricks implémente une méthode de groupement unique connue sous le nom de groupement liquide. Bien que les spécificités du groupement liquide sortent du cadre de cet article de blog, nous utilisons systématiquement la clause CLUSTER BY sur la clé substitut de nos tables de dimensions lors de leur définition pour tirer parti de cette fonctionnalité efficacement.

Ce modèle de versionnement des enregistrements de dimension lorsque les attributs changent est connu sous le nom de modèle de Dimension à évolution lente de type 2 (ou simplement SCD de type 2). Le modèle SCD de type 2 est préféré pour enregistrer les données de dimension dans la méthodologie dimensionnelle classique. Cependant, il existe d'autres façons de gérer les changements dans les enregistrements de dimension.

L'une des méthodes les plus courantes pour gérer les valeurs de dimension changeantes consiste à mettre à jour les enregistrements existants en place.  Une seule version de l'enregistrement est créée, de sorte que la clé métier reste l'identifiant unique de l'enregistrement.  Pour diverses raisons, notamment les performances et la cohérence, nous implémentons toujours une clé substitut et lions nos enregistrements de faits à ces dimensions sur ces clés. Néanmoins, les champs de métadonnées StartDate et EndDate qui décrivent les intervalles de temps pendant lesquels un enregistrement de dimension donné est considéré comme actif ne sont pas nécessaires. C'est ce qu'on appelle le modèle de SCD de type 1.  La dimension Promotion dans notre schéma en étoile fournit un bon exemple d'implémentation de table de dimension de type 1 :

Mais qu'en est-il du champ de métadonnées IsLateArriving vu dans la dimension client de type 2 mais absent de la dimension de type 1 ? Ce champ est utilisé pour marquer les enregistrements comme arrivant tardivement.  Un enregistrement arrivant tardivement est un enregistrement dont la clé métier apparaît lors d'un cycle ETL de faits, mais pour lequel aucun enregistrement n'est trouvé lors du traitement préalable des dimensions.  Dans le cas des SCD de type 2, ce champ est utilisé pour indiquer que lorsque les données d'un enregistrement arrivant tardivement sont observées pour la première fois dans un cycle ETL de dimension, l'enregistrement doit être mis à jour en place (comme dans un modèle SCD de type 1) puis versionné à partir de ce moment-là.  Dans le cas des SCD de type 1, ce champ n'est pas nécessaire car l'enregistrement sera mis à jour en place quoi qu'il arrive.

NOTE : Le Kimball Group reconnaît des modèles SCD supplémentaires, dont la plupart sont des variations et des combinaisons des modèles de Type 1 et de Type 2. Étant donné que les SCD de Type 1 et de Type 2 sont les plus fréquemment implémentés parmi ces modèles et que les techniques utilisées avec les autres sont étroitement liées à ce qui est employé avec ceux-ci, nous limitons ce blog à ces deux types de dimensions. Pour plus d'informations sur les huit types de SCD reconnus par le Kimball Group, veuillez consulter la section Techniques de dimensionnalisation à évolution lente de ce document.

Implémentation du modèle SCD de Type 1

Avec les données mises à jour sur place, le modèle de flux de travail SCD de Type 1 est le plus simple des deux modèles ETL dimensionnels. Pour prendre en charge ces types de dimensions, nous nous contentons de :

1. Extraire les données requises de nos systèmes opérationnels
2. Effectuer les opérations de nettoyage de données requises
3. Comparer nos enregistrements entrants à ceux déjà présents dans la table de dimension
4. Mettre à jour les enregistrements existants où les attributs entrants diffèrent de ce qui est déjà enregistré
5. Insérer les enregistrements entrants qui n'ont pas d'enregistrement correspondant dans la table de dimension

Pour illustrer une implémentation SCD de Type 1, nous allons définir l'ETL pour la population continue de la table DimPromotion.

Étape 1 : Extraire les données d'un système opérationnel

Notre première étape consiste à extraire les données de notre système opérationnel. Comme notre entrepôt de données est basé sur la base de données d'exemple AdventureWorksDW fournie par Microsoft, nous utilisons la base de données d'exemple AdventureWorks (OLTP) associée comme source. Cette base de données a été déployée sur une instance Azure SQL Database et rendue accessible dans notre environnement Databricks via une requête fédérée. L'extraction est ensuite facilitée par une requête simple (avec certains champs masqués pour économiser de l'espace), les résultats de la requête étant persistés dans une table de notre schéma staging (qui n'est accessible qu'aux ingénieurs de données de notre environnement via des paramètres de permission non montrés ici). Ceci n'est qu'une des nombreuses façons d'accéder aux données du système source dans cet environnement :

Étape 2 : Comparer les enregistrements entrants à ceux de la table

En supposant que nous n'avons pas d'étapes de nettoyage de données supplémentaires à effectuer (que nous pourrions implémenter avec une instruction UPDATE ou une autre instruction CREATE TABLE AS), nous pouvons alors aborder nos opérations de mise à jour/insertion de données de dimension en une seule étape en utilisant une instruction MERGE, en faisant correspondre nos données mises en scène et nos données de dimension sur la clé métier :

Une chose importante à noter concernant l'instruction, telle qu'elle est écrite ici, est que nous mettons à jour tous les enregistrements existants lorsqu'une correspondance est trouvée entre les données mises en scène et les données de la table de dimension publiées. Nous pourrions ajouter des critères supplémentaires à la clause WHEN MATCHED pour limiter les mises à jour aux cas où un enregistrement dans staging contient des informations différentes de celles trouvées dans la table de dimension, mais étant donné le nombre relativement faible d'enregistrements dans cette table particulière, nous avons choisi d'employer la logique relativement plus légère montrée ici. (Nous utiliserons la logique WHEN MATCHED supplémentaire avec DimCustomer, qui contient beaucoup plus de données.)

Le modèle SCD de Type 2

Le modèle SCD de Type 2 est un peu plus complexe. Pour prendre en charge ces types de dimensions, nous devons :

1. Extraire les données requises de nos systèmes opérationnels
2. Effectuer les opérations de nettoyage de données requises
3. Mettre à jour les enregistrements de membres arrivant tardivement dans la table cible
4. Expirer les enregistrements existants dans la table cible pour lesquels de nouvelles versions sont trouvées dans staging
5. Insérer les nouveaux enregistrements (ou les nouvelles versions d'enregistrements) dans la table cible

Étape 1 : Extraire et nettoyer les données d'un système source

Comme dans le modèle SCD de Type 1, nos premières étapes consistent à extraire et à nettoyer les données du système source. En utilisant la même approche que ci-dessus, nous émettons une requête fédérée et persistons les données extraites dans une table de notre schéma staging :

Étape 2 : Comparer à une table de dimension

Une fois ces données chargées, nous pouvons maintenant les comparer à notre table de dimensions afin d'apporter les modifications de données nécessaires.   La première de ces modifications consiste à mettre à jour sur place tous les enregistrements marqués comme arrivant tardivement par les processus ETL précédents de la table de faits.   Veuillez noter que ces mises à jour sont limitées aux enregistrements marqués comme arrivant tardivement et que le drapeau IsLateArriving est réinitialisé avec la mise à jour afin que ces enregistrements se comportent comme des SCD de type 2 normaux par la suite :

Étape 3 : Expirer les enregistrements versionnés

Le prochain ensemble de modifications de données consiste à expirer tous les enregistrements qui doivent être versionnés.   Il est important que la valeur EndDate que nous définissons pour ceux-ci corresponde à la StartDate des nouvelles versions d'enregistrements que nous implémenterons à l'étape suivante.   Pour cette raison, nous allons définir une variable de timestamp à utiliser entre ces deux étapes :

NOTE : Selon les données dont vous disposez, vous pouvez choisir d'utiliser une valeur EndDate provenant du système source, auquel cas vous ne déclareriez pas nécessairement une variable comme indiqué ici.

Veuillez noter le critère supplémentaire utilisé dans la clause WHEN MATCHED.   Comme nous n'effectuons qu'une seule opération avec cette instruction, il serait possible de déplacer cette logique dans la clause ON, mais nous l'avons gardée séparée de la logique de correspondance principale, où nous faisons correspondre la version actuelle de l'enregistrement de dimension pour plus de clarté et de maintenabilité.

Dans le cadre de cette logique, nous utilisons intensivement la fonction equal_null().   Cette fonction renvoie TRUE lorsque la première et la seconde valeur sont identiques ou toutes deux NULL ; sinon, elle renvoie FALSE.   Cela offre un moyen efficace de rechercher des modifications colonne par colonne.   Pour plus de détails sur la façon dont Databricks prend en charge la sémantique NULL, veuillez vous référer à ce document.

À ce stade, toutes les versions précédentes des enregistrements dans la table de dimensions qui ont expiré ont été clôturées.   

Étape 4 : Insérer de nouveaux enregistrements

Nous pouvons maintenant insérer de nouveaux enregistrements, à la fois véritablement nouveaux et nouvellement versionnés :

Comme précédemment, cela aurait pu être implémenté à l'aide d'une instruction INSERT, mais le résultat est le même.   Avec cette instruction, nous avons identifié tous les enregistrements de la table de staging qui n'ont pas d'enregistrement correspondant non expiré dans les tables de dimensions. Ces enregistrements sont simplement insérés avec une valeur StartDate cohérente avec tous les enregistrements expirés qui peuvent exister dans cette table.

Prochaines étapes : implémentation de l'ETL de la table de faits

Avec les dimensions implémentées et peuplées de données, nous pouvons maintenant nous concentrer sur les tables de faits. Dans la Partie 3, nous montrerons comment l'ETL pour ces tables peut être implémenté.

Pour en savoir plus sur Databricks SQL, visitez notre site web ou consultez la documentation. Vous pouvez également découvrir la visite guidée du produit pour Databricks SQL. Supposons que vous souhaitiez migrer votre entrepôt existant vers un entrepôt de données serveurless haute performance avec une excellente expérience utilisateur et un coût total réduit. Dans ce cas, Databricks SQL est la solution — essayez-le gratuitement.

(Cet article de blog a été traduit à l'aide d'outils basés sur l'intelligence artificielle) Article original