ExamFinch.com My Study

Study guide

6.2 Query History & Execution Profile Inspection

Practice this exam

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

6.2 Query History & Execution Profile Inspection

Key Takeaways Explain This Topic AI

Databricks SQL Query History retains up to 30 days of past query execution logs, details, physical plans, and user performance telemetry.

The graphical Execution Profile visualizes query DAGs, breakdown timings per operator node, task metrics, and active Photon Engine acceleration status.

Data spilling to disk (Bytes Spilled to Disk) occurs when intermediate task data exceeds memory allocations during sorting or hash joins, leading to severe IO latency.

The Bytes Read from Cache metric highlights local Delta cache efficiency, where reading from high-speed SSD cache avoids remote cloud storage latency.

Identifying data skew requires comparing 99th percentile task duration against median task duration within execution profile nodes.

Last updated: July 2026

Query History & Execution Profile Inspection

In Databricks SQL, optimizing query performance requires moving beyond execution runtime numbers to inspect granular hardware and operator telemetry. The Query History interface and graphical Execution Profile provide data analysts with deep visibility into physical execution DAGs (Directed Acyclic Graphs), task distributions, resource bottlenecks, and Photon Engine utilization.

Query History Interface & Search Capabilities

The Query History page logs every query executed within a Databricks SQL Warehouse. Databricks retains query metrics for 30 days , offering an extensive audit and diagnostic window for historical analysis. +-----------------------------------------------------------------------------------------+

  • | Databricks SQL Query History |
  • +-----------------------------------------------------------------------------------------+
  • | Filters: [ User: All ] [ Warehouse: BI_Analytics ] [ Status: Success ] [ Time: 7 Days ] |
  • +-----------------------------------------------------------------------------------------+
  • | Query ID | Statement | User | Duration | Wall Time | Status |
  • | 8f4a12c9 | SELECT customer_id... | j.doe@co | 4m 12s | 4m 10s | SUCCESS|
  • | a3b719e0 | MERGE INTO sales_fact... | ETL_Service | 12m 45s | 12m 40s | SUCCESS|
  • | f9e821a4 | SELECT * FROM raw_logs... | a.smith@co | 15s | 14s | FAILED |
  • +-----------------------------------------------------------------------------------------+

Analysts can filter Query History using several key dimensions:

Statement / Query ID: Search by specific SQL text or unique execution UUID.

User / Service Principal: Audit queries triggered by individual analysts or automated BI service accounts.

SQL Warehouse: Compare performance across Serverless, Pro, or Classic warehouse instances.

Duration / Status: Isolate long-running queries, canceled statements, or runtime execution errors.

Graphical Execution Profile Structure

Selecting an individual query entry opens the Execution Profile . The Execution Profile renders the query's physical execution plan as a top-down or left-to-right DAG composed of interconnected operator nodes.

Each node represents a distinct physical processing stage (e.g., PhotonScan , PhotonHashAggregate , Filter , Exchange ). Clicking on an operator node expands a details panel displaying execution metrics, memory consumption, and task timing distributions.

Key Metrics to Inspect in Execution Profiles

Profile Metric Description Healthy Baseline Bottleneck Indicator

Task Execution Time Wall-clock CPU time spent executing tasks inside node Even distribution across tasks 99th percentile task time >> Median task time ( Data Skew )

Bytes Read from Cache Volume of data retrieved directly from local NVMe cache High % relative to total read bytes 0 bytes read from cache (Cold read / Remote storage fetch)

Bytes Spilled to Disk Intermediate data written to local disk when RAM is full 0 Bytes > 0 Bytes ( Memory Spill , severe IO performance penalty)

Files Pruned vs. Read Delta Lake file pruning efficiency via Data Skipping/Z-Order High ratio of pruned files Low pruning ratio (Full table scans on multi-terabyte tables)

Shuffle Read / Write Data volume exchanged across cluster nodes during joins/groupings Minimal shuffle data volume High shuffle bytes (Missing broadcast join candidates or bad partition keys)

  • Diagnosing Common Query Bottlenecks
  • 1. Memory Spilling ( Bytes Spilled to Disk )

Spill to Disk occurs when an execution task (such as a large sort, window function, or hash join build table) exceeds its allotted executor memory segment. When RAM is exhausted, Spark spills intermediate partitions to local executor disk drives, causing severe disk IO bottlenecks.

Diagnostic Indicator: In the Execution Profile node, Spill (Memory) and Spill (Disk) metrics display non-zero values (e.g., Spill (Memory): 45.2 GB , Spill (Disk): 12.8 GB ).

Remediation Strategies:

Increase the compute size of the SQL Warehouse (e.g., scaling from Medium to Large), which increases memory per core.

Optimize query logic by filtering early or selecting only necessary columns to reduce intermediate payload sizes.

Replace Sort-Merge joins with Broadcast Hash joins by ensuring dimension tables are appropriately sized.

2. Data Skew

Data Skew occurs when data partitions are unevenly distributed across worker tasks. A single task may process 90% of a skewed join key (e.g., NULL values or high-frequency default customer IDs), forcing 127 cluster cores to sit idle while 1 core struggles to complete. Task Execution Time Distribution (Data Skew Example):

  • Min Task Time: 1.2s |==================|
  • Median Task Time: 1.5s |====================|
  • Max Task Time: 180.4s |=========================================================================>| (SKEW)

Diagnostic Indicator: Extreme variance between task duration metrics within a node. The Max Task Duration (or 99th percentile) is order-of-magnitude larger than the Median Task Duration .

Remediation Strategies:

Filter out NULL or placeholder keys prior to performing join operations.

Enable Databricks Adaptive Query Execution (AQE) skew join handling: SET spark.sql.adaptive.skewJoin.enabled = true;

Apply salting techniques to uniformize partition distribution on heavily skewed join keys.

3. Missing File Pruning (Unoptimized Data Scans)

When queries execute against unpartitioned or unindexed Delta tables, file scanners must evaluate every single Data File in cloud storage.

Diagnostic Indicator: High Files Scanned count (e.g., 50,000 files) matching total table file count, with zero or low Files Pruned .

Remediation Strategies:

Execute OPTIMIZE table_name ZORDER BY (frequent_filter_column) to group co-located values within parquet files.

Apply Liquid Clustering ( CLUSTER BY (column_a, column_b) ) on high-cardinality query filter columns.

Real-World Scenario: Investigating Dashboard Delay & Disk Spill

A senior data analyst notices that a daily revenue summary query on a 1-billion-row transactions dataset degraded from 30 seconds to 14 minutes .

Query History Investigation: The analyst opens Query History, filters by the query string, and observes wall-clock execution time spiked to 840 seconds .

Execution Profile Inspection: Opening the graphical profile reveals that 82% of the query time was concentrated in a PhotonHashJoin node. Expanding node metrics reveals:

  • Spill (Memory): 112 GB
  • Spill (Disk): 34 GB

Task Execution Time Max: 410s , Median: 4s (Indicating severe Skew + Memory Spill).

Root Cause Analysis: A recent upstream ETL bug introduced millions of default dummy records with customer_id = -1 . The join operation routed all -1 records to a single hash join bucket, exceeding task memory and forcing 34 GB of disk spill.

Resolution: The analyst updated the query predicate to exclude invalid customer IDs prior to joining ( WHERE customer_id != -1 ). Re-running the query eliminated disk spill ( Spill to Disk: 0 Bytes ) and restored execution time to 22 seconds .