Les analytics engineers travaillent souvent de manière productive dans BigQuery pendant des années sans comprendre ce qui se passe sous leurs requêtes. Vous écrivez des modèles dbt, ils se matérialisent, les dashboards se mettent à jour. Tout fonctionne jusqu’au jour où ça ne fonctionne plus. Un projet mal structuré fait exploser les coûts par 10. Une erreur de région bloque des jointures critiques. Une mauvaise configuration IAM expose des données sensibles à la mauvaise équipe.

Ces échecs partagent une cause commune : des décisions architecturales prises sans comprendre leurs conséquences. Ce guide fournit les fondations conceptuelles dont vous avez besoin (couvrant tout, de la hiérarchie des ressources BigQuery aux patterns multi-organisations) pour que vous puissiez concevoir des systèmes qui passent à l’échelle plutôt que des systèmes que vous devrez reconstruire.

Comment BigQuery fonctionne réellement sous le capot

Pour comprendre BigQuery, il faut se forger un modèle mental de son fonctionnement réel. Non pas en mémorisant les systèmes internes de Google, mais en comprenant les contraintes et compromis qui façonnent chaque décision architecturale.

Séparation du stockage et du calcul

L’innovation fondamentale de BigQuery est la séparation totale entre le stockage des données et l’exécution des requêtes. Vos tables existent dans Colossus, le système de fichiers distribué de Google, stockées dans un format colonnaire propriétaire appelé Capacitor. Vos requêtes s’exécutent séparément via Dremel, un arbre d’exécution multi-niveaux qui parallélise le travail sur des milliers de nœuds. Le réseau Jupiter de Google (avec plus de 6 pétabits par seconde de bande passante) les connecte.

Cette séparation a des implications profondes. Les coûts de stockage et les coûts de calcul sont complètement indépendants. Vous payez pour les octets stockés indépendamment de la fréquence de vos requêtes. Vous payez pour les octets scannés indépendamment de la quantité de données stockées. Une table de 10 To interrogée une seule fois coûte bien moins qu’une table de 100 Go interrogée des milliers de fois par jour.

Contrairement aux virtual warehouses de Snowflake ou aux clusters de nœuds de Redshift, il n’y a pas de ressources à provisionner, redimensionner ou gérer. BigQuery alloue automatiquement des slots (des CPU virtuels avec mémoire et capacité réseau) en fonction de la complexité des requêtes. Les clients en facturation à la demande peuvent utiliser jusqu’à 2 000 slots concurrents par projet. La tarification basée sur la capacité vous permet de réserver des slots dédiés pour des performances prévisibles. Vous ne voyez jamais ces slots et ne les gérez pas directement. Vous exécutez simplement des requêtes.

Pourquoi le stockage colonnaire change tout

Le format Capacitor stocke chaque colonne séparément sur le disque. Quand vous exécutez SELECT user_id, event_date FROM events, BigQuery ne lit que ces deux colonnes. Les 47 autres colonnes de votre table ? Jamais touchées. Jamais facturées.

Cela rend SELECT * véritablement coûteux. Une table avec 50 colonnes coûte environ 50 fois plus cher à scanner entièrement que de sélectionner une seule colonne. Cette requête “pratique” pendant le développement devient un problème budgétaire à grande échelle. Cette structure de coûts devrait influencer la façon dont vous concevez vos tables et écrivez vos modèles : les tables dénormalisées larges ne sont pas intrinsèquement mauvaises, mais les scanner sans discernement l’est.

BigQuery collecte des statistiques pendant l’encodage des données et peut opérer directement sur des données compressées sans surcoût de décompression. L’optimiseur de requêtes en sait souvent plus sur vos données que vous, et peut prendre des décisions intelligentes sur la stratégie d’exécution. Cela se révèle payant avec les vues matérialisées : BigQuery peut automatiquement router les requêtes vers elles sans aucune configuration.

La hiérarchie des ressources que tout analytics engineer devrait maîtriser

La hiérarchie des ressources de BigQuery suit la structure de Google Cloud avec des ajouts spécifiques à BigQuery :

Organization
    └── Folders
        └── Projects
            └── Datasets
                └── Tables / Views / Materialized Views / Models

Chaque niveau sert un objectif distinct, et comprendre ces frontières évite des erreurs coûteuses.

Les projets comme frontière principale

Les projets forment l’unité organisationnelle fondamentale dans Google Cloud. Chaque projet a son propre compte de facturation, ses propres quotas et l’activation de ses propres API. En ce qui concerne BigQuery, les projets déterminent où les coûts de requêtes sont facturés.

Quand vous interrogez des données, les coûts sont facturés au projet exécutant la requête, pas au projet stockant les données. Si vos données résident dans project-warehouse mais que vous exécutez des requêtes depuis project-analytics, les coûts de calcul apparaissent sur la facture de project-analytics. Les coûts de stockage restent toujours avec le projet contenant les données.

Cette séparation permet des patterns puissants. Une équipe data centrale peut gérer l’ingestion et le stockage des données brutes dans un projet. Les équipes des départements peuvent interroger ces données depuis leurs propres projets, avec les coûts allant vers leurs budgets. Les agences peuvent interroger les datasets clients tout en facturant le calcul sur leurs propres comptes.

