Cas pratiques BigQuery

Contexte : Un fichier CSV contenant des données clients est stocké dans Google Cloud Storage. Le Problème : Les données doivent être importées dans BigQuery pour analyse. Contraintes : Créer une table adaptée. Utiliser une commande LOAD DATA ou l’interface BigQuery pour importer depuis GCS. Respecter le schéma du fichier. Livrable attendu : Fournir la commande SQL ou configuration nécessaire pour charger correctement les données CSV dans BigQuery.

Contexte : Un dataset a été créé en région US alors que l’entreprise doit stocker ses données en Europe pour conformité. Le Problème : Les contraintes réglementaires ne sont pas respectées. Contraintes : Recréer le dataset dans la région EU. Migrer les données existantes sans perte. Livrable attendu : Proposer une stratégie de migration vers un dataset conforme aux exigences régionales.

Contexte : Un analyste tente d’exécuter une requête sur un dataset mais reçoit une erreur d’autorisation. Le Problème : Le compte utilisateur ne dispose pas du rôle BigQuery Data Viewer. Contraintes : Accorder les permissions minimales nécessaires sur le dataset concerné. Respecter le principe du moindre privilège. Livrable attendu : Proposer la commande ou configuration IAM permettant d’accorder un accès en lecture au dataset.

Contexte : Une table BigQuery contient une colonne transaction_date définie comme STRING. Le Problème : Les requêtes utilisant des filtres de date sont inefficaces. Extrait actuel : transaction_date STRING Contraintes : Corriger le type en DATE ou TIMESTAMP. Maintenir l’intégrité des données existantes. Livrable attendu : Proposer une stratégie de correction du schéma permettant d’utiliser efficacement les fonctions de date BigQuery.

Contexte : Une requête analytique mensuelle entraîne des coûts élevés. Le Problème : La requête ne filtre pas par date sur une table partitionnée. Extrait actuel : SELECT * FROM sales_data.transactions; Toutes les partitions sont scannées. Contraintes : Utiliser un filtre sur la colonne partitionnée transaction_date. Réduire le volume de données scanné. Livrable attendu : Proposer une requête optimisée limitant le scan aux données nécessaires.

Contexte : Les analystes utilisent systématiquement SELECT * dans leurs requêtes. Le Problème : Cela augmente le volume de données scanné et les coûts associés. Extrait actuel : SELECT * FROM sales_data.transactions WHERE amount > 100; Contraintes : Sélectionner uniquement les colonnes nécessaires. Réduire le coût du scan. Livrable attendu : Proposer une requête optimisée limitant les colonnes sélectionnées.

Contexte : Votre entreprise migre ses données analytiques vers Google BigQuery afin de centraliser les rapports mensuels. Le Problème : Aucun dataset ni table n’a encore été créé pour stocker les ventes. Contraintes : Créer un dataset nommé sales_data dans une région spécifique. Définir une table transactions avec des colonnes id, transaction_date et amount. Utiliser des types adaptés. Livrable attendu : Fournir les commandes SQL BigQuery permettant de créer le dataset et la table structurée pour les futures analyses.

Contexte : L’équipe marketing souhaite connaître le nombre de transactions par pays. Le Problème : Aucune requête agrégée n’a encore été écrite. Contraintes : Utiliser GROUP BY sur la colonne country. Compter le nombre de transactions. Livrable attendu : Fournir une requête SQL BigQuery permettant d’obtenir le nombre de transactions par pays.

Contexte : L’équipe finance souhaite consulter facilement le total des ventes par mois. Le Problème : Les requêtes actuelles doivent être réécrites manuellement à chaque analyse. Contraintes : Créer une vue SQL calculant SUM(amount) groupé par mois à partir de la table transactions. Utiliser des fonctions de date adaptées. Livrable attendu : Fournir la requête CREATE VIEW permettant d’automatiser l’accès aux données agrégées mensuelles.

Contexte : Une table transactions contient plusieurs millions de lignes. Le Problème : Les requêtes mensuelles scannent toute la table. Contraintes : Créer une table partitionnée sur transaction_date. Optimiser pour les filtres temporels. Livrable attendu : Fournir la commande CREATE TABLE avec partitioning permettant de réduire les coûts et améliorer les performances.

Contexte : Votre entreprise analyse des centaines de millions de transactions par an et observe une augmentation progressive des coûts de requêtes. Le Problème : La table actuelle est uniquement partitionnée par date, mais les requêtes filtrent également très souvent par country et product_id. Contraintes : Conserver la partition sur transaction_date. Ajouter un clustering pertinent. Minimiser le volume de données scanné. Ne pas impacter les requêtes existantes. Livrable attendu : Proposer une nouvelle définition de table intégrant partitionnement et clustering optimisés, avec justification des choix techniques.

