Configuration dbt BigQuery

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

La traduction clé : le project dbt (parfois appelé database dans d’autres adaptateurs) correspond au project BigQuery. Le schema dbt correspond au dataset BigQuery. Quand schema: base est défini dans dbt, on cible un dataset BigQuery appelé base.

profiles.yml pour plusieurs environnements

Une configuration BigQuery typique dispose de targets séparées pour le développement et la production :

my_analytics:
  target: dev
  outputs:
    dev:
      type: bigquery
      method: oauth  # Utilise les 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

Plusieurs configurations importent significativement :

location doit correspondre aux datasets existants. BigQuery n’autorise pas les opérations cross-région, donc cela doit s’aligner avec les décisions d’architecture régionale. Si les datasets sont en EU, définir location: EU. Une erreur ici produit des messages d’erreur cryptiques.

execution_project (non montré ci-dessus) facture les coûts de requêtes à un project différent de celui où les modèles se matérialisent. Utile pour le pattern lac de données central + marts départementaux où les coûts de compute doivent alimenter les budgets départementaux.

job_execution_timeout_seconds empêche les requêtes incontrôlées. BigQuery n’a pas de timeout par défaut — les requêtes peuvent s’exécuter indéfiniment, consommant des slots et pouvant maintenir des verrous. Définir une limite explicite basée sur la durée la plus longue attendue pour une requête légitime. 600 secondes (10 minutes) est raisonnable pour la plupart des modèles dbt ; augmenter à 1800 (30 minutes) pour les transformations particulièrement lourdes.

maximum_bytes_billed définit un plafond dur sur les octets scannés par requête. Si une requête dépasserait cette limite, elle échoue avant de scanner. Cela détecte les requêtes mal formées qui scanneraient inopinément des tables entières — particulièrement précieux pour les modèles incrémentaux qui pourraient déclencher accidentellement un full refresh. 10 à 50 Go est une plage raisonnable pour la plupart des workflows.

Méthodes d’authentification par contexte de déploiement

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

Développement local : Utiliser OAuth via gcloud auth application-default login. Les développeurs s’authentifient en tant qu’eux-mêmes, et leur identité personnelle apparaît dans les logs d’audit.

dbt Cloud : Télécharger un fichier de clé de compte de service dans les paramètres de connexion. dbt Cloud gère le credential de manière sécurisée.

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

GitHub Actions : Stocker le JSON du compte de service comme secret du dépôt. L’injecter comme variable d’environnement pendant l’exécution du workflow.

Impersonation de compte de service : Le pattern recommandé pour le développement local sophistiqué. Les développeurs s’authentifient via OAuth mais usurpent l’identité d’un compte de service 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 la distribution de clés de compte de service tout en maintenant des limites de permissions claires. L’identité du développeur apparaît dans les logs d’audit comme acteur, avec le compte de service usurpé noté. On obtient l’auditabilité de l’authentification personnelle avec les contraintes de permission d’un compte de service.

La macro generate_schema_name

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

Cela fonctionne pour le développement mais crée des noms de datasets de production maladroits. Les modèles de production sont probablement souhaités dans base, pas dans analytics_base.

Remplacer 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 (schéma cible comme préfixe) mais juste base en production (schéma personnalisé uniquement, sans préfixe). La distinction est contrôlée en vérifiant si target.name == 'prod' ou une logique similaire à l’intérieur de la macro.

C’est l’une de ces macros qu’on configure une fois et qu’on n’a plus jamais à penser — mais oublier de la configurer signifie que les datasets de production obtiennent des noms préfixés laids, ou que les datasets dev entrent en collision avec la production. La note Structure et nommage des projets dbt couvre les conventions de nommage plus larges que cette macro permet.

Labels de job pour l’attribution des coûts

Les jobs BigQuery peuvent porter des labels qui apparaissent dans les exports de facturation et dans 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 cela activé, chaque requête que dbt exécute porte des labels comme dbt_model: base__shopify__orders. C’est ce qui rend l’analyse des coûts par modèle possible.

Interroger 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. Sans labels de job, l’optimisation des coûts nécessite d’analyser les dépenses totales sans attribution par modèle. Avec les labels, la requête identifie quels modèles consomment le plus de ressources.

Liste de contrôle des contrôles de coûts

maximum_bytes_billed et job_execution_timeout_seconds sont illimités par défaut. Définir des limites explicites sur chaque target :

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

Envisager également des quotas au niveau du project dans la console GCP. On peut 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.

La hiérarchie des contrôles de coûts, du plus au moins granulaire :

maximum_bytes_billed par target — détecte les requêtes individuelles incontrôlées
job_execution_timeout_seconds par target — détecte les boucles infinies et les requêtes bloquées
Quotas journaliers au niveau du project — détecte les dépassements continus de nombreuses requêtes
Alertes budgétaires — détecte tout le reste, mais seulement après que l’argent est dépensé

Combiner les quatre. Chacun détecte des échecs que les autres ratent.