Shopify a découvert une requête qui leur aurait coûté 949 000 $ par mois. La solution ? Ajouter du clustering sur les colonnes utilisées dans les clauses WHERE. Ce simple changement a réduit les octets scannés de 75 Go à 508 Mo, une amélioration de 150x grâce à une configuration qui prend quelques minutes à implémenter.

Cette histoire illustre l’essence de l’optimisation des coûts BigQuery : les gains les plus importants proviennent d’un petit ensemble de techniques que la plupart des équipes ignorent ou configurent mal. Pendant ce temps, les équipes passent des heures à débattre des niveaux de stockage alors que 85-90% de leur facture BigQuery provient des coûts de calcul.

Ce guide couvre les 20% d’optimisations qui génèrent 80% des économies. Nous passerons les bases (vous savez probablement déjà ce qu’est le partitionnement). Nous nous concentrerons plutôt sur les configurations qui font vraiment la différence, les anti-patterns qui créent des factures surprises de 20 000 $, et les stratégies spécifiques à dbt qui restent sous-explorées dans la plupart des guides BigQuery.

Partie 1 : Comprendre où va réellement votre argent

La répartition des coûts BigQuery que la plupart des équipes comprennent mal

Les coûts BigQuery se répartissent en trois catégories : calcul (octets scannés), stockage et ingestion en streaming. Voici ce que la plupart des équipes ratent : le calcul représente généralement 85-90% des dépenses totales. Pourtant, les efforts d’optimisation commencent souvent par le nettoyage du stockage.

Les mathématiques sont claires. À 6,25 $ par Tio pour les requêtes à la demande, scanner 10 To quotidiennement coûte 1 875 $ par mois en calcul seul. Ces mêmes 10 To stockés coûtent 200 $ par mois. Une réduction de 50% des octets scannés économise 937 $ mensuellement ; une réduction de 50% du stockage économise 100 $.

Le modèle mental clé : BigQuery facture les données lues, pas les données retournées. Ajouter LIMIT 1000 à une requête ne réduit pas les coûts car BigQuery scanne quand même l’ensemble du dataset avant d’appliquer la limite. Sélectionner 5 colonnes au lieu de 50 dans une table large peut réduire les coûts de 90%. La taille du résultat est sans importance ; c’est la taille du scan qui détermine votre facture.

Comment trouver vos gouffres financiers

Avant d’optimiser quoi que ce soit, identifiez où va réellement l’argent. Cette requête fait ressortir 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;

Pour l’attribution des coûts par utilisateur ou compte de service, agrégez les données :

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;

Exécutez ces requêtes hebdomadairement. Les résultats révèlent souvent que 3-5 requêtes ou utilisateurs représentent 70%+ des dépenses de calcul. Concentrez vos efforts d’optimisation là en premier.

Partie 2 : Les trois leviers qui contrôlent 90% des coûts de requête

Partitionnement : votre première ligne de défense

Le partitionnement divise une table en segments basés sur les valeurs d’une colonne, permettant à BigQuery d’ignorer entièrement les segments non pertinents. Une table bien partitionnée avec des filtres de requête appropriés réduit typiquement les octets scannés de 70-90%.

Quand partitionner : Tables dépassant 100 Go avec des patterns de filtrage cohérents sur une colonne spécifique. Le recommender de Google ne suggère le partitionnement que pour les tables au-dessus de ce seuil car les tables plus petites n’en bénéficient pas assez pour justifier la surcharge de métadonnées.

Types de partition :

• Partitionnement par unité de temps (DAY, MONTH, YEAR) : Idéal pour les données d’événements, logs et transactions où vous filtrez sur un timestamp
• Partitionnement par plage d’entiers : Utile pour le filtrage basé sur des ID quand le temps n’est pas pertinent
• Partitionnement par temps d’ingestion : Partitionne par temps de chargement plutôt que par contenu des données ; utile quand les données source n’ont pas de timestamp fiable

La limite des 10 000 partitions : BigQuery autorise un maximum de 10 000 partitions par table. Le partitionnement quotidien couvre environ 27 ans ; le partitionnement horaire épuise la limite en environ 14 mois. Choisissez la granularité en fonction de votre période de rétention et de vos patterns de requête.

Création d’une table partitionnée avec filtres de partition obligatoires :