Contexte : Les analystes exécutent régulièrement des requêtes exploratoires coûteuses. Le Problème : Certaines requêtes scannent des téraoctets de données inutilement. Contraintes : Utiliser EXPLAIN et estimation des bytes scannés. Mettre en place des bonnes pratiques (limite, filtres, partition pruning). Sensibiliser à l’optimisation. Livrable attendu : Proposer une requête optimisée et une stratégie de gouvernance permettant de réduire les coûts analytiques.

Contexte : Un processus ETL met à jour une table clients via MERGE. Le Problème : Des doublons apparaissent malgré la condition de jointure. Extrait actuel : MERGE clients T USING staging S ON T.id = S.id WHEN NOT MATCHED THEN INSERT ROW; La clause UPDATE est absente. Contraintes : Gérer correctement les cas MATCHED et NOT MATCHED. Garantir l’unicité des identifiants. Livrable attendu : Fournir une requête MERGE complète permettant un upsert fiable sans duplication.

Contexte : Une table reçoit des données JSON avec des champs évolutifs. Le Problème : Les nouveaux champs provoquent des erreurs ou sont ignorés lors du chargement. Contraintes : Mettre en place une gestion de schéma flexible. Utiliser des colonnes STRUCT ou JSON si nécessaire. Maintenir compatibilité descendante. Livrable attendu : Proposer une stratégie de gestion de schéma évolutif adaptée aux données semi-structurées.

Contexte : Une requête analytique joint une table transactions très volumineuse avec une petite table products. Le Problème : La requête scanne l’intégralité de la grande table avant filtrage. Extrait actuel : SELECT * FROM transactions t JOIN products p ON t.product_id = p.id WHERE p.category = 'electronics'; Contraintes : Réduire le volume scanné. Optimiser l’ordre logique de la requête. Éviter SELECT *. Livrable attendu : Proposer une requête optimisée limitant le scan inutile et améliorant les performances.

Contexte : Une requête agrégée complexe est exécutée plusieurs dizaines de fois par jour. Le Problème : Chaque exécution recalcul intégralement l’agrégation sur une grande table. Contraintes : Identifier les champs agrégés pertinents. Mettre en place une materialized view. Garantir l’actualisation automatique. Livrable attendu : Proposer la création d’une materialized view adaptée afin d’améliorer significativement le temps de réponse.

Contexte : Des données sont importées quotidiennement dans une table BigQuery via batch. Le Problème : Chaque chargement recharge l’ensemble des données au lieu d’ajouter uniquement les nouvelles lignes. Contraintes : Identifier les nouvelles lignes via un champ updated_at. Éviter les doublons. Maintenir l’intégrité des données. Livrable attendu : Proposer une stratégie de chargement incrémental utilisant MERGE ou une table intermédiaire pour intégrer uniquement les nouvelles données.

Contexte : Plusieurs équipes accèdent au même projet BigQuery avec des besoins différents. Le Problème : Les permissions actuelles sont trop larges et exposent des données sensibles. Contraintes : Définir des rôles IAM spécifiques par dataset. Restreindre les permissions à lecture seule pour certains groupes. Appliquer le principe du moindre privilège. Livrable attendu : Proposer une configuration IAM permettant une gestion fine et sécurisée des accès aux datasets.

Contexte : Une requête utilise des fonctions window pour calculer un classement par utilisateur. Le Problème : Les performances deviennent lentes sur un grand volume de données. Contraintes : Optimiser le PARTITION BY et ORDER BY. Réduire les colonnes scannées. Conserver la logique métier. Livrable attendu : Proposer une version optimisée de la requête utilisant efficacement les fonctions analytiques BigQuery.

Contexte : Une table externe BigQuery interroge directement des fichiers stockés dans Cloud Storage. Le Problème : Les performances sont instables et les coûts variables selon le volume interrogé. Contraintes : Optimiser le format des fichiers (par exemple Parquet). Limiter les colonnes scannées. Évaluer la nécessité d’une ingestion native. Livrable attendu : Proposer une stratégie d’optimisation pour améliorer performance et maîtrise des coûts des tables externes.

Contexte : Votre entreprise doit respecter des réglementations strictes sur la localisation et la protection des données. Le Problème : Certains datasets contiennent des données sensibles accessibles trop largement. Contraintes : Configurer le chiffrement géré par clé client (CMEK). Restreindre l’accès via IAM conditionnel. Mettre en place une politique de rétention et audit complet. Livrable attendu : Proposer une architecture BigQuery sécurisée et conforme intégrant contrôle d’accès avancé, chiffrement et auditabilité complète.

