Accueil›Blog›Test technique Snowflake performance : query profiling, clustering, materialized views

Guide recrutement data

Test technique Snowflake performance : query profiling, clustering, materialized views

Au-delà des bases Snowflake, l optimisation des performances nécessite de maîtriser le Query Profile, le clustering intelligent et les fonctionnalités avancées. En entretien Senior, c est ce qu on évalue.

Data Builder·Juin 2025·6 min de lecture·Data Engineer · Analytics Engineer

Sommaire

Query Profile
Clustering avancé
Materialized Views
Search Optimization Service
Query Acceleration Service
Warehouse tuning
Grille

1Query Profile : diagnostiquer les requêtes lentes

Question discriminante

Comment utilisez-vous le Query Profile Snowflake pour identifier un goulot d étranglement ?

-- Trouver les requêtes lentes des dernières 24h
SELECT
    query_id,
    query_text,
    total_elapsed_time / 1000 AS elapsed_sec,
    bytes_scanned / POW(1024, 3) AS gb_scanned,
    partitions_scanned,
    partitions_total,
    partitions_scanned * 100.0 / NULLIF(partitions_total, 0) AS pct_scanned
FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
WHERE START_TIME >= DATEADD('hour', -24, CURRENT_TIMESTAMP)
  AND EXECUTION_STATUS = 'SUCCESS'
  AND TOTAL_ELAPSED_TIME > 30000  -- > 30 secondes
ORDER BY TOTAL_ELAPSED_TIME DESC
LIMIT 20;

-- Signal de problème : pct_scanned proche de 100%
-- Signifie que le clustering n aide pas

Nodes dans Query Profile — TableScan (pruning effectif ?), Sort (évitable ?), Join (hash vs merge ?)
Partitions pruned — si 100% des partitions sont scannées, le clustering ne sert à rien sur ce filtre

2Clustering avancé : au-delà des bases

Question discriminante

Quand créez-vous un Clustering Key sur plusieurs colonnes ? Comment vérifier son efficacité ?

-- Clustering sur plusieurs colonnes (ordre important)
-- Colonne la plus sélective en premier
ALTER TABLE fct_events
CLUSTER BY (event_date, event_type, user_segment);

-- Vérifier l efficacité du clustering
SELECT SYSTEM$CLUSTERING_INFORMATION('fct_events',
    '(event_date, event_type)');
-- average_depth proche de 1 = bon clustering
-- average_depth > 3 = reclustering nécessaire

-- Clustering automatique (Automatic Clustering)
-- Snowflake maintient le clustering automatiquement
-- Coût: crédit de compute supplémentaire
ALTER TABLE fct_events RESUME RECLUSTER;

3Materialized Views : quand les utiliser

Question discriminante

Dans quel cas une Materialized View Snowflake est-elle préférable à une table dbt ?

Materialized View Snowflake — refresh automatique et transparent quand les données sources changent. Zéro orchestration
Requêtes très fréquentes — une agrégation requêtée 1000 fois par jour bénéficie d une MV
Limites — SQL plus restrictif que les modèles dbt (pas de JOINs complexes, pas de sous-requêtes)
Vs table dbt — la table dbt est plus flexible (tout SQL) mais nécessite un scheduler. La MV est automatique mais plus limitée

4Search Optimization Service

Question discriminante

Qu est-ce que le Search Optimization Service ? Pour quels types de requêtes ?

Search Optimization Service — index de recherche pour les lookups par valeur exacte sur des tables très larges
Cas d usage — requêtes WHERE id = 'abc123' sur une table de 10 milliards de lignes. Sans SOS : scan complet. Avec SOS : lookup direct
Ne pas utiliser pour — les requêtes analytiques avec des agrégations. Le clustering est plus adapté
Coût — coût de stockage supplémentaire pour l index. Rentable si les gains de performance sont importants

5Query Acceleration Service

Question discriminante

Qu est-ce que le Query Acceleration Service ?

QAS — Snowflake lance automatiquement des ressources supplémentaires pour accélérer les parties lentes d une requête (outlier partitions)
Activer — ALTER WAREHOUSE mywh SET ENABLE_QUERY_ACCELERATION = TRUE
Cas d usage — requêtes avec quelques partitions beaucoup plus grandes que les autres (data skew dans les partitions)
Coût — crédits supplémentaires uniquement quand utilisé. Pas de coût fixe

6Warehouse sizing et multi-cluster

Question discriminante

Comment choisissez-vous entre scaler up (plus grand warehouse) et scaler out (multi-cluster) ?

Problème	Solution	Pourquoi
Requête unique trop lente	Scale UP (XS → S → M)	Plus de compute par requête
Trop de requêtes simultanées (queueing)	Scale OUT (multi-cluster)	Plus de warehouses parallèles
Transformation nightly lente	Scale UP temporairement	Finir plus vite, auto-suspend ensuite
Dashboard BI avec 50 users simultanés	Multi-cluster auto-scale	Distribuer la charge

