Comment Suivre le Coût de vos Projets DBT sur BigQuery ?

Reprenez ENFIN le contrôle d’une partie de vos factures Google 💰

L’optimisation des coûts est un enjeu clé pour toute équipe data travaillant sur BigQuery. Avec dbt, chaque transformation SQL entraîne des coûts de requêtage, et il est essentiel d’avoir un suivi précis pour comprendre l’impact financier des pipelines de données.

Dans cet article, nous allons voir comment suivre ces coûts efficacement en exploitant les données de facturation de BigQuery et en ajoutant des informations spécifiques à dbt pour un suivi plus fin.

A la fin de cet article, vous serez capable de construire ce type de Dashboard !

📊 Quelles Informations BigQuery Donne pour Suivre les Coûts ?

BigQuery enregistre des informations détaillées sur chaque requête exécutée, accessibles via la table système INFORMATION_SCHEMA.JOBS ou via les logs d’audit dans cloudaudit.googleapis.com/data_access.

Les principales colonnes à surveiller sont :

• job_id : Identifiant unique de chaque exécution
• creation_time : Horodatage du lancement de la requête
• query : Le SQL exécuté
• total_bytes_billed : Nombre d’octets facturés
• total_bytes_processed : Nombre d’octets scannés
• statement_type : Type d’opération SQL (SELECT, INSERT, CREATE TABLE AS, etc.)
• labels : Métadonnées associées aux requêtes, qui permettent de filtrer par projet, utilisateur ou job spécifique

Par exemple, pour récupérer des informations sur les jobs (i.e, l’ensemble des requêtes exécutées sur les 30/31 derniers jours) :

# Nous appelerons cette vue/table "data_costs" par la suite.
SELECT
    DATE(creation_time) AS jour,
    query AS requete_sql,
    user_email as email,
     ARRAY(
        SELECT STRUCT(key, value)
        FROM UNNEST(labels)
        ORDER BY key
    ) AS labels,

    ROUND(SUM(total_bytes_billed) / POW(1024,3),2) AS go_factures,
    # le coût par go dépend de votre mode de facturation et de la région où sont hébergées vos données
    ROUND(8.13 * SUM(total_bytes_billed) / POW(1024,4),2) AS cout_dollars

FROM
    `<gcp_project_id>.region-<region_id>.INFORMATION_SCHEMA.JOBS` 
WHERE
    statement_type != 'SCRIPT'
    AND cache_hit IS false
   AND DATE(creation_time) BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 1 MONTH) AND CURRENT_DATE()
GROUP BY ALL

Attention, cet exemple n’est pas strictement juste puisque selon votre modèle de facturation, Google vous offre probablement votre 1ier To de go facturés [1] !

🔎 Quelles Informations dbt Envoie à BigQuery ?

Lorsque dbt exécute des transformations, il transmet plusieurs métadonnées à BigQuery :

• Nom complet du modèle : Le nom de la transformation, ainsi que le dataset et le projet
• Job ID : Correspondant à l’exécution spécifique
• User : L’utilisateur ayant déclenché la transformation
• Start Time / End Time : Début et fin de l’exécution
• Bytes Processed : Volume de données manipulées
• Labels : dbt ajoute des labels personnalisables pour identifier les transformations

Ces informations sont accessibles directement via la console BigQuery ou en interrogeant les tables système.

A noter que dbt ajoute aussi un ensemble d’informations qui sont configurables et seront envoyées sous forme de commentaires dans la requête SQL :

dbt envoie des informations sur les transformations directement en tant que commentaires SQL

En analysant les requêtes SQL à l’aide d’expressions régulières, vous pouvez donc récupérer quelques informations de base sur une transformation dbt. Mais à l’aide des labels, nous allez beaucoup plus loin en granularité et donc en intérêt !

🏷️ Ajouter des Labels pour Un Suivi Plus Fin

Pour enrichir le suivi des coûts, je vais vous montrer comment ajouter des labels supplémentaires dans dbt afin de mieux catégoriser les transformations. Cela peut inclure :

• Le nom du projet data
• Le type de transformation (modèle de fait, agrégation, etc.)
• L’environnement (dev, prod, staging)
• L’utilisateur ou l’équipe responsable

💡 Comment configurer les labels dans dbt ?

Dans la documentation officielle de dbt, on trouve l’information suivante à propos de la gestion commentaires de requêtes [2] :​

A string to inject as a comment in each query that dbt runs against your database. This comment can attribute SQL statements to specific dbt resources like models and tests.

The query-comment configuration can also call a macro that returns a string.

Donc, à l’aide d’une macro dbt, on peut altérer le comportement du commentaire généré dans chaque requête (voir même, selon la requête !).

Là où ça devient très intéressant pour mettre en place un peu de monitoring, c’est que si vous utilisez BigQuery alors il est possible d’activer une fonctionnalité qui permet de prendre la main sur les labels envoyés en tant que meta-données de la requête.

# dbt_project.yml
query-comment:
  comment: '{{ query_comment(node) }}'
  job-label: true

