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

BigQuery facture a la requete selon les octets lus. Un Data Engineer Senior sait reduire les couts de 90% avec le bon partitionnement et les bonnes pratiques — c est ce qu on evalue en entretien.

1Partitionnement et clustering

Question discriminante

Quelle est la difference entre partitionnement et clustering dans BigQuery ? Comment choisissez-vous l un, l autre, ou les deux ?

-- Table partitionnee par date ET clusterisee
CREATE TABLE `projet.dataset.events`
PARTITION BY DATE(event_date)
CLUSTER BY user_id, event_type
OPTIONS (require_partition_filter = TRUE)  -- force le filtre de partition
AS SELECT * FROM source_table;

-- Partition Expiration : supprimer auto les vieilles partitions
ALTER TABLE events
SET OPTIONS (partition_expiration_days = 365);

-- Cost estimation avant execution
EXPLAIN SELECT * FROM events WHERE event_date = '2025-01-01';
-- Voir les bytes processed estimes

Partitionnement — divise physiquement la table en segments. BigQuery ne lit que les partitions filtrees. Reduction de couts jusqu a 99%
Clustering — organise les blocs de donnees a l interieur d une partition. Complement du partitionnement pour les filtres multi-colonnes
Quand les deux — tables >100GB avec requetes qui filtrent toujours sur la date ET sur d autres colonnes

2INFORMATION_SCHEMA : audit et monitoring

-- Top 10 requetes les plus couteuses du mois
SELECT
    user_email,
    query,
    total_bytes_billed / POW(1024, 4) AS tb_billed,
    total_bytes_billed / POW(1024, 4) * 6.25 AS estimated_cost_usd,
    creation_time
FROM `region-eu`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE DATE(creation_time) >= DATE_SUB(CURRENT_DATE, INTERVAL 30 DAY)
    AND statement_type = 'SELECT'
ORDER BY total_bytes_billed DESC
LIMIT 10;

-- Taille de chaque table dans un dataset
SELECT table_id,
       row_count,
       ROUND(size_bytes / POW(1024, 3), 2) AS size_gb
FROM `dataset`.__TABLES__
ORDER BY size_bytes DESC;

3Maitrise des couts

SELECT * est interdit — specifier toujours les colonnes necessaires. BigQuery facture les colonnes lues, pas les lignes
require_partition_filter — forcer l utilisation d un filtre de partition pour eviter les full scans accidentels
Materialized Views — precalculer les aggregations couteuses. Refresh automatique et incremental
Flat rate vs on-demand — flat rate previsible pour les gros volumes, on-demand pour les charges variables
BI Engine — cache en memoire pour les requetes repetitives (Looker Studio, Tableau)

4BigQuery ML : ML directement en SQL

-- Creer un modele de regression logistique en SQL
CREATE OR REPLACE MODEL `dataset.churn_model`
OPTIONS(model_type='logistic_reg', input_label_cols=['churned']) AS
SELECT age, nb_purchases, last_purchase_days, average_basket, churned
FROM `dataset.customers_features`;

-- Evaluer
SELECT * FROM ML.EVALUATE(MODEL `dataset.churn_model`);

-- Predire
SELECT customer_id, predicted_churned, predicted_churned_probs
FROM ML.PREDICT(MODEL `dataset.churn_model`,
    (SELECT * FROM `dataset.new_customers`));

5BigQuery Omni et Biglake

BigQuery Omni — executer des requetes BigQuery directement sur des donnees dans AWS S3 ou Azure Blob Storage, sans les deplacer
BigLake — tables BigQuery sur des fichiers stockes dans GCS, S3 ou ADLS. Securite centralisee (column-level security) sur des donnees externes
Biglake Metastore — compatible Apache Iceberg, interoperable avec Spark, Hive, Trino

6Bonnes pratiques en production

Utiliser _PARTITIONDATE — pseudo-colonne de partition, plus performante que DATE(timestamp_col)
Eviter les CROSS JOIN — dans BigQuery, un CROSS JOIN sur deux tables moyennes peut scanner des teraoctets
Denormalisation deliberee — BigQuery fonctionne mieux avec des tables larges et denormalisees (colonnes imbriquees STRUCT/ARRAY) qu avec des jointures multiples
Colonnes imbriquees — STRUCT et ARRAY permettent de stocker des donnees hierarchiques sans jointures couteuses

7Grille par niveau

Niveau	Maitrise	Signal GO	NO-GO
Confirme	Partitionnement, clustering, INFORMATION_SCHEMA	Configure partitionnement + clustering, lit INFORMATION_SCHEMA pour les couts	Fait des SELECT * sans WHERE sur les partitions
Senior	BigQuery ML, Omni/BigLake, optimisation avancee	A utilise BigQuery ML pour un modele simple, connait BigLake, a optimise les couts de 50%+	Ne sait pas pourquoi le partitionnement reduit les couts

Vous recrutez un profil data ?

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

Tester gratuitement Reserver un appel