CREATE TABLE `project.dataset.events`
(
  event_id STRING,
  user_id STRING,
  event_type STRING,
  event_timestamp TIMESTAMP,
  properties JSON
)
PARTITION BY DATE(event_timestamp)
CLUSTER BY user_id, event_type
OPTIONS (
  require_partition_filter = true
);

L’option require_partition_filter = true empêche les scans accidentels de table complète. Les requêtes sans filtre sur la colonne de partition échoueront plutôt que de tout scanner.

Dans dbt, configurez le partitionnement dans votre modèle :

{{ config(
    materialized='table',
    partition_by={
      "field": "event_timestamp",
      "data_type": "timestamp",
      "granularity": "day"
    },
    require_partition_filter=true
) }}

SELECT
    event_id,
    user_id,
    event_type,
    event_timestamp,
    properties
FROM {{ source('raw', 'events') }}

Clustering : le multiplicateur composé

Le clustering trie les données au sein des partitions (ou à travers toute la table pour les tables non partitionnées) basé sur jusqu’à quatre colonnes. Contrairement au partitionnement, le clustering ne crée pas de limites strictes. Il organise les données pour minimiser les octets scannés lors du filtrage sur les colonnes clusterisées.

Une table peut être à la fois partitionnée et clusterisée, et cette combinaison donne les meilleurs résultats. Le partitionnement fournit un filtrage grossier (ignorer des plages de dates entières), tandis que le clustering fournit un filtrage fin au sein de ces partitions (ignorer les blocs qui ne contiennent pas les user_ids pertinents).

L’ordre des colonnes compte significativement. BigQuery priorise la première colonne de clustering, puis la seconde au sein des blocs de la première, et ainsi de suite. Placez votre colonne la plus fréquemment filtrée en premier.

Taille minimum de table : Le clustering bénéficie aux tables plus grandes que 64 Mo. Les tables plus petites tiennent de toute façon dans un seul bloc, rendant le clustering inutile.

Le piège du dry run : Contrairement au partitionnement, le clustering ne fournit pas d’estimations de coût précises dans les dry runs. Un dry run pourrait estimer 50 Go scannés, mais la requête réelle ne scanne que 5 Go grâce au clustering. Les estimations conservatrices ne devraient pas vous décourager de clusteriser.

CREATE TABLE `project.dataset.user_events`
(
  event_date DATE,
  user_id STRING,
  event_type STRING,
  event_name STRING,
  properties JSON
)
PARTITION BY event_date
CLUSTER BY user_id, event_type;

Dans dbt, combinez partitionnement et clustering :

{{ config(
    materialized='incremental',
    partition_by={
      "field": "event_date",
      "data_type": "date"
    },
    cluster_by=["user_id", "event_type"],
    require_partition_filter=true
) }}

La fameuse réduction de coûts de Shopify provenait entièrement de l’ajout de clustering. Leur requête filtrait sur des colonnes qui n’étaient pas clusterisées, forçant BigQuery à scanner 75 Go. Après avoir ajouté le clustering sur ces colonnes de filtre, les scans sont tombés à 508 Mo : une réduction de 150x qui a évité 949 000 $ de coûts mensuels.

Sélection des colonnes : le multiplicateur oublié

Le stockage en colonnes de BigQuery signifie que chaque colonne est stockée séparément. Sélectionner les 50 colonnes d’une table coûte 50x plus que sélectionner une seule colonne. Cela fait de SELECT * l’un des patterns les plus coûteux dans BigQuery.

Considérez une table de 5 To avec 10 colonnes de taille à peu près égale :

Le mythe du LIMIT : Beaucoup d’équipes supposent que LIMIT 1000 réduit les coûts. BigQuery scanne d’abord l’ensemble du dataset, puis applique la limite sur la sortie. Cette requête coûte exactement la même chose que vous limitiez à 10 lignes ou 10 millions :

-- Les deux requêtes scannent des octets identiques
SELECT * FROM large_table LIMIT 10;
SELECT * FROM large_table LIMIT 10000000;

**Alternatives pratiques à SELECT *** :

1. Onglet de prévisualisation : L’onglet de prévisualisation de la console BigQuery affiche des données d’exemple sans aucun coût de requête (totalement gratuit)
2. TABLESAMPLE : Pour l’échantillonnage statistique, SELECT * FROM table TABLESAMPLE SYSTEM (1 PERCENT) ne scanne que 1% des données
3. Listes explicites de colonnes : Toujours spécifier les colonnes dans les requêtes de production

