Sommaire

Schéma en étoile ou OBT : une analyse de la meilleure solution pour votre data warehouse

Les résultats : Les tables dénormalisées permettent une réponse plus rapide aux requêtes‍

Détails de l'analyse‍

Autres considérations

La modélisation d'un data warehouse est une partie cruciale mais négligée du développement d'un data warehouse. La modélisation d’un data warehouse est le processus de création de schémas pour les informations résumées et détaillées d'un data warehouse.

Votre data warehouse est essentiellement un référentiel de données collectées à partir de plusieurs sources qui contiennent des données liées ou similaires dans différents formats. La modélisation du data warehouse permet de cartographier ces différentes structures et formats afin de s'assurer que les données entrantes respectent la conception de votre data warehouse. Cela permet de rendre vos données productives pour l'exploration et l'analyse des données.

Considérez votre modèle de data warehouse comme un langage intermédiaire qui permet à vos systèmes de coordonner leur acceptation et leur compréhension des données mentionnées dans le modèle. Le choix de votre schéma peut affecter les performances du data warehouse. Nombreux sont ceux qui se demandent, et certains prétendent même le savoir, si le schéma en étoile ou une grande table (OBT) offre les meilleures performances qui soient dans un data warehouse. Dans cet article, nous avons effectué quelques tests pour vous aider à prendre la bonne décision. Mais avant cela, découvrons les schémas de data warehouse que vous allez utiliser.

[CTA_MODULE]

Qu'est-ce qu'un schéma en étoile ?

Un schéma en étoile utilise une table centrale, appelée table des faits, pour stocker les données mesurées ou transactionnelles. Une table de faits utilise des faits agrégés pour stocker des informations commerciales clés, telles que des données financières ou des données de performance enregistrées. Les tables de faits sont liées à plusieurs tables dimensionnelles qui stockent les attributs relatifs aux données de la table de faits.

Le schéma en étoile est généralement considéré comme idéal pour les rapports, car il facilite la recherche des données. Il est également très pratique d'utiliser des jointures dans les requêtes du schéma en étoile, ce qui peut améliorer les performances des requêtes.

Qu'est-ce que l'OBT ?

OBT signifie « one big table » (une grande table). Comme son nom l'indique, il s'agit de l'utilisation d'une table unique pour regrouper toutes les données dans une seule grande table. Cette approche permet de s'assurer que le data warehouse n'a pas à effectuer de jointures à la volée. En raison de sa simplicité, l'OBT convient aux petites équipes et aux petits projets qui se concentrent sur le suivi d'un élément spécifique. Il s'agit généralement d'un élément auquel sont associés plusieurs attributs.

Par exemple, si vous souhaitez utiliser votre data warehouse pour l'analyse des clients, votre OBT sera axé sur les « clients » avec des attributs tels que l'identifiant du client, le nom, l'âge, etc.

Schéma en étoile ou OBT : Une analyse de la meilleure solution pour votre data warehouse

L'objectif de cette analyse est de comprendre les implications en termes de performances de ces différents modèles de modélisation de data warehouse dans le cadre de charges de travail normales de type BI au sein d'un data warehouse donné. En d'autres termes, nous n'essayons pas de comparer les data warehouse entre eux ou de comprendre leurs performances relatives et les arbitrages en termes de coûts. Nous voulons comprendre comment les différents modèles de modélisation de data warehouse fonctionnent une fois que vous avez choisi le data warehouse à utiliser.

En particulier, cette analyse est axée sur les modèles d'architecture destinés à prendre en charge les charges de travail de type BI, et pas nécessairement sur les performances de requêtes ad hoc diverses et arbitrairement complexes. Conformément à la manière dont de nombreuses personnes développent leurs data warehouses aujourd'hui (en utilisant un paradigme ELT avec un outil comme dbt), le schéma en étoile est créé à la fin d'une exécution ELT et est explicitement conçu pour prendre en charge des requêtes de type BI dans des outils comme Looker ou Periscope. Dans cette optique, les requêtes que nous utilisons pour tester ces différents styles de distribution ne sont pas particulièrement complexes, car elles sont intentionnellement conçues pour refléter les types courants de requêtes exécutées par un outil BI - en agrégeant des mesures sur une variété de dimensions différentes, avec parfois une fonction CTE ou une fonction fenêtre.