Il nous reste à créer notre macro dbt (ici appelée query_comment() et qui accepte en argument un node et dans lequel tout un tas de variables de contexte sont accessibles.

J’ai eu la chance de croiser Axel Thevenot qui m’a partagé sa version que j’ai à peine modifiée :

# macros/tools/bq/query_comment.sql
# Kudos Axel 😘

{% macro query_comment(node) %}

    {%- set comment_dict = {} -%}

    {%- if node is not none -%}
        {% set original_dict = node.config.get('labels', {}) %}
        {% set cleaned_dict = {} %}

        {% for key, value in original_dict.items() %}
            {% set new_key = key.lstrip('+') %}
            {% do cleaned_dict.update({new_key: value}) %}
        {% endfor %}

        {%- do comment_dict.update(
            dbt_resource_type=node.resource_type,
            dbt_database=node.database,
            dbt_schema=node.schema,
            dbt_name=node.name,
            dbt_alias=node.alias,
            dbt_materialized=node.config.materialized,
            dbt_incremental_strategy=node.config.incremental_strategy,
            **cleaned_dict
        ) -%}

    {%- else %}
        {%- do comment_dict.update(node_id='internal') -%}
    {%- endif -%}

    {%- do return(tojson(comment_dict)) %}
{% endmacro %}

Pour vous résumer le fonctionnement de cette macro :

1. labels est un dictionnaire de type “key” : “value”
2. lors de l’exécution du modèle (en run, en build, en test, etc…), s’il existe une description complémentaire de labels pour le modèle concerné dans le projet dbt, ALORS ces données seront ajoutées/mergées.

D’accord, mais jusque là tu n’as ajouté aucun label, si ? 🤔

Très juste ! Voici un exemple concret dans le dossier dbt_project.yml pour plus de facilité :

models:
  article_1:
    marts:
      +labels:
        type: marts
      dema1n:
        +labels:
          project: dema1n
          description: "DEMA1N est une plateforme gratuite [...]"
          frequency: "daily"
          whatever_you_need: ["a", "b", "c", "d"]
          est_requis: true

Une fois en place, ces labels apparaissent dans BigQuery et vous permettent donc reporting plus précis (filtres par projet/sous-projet, type de modèle, criticité du modèle, etc…)

📈 Requêtes SQL Pour Suivre les Dépenses

Une fois les labels mis en place, voici quelques requêtes utiles pour créer un dashboard de suivi des coûts dbt sur BigQuery :

1️⃣ Coût total par mois

CREATE OR REPLACE VIEW `ton_projet.ton_dataset.data_costs_by_month` AS
SELECT 
    TIMESTAMP_TRUNC(start_time, MONTH) AS month,
    SUM(cost) AS total_cost
FROM `<gcp_project_id>.<dataset_id>.data_costs`
GROUP BY month
ORDER BY month DESC

2️⃣ Coût par modèle dbt

CREATE OR REPLACE VIEW `ton_projet.ton_dataset.data_costs_by_model` AS
SELECT 
    JSON_VALUE(labels, "$.profile_name") AS dbt_model,
    SUM(cost) AS total_cost
FROM `<gcp_project_id>.<dataset_id>.data_costs`
WHERE JSON_VALUE(labels, "$.profile_name") IS NOT NULL  -- Exclut les NULL
GROUP BY dbt_model
ORDER BY total_cost DESC

3️⃣ Coût par environnement (dev, prod)

CREATE OR REPLACE VIEW `ton_projet.ton_dataset.data_costs_by_environment` AS
SELECT 
    JSON_VALUE(labels, "$.target_name") AS environment,
    SUM(cost) AS total_cost
FROM `<gcp_project_id>.<dataset_id>.data_costs`
WHERE JSON_VALUE(labels, "$.target_name") IS NOT NULL  -- Exclut les NULL
GROUP BY environment
ORDER BY total_cost DESC

Ces analyses permettent d’identifier rapidement les transformations les plus coûteuses et de lister celles qui nécessitent des optimisations.

🚀 Conclusion

Avec une bonne utilisation des tables système de Google BigQuery et l’ajout de labels dans dbt, il est possible de suivre précisément le coût des transformations et d’optimiser ses pipelines de données.

🔹 Étapes clés à retenir :
✅ Récupérer les données depuis INFORMATION_SCHEMA.JOBS
✅ Configurer dbt pour ajouter des labels pertinents
✅ Construire un dashboard pour surveiller les coûts et ajuster les performances

🔹 Références utilisées dans l’article :

• [1] Modèles de pricing de Bigquery : https://cloud.google.com/bigquery/pricing?hl=fr#analysis_pricing_models
• [2] Activer l’ajout de labels dans dbt : https://docs.getdbt.com/reference/project-configs/query-comment#bigquery-include-query-comment-items-as-job-labels

💡 Et toi, comment tu contrôles tes coûts BigQuery ?

Partage tes astuces en commentaire ! 🚀