Google PDE: Analytique BigQuery et ingénierie d'entrepôt de données — Guide d'étude

By Laurynas Brazys · 21 August 2026 · #google #PDE #certification #exam-prep #study-guide

Fait partie du Google Professional Data Engineer — Guide d’étude. Entraînez-vous avec des réponses vérifiées dans le centre d’examens Google, ou passez des tests chronométrés sur ExamRoll.io.

Optimisation des requêtes et gestion de la charge de travail

• Élagage des partitions et clustering :

  • Filtrez toujours sur la colonne de partition pour éviter d’analyser les partitions froides. Utilisez BETWEEN avec des fenêtres étroites.
  • Ordonnez les clés de clustering par sélectivité ; les premières clés doivent correspondre aux filtres et jointures fréquents. Évitez le clustering sur des colonnes à très faible cardinalité.

• Optimisation du plan de requête :

  • Utilisez EXPLAIN et les détails d’exécution pour trouver les jointures asymétriques, les shuffles importants ou les scans non élagués.
  • Réduisez le nombre de colonnes tôt avec des listes SELECT et des sous-requêtes ; BigQuery est colonnaire et supprime efficacement les colonnes inutilisées.
  • Préférez les agrégations approximatives (par exemple, APPROX_QUANTILES) pour des compromis vitesse/coût sur de grands volumes de données.
  • Appliquez le dédoublonnage avec des fonctions de fenêtrage lorsque les sources d’ingestion peuvent répéter des événements :
    • SELECT * EXCEPT(rn) FROM (SELECT t.*, ROW_NUMBER() OVER (PARTITION BY unique_id ORDER BY event_ts DESC)

Sécurité, gouvernance et contrôle des coûts

• IAM et principe du moindre privilège :

  • N’accordez les rôles au niveau de l’ensemble de données (BigQuery Data Viewer, Data Editor) qu’aux utilisateurs approuvés et de manière minimale ; limitez l’accès à l’API aux comptes de service et aux groupes sélectionnés.
  • Séparez les clients et les environnements par ensemble de données et par projet pour l’isolation. Attribuez des réservations par projet ou par dossier pour éviter les « voisins bruyants ».

• Vues autorisées et sécurité au niveau des lignes :

  • Les vues autorisées n’exposent que des colonnes/lignes sélectionnées à des projets externes, tandis que le projet de la vue conserve l’accès à la table. Conservez la vue et la source dans le même ensemble de données ou utilisez l’autorisation au niveau de l’ensemble de données pour le projet cible.
  • La sécurité au niveau des lignes avec des politiques d’accès aux lignes filtre les lignes par utilisateur ou par groupe au moment de la requête ; combinez-la avec des vues autorisées pour un contrôle à plusieurs niveaux.

• Sécurité au niveau des colonnes et tags de stratégie :

  • Utilisez les tags de stratégie Data Catalog pour protéger les colonnes sensibles et activer le masquage de données. Attribuez l’accès aux tags (et non aux tables) pour l’aligner sur la classification des données. Pour l’accès des partenaires, masquez ou refusez les colonnes PII via les tags.

• BigQuery ML et analytique intra-entrepôt :

  • Entraînez et servez des modèles directement dans BigQuery (par exemple, régression linéaire/logistique, XGBoost, K-means, séries temporelles) avec CREATE MODEL et ML.PREDICT. Stockez les caractéristiques dans des tables partitionnées et utilisez le réentraînement planifié.
  • Les modèles distants vous permettent d’invoquer Vertex AI ou des points de terminaison externes depuis SQL pour le scoring au sein d’une gouvernance fédérée. Mettez en cache les sorties dans des tables pour amortir la latence des requêtes répétées.

• Contrôle des coûts et dépannage des performances :

  • Réduire les octets analysés :
    • Partitionnez et clusterisez ; filtrez toujours sur ces clés.
    • SELECT uniquement les colonnes nécessaires ; évitez SELECT *.
    • Utilisez les vues matérialisées et la mise en cache des résultats lorsque c’est pertinent.
    • Définissez maximum_bytes_billed pour plafonner les coûts.
  • Dépanner les performances :
    • Inspectez les détails d’exécution des tâches pour détecter le déséquilibre (skew), les partitions non élaguées ou les points chauds de brassage (shuffle hotspots). Modifiez les clés de jointure ou pré-agrégez pour réduire le brassage.
    • Validez les réécritures de vues matérialisées (MV) ; assurez-vous que les prédicats et les fonctions déterministes sont compatibles.
    • Pour les tableaux de bord où il manque des données de streaming récentes, tenez compte du décalage de cohérence ou désactivez la mise en cache côté client.
  • Visibilité de la gouvernance : auditez avec Cloud Logging, INFORMATION_SCHEMA et des libellés de tâches précis pour la refacturation et l’identification des anomalies.

Scénario de problème pratique

NovaCare Health exploite une plateforme de télémédecine régionale. Une conception à table unique patient_and_visit a permis de réaliser un projet pilote, mais avec une mise à l’échelle de 100x, les rapports expirent (timeout), des doublons apparaissent suite aux upserts en streaming, et les partenaires exigent une isolation stricte des données.

Approche :

1. Reconcevoir le schéma et la disposition

   • Créez des tables de base normalisées : patients(patient_id, demographics, updated_at) et visits(visit_id, patient_id, visit_ts, metrics, updated_at).
   • Faites de visits une table partitionnée sur DATE(visit_ts), clusterisée par patient_id et visit_id. Conservez les petites dimensions de référence dénormalisées dans visits pour la rapidité des tableaux de bord.
   • Justification : La normalisation évite les auto-jointures coûteuses et les mises à jour de lignes lourdes sur une seule table très sollicitée. Le partitionnement élague les analyses historiques ; la clusterisation colocalise les jointures et les filtres sur patient_id, réduisant ainsi le brassage (shuffle).

2. Ingérer avec l’API Storage Write et garantir l’idempotence

   • Utilisez un pipeline Dataflow pour analyser les événements entrants, les valider et les écrire dans des flux nommés de l’API Storage Write avec des offsets idempotents.
   • Acheminez les événements malformés vers une table BigQuery de lettres mortes (dead-letter) pour le triage.
   • Justification : L’API Storage Write offre un débit plus élevé, une latence plus faible et de meilleures garanties de déduplication que le streaming hérité. La mise en file d’attente des lettres mortes préserve la visibilité sur les problèmes de qualité des données des partenaires.

3. Concevoir pour la fraîcheur et la déduplication dans les requêtes

   • Pour l’analytique interactive, ajoutez un filigrane (watermark) court (par exemple, 2x la disponibilité observée) avant d’interroger la partition la plus récente ; ou filtrez par _PARTITIONDATE où partition_date <= CURRENT_DATE() pour exclure les lignes en transit.
   • Utilisez ROW_NUMBER() OVER (PARTITION BY visit_id ORDER BY event_ts DESC) = 1 dans les vues qui doivent tolérer les nouvelles tentatives en amont.
   • Justification : Le streaming est « eventually consistent » pendant un bref intervalle. Le filigrane et la déduplication par fenêtrage protègent les tableaux de bord des lacunes et des doublons transitoires.

4. Accélérer les agrégats courants avec des vues matérialisées

   • Créez des vues matérialisées (MV) alignées sur les partitions sur la table visits pour les KPI quotidiens regroupés par DATE(visit_ts) et par cohortes de patients. Assurez-vous que les prédicats sont compatibles avec la réécriture.
   • Justification : Les MV réduisent la latence et les octets analysés pour les rapports récurrents ; BigQuery réécrit les requêtes pour utiliser la MV de manière transparente.

5. Appliquer l’isolation des locataires (tenants) et une sécurité fine

   • Placez chaque partenaire dans un ensemble de données dédié. Accordez des rôles au niveau de l’ensemble de données selon le principe du moindre privilège aux groupes de partenaires.
   • Publiez des vues autorisées pour des benchmarks partagés et inter-partenaires sans révéler les tables brutes.
   • Appliquez des tags de stratégie aux colonnes PII et ajoutez des politiques d’accès aux lignes à la table visits pour restreindre l’accès par partner_id pour l’analytique interne multi-locataire.
   • Justification : La segmentation par ensemble de données par locataire, ainsi que les vues autorisées et les tags de stratégie, appliquent le principe du moindre privilège tout en permettant un partage organisé.

6. Gérer les charges de travail avec les éditions, les réservations et l’autoscaling

   • Achetez de la capacité dans les éditions BigQuery et créez deux réservations : etl (pour les récepteurs Dataflow, les transformations planifiées) et bi (pour les requêtes ad hoc/reporting). Attribuez les projets en conséquence et activez l’autoscaling pour absorber les pics.
   • Planifiez les requêtes ELT en mode batch avec des SLA clairs ; définissez maximum_bytes_billed pour les projets interactifs.
   • Justification : Des réservations distinctes empêchent l’ETL de priver la BI de ressources. L’autoscaling s’adapte aux charges de travail en rafale sans surprovisionnement.

7. Gouverner les coûts et observer l’utilisation

   • Exigez des filtres sur visit_ts ; rejetez SELECT * dans les vues partagées. Utilisez INFORMATION_SCHEMA.JOBS pour détecter les analyses non élaguées et les jointures déséquilibrées.
   • Exportez les journaux d’audit de BigQuery vers Pub/Sub avec un récepteur de journaux (log sink) filtré sur les tâches d’insertion dans visits pour déclencher des alertes de surveillance en cas de pics inattendus.
   • Justification : L’élagage d’octets et la projection de colonnes contrôlent les coûts ; les journaux d’audit font remonter les modèles d’accès et les anomalies en quasi-temps réel.

8. Planifier la récupération et les remplissages (backfills)

   • Activez des politiques d’expiration de table par défaut qui s’alignent sur les besoins de conformité et de voyage dans le temps (time-travel). Pour les modifications importantes, créez un instantané (snapshot), exécutez les changements et revenez en arrière rapidement si nécessaire. Utilisez des clones de table pour les analyses de simulation (what-if) en dev/test sans dupliquer le stockage.
   • Justification : Les instantanés et les clones fournissent des filets de sécurité rapides et économes en espace ; le voyage dans le temps couvre les petites restaurations correctives.

9. Intégrer le ML intra-entrepôt

   • Stockez les caractéristiques préparées (engineered features) dans des tables partitionnées et entraînez des modèles de classification BigQuery ML pour le risque de réadmission. Pour les modèles externes hébergés sur Vertex AI, créez des modèles distants et mettez en cache les prédictions dans une table clusterisée pour des jointures à faible latence.
   • Justification : Garder le ML proche des données réduit les déplacements et la complexité de la gouvernance ; la mise en cache de l’inférence à distance amortit la latence et les coûts.

Avec cette conception, NovaCare obtient des performances prévisibles sous une charge 100x supérieure, une isolation forte des locataires et des coûts maîtrisés, tout en préservant une analytique à faible latence et une récupération reproductible.

← Stockage des données · Tous les domaines · Traitement en flux continu avec Dataflow et Apache Beam →

Entraînez-vous sur ces questions → · Tests chronométrés sur ExamRoll.io →

Parcourir Google →