The Top 10 Best Practices for AI/BI Dashboards Performance Optimization (Part 2)

This post is the second part of a two-part series on optimizing Databricks AI/BI dashboard performance at scale. 

In the previous post, we focused how layout, filters, parameters, and caching determine how much work the system does for every click. Those optimizations are often enough to make dashboards feel fast. 

In this post, we shift the focus to the platform foundations that keep them fast as usage grows. We’ll look at warehouse selection and sizing, data modeling and schema choices, file layout and clustering, and when to rely on materialization instead of recomputation to achieve stable, predictable performance.

Optimization #6: Choose the warehouse configuration that matches the design 

Match your dashboard shape (pages, query mix, user burstiness) to the DBSQL warehouse type and sizing so the system admits work quickly without queueing.

Because visible widgets submit together, dashboards naturally generate short-lived concurrency spikes. If the warehouse can’t absorb the burst, you’ll see queueing (Peak Queued Queries > 0) and inconsistent tile load times—especially at peak hours.

How DBSQL warehouse concurrency works

• Serverless + IWM: Serverless uses Intelligent Workload Management to predict cost, admit and prioritize queries, and autoscale rapidly. Typical startup is 2–6 seconds, so bursts stay low‑latency without manual tuning.
• Pro/Classic: Fixed “10 concurrent queries per cluster” gate, autoscaling adds clusters on minute‑level thresholds, and startup is multi‑minute, plan capacity by expected concurrency and avoid surges at page load.
• Monitor and right‑size: Watch Peak Queued Queries and Query History, if peaks persist above 0, increase cluster size (especially if Query Profile shows spill to disk) or raise max clusters. 

Why Serverless first

• Serverless recommended for interactive BI: fastest startup, dynamic concurrency via IWM, and more efficient IO.

Practical sizing heuristics

• Start larger (cluster size) and size down after testing, let Serverless autoscaling handle concurrency bursts, and increase max clusters only when queue peaks persist.
• Keep heavy ETL and BI separate: dedicate Serverless warehouses per workload/domain (avoid cache pollution and let IWM learn the workload pattern).
• Prioritize small, frequent queries: Serverless IWM protects short interactions and scales quickly during mixed loads; design pages so the Overview executes the lightest tiles first. 

For more details, see: https://docs.databricks.com/aws/en/compute/sql-warehouse/warehouse-behavior 

Optimization #7: Apply data modelling best practices

Well-designed data models are a foundational performance feature for AI/BI dashboards. The star schema remains the most effective and predictable modeling pattern for interactive analytics because it aligns directly with how BI queries are written and optimized.

In a star schema, a central fact table contains measurable events (sales, transactions, clicks) and joins to surrounding dimension tables (date, customer, product, region). This structure minimizes join complexity, reduces data duplication, and enables efficient aggregations with simple, stable query patterns. As a result, dashboards execute fewer joins, scan less data, and benefit more consistently from caching and query optimization.

A critical but often overlooked detail is join key data types. Dimension and fact table joins should use integer-based surrogate keys, not strings. Integer joins are significantly faster, require less memory, improve cache efficiency, and allow Photon to execute joins using highly optimized vectorized paths. String-based joins increase CPU cost and can become a hidden bottleneck as data and concurrency grow.

In Databricks, this pattern fits naturally with the Lakehouse architecture. The gold layer should be modeled as facts and dimensions stored as Unity Catalog tables, providing a governed, reusable semantic foundation for AI/BI dashboards, metric views, and materialized views.

The takeaway is simple: model for how BI queries actually run. A star schema with integer join keys in the gold layer delivers simpler SQL, faster joins, and more predictable performance at scale.

Optimization #8: Parquet Optimization techniques

Design your data layout so dashboards read far less data per query, then let the engine exploit Parquet stats and selective reads. 

Treat file layout as a performance feature

Databricks SQL is fastest when it can:

• prune whole files using metadata (min/max stats),
• read large, contiguous chunks efficiently,
• and avoid opening thousands of tiny files.

So the two biggest wins are: compact files into optimal sizes, and
cluster data so predicates prune files.