Les projets délimitent aussi les quotas. La tarification à la demande donne à chaque projet jusqu’à 2 000 slots concurrents, avec 20 000 slots partagés au niveau de l’organisation. Si vos environnements dev et prod partagent un projet, une requête de développement qui s’emballe peut priver vos dashboards de production de ressources de calcul.

Les datasets comme frontières de contrôle d’accès

Les datasets sont les conteneurs logiques de BigQuery pour les tables et les vues. Ils n’existent pas dans la hiérarchie standard des ressources GCP (vous ne les trouverez pas dans Cloud Resource Manager) mais ils sont l’unité principale pour le contrôle d’accès et l’organisation des données.

Chaque table hérite des permissions de son dataset parent. Accordez bigquery.dataViewer sur un dataset, et ce principal peut lire toutes les tables actuelles et futures qu’il contient. Cet héritage fait des datasets la frontière naturelle pour le contrôle d’accès : groupez les tables selon qui doit y avoir accès, pas seulement selon leur contenu.

Plusieurs propriétés de dataset méritent attention :

Location est définie à la création et ne peut pas être changée ensuite. C’est la décision la plus conséquente et la moins réversible que vous prendrez. Un dataset créé en US restera en US pour toujours. Le déplacer nécessite d’exporter vers Cloud Storage, de créer un nouveau dataset dans la bonne région, de recharger toutes les données et de mettre à jour chaque référence en aval. Ou de le copier, mais c’est limité à 1 000 tables par jour.

Default table expiration supprime automatiquement les tables après une période spécifiée. Utile pour les datasets de développement où vous voulez un nettoyage automatique des expérimentations abandonnées.

Labels permettent le suivi des coûts et l’organisation des ressources. Appliquez des labels de manière cohérente sur les datasets pour permettre une analyse significative des coûts par équipe, projet ou environnement.

Storage billing model vous permet de choisir entre octets logiques (ce que vos données représentent) et octets physiques (ce qui est réellement stocké après compression). La facturation physique réduit souvent les coûts de 60-80% pour les données hautement compressibles, mais nécessite une activation par dataset.

Tables et vues

Les tables standard stockent des données. Les vues stockent des requêtes qui s’exécutent à la lecture. Les vues matérialisées stockent des résultats de requêtes pré-calculés que BigQuery maintient automatiquement.

La distinction entre vues standard et vues matérialisées compte plus qu’il n’y paraît. Une vue standard exécute sa requête de définition chaque fois que quelqu’un la lit. Si cette vue agrège un milliard de lignes, chaque rafraîchissement de dashboard paie pour cette agrégation. Une vue matérialisée pré-calcule les résultats et se rafraîchit de manière incrémentale, réduisant souvent à la fois la latence et le coût de plusieurs ordres de grandeur.

La fonctionnalité smart tuning de BigQuery route automatiquement les requêtes sur les tables de base vers les vues matérialisées quand c’est bénéfique. Si vous créez une vue matérialisée agrégeant les ventes quotidiennes, les requêtes demandant des agrégations compatibles utiliseront la vue matérialisée même si elles référencent directement la table de base. Aucune configuration requise.

Les conventions de nommage qui passent à l’échelle

Le nommage semble être un débat futile jusqu’à ce que vous gériez 400 modèles répartis sur 50 datasets et que vous ne vous souveniez plus quelle table orders fait autorité. Établissez des conventions tôt.

Utilisez snake_case universellement. BigQuery est sensible à la casse, et mélanger les conventions crée confusion et bugs. Utilisez des préfixes sémantiques qui indiquent le rôle d’une table dans le pipeline de transformation :

La convention du double underscore (base__shopify__orders) clarifie l’identification du système source et passe élégamment à l’échelle. Quand vous avez 30 systèmes sources, savoir d’un coup d’œil que base__shopify__orders et base__netsuite__orders proviennent de sources différentes fait réellement gagner du temps.

Les décisions régionales qui vous enferment

BigQuery offre des multi-régions (US, EU) et plus de 40 régions individuelles dans le monde. Votre choix de localisation est permanent, ce qui en fait l’une des décisions architecturales les plus critiques que vous prendrez.

Multi-région vs région unique

Les multi-régions stockent les données à travers plusieurs data centers au sein d’une géographie. La multi-région US peut placer vos données dans l’Iowa, l’Oregon ou l’Oklahoma. Vous ne contrôlez pas où, mais Google garantit que ça reste aux États-Unis. Les multi-régions offrent une disponibilité plus élevée et des quotas de slots plus importants.

Les régions uniques fournissent des garanties de résidence des données pour des points géographiques spécifiques. Les données dans europe-west1 restent en Belgique. Point final. C’est important pour la conformité réglementaire mais offre moins de redondance.

La tarification est généralement équivalente entre les régions pour la plupart des charges de travail. Ne choisissez pas les régions en fonction du coût ; choisissez en fonction des exigences.

La contrainte qui bloque tout

La règle qui pose le plus de problèmes : tous les datasets joints dans une même requête doivent être dans la même localisation.

Vous ne pouvez pas joindre une table en US avec une table en EU. Vous ne pouvez pas joindre us-central1 avec us-east1. Toute tentative de jointure cross-region échoue immédiatement avec une erreur d’accès refusé qui n’explique pas clairement le problème réel.

Un seul dataset mal placé peut bloquer l’ensemble de votre workflow analytique. Si vos tables principales sont en US et que quelqu’un crée une table de référence en EU, chaque requête les joignant échoue. Le correctif nécessite de recréer le dataset dans la bonne région, ce qui signifie exporter les données, créer de nouvelles ressources, recharger et mettre à jour toutes les références.

