Accueil›Blog›Test technique BigQuery avance : partitionnement, INFORMATION_SCHEMA, couts

Guide recrutement data

Test technique BigQuery avance : partitionnement, INFORMATION_SCHEMA, couts

BigQuery facture a la requete selon les octets lus. Un Data Engineer Senior s...

Data Builder·Juin 2025·7 min·Data Engineer · Analytics Engineer

Sommaire

Partitionnement et clustering
INFORMATION_SCHEMA
Maitrise des couts
BigQuery ML
BigQuery Omni et Biglake
Bonnes pratiques
Grille

1Partitionnement et clustering

Question discriminante

Quelle est la différence entre partitionnement et clustering dans BigQuery ? Comment choisir ?

Partitionnement — divise la table par colonnes date/timestamp ou par ingestion time. BigQuery scanne uniquement les partitions filtrées. Réduction des coûts directe
Clustering — trie les données dans chaque partition selon 1-4 colonnes. Réduit le volume scanné pour les filtres et les agrégations. Gratuit (pas de surcoût)
Combo optimal — PARTITION BY date + CLUSTER BY (region, product_id). La partition réduit les partitions scannées, le clustering réduit les octets dans la partition
Partition pruning — fonctionne uniquement si le filtre porte sur la colonne de partition. WHERE DATE(created_at) = '2025-01-01' active le pruning. WHERE EXTRACT(YEAR FROM created_at) = 2025 ne l'active pas

-- Table partitionnée et clusterisée
CREATE TABLE analytics.fct_orders
PARTITION BY DATE(order_date)
CLUSTER BY region, product_category
AS
SELECT order_id, customer_id, amount, region, product_category, order_date
FROM raw.orders;

-- Vérifier l'efficacité du partitionnement avec INFORMATION_SCHEMA
SELECT
    table_name,
    partition_id,
    total_rows,
    total_logical_bytes / POW(1024, 3) AS gb_size
FROM analytics.INFORMATION_SCHEMA.PARTITIONS
WHERE table_name = 'fct_orders'
ORDER BY partition_id DESC
LIMIT 30;

2INFORMATION_SCHEMA : audit et monitoring

Question discriminante

Comment utilisez-vous INFORMATION_SCHEMA pour monitorer les coûts et les performances BigQuery ?

-- Top 10 requêtes les plus coûteuses des dernières 24h
SELECT
    user_email,
    job_id,
    ROUND(total_bytes_billed / POW(1024, 4), 4) AS tb_billed,
    ROUND(total_bytes_billed / POW(1024, 4) * 5, 4) AS cost_usd,
    total_slot_ms / 1000 AS slot_seconds,
    SUBSTR(query, 1, 200) AS query_preview,
    creation_time
FROM `region-eu`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 24 HOUR)
  AND job_type = 'QUERY'
  AND state = 'DONE'
ORDER BY total_bytes_billed DESC
LIMIT 10;

-- Tables les plus grosses du dataset
SELECT table_name, row_count, 
       ROUND(size_bytes / POW(1024, 3), 2) AS size_gb
FROM analytics.INFORMATION_SCHEMA.TABLE_STORAGE
ORDER BY size_bytes DESC;

JOBS_BY_PROJECT — toutes les requêtes exécutées : user, durée, octets scannés, coût estimé
TABLE_STORAGE — taille des tables, nombre de lignes, date de dernière modification
PARTITIONS — métadonnées sur chaque partition : lignes, taille, dernière modification
RESERVATIONS — monitoring des slots alloués (en mode Capacity)

3Maîtrise des coûts BigQuery

Question discriminante

Quelles sont vos pratiques pour contrôler les coûts BigQuery en production ?

SELECT * interdit — BigQuery facture sur les colonnes scannées. Toujours sélectionner uniquement les colonnes nécessaires
Cost controls — configurer un maximum de 1TB par requête (custom quotas) pour les développeurs
Partitioning obligatoire — toute table >10GB sans partitionnement est un anti-pattern
BI Engine — cache en mémoire pour les dashboards Looker Studio. Évite de rescanner la table à chaque actualisation
Slots vs on-demand — on-demand : 5$/TB scanné. Slots réservés : coût fixe mensuel, rentable à >10TB/jour de requêtes

