Databricks SQL Query History for Audit, Analysis, and Performance Tuning

Query history is a core dataset that lets you see, at a glance, "who ran what SQL, when, on which Warehouse, and for how long." In operations, it underpins audit, SLA verification, and cost optimization; on the exam, it shows up in the context of log usage and performance observation.

This article organizes how to work with the UI, the REST API, and System Tables, and offers query examples and decision criteria you can use directly in production. Areas where behavior may change across versions are flagged, and explanations follow the official specifications.

The Big Picture of Query History and Prerequisites

Databricks SQL Query history is a feature that aggregates and displays execution history from SQL Warehouses. The main attributes typically shown include execution start time, user, SQL text, Warehouse, duration, and status (success/failure). In addition to UI visualization, history can be retrieved programmatically via the REST API.

Permissions and visibility depend on workspace settings and roles. In many environments, regular users can see their own history, while admins (or users with appropriate permissions) can view broader history. In environments with strict audit requirements, the standard approach is to combine Unity Catalog System Tables (e.g. system.access.audit) and account-level audit logs.

• Scope: queries executed on Databricks SQL Warehouses
• Key fields: user, start/end time, Warehouse, SQL, duration, status
• Uses: audit, SLA/latency tracking, bottleneck investigation, input for cost optimization

Flow from query execution to history retrieval

Check your context (a minimal, safe-to-run command)

SELECT current_user() AS user, current_catalog() AS catalog, current_schema() AS schema;

Choosing How to Retrieve History: UI / REST API / System Tables

Three pillars to consider when pulling query history: the UI Query history, the REST API, and Unity Catalog System Tables (audit). For operational automation and cross-cutting analysis, lean on the REST API or System Tables; for immediate troubleshooting, the UI is quickest.

With the REST API, you specify filter conditions (time range, user, Warehouse, etc.) and paginate the results, then land them in a Delta table to make secondary use easy. System Tables shine as a governance foundation, letting you aggregate history and audit events across the org with SQL.

• UI: best for manual investigation and confirmation
• REST API: best for scheduled retrieval and dashboarding
• System Tables: best for org-wide audit, aggregation, and correlation analysis

Retrieve query history via REST API and land it in Delta (example)

curl -s -X POST \
  -H "Authorization: Bearer DAPIxxxxxxxx" \
  -H "Content-Type: application/json" \
  https://YOUR-WORKSPACE.cloud.databricks.com/api/2.0/sql/history/queries \
  -d '{
        "filter_by": {
          "query_start_time_range": {
            "start_time_ms": 1719878400000,
            "end_time_ms": 1719964800000
          }
        },
        "max_results": 1000
      }'

# Process the returned JSON in a notebook, shape it with from_json, and write to Delta — the standard pattern.
# Refer to the latest documentation for the actual request/response details.

Audit: Who Ran What, Where, and When

Step one of audit is identifying the user, time window, source (IP/client), and the target catalog/schema/table. The UI makes it easy to trace responsibility for individual queries, but for organization-wide trends and longitudinal checks, combining with System Tables is much more powerful.

When Unity Catalog System Tables (e.g. system.access.audit) are enabled, you can produce audit trails with SQL alone. Field names and structure can differ across environments, so in practice it's smart to confirm the real schema with DESCRIBE and pair it with JSON extraction functions.

• Minimum set: user, timestamp, Warehouse, SQL text, result (success/failure)
• Sensitive info: handle SQL text per internal policy
• Long-term retention: pull regularly via REST API, store in Delta, and apply permissions

Audit aggregation with System Tables (representative example, verify the real schema)

SELECT
  event_time,
  user_identity.email       AS user_email,
  service_name,
  action_name,
  request_params:warehouse_id    AS warehouse_id,
  request_params:statement_text  AS sql_text,
  response_status:status_code    AS status_code
FROM system.access.audit
WHERE service_name = 'sql'
  AND action_name LIKE 'execute%'
  AND event_time >= TIMESTAMP('2024-06-01')
ORDER BY event_time DESC
LIMIT 200;

-- Note: JSON field names like request_params/response_status may differ across environments.

Performance Analysis: Finding Slow Queries and Fixing Them

In practice, what proves useful is identifying p95 duration, retry frequency, and the long-form SQL templates that dominate. For ad-hoc root-cause isolation, use the UI Query history and Query profile; for structural improvements, grab the execution plan with EXPLAIN.

For repeatedly slow patterns, validate the effect of appropriate partition filters, pre-computing aggregations, revisiting join-key cardinality, and Delta optimizations (right-sizing files, Z-ORDER, etc.).

• Start with Query profile in the UI to check scan volume and stage time
• Templatize similar queries to crush the root cause
• Inspect join order and filter pushdown with EXPLAIN

Capture the execution plan with EXPLAIN (Databricks SQL)

EXPLAIN
SELECT c.customer_id, SUM(o.amount) AS revenue
FROM sales.orders o
JOIN sales.customers c ON o.customer_id = c.customer_id
WHERE o.order_date >= DATE_SUB(CURRENT_DATE(), 30)
GROUP BY c.customer_id;

Cost Management and SLA: Operating Warehouses and Reading the History

Query history is the evidence base for adjusting Warehouse size, concurrency, and auto-stop. Understand the relationship between concurrent execution and latency during peak hours, and revisit scale-up/out or scheduling as needed.

For continuous monitoring, the practical approach is to land REST API-retrieved history in Delta and dashboard query count, failure rate, and p95 duration. Early on, daily batches are fine; once mature, incremental collection on a ~15-minute cadence is a good balance.

• Correlate peak-hour concurrency with latency
• Compare "small Warehouse, long execution" vs. "large Warehouse, short execution"
• Tune auto-stop/auto-start thresholds from history

Survey query counts at 5-minute granularity from audit logs (representative example)

WITH events AS (
  SELECT
    window(event_time, '5 minutes') AS w,
    COUNT_IF(action_name LIKE 'execute%') AS qps
  FROM system.access.audit
  WHERE service_name = 'sql'
    AND event_time >= date_sub(current_timestamp(), 7)
  GROUP BY window(event_time, '5 minutes')
)
SELECT w.start AS window_start, w.end AS window_end, qps
FROM events
ORDER BY window_start;

Data Analyst Exam Angles and Common Pitfalls

The exam tends to test "operational playbooks" — how to choose between UI, REST API, and System Tables, and how to triage slow queries (EXPLAIN, applying filters, join strategy). The weight is on selecting the right tool for each use case, not memorizing specific endpoints.

Watch out for terminology confusion. Query history is a list of execution logs; table-change history (Delta's DESCRIBE HISTORY) is a different thing. Keep in mind that audit and performance analysis can draw from different data sources.

• UI for single-incident response, REST API for continuous monitoring, System Tables for audit aggregation
• For latency, use Query profile first; for sustained fixes, revisit EXPLAIN and data layout
• Don't confuse Delta table history with SQL query history

Aggregate p95 duration (assuming history is already in Delta)

SELECT
  date_trunc('day', start_time) AS day,
  APPROX_PERCENTILE(duration_ms, 0.95) AS p95_ms,
  COUNT(*) AS total_queries
FROM analytics.query_history_delta
WHERE start_time >= date_sub(current_date(), 14)
GROUP BY 1
ORDER BY 1;

Check Your Understanding

Frequently Asked Questions