Comment choisir votre région

Prenez cette décision une fois, documentez-la et faites-la respecter :

Les exigences réglementaires imposent souvent des régions spécifiques. La conformité RGPD peut nécessiter un stockage uniquement en UE pour les données des clients européens. Les réglementations de santé peuvent exiger des frontières géographiques spécifiques. Vérifiez avec votre équipe juridique.

La proximité avec les utilisateurs affecte la latence des dashboards. Si vos analystes sont à Paris, les données en europe-west1 se chargent plus vite que les données en us-central1. Cela compte moins pour l’analytique en batch mais impacte significativement l’exploration interactive.

La co-localisation avec les sources de données améliore les performances de chargement. Les buckets Cloud Storage et les datasets BigQuery dans la même région transfèrent les données plus vite et moins cher. Si votre pipeline d’événements dépose des données dans un bucket GCS en us-central1, vos datasets raw devraient y être aussi.

Les standards organisationnels comptent le plus. Choisissez une région (ou une multi-région) et utilisez-la partout. Les avantages de la flexibilité régionale compensent rarement les risques d’erreurs inter-régions.

Les coûts de transfert de données cross-region vont de 0,02 $ à 0,14 $ par Gio selon la géographie. Mais le surcoût opérationnel de la gestion d’architectures multi-régions (le risque d’erreurs, la complexité des mouvements de données) dépasse généralement ces coûts. Pour la plupart des équipes d’analytics engineering, tout consolider dans une seule région est le bon choix.

Les architectures multi-environnements qui fonctionnent vraiment

Chaque équipe a besoin d’au moins deux environnements : un endroit pour développer et un endroit pour servir la production. La plupart en ont besoin de trois : développement, staging et production. La question est comment implémenter cette séparation dans BigQuery.

Google Cloud recommande explicitement des projets GCP séparés pour séparer les environnements. Cela fournit l’isolation la plus forte : facturation indépendante, quotas, frontières de sécurité et réservations de slots. Mais ce n’est pas la seule option, et des approches plus simples peuvent fonctionner pour les petites équipes.

Pattern A : Projets séparés par environnement

Organization
└── Data Platform Folder
    ├── analytics-dev
    ├── analytics-staging
    └── analytics-prod

Chaque environnement a son propre projet. Le développement se fait dans analytics-dev. Le staging valide les changements dans analytics-staging. La production sert les dashboards et rapports depuis analytics-prod.

Cette séparation fournit une facturation indépendante pour que vous puissiez voir exactement ce que coûte le développement par rapport à la production. Chaque projet a ses propres quotas, donc une requête dev qui s’emballe ne peut pas impacter la production. Les frontières de sécurité sont claires : les service accounts de production ne touchent jamais les données de dev, et vice versa. Si vous utilisez la tarification par capacité, vous pouvez réserver des slots dédiés pour la production tandis que le dev utilise la facturation à la demande.

L’inconvénient est la complexité. Vous gérez plus de service accounts, plus de politiques IAM, plus de cibles dans profiles.yml. Pour les équipes de plus de trois ou quatre personnes, cette complexité en vaut la peine. Pour les plus petites équipes, des options plus simples existent.

Pattern B : Datasets séparés dans un seul projet

analytics-data
├── dev_base
├── dev_marts
├── prod_base
└── prod_marts

Un seul projet contient tous les environnements, séparés par le nommage des datasets. Les modèles de développement écrivent dans les datasets dev_* ; la production écrit dans les datasets prod_*.

C’est plus simple à gérer. Un compte de facturation, un ensemble de quotas, moins de service accounts. Les permissions au niveau dataset isolent toujours l’accès : vous pouvez accorder aux développeurs l’accès en écriture aux datasets dev_* tout en restreignant les datasets de production aux service accounts automatisés.

Les inconvénients sont les quotas partagés (une utilisation dev intensive affecte la prod) et une séparation de facturation moins claire. Ce pattern fonctionne bien pour les petites équipes, les projets en phase initiale, ou les situations où l’isolation des environnements n’est pas critique.

Pattern C : Data lake central avec data marts par département

data-lake-storage (équipe centrale)
    └── datasets raw, datasets base
         ↓ (accès Data Viewer)
├── marketing-analytics (équipe Marketing)
├── finance-analytics (équipe Finance)
└── product-analytics (équipe Produit)

Une équipe centrale de data engineering gère l’ingestion, le nettoyage et la modélisation de base dans un projet de stockage dédié. Les équipes des départements exécutent leurs propres transformations et requêtes dans des projets séparés, construisant des marts spécifiques à leur domaine.

Les coûts de stockage vont au projet central. Les coûts de calcul vont aux comptes de facturation des départements. Cela sépare proprement les coûts de plateforme (“combien coûte notre infrastructure data ?”) des coûts de consommation (“combien l’équipe marketing dépense-t-elle en analytique ?”).

L’équipe centrale accorde bigquery.dataViewer sur les datasets curatés aux projets des départements. Chaque département gère ses propres projets dbt, ses propres modèles, ses propres dashboards, en sourçant depuis le data lake central.

Ce pattern nécessite plus de coordination organisationnelle mais passe à l’échelle pour les grandes entreprises avec des exigences de refacturation.

Requêtes cross-project