-- Quotas par utilisateur (Cloud Console → IAM → Quotas)
-- Ou via gcloud :
gcloud alpha services quota update --consumer=project:mon-projet     --service=bigquery.googleapis.com     --metric=bigquery.googleapis.com/quota/query/usage     --value=1099511627776  # 1TB max par requête

-- Dry run : estimer le coût avant d'exécuter
bq query --dry_run --use_legacy_sql=false     "SELECT * FROM analytics.fct_orders WHERE order_date = '2025-01-01'"
# → This query will process 245 MB when run.

4BigQuery ML : ML directement en SQL

Question discriminante

Quand utilisez-vous BigQuery ML plutôt qu'un framework Python (sklearn, XGBoost) ?

-- Entraîner un modèle de régression logistique directement en SQL
CREATE OR REPLACE MODEL analytics.churn_model
OPTIONS(
    model_type='LOGISTIC_REG',
    input_label_cols=['churned'],
    max_iterations=20,
    learn_rate=0.1
) AS
SELECT
    nb_commandes_30j,
    montant_total_90j,
    jours_depuis_derniere_connexion,
    segment,
    churned
FROM analytics.features_churn
WHERE split = 'TRAIN';

-- Scorer les clients actuels
SELECT customer_id, predicted_churned, predicted_churned_probs
FROM ML.PREDICT(MODEL analytics.churn_model,
    (SELECT * FROM analytics.features_churn WHERE split = 'PREDICT'));

-- Évaluer le modèle
SELECT * FROM ML.EVALUATE(MODEL analytics.churn_model);

Quand BQML — équipes SQL-first sans infra Python, prototypage rapide, données déjà dans BigQuery
Quand Python — modèles complexes (DNN, transformers), feature engineering avancé, plus de contrôle sur l'entraînement
Modèles disponibles — régression linéaire/logistique, XGBoost, DNN, k-means, ARIMA, import de modèles TensorFlow/PyTorch

5BigQuery Omni et BigLake

Question discriminante

Qu'est-ce que BigLake ? Dans quel cas l'utilisez-vous ?

BigLake — requêter des fichiers Parquet/CSV dans GCS, AWS S3 ou Azure Blob comme des tables BigQuery, avec les mêmes permissions Unity Catalog-like et le fine-grained access
BigQuery Omni — exécuter des requêtes BigQuery sur AWS/Azure sans déplacer les données. Utile pour le multi-cloud
Connexions externes — connecter BigQuery à des APIs externes ou à des fonctions Cloud Run pour enrichir les données
Avantage BigLake — gérer les permissions d'accès aux fichiers S3/GCS via BigQuery IAM plutôt que des ACLs stockage complexes

6Bonnes pratiques en production

Question discriminante

Quelles sont vos règles non-négociables pour BigQuery en production ?

Partitionnement systématique — toute table >5GB partitionnée par date
Clustering sur les colonnes de filtre — les colonnes les plus souvent utilisées dans WHERE et GROUP BY
Pas de SELECT * — politique de revue de code qui bloque les PR avec SELECT *
Labels sur les jobs — labelliser les requêtes par équipe et projet pour l'attribution des coûts
Monitoring INFORMATION_SCHEMA — dashboard de coûts hebdomadaire par utilisateur/équipe
Dataset par environnement — analytics_dev, analytics_staging, analytics_prod. Pas de dev sur le dataset de prod

BI Engine - cache en memoire pour les dashboards Looker Studio. Evite de rescanner la table a chaque actualisation. Facturation au Go de memoire reserve
Slots vs on-demand - on-demand : 5 USD/TB scanne. Slots reserves : cout fixe mensuel, rentable a plus de 10TB/j de requetes
BigLake - requeter des fichiers Parquet dans GCS, AWS S3 ou Azure Blob comme des tables BigQuery, avec les memes permissions et le fine-grained access
BigQuery ML - BQML : quand equipes SQL-first sans infra Python, prototypage rapide. Python : modeles complexes (DNN, transformers), feature engineering avance
Labels sur les jobs - labelliser les requetes par equipe et projet pour l attribution des couts. Configurer dans les settings BigQuery ou via les APIs