Dans les modèles dbt, évitez le pattern paresseux de tout sélectionner depuis les modèles de base :

-- Coûteux : scanne toutes les colonnes du modèle de base
SELECT * FROM {{ ref('base__analytics__events') }}

-- Mieux : colonnes explicites, seulement ce qui est nécessaire
SELECT
    event_id,
    user_id,
    event_timestamp,
    event_type
FROM {{ ref('base__analytics__events') }}

Partie 3 : Coûts de stockage (les changements que la plupart des guides ratent)

Facturation physique vs logique

En juillet 2023, Google a introduit la facturation par octets physiques comme alternative à la facturation logique par défaut. Ce changement reste sous-exploré dans la plupart des guides d’optimisation mais peut générer 30-50% d’économies de stockage.

Facturation logique (par défaut) : Vous payez pour la taille des données non compressées à 0,02 $/Go par mois. Le time travel et le stockage fail-safe sont inclus gratuitement.

Facturation physique : Vous payez pour les données compressées à 0,04 $/Go par mois. Le time travel (7 jours) et le fail-safe (7 jours) sont facturés séparément.

La facturation physique gagne quand votre ratio de compression dépasse 2:1. Puisque le physique coûte 2x par Go mais que les données se compressent typiquement 6-17x, la plupart des organisations économisent significativement en basculant.

Interrogez votre ratio de compression réel avant de décider :

SELECT
  table_schema,
  table_name,
  total_logical_bytes / POW(1024, 3) AS logical_gb,
  total_physical_bytes / POW(1024, 3) AS physical_gb,
  ROUND(total_logical_bytes / total_physical_bytes, 2) AS compression_ratio,
  -- Si le ratio > 2, la facturation physique économise de l'argent
  CASE
    WHEN total_logical_bytes / total_physical_bytes > 2
    THEN 'Passer au physique'
    ELSE 'Garder le logique'
  END AS recommendation
FROM `project.region-us`.INFORMATION_SCHEMA.TABLE_STORAGE
WHERE total_logical_bytes > 0
ORDER BY total_logical_bytes DESC;

Compromis à considérer : Avec la facturation physique, le time travel et le fail-safe apparaissent comme des postes séparés. Pour les tables avec des mises à jour fréquentes (qui génèrent une surcharge de time travel), cela peut compenser les économies de compression.

Stockage long terme : réduction gratuite de 50% après 90 jours

BigQuery transfère automatiquement les données vers le stockage long terme après 90 jours consécutifs sans modification. Le prix passe de 0,02 $/Go à 0,01 $/Go : une réduction de 50% ne nécessitant aucune configuration.

Ce qui réinitialise le compteur de 90 jours :

• Toute opération DML (INSERT, UPDATE, DELETE, MERGE)
• Les insertions en streaming
• CREATE OR REPLACE TABLE

Ce qui NE réinitialise PAS le compteur :

• Interroger la table
• Créer des vues sur la table
• Exporter des données
• Copier la table

Pour les tables partitionnées, chaque partition est évaluée indépendamment. Une table avec 365 partitions quotidiennes aura ~275 partitions au tarif long terme et ~90 au tarif actif, en supposant que seules les données récentes reçoivent des écritures.

Expiration des tables : prévenir l’accumulation de stockage

Les tables temporaires, artefacts ETL et expérimentations de développement s’accumulent silencieusement. Une startup a découvert qu’elle payait 3 000 $ mensuellement pour des tables temporaires que personne n’avait interrogées depuis des mois.

Définissez des valeurs par défaut au niveau du dataset :

ALTER SCHEMA `project.staging`
SET OPTIONS (
  default_table_expiration_days = 7
);

Surchargez au niveau de la table si nécessaire :

CREATE TABLE `project.staging.temp_analysis`
OPTIONS (
  expiration_timestamp = TIMESTAMP_ADD(CURRENT_TIMESTAMP(), INTERVAL 24 HOUR)
) AS
SELECT * FROM source_table WHERE condition;

Dans dbt, utilisez la config hours_to_expiration pour les modèles temporaires :

{{ config(
    materialized='table',
    hours_to_expiration=168  -- 7 jours
) }}

Partie 4 : Quand passer de la demande aux slots

Les mathématiques du seuil de rentabilité

La tarification par Éditions de BigQuery (introduite en juillet 2023) a remplacé le forfait legacy par trois niveaux :