Interroger entre projets ne nécessite aucune configuration spéciale. Utilisez simplement des noms de tables pleinement qualifiés :

SELECT
  c.customer_id,
  c.customer_name,
  o.order_id,
  o.order_date
FROM `other-project.shared_dataset.mrt__sales__customers` c
JOIN `my-project.local_dataset.mrt__sales__orders` o
  ON c.customer_id = o.customer_id

Les requêtes cross-project dans la même région n’engendrent aucun coût de transfert réseau. Les données ne se déplacent pas entre les projets ; BigQuery route la requête vers l’endroit où les données résident.

Permissions requises : bigquery.dataViewer sur le dataset source (pour lire les données) et bigquery.jobUser sur le projet exécutant la requête (pour exécuter des requêtes). Le projet interrogeant paie pour le calcul.

Les patterns IAM qui respectent le moindre privilège

Le modèle de permissions de BigQuery est plus nuancé qu’il n’y paraît au premier abord. Bien le configurer signifie comprendre la différence entre l’accès aux données et l’accès au calcul, et accorder chacun au niveau approprié.

Les rôles prédéfinis à utiliser

Google fournit des rôles prédéfinis spécifiques à BigQuery. Utilisez-les au lieu des rôles basiques (Owner, Editor, Viewer), qui précèdent IAM et accordent des accès excessifs et mal délimités.

Le pattern essentiel sépare l’accès aux données de l’accès au calcul :

Au niveau projet : Accordez bigquery.jobUser pour donner aux utilisateurs la capacité d’exécuter des requêtes. C’est la permission “calcul”. Elle leur permet d’exécuter des jobs mais ne détermine pas à quelles données ils peuvent accéder.

Au niveau dataset : Accordez bigquery.dataViewer ou bigquery.dataEditor pour contrôler quelles données les utilisateurs peuvent voir et modifier. C’est la permission “données”. Elle détermine quelles tables sont accessibles.

Un utilisateur avec jobUser sur project-analytics et dataViewer sur project-warehouse.sales_data peut exécuter des requêtes dans project-analytics qui lisent depuis project-warehouse.sales_data. Il ne peut pas lire depuis des datasets où il n’a pas dataViewer. Il ne peut pas exécuter de requêtes dans des projets où il n’a pas jobUser.

Stratégie de service accounts

Créez des service accounts dédiés pour chaque fonction dans votre stack data :

sa-dbt-dev@analytics-dev.iam.gserviceaccount.com
sa-dbt-prod@analytics-prod.iam.gserviceaccount.com
sa-fivetran@analytics-prod.iam.gserviceaccount.com
sa-looker@analytics-prod.iam.gserviceaccount.com
sa-airflow@analytics-prod.iam.gserviceaccount.com

Chaque service account n’obtient que les permissions dont il a besoin. Le service account dbt de production a besoin de bigquery.dataEditor (pour créer des tables) et bigquery.jobUser (pour exécuter des requêtes). Le service account Looker n’a besoin que de bigquery.dataViewer. Il ne devrait jamais créer ou modifier de tables.

Effectuez une rotation des clés de service account tous les 90 jours si vous utilisez des fichiers de clés. Mieux encore, utilisez Workload Identity Federation quand vous exécutez dans des environnements GCP. Vos workloads Cloud Run ou GKE peuvent s’authentifier sans gérer de clés du tout.

Ce qu’il faut éviter

Les rôles basiques (Owner, Editor, Viewer) accordent des permissions larges sur tous les services GCP, pas seulement BigQuery. Un Editor peut modifier les buckets Cloud Storage, les instances Compute Engine et tout le reste. N’utilisez jamais ces rôles pour le contrôle d’accès BigQuery.

bigquery.admin au niveau projet donne un contrôle total sur toutes les ressources BigQuery d’un projet. La plupart des utilisateurs n’en ont pas besoin. La plupart des service accounts n’en ont certainement pas besoin. N’accordez l’accès admin qu’au petit nombre de personnes qui administrent réellement les paramètres BigQuery.

L’accès trop large aux datasets se produit quand les équipes accordent dataViewer sur tous les datasets pour éviter les tickets de demande de permissions. Cela annule tout l’intérêt de l’isolation au niveau dataset. Si quelqu’un a besoin d’accéder à des données spécifiques, accordez l’accès à ce dataset spécifique.

Les patterns cross-organisation pour agences et cabinets de conseil

Les agences gérant plusieurs environnements BigQuery clients font face à des défis uniques. Chaque client devrait avoir sa propre organisation GCP avec ses propres projets, facturation et exigences de sécurité. L’agence fournit des services analytiques en travaillant au sein des organisations clientes, pas en centralisant les données clients dans l’infrastructure de l’agence.

Le pattern recommandé : Travailler dans les organisations clientes

Chaque client possède son organisation GCP. L’agence obtient l’accès selon les besoins :

Organisation Client Alpha (propriété du client)
├── client-alpha-analytics
│   └── Toutes les ressources BigQuery, modèles dbt, données
└── IAM : Équipe de l'agence avec accès accordé

Organisation Client Beta (propriété du client)
├── client-beta-analytics
│   └── Toutes les ressources BigQuery, modèles dbt, données
└── IAM : Équipe de l'agence avec accès accordé