7Grille par niveau

Niveau	Maîtrise	Signal GO	NO-GO
Confirmé	Partitionnement, clustering, contrôle des coûts basique	Configure systématiquement le partitionnement, connaît l'impact du SELECT *, utilise INFORMATION_SCHEMA	Crée des tables sans partitionnement, fait des SELECT * en production
Senior	BQML, BigLake, optimisation slots, architecture multi-projet	A utilisé BQML pour du ML en SQL, configure des slots réservés, gère les coûts par équipe via labels	Ne sait pas la différence entre on-demand et slots réservés

1Partitioning and clustering

Discriminating question

What is the difference between partitioning and clustering in BigQuery? How do you choose?

Partitioning — splits the table by date/timestamp columns or by ingestion time. BigQuery only scans filtered partitions. Direct cost reduction
Clustering — sorts data within each partition by 1-4 columns. Reduces scanned volume for filters and aggregations. Free (no extra cost)
Optimal combo — PARTITION BY date + CLUSTER BY (region, product_id). Partitioning reduces scanned partitions, clustering reduces bytes within the partition
Partition pruning — only works if the filter applies to the partition column. WHERE DATE(created_at) = '2025-01-01' enables pruning. WHERE EXTRACT(YEAR FROM created_at) = 2025 does not

-- Partitioned and clustered table
CREATE TABLE analytics.fct_orders
PARTITION BY DATE(order_date)
CLUSTER BY region, product_category
AS
SELECT order_id, customer_id, amount, region, product_category, order_date
FROM raw.orders;

-- Check partitioning efficiency with INFORMATION_SCHEMA
SELECT
    table_name,
    partition_id,
    total_rows,
    total_logical_bytes / POW(1024, 3) AS gb_size
FROM analytics.INFORMATION_SCHEMA.PARTITIONS
WHERE table_name = 'fct_orders'
ORDER BY partition_id DESC
LIMIT 30;

2INFORMATION_SCHEMA: audit and monitoring

Discriminating question

How do you use INFORMATION_SCHEMA to monitor BigQuery costs and performance?

-- Top 10 most expensive queries in the last 24h
SELECT
    user_email,
    job_id,
    ROUND(total_bytes_billed / POW(1024, 4), 4) AS tb_billed,
    ROUND(total_bytes_billed / POW(1024, 4) * 5, 4) AS cost_usd,
    total_slot_ms / 1000 AS slot_seconds,
    SUBSTR(query, 1, 200) AS query_preview,
    creation_time
FROM `region-eu`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 24 HOUR)
  AND job_type = 'QUERY'
  AND state = 'DONE'
ORDER BY total_bytes_billed DESC
LIMIT 10;

-- Largest tables in the dataset
SELECT table_name, row_count, 
       ROUND(size_bytes / POW(1024, 3), 2) AS size_gb
FROM analytics.INFORMATION_SCHEMA.TABLE_STORAGE
ORDER BY size_bytes DESC;

JOBS_BY_PROJECT — all executed queries: user, duration, bytes scanned, estimated cost
TABLE_STORAGE — table size, row count, last modification date
PARTITIONS — metadata for each partition: rows, size, last modification
RESERVATIONS — monitoring of allocated slots (in Capacity mode)

3BigQuery cost control

Discriminating question

What are your practices for controlling BigQuery costs in production?

SELECT * forbidden — BigQuery charges based on scanned columns. Always select only the necessary columns
Cost controls — configure a maximum of 1TB per query (custom quotas) for developers
Mandatory partitioning — any table >10GB without partitioning is an anti-pattern
BI Engine — in-memory cache for Looker Studio dashboards. Avoids rescanning the table on every refresh
Slots vs on-demand — on-demand: $5/TB scanned. Reserved slots: fixed monthly cost, cost-effective above >10TB/day of queries