Vous pouvez consulter les requêtes que nous avons utilisées pour le test ici.

Les résultats : les tables dénormalisées permettent une réponse plus rapide aux requêtes

Pour les trois data warehouses testés - Redshift, Snowflake et BigQuery - l'utilisation d'une seule table dénormalisée au lieu d'un schéma en étoile entraîne une amélioration substantielle des temps de requête. L'utilisation d'une seule table dénormalisée entraîne une augmentation de la vitesse de 25 à 50 %, en fonction du data warehouse utilisé. Cela représente une différence d'environ 10 secondes sur un cluster à nœud unique dans Redshift. Si l'on exclut le temps de compilation des requêtes Redshift, les améliorations sont les suivantes :

• Redshift : 25 % à 30 % (en fonction de la taille du data warehouse et du nombre de clusters)
• Snowflake : ~25 %
• BigQuery : ~50 %

Redshift

Pour les résultats Redshift, nous présentons des données provenant d’exécutions utilisant un grand cluster multi-nœuds ainsi qu'un petit cluster à un seul nœud. Nous avons également divisé les résultats entre la première fois qu'une requête a été exécutée (ce qui inclut le temps nécessaire à Redshift pour compiler la requête) et les exécutions suivantes qui incluent uniquement le temps de calcul.

Nœud unique

Première exécution

Exécutions suivantes

Multi-nœuds

Première exécution

Exécutions suivantes

On constate ici que le modèle OBT (dénormalisé) est plus performant que le modèle du schéma en étoile pour toutes les requêtes testées, sauf une. À l'exception de l'énigme de la requête 4, la table dénormalisée surpasse le schéma en étoile de 10 % à 45 %, selon la requête.

Snowflake

Pour Snowflake, les résultats sont plus mitigés. Alors que le modèle OBT (dénormalisé) est nettement plus rapide que le schéma en étoile pour les requêtes les plus lentes (requêtes 8, 9 et 10), le schéma en étoile semble en fait plus performant que le modèle OBT pour certaines des requêtes les plus simples (à savoir les requêtes 3, 4 et 7). Notez que ces requêtes incluent le temps de compilation de la requête.

Notre compréhension du fonctionnement interne de Snowflake n’est pas suffisante pour éclairer les raisons de ce phénomène, mais si l'un de nos lecteurs est un expert de Snowflake, nous serions ravis d'entendre vos hypothèses !

Google Analytics BigQuery

Pour BigQuery, les résultats sont encore plus spectaculaires que ce que nous avons vu dans Redshift - l'amélioration moyenne du temps de réponse des requêtes est de 49 %, la table dénormalisée étant plus performante que le schéma en étoile dans toutes les catégories. Notez que ces requêtes incluent le temps de compilation de la requête.

Il est intéressant de noter à quel point les variances des temps de réponse des requêtes sont radicalement différentes entre les deux styles de distribution. Le schéma en étoile a une variance beaucoup plus élevée dans le temps de réponse des requêtes, ce qui, nous le supposons, est lié à la façon dont BigQuery planifie l'exécution (mais n’étant pas des experts BQ, nous aimerions que quelqu'un ayant plus de connaissances nous éclaire sur ce qui se passe ici).

Détails de l'analyse

Cette comparaison a été effectuée à l'aide d'un sous-ensemble de données provenant du test de référence TPC-DS, gracieusement mis à disposition par des employés de Fivetran. Pour toutes les analyses, nous avons utilisé les données TPC-DS « 100 ».

• Redshift :
• dc2.large avec 1 nœud
• dc2.8xlarge cluster avec trois nœuds
• Snowflake :
• Data warehouse X-Large (16 serveurs)
• Bigquery :
• J'ai utilisé la configuration par défaut fournie avec un nouveau data warehouse