Organisation Agence (propriété de l'agence)
└── agency-internal
    └── Outillage interne, templates, documentation uniquement

Cette séparation fournit une propriété claire des données. Les données client restent dans l’infrastructure client. Quand les engagements se terminent, les clients conservent le contrôle total sans migration de données. La facturation va naturellement aux comptes clients. Les exigences de conformité (résidence des données, contrôles d’accès) sont la responsabilité du client à définir et la responsabilité de l’agence à respecter.

L’équipe de l’agence s’authentifie à chaque organisation cliente séparément. Maintenez des service accounts séparés par client, des credentials séparés, des profils dbt séparés. Cela représente plus de travail, mais évite les fuites de données entre clients et garde les pistes d’audit propres.

Solution de repli : Projet par client dans l’organisation de l’agence

Certains clients ne peuvent pas ou ne veulent pas maintenir leur propre organisation GCP. Petites entreprises sans infrastructure IT, engagements à court terme, ou clients qui veulent explicitement que l’agence gère tout. Dans ces cas, créez des projets clients au sein de l’organisation de l’agence :

Organisation Agence
├── agency-admin
│   └── Logs d'audit, exports de facturation, outillage partagé
├── client-alpha-analytics
│   └── Toutes les ressources BigQuery pour Client Alpha
└── client-beta-analytics
    └── Toutes les ressources BigQuery pour Client Beta

Ce pattern nécessite une attention supplémentaire. Documentez clairement que l’agence possède l’infrastructure. Définissez la propriété des données et les droits d’export dans les contrats. Anticipez le désengagement : si un client veut partir, vous devrez exporter ses données et potentiellement migrer son projet dbt vers sa propre organisation.

La facturation peut aller au compte de facturation du client (lié au projet de l’agence) ou l’agence peut facturer en fonction des coûts. Dans tous les cas, utilisez des labels et des projets séparés pour suivre les coûts précisément par client.

Analytics Hub pour le partage de données formel

Analytics Hub permet le partage de données sans copie à travers les frontières organisationnelles. C’est particulièrement utile quand les clients veulent partager des données avec l’agence (ou vice versa) sans copier les données ou accorder un accès direct aux datasets.

Un propriétaire de données publie un dataset vers un listing Analytics Hub. Les abonnés dans d’autres organisations demandent l’accès au listing. Une fois approuvés, ils obtiennent un linked dataset, une référence en lecture seule qui pointe vers les données source sans les copier.

Les coûts de stockage sont à la charge de l’éditeur. Les coûts de requête sont à la charge de l’abonné. L’abonné ne peut pas modifier les données, ne peut pas définir d’IAM sur les tables individuelles, ne peut rien faire d’autre que lire. Les éditeurs peuvent restreindre complètement l’exfiltration de données, bloquant les opérations EXPORT, CREATE TABLE AS SELECT et de copie.

Pour le travail d’agence, créez un exchange privé au sein d’Analytics Hub. Ajoutez des principals spécifiques (utilisateurs, groupes ou service accounts) comme abonnés. Les clients peuvent ensuite s’abonner aux listings et interroger via leurs linked datasets. Tous les accès sont journalisés pour l’audit.

Les vues autorisées pour un accès contrôlé

Les vues autorisées résolvent un problème courant : exposer des données filtrées ou agrégées sans accorder l’accès aux tables sous-jacentes. Elles fonctionnent à travers les frontières organisationnelles.

Le pattern :

1. Créez une vue dans un dataset dédié qui interroge les tables sources sensibles
2. Autorisez le dataset de la vue à accéder au dataset source
3. Accordez aux utilisateurs l’accès uniquement au dataset de la vue

-- Dans le dataset : shared_views
CREATE VIEW shared_views.analyst_orders AS
SELECT
  order_id,
  order_date,
  total_amount
  -- customer_ssn exclu
  -- internal_notes exclu
FROM sensitive_data.mrt__sales__orders
WHERE region = 'EMEA';  -- Filtrage de lignes

La vue fait référence à sensitive_data.mrt__sales__orders, mais les utilisateurs n’ont besoin que de l’accès à shared_views. Dans la console BigQuery, vous autorisez shared_views à accéder à sensitive_data. Quiconque peut interroger shared_views.analyst_orders obtient la vue filtrée, avec colonnes restreintes, sans aucune permission sur sensitive_data.

Les datasets autorisés étendent ce concept. Au lieu d’autoriser des vues individuelles, vous autorisez un dataset entier. Toutes les vues actuelles et futures dans ce dataset peuvent accéder aux données sources. Cela réduit la charge de gestion quand vous créez de nombreuses vues pour différents patterns d’accès.

Sécurité au niveau ligne et colonne

Pour des environnements vraiment multi-tenant où différents utilisateurs voient différentes lignes de la même table, BigQuery offre la sécurité au niveau ligne :

CREATE ROW ACCESS POLICY client_a_only
ON multi_tenant.mrt__shared__orders
GRANT TO ('group:client-a-analysts@clienta.com')
FILTER USING (client_id = 'CLIENT_A');

CREATE ROW ACCESS POLICY client_b_only
ON multi_tenant.mrt__shared__orders
GRANT TO ('group:client-b-analysts@clientb.com')
FILTER USING (client_id = 'CLIENT_B');

Les analystes de Client A interrogeant multi_tenant.mrt__shared__orders ne voient que leurs lignes. Client B ne voit que les siennes. La table est la même ; l’accès diffère.

La sécurité au niveau colonne utilise les policy tags de Data Catalog. Vous créez un tag (disons PII), l’appliquez aux colonnes sensibles, puis accordez à des utilisateurs spécifiques le rôle Fine-Grained Reader pour ce tag. Les utilisateurs sans le rôle voient ces colonnes comme null ou reçoivent des erreurs d’accès.

Le masquage dynamique des données va plus loin. Les utilisateurs avec le rôle Masked Reader voient des valeurs hashées ou partiellement masquées au lieu de nulls. Utile pour les scénarios où les analystes ont besoin de voir qu’une valeur existe et de l’utiliser dans des jointures, sans pour autant voir le contenu réel.

Les configurations dbt qui fonctionnent avec BigQuery

L’adaptateur dbt-bigquery mappe les concepts dbt aux ressources BigQuery. Comprendre ce mapping vous aide à configurer dbt correctement pour votre architecture.

Le project de dbt (parfois appelé database dans d’autres adaptateurs) correspond au projet BigQuery. Le schema de dbt correspond au dataset BigQuery. Quand vous définissez schema: base dans dbt, vous ciblez un dataset BigQuery appelé base.

profiles.yml pour plusieurs environnements

Une configuration BigQuery typique a des cibles séparées pour le développement et la production :

my_analytics:
  target: dev
  outputs:
    dev:
      type: bigquery
      method: oauth  # Utilise vos credentials gcloud
      project: analytics-dev
      dataset: "dbt_{{ env_var('USER', 'developer') }}"
      location: US
      threads: 4

    prod:
      type: bigquery
      method: service-account
      project: analytics-prod
      dataset: analytics
      location: US
      threads: 8
      keyfile: /secrets/sa-dbt-prod.json
      job_execution_timeout_seconds: 600
      maximum_bytes_billed: 10000000000  # Limite de 10 Go

Certains paramètres méritent une attention particulière :

location doit correspondre à vos datasets existants. BigQuery n’autorise pas les opérations cross-region, donc cela doit s’aligner avec vos décisions architecturales. Si vos datasets sont en EU, définissez location: EU.

execution_project (non montré ci-dessus) facture les coûts de requêtes à un projet différent de celui où les modèles se matérialisent. Utile pour le pattern “data lake central + marts par département” où vous voulez que les coûts de calcul aillent aux budgets des départements.

job_execution_timeout_seconds empêche les requêtes qui s’emballent. BigQuery n’a pas de timeout par défaut et les requêtes peuvent s’exécuter indéfiniment. Définissez une limite explicite basée sur votre requête légitime la plus longue attendue.

maximum_bytes_billed fixe un plafond dur sur les octets scannés par requête. Si une requête dépasse cette limite, elle échoue avant de scanner. Cela permet d’intercepter les requêtes mal formées qui scanneraient des tables entières de manière inattendue.

Patterns de service accounts par méthode de déploiement

Différents contextes de déploiement nécessitent différentes approches d’authentification :

Développement local : Utilisez OAuth via gcloud auth application-default login. Les développeurs s’authentifient en leur propre nom, et leur identité personnelle apparaît dans les logs d’audit.

dbt Cloud : Importez un fichier de clé de service account dans les paramètres de connexion. dbt Cloud gère les credentials de manière sécurisée.

Cloud Run ou GKE : Utilisez Workload Identity. Le service obtient une identité GCP automatiquement sans fichiers de clés à gérer. Dans profiles.yml, définissez method: oauth et l’environnement fournit les credentials.

GitHub Actions : Stockez le JSON du service account comme secret de repository. Injectez-le comme variable d’environnement pendant l’exécution du workflow.

Impersonation de service account : Le pattern recommandé pour le développement local sophistiqué. Les développeurs s’authentifient via OAuth mais impersonnent un service account avec les permissions appropriées :

dev:
  type: bigquery
  method: oauth
  project: analytics-dev
  dataset: dbt_dev
  impersonate_service_account: sa-dbt-dev@analytics-dev.iam.gserviceaccount.com

Cela évite de distribuer des clés de service account tout en maintenant des frontières de permissions claires. L’identité du développeur apparaît dans les logs d’audit comme l’acteur, et le service account impersonné y est également mentionné.

La macro generate_schema_name

Par défaut, dbt crée des noms de datasets en combinant votre schema cible avec tout schema personnalisé : <target_schema>_<custom_schema>. Si votre schema cible est dbt_alice et qu’un modèle spécifie schema: base, le modèle se retrouve dans dbt_alice_base.

Cela fonctionne pour le développement mais crée des noms de datasets de production maladroits. Vous voulez probablement que les modèles de production soient dans base, pas analytics_base.

Surchargez le comportement par défaut avec l’alternative intégrée de dbt :

-- macros/generate_schema_name.sql
{% macro generate_schema_name(custom_schema_name, node) -%}
    {{ generate_schema_name_for_env(custom_schema_name, node) }}
{%- endmacro %}

Cela produit dbt_alice_base en dev (schema cible comme préfixe) mais juste base en production (schema personnalisé uniquement, sans préfixe). La macro fait cette distinction en vérifiant si target.name == 'prod' ou via une logique similaire.

Les labels de jobs pour l’attribution des coûts

Les jobs BigQuery peuvent porter des labels qui apparaissent dans les exports de facturation et INFORMATION_SCHEMA. dbt peut automatiquement appliquer des labels identifiant quel modèle a généré chaque requête :

query-comment:
  comment: "{{ query_comment(node) }}"
  job-label: true

Avec cette option activée, chaque requête exécutée par dbt porte des labels comme dbt_model: base__shopify__orders. Interrogez INFORMATION_SCHEMA.JOBS_BY_PROJECT pour analyser les coûts par modèle :

SELECT
  (SELECT value FROM UNNEST(labels) WHERE key = 'dbt_invocation_id') AS run_id,
  (SELECT value FROM UNNEST(labels) WHERE key = 'dbt_model') AS model,
  COUNT(*) AS query_count,
  SUM(total_bytes_billed) / POW(10, 9) AS total_gb_billed,
  SUM(total_slot_ms) / 1000 AS total_slot_seconds
FROM `region-US`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE creation_time > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY)
  AND job_type = 'QUERY'