100 slots fonctionnant en continu sur l’Édition Standard PAYG coûtent 2 920 $ mensuellement. À 6,25 $ par Tio à la demande, cela équivaut à traiter 467 To. Si vous traitez plus de 400-500 To mensuellement avec des patterns cohérents, les slots économisent probablement de l’argent.

Mais le calcul change dramatiquement pour les workloads en rafale. Traiter 20 To en moins d’une heure :

• À la demande : 125 $ (20 × 6,25 $)
• 100 slots pour 1 heure (Standard) : ~5 $

C’est 95% d’économies pour les scénarios en rafale, rendant les slots attractifs même à des volumes mensuels plus faibles si les workloads se concentrent dans des fenêtres.

Stratégie d’autoscaling

L’autoscaling est devenu généralement disponible en février 2025, permettant une allocation dynamique de slots qui scale de zéro jusqu’à votre maximum configuré.

Décisions de configuration clés :

Slots de base : Définissez à zéro pour les workloads avec des périodes d’inactivité. Les slots scalent depuis zéro instantanément sans pénalité de démarrage. Payez uniquement pour ce que vous utilisez.

Slots maximum : Votre plafond pendant la demande de pointe. Commencez prudemment et augmentez en fonction des temps d’attente observés.

Utilisation cible : Visez 60-80% pendant les heures de pointe. Constamment plus élevé indique un sous-approvisionnement (requêtes en file d’attente) ; constamment plus bas suggère un sur-approvisionnement (paiement pour une capacité inutilisée).

-- Créer une réservation avec autoscaling
CREATE RESERVATION `project.region-us.prod_reservation`
OPTIONS (
  slot_capacity = 0,  -- baseline
  edition = 'ENTERPRISE',
  autoscale = (
    max_slots = 500
  )
);

-- Créer une affectation pour votre projet
CREATE ASSIGNMENT `project.region-us.prod_reservation.prod_assignment`
OPTIONS (
  assignee = 'projects/my-project',
  job_type = 'QUERY'
);

Remises sur engagement (nouveauté 2025)

Google Cloud Next 2025 a introduit des remises sur engagement basées sur les dépenses pour BigQuery :

• Engagement 1 an : 10% de réduction par rapport au PAYG
• Engagement 3 ans : 20% de réduction par rapport au PAYG

Contrairement aux engagements basés sur les slots, ceux-ci sont libellés en dollars et flexibles entre les Éditions BigQuery, Cloud Composer 3 et les produits Data Governance. Cela simplifie la planification de capacité : engagez-vous sur un niveau de dépenses mensuelles plutôt que de deviner les comptages de slots.

Pour les workloads prévisibles, combinez un engagement pour la capacité de base avec l’autoscaling pour les rafales. Vous capturez des économies sur l’utilisation prévisible tout en maintenant la flexibilité pour les pics.

Comparaison des fonctionnalités par édition

Au-delà du prix, les éditions diffèrent en capacités :

Édition Standard :

• Maximum 1 600 slots
• Pas de BigQuery ML
• Pas de vues matérialisées avec rafraîchissement automatique
• Idéale pour : Développement, analyse ad-hoc, workloads sensibles aux coûts

Édition Enterprise :

• Slots illimités (avec quota approprié)
• BigQuery ML inclus
• Accélération BI Engine
• Recherche full-text
• SLA 99,99%
• Idéale pour : Workloads de production, pipelines ML

Édition Enterprise Plus :

• Tout ce qui est dans Enterprise
• Reprise après sinistre multi-région
• Certifications de conformité (FedRAMP, CJIS, IL4)
• Idéale pour : Industries réglementées, workloads critiques

Note importante : La tarification à la demande maintient la parité des fonctionnalités avec Enterprise Plus (sauf les requêtes continues). Si vous avez besoin de fonctionnalités de conformité mais avez une utilisation imprévisible, la demande peut être plus rentable que les slots Enterprise Plus.

Partie 5 : Optimisations spécifiques à dbt

La plupart des guides traitent dbt et l’optimisation des coûts BigQuery séparément. Pourtant, les résultats réels des clients démontrent l’impact : Bilt Rewards a réduit de 20 000 $ mensuellement, Enpal a réduit ses dépenses data de 70%, et Symend a diminué l’utilisation quotidienne de son data warehouse de 70% grâce à des configurations dbt appropriées.