More details are available here: https://www.databricks.com/discover/pages/optimize-data-workloads-guide

A classic anti-pattern: a dashboard filter like WHERE customer_id = ? looks selective, but if the data isn’t clustered, the engine still touches a huge portion of files because the matching rows are scattered everywhere.

Techniques

• Use Photon to benefit from built‑in Predictive IO on Parquet: Photon applies AI‑assisted selective reading and parallel IO to skip non‑matching blocks and list fewer files, delivering fast selective scans without manual indexing.
• Enable Predictive Optimizations for managed tables: Databricks can automatically schedule and execute table maintenance based on observed workload patterns—OPTIMIZE (compaction to keep file sizes healthy), ANALYZE (fresh stats), VACUUM (cleanup), and Liquid Clustering (adaptive layout)—freeing you from manual tuning while improving read price/performance at scale. In practice, this helps keep file sizes healthy by proactively compacting small files (via OPTIMIZE) so Parquet metadata (footers + min/max stats) remains effective for data skipping, selective scans, and BI scans/concurrency.
• Trigger the same operations manually when needed: You can still run them yourself when you need tighter control or faster time-to-benefit (e.g., after large ingests/backfills, schema changes, or before a known reporting peak) by running commands like OPTIMIZE and ANALYZE. The key is to be intentional: align maintenance cadence with how frequently the table changes, and ensure the compute cost is justified by downstream gains in concurrency, latency, and scan efficiency.
• Adopt Liquid Clustering instead of heavy partitioning. Liquid incrementally clusters data for point lookups and selective scans, and you can change clustering columns any time (even high‑cardinality) without a full rewrite, the layout adapts as usage evolves.
• Align layout to dashboard predicates. Choose Liquid clustering columns that mirror common filter/group‑by dimensions (e.g., date, customer, region) so Predictive IO can skip big swaths for “Investigate” and “Deep dive” pages. 

Result: fewer files touched, more blocks skipped, and shorter wall‑clock for the same insights, without brittle indexes or manual tuning. 

For more details, see: 

• https://www.databricks.com/blog/faster-lakeview-dashboards-materialized-views
• https://docs.databricks.com/aws/en/compute/photon
• https://docs.databricks.com/aws/en/delta/data-skipping
• https://docs.databricks.com/aws/en/delta/optimize 

Optimization #9: Make use of Metric View Materialization

In Optimization #7: Apply data modeling best practices, we focused on the importance of the star schema with clearly defined facts, dimensions, and KPIs. Metric Views are a direct continuation of these principles in Databricks AI/BI.

Metric Views are designed around BI semantics: they consist of measures and dimensions, making them a natural abstraction for modeling KPIs. They allow teams to define business metrics once and reuse the same KPIs consistently across multiple dashboards, agents, and other client tools. This reduces duplication, prevents KPI drift, and keeps analytical logic aligned as adoption grows.

With Materilization for Metric Views, Databricks automatically precomputes and maintains frequently used aggregates. These aggregates are updated incrementally, and at query time the optimizer transparently rewrites dashboard queries to the best matching precomputed result. As a result, dashboards queries scan far less data per interaction - without requiring teams to manage separate aggregation tables or custom pipelines.

If Metric Views are not used, the same approach can be applied with Materialized Views. For example, an aggregated version of a large fact table can be precomputed and stored, allowing dashboards to query a much smaller, optimized dataset. This significantly improves performance by reducing the amount of data scanned and avoids repeatedly recomputing expensive aggregations for each user interaction.

All of these techniques optimize the same thing: scanning less data. By defining KPIs once and precomputing frequently used aggregates with Metric Views or Materialized Views, dashboards avoid repeatedly aggregating large fact tables. Fewer scanned bytes translate directly into faster queries, more predictable latency, and better performance at scale.

For more details, see: 

• https://docs.databricks.com/aws/en/metric-views/materialization

Optimization #10: Optimize data types

Data types directly influence how much data Databricks SQL has to read, move, and process for every dashboard query. Even with perfect SQL and caching, inefficient data types quietly increase IO, memory pressure, and CPU cost—showing up as slower tiles and reduced concurrency.