GROUP BY 1, 2
ORDER BY total_gb_billed DESC
LIMIT 50;

Cette requête montre quels modèles consomment le plus de ressources, permettant une optimisation ciblée. Ce modèle mrt__analytics__events qui consomme 80% de votre budget de calcul ? Maintenant vous savez où concentrer vos efforts.

Les anti-patterns qui détruisent les budgets et bloquent les déploiements

Les erreurs architecturales se cumulent. Une mauvaise décision au mois un devient une crise au mois six quand vous avez besoin de capacités que votre structure ne supporte pas. Voici les patterns qui causent le plus de douleur.

Le piège du SELECT *

Ça semble pratique. C’est catastrophiquement coûteux.

SELECT * scanne chaque colonne d’une table. Dans le stockage colonnaire de BigQuery, le nombre de colonnes est directement proportionnel au coût. Une table avec 100 colonnes coûte environ 100 fois plus cher à scanner par ligne que de sélectionner une seule colonne.

Cela compte le plus dans les modèles base. Un pattern courant :

-- Mauvais : scanne les 87 colonnes
SELECT * FROM {{ source('shopify', 'orders') }}

Mieux :

-- Bon : scanne uniquement les colonnes nécessaires
SELECT
  order_id,
  customer_id,
  created_at,
  total_price,
  financial_status
