Comment j'audite un environnement BigQuery en 2 heures

Quand je fais un audit FinOps BigQuery, j'ai 2 heures pour comprendre où part l'argent et identifier les quick wins. Voici ma méthode, étape par étape, avec toutes les requêtes SQL.

Mon opinion tranchée : ne commence jamais un audit par le storage. Je vois trop de gens passer des heures à analyser les coûts de stockage alors que 90% du temps, c'est le compute (les requêtes) qui coûte cher. Le storage, c'est $0.02/GB/mois. Une seule mauvaise requête peut coûter plus que 10 TB de stockage.

Prérequis

Accès nécessaires :

• Lecture sur INFORMATION_SCHEMA (niveau projet ou région)
• Lecture sur les billing exports (si disponibles)
• Idéalement : accès BigQuery Admin pour voir tous les jobs

Outils :

• Console BigQuery ou client SQL
• Spreadsheet pour noter les findings
• Calculatrice (ou tête)

Phase 1 : Vue d'ensemble (15 min)

1.1 Coût total des 30 derniers jours

-- Coût total on-demand (30 jours)
SELECT
  ROUND(SUM(total_bytes_billed) / POW(1024, 4) * 5, 2) AS cost_usd,
  ROUND(SUM(total_bytes_billed) / POW(1024, 4) * 5 * 0.92, 2) AS cost_eur,
  COUNT(*) AS total_queries,
  COUNT(DISTINCT user_email) AS unique_users
FROM `region-eu`.INFORMATION_SCHEMA.JOBS
WHERE creation_time > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY)
  AND job_type = 'QUERY'
  AND state = 'DONE';

Ce que je note :

• Coût mensuel estimé
• Nombre de requêtes (volume)
• Nombre d'utilisateurs actifs
• Ratio coût/utilisateur

1.2 Évolution jour par jour

SELECT
  DATE(creation_time) AS day,
  COUNT(*) AS queries,
  ROUND(SUM(total_bytes_billed) / POW(1024, 4) * 5, 2) AS cost_usd
FROM `region-eu`.INFORMATION_SCHEMA.JOBS
WHERE creation_time > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY)
  AND job_type = 'QUERY'
GROUP BY day
ORDER BY day;

Ce que je cherche :

• Tendance (croissante, stable, pics)
• Anomalies (jours à 10x le coût normal)
• Pattern semaine vs week-end

J'ai vu chez un client une facture qui triplait chaque lundi. Après investigation : un job Airflow qui recalculait toutes les métriques de la semaine précédente en full scan au lieu d'utiliser le partitionnement. 3 lignes de SQL à changer, -65% sur la facture mensuelle.

1.3 Répartition par type de coût

SELECT
  job_type,
  COUNT(*) AS count,
  ROUND(SUM(total_bytes_billed) / POW(1024, 4) * 5, 2) AS cost_usd,
  ROUND(SUM(total_bytes_billed) / POW(1024, 4) * 5 /
    SUM(SUM(total_bytes_billed)) OVER() * 100, 1) AS pct
FROM `region-eu`.INFORMATION_SCHEMA.JOBS
WHERE creation_time > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY)
GROUP BY job_type
ORDER BY cost_usd DESC;

Répartition typique saine :

• QUERY : 70-85%
• LOAD : 5-15%
• COPY : 0-5%
• EXTRACT : 0-5%

Phase 2 : Identifier les gros consommateurs (20 min)

2.1 Top utilisateurs

SELECT
  user_email,
  COUNT(*) AS query_count,
  ROUND(SUM(total_bytes_billed) / POW(1024, 4), 2) AS tb_scanned,
  ROUND(SUM(total_bytes_billed) / POW(1024, 4) * 5, 2) AS cost_usd,
  ROUND(AVG(total_bytes_billed) / POW(1024, 3), 2) AS avg_gb_per_query
FROM `region-eu`.INFORMATION_SCHEMA.JOBS
WHERE creation_time > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY)
  AND job_type = 'QUERY'
GROUP BY user_email
ORDER BY cost_usd DESC
LIMIT 15;

Ce que je cherche :

