Applies toDatabricks SQL (serverless, pro, classic warehouses), Power BI/Tableau connected via ODBC/JDBC
1. Warehouse types
| Type | Start time | Best for |
|---|---|---|
| Serverless | Seconds | Most BI and ad-hoc work; intelligent workload management; fast auto-stop |
| Pro | Minutes | When serverless isn't available or allowed in your region/network |
| Classic | Minutes | Legacy; fewer features |
Default to serverless unless networking or policy prevents it.
2. Size vs scale
- Size (T-shirt) = how big each cluster is → match to query complexity and data volume. If single queries spill or take long, size up.
- Scaling (min/max clusters) = how many clusters → match to concurrency. If queries queue, raise max clusters.
databricks warehouses edit 1234abcd --cluster-size Medium --min-num-clusters 1 --max-num-clusters 4 --auto-stop-mins 10
3. Find what to fix: query history
SELECT statement_id, executed_by, compute.warehouse_id,
total_duration_ms / 1000 AS secs, read_bytes / 1e9 AS gb_read,
waiting_at_capacity_duration_ms / 1000 AS queued_secs, statement_text
FROM system.query.history
WHERE start_time >= current_date() - 7
ORDER BY total_duration_ms DESC LIMIT 20;
| Pattern | Action |
|---|---|
High queued_secs | More max clusters (concurrency) |
Few heavy queries, large gb_read | Table layout (clustering), filters, materialised views; larger size |
| Same query repeated by many users | Result cache is working? Check dashboards don't add random parameters |
| Many tiny queries from BI tools | BI tool settings (query reduction, aggregations) |
4. Caching
- Query result cache: identical queries on unchanged data return instantly.
- Disk cache on warehouse nodes for remote data.
- For heavy repeated aggregations, use materialised views (refreshed on a schedule) instead of recomputing per dashboard load.
CREATE MATERIALIZED VIEW prod_sales.reporting.daily_sales
SCHEDULE CRON '0 30 5 * * ?'
AS SELECT order_date, region, SUM(amount) AS revenue, COUNT(*) AS orders
FROM prod_sales.orders.orders GROUP BY order_date, region;
5. Power BI specifics
- Use DirectQuery with aggregations or Import for small models; avoid DirectQuery on huge detailed tables without filters.
- Use the Databricks connector with Cloud Fetch (default) and a dedicated warehouse for BI.
- Push heavy logic into gold tables / materialised views, not DAX over raw data.
6. Cost controls
- Auto-stop 5–10 min (serverless); avoid "always on" unless a strict SLA needs it.
- Separate warehouses for BI, ad-hoc and ELT so they can be sized and attributed separately; tag them.
- Query timeouts (
STATEMENT_TIMEOUT) to stop runaway queries.
Tip
Most "slow dashboard" tickets are fixed in the data model, not the warehouse: a pre-aggregated gold table or a materialised view usually beats doubling the warehouse size, at a fraction of the cost.