FROM {{ source('shopify', 'orders') }}

La version explicite scanne 5 colonnes au lieu de 87, soit environ 94% de réduction de coût pour le même nombre de lignes. Multipliez par chaque modèle base, chaque exécution incrémentale, chaque full refresh, et les économies sont substantielles.

Prolifération des datasets et chaos de nommage

Cet anti-pattern émerge lentement. Quelqu’un crée test_data pour une expérience. Quelqu’un d’autre crée backup_orders_v2. Six mois plus tard, vous avez 40 datasets et personne ne sait quelle table customers fait autorité.

Les symptômes incluent :

• Des datasets nommés test, temp, backup, old, new, v2, final, final_v2
• Le même nom de table apparaissant dans plusieurs datasets
• Aucune documentation sur quels datasets sont actifs
• Des grants IAM éparpillés sur des dizaines de datasets sans pattern clair

Le correctif commence par un audit. Interrogez INFORMATION_SCHEMA.SCHEMATA pour lister tous les datasets. Vérifiez les logs d’accès pour voir lesquels sont réellement interrogés. Archivez les datasets inutilisés vers un projet séparé (ne supprimez pas, vous pourriez avoir besoin de ces données un jour). Établissez des conventions de nommage et faites-les respecter via la revue de code.

La catastrophe du mélange de régions

Vous avez choisi US pour vos datasets principaux. Un prestataire a créé une table de référence en EU. Maintenant chaque requête les joignant échoue.

Le message d’erreur n’est pas utile : “Access Denied: BigQuery BigQuery: Not found: Dataset.” Rien n’indique qu’il s’agit d’un problème de région. Vous débuguez pendant des heures avant de comprendre.

Le correctif est douloureux :

1. Exportez les données mal placées vers Cloud Storage
2. Créez un nouveau dataset dans la bonne région
3. Chargez les données depuis Cloud Storage dans le nouveau dataset
4. Mettez à jour toutes les références à la table
5. Supprimez l’ancien dataset

La prévention est plus facile : établissez une région unique comme standard, documentez-la de manière visible, et validez en CI que tous les nouveaux datasets utilisent la bonne localisation.

La sur-permission IAM

“Mettons tout le monde admin pour qu’on ne soit pas bloqués par des problèmes de permissions.”

Cela crée des risques de sécurité, du bruit dans les audits et de la confusion. Quand tout le monde a le contrôle total, personne ne sait qui a changé quoi ou pourquoi. Si des credentials fuient, c’est l’ensemble du système qui est compromis.

Le pattern émerge généralement de la friction : quelqu’un avait besoin d’accès, la solution la plus rapide était des permissions larges, et personne n’est repassé derrière. La solution est de traiter l’IAM comme du code : définir les permissions dans Terraform ou similaire, exiger une revue pour les changements, et auditer régulièrement.

Commencez par identifier les principals sur-permissionnés :

• Tout utilisateur avec bigquery.admin au niveau projet (la plupart n’en ont pas besoin)
• Les service accounts avec dataEditor sur des datasets qu’ils ne font que lire
• Les rôles basiques (Owner/Editor/Viewer) accordés n’importe où

Rétrogradez aux permissions minimum requises. Oui, cela peut causer des problèmes d’accès temporaires. Mieux vaut les corriger intentionnellement que de découvrir que votre modèle de permissions n’existe que sur le papier.