Impact de la matérialisation sur les coûts

Votre choix de matérialisation détermine les coûts de base pour chaque modèle :

Table : Un scan complet unique lors de la construction, puis les coûts de stockage. Chaque requête en aval lit depuis la table matérialisée gratuitement (pas de recalcul).

View : Zéro coût de construction, zéro stockage, mais scan complet en amont à chaque requête. Cela devient un piège à coûts pour les transformations complexes ou les modèles fréquemment interrogés.

Ephemeral : Pas de stockage, interpolé comme un CTE. Les CTE référencés plusieurs fois s’exécutent une fois par référence, potentiellement doublant les coûts. Les modèles éphémères sont aussi difficiles à déboguer car ils n’existent pas comme objets interrogeables.

L’anti-pattern de la chaîne de vues : dbt_project_evaluator signale les chaînes de 4+ vues comme problématiques. Rien ne calcule jusqu’à ce qu’une requête atteigne une table, déclenchant l’exécution en cascade de toute la logique des vues en amont. Cinq vues chaînées ne coûtent pas 5x ; elles peuvent coûter 5x sur chaque requête en aval.

Étant donné que les coûts de stockage sont négligeables par rapport au calcul (Partie 1), utiliser les tables par défaut a du sens :

models:
  my_project:
    base:
      +materialized: table
    intermediate:
      +materialized: table
    marts:
      +materialized: table
      +partition_by:
        field: created_date
        data_type: date

Les vues peuvent fonctionner pour des transformations vraiment simples qui sont rarement interrogées, mais les tables éliminent le risque de coûts de calcul en cascade.

Modèles incrémentaux : le gain le plus important

Les rafraîchissements complets de tables scannent et réécrivent des tables entières à chaque exécution. Pour une table de 500 Go exécutée quotidiennement, c’est 3,13 $ par exécution, 94 $ mensuellement, juste pour la reconstruction avant toute requête.

Les modèles incrémentaux ne traitent que les données nouvelles ou modifiées. Les économies scalent avec la taille de la table et la fréquence d’exécution.

Le piège de la stratégie par défaut : La stratégie incrémentale par défaut de BigQuery est merge, qui scanne toute la table de destination à chaque exécution pour identifier les lignes correspondantes. Pour les grandes tables, cela annule l’objectif.

Passez à insert_overwrite pour les tables partitionnées :

{{ config(
    materialized='incremental',
    incremental_strategy='insert_overwrite',
    partition_by={
      "field": "event_date",
      "data_type": "date"
    },
    cluster_by=["user_id", "event_type"]
) }}

SELECT
    event_date,
    user_id,
    event_type,
    COUNT(*) as event_count
FROM {{ source('raw', 'events') }}
WHERE event_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 3 DAY)
GROUP BY 1, 2, 3

En utilisant insert_overwrite, ajoutez copy_partitions: true pour utiliser l’API Copy Table de BigQuery au lieu des instructions INSERT :

{{ config(
    materialized='incremental',
    incremental_strategy='insert_overwrite',
    partition_by={"field": "event_date", "data_type": "date"},
    partitions=["date_sub(current_date, interval 1 day)", "current_date"],
    copy_partitions=true
) }}

Le piège is_incremental() : Le pattern dbt courant pour les modèles incrémentaux cause toujours des scans complets de la table source :

-- Ce pattern scanne toute la table source !
{% if is_incremental() %}
WHERE event_timestamp > (SELECT MAX(event_timestamp) FROM {{ this }})
{% endif %}

La sous-requête SELECT MAX(event_timestamp) FROM {{ this }} n’aide pas BigQuery à élaguer les partitions dans la table source. Utilisez des valeurs de partition statiques ou la variable _dbt_max_partition à la place :

{{ config(
    materialized='incremental',
    incremental_strategy='insert_overwrite',
    partition_by={"field": "event_date", "data_type": "date"}
) }}

SELECT *
FROM {{ source('raw', 'events') }}
{% if is_incremental() %}
-- La référence de partition statique permet l'élagage
WHERE event_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 3 DAY)
{% endif %}

Ou utilisez la variable de partition intégrée de dbt :

{% if is_incremental() %}
WHERE event_date >= DATE('{{ var("_dbt_max_partition") }}')
{% endif %}

Attribution des coûts avec dbt

Suivre les coûts par modèle nécessite d’étiqueter les requêtes avec des métadonnées. Configurez dbt pour ajouter des labels de job :