• Service accounts (dbt, Airflow, Looker) vs humains
• Concentration : les top 3 font souvent 60-80% des coûts
• Utilisateurs avec avg_gb_per_query anormalement élevé

Dans mes audits, je trouve systématiquement que 80% des coûts viennent de 3-4 service accounts. Le problème, c'est que personne ne sait vraiment ce qu'ils font. Les équipes data ont configuré des pipelines il y a 2 ans et n'y ont plus touché. Première action : identifier le owner de chaque service account.

2.2 Top requêtes individuelles

SELECT
  job_id,
  user_email,
  ROUND(total_bytes_billed / POW(1024, 4) * 5, 2) AS cost_usd,
  ROUND(total_bytes_billed / POW(1024, 3), 1) AS gb_scanned,
  TIMESTAMP_DIFF(end_time, start_time, SECOND) AS duration_sec,
  cache_hit,
  SUBSTR(query, 1, 300) AS query_preview
FROM `region-eu`.INFORMATION_SCHEMA.JOBS
WHERE creation_time > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY)
  AND job_type = 'QUERY'
  AND total_bytes_billed > 0
ORDER BY total_bytes_billed DESC
LIMIT 25;

Red flags :

• Requêtes > $10 → à investiguer
• Requêtes > $100 → incident potentiel
• Même requête répétée sans cache hit

2.3 Requêtes les plus fréquentes

-- Hash des requêtes pour regrouper les similaires
SELECT
  FARM_FINGERPRINT(REGEXP_REPLACE(query, r'\d+', 'N')) AS query_hash,
  COUNT(*) AS executions,
  ROUND(SUM(total_bytes_billed) / POW(1024, 4) * 5, 2) AS total_cost_usd,
  ROUND(AVG(total_bytes_billed) / POW(1024, 3), 2) AS avg_gb,
  ANY_VALUE(SUBSTR(query, 1, 200)) AS query_sample
FROM `region-eu`.INFORMATION_SCHEMA.JOBS
WHERE creation_time > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY)
  AND job_type = 'QUERY'
GROUP BY query_hash
HAVING COUNT(*) > 10
ORDER BY total_cost_usd DESC
LIMIT 20;

Ce que je cherche :

• Requêtes exécutées 100+ fois/jour → candidat pour matérialisation
• Coût total élevé mais coût unitaire faible → volume à optimiser

Phase 3 : Analyser les patterns problématiques (30 min)

3.1 SELECT * (le classique)

L'erreur que je vois le plus souvent : des SELECT * dans les pipelines dbt. Les data engineers pensent "c'est juste pour le développement, on optimisera plus tard". Sauf que "plus tard" n'arrive jamais, et ces requêtes tournent 50 fois par jour en prod.

SELECT
  job_id,
  user_email,
  ROUND(total_bytes_billed / POW(1024, 3), 1) AS gb_scanned,
  ROUND(total_bytes_billed / POW(1024, 4) * 5, 2) AS cost_usd,
  SUBSTR(query, 1, 150) AS query_preview
FROM `region-eu`.INFORMATION_SCHEMA.JOBS
WHERE creation_time > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY)
  AND job_type = 'QUERY'
  AND REGEXP_CONTAINS(UPPER(query), r'SELECT\s+\*')
  AND total_bytes_billed > 10 * POW(1024, 3)  -- > 10 GB
ORDER BY total_bytes_billed DESC
LIMIT 20;

3.2 Full table scans (pas de partition filter)

SELECT
  job_id,
  user_email,
  referenced_tables,
  ROUND(total_bytes_billed / POW(1024, 3), 1) AS gb_scanned,
  ROUND(total_bytes_billed / POW(1024, 4) * 5, 2) AS cost_usd,
  SUBSTR(query, 1, 200) AS query_preview
FROM `region-eu`.INFORMATION_SCHEMA.JOBS
WHERE creation_time > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY)
  AND job_type = 'QUERY'
  AND total_bytes_billed > 100 * POW(1024, 3)  -- > 100 GB
  -- Indice : pas de filtre date visible
  AND NOT REGEXP_CONTAINS(LOWER(query), r'where.*date|partition|_partitiontime')
