Optimization: faster dashboards on Databricks SQL with materialized views, streaming tables and result caching

#Find the repeated heavy queries

-- Most expensive repeated statements on SQL warehouses (last 7 days)
SELECT statement_text,
       COUNT(*) AS runs,
       AVG(total_duration_ms) / 1000 AS avg_sec,
       SUM(read_bytes) / 1e9 AS gb_read
FROM system.query.history
WHERE start_time >= current_date() - 7
  AND compute.type = 'WAREHOUSE'
GROUP BY statement_text
HAVING COUNT(*) > 20
ORDER BY gb_read DESC
LIMIT 20;

Dashboard queries with the same shape (same aggregations, different filters) are prime candidates.

#Materialized views

CREATE MATERIALIZED VIEW gold.mv_daily_sales
SCHEDULE CRON '0 0 5 * * ?' AT TIME ZONE 'UTC'
AS
SELECT order_date, region, product_category,
       SUM(amount) AS revenue, COUNT(*) AS orders
FROM silver.orders
GROUP BY order_date, region, product_category;

• Refreshes can be incremental when the query and source support it (e.g. Delta sources with row tracking/change data feed); otherwise a full recompute.
• Refresh runs on serverless pipelines compute; include it in cost tracking.
• Point dashboards at the materialized view instead of the raw fact.

#Streaming tables

For append-only data arriving continuously (events, logs), streaming tables ingest incrementally:

CREATE OR REFRESH STREAMING TABLE bronze.app_events
SCHEDULE CRON '0 0/15 * * * ?' AT TIME ZONE 'UTC'
AS SELECT * FROM STREAM read_files('abfss://landing@stlake.dfs.core.windows.net/events/', format => 'json');

#Caching layers

#Design the dashboard too

• Fewer visuals per page; each visual is a query.
• Default filters to recent periods.
• Avoid high-cardinality slicers on huge tables.

Tip

Track warehouse cost and p95 dashboard query time before and after. Materialized views trade refresh compute for query compute; it pays off when many users hit the same aggregates.

#Checklist

• Repeated heavy queries identified
• Materialized views created with refresh schedules
• Streaming tables for continuous appends
• Dashboards repointed
• Caching layers understood and used
• Before/after cost and latency measured