Vues matérialisées BigQuery

Les vues matérialisées précalculent les résultats d’agrégation et s’actualisent de manière incrémentale. Les requêtes ultérieures lisent depuis le résultat précalculé plutôt que de rescanner la table de base.

Les vues matérialisées BigQuery prennent en charge la réécriture transparente des requêtes : l’optimiseur réécrit automatiquement les requêtes sur la table de base pour utiliser la VM lorsqu’il détecte une correspondance. Aucune modification des requêtes des consommateurs n’est nécessaire.

Créer une vue matérialisée

Le pattern de base : définissez l’agrégation à précalculer, configurez le comportement d’actualisation, et définissez la tolérance à la latence des données.

CREATE MATERIALIZED VIEW `project.dataset.mv_daily_revenue`
OPTIONS (
  enable_refresh = true,
  refresh_interval_minutes = 30,
  max_staleness = INTERVAL '4' HOUR
)
AS
SELECT
  DATE(transaction_time) AS transaction_date,
  product_category,
  SUM(amount) AS total_revenue,
  COUNT(*) AS transaction_count
FROM `project.dataset.transactions`
GROUP BY 1, 2;

Trois options de configuration contrôlent le comportement :

enable_refresh : Active l’actualisation incrémentale automatique. BigQuery suit les lignes modifiées dans la table de base depuis la dernière actualisation et ne recalcule que celles-ci. C’est fondamentalement différent d’une requête planifiée qui recalcule tout.

refresh_interval_minutes : La fréquence à laquelle BigQuery vérifie les changements. Le régler à 30 signifie que la VM s’actualise au plus toutes les 30 minutes. Des intervalles plus courts donnent des données plus fraîches mais consomment plus de compute.

max_staleness : C’est le levier de réglage clé. Il indique à BigQuery de combien les données de la VM peuvent être en retard avant qu’une actualisation soit obligatoire. Définir INTERVAL '4' HOUR signifie “je suis à l’aise avec des données vieilles de 4 heures au maximum”. Cela permet à l’optimiseur d’utiliser la VM plus agressivement et réduit la fréquence d’actualisation. Pour les tableaux de bord en temps réel, omettez-le (la valeur par défaut est toujours frais). Pour les rapports hebdomadaires, réglez-le à plusieurs jours.

Réécriture transparente des requêtes

C’est là que les VM diffèrent de la simple création d’une table de résumé. Quand vous interrogez la table de base :

SELECT
  DATE(transaction_time) AS transaction_date,
  SUM(amount) AS total_revenue
FROM `project.dataset.transactions`
WHERE DATE(transaction_time) >= '2025-01-01'
GROUP BY 1;

BigQuery reconnaît que cette requête peut être satisfaite depuis mv_daily_revenue et la réécrit automatiquement. L’utilisateur ne sait pas et n’a pas besoin de savoir qu’une VM existe. Le planificateur de requêtes gère le routage.

Cela fonctionne parce que BigQuery peut vérifier que le résultat de la VM est équivalent à (ou dérivable de) la requête originale. Il gère les rollups d’agrégation : si la VM regroupe par product_category et que votre requête ne le fait pas, BigQuery peut agréger sur les catégories depuis le résultat de la VM plutôt que depuis la table de base.

Ce que les VM peuvent et ne peuvent pas faire

Pris en charge : Les agrégations simples (SUM, COUNT, AVG, MIN, MAX, COUNT DISTINCT, HLL_COUNT.INIT, APPROX_COUNT_DISTINCT), GROUP BY, les filtres WHERE, les INNER JOIN vers des tables de dimension, et les UNION ALL de tables.

Non pris en charge : ORDER BY, LIMIT, HAVING, les sous-requêtes dans la clause SELECT, les fonctions non déterministes comme CURRENT_TIMESTAMP(), les fonctions de fenêtrage, les outer joins et les cross joins.

La restriction sur les fonctions non déterministes est celle qui surprend le plus souvent. Si votre agrégation inclut CURRENT_TIMESTAMP() pour une colonne “dernière actualisation”, BigQuery ne peut pas faire d’actualisation incrémentale car la valeur change à chaque évaluation. La VM revient à un recalcul complet, ce qui en annule l’intérêt.

Gardez les VM simples : des agrégations sur des tables de base filtrées. Les transformations complexes appartiennent à vos modèles dbt.

La fraîcheur des données comme décision de conception

Le comportement par défaut (sans max_staleness) signifie que la VM doit être parfaitement à jour avant que BigQuery ne l’utilise. En pratique, cela entraîne des actualisations fréquentes et des coûts de compute plus élevés pour maintenir la VM à jour.

