Module d'onboarding — BigQuery

Contenu pédagogique produit par Bill (QA). Destiné à onboarding.fastodata.com.
Public : ingénieur data, SQL OK. Format : théorie d'abord → exercices → quiz corrigé.
Contexte équipe : entrepôt central, socle de dbt et de l'orchestration. Module fondamental (à faire avant dbt).
Durée estimée : ~3-4 h.

Objectifs pédagogiques

À la fin, l'apprenant sait :

1. Expliquer l'architecture serverless de BigQuery (stockage/calcul séparés, slots).
2. Organiser project → dataset → table ; types de tables (native, externe, vue, vue matérialisée).
3. Maîtriser le coût : partitionnement, clustering, --dry-run, éviter SELECT *.
4. Écrire des requêtes performantes et lire le query plan.
5. Charger/exporter la donnée (batch, streaming, fédéré).
6. Gérer accès et bonnes pratiques FinOps.

Module 1 — Modèle mental & architecture

BigQuery est un entrepôt serverless : pas de cluster à gérer. Deux ressources découplées :

• Stockage en colonnes (Capacitor), facturé au Go stocké.
• Calcul en slots (unités de parallélisme), facturé soit à la donnée scannée (on-demand), soit en capacité réservée (slots).

Hiérarchie : Project → Dataset → Table/View. Le dataset porte la localisation (région, ex. EU) et les ACL.

⚠️ Le levier de coût n°1 = la quantité de données scannées par requête (mode on-demand). Réduire les colonnes et les partitions lues = payer moins.

Module 2 — Tables, partitions, clustering

Types de tables :

• native : stockée dans BigQuery.
• externe : pointe des fichiers (GCS, Sheets…) — pas de stockage BQ, perfs moindres.
• vue : requête sauvegardée, recalculée à chaque appel.
• vue matérialisée : résultat pré-calculé et rafraîchi auto (cache intelligent).

Partitionnement (découpe physique d'une table) :

CREATE TABLE ventes.orders (order_id INT64, order_date DATE, amount NUMERIC)
PARTITION BY order_date;

Une requête filtrée sur order_date ne scanne que les partitions utiles (= partition pruning) → coût ↓↓. Partitions courantes : par date/timestamp, par ingestion _PARTITIONTIME, ou par range d'entier.

Clustering (tri physique dans la partition, jusqu'à 4 colonnes) :

CREATE TABLE ventes.orders (...) PARTITION BY order_date CLUSTER BY customer_id;

Accélère filtres/agrégations sur les colonnes de cluster. Partition + cluster = combo standard sur les grosses tables.

Module 3 — Maîtriser le coût (FinOps) — LE module clé

• SELECT * est l'ennemi : BigQuery est colonnaire → scanner toutes les colonnes coûte cher. Ne sélectionne que les colonnes utiles.
• Estimer AVANT d'exécuter : bq query --dry-run ou le badge « This query will process X » dans l'UI. Réflexe systématique.
• Filtrer sur la colonne de partition pour déclencher le pruning.
• Aperçu gratuit : tbl.preview / LIMIT ne réduit PAS la donnée scannée (LIMIT s'applique après le scan). Pour échantillonner sans coût : TABLESAMPLE ou la preview UI.
• Cache : un résultat identique re-servi du cache = gratuit.
• Maximum bytes billed : garde-fou par requête pour éviter une facture surprise.

Le bon réflexe d'équipe : --dry-run → regarder les Go scannés → optimiser (colonnes + partition) → exécuter.

Module 4 — Requêtes performantes

• Approx quand l'exact n'est pas requis : APPROX_COUNT_DISTINCT() >> COUNT(DISTINCT) sur gros volumes.
• Window functions, QUALIFY pour filtrer sur une window sans sous-requête :

SELECT * FROM orders
QUALIFY ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) = 1;

• Évite les jointures explosives ; filtre tôt ; pré-agrège.
• STRUCT / ARRAY (données imbriquées) : BigQuery est fait pour le semi-structuré ; UNNEST() pour aplatir.
• Lire le query plan (onglet Execution details) : repérer les étapes qui scannent/brassent le plus.

Module 5 — Charger & exporter

• Batch load : depuis GCS (CSV/JSON/Avro/Parquet/ORC) via bq load ou l'API. Parquet/Avro = schéma porté, recommandé.
• Streaming : tabledata.insertAll / Storage Write API pour de l'insert temps réel.
• Requêtes fédérées : interroger GCS, Cloud SQL, Sheets sans importer.
• Export : vers GCS (EXPORT DATA OPTIONS(...)).
• Scheduled queries : planifier une requête récurrente (petite orchestration native).

Module 6 — Accès, sécurité, bonnes pratiques

• IAM au niveau project/dataset ; rôles (dataViewer, dataEditor, jobUser…).
• Vues autorisées / row/column-level security pour exposer un sous-ensemble.
• Labels sur jobs/datasets pour le suivi de coût.
• Bonnes pratiques : datasets par domaine + environnement (raw, staging, mart × dev/prod) — cohérent avec les couches dbt ; conventions de nommage ; expiration des tables temporaires.

Exercices

1. Crée une table orders partitionnée par order_date, clusterée par customer_id. Pourquoi ce combo ?
2. Tu dois sommer amount sur janvier 2024. Écris la requête qui scanne le MOINS de données possible. Quel filtre déclenche le pruning ?
3. Avant de lancer une requête sur une table de 4 To, comment estimes-tu le coût ? Quelle commande ?
4. Récupère la dernière commande de chaque client en une requête, sans sous-requête imbriquée. Quel mot-clé ?
5. Pourquoi SELECT * est-il coûteux ici alors qu'il serait neutre sur une base ligne (Postgres) ?

Quiz final corrigé

Q1. Qu'est-ce qui est facturé en mode on-demand ?
→ La donnée scannée par la requête (pas le nombre de lignes retournées). D'où l'importance des colonnes/partitions lues.

Q2. À quoi sert le partitionnement, concrètement sur le coût ?
→ Filtrer sur la colonne de partition ne scanne que les partitions utiles (pruning) → moins de données scannées → moins cher.

Q3. LIMIT 10 réduit-il le coût d'une requête ?
→ Non : le LIMIT s'applique après le scan. Pour limiter le coût : sélectionner moins de colonnes, filtrer sur partition, ou TABLESAMPLE.

Q4. Partitionnement vs clustering ?
→ Partition = découpe physique par valeur (souvent date) → pruning. Clustering = tri physique dans la partition (jusqu'à 4 cols) → filtres/agrégations plus rapides. On combine les deux.

Q5. Comment estimer le coût d'une requête sans l'exécuter ?
→ bq query --dry-run (ou l'estimation « will process X bytes » de l'UI).

Q6. Pourquoi SELECT * coûte cher sur BigQuery ?
→ Stockage colonnaire : * force le scan de toutes les colonnes ; ne lire que les colonnes nécessaires réduit fortement la donnée scannée.

Q7. native vs externe vs vue matérialisée ?
→ native = stockée dans BQ (perf max). externe = pointe des fichiers (pas de stockage BQ, perf moindre). vue matérialisée = résultat pré-calculé rafraîchi auto (cache).

Pour aller plus loin

• Doc BigQuery (pricing, partitioning, BI Engine, BQML).
• Skills équipe : bigquery, bigquery-finops, bigquery-ml, bigquery-schema-validation.
• Lien avec dbt : BQ est la cible d'exécution des modèles ; les conventions de datasets (raw/staging/mart) reflètent les couches dbt.