Skip to main content

05.8 SQL Query History, Monitoring & Cost Optimization

A production SQL platform needs more than fast queries. You need to know who is running what, how long it takes, what it costs, where capacity is being consumed, and whether performance is getting worse.

Databricks SQL monitoring and cost optimization architecture

1. Learning objectives​

By the end of this topic you should be able to:

  • Use the Query History UI and system.query.history.
  • Build operational metrics for duration, failures, queue time, I/O, and throughput.
  • Attribute workload usage with query and compute tags.
  • Correlate queries, warehouse events, and billing usage.
  • Understand DBUs, SKU-based billing records, and list-price estimates.
  • Build team, project, application, and environment cost views.
  • Diagnose performance regressions and capacity problems.
  • Design cost guardrails without damaging developer productivity.
  • Build an enterprise SQL observability operating model.

2. The enterprise monitoring model​

SQL workload
    ↓
Query History
    ↓
Performance metrics ──→ Query Profile
    ↓
Workload attribution ──→ Tags
    ↓
Warehouse events
    ↓
Billing usage
    ↓
Dashboards / alerts / budgets
    ↓
Optimization decisions

Databricks currently provides system.query.history for query records, system.compute.warehouse_events for warehouse lifecycle/scaling events, system.compute.warehouses for warehouse configuration history, and system.billing.usage for billable usage. citeturn304353search1turn304353search5

3. Query History UI​

The Query History page lets you filter previous runs by user, date range, compute, duration, status, statement type, statement ID, and query tags. Query details include the statement, duration, rows returned, I/O metrics, and access to Query Profile. citeturn304353search3

Use it for the first response to a production incident:

Is it slow?
  ↓
Which query?
  ↓
Which user/application?
  ↓
Which warehouse?
  ↓
How long?
  ↓
Where is time spent?
  ↓
How much data moved?

Important metrics​

  • Wall-clock duration
  • Execution duration
  • Queue/capacity waiting
  • Rows read
  • Rows returned
  • Input/output volume
  • Query status
  • Query source
  • Query tags

4. system.query.history​

The query history system table is the foundation for SQL workload observability. It contains records for SQL warehouses and supported serverless compute workloads. Records are typically available within about one hour, and the table retains 365 days of regional history. citeturn304353search5turn304353search4

Example: recent slow queries

SELECT
  statement_id,
  workspace_id,
  executed_by,
  warehouse_id,
  execution_status,
  total_duration_ms,
  execution_duration_ms,
  waiting_at_capacity_duration_ms,
  read_rows,
  produced_rows,
  statement_text
FROM system.query.history
WHERE start_time >= NOW() - INTERVAL 1 DAY
ORDER BY total_duration_ms DESC
LIMIT 50;

Production interpretation​

A 15-minute query is not necessarily a 15-minute compute problem.

Total duration
├── queue / capacity wait
├── planning / optimization
├── execution
└── other scheduling overhead

Use the breakdown to identify the actual bottleneck.

5. Failed query monitoring​

SELECT
  execution_status,
  COUNT(*) AS query_count
FROM system.query.history
WHERE start_time >= NOW() - INTERVAL 7 DAY
GROUP BY execution_status
ORDER BY query_count DESC;

Failure rate is more useful when segmented by workload:

SELECT
  executed_by,
  COUNT(*) AS total_queries,
  SUM(CASE WHEN execution_status = 'FAILED' THEN 1 ELSE 0 END) AS failures
FROM system.query.history
WHERE start_time >= NOW() - INTERVAL 7 DAY
GROUP BY executed_by
ORDER BY failures DESC;

For an enterprise platform, trend failure rate rather than reacting only to individual incidents.

6. Slow-query SLOs​

Define operational targets such as:

MetricExample SLO
Dashboard p95< 10 sec
Critical report p95< 60 sec
ETL query success> 99%
Capacity waiting< 5% of runtime
Failed production queries< 1%

The numbers are examples, not universal Databricks limits. Set targets from business requirements and workload characteristics.

7. Query tags — the bridge to FinOps​

Query tags are custom key-value pairs that can group SQL workloads by business context and track costs. Tags appear in system.query.history and the Query History UI. citeturn304353search0

Example taxonomy:

domain:finance
team:analytics
project:sales360
environment:prod
application:powerbi
cost_center:FIN-042

Design rules​

  1. Use stable controlled values.
  2. Do not put secrets or sensitive data in tags.
  3. Keep values short and searchable.
  4. Define ownership for the taxonomy.
  5. Make tags part of the platform operating model.

8. Cost attribution​

A useful enterprise chain is:

Business unit
   ↓
Project / application
   ↓
Query tags
   ↓
Query history
   ↓
Warehouse / serverless usage
   ↓
system.billing.usage
   ↓
Chargeback / showback

Databricks recommends tags for attributing usage to teams, projects, workspaces, or users. citeturn304353search11turn304353search17

Showback vs chargeback​

Showback: report consumption without directly billing the team.

Chargeback: assign actual internal cost responsibility.

Start with showback when cost data quality is immature. Move to chargeback once tagging and ownership are reliable.

9. system.billing.usage​

The billable usage system table contains account-level billing records, including usage metadata, identity metadata, and custom tags. It is the source of truth for actual billable usage. citeturn304353search12turn304353search2

Example: DBUs by day and SKU:

SELECT
  usage_date,
  sku_name,
  ROUND(SUM(usage_quantity), 2) AS total_dbus
FROM system.billing.usage
WHERE usage_date >= CURRENT_DATE() - 30
GROUP BY usage_date, sku_name
ORDER BY usage_date DESC, total_dbus DESC;

Do not treat DBUs as dollars by themselves. Price depends on SKU, contract, discounts, cloud infrastructure, and pricing terms.

10. Estimating list-price cost​

Databricks provides system.billing.list_prices so usage can be joined to historical pricing. citeturn304353search1

Conceptually:

SELECT
  u.usage_date,
  u.sku_name,
  SUM(u.usage_quantity) AS dbus,
  SUM(u.usage_quantity * p.pricing.default) AS estimated_list_cost
FROM system.billing.usage u
LEFT JOIN system.billing.list_prices p
  ON u.sku_name = p.sku_name
 AND u.usage_start_time >= p.price_start_time
 AND u.usage_start_time < COALESCE(p.price_end_time, NOW())
WHERE u.usage_date >= CURRENT_DATE() - 30
GROUP BY u.usage_date, u.sku_name;

This is a list-price estimate, not necessarily the final invoice amount.

11. Warehouse events​

system.compute.warehouse_events captures events such as warehouse start, stop, scale-up, and scale-down. citeturn304353search1

This is valuable because cost is a time-series behavior, not just a monthly number.

Queries arrive
      ↓
Warehouse scales up
      ↓
Concurrency improves
      ↓
Workload falls
      ↓
Warehouse scales down
      ↓
Idle cost avoided

Correlate scaling behavior with query volume before changing warehouse configuration.

12. Warehouse configuration history​

system.compute.warehouses contains configuration snapshots over time. citeturn304353search1

This allows you to investigate questions such as:

  • When was the warehouse resized?
  • Did performance change after the change?
  • Did auto-stop settings change?
  • Was scaling configuration altered?
  • Did a team increase capacity without reducing query cost?

This is essential for performance regression analysis.

13. Query cost is not only query duration​

Consider two queries:

QueryRuntimeResource behavior
A30 secefficient, small scan
B30 sechuge scan, high concurrency

Same runtime does not imply same cost or platform impact.

Therefore optimize for a multidimensional objective:

Latency + throughput + reliability + resource consumption + business value

14. Cost optimization framework​

Use five levers:

Lever 1 — Read less​

  • Reduce scanned data.
  • Select required columns.
  • Improve pruning and table layout.

Lever 2 — Compute less​

  • Remove unnecessary transformations.
  • Pre-aggregate where justified.
  • Avoid repeated expensive computation.

Lever 3 — Run efficiently​

  • Choose appropriate warehouse/compute.
  • Use autoscaling appropriately.
  • Avoid unnecessary always-on capacity.

Lever 4 — Attribute​

  • Tag workloads.
  • Assign owners.
  • Build showback dashboards.

Lever 5 — Govern​

  • Budgets.
  • Alerts.
  • Policies.
  • Review cycles.

15. Serverless and warehouse selection​