-- Quotas per user (Cloud Console → IAM → Quotas)
-- Or via gcloud:
gcloud alpha services quota update --consumer=project:my-project     --service=bigquery.googleapis.com     --metric=bigquery.googleapis.com/quota/query/usage     --value=1099511627776  # 1TB max per query

-- Dry run: estimate cost before executing
bq query --dry_run --use_legacy_sql=false     "SELECT * FROM analytics.fct_orders WHERE order_date = '2025-01-01'"
# → This query will process 245 MB when run.

4BigQuery ML: ML directly in SQL

Discriminating question

When do you use BigQuery ML rather than a Python framework (sklearn, XGBoost)?

-- Train a logistic regression model directly in SQL
CREATE OR REPLACE MODEL analytics.churn_model
OPTIONS(
    model_type='LOGISTIC_REG',
    input_label_cols=['churned'],
    max_iterations=20,
    learn_rate=0.1
) AS
SELECT
    nb_commandes_30j,
    montant_total_90j,
    jours_depuis_derniere_connexion,
    segment,
    churned
FROM analytics.features_churn
WHERE split = 'TRAIN';

-- Score current customers
SELECT customer_id, predicted_churned, predicted_churned_probs
FROM ML.PREDICT(MODEL analytics.churn_model,
    (SELECT * FROM analytics.features_churn WHERE split = 'PREDICT'));

-- Evaluate the model
SELECT * FROM ML.EVALUATE(MODEL analytics.churn_model);

When BQML — SQL-first teams without Python infrastructure, rapid prototyping, data already in BigQuery
When Python — complex models (DNN, transformers), advanced feature engineering, more control over training
Available models — linear/logistic regression, XGBoost, DNN, k-means, ARIMA, import of TensorFlow/PyTorch models

5BigQuery Omni and BigLake

Discriminating question

What is BigLake? In which cases do you use it?

BigLake — query Parquet/CSV files in GCS, AWS S3 or Azure Blob as BigQuery tables, with the same Unity Catalog-like permissions and fine-grained access
BigQuery Omni — run BigQuery queries on AWS/Azure without moving data. Useful for multi-cloud
External connections — connect BigQuery to external APIs or Cloud Run functions to enrich data
BigLake advantage — manage access permissions to S3/GCS files via BigQuery IAM rather than complex storage ACLs

6Production best practices

Discriminating question

What are your non-negotiable rules for BigQuery in production?

Systematic partitioning — every table >5GB partitioned by date
Clustering on filter columns — columns most frequently used in WHERE and GROUP BY
No SELECT * — code review policy that blocks PRs with SELECT *
Labels on jobs — label queries by team and project for cost attribution
INFORMATION_SCHEMA monitoring — weekly cost dashboard per user/team
Dataset per environment — analytics_dev, analytics_staging, analytics_prod. No dev on the prod dataset

BI Engine - in-memory cache for Looker Studio dashboards. Avoids rescanning the table on every refresh. Billed per GB of reserved memory
Slots vs on-demand - on-demand: $5 USD/TB scanned. Reserved slots: fixed monthly cost, cost-effective above 10TB/day of queries
BigLake - query Parquet files in GCS, AWS S3 or Azure Blob as BigQuery tables, with the same permissions and fine-grained access
BigQuery ML - BQML: when SQL-first teams without Python infrastructure, rapid prototyping. Python: complex models (DNN, transformers), advanced feature engineering
Labels on jobs - label queries by team and project for cost attribution. Configure in BigQuery settings or via APIs

7Level grid

Level	Mastery	GO signal	NO-GO
Mid-level	Partitioning, clustering, basic cost control	Systematically configures partitioning, knows the impact of SELECT *, uses INFORMATION_SCHEMA	Creates tables without partitioning, uses SELECT * in production
Senior	BQML, BigLake, slots optimization, multi-project architecture	Has used BQML for ML in SQL, configures reserved slots, manages costs per team via labels	Does not know the difference between on-demand and reserved slots

Vous recrutez un profil data ?

Premier entretien gratuit. Rapport GO/NO-GO sous 48h.

Tester gratuitement Reserver un appel