-- Identifier les requetes lentes
SELECT query_id, total_elapsed_time/1000 AS sec,
    bytes_scanned/POW(1024,3) AS gb,
    ROUND(partitions_scanned*100.0/NULLIF(partitions_total,0),1) AS pct_scanned
FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
WHERE START_TIME >= DATEADD('hour',-24,CURRENT_TIMESTAMP)
  AND TOTAL_ELAPSED_TIME > 30000
ORDER BY TOTAL_ELAPSED_TIME DESC LIMIT 20;

-- Verifier clustering
SELECT SYSTEM$CLUSTERING_INFORMATION('fct_events','(event_date, event_type)');
-- average_depth proche de 1 = bon clustering

-- Search Optimization Service
ALTER TABLE customers ADD SEARCH OPTIMIZATION ON EQUALITY(customer_id, email);

-- Resource monitor
CREATE RESOURCE MONITOR monthly_budget
    WITH CREDIT_QUOTA = 500
    TRIGGERS ON 80 PERCENT DO NOTIFY
             ON 100 PERCENT DO SUSPEND;
ALTER WAREHOUSE ETL_WH SET RESOURCE_MONITOR = monthly_budget;

Query Profile UI - identifier les goulots : TableScan (pruning ok ?), Sort (evitable ?), Join (hash vs merge ?). Nodes les plus lents = priorite d optimisation
Materialized Views vs Dynamic Tables - MV : refresh automatique, SQL limite. Dynamic Tables : JOINs complexes, incremental auto, TARGET_LAG configurable
Search Optimization Service - index pour lookups ponctuels (WHERE id = 'abc') sur tables > 10M lignes. Couteux : verifier le ROI avant d activer
Query Acceleration Service - Snowflake lance des ressources supplementaires pour les requetes avec data skew. SET ENABLE_QUERY_ACCELERATION = TRUE sur le warehouse
Scale up vs Scale out - requete unique lente = scale UP (XS vers M). Trop de requetes en queue = scale OUT (multi-cluster). Deux problemes differents, deux solutions

Query Profile UI - identifier les goulots : TableScan (pruning ok ?), Sort (evitable ?), Join (hash vs merge ?). Nodes les plus lents = priorite d optimisation
Search Optimization Service - index pour lookups ponctuels (WHERE id = 'abc') sur tables > 10M lignes. Verifier le ROI avant d activer : cout mensuel fixe
Query Acceleration Service - Snowflake lance des ressources supplementaires pour les requetes avec data skew. SET ENABLE_QUERY_ACCELERATION = TRUE sur le warehouse
Scale up vs Scale out - requete unique lente = scale UP (XS vers M). Trop de requetes en queue = scale OUT (multi-cluster). Deux problemes differents, deux solutions
Materialized Views vs Dynamic Tables - MV : refresh automatique, SQL limite. Dynamic Tables : JOINs complexes, incremental auto, TARGET_LAG configurable

7Grille par niveau

Niveau	Maitrise	Signal GO	NO-GO
Confirmé	Query Profile, clustering basique, auto-suspend	Lit le Query Profile, a configuré un clustering key	Ne sait pas lire le Query Profile
Senior	Clustering efficacité, MV, SOS, QAS, multi-cluster	Vérifie l efficacité du clustering avec CLUSTERING_INFORMATION, a utilisé MV ou SOS	Ne sait pas la différence entre scale up et scale out

1Query Profile: diagnosing slow queries

Discriminating question

How do you use the Snowflake Query Profile to identify a bottleneck?

-- Find slow queries from the last 24h
SELECT
    query_id,
    query_text,
    total_elapsed_time / 1000 AS elapsed_sec,
    bytes_scanned / POW(1024, 3) AS gb_scanned,
    partitions_scanned,
    partitions_total,
    partitions_scanned * 100.0 / NULLIF(partitions_total, 0) AS pct_scanned
FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
WHERE START_TIME >= DATEADD('hour', -24, CURRENT_TIMESTAMP)
  AND EXECUTION_STATUS = 'SUCCESS'
  AND TOTAL_ELAPSED_TIME > 30000  -- > 30 seconds
ORDER BY TOTAL_ELAPSED_TIME DESC
LIMIT 20;

-- Problem signal: pct_scanned close to 100%
-- Means clustering is not helping

Nodes in Query Profile — TableScan (effective pruning?), Sort (avoidable?), Join (hash vs merge?)
Partitions pruned — if 100% of partitions are scanned, clustering is useless on this filter

2Advanced clustering: beyond the basics

Discriminating question

When do you create a Clustering Key on multiple columns? How do you verify its effectiveness?

-- Clustering on multiple columns (order matters)
-- Most selective column first
ALTER TABLE fct_events
CLUSTER BY (event_date, event_type, user_segment);

-- Check clustering effectiveness
SELECT SYSTEM$CLUSTERING_INFORMATION('fct_events',
    '(event_date, event_type)');
-- average_depth close to 1 = good clustering
-- average_depth > 3 = reclustering needed

-- Automatic Clustering
-- Snowflake maintains clustering automatically
-- Cost: additional compute credits
ALTER TABLE fct_events RESUME RECLUSTER;

