Study guide
7.3 Troubleshooting Slow & Failing Queries
Databricks Certified Data Analyst Associate Exam
Track your progress as you read
Finished sections get a check mark.
1 Platform Overview & Unity Catalog Foundations
1.1 Databricks Architecture & Compute Concepts 1.2 Databricks Marketplace & Data Sharing Basics 1.3 Unity Catalog Namespace & Object Hierarchy 1.4 Governed Asset Discovery & System Tables
2 Managing & Cleaning Governed Data
2.1 Dataset Discovery & Asset Tagging 2.2 Lineage Inspection & Data Traceability 2.3 SQL Data Cleaning & Transformation Techniques
3 Data Ingestion & Import Patterns
3.1 Workspace Uploads & Cloud File Ingestion 3.2 Incremental File Ingestion with Auto Loader 3.3 Ingesting Data via Delta Sharing & External Connections
4 Databricks SQL Warehouses & Core Querying
4.1 Databricks SQL Interface & SQL Assistant 4.2 SQL Warehouses Sizing & Management 4.3 Table Creation & Data Modification in Databricks SQL
5 Advanced SQL Authoring & Time Travel
5.1 Advanced Aggregations, Window Functions & Analytics 5.2 Complex Joins, CTEs & Set Operations 5.3 Views, Materialized Views & Delta Time Travel
6 Query Performance Analysis & Photon Engine
6.1 Photon Engine Architecture & Acceleration 6.2 Query History & Execution Profile Inspection 6.3 Delta Lake Audit History & Commit Logs
7 Liquid Clustering, Caching & Performance Troubleshooting
7.1 Caching Hierarchy in Databricks SQL 7.2 Data Layout Optimization: Liquid Clustering vs Partitioning & Z-Ordering 7.3 Troubleshooting Slow & Failing Queries
8 AI/BI Dashboards & Visualization Design
8.1 AI/BI Dashboards Overview & Canvas Layout 8.2 Chart Types & Visualization Selection 8.3 Notebook & SQL Editor Visualizations
9 Dashboard Interactivity, Publishing & Alerts
9.1 Dashboard Parameters & Interactive Filtering 9.2 Publishing, Sharing & Access Control 9.3 Scheduled Refresh, Email Subscriptions & Alerts
10 Developing & Maintaining AI/BI Genie Spaces
10.1 AI/BI Genie Architecture & Space Setup 10.2 Curation via Instructions, Descriptions & Sample Questions 10.3 Trusted Assets & Parameterized Functions 10.4 Monitoring, Benchmarks, Feedback & Maintenance
11 Data Modeling & Medallion Architecture
11.1 Dimensional Modeling Fundamentals 11.2 Medallion Architecture for Analytics 11.3 Constraint Enforcement & Relationships in Unity Catalog
12 Securing Data & Unity Catalog Governance
12.1 Unity Catalog Privilege Model & Grants 12.2 Ownership, Inheritance & Access Control Patterns 12.3 Fine-Grained Security: Row Filters & Column Masks
7.3 Troubleshooting Slow & Failing Queries
Key Takeaways Explain This Topic AI
The Databricks SQL Query History interface retains 30 days of execution logs and provides the Query Profile visualizer to inspect DAG execution operators and metrics.
Disk spill (Spill memory / Spill disk) occurs when intermediate data during joins or aggregations exceeds executor RAM, which can be fixed by upsizing the SQL Warehouse or filtering data earlier.
Data skew is identified in the Query Profile when Max Task Duration is significantly higher than Median Task Duration; it can be remediated by filtering NULLs, enabling AQE skew joins, or key salting.
Non-sargable predicates (such as WHERE YEAR(date_col) = 2026) prevent Delta min/max data skipping and force full table scans; they should be rewritten as sargable range filters.
High Queued Time indicates SQL Warehouse concurrency saturation, which can be resolved by configuring Multi-Cluster Auto-scaling (increasing Max Clusters).
Last updated: July 2026
Troubleshooting Slow & Failing Queries
Exam Focus: Databricks Data Analyst Associates must be adept at using the Query History interface and Query Profile visualizer to identify, diagnose, and resolve query performance bottlenecks and failures. Exam questions test your ability to interpret execution metrics (such as disk spill, task skew, and file scan volumes) and select the correct remediation strategy.
The Databricks SQL Diagnostic Toolkit
When a query runs slowly or fails, the first step is opening Query History in the Databricks SQL sidebar. Query History provides administrative and diagnostic metadata for every query executed on a SQL Warehouse within the past 30 days.
Key diagnostic details provided in Query History include:
Statement Status : COMPLETED , FAILED , CANCELED , or QUEUED .
Duration Breakdown : Execution Time, Compile Time, Queued Time, and Result Fetching Time.
SQL Warehouse Info : Warehouse name, compute size (e.g., Small, Medium, Large), and cluster type (Serverless vs Classic).
Query Profile Visualizer : A graphical DAG (Directed Acyclic Graph) showing exact tree operators ( ScanExistingTable , Filter , HashAggregate , BroadcastHashJoin , SortMergeJoin ) and per-operator metric statistics.
- Troubleshooting Common Performance Bottlenecks
- 1. Data Spill to Disk (Memory Exhaustion)
Symptom : Query execution slows down significantly during large join or aggregation steps. The Query Profile displays high values for Spill (disk) and Spill (memory) .
Root Cause : Disk spill occurs when an executor's allocated RAM (JVM heap / off-heap memory) is insufficient to hold intermediate data structures during sorting, hash aggregation, or shuffle join operations. Spark spills the excess data to local worker disk, incurring heavy read/write I/O penalties. Query Profile Warning Indicator:
[HashAggregate] -> Spill (memory): 14.2 GiB | Spill (disk): 4.1 GiB
Remediation Strategies :
Upsize the SQL Warehouse : Scaling up the SQL Warehouse size (e.g., from Medium to Large ) increases RAM capacity per worker node.
Filter Early : Move WHERE clause filters ahead of complex joins to reduce intermediate record volumes.
Optimize Join Strategy : Enable broadcast joins for smaller lookup tables using hints: SELECT /*+ BROADCAST(dim_customer) */ f.order_id, d.customer_name
- FROM sales_fact f
- JOIN dim_customer d ON f.customer_id = d.customer_id;
- 2. Data Skew (Uneven Executor Workloads)
Symptom : A query gets stuck at 99% progress for a long time. In the Query Profile, inspecting task attempt durations shows that Max Task Duration is dramatically higher than Median Task Duration (e.g., 1 task takes 12 minutes while 100 tasks finish in 3 seconds).
Root Cause : Data skew occurs when data distribution across partition keys is uneven. For example, joining tables on a customer_id column where 50% of rows contain NULL or a default placeholder ( 'UNKNOWN' ) forces a single executor node to process half of the entire dataset.
Remediation Strategies :
- Filter Out Nulls/Placeholders : Exclude heavily skewed values before performing joins: SELECT *
- FROM orders o
- JOIN customers c ON o.customer_id = c.customer_id
- WHERE o.customer_id IS NOT NULL AND o.customer_id != 'UNKNOWN';
Enable Adaptive Query Execution (AQE) Skew Join : Ensure AQE skew join handling is enabled: SET spark.sql.adaptive.skewJoin.enabled = true;
Key Salting : For persistent high-cardinality skew, salt the join key by appending random integers to distribute skewed keys across multiple executor tasks.
3. Full Table Scans & Non-Sargable Predicates
Symptom : Query Profile reveals high Files Read and Bytes Read from Storage metrics that match total table size, indicating zero data skipping occurred despite having WHERE clauses.
Root Cause : Using non-sargable predicates (Search Argument Able) prevents Delta Lake from using file min/max statistics for data skipping. Wrapping filter columns in scalar functions forces Spark to evaluate every row across all files. -- NON-SARGABLE (Bad): Forces full table scan because YEAR() wraps the column:
- SELECT SUM(amount)
- FROM sales_fact
- WHERE YEAR(order_date) = 2026;
-- SARGABLE (Good): Enables Delta file min/max data skipping:
- SELECT SUM(amount)
- FROM sales_fact
- WHERE order_date >= '2026-01-01' AND order_date <= '2026-12-31';
Remediation Strategies :
Rewrite predicates to isolate table columns on one side of comparison operators.
Run ANALYZE TABLE table_name COMPUTE STATISTICS; to update column statistics.
Apply Liquid Clustering ( CLUSTER BY ) on heavily filtered query columns.
4. High Queued Time (Warehouse Concurrency Limits)
Symptom : Queries experience long execution delays, but the Query Profile shows very low active execution time while Queued Time accounts for 80%+ of overall duration.
Root Cause : The SQL Warehouse has reached its concurrent query execution capacity limit. Subsequent incoming queries are queued waiting for compute threads to free up.
Remediation Strategies :
Enable Multi-Cluster Auto-scaling on the SQL Warehouse (e.g., setting Min Clusters = 1, Max Clusters = 5). When queueing occurs, the warehouse automatically spins up additional clusters to handle concurrent query demand.
Route ad-hoc analyst queries and automated dashboard refreshes to separate dedicated SQL Warehouses.
Comprehensive Troubleshooting Reference Matrix
- Error / Symptom Primary Diagnostic Metric Primary Cause Recommended Action
- Disk Spill Spill (disk) > 0 B Executor RAM exceeded during join/sort Upsize SQL Warehouse; filter data earlier
Data Skew Max Task Time >> Median Task Time Uneven key distribution across tasks Filter NULLs; enable AQE skew join; salt keys
Slow Full Scan Files Read == Total Files Non-sargable filter or missing cluster keys Rewrite sargable WHERE clause; run OPTIMIZE
High Queueing Queued Time > Execution Time Warehouse concurrency limit reached Increase Max Clusters in Warehouse auto-scaling