Monitor and Optimize Queries in Databricks SQL

Platform: Databricks SQL

Table of Contents

1. Understanding query execution in Databricks SQL

• 1.1 General architecture of Databricks SQL
• 1.2 SQL Warehouses configuration and impact on performance
• 1.3 The Photon Engine — Query Acceleration
• 1.4 The 5 steps of query execution — From plan to results

2. Monitor Queries with Query History and Query Profile

• 2.1 Navigation in Query History
• 2.2 Query Profile Deep Analysis
• 2.3 Identify common performance issues in the Query Profile
• 2.4 Use system tables for long-term monitoring

3. Diagnose and Fix Bottlenecks

• 3.1 Partition Pruning and Predicate Pushdown
• 3.2 Join Optimization
• 3.3 Use Result Caching
• 3.4 Compare costs between different Warehouses

4. Module 4 — Optimize Delta Tables for Performance

• 4.1 OPTIMIZE and File Compaction
• 4.2 Z-ORDER for query optimization
• 4.3 Liquid Clustering — The Modern Alternative
• 4.4 VACUUM and Predictive Optimization

5. Scaling and Planning for Profitability

• 5.1 Choose the right warehouse size and scaling strategy
• 5.2 Schedule queries and manage recurring workloads
• 5.3 Query parameterization and reuse
• 5.4 Measure optimization results

6. Summary of good practices
7. Quick reference of essential SQL commands

1. Understanding query execution in Databricks SQL

1.1 General architecture of Databricks SQL

Before you can optimize something, you need to understand how it works behind the scenes. Here is the high-level architecture:

┌─────────────────────────────────────────────────────┐
│                  SQL Editor                          │
│         (point d'entrée de la requête)               │
└──────────────────────┬──────────────────────────────┘
                       │
                       ▼
┌─────────────────────────────────────────────────────┐
│              SQL Warehouse                           │
│   (couche compute qui exécute réellement la requête) │
└──────────────────────┬──────────────────────────────┘
                       │
                       ▼
┌─────────────────────────────────────────────────────┐
│           Unity Catalog                              │
│   (gouvernance et contrôle d'accès)                  │
└──────────────────────┬──────────────────────────────┘
                       │
                       ▼
┌─────────────────────────────────────────────────────┐
│              Delta Lake                              │
│  (stockage objet cloud : S3, ADLS, GCS)              │
└─────────────────────────────────────────────────────┘

Types of SQL Warehouses

Recommendation: For most use cases today, Serverless or Pro is the best choice.

Distributed execution model

When a query executes, there is a driver node that coordinates everything:

1. The driver takes the physical plan and breaks it down into tasks
2. Tasks are distributed across several worker nodes
3. Each worker processes its portion of data in parallel
4. Results go back to the driver for final assembly

This distributed model is what makes Databricks SQL so fast for large volumes, but it introduces risks of data skew and shuffle, which can become bottlenecks.

Simple aggregation query example

-- Requête de base : total des commandes et revenus par région
SELECT
    region,
    COUNT(*)       AS order_count,
    SUM(revenue)   AS total_revenue
FROM sql_optimization_course.orders
WHERE order_date BETWEEN '2024-01-01' AND '2024-12-31'
GROUP BY region;

After execution, open the execution side panel to observe:

• Time broken down into phases: scheduling, query optimization, file pruning, execution
• Metrics of the Scan Table phase: files pruned, lines scanned, filters applied

1.2 Configuration of SQL Warehouses and impact on performance

Warehouse configuration is one of the first levers to pull. The key parameters are:

Practical Sizing Guide

Recommended hitchhiking settings

Concurrency

Each cluster can handle approximately 10 concurrent requests. When the query queue exceeds this capacity, auto-scaling starts a new cluster.

SQL Warehouse management tabs

• Overview: Status, type, cluster size, hitchhiking, scaling parameters
• Connection details: Server hostname, HTTP path, JDBC URL (for Tableau, Power BI, dbt, etc.)
• Monitoring: Current queries, queued queries, active clusters, peak queries graphs

1.3 The Photon Engine — Query acceleration

Photon is a native vectorized C++ execution engine that bypasses the JVM entirely, delivering dramatically faster queries.

Main Features

• Enabled by default on all SQL Warehouses
• Available on Databricks Runtime 13.0 and above
• Includes Predictive I/O: predicts which data blocks will be needed for selective scans, reducing data movements

Identify Photon in Query Profile

In the Query Profile, operators executed by Photon display a lightning bolt (⚡) icon. Without this icon, Spark manages this step — this is called a Spark fallback.

Common Causes of Spark Fallback

• Python UDFs (User-Defined Functions)
• Complex user-defined types
• Some regex operations

Handy tip: Avoid Python UDFs whenever possible. Use built-in SQL functions to stay on the Photon execution path.

1.4 The 5 Steps of Query Execution — From Plan to Results

┌──────────────────────────────────────────────────────────────────┐
│  Stage 1 : SQL Parsing                                           │
│  Validation de la syntaxe + résolution des références via        │
│  Unity Catalog → création du logical plan                        │
└──────────────────────────────────────┬───────────────────────────┘
                                       │
                                       ▼
┌──────────────────────────────────────────────────────────────────┐
│  Stage 2 : Logical Plan                                          │
│  Génération d'un plan de requête abstrait depuis le SQL parsé    │
└──────────────────────────────────────┬───────────────────────────┘
                                       │
                                       ▼
┌──────────────────────────────────────────────────────────────────┐
│  Stage 3 : Physical Plan                                         │
│  Le Cost-Based Optimizer (CBO) choisit le meilleur ordre de      │
│  jointure, la stratégie et le parallélisme                       │
└──────────────────────────────────────┬───────────────────────────┘
                                       │
                                       ▼
┌──────────────────────────────────────────────────────────────────┐
│  Stage 4 : Execution                                             │
│  Distribution des tasks sur les worker nodes ; AQE               │
│  (Adaptive Query Execution) peut re-optimiser à la volée :       │
│  coalescer des petites partitions, changer de stratégie de       │
│  jointure, gérer le data skew                                    │
└──────────────────────────────────────┬───────────────────────────┘
                                       │
                                       ▼
┌──────────────────────────────────────────────────────────────────┐
│  Stage 5 : Result Delivery                                       │
│  Résultats mis en cache par utilisateur ; les requêtes           │
│  identiques sur des données inchangées reviennent instantanément │
│  sans ré-exécution                                               │
└──────────────────────────────────────────────────────────────────┘

The 3 key optimization mechanisms

Multi-table query example with Query Profile

-- Requête multi-tables : nom client, nombre total de commandes, montant dépensé
SELECT
    c.customer_name,
    COUNT(o.order_id)  AS total_orders,
    SUM(o.amount)      AS total_spent
FROM sql_optimization_course.customers c
JOIN sql_optimization_course.orders o
    ON c.customer_id = o.customer_id
WHERE o.status = 'completed'
GROUP BY c.customer_name
ORDER BY total_spent DESC;

Operators visible in the Query Profile (from bottom to top):

1. Scan Table (customers) — reading the customers table
2. Scan Table (orders) — reading the orders table
3. Shuffle — redistribution of data for join
4. Inner Join — merging the two streams
5. Filter — applying the WHERE clause
6. Grouping Aggregate — calculation of COUNT and SUM
7. Result — return lines to the client

Checkpoints in Profile:

• Inner Join node: time spent, peak memory, output rows → join cost
• Scan Table nodes: rows scanned, bytes read, files pruned, file sizes → scan efficiency
• Shuffle node: if spill_bytes > 0, this indicates memory pressure → scale up necessary

Aggregate view: Switch between the Time spent, Memory peak, Rows tabs to identify if the bottleneck is in scanning, joining or aggregation.

2. Monitor Queries with Query History and Query Profile

2.1 Navigating Query History

Query History is your first stop for isolating slow queries.

Access

From the left navigation bar → Query History

Important columns

Filters available

• By user
• By warehouse
• By duration
• By status (Finished, Running, Canceled)
• By time range (e.g.: last 24 hours)

Interpret the wall-clock breakdown

Total Duration = Scheduling + Compilation + Execution

Key tip: If a query spends the majority of its time in the scheduling phase, the problem is not your SQL — it’s availability or warehouse contention.

2.2 In-depth analysis of the Query Profile

The Query Profile displays an execution DAG (Directed Acyclic Graph) with each operator, the data flow between them, and the time spent at each step.

Metrics available by operator

• Wall time: real time elapsed
• Rows in / Rows out: number of incoming and outgoing rows
• Peak memory usage: maximum memory consumed
• Bytes spilled: data written to disk (sign of memory pressure)

Key operators to watch out for

The 3 tabs of the Query Profile

Time spent tab:

• Operator tree view weighted by time consumed
• The width/color of each node indicates its proportion of the total time
• Allows you to identify if the bottleneck is in the scan, join or aggregation

Memory peak tab:

• Memory consumption at each operator step
• Joins, sorts and window functions can cause spills to disk if memory overflows → silent performance killer

Rows tab:

• Count of lines crossing each operator at each step
• High volume of rows entering an operator with low volume leaving → predicate pushdown potential

Predicate Pushdown Concept

If a filter with a high reduction ratio (many rows removed) is placed far from the scan, it can be pushed earlier in the plan to reduce the work of all downstream operators. This is one of the largest performance gains available in Spark SQL.

2.3 Identify common performance issues in the Query Profile

The 4 main warning indicators

1. High scan-to-return ratio

• Scan millions of rows to return only a handful
• Cause: missing WHERE clauses, partition pruning not used
• Solution: add filters on partitioned columns

2. Task time imbalance

• One task takes 10 to 100 times longer than the others
• Cause: data skew in joins — one partition contains much more data
• Solution: Z-ORDER, liquid clustering, or AQE skew handling

3. SortMergeJoin on a small table

• A SortMergeJoin when one side of the join is small
• Solution: use a BROADCAST hint or lower the auto-broadcast threshold

4. High volume on Exchange operator

• Exchange shuffles gigabytes of data
• Cause: GROUP BY or JOIN keys not optimized
• Solution: optimize keys, use liquid clustering

Additional Alert Indicators

2.4 Using system tables for long-term monitoring

The Query Profile is ideal for individual queries, but for long-term monitoring, system tables are essential.

Main table: system.query.history

-- Colonnes clés de system.query.history
-- statement_id      : identifiant unique de la requête
-- statement_type    : SELECT, INSERT, MERGE, etc.
-- duration_ms       : durée totale en millisecondes
-- total_task_duration_ms : CPU total consommé par toutes les tasks
-- read_bytes        : quantité de données lues depuis le stockage
-- source            : SQL editor, dashboard, notebook, workflow job
-- executed_as       : identité de l'utilisateur exécutant

Query 1 — Macro view of recent queries

SELECT
    statement_type,
    total_duration_ms,
    read_bytes
FROM system.query.history
ORDER BY total_duration_ms DESC;

Query 2 — Target long queries (> 60 seconds)

SELECT
    statement_text,
    total_duration_ms / 1000.0          AS duration_seconds,
    read_bytes / POWER(1024.0, 3)       AS gb_scanned
FROM system.query.history
WHERE total_duration_ms > 60000
ORDER BY total_duration_ms DESC;

Note: Queries exceeding 1 minute on an analytical warehouse are almost always candidates for optimization. The gb_scanned column directly correlates to the billing cost in serverless and classic.

Query 3 — Governance report: cost per user

-- Leaderboard des requêtes les plus coûteuses par utilisateur
SELECT
    executed_as,
    COUNT(*)                                    AS query_count,
    AVG(total_duration_ms)                      AS avg_duration_ms,
    SUM(read_bytes) / POWER(1024.0, 3)          AS total_gb_scanned
FROM system.query.history
GROUP BY executed_as
ORDER BY avg_duration_ms DESC;

Handy Tip: This query, or a variation of it, should be scheduled as a Databricks Job, running every night, writing the results to a Delta table that feeds a monitoring dashboard. This is the transition from reactive optimization to proactive optimization.

Query 4 — Parallelism ratio

SELECT
    statement_id,
    total_duration_ms                                               AS wall_clock_ms,
    total_task_duration_ms,
    total_task_duration_ms / NULLIF(total_duration_ms, 0)          AS parallelism_ratio
FROM system.query.history
ORDER BY total_duration_ms DESC
LIMIT 20;

The parallelism_ratio indicates how efficiently Databricks distributed the work. A low ratio means that the query was largely sequential, leaving compute capacity underutilized.

Create views per user with CURRENT_USER()

-- Vue permettant aux équipes de voir leur propre historique sans accès admin
CREATE OR REPLACE VIEW my_team_query_history AS
SELECT *
FROM system.query.history
WHERE executed_as = CURRENT_USER();

3. Diagnose and Fix Bottlenecks

3.1 Partition Pruning and Predicate Pushdown

The fastest way to speed up a query is to read less data.

The 3 main scan reduction techniques

1. Min/Max column statistics

• Engine completely skips files that cannot match predicate
• Example: if the maximum of a file is 100 and the filter looks for > 200, the file is ignored

2. Partition Pruning

• Skips entire file directories
• If the table is partitioned by date and the query filters on a date range, the engine does not even look at directories outside of that range
• Can reduce scanned data by 90% or more

Important: Filter directly on partition columns. Wrapping the column in a function (e.g.: YEAR(date) or DATE(timestamp)) breaks pruning because the engine cannot evaluate the function against directory names.

3. Dynamic File Pruning

• Use min/max statistics at row group level in Parquet files
• Go further by using filters on the join side to ignore irrelevant files at runtime

Example — Query with Partition Pruning

-- La table est partitionnée par 'region'
-- Databricks ne lira que le répertoire region='North'
SELECT
    region,
    SUM(amount)  AS total_sales,
    COUNT(*)     AS record_count
FROM sql_optimization_course.sales_partitioned
WHERE region = 'North';
-- Résultat attendu : ~57% de bytes pruned dans le Query Profile

Example — Predicate Pushdown in action

-- SANS predicate pushdown (scan complet puis filtre)
SELECT
    p.product_name,
    SUM(oi.quantity)    AS total_sold,
    COUNT(oi.order_id)  AS order_count
FROM sql_optimization_course.order_items oi
JOIN sql_optimization_course.products p
    ON oi.product_id = p.product_id
GROUP BY p.product_name
ORDER BY total_sold DESC
LIMIT 20;

-- AVEC predicate pushdown (filtre poussé près du scan)
-- Databricks peut pousser le filtre sur product_id au stage Scan Table
-- avant tout join ou agrégation
SELECT
    p.product_name,
    SUM(oi.quantity)    AS total_sold,
    COUNT(oi.order_id)  AS order_count
FROM sql_optimization_course.order_items oi
JOIN sql_optimization_course.products p
    ON oi.product_id = p.product_id
WHERE oi.product_id BETWEEN 1 AND 50
GROUP BY p.product_name
ORDER BY total_sold DESC
LIMIT 20;

Golden rule: Filter as early as possible, as close to the source table. The further upstream your predicates are, the less data passes through each downstream step.

3.2 Join Optimization

Joins are often the most expensive part of a query.

The 3 join strategies in Databricks SQL

Adaptive Query Execution (AQE) can automatically convert a SortMergeJoin to a BroadcastHashJoin at runtime when it discovers that one side is actually small.

Example — Join with hint SHUFFLE_HASH

-- SHUFFLE_HASH force une ShuffleHashJoin
-- Utile pour les joins large-to-large quand SortMergeJoin est trop lent
-- Attention : risque d'OOM si les partitions sont trop grandes
SELECT /*+ SHUFFLE_HASH(o) */
    c.customer_name,
    COUNT(o.order_id)   AS total_orders,
    SUM(o.amount)       AS total_spent
FROM customers c
JOIN orders o
    ON c.customer_id = o.customer_id
WHERE o.order_date BETWEEN '2024-01-01' AND '2024-06-30'
GROUP BY c.customer_name;

Example — BROADCAST hint for small tables

-- Quand une table est significativement plus petite,
-- la broadcaster à tous les workers élimine le shuffle entièrement
SELECT /*+ BROADCAST(p) */
    p.product_name,
    SUM(oi.quantity)    AS total_sold
FROM order_items oi
JOIN products p
    ON oi.product_id = p.product_id
GROUP BY p.product_name;

Analyze joins in Query Profile

• The Inner Join node shows time spent and peak memory for the join alone
• Shuffle nodes represent the two sides of the join being repartitioned
• High volume on Shuffle with spill_bytes > 0 = memory pressure → scale up or strategy change

3.3 Using Result Caching

One of the easiest performance gains in Databricks SQL.

The 2 cache layers

Rules for obtaining cache hits

TODO: Use explicit dates and deterministic functions

-- ✅ Cache-friendly : dates statiques explicites
SELECT
    region,
    SUM(amount) AS total_revenue
FROM sales_data
WHERE sale_date BETWEEN '2024-01-01' AND '2024-03-31';

TO AVOID: Non-deterministic functions that systematically invalidate the cache

-- ❌ Cache-busting : CURRENT_DATE() change chaque jour → jamais de cache hit
SELECT
    region,
    SUM(amount) AS total_revenue
FROM sales_data
WHERE sale_date >= CURRENT_DATE() - INTERVAL 90 DAYS;

Note: Even a white space change in the SQL text invalidates the cache. Consistency in query formatting is important.

Cache pre-warming with CACHE SELECT

-- Précharger les données columnar en SSD local avant l'exécution du dashboard
CACHE SELECT * FROM sales_data;

Cache and data mutations

The cache is Delta-aware: as soon as a transaction is committed and the table version changes, the next query correctly bypasses the stale cache and reads fresh data.

Check a cache hit in the Query Profile

The status at the top of the profile will show “Finished Cache” — the request did not hit the storage at all.

3.4 Compare costs between different Warehouses

DBU Cost Formula

$$\text{DBU cost} = \text{warehouse DBUs} \times \frac{\text{active seconds}}{3600} \times \text{rate per DBU}$$

Selection guide by warehouse type

Accurately assign cost per query

-- Joindre system.billing.usage avec system.query.history
-- pour obtenir le coût exact par requête
SELECT
    qh.statement_id,
    qh.statement_text,
    qh.total_duration_ms,
    bu.usage_quantity                      AS dbu_consumed,
    bu.usage_quantity * <taux_par_dbu>     AS estimated_cost_usd
FROM system.query.history qh
JOIN system.billing.usage bu
    ON qh.warehouse_id = bu.resource_id
    AND DATE(qh.start_time) = bu.usage_date
WHERE bu.sku_name LIKE '%SQL%'
ORDER BY dbu_consumed DESC;

4-Step Playbook for Cost Optimization

1. IDENTIFIER   → Trouver les 10 requêtes les plus coûteuses par total_task_duration_ms
2. DIAGNOSTIQUER → Ouvrir le Query Profile pour chacune :
                    - Scans larges sans pruning
                    - Pruning manquant
                    - Mauvaises stratégies de join
3. CORRIGER     → Ajouter des filtres, des hints BROADCAST,
                    exécuter OPTIMIZE, ou configurer liquid clustering
4. MESURER      → Comparer avant/après dans system.query.history
                    + configurer des alertes de régression

Query — Top 10 most expensive queries

SELECT
    statement_id,
    SUBSTRING(statement_text, 1, 100)   AS query_preview,
    total_task_duration_ms,
    read_bytes / POWER(1024.0, 3)       AS gb_scanned
FROM system.query.history
ORDER BY total_task_duration_ms DESC
LIMIT 10;

4. Optimizing Delta Tables for Performance

4.1 OPTIMIZE and File Compaction

The small file problem

During streaming ingestion or frequent small writes, thousands of files under 10 MB are created. Each file requires its own task to be scanned, and this task scheduling overhead adds up quickly.

The solution: OPTIMIZE

OPTIMIZE compacts these files into targets of approximately 1 GB, which can reduce the number of files by 10 to 100 times.

-- Compacter les fichiers de la table sales_unoptimized
OPTIMIZE sales_unoptimized;

Results returned by OPTIMIZE:

When to run OPTIMIZE

• After a batch ingestion
• After streaming writes
• After DELETE and MERGE operations

Auto-compaction for streaming tables

For tables receiving frequent writes, running OPTIMIZE manually between each microbatch is impractical. Auto-compaction automatically triggers compaction after writes.

-- Activer l'auto-compaction sur une table de streaming
ALTER TABLE streaming_table
SET TBLPROPERTIES (
    'delta.autoOptimize.optimizeWrite' = 'true'
);

4.2 Z-ORDER for per-query optimization

How Z-ORDER works

Z-ORDER uses space-filling curves to interleave bits from high cardinality filter columns. The effect: associated data ends up co-located in the same files.

• Without Z-ORDER: the values of a column are randomly distributed in all files
• With Z-ORDER: they are grouped — a filter on this column only affects a few files

After a Z-ORDER, file-level min/max statistics become much more accurate, allowing the engine to use data skipping to skip 10 to 100 times more files.

Syntax

-- Z-ORDER sur les colonnes sale_date et product_id
OPTIMIZE fact_sales_unordered
ZORDER BY (sale_date, product_id);

Check impact

-- Avant Z-ORDER : 1600 fichiers lus pour une seule ligne
SELECT * FROM fact_sales_unordered
WHERE sale_date = '2024-06-15' AND product_id = 42;

-- Exécuter Z-ORDER
OPTIMIZE fact_sales_unordered ZORDER BY (sale_date, product_id);

-- Vérifier la structure de fichiers résultante
DESCRIBE DETAIL fact_sales_unordered;

-- Après Z-ORDER : seulement quelques fichiers lus pour la même requête
SELECT * FROM fact_sales_unordered
WHERE sale_date = '2024-06-15' AND product_id = 42;

Z-ORDER Best Practices

Important limitation

Z-ORDER rewrites all data on each run — cost is proportional to table size. For this reason, Liquid Clustering has largely supplanted Z-ORDER.

4.3 Liquid Clustering — The Modern Alternative

Liquid Clustering is the modern replacement for legacy partitions and Z-ORDER.

The 3 key advantages

Create a table with Liquid Clustering

-- Créer une table avec liquid clustering dès la création
CREATE TABLE fact_sales
CLUSTER BY (sale_date, product_id)
AS SELECT * FROM fact_sales_source;

Apply clustering to an existing table

-- Définir les clustering keys sur une table existante
ALTER TABLE fact_sales
CLUSTER BY (region, sale_date);

-- Puis lancer OPTIMIZE pour appliquer le clustering
-- (appliqué graduellement, pas de réécriture complète)
OPTIMIZE fact_sales;

Demo Workflow

-- Étape 1 : Vérifier l'état actuel de la table (clusteringColumns)
DESCRIBE DETAIL fact_sales;

-- Étape 2 : Requête de référence avant clustering
SELECT
    region,
    SUM(total_sales)    AS revenue,
    COUNT(*)            AS record_count
FROM fact_sales
WHERE region = 'West'
  AND sale_date BETWEEN '2024-01-01' AND '2024-06-30';
-- Observer le nombre de fichiers lus dans le Query Profile

-- Étape 3 : Appliquer liquid clustering
ALTER TABLE fact_sales CLUSTER BY (region, sale_date);
OPTIMIZE fact_sales;

-- Étape 4 : Relancer la même requête
-- → Le Query Profile devrait montrer un seul fichier lu
SELECT
    region,
    SUM(total_sales)    AS revenue,
    COUNT(*)            AS record_count
FROM fact_sales
WHERE region = 'West'
  AND sale_date BETWEEN '2024-01-01' AND '2024-06-30';

Liquid Clustering is the recommended approach for most Delta tables in production.

4.4 VACUUM and Predictive Optimization

VACUUM — Recover storage

Delta Lake’s time travel feature keeps every version of every file on storage until you explicitly clean it up.

-- Voir l'historique complet des versions d'une table
DESCRIBE HISTORY sales_data;

Always do a DRY RUN first!

-- DRY RUN : preview des fichiers qui seraient supprimés
-- sans rien supprimer réellement
VACUUM sales_data RETAIN 168 HOURS DRY RUN;

-- Exécuter réellement le VACUUM
VACUUM sales_data RETAIN 168 HOURS;

Important VACUUM Rules

Caution: Reducing the retention period below 168 hours risks breaking drives that rely on older versions of the data.

Serverless specific case

On a SQL Warehouse Serverless, some low-level Spark configurations are not accessible. If you try to disable the safety check, you will get the error CONFIG_NOT_AVAILABLE. The solution is to specify a retention window that satisfies the security threshold.

Predictive Optimization — Automate maintenance

-- Activer Predictive Optimization sur une table spécifique
ALTER TABLE my_table ENABLE PREDICTIVE OPTIMIZATION;

With Predictive Optimization enabled, Databricks:

• Automatically monitors table usage patterns
• Automatically trigger OPTIMIZE, VACUUM and ANALYZE TABLE
• Eliminates the need to manually schedule maintenance jobs

Note: Predictive Optimization can also be activated at the catalog or schema level to apply to all tables.

Summary of 5 Delta Table Maintenance Strategies

5. Scaling and Planning for Profitability

5.1 Choosing the right warehouse size and scaling strategy

General Sizing Guide

The cost-latency curve

The cost-latency curve is approximately linear:

• Double the size of the warehouse ≈ divide the query time by 2
• Double the size of the warehouse = double the DBU expenses

You must decide where to position yourself on this curve for each type of load.

Auto-scaling configuration

Min clusters ← balance cold-start latency
Max clusters ← balance idle cost

Tip: Do not use keep-alive queries to prevent warehouses from stopping. This wastes resources. Let hitchhiking and serverless handle inactivity management.

Validate the sizing in the Monitoring tab

From the Monitoring tab of the warehouse, observe in real time:

• Peak query count: maximum number of simultaneous queries
• Completed queries per minute: throughput
• Active cluster count: number of currently active clusters

These metrics validate whether the warehouse is properly sized for the load it manages.

5.2 Schedule queries and manage recurring workloads

The 3 patterns for recurring charges

Plan a Query — Steps

1. Save the query in the SQL Editor (prerequisite for planning)
2. Click on the Schedule button in the toolbar
3. Set the cadence (e.g.: every day at 6:30 a.m.)
4. Manage the job in Jobs & Pipelines → possibility of Run now, Edit trigger

Tip: Scheduled queries run with the identity and permissions of the owner. Stagger dashboard refreshes by 5–10 minutes to avoid hitting the warehouse simultaneously.

Create a Materialized View

-- Pré-calculer les agrégations régionales mensuelles
CREATE MATERIALIZED VIEW mv_regional_sales AS
SELECT
    region,
    DATE_TRUNC('month', sale_date) AS month,
    SUM(amount)                    AS total_revenue,
    COUNT(*)                       AS transaction_count
FROM sales_data
GROUP BY
    region,
    DATE_TRUNC('month', sale_date);

-- Requête lente (scan complet)
SELECT region, DATE_TRUNC('month', sale_date), SUM(amount)
FROM sales_data
GROUP BY region, DATE_TRUNC('month', sale_date);

-- Requête rapide (depuis la Materialized View)
SELECT * FROM mv_regional_sales
WHERE region = 'West';

Best scheduling practices

• Batch ETL → run during off-peak hours to reduce contention with interactive users
• Dashboard refreshes → stagger by 5–10 minutes
• Cold starts → schedule during active warehouse hours, or use serverless (2–6 seconds startup)

5.3 Query parameterization and reuse

Benefits of parameterization

Parameterization syntax (:parameter)

-- Requête paramétrée avec :start_date et :status_filter
-- Les widgets de dashboard sont automatiquement liés aux paramètres
SELECT
    order_id,
    customer_name,
    amount,
    status,
    order_date
FROM sql_optimization_course.orders
WHERE order_date >= :start_date
  AND status = :status_filter
ORDER BY order_date DESC;

Example of use:

• start_date = 2024-01-01, status_filter = Completed
• start_date = 2024-01-01, status_filter = Shipped

Same query, same logic, different results instantly — the second run benefits from the cache because the SQL text is identical.

CTEs to eliminate redundant subqueries

Instead of copying and pasting the same subquery in multiple places, define once as CTE (Common Table Expression):

-- Mauvaise pratique : sous-requête dupliquée
SELECT customer_id, amount
FROM orders
WHERE customer_id IN (
    SELECT customer_id FROM customers WHERE region = 'West'
)
UNION ALL
SELECT customer_id, amount
FROM returns
WHERE customer_id IN (
    SELECT customer_id FROM customers WHERE region = 'West'  -- doublon !
);

-- Bonne pratique : CTE réutilisable
WITH west_customers AS (
    SELECT customer_id
    FROM customers
    WHERE region = 'West'
)
SELECT o.customer_id, o.amount, 'order'  AS type FROM orders  o JOIN west_customers wc ON o.customer_id = wc.customer_id
UNION ALL
SELECT r.customer_id, r.amount, 'return' AS type FROM returns r JOIN west_customers wc ON r.customer_id = wc.customer_id;

5.4 Measuring optimization results

All the optimizations in the world don’t matter if you don’t measure the results.

The 4 key metrics to track

Query 1 — Macro view: latency and scanned data

-- Vue agrégée de la performance du warehouse
SELECT
    COUNT(*)                                        AS total_queries,
    AVG(total_duration_ms)                          AS avg_duration_ms,
    PERCENTILE_CONT(0.50) WITHIN GROUP
        (ORDER BY total_duration_ms)                AS p50_duration_ms,
    PERCENTILE_CONT(0.95) WITHIN GROUP
        (ORDER BY total_duration_ms)                AS p95_duration_ms,
    AVG(read_bytes) / POWER(1024.0, 3)              AS avg_gb_scanned
FROM system.query.history
WHERE start_time >= CURRENT_TIMESTAMP() - INTERVAL 7 DAYS;

Query 2 — 20 most recent queries (trend analysis)

SELECT
    SUBSTRING(statement_text, 1, 80)   AS query_preview,
    total_duration_ms,
    read_bytes / POWER(1024.0, 3)      AS gb_scanned,
    start_time
FROM system.query.history
ORDER BY start_time DESC
LIMIT 20;

Query 3 — Baseline before/after optimization

-- Capturer les métriques AVANT l'optimisation, noter le statement_id
-- Appliquer les changements (OPTIMIZE, ZORDER, liquid clustering, etc.)
-- Recapturer APRÈS → la différence est votre preuve d'amélioration
SELECT
    statement_id,
    SUBSTRING(statement_text, 1, 80)   AS query_preview,
    total_duration_ms                  AS duration_ms,
    read_bytes / POWER(1024.0, 3)      AS gb_scanned,
    start_time
FROM system.query.history
WHERE statement_text LIKE '%votre_requête_cible%'
ORDER BY start_time DESC
LIMIT 10;

Query 4 — DBU consumption (actual cost)

-- Consommation DBU par date et SKU pour les workloads SQL
SELECT
    usage_date,
    sku_name,
    SUM(usage_quantity)         AS total_dbu,
    SUM(usage_quantity) * 0.22  AS estimated_cost_usd  -- adapter le taux
FROM system.billing.usage
WHERE sku_name LIKE '%SQL%'
GROUP BY usage_date, sku_name
ORDER BY usage_date DESC;

These 4 queries form a complete optimization feedback loop. Use these queries regularly to establish baselines and monitor workloads over time.

6. Summary of best practices

✅ TO DO

❌ TO AVOID

7. Quick reference of essential SQL commands

Managing Delta tables

-- Compacter les fichiers
OPTIMIZE <table_name>;

-- Compacter avec Z-ORDER
OPTIMIZE <table_name>
ZORDER BY (<col1>, <col2>);

-- Liquid clustering
ALTER TABLE <table_name>
CLUSTER BY (<col1>, <col2>);

-- Auto-compaction
ALTER TABLE <table_name>
SET TBLPROPERTIES ('delta.autoOptimize.optimizeWrite' = 'true');

-- VACUUM : preview
VACUUM <table_name> RETAIN 168 HOURS DRY RUN;

-- VACUUM : exécution
VACUUM <table_name> RETAIN 168 HOURS;

-- Predictive Optimization
ALTER TABLE <table_name> ENABLE PREDICTIVE OPTIMIZATION;

-- Statistiques pour le CBO
ANALYZE TABLE <table_name> COMPUTE STATISTICS;

-- Historique des versions
DESCRIBE HISTORY <table_name>;

-- Détails physiques de la table
DESCRIBE DETAIL <table_name>;

Monitoring with System Tables

-- Requêtes lentes (> 60 secondes)
SELECT
    statement_text,
    total_duration_ms / 1000.0        AS duration_seconds,
    read_bytes / POWER(1024.0, 3)     AS gb_scanned
FROM system.query.history
WHERE total_duration_ms > 60000
ORDER BY total_duration_ms DESC;

-- P95 de latence des 7 derniers jours
SELECT
    PERCENTILE_CONT(0.95) WITHIN GROUP
        (ORDER BY total_duration_ms) AS p95_duration_ms
FROM system.query.history
WHERE start_time >= CURRENT_TIMESTAMP() - INTERVAL 7 DAYS;

-- Consommation DBU par date
SELECT
    usage_date,
    sku_name,
    SUM(usage_quantity) AS total_dbu
FROM system.billing.usage
WHERE sku_name LIKE '%SQL%'
GROUP BY usage_date, sku_name
ORDER BY usage_date DESC;

Join Hints

-- BroadcastHashJoin (petite table diffusée à tous les workers)
SELECT /*+ BROADCAST(small_table) */ ...
FROM large_table JOIN small_table ON ...;

-- ShuffleHashJoin
SELECT /*+ SHUFFLE_HASH(table_alias) */ ...
FROM table1 JOIN table2 ON ...;

Cache pre-warming

-- Charger les données en cache SSD local
CACHE SELECT * FROM <table_name>;

Materialized Views

CREATE MATERIALIZED VIEW <view_name> AS
SELECT ...
FROM ...
GROUP BY ...;

Parameterization

-- Syntaxe :parameter pour les widgets de dashboard
SELECT *
FROM orders
WHERE order_date >= :start_date
  AND status = :status_filter;

Conclusion: Optimization is not a one-time activity. It is a continuous cycle of monitoring, diagnosis, correction and measurement. Build these dashboards, configure these alerts, and continue to iterate.

Search Terms

monitor · optimize · queries · databricks · sql · azure · spark · data · engineering · analytics · query · optimization · profile · cache · tables · cost · join · parameterization · clustering · delta · execution · important · monitoring · per