3Materialized Views: when to use them

Discriminating question

In which case is a Snowflake Materialized View preferable to a dbt table?

Snowflake Materialized View — automatic and transparent refresh when source data changes. Zero orchestration
Very frequent queries — an aggregation queried 1000 times per day benefits from a MV
Limitations — more restrictive SQL than dbt models (no complex JOINs, no subqueries)
Vs dbt table — the dbt table is more flexible (any SQL) but requires a scheduler. The MV is automatic but more limited

4Search Optimization Service

Discriminating question

What is the Search Optimization Service? For which types of queries?

Search Optimization Service — search index for exact-value lookups on very large tables
Use case — WHERE id = 'abc123' queries on a 10 billion row table. Without SOS: full scan. With SOS: direct lookup
Do not use for — analytical queries with aggregations. Clustering is more appropriate
Cost — additional storage cost for the index. Worth it if performance gains are significant

5Query Acceleration Service

Discriminating question

What is the Query Acceleration Service?

QAS — Snowflake automatically spins up additional resources to accelerate the slow parts of a query (outlier partitions)
Enable — ALTER WAREHOUSE mywh SET ENABLE_QUERY_ACCELERATION = TRUE
Use case — queries with a few partitions much larger than the others (data skew in partitions)
Cost — additional credits only when used. No fixed cost

6Warehouse sizing and multi-cluster

Discriminating question

How do you choose between scaling up (larger warehouse) and scaling out (multi-cluster)?

Problem	Solution	Why
Single query too slow	Scale UP (XS → S → M)	More compute per query
Too many concurrent queries (queueing)	Scale OUT (multi-cluster)	More parallel warehouses
Slow nightly transformation	Scale UP temporarily	Finish faster, then auto-suspend
BI dashboard with 50 concurrent users	Multi-cluster auto-scale	Distribute the load

-- Identify slow queries
SELECT query_id, total_elapsed_time/1000 AS sec,
    bytes_scanned/POW(1024,3) AS gb,
    ROUND(partitions_scanned*100.0/NULLIF(partitions_total,0),1) AS pct_scanned
FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
WHERE START_TIME >= DATEADD('hour',-24,CURRENT_TIMESTAMP)
  AND TOTAL_ELAPSED_TIME > 30000
ORDER BY TOTAL_ELAPSED_TIME DESC LIMIT 20;

-- Check clustering
SELECT SYSTEM$CLUSTERING_INFORMATION('fct_events','(event_date, event_type)');
-- average_depth close to 1 = good clustering

-- Search Optimization Service
ALTER TABLE customers ADD SEARCH OPTIMIZATION ON EQUALITY(customer_id, email);

-- Resource monitor
CREATE RESOURCE MONITOR monthly_budget
    WITH CREDIT_QUOTA = 500
    TRIGGERS ON 80 PERCENT DO NOTIFY
             ON 100 PERCENT DO SUSPEND;
ALTER WAREHOUSE ETL_WH SET RESOURCE_MONITOR = monthly_budget;

Query Profile UI - identify bottlenecks: TableScan (pruning ok?), Sort (avoidable?), Join (hash vs merge?). Slowest nodes = optimization priority
Materialized Views vs Dynamic Tables - MV: automatic refresh, limited SQL. Dynamic Tables: complex JOINs, auto incremental, configurable TARGET_LAG
Search Optimization Service - index for point lookups (WHERE id = 'abc') on tables > 10M rows. Costly: verify ROI before enabling
Query Acceleration Service - Snowflake spins up additional resources for queries with data skew. SET ENABLE_QUERY_ACCELERATION = TRUE on the warehouse
Scale up vs Scale out - single slow query = scale UP (XS to M). Too many queries queuing = scale OUT (multi-cluster). Two different problems, two solutions

Query Profile UI - identify bottlenecks: TableScan (pruning ok?), Sort (avoidable?), Join (hash vs merge?). Slowest nodes = optimization priority
Search Optimization Service - index for point lookups (WHERE id = 'abc') on tables > 10M rows. Verify ROI before enabling: fixed monthly cost
Query Acceleration Service - Snowflake spins up additional resources for queries with data skew. SET ENABLE_QUERY_ACCELERATION = TRUE on the warehouse
Scale up vs Scale out - single slow query = scale UP (XS to M). Too many queries queuing = scale OUT (multi-cluster). Two different problems, two solutions
Materialized Views vs Dynamic Tables - MV: automatic refresh, limited SQL. Dynamic Tables: complex JOINs, auto incremental, configurable TARGET_LAG

7Level grid

Level	Mastery	GO signal	NO-GO
Confirmed	Query Profile, basic clustering, auto-suspend	Reads the Query Profile, has configured a clustering key	Cannot read the Query Profile
Senior	Clustering effectiveness, MV, SOS, QAS, multi-cluster	Verifies clustering effectiveness with CLUSTERING_INFORMATION, has used MV or SOS	Does not know the difference between scale up and scale out

Vous recrutez un Data Engineer Snowflake Senior ?

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

Tester gratuitement Reserver un appel