Under the hood, Databricks SQL operates on columnar Parquet files. Smaller, well-chosen data types mean:

• Less data scanned from storage (narrower columns),
• Better cache density (more values fit in memory and result cache),
• Faster vectorized execution in Photon (SIMD-friendly layouts),
• More effective data skipping, because min/max statistics are tighter.

A few mechanics matter most:

• Use INT / BIGINT instead of STRING for identifiers wherever possible. Strings are expensive to scan, compare, and cache; numeric keys are orders of magnitude cheaper.
• Prefer DATE or TIMESTAMP over string-based dates. Native temporal types enable predicate pushdown, efficient comparisons, and better pruning.
• Use the smallest numeric type that fits (INT vs BIGINT, FLOAT vs DOUBLE) to reduce column width and memory footprint.
• Avoid overusing DECIMAL with excessive precision in BI-facing tables unless required; high-precision decimals increase CPU cost during aggregation.
• Keep schemas clean and stable. Implicit casts (for example STRING → INT at query time) disable optimizations and add unnecessary compute on every execution.

In BI workloads, these choices compound quickly: a dashboard page may execute dozens of queries, each scanning millions of rows. Narrow, well-typed columns reduce scan time, improve cache hit rates, and allow Photon to operate at peak efficiency.

Rule of thumb: treat schema design as a performance feature. Optimize data types once in the Lakehouse, and every dashboard—current and future—benefits automatically.

Conclusion

The theme across all ten best practices is simple: stop paying the full price of a dashboard interaction every time. Make the system do less work per view (less fan-out, less data scanned), and make the work it does reusable (shared datasets, deterministic queries, caches, and precomputed aggregates). When those pieces line up, performance becomes stable under concurrency—and cost becomes predictable.

Actionably, you should be able to answer “yes” to these questions for your most-used dashboards:

• Do users get a fast first paint (light landing view + sensible defaults)?
• Does a typical interaction trigger a small number of cheap queries (parameters filter early, not after scanning)?
• Are repeat views turning into cache hits (deterministic tiles, reuse across tiles, scheduled warm-up)?
• Can the warehouse absorb peak load without queueing or spilling (Peak Queued Queries stays near zero, Query Profile doesn’t spill)?
• Is the Lakehouse optimized for reads (healthy file sizes, Liquid Clustering, clean data types, and precomputed hot aggregates)?

Pick one dashboard with real adoption, run a quick baseline (first paint, interaction latency, Peak Queued Queries, spill, cache hit rate), apply a couple of the highest-leverage changes, and re-measure. Do that consistently, and you’ll move from “sometimes fast” to reliably fast AI/BI as data and users grow.

References and further reading

• SQL warehouse sizing, scaling, queuing, and monitoring, Serverless IWM vs Pro/Classic autoscaling. https://docs.databricks.com/aws/en/compute/sql-warehouse/warehouse-behavior
• Create/configure warehouses, clusters‑per‑concurrency guidance.
  https://docs.databricks.com/aws/en/compute/sql-warehouse/create
• Serverless advantages and why it’s recommended.
  https://docs.databricks.com/aws/en/compute/sql-warehouse/
• Warehouse types and performance features (Photon, Predictive IO, IWM, startup 2–6s for Serverless).
  https://docs.databricks.com/aws/en/compute/sql-warehouse/warehouse-types
• Result caching (per‑cluster and Serverless remote result cache).
  https://docs.databricks.com/aws/en/lakehouse-architecture/performance-efficiency/best-practices https://learn.microsoft.com/en-us/azure/databricks/sql/user/queries/query-caching
• AI/BI dashboards: pages/canvas/data model and components.
  https://docs.databricks.com/aws/en/dashboards
• Parameters vs field filters, query‑based parameters.
  https://docs.databricks.com/aws/en/dashboards/parameters
• Export a dashboard dataset to a materialized view to speed heavy workloads.
  https://docs.databricks.com/aws/en/ai-bi/release-notes/2025
• Dashboard query executions in audit logs (verify what runs on load).
  https://docs.databricks.com/aws/en/admin/account-settings/audit-logs