query-comment:
  comment: "dbt: {{ node.unique_id }}"
  append: true

# Ou plus détaillé :
query-comment:
  comment: |
    {
      "dbt_model": "{{ node.unique_id }}",
      "dbt_invocation_id": "{{ invocation_id }}",
      "dbt_target": "{{ target.name }}"
    }
  append: true

Puis interrogez les coûts par modèle :

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;

Pour un monitoring complet, le package communautaire dbt-bigquery-monitoring automatise ce pattern à travers les projets et régions.

Partie 6 : Les erreurs coûteuses

Les forums communautaires révèlent des patterns d’erreurs BigQuery coûteuses qui diffèrent de ce que les guides d’optimisation couvrent typiquement. Un fil Hacker News sur les coûts BigQuery a fait émerger des praticiens partageant des histoires de surprises de 8 000 à 20 000 $ sur un seul mois.

Le mythe de la clause LIMIT

Cette idée fausse cause le plus de factures inattendues. Les tests confirment : SELECT * FROM table LIMIT 100 scanne la table entière. LIMIT n’affecte que la taille de sortie, pas les octets traités.

L’exemple de Google lui-même : si 10 utilisateurs exécutent des requêtes SELECT * 10 fois par mois sur une table de 10 To, les coûts de calcul passent de 200 $ (stockage seul) à plus de 5 000 $ mensuellement.

Utilisez l’onglet de prévisualisation (totalement gratuit), appliquez des filtres de partition, ou sélectionnez uniquement les colonnes nécessaires. N’utilisez jamais LIMIT comme mécanisme de contrôle des coûts.

Double comptage des CTE

BigQuery ne matérialise pas les CTE non récursifs. Chaque référence réexécute le scan sous-jacent. Un CTE référencé deux fois coûte deux fois plus.

-- Ceci scanne la table de base DEUX FOIS
WITH user_aggregates AS (
  SELECT user_id, complex_aggregation
  FROM base__analytics__user_events
  GROUP BY user_id
)
SELECT a.user_id, a.complex_aggregation, b.complex_aggregation
FROM user_aggregates a
JOIN user_aggregates b ON a.user_id = b.user_id;  -- Deuxième scan !

Matérialisez les CTE coûteux dans des tables temporaires :

-- Matérialiser dans une table temp (scanne une fois)
CREATE TEMP TABLE user_aggregates AS
SELECT user_id, complex_aggregation
FROM base__analytics__user_events
GROUP BY user_id;

-- Maintenant les deux références lisent depuis la table temp (pas cher)
SELECT a.user_id, a.complex_aggregation, b.complex_aggregation
FROM user_aggregates a
JOIN user_aggregates b ON a.user_id = b.user_id;

Streaming vs chargement par batch

Les insertions en streaming coûtent 0,01 $ par 200 Mo avec un minimum de 1 Ko par ligne. Le chargement par batch utilisant le pool de slots partagés de BigQuery est totalement gratuit.

Pour une table recevant 10 Go de données quotidiennement :

• Streaming : 0,50 $/jour, 15 $/mois
• Chargement par batch : 0 $/mois

Un praticien a documenté une réduction de 95% des coûts BigQuery en passant du streaming aux chargements de fichiers avec du micro-batching.

Quand le streaming est justifié :

• Exigences temps réel véritables (latence inférieure à la minute)
• Données qui perdent de la valeur après des minutes, pas des heures
• Systèmes où l’orchestration par batch ajoute une complexité inacceptable

Quand utiliser le batch à la place :

• Tolérance de latence de 15+ minutes
• Ingestion à grand volume (les coûts de streaming scalent linéairement)
• Patterns de rafraîchissement de data warehouse (horaire, quotidien)

Filtres de partition manquants

La même requête sur la même table peut coûter 17x plus selon la colonne sur laquelle vous filtrez :

-- Scanne uniquement les partitions pertinentes : 884 Mo
SELECT * FROM events
WHERE event_date = '2024-01-15';

-- Scanne toute la table : 15 Go (17x plus cher !)
SELECT * FROM events
WHERE DATE(event_timestamp) = '2024-01-15';

La deuxième requête applique une fonction à la colonne de partition, empêchant BigQuery d’élaguer les partitions.

Patterns de filtre courants qui cassent l’élagage :