L’absence de contrôles de coûts

Par défaut, BigQuery est très permissif. Sans limites explicites, une seule requête mal formée peut scanner des pétaoctets. Sans timeouts, une requête peut s’exécuter indéfiniment, consommant des slots et bloquant potentiellement des ressources.

maximum_bytes_billed et job_execution_timeout_seconds sont par défaut illimités. Définissez des limites explicites sur chaque cible dans profiles.yml :

prod:
  type: bigquery
  # ... autres paramètres ...
  maximum_bytes_billed: 50000000000  # 50 Go
  job_execution_timeout_seconds: 1800  # 30 minutes

Considérez aussi les quotas au niveau projet dans la console GCP. Vous pouvez définir des limites quotidiennes sur les octets scannés, fournissant un filet de sécurité même si les limites de requêtes individuelles échouent.

Ne pas utiliser les vues matérialisées pour les requêtes répétées

Un dashboard BI exécute la même requête d’agrégation chaque fois que quelqu’un le charge. Dix analystes qui le chargent dix fois par jour, c’est 100 exécutions d’un calcul identique.

Les vues matérialisées résolvent cela. Créez une vue qui pré-calcule l’agrégation :

CREATE MATERIALIZED VIEW dashboard_data.daily_sales_summary AS
SELECT
  DATE(order_date) AS order_day,
  region,
  COUNT(*) AS order_count,
  SUM(total_amount) AS total_revenue
FROM transactions.mrt__sales__orders
GROUP BY 1, 2;

BigQuery maintient cela automatiquement. Quand les données sous-jacentes changent, BigQuery met à jour la vue matérialisée de manière incrémentale. Les requêtes de dashboard accèdent aux résultats pré-calculés au lieu de ré-agréger les données brutes.

Le smart tuning de BigQuery peut aussi router les requêtes sur la table de base vers la vue matérialisée automatiquement. Une requête demandant SUM(total_amount) groupé par DATE(order_date) pourrait utiliser la vue matérialisée même si elle référence directement transactions.mrt__sales__orders.

Synthèse des bonnes pratiques

Il y a beaucoup à assimiler. Une checklist condensée pour référence :

Décisions architecturales à prendre tôt

Ces choix ont des conséquences à long terme et sont difficiles à changer plus tard :

• Choisissez une région et utilisez-la partout. Documentez votre choix. Validez en CI.
• Séparez les projets pour les environnements quand les équipes dépassent 3-4 personnes ou quand l’isolation de facturation compte.
• Utilisez une hiérarchie de dossiers pour grouper les projets liés et permettre l’héritage IAM.
• Établissez des conventions de nommage pour les datasets et tables. Faites-les respecter via la revue de code.

Contrôles de coûts à implémenter immédiatement

Ceux-ci évitent les surprises coûteuses :

• Définissez maximum_bytes_billed sur toutes les cibles dbt. 10-50 Go est raisonnable pour la plupart des workflows ; ajustez selon vos requêtes légitimes les plus volumineuses.
• Configurez les timeouts de jobs via job_execution_timeout_seconds. 30-60 minutes permet d’intercepter les requêtes qui s’emballent sans bloquer les jobs légitimes de longue durée.
• Activez les labels de jobs pour suivre les coûts par modèle. Sans attribution, on optimise à l’aveugle.
• Surveillez via INFORMATION_SCHEMA.JOBS. Construisez un dashboard ou une requête planifiée alertant sur les anomalies.

Patterns de sécurité à suivre

Ceux-ci protègent les données sans créer de friction :

• Rôles prédéfinis uniquement. N’utilisez jamais les rôles basiques (Owner/Editor/Viewer) pour le contrôle d’accès BigQuery.
• Niveau dataset pour les permissions de données. Accordez dataViewer ou dataEditor sur des datasets spécifiques, pas au niveau projet.
• Niveau projet pour les permissions de calcul. Accordez jobUser sur les projets où les utilisateurs devraient exécuter des requêtes.
• Service accounts séparés par fonction. dbt, Fivetran, Looker et Airflow ont chacun leur propre identité.
• Vues autorisées pour le partage contrôlé. Exposez des sous-ensembles de données sans accorder l’accès aux tables sous-jacentes.

Pratiques spécifiques à dbt

Ces pratiques permettent à dbt de fonctionner harmonieusement avec votre architecture :

• Surchargez generate_schema_name pour des noms de datasets de production propres sans préfixes.
• Utilisez l’impersonation de service account pour le développement local. Évite la distribution de clés tout en maintenant les pistes d’audit.
• Implémentez le slim CI avec state:modified+ --defer pour tester uniquement les modèles modifiés contre l’état de production.
• Partitionnez les grandes tables par date. Clusterisez sur les colonnes fréquemment filtrées. (Plus d’informations dans l’article sur le partitionnement.)

Où ces fondations mènent

Cette compréhension architecturale prépare le terrain pour l’optimisation. Sans savoir comment BigQuery organise les ressources, les discussions sur le partitionnement et le clustering sont abstraites. Sans comprendre les projets et la facturation, les réservations de slots n’ont pas de sens. Sans saisir les frontières IAM, les recommandations de sécurité manquent de contexte.

Faites bien l’architecture, et tout le reste devient de l’optimisation. Faites-la mal, et vous reconstruirez au lieu d’affiner.