ORDER BY total_bytes_billed DESC
LIMIT 15;

3.3 Taux de cache

SELECT
  DATE(creation_time) AS day,
  COUNTIF(cache_hit) AS cache_hits,
  COUNTIF(NOT cache_hit) AS cache_misses,
  ROUND(COUNTIF(cache_hit) / COUNT(*) * 100, 1) AS cache_hit_rate
FROM `region-eu`.INFORMATION_SCHEMA.JOBS
WHERE creation_time > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY)
  AND job_type = 'QUERY'
GROUP BY day
ORDER BY day;

Benchmark :

• < 20% cache hit → problème (requêtes trop variées ou tables modifiées souvent)
• 20-40% → normal pour analytics

• 40% → bon, dashboards bien configurés

3.4 Échecs et timeouts

SELECT
  state,
  error_result.reason AS error_reason,
  COUNT(*) AS count,
  ROUND(SUM(total_bytes_processed) / POW(1024, 4), 2) AS tb_processed_anyway
FROM `region-eu`.INFORMATION_SCHEMA.JOBS
WHERE creation_time > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY)
  AND state != 'DONE'
GROUP BY state, error_reason
ORDER BY count DESC;

Ce que je cherche :

• Beaucoup de CANCELLED → timeouts, possible gaspillage
• Erreurs récurrentes → pipelines cassés qui réessaient

Phase 4 : Analyser le storage (20 min)

Rappel : je fais cette phase en dernier, pas en premier. Le storage coûte rarement plus de 10-15% de la facture BigQuery totale.

4.1 Vue d'ensemble storage

SELECT
  table_schema AS dataset,
  COUNT(*) AS table_count,
  ROUND(SUM(active_physical_bytes) / POW(1024, 4), 2) AS active_tb,
  ROUND(SUM(long_term_physical_bytes) / POW(1024, 4), 2) AS longterm_tb,
  ROUND(SUM(active_physical_bytes + long_term_physical_bytes) / POW(1024, 4), 2) AS total_tb
FROM `project`.`region-eu`.INFORMATION_SCHEMA.TABLE_STORAGE
GROUP BY dataset
ORDER BY total_tb DESC
LIMIT 20;

4.2 Ratio active vs long-term

SELECT
  ROUND(SUM(active_physical_bytes) / POW(1024, 4), 2) AS active_tb,
  ROUND(SUM(long_term_physical_bytes) / POW(1024, 4), 2) AS longterm_tb,
  ROUND(SUM(active_physical_bytes) /
    (SUM(active_physical_bytes) + SUM(long_term_physical_bytes)) * 100, 1) AS active_pct
FROM `project`.`region-eu`.INFORMATION_SCHEMA.TABLE_STORAGE;

Benchmark :

• Active > 50% → lifecycle policies manquantes
• Active < 30% → bien optimisé

4.3 Tables potentiellement inutilisées

SELECT
  table_schema,
  table_name,
  ROUND(total_physical_bytes / POW(1024, 3), 2) AS size_gb,
  TIMESTAMP_MILLIS(last_modified_time) AS last_modified,
  DATE_DIFF(CURRENT_DATE(), DATE(TIMESTAMP_MILLIS(last_modified_time)), DAY) AS days_since_modified
FROM `project.dataset.__TABLES__`
WHERE DATE_DIFF(CURRENT_DATE(), DATE(TIMESTAMP_MILLIS(last_modified_time)), DAY) > 90
ORDER BY total_physical_bytes DESC
LIMIT 20;

J'ai vu chez un client 2 TB de tables "tmp_" et "test_" créées par des data scientists qui avaient quitté l'entreprise depuis 18 mois. Personne n'osait les supprimer "au cas où". On a fait le ménage ensemble : 400€/mois d'économies juste sur le storage.

4.4 Tables sans partitionnement

SELECT
  table_schema,
  table_name,
  ROUND(total_physical_bytes / POW(1024, 3), 2) AS size_gb
FROM `project`.`region-eu`.INFORMATION_SCHEMA.TABLE_STORAGE ts
LEFT JOIN `project`.`region-eu`.INFORMATION_SCHEMA.PARTITIONS p
  ON ts.table_schema = p.table_schema AND ts.table_name = p.table_name