-- MAUVAIS : Fonction sur la colonne de partition
WHERE DATE(created_timestamp) = '2024-01-15'
WHERE EXTRACT(YEAR FROM event_date) = 2024

-- BON : Comparaison directe
WHERE created_timestamp BETWEEN '2024-01-15' AND '2024-01-16'
WHERE event_date = '2024-01-15'

-- MAUVAIS : OR avec une colonne non-partition
WHERE event_date = '2024-01-15' OR user_id = 'abc'

-- BON : Requêtes séparées ou restructurer la logique
WHERE event_date = '2024-01-15'

Self-joins

La documentation de Google avertit explicitement : les self-joins “peuvent mettre au carré le nombre de lignes de sortie”, faisant exploser les coûts de manière imprévisible.

-- Self-join : scanne potentiellement la table deux fois, les lignes de sortie peuvent exploser
SELECT
  a.user_id,
  a.event_timestamp AS first_event,
  b.event_timestamp AS next_event
FROM events a
JOIN events b
  ON a.user_id = b.user_id
  AND b.event_timestamp > a.event_timestamp;

Remplacez par des fonctions de fenêtre :

-- Fonction de fenêtre : un seul scan, sortie prévisible
SELECT
  user_id,
  event_timestamp AS first_event,
  LEAD(event_timestamp) OVER (
    PARTITION BY user_id
    ORDER BY event_timestamp
  ) AS next_event
FROM events;

Les fonctions de fenêtre (LEAD, LAG, ROW_NUMBER, RANK) accomplissent la plupart des cas d’usage de self-join avec un seul scan de table.

Partie 7 : Gouvernance et garde-fous

Pour les organisations avec des utilisateurs d’analytique ad-hoc, les contrôles de gouvernance peuvent générer plus d’économies que les optimisations techniques. Un seul analyste exécutant des requêtes SELECT * non filtrées peut générer des factures dépassant des mois d’efforts d’ingénierie sur le partitionnement.

Contrôles au niveau requête

max_bytes_billed définit une limite stricte par requête. Les requêtes qui dépasseraient cette limite échouent avant de scanner les données :

-- La requête échoue si elle scannerait plus de 10 Go
SELECT * FROM large_table
OPTIONS (max_bytes_billed = 10737418240);  -- 10 Go en octets

Dans dbt, configurez dans votre profil :

my_project:
  target: prod
  outputs:
    prod:
      type: bigquery
      method: oauth
      project: my-project
      dataset: production
      maximum_bytes_billed: 107374182400  # 100 Go

Pour les exécutions dbt, cela empêche tout modèle individuel de scanner plus que la limite spécifiée. Utile pour détecter les modèles incrémentaux mal configurés qui exécutent accidentellement des rafraîchissements complets.

Quotas au niveau projet

Depuis septembre 2025, les nouveaux projets BigQuery ont par défaut un quota quotidien de 200 Tio. Pour les projets existants, configurez des quotas personnalisés via l’Admin SDK ou la Cloud Console.

Considérez implémenter :

• Limites quotidiennes par utilisateur : Empêcher les analystes individuels de monopoliser le budget
• Limites par projet : Plafonner les dépenses sur les projets de développement vs production
• Seuils d’alerte : Notifier avant d’atteindre les limites strictes

Patterns d’accès qui préviennent les erreurs coûteuses

Comptes de service par intégration : Créez des comptes de service séparés pour dbt, Looker, Fivetran, etc. Interrogez INFORMATION_SCHEMA par email de compte de service pour attribuer les coûts par système.

**Vues autorisées pour la protection SELECT *** : Au lieu d’accorder un accès direct aux tables, créez des vues qui n’exposent que les colonnes nécessaires :

CREATE VIEW `project.views.user_summary` AS
SELECT user_id, signup_date, user_type
FROM `project.raw.users`;

-- Accorder l'accès à la vue, pas à la table
GRANT roles/bigquery.dataViewer
ON `project.views.user_summary`
TO 'user:analyst@company.com';

Affectations de réservation pour l’isolation de capacité : Affectez différentes équipes à des réservations séparées. Cela empêche le workload lourd d’une équipe de consommer les slots d’une autre équipe.

Partie 8 : Monitoring et optimisation continue

Les requêtes à exécuter hebdomadairement

Top 10 des requêtes coûteuses cette semaine :