La plupart des consommateurs de tableaux de bord peuvent tolérer une certaine latence des données. Posez la question explicitement : ce rapport a-t-il besoin de données des 5 dernières minutes, ou des données des 2 dernières heures sont-elles suffisantes ? La réponse détermine votre paramètre max_staleness, qui détermine à son tour votre coût d’actualisation.

Un point de départ raisonnable selon les cas d’usage :

Impact sur les performances

Google rapporte que des requêtes scannant 500 Go ont été réduites à 2,5 Mo avec des VM bien conçues — une réduction de 200 000×. Améliorations typiques :

• Accélération des requêtes : 5 à 100× pour les agrégations complexes
• Octets scannés : réduits selon le ratio des données brutes aux données agrégées
• Consommation de slots : réduction proportionnelle car moins de données signifie moins de compute

Le coût d’actualisation est la contrepartie. Chaque actualisation scanne les données modifiées dans la table de base et recalcule les agrégations affectées. Pour les tables à ajout dominant (comme les logs d’événements), les actualisations incrémentielles sont peu coûteuses car seules les nouvelles lignes sont traitées. Pour les tables avec des mises à jour généralisées, les actualisations approchent un recalcul complet.

Limites et contraintes

BigQuery impose des limites strictes :

• 20 VM par dataset
• 100 VM par projet
• La VM et la table de base doivent être dans le même dataset
• La table de base doit être une table BigQuery native (pas externe ou fédérée)

La limite de 20 par dataset est celle que la plupart des équipes atteignent en premier. Elle vous oblige à être intentionnel sur les agrégations qui méritent la matérialisation. Priorisez les VM pour les requêtes qui s’exécutent fréquemment sur de grandes tables avec des patterns d’agrégation stables.

VM vs modèles incrémentiels dbt

Il existe un chevauchement entre les vues matérialisées et les modèles incrémentiels dbt. Les deux précalculent des résultats pour éviter des scans complets répétés. La distinction :

• Les VM sont gérées par BigQuery, s’actualisent automatiquement, et prennent en charge la réécriture transparente des requêtes. Elles sont idéales pour les agrégations orientées tableaux de bord où les consommateurs interrogent la table de base directement.
• Les modèles incrémentiels dbt sont gérés par votre pipeline, offrent une flexibilité SQL complète (joins, fonctions de fenêtrage, logique complexe), et produisent des tables régulières. Ils sont idéaux pour les transformations dans votre couche de modélisation.

Les VM fonctionnent bien comme couche d’optimisation finale : après que les modèles dbt produisent des tables propres et bien structurées, une VM par-dessus accélère les requêtes des tableaux de bord sans modifier le code Looker ou Tableau.

Créer des VM avec dbt

dbt prend en charge les vues matérialisées comme stratégie de matérialisation (introduite dans dbt 1.7 pour BigQuery) :

{{ config(
    materialized='materialized_view',
    on_configuration_change='apply'
) }}

SELECT
  DATE(transaction_time) AS transaction_date,
  product_category,
  SUM(amount) AS total_revenue,
  COUNT(*) AS transaction_count
FROM {{ ref('fct_transactions') }}
GROUP BY 1, 2

Le paramètre on_configuration_change='apply' indique à dbt de mettre à jour la définition de la VM si le SQL change. Sans lui, dbt ne détectera pas ni n’appliquera la dérive de configuration.

Les mêmes restrictions s’appliquent : pas de fonctionnalités SQL complexes, pas de fonctions non déterministes, pas de fonctions de fenêtrage. Si votre modèle dbt a besoin de l’un de ces éléments, utilisez la matérialisation incrémentale ou table.

Quand utiliser les vues matérialisées

Les VM sont appropriées quand :

1. La même agrégation s’exécute de manière répétée (tableaux de bord, rapports, endpoints API)
2. La table de base est suffisamment grande pour que les coûts de scan soient significatifs
3. L’agrégation est suffisamment simple pour respecter les contraintes des VM
4. Les consommateurs peuvent tolérer une certaine latence des données
5. Les limites de 20 par dataset et 100 par projet ne sont pas dépassées

Quand les agrégations sont complexes, nécessitent des joins vers plusieurs tables, ou utilisent des fonctions de fenêtrage, utilisez plutôt un modèle dbt. Les VM sont optimisées pour le cas simple et haute fréquence. Le calcul ROI du modèle de coût est : coût d’actualisation vs coût cumulatif de scan de l’agrégation directe.