WHERE p.partition_id IS NULL
  AND ts.total_physical_bytes > 10 * POW(1024, 3)  -- > 10 GB
ORDER BY total_physical_bytes DESC;

Phase 5 : Vérifier la gouvernance (15 min)

5.1 Configuration des tables critiques

SELECT
  table_schema,
  table_name,
  ROUND(total_physical_bytes / POW(1024, 3), 2) AS size_gb,
  -- Ces infos viennent des options de table
  'CHECK MANUALLY' AS require_partition_filter,
  'CHECK MANUALLY' AS partition_expiration
FROM `project`.`region-eu`.INFORMATION_SCHEMA.TABLE_STORAGE
WHERE total_physical_bytes > 100 * POW(1024, 3)  -- > 100 GB
ORDER BY total_physical_bytes DESC
LIMIT 10;

Pour chaque grande table, vérifier manuellement :

SELECT option_name, option_value
FROM `project.dataset.INFORMATION_SCHEMA.TABLE_OPTIONS`
WHERE table_name = 'ma_grande_table';

5.2 Labels/tags existants

SELECT
  table_schema,
  table_name,
  labels
FROM `project`.`region-eu`.INFORMATION_SCHEMA.TABLE_STORAGE
WHERE labels IS NOT NULL
LIMIT 20;

Ce que je cherche :

• % de tables avec labels → gouvernance en place ou non
• Labels utilisés → team, env, cost_center ?

5.3 Quotas et limites

Vérifier dans la console GCP :

• Project-level quotas configurés ?
• Custom quotas par utilisateur ?
• Alertes budget en place ?

Ce que je déconseille : mettre des quotas utilisateur trop stricts dès le départ. J'ai vu des équipes data complètement bloquées parce qu'un admin FinOps avait mis un quota de 1 TB/jour sans prévenir. Commence par monitorer, ensuite tu ajustes.

Phase 6 : Synthèse et recommandations (20 min)

Template de rapport

Structure recommandée :

1. Résumé exécutif

• Coût mensuel actuel : XX €
• Économies potentielles identifiées : XX € (XX%)
• Priorité #1 : [Quick win principal]

2. Findings

• Critiques : [Finding] - Impact : XX €/mois - Effort : Faible/Moyen/Élevé
• Importants : ...
• Améliorations : ...

3. Top 5 Actions

4. Métriques à suivre

• Coût quotidien moyen
• Cache hit rate
• Top 3 consommateurs

Checklist récapitulative

Phase 1 : Vue d'ensemble (15 min)

• Coût total 30 jours
• Évolution jour par jour
• Répartition par type

Phase 2 : Gros consommateurs (20 min)

• Top 15 utilisateurs
• Top 25 requêtes
• Requêtes fréquentes

Phase 3 : Patterns problématiques (30 min)

• SELECT *
• Full table scans
• Taux de cache
• Échecs

Phase 4 : Storage (20 min)

• Vue d'ensemble
• Ratio active/long-term
• Tables zombies
• Tables non partitionnées

Phase 5 : Gouvernance (15 min)

• Config grandes tables
• Labels
• Quotas

Phase 6 : Synthèse (20 min)

• Rapport écrit
• Top 5 actions
• Métriques de suivi

Conclusion

En 2 heures, tu peux avoir une vue claire de :

• Où part l'argent
• Qui consomme le plus
• Quels patterns sont problématiques
• Quelles sont les quick wins

Le plus important n'est pas d'être exhaustif, mais d'identifier les 20% d'actions qui apporteront 80% des économies.

Cette méthode, je l'ai affinée sur des dizaines d'audits. Elle fonctionne aussi bien pour une startup avec 500€/mois de BigQuery que pour une scale-up à 50k€/mois. La différence, c'est juste le nombre de zéros sur les économies identifiées.

Si ce contenu t'a été utile, tu peux me suivre sur LinkedIn où je partage régulièrement mes retours d'expérience FinOps, ou t'abonner à ma newsletter pour recevoir mes prochains articles directement dans ta boîte mail.