SELECT
  user_email,
  SUBSTR(query, 1, 200) AS query_preview,
  ROUND(total_bytes_processed / POW(1024, 4), 4) AS tib_scanned,
  ROUND(total_bytes_processed / POW(1024, 4) * 6.25, 2) AS cost_usd,
  creation_time
FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE
  creation_time > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY)
  AND job_type = 'QUERY'
  AND total_bytes_processed > 0
ORDER BY total_bytes_processed DESC
LIMIT 10;

Tendance des coûts par dataset :

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;

Tables sans partitionnement ou clustering (candidates à l’optimisation) :

SELECT
  table_schema,
  table_name,
  ROUND(total_logical_bytes / POW(1024, 3), 2) AS size_gb,
  CASE WHEN partition_column IS NULL THEN 'Manquant' ELSE 'OK' END AS partitioning,
  CASE WHEN clustering_columns IS NULL THEN 'Manquant' 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;

Exploiter les recommandations intégrées de Google

Le recommender de BigQuery suggère automatiquement le partitionnement et le clustering pour les tables qui en bénéficieraient. Accédez aux recommandations via :

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

Le recommender analyse les patterns de requêtes et suggère des colonnes pour le partitionnement/clustering basé sur l’utilisation réelle des filtres, pas des suppositions. La documentation des bonnes pratiques d’optimisation des coûts de Google couvre des techniques supplémentaires.

Construire un dashboard de coûts

Métriques clés à suivre :

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 changements de coûts
3. Utilisation des slots (si vous utilisez des réservations) : Identifier le sous/sur-approvisionnement
4. Coût moyen par requête : Suivre le progrès de l’optimisation dans le temps
5. Principaux contributeurs aux coûts : Utilisateurs, comptes de service, datasets, modèles

Définissez des 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

Conclusion : La hiérarchie d’optimisation

Une hiérarchie claire devrait guider vos priorités d’optimisation :

Priorité 1 : Patterns de requête (80%+ d’impact)

• Sélection des colonnes : Arrêtez d’utiliser SELECT *
• Filtres de partition : Toujours filtrer sur les colonnes de partition
• Clustering : Ajouter aux tables de plus de 64 Mo que vous filtrez fréquemment

Ces changements prennent quelques minutes à implémenter et capturent la majorité des économies.

Priorité 2 : Configurations dbt (70% de potentiel d’économies)

• Passer aux modèles incrémentaux pour les grandes tables à ajout fréquent
• Utiliser la stratégie insert_overwrite avec copy_partitions: true
• Corriger les patterns is_incremental() qui causent des scans complets

Priorité 3 : Gouvernance (prévient les factures surprises)

• Définir max_bytes_billed sur tous les projets
• Implémenter des quotas quotidiens pour les utilisateurs ad-hoc
• Créer des vues autorisées au lieu d’un accès direct aux tables

Priorité 4 : Modèle de tarification (20-40% d’économies)

• Évaluer les slots si vous traitez > 400 To mensuellement ou des workloads en rafale
• Configurer l’autoscaling avec une base de zéro pour les workloads variables
• Superposer des remises sur engagement pour une base prévisible

Priorité 5 : Stockage (10-30% d’économies)

• Évaluer la facturation physique vs logique en utilisant la requête de ratio de compression
• Définir des politiques d’expiration sur les tables temporaires
• Laisser les remises de stockage long terme fonctionner automatiquement

Par où commencer

Exécutez la requête INFORMATION_SCHEMA de la Partie 1 pour trouver vos 5 requêtes les plus coûteuses. Pour chacune, vérifiez :

1. Utilise-t-elle SELECT * alors qu’elle n’a pas besoin de toutes les colonnes ?
2. Filtre-t-elle sur la colonne de partition ?
3. La table est-elle clusterisée sur les colonnes utilisées dans les clauses WHERE ?

Corriger ces trois problèmes sur vos 5 principales requêtes capture probablement la plupart de vos économies disponibles. Tout le reste est de l’optimisation à la marge.

Annexe : Référence rapide

Aide-mémoire des tarifs (à date de janvier 2026)

Seuils clés

Référence rapide des configs dbt

# Modèle incrémental optimisé pour BigQuery
{{ config(
    materialized='incremental',
    incremental_strategy='insert_overwrite',
    partition_by={
      "field": "event_date",
      "data_type": "date",
      "granularity": "day"
    },
    cluster_by=["user_id", "event_type"],
    require_partition_filter=true,
    copy_partitions=true
) }}