Attribution des coûts BigQuery avec INFORMATION_SCHEMA

Les vues INFORMATION_SCHEMA de BigQuery exposent les coûts des requêtes, les dépenses par utilisateur, le statut de partitionnement des tables et les tendances de coûts dans le temps. Cette note couvre les requêtes clés pour l’attribution des coûts et la revue d’optimisation des tables.

Identifier Vos Sources de Coûts

Avant d’optimiser quoi que ce soit, identifiez où va réellement l’argent. Cette requête fait remonter vos 10 requêtes les plus coûteuses des 30 derniers jours :

SELECT
  user_email,
  query,
  total_bytes_processed,
  ROUND(total_bytes_processed / POW(1024, 4), 2) AS tib_processed,
  ROUND(total_bytes_processed / POW(1024, 4) * 6.25, 2) AS estimated_cost_usd,
  creation_time,
  total_slot_ms
FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE
  creation_time > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY)
  AND job_type = 'QUERY'
  AND state = 'DONE'
  AND total_bytes_processed > 0
ORDER BY total_bytes_processed DESC
LIMIT 10;

Dans la plupart des projets, 3 à 5 requêtes ou utilisateurs représentent plus de 70 % des dépenses de calcul. Optimisez celles-ci avant de vous attaquer à la longue traîne.

Attribution des Coûts par Utilisateur

Pour la gestion continue des coûts, agrégez par utilisateur ou compte de service :

SELECT
  user_email,
  COUNT(*) AS query_count,
  ROUND(SUM(total_bytes_processed) / POW(1024, 4), 2) AS total_tib,
  ROUND(SUM(total_bytes_processed) / POW(1024, 4) * 6.25, 2) AS total_cost_usd
FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE
  creation_time > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY)
  AND job_type = 'QUERY'
  AND state = 'DONE'
ORDER BY total_cost_usd DESC
LIMIT 20;

Cela vous indique immédiatement si les coûts sont pilotés par des comptes de service dbt (coûts de pipeline, optimisables via les matérialisations et les incrémentaux), des comptes de service d’outils BI (coûts de dashboards, optimisables via le partitionnement et les vues matérialisées), ou des utilisateurs humains (coûts ad-hoc, adressables via les contrôles de gouvernance).

Attribution des Coûts par Modèle dbt

Si votre projet dbt est configuré avec des commentaires de requête et des labels de job, vous pouvez attribuer les coûts aux modèles individuels :

SELECT
  JSON_EXTRACT_SCALAR(query, '$.dbt_model') AS dbt_model,
  COUNT(*) AS runs,
  ROUND(SUM(total_bytes_processed) / POW(1024, 4), 4) AS total_tib,
  ROUND(SUM(total_bytes_processed) / POW(1024, 4) * 6.25, 2) AS total_cost_usd
FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE
  creation_time > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY)
  AND query LIKE '%dbt_model%'
GROUP BY 1
ORDER BY total_cost_usd DESC;

Cette requête révèle quels modèles consomment le plus de ressources.

Pour un suivi des coûts dbt plus complet, le package communautaire dbt-bigquery-monitoring automatise ce pattern à travers les projets et les régions.

Tendance des Coûts par Dataset

Suivez l’évolution des coûts dans le temps pour détecter les régressions tôt :

SELECT
  DATE(creation_time) AS date,
  referenced_table.dataset_id,
  ROUND(SUM(total_bytes_processed) / POW(1024, 4), 2) AS tib_scanned,
  ROUND(SUM(total_bytes_processed) / POW(1024, 4) * 6.25, 2) AS cost_usd
FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT,
UNNEST(referenced_tables) AS referenced_table
WHERE
  creation_time > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY)
  AND job_type = 'QUERY'
GROUP BY 1, 2
ORDER BY date DESC, cost_usd DESC;

Un pic soudain dans les coûts d’un dataset signifie souvent que quelqu’un a déployé un nouveau modèle sans partitionnement, changé une matérialisation d’incrémental à rafraîchissement complet, ou ajouté une nouvelle requête coûteuse. Détecter cela dans la journée fait la différence entre une erreur à 50 € et un mois à 1 500 €.