Contexte : Votre organisation internationale utilise plusieurs projets GCP pour séparer les environnements (dev, test, prod) et les équipes métiers. Le Problème : Les accès aux datasets sont hétérogènes et difficiles à auditer, ce qui pose un risque de non-conformité. Contraintes : Mettre en place une gouvernance centralisée basée sur IAM, groupes et politiques organisationnelles. Séparer clairement les rôles d’administration, d’ingestion et de consultation. Assurer traçabilité et audit. Livrable attendu : Proposer une architecture BigQuery multi-projets incluant stratégie IAM, séparation des environnements et mécanismes d’audit robustes.

Contexte : Une table contenant plusieurs milliards de lignes est fréquemment interrogée sans partitionnement. Le Problème : Chaque requête scanne l’intégralité de la table, entraînant des coûts très élevés. Extrait actuel : CREATE TABLE transactions ( id STRING, transaction_date TIMESTAMP, amount FLOAT64 ); Aucune partition ni clustering. Contraintes : Introduire un partitionnement adapté. Ajouter un clustering pertinent. Migrer les données sans perte. Livrable attendu : Proposer une nouvelle définition de table optimisée et une stratégie de migration réduisant drastiquement le volume scanné.

Contexte : Un pipeline ETL charge quotidiennement des données via MERGE dans une table principale. Le Problème : Certaines mises à jour sont ignorées ou créent des doublons à cause d’une condition de jointure incomplète. Extrait actuel : MERGE main T USING staging S ON T.user_id = S.user_id WHEN MATCHED THEN UPDATE SET ... La clé composite réelle inclut aussi event_date. Contraintes : Corriger la logique MERGE pour garantir l’unicité et la cohérence. Assurer idempotence du traitement. Livrable attendu : Fournir une requête MERGE corrigée garantissant un chargement incrémental fiable et cohérent.

Contexte : Une requête analytique joint plusieurs tables volumineuses pour produire un rapport consolidé. Le Problème : Le plan d’exécution montre un shuffle massif de données, ralentissant fortement la requête. Contraintes : Réduire la taille des datasets avant jointure. Utiliser des sous-requêtes filtrées ou tables intermédiaires. Maintenir exactitude des résultats. Livrable attendu : Proposer une stratégie d’optimisation permettant de limiter le shuffle et améliorer les performances globales.

Contexte : Les coûts BigQuery explosent à cause de requêtes non contrôlées exécutées par plusieurs équipes. Le Problème : Il est difficile d’identifier les requêtes les plus coûteuses et leurs auteurs. Contraintes : Exploiter les vues INFORMATION_SCHEMA.JOBS et Cloud Logging. Identifier les requêtes les plus consommatrices. Mettre en place des quotas ou alertes budgétaires. Livrable attendu : Proposer une stratégie d’audit et de gouvernance permettant de contrôler durablement les coûts BigQuery.

Contexte : L’entreprise conserve des données historiques massives dans Cloud Storage au format Parquet. Le Problème : Charger toutes les données dans BigQuery serait coûteux et inutile pour des analyses occasionnelles. Contraintes : Utiliser des external tables ou BigLake. Optimiser les performances de lecture. Sécuriser l’accès aux données. Livrable attendu : Proposer une architecture hybride combinant stockage externe et BigQuery pour un équilibre optimal entre coût et performance.

Contexte : Une équipe data science calcule des indicateurs complexes utilisant plusieurs fonctions analytiques sur des centaines de millions de lignes. Le Problème : Les requêtes prennent plusieurs minutes et consomment beaucoup de ressources. Contraintes : Optimiser le partitionnement des fonctions window. Réduire les colonnes inutiles. Introduire éventuellement des tables intermédiaires matérialisées. Livrable attendu : Proposer une refonte optimisée de la requête permettant de réduire significativement le temps d’exécution tout en conservant la logique métier.

Contexte : Une table BigQuery contient une colonne JSON stockant des événements applicatifs complexes. Le Problème : Les requêtes utilisant JSON_EXTRACT sont lentes sur un grand volume de données. Contraintes : Identifier les champs fréquemment interrogés. Normaliser partiellement le schéma si nécessaire. Minimiser les coûts de transformation. Livrable attendu : Proposer une stratégie d’optimisation pour interroger efficacement des données JSON à grande échelle.

Contexte : Une table reçoit des données en streaming continu via API. Le Problème : Les requêtes analytiques récentes sont lentes car le partitionnement actuel n’est pas adapté aux usages métiers. Contraintes : Analyser la pertinence d’un partitionnement par ingestion versus par colonne métier. Minimiser les coûts de stockage et de requêtes. Livrable attendu : Proposer une stratégie de partitionnement adaptée aux workloads de streaming et aux requêtes analytiques fréquentes.