Nous utilisons les tables suivantes : store_sales, date_dim, store, household_demographics, customer_address.

Pour le schéma en étoile, nous avons conservé les tables telles quelles (en distribuant la table des faits par clé ss_item_key et en distribuant les tables de dimensions sur tous les nœuds. Dans Redshift, nous effectuons également une distribution par ss_item_key).

Pour le test du temps de réponse, nous désactivons les mécanismes de mise en cache des requêtes conformément à ces documents pour Redshift, ces documents pour BigQuery et ces documents pour Snowflake.

Pour Snowflake, nous excluons l'exécution initiale des requêtes qui servent à « préchauffer le cache » en lisant les données de S3 sur le SSD que Snowflake utilise pour la mise en cache. Bien que les résultats de la requête ne soient pas directement mis en cache, nous voulons évaluer Snowflake dans une situation dans laquelle les données ont été lues sur S3. Vous pouvez en savoir plus sur la mise en cache Snowflake ici.

Pour les tables dénormalisées, nous effectuons une simple jointure pour tout rassembler.

SELECT 
  * 
FROM 
  public.store_sales 
  LEFT JOIN public.date_dim ON store_sales.ss_sold_date_sk = date_dim.d_date_sk 
  LEFT JOIN public.store ON store_sales.ss_store_sk = store.s_store_sk 
  LEFT JOIN public.household_demographics ON store_sales.ss_hdemo_sk = household_demographics.hd_demo_sk 
  LEFT JOIN public.customer_address ON store_sales.ss_addr_sk = customer_address.ca_address_sk

L'ensemble du code permettant de reproduire l'analyse se trouve dans ce référentiel.

Autres considérations

Il y a plusieurs raisons pour lesquelles vous pouvez envisager d'utiliser le schéma en étoile (ou quelque chose de similaire) :

• Le schéma en étoile permet d'améliorer la conceptualisation et l'organisation du code ELT / ETL.
• Le schéma en étoile est plus facile à utiliser pour les utilisateurs finaux (analystes et autres rédacteurs de requêtes).
• Le schéma en étoile occupe moins d'espace disque.

Bien que les deux premières préoccupations soient importantes, nous pensons qu'elles peuvent être gérées assez facilement en organisant votre processus ELT de manière à ce que les données soient toutes transformées en quelque chose comme un schéma en étoile avant que tout ne soit à nouveau joint pour la requête de l'utilisateur final.

Le troisième point mérite plus d'attention, en particulier dans un data warehouse comme Redshift - la matérialisation des données dénormalisées occupe une quantité importante d'espace disque sur le cluster. Le simple fait de matérialiser la table a fait passer l'utilisation de l'espace disque d'un peu plus de 30 gigaoctets à plus de 90.

Il ne s'agit que d'un sous-ensemble des données que nous aurions pu joindre à store_sales ! En fait, lorsque nous avons commencé cette tâche d'analyse, nous voulions joindre toutes les dimensions possibles à store_sales, mais nous n'avons pas pu le faire parce que Redshift manquait d'espace disque (sur un cluster dc2.large avec un seul nœud).

En fonction de la taille de vos données, le coût de stockage de la duplication de toutes les dimensions sur le disque pourrait être trop élevé.

Contactez-moi

Si vous avez des questions ou des réflexions sur cette analyse, j'aimerais les entendre. Vous pouvez me joindre par e-mail à l'adresse kaminsky.michael@gmail.com, ou me trouver sur mon blog locallyoptimistic.com.

Remarques

La raison pour laquelle le schéma en étoile est plus performant que la table dénormalisée pour la requête 4 dans le cluster à un nœud (mais pas dans le cluster à plusieurs nœuds) est laissée à l'appréciation du lecteur. Essentiellement, parce que je n'en ai aucune idée.

Comme dbt n'a pas la possibilité de spécifier la compression des colonnes ou le style d'encodage dans Redshift, c'est probablement le pire cas possible en termes de taille de stockage sur disque. Je pense qu'un codage correct des colonnes permettrait de résoudre une grande partie de ce problème.

[CTA_MODULE]