Identifier les Tables Non Optimisées

Les grandes tables sans partitionnement ou clustering sont des candidats à l’optimisation qui se cachent en évidence :

SELECT
  table_schema,
  table_name,
  ROUND(total_logical_bytes / POW(1024, 3), 2) AS size_gb,
  CASE WHEN partition_column IS NULL THEN 'Missing' ELSE 'OK' END AS partitioning,
  CASE WHEN clustering_columns IS NULL THEN 'Missing' ELSE 'OK' END AS clustering
FROM `project.region-us`.INFORMATION_SCHEMA.TABLE_STORAGE
LEFT JOIN `project.region-us`.INFORMATION_SCHEMA.PARTITIONS USING (table_schema, table_name)
LEFT JOIN (
  SELECT table_schema, table_name, STRING_AGG(clustering_ordinal_position) AS clustering_columns
  FROM `project.region-us`.INFORMATION_SCHEMA.COLUMNS
  WHERE clustering_ordinal_position IS NOT NULL
  GROUP BY 1, 2
) USING (table_schema, table_name)
WHERE total_logical_bytes > 107374182400  -- > 100 Go
ORDER BY total_logical_bytes DESC;

Toute table de plus de 100 Go affichant « Missing » pour le partitionnement vous coûte probablement une somme significative à chaque requête. Croisez avec les requêtes les plus coûteuses — si une requête coûteuse frappe une table non partitionnée, vous avez trouvé un gain rapide.

Utiliser les Recommandations Intégrées de Google

Le recommandeur BigQuery suggère automatiquement le partitionnement et le clustering pour les tables qui en bénéficieraient. Il analyse les patterns de requêtes et propose des colonnes basées sur l’utilisation réelle des filtres, pas des suppositions. Accédez aux recommandations via :

• Cloud Console : BigQuery > Détails de la table > Onglet Recommandations
• INFORMATION_SCHEMA.TABLE_OPTIONS pour un accès programmatique
• L’API Recommender pour le traitement automatisé

Le recommandeur ne signale les tables que si elles dépassent 100 Go pour le partitionnement, ce qui s’aligne sur le propre seuil de Google pour justifier la surcharge de partitionnement.

Bâtir une Pratique de Revue Hebdomadaire

Les requêtes ci-dessus sont plus utiles quand elles sont exécutées régulièrement. Une revue hebdomadaire des coûts prend 15 minutes et détecte les problèmes avant qu’ils ne se transforment en factures surprises.

Liste de vérification hebdomadaire :

1. Top 10 des requêtes coûteuses : De nouvelles sont-elles apparues ? Y en a-t-il qui pourraient être optimisées avec de meilleurs filtres ?
2. Coûts par utilisateur/compte de service : Des pics inattendus ? De nouveaux comptes de service apparaissent ?
3. Tendance des coûts par dataset : Des datasets en progression ? Des régressions suite à des déploiements récents ?
4. Tables non optimisées : De nouvelles grandes tables créées sans partitionnement ?

Métriques clés pour un dashboard de coûts :

1. Dépenses de calcul quotidiennes : Détecter les anomalies avant qu’elles ne s’accumulent
2. Tendance des octets scannés : Indicateur avancé des évolutions de coûts
3. Utilisation des slots (si vous utilisez les réservations) : Identifier le sur/sous-provisionnement
4. Coût moyen par requête : Suivre les progrès d’optimisation dans le temps
5. Principaux contributeurs de coûts : Utilisateurs, comptes de service, datasets, modèles

Seuils d’alerte :

• 50 % du budget quotidien : notification d’avertissement
• 80 % du budget quotidien : notification critique au responsable d’équipe
• 100 % : processus d’investigation déclenché automatiquement

La revue hebdomadaire détecte les régressions de coût au plus près du déploiement, quand la cause est plus facile à identifier et à corriger.