Current Databricks guidance generally recommends serverless compute for many workloads because of faster startup, automatic scaling, and lower management overhead; serverless SQL warehouses are recommended for BI, ETL, and exploratory SQL workloads where available. citeturn304353search6

But architecture is not simply “serverless is always cheaper.” Evaluate:

  • workload shape
  • concurrency
  • startup sensitivity
  • network requirements
  • supported features
  • data access model
  • actual measured cost

For example, Pro SQL warehouses can be appropriate when custom networking is required. citeturn304353search6

16. Capacity waiting vs execution time​

Suppose:

Query A
Execution: 20 sec
Capacity wait: 2 sec
Total: 22 sec

Query B
Execution: 20 sec
Capacity wait: 90 sec
Total: 110 sec

Rewriting Query B may not solve the primary problem. The platform has a capacity/concurrency issue.

Architect decision:

High execution time → tune query
High capacity wait → tune concurrency/capacity
Both high → address both

17. Cost anomaly detection​

A simple daily trend can expose unusual consumption:

SELECT
  usage_date,
  SUM(usage_quantity) AS dbus
FROM system.billing.usage
WHERE usage_date >= CURRENT_DATE() - 60
GROUP BY usage_date
ORDER BY usage_date;

Then compare recent consumption against a baseline.

Example anomaly signals:

  • DBUs suddenly double.
  • One project consumes most incremental spend.
  • A new warehouse stays active overnight.
  • Query volume is stable but cost rises.
  • A release increases bytes scanned per query.

18. Cost per query and cost per business outcome​

Raw DBUs are useful, but business metrics are better.

Cost / dashboard refresh
Cost / million rows processed
Cost / ETL run
Cost / successful pipeline
Cost / 1,000 customer insights

This changes the discussion from infrastructure spend to business efficiency.

19. Enterprise SQL observability dashboard​

Build a dashboard with five pages:

Page 1 — Executive​

  • Daily/monthly spend
  • DBU trend
  • Top cost centers
  • Budget variance

Page 2 — Workload​

  • Query count
  • p50/p95/p99 duration
  • failure rate
  • top queries

Page 3 — Warehouse​

  • utilization
  • queue time
  • scaling events
  • configuration changes

Page 4 — Cost attribution​

  • team
  • project
  • application
  • environment
  • workspace

Page 5 — Optimization​

  • highest scan volume
  • highest shuffle/resource consumption
  • repeated slow queries
  • regression candidates

20. Alert design​

Avoid alerting on every slow query.

Better alerts:

  • Critical workload failure rate crosses threshold.
  • p95 latency increases materially for a key dashboard.
  • Capacity waiting exceeds target.
  • Daily spend exceeds budget trajectory.
  • A new workload produces an abnormal scan volume.
  • A warehouse remains active outside expected operating hours.

An alert should answer three questions:

What happened?
Why should I care?
Who owns it?

21. Budgets and guardrails​

Databricks budgets can monitor spend and enforce usage thresholds in supported scenarios. Budget estimates can differ temporarily from system.billing.usage; Databricks identifies the billing system table as the source of truth for actual billable usage. citeturn304353search16

Use layered controls:

Budget
  ↓
Alert
  ↓
Owner notification
  ↓
Investigation
  ↓
Policy / workload correction

Do not make hard blocking the first response to every anomaly. Protect critical workloads while controlling discretionary consumption.

22. Query monitoring SQL toolkit​

Top slow queries​

SELECT
  statement_id, executed_by, total_duration_ms,
  execution_duration_ms, statement_text
FROM system.query.history
WHERE start_time >= NOW() - INTERVAL 7 DAY
ORDER BY total_duration_ms DESC
LIMIT 100;

Top users by query count​

SELECT executed_by, COUNT(*) AS queries
FROM system.query.history
WHERE start_time >= NOW() - INTERVAL 30 DAY
GROUP BY executed_by
ORDER BY queries DESC;

Capacity waiting​

SELECT
  statement_id,
  total_duration_ms,
  waiting_at_capacity_duration_ms
FROM system.query.history
WHERE start_time >= NOW() - INTERVAL 7 DAY
  AND waiting_at_capacity_duration_ms > 0
ORDER BY waiting_at_capacity_duration_ms DESC;

Tagged workload​

SELECT
  query_tags,
  COUNT(*) AS queries,
  AVG(total_duration_ms) AS avg_duration_ms
FROM system.query.history
WHERE start_time >= NOW() - INTERVAL 30 DAY
GROUP BY query_tags
ORDER BY queries DESC;

Always validate column names against the current system-table schema in your environment before productionizing monitoring SQL.

23. Hands-on labs​

Lab A — Build a slow-query report​

Use system.query.history to produce top 50 queries by duration and group them by owner.

Lab B — Failure SLO​

Calculate daily failed-query percentage and identify regression days.

Lab C — Capacity diagnosis​

Compare execution duration with capacity-wait duration and classify bottlenecks.

Lab D — Cost attribution​

Create a tag taxonomy and build a team/project cost report.

Lab E — Warehouse scaling​

Join warehouse events with query activity and identify idle or over-provisioned periods.

Lab F — Cost anomaly​

Create a 60-day DBU trend and flag days with abnormal consumption.

Lab G — Executive dashboard​

Build the five-page SQL observability dashboard described above.

24. Enterprise operating model​

A mature organization defines roles:

RoleResponsibility
Data engineerQuery/pipeline efficiency
Analytics engineerSQL/model quality
Platform engineerWarehouse reliability
FinOpsCost attribution and budgets
ArchitectPlatform standards and trade-offs
Product ownerBusiness SLOs

This avoids the anti-pattern where everyone sees cost but nobody owns it.

25. Architect reference architecture​

                ┌────────────────────┐
                │ BI / Apps / Jobs    │
                └─────────┬──────────┘
                          ↓
                ┌────────────────────┐
                │ SQL Warehouses     │
                └─────────┬──────────┘
                          ↓
              ┌───────────┴───────────┐
              ↓                       ↓
     system.query.history     warehouse events
              │                       │
              └───────────┬───────────┘
                          ↓
                ┌────────────────────┐
                │ Observability model│
                └─────────┬──────────┘
                          ↓
          ┌───────────────┼───────────────┐
          ↓               ↓               ↓
       Cost           Performance       SLOs
          │               │               │
          └───────────────┼───────────────┘
                          ↓
                Governance / FinOps

Billing usage is then joined to ownership dimensions to build enterprise chargeback/showback.

26. Common mistakes​

Mistake 1 — Looking only at monthly spend​

You need workload-level attribution to find causes.

Mistake 2 — Treating DBUs as invoice dollars​

Use pricing and contract context.

Mistake 3 — No tagging strategy​

Unattributed spend becomes difficult to govern.

Mistake 4 — Alerting on every query​

Alert fatigue destroys operational value.

Mistake 5 — Optimizing only latency​

A fast but extremely expensive query can still be a poor design.

Mistake 6 — Ignoring data latency​

System tables are operational data products; understand their availability windows.

27. Interview questions​

Foundation​

  1. What is system.query.history?
  2. What is system.billing.usage?
  3. What is the difference between query duration and capacity wait?
  4. Why are query tags useful?

Troubleshooting​

  1. How would you find the top 10 expensive workloads?
  2. How would you identify a warehouse capacity bottleneck?
  3. How would you detect a performance regression?
  4. How would you investigate a sudden DBU spike?

Architect​

  1. How would you design chargeback across 50 business units?
  2. How would you design SQL observability for 100+ warehouses?
  3. How would you balance cost optimization with dashboard SLOs?
  4. When would you prefer serverless versus Pro/classic warehouse?
  5. What governance controls would you implement without slowing developers?

28. Production checklist​

  • Query History is actively used for incident investigation.
  • system.query.history is available to the monitoring team.
  • Query/compute tags follow a controlled taxonomy.
  • Billing usage is integrated into cost reporting.
  • Warehouse events are monitored.
  • Query latency SLOs exist for critical workloads.
  • Failure-rate monitoring exists.
  • Capacity waiting is monitored.
  • Cost anomalies are detected.
  • Owners exist for major workloads.
  • Budget/alert controls are defined.
  • Performance and cost are reviewed together.

29. Key takeaway​

Enterprise SQL optimization ends when you can measure, attribute, explain, and govern the workload — not merely when one query becomes faster.

The architect mental model is: Observe → Attribute → Diagnose → Optimize → Govern → Repeat.

Next: 06. Delta Lake.

Official references​