Implémentation d'un entrepôt de données dimensionnel avec Databricks SQL, partie 3

La modélisation dimensionnelle est une approche éprouvée pour construire des entrepôts de données prêts pour l'analytique. Bien que de nombreuses organisations se tournent vers des plateformes modernes comme Databricks, ces techniques fondamentales s'appliquent toujours.

Dans la première partie, nous avons conçu notre schéma dimensionnel. Dans la deuxième partie, nous avons créé des pipelines ETL pour les tables de dimensions. Maintenant, dans la troisième partie, nous implémentons la logique ETL pour les tables de faits, en mettant l'accent sur l'efficacité et l'intégrité.

Tables de faits et extractions delta

Dans le premier article, nous avons défini la table de faits, FactInternetSales, comme illustré ci-dessous. Comparée à nos tables de dimensions, la table de faits est relativement étroite en termes de longueur d'enregistrement, avec seulement des références de clés étrangères vers nos tables de dimensions, nos mesures de faits, nos champs de dimensions dégénérées et un seul champ de métadonnées :

NOTE : Dans l'exemple ci-dessous, nous avons modifié l'instruction CREATE TABLE de notre premier article pour inclure les définitions de clés étrangères au lieu de les définir dans des instructions ALTER TABLE distinctes. Nous avons également inclus une contrainte de clé primaire sur les champs de dimensions dégénérées pour être plus explicites sur leur rôle dans cette table de faits.

La définition de la table est assez simple, mais il convient de prendre un moment pour discuter du champ de métadonnées LastModifiedDateTime. Bien que les tables de faits soient relativement étroites en termes de nombre de champs, elles ont tendance à être très profondes en termes de nombre de lignes. Les tables de faits hébergent souvent des millions, voire des milliards, d'enregistrements, souvent dérivés d'activités opérationnelles à haut volume. Au lieu de tenter de recharger la table avec une extraction complète à chaque cycle ETL, nous limiterons généralement nos efforts aux nouveaux enregistrements et à ceux qui ont été modifiés.

Selon le système source et son infrastructure sous-jacente, il existe de nombreuses façons d'identifier les enregistrements opérationnels qui doivent être extraits lors d'un cycle ETL donné. Les capacités de capture de données de modification (CDC) implémentées du côté opérationnel sont les mécanismes les plus fiables. Mais lorsqu'elles sont indisponibles, nous nous rabattons souvent sur les horodatages enregistrés à chaque transaction lors de sa création et de sa modification. Cette approche n'est pas infaillible pour la détection des changements, mais comme tout développeur ETL expérimenté le confirmera, c'est souvent le mieux que nous ayons.

NOTE : L'introduction de Lakeflow Connect offre une option intéressante pour effectuer la capture de données de modification sur les bases de données relationnelles. Cette fonctionnalité est en préversion au moment de la rédaction de cet article. Néanmoins, à mesure que cette capacité mûrit et s'étend à de plus en plus de SGBDR, nous nous attendons à ce qu'elle fournisse un mécanisme efficace pour les extractions incrémentielles.

Dans notre table de faits, le champ LastModifiedDateTime capture une telle valeur d'horodatage enregistrée dans le système opérationnel. Avant d'extraire les données de notre système opérationnel, nous examinerons la table de faits pour identifier la dernière valeur de ce champ que nous avons enregistrée. Cette valeur sera le point de départ de notre extraction incrémentielle (également appelée delta).

Le flux de travail ETL des faits

Le flux de travail de haut niveau pour notre ETL de faits se déroulera comme suit :

1. Récupérer la dernière valeur LastModifiedDateTime de notre table de faits.
2. Extraire les données transactionnelles pertinentes du système source avec des horodatages à la date de la dernière LastModifiedDateTime valeur ou après.
3. Effectuer les étapes de nettoyage de données supplémentaires requises sur les données extraites.
4. Publier les valeurs de membres arrivant tardivement aux dimensions associées.
5. Rechercher les valeurs de clés étrangères auprès des dimensions associées.
6. Publier les données dans la table de faits.

Pour faciliter la compréhension de ce workflow, nous allons décrire ses phases clés dans les sections suivantes. Contrairement à l'article sur l'ETL de dimension, nous implémenterons notre logique pour ce workflow en utilisant une combinaison de SQL et de Python, en fonction du langage qui rend chaque étape la plus simple à implémenter. Encore une fois, l'un des points forts de la plateforme Databricks est son support de plusieurs langages. Au lieu de le présenter comme un choix tout ou rien fait au début d'une implémentation, nous montrerons comment les ingénieurs de données peuvent rapidement basculer entre les deux au sein d'une même implémentation.

Étapes 1-3 : Phase d'extraction Delta

Les deux premières étapes de notre workflow se concentrent sur l'extraction des informations nouvelles et récemment mises à jour de notre système opérationnel. Dans la première étape, nous effectuons une simple recherche de la dernière valeur enregistrée pour LastModifiedDateTime. Si la table de faits est vide, comme elle devrait l'être lors de l'initialisation, nous définissons une valeur par défaut suffisamment éloignée dans le temps pour capturer toutes les données pertinentes du système source :

Nous pouvons maintenant extraire les données requises de notre système opérationnel en utilisant cette valeur. Bien que cette requête inclue beaucoup de détails, concentrez votre attention sur la clause WHERE, où nous utilisons la dernière valeur de timestamp observée de l'étape précédente pour récupérer les éléments individuels qui sont nouveaux ou modifiés (ou associés à des commandes de vente nouvelles ou modifiées) :

Comme précédemment, les données extraites sont persistées dans une table de notre schéma de staging, accessible uniquement à nos ingénieurs de données, avant de passer aux étapes suivantes du workflow. Si nous avons des nettoyages de données supplémentaires à effectuer, nous devrions le faire maintenant.

Étape 4 : Phase des membres arrivant tardivement

La séquence typique dans un cycle ETL d'un entrepôt de données consiste à exécuter nos workflows ETL de dimension, puis nos workflows de faits peu de temps après. En organisant nos processus de cette manière, nous pouvons mieux garantir que toutes les informations nécessaires pour connecter nos enregistrements de faits aux données de dimension seront en place. Cependant, il existe une courte fenêtre pendant laquelle les nouvelles données orientées dimension arrivent et sont capturées par un enregistrement transactionnel pertinent pour les faits. Cette fenêtre s'agrandit si nous avons une défaillance dans le cycle ETL global qui retarde l'extraction des données de faits. Et, bien sûr, il peut toujours y avoir des défaillances de référence dans les systèmes sources qui permettent à des données douteuses d'apparaître dans un enregistrement transactionnel.

Pour nous prémunir contre ce problème, nous allons insérer dans une table de dimension donnée toutes les valeurs de clés métier trouvées dans nos données de faits mises en staging mais pas dans l'ensemble des enregistrements actuels (non expirés) pour cette dimension. Cette approche créera un enregistrement avec une clé métier (naturelle) et une clé substitut que notre table de faits pourra référencer. Ces enregistrements seront marqués comme arrivant tardivement si la dimension ciblée est une SCD de Type 2, afin que nous puissions mettre à jour de manière appropriée lors du prochain cycle ETL.

Pour commencer, nous allons compiler une liste des champs clés de notre staging. Ici, nous exploitons des conventions de nommage strictes qui nous permettent d'identifier ces champs dynamiquement :

NOTE : Nous passons à Python pour les exemples de code suivants. Databricks prend en charge l'utilisation de plusieurs langages, même au sein du même workflow. Dans cet exemple, Python nous offre un peu plus de flexibilité tout en restant aligné sur les concepts SQL, rendant cette approche accessible à davantage de développeurs SQL traditionnels.

Remarquez que nous avons séparé nos clés de date des autres clés métier. Nous y reviendrons bientôt, mais pour l'instant, concentrons-nous sur les clés non-date (autres) de cette table.

Pour chaque clé métier non-date, nous pouvons utiliser nos conventions de nommage de champs et de tables pour identifier la table de dimension qui devrait contenir cette clé, puis effectuer une jointure left-semi (similaire à une comparaison NOT IN() mais supportant la correspondance multi-colonnes si nécessaire) pour identifier les valeurs de cette colonne dans la table de staging mais pas dans la table de dimension. Lorsque nous trouvons une valeur non correspondante, nous l'insérons simplement dans la table de dimension avec le paramètre approprié pour le champ IsLateArriving :

Cette logique fonctionnerait bien pour nos références de dimension de date si nous voulions nous assurer que nos enregistrements de faits sont liés à des entrées valides. Cependant, de nombreux systèmes de BI en aval implémentent une logique qui exige que la dimension de date contienne une série continue et ininterrompue de dates entre les valeurs les plus anciennes et les plus récentes enregistrées. Si nous rencontrons une date avant ou après la plage de valeurs dans la table, nous devons non seulement insérer le membre manquant, mais aussi créer les valeurs supplémentaires nécessaires pour préserver une plage ininterrompue. Pour cette raison, nous avons besoin d'une logique légèrement différente pour les dates arrivant tardivement :

Si vous n'avez pas beaucoup travaillé avec Databricks ou Spark SQL, la requête au cœur de cette dernière étape vous est probablement étrangère.  La fonction sequence() crée une séquence de valeurs basée sur un début et une fin spécifiés.  Le résultat est un tableau que nous pouvons ensuite décomposer (en utilisant la fonction explode()) afin que chaque élément du tableau forme une ligne dans un jeu de résultats.  À partir de là, nous comparons simplement la plage requise à ce qui se trouve dans la table de dimension pour identifier les éléments qui doivent être insérés.  Avec cette insertion, nous nous assurons d'avoir une valeur de clé substitut implémentée dans cette dimension sous forme de clé intelligente afin que nos enregistrements de faits aient quelque chose à référencer. 

Étapes 5 - 6 : Phase de publication des données

Maintenant que nous pouvons être sûrs que toutes les clés métier de notre table de staging peuvent être mises en correspondance avec des enregistrements dans leurs dimensions correspondantes, nous pouvons procéder à la publication dans la table de faits.

La première étape de ce processus consiste à rechercher les valeurs de clé étrangère pour ces clés métier.  Cela peut être fait dans le cadre d'une seule étape de publication, mais le grand nombre de jointures dans la requête rend souvent cette approche difficile à maintenir.  Pour cette raison, nous pouvons adopter une approche moins efficace mais plus facile à comprendre et à modifier, consistant à rechercher les valeurs de clé étrangère une clé métier à la fois et à ajouter ces valeurs à notre table de staging :

Encore une fois, nous exploitons les conventions de nommage pour rendre cette logique plus simple à implémenter.  Parce que notre dimension de date est une dimension jouant plusieurs rôles et suit donc une convention de nommage plus variable, nous implémentons une logique légèrement différente pour ces clés métier.

À ce stade, notre table de staging contient des clés métier et des valeurs de clés substituts ainsi que nos mesures, nos champs de dimension dégénérés et la valeur LastModifiedDate extraite de notre système source. Pour rendre la publication plus gérable, nous devons aligner les champs disponibles avec ceux pris en charge par la table de faits.  Pour ce faire, nous devons supprimer les clés métier :

NOTE : Le dataframe source est défini dans le bloc de code précédent.

Avec les champs alignés, l'étape de publication est simple. Nous mettons en correspondance nos enregistrements entrants avec ceux de la table de faits en fonction des champs de dimension dégénérés, qui servent d'identifiant unique pour nos enregistrements de faits, puis mettons à jour ou insérons les valeurs si nécessaire :

Prochaines étapes

Nous espérons que cette série de blogs a été informative pour ceux qui cherchent à construire des modèles dimensionnels sur la plateforme Databricks.  Nous nous attendons à ce que beaucoup d'expérimentés avec cette approche de modélisation de données et les flux de travail ETL associés la trouvent familière, accessible et capable de supporter des modèles établis avec des changements minimes par rapport à ce qui aurait pu être implémenté sur des plateformes RDBMS.  Là où des changements apparaissent, comme la capacité d'implémenter une logique de flux de travail en utilisant une combinaison de Python et SQL, nous espérons que les ingénieurs de données trouveront que cela rend leur travail plus simple à implémenter et à supporter au fil du temps.

Pour en savoir plus sur Databricks SQL, visitez notre site web ou lisez la documentation. Vous pouvez également consulter la visite guidée du produit pour Databricks SQL. Si vous souhaitez migrer votre entrepôt existant vers un entrepôt de données sans serveur haute performance avec une excellente expérience utilisateur et un coût total réduit, 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