| name | databricks-aibi-dashboards |
| description | Production-grade patterns for Databricks AI/BI (Lakeview) dashboards. Prevents visualization errors, deployment failures, and maintenance issues through widget-query alignment, number formatting, parameter configuration, monitoring table patterns, chart scale properties, and automated deployment workflows. Includes pivot tables with hierarchy drill-down and ratio metrics, point/choropleth maps, sankey diagrams, waterfall and histogram charts, cross-filtering and drill-through patterns, filter defaultSelection/disallowAll configuration, disaggregated vs aggregated query modes, and complete JSON templates for all widget types. |
| clients | ["ide_cli","genie_code"] |
| bundle_resource | dashboards |
| deploy_verb | bundle_deploy |
| deploy_note | AI/BI (Lakeview) dashboards deploy via `bundle deploy --target dev` (runDatabricksCli on Genie Code); .lvdash.json content MUST be base64(ascii) on import (B6) and every widget fieldName must match a SQL alias. On Genie Code, write the generated .lvdash.json under the cloned repo root (`{REPO_ROOT}` = `state_file_root` from `skills/vibecoding-state`, e.g. `resources/`), not a bare relative path — relative paths resolve against the page CWD (see `skills/genie-code-environment` §8). |
| coverage | full |
| metadata | {"author":"prashanth subrahmanyam","version":"4.1","domain":"monitoring","role":"worker","pipeline_stage":7,"pipeline_stage_name":"observability","called_by":["observability-setup"],"standalone":true,"keywords":["databricks","ai/bi","lakeview","dashboards","monitoring","visualization","deployment","widget configuration","sql validation","automation","production-grade"],"last_verified":"2026-04-16","volatility":"medium","upstream_sources":[{"name":"ai-dev-kit","repo":"databricks-solutions/ai-dev-kit","paths":["databricks-skills/databricks-aibi-dashboards/SKILL.md"],"relationship":"extended","last_synced":"2026-02-19","sync_commit":"97a3637"}]} |
Databricks AI/BI Dashboards
Overview
This skill provides comprehensive patterns for building production-grade Databricks AI/BI (Lakeview) dashboards. These patterns were developed from 100+ production deployments and prevent common visualization errors, deployment failures, and maintenance issues.
Core Philosophy: Self-Service Analytics
AI/BI Lakeview dashboards provide visual analytics for business users with no SQL required. This skill emphasizes:
- ✅ Visual insights for non-technical users
- ✅ Consistent metrics across the organization
- ✅ Self-service analytics without coding knowledge
- ✅ Professional, branded appearance
- ✅ Automated deployment with validation
- ✅ Error prevention through pre-deployment checks
Key Capabilities:
- Widget-query column alignment validation
- Number formatting rules (percentages, currency, plain numbers)
- Parameter configuration (time ranges, multi-select, text input)
- Monitoring table query patterns (window structs, CASE pivots, custom drift metrics)
- Chart configuration (pie, bar, line, area, table, pivot, point map, choropleth, sankey)
- Pivot tables with hierarchy expand/collapse and non-additive ratio metric support
- Map visualizations (point maps with lat/lng, choropleth by region)
- disaggregated vs aggregated query mode patterns for correct metric rollups
- Pre-deployment SQL validation (90% reduction in dev loop time)
- UPDATE-or-CREATE deployment pattern (preserves URLs and permissions)
- Variable substitution (no hardcoded schemas)
- Complete JSON templates for all widget types
- Phase-by-phase implementation guide
- Production example - Complete Jobs System Tables Dashboard demonstrating all patterns
When to Use This Skill
Use this skill when:
- Building AI/BI dashboards - Creating new dashboards with proper widget configurations
- Troubleshooting visualization errors - Fixing "no fields to visualize", empty charts, or formatting issues
- Deploying dashboards via API - Automating dashboard deployment with UPDATE-or-CREATE pattern
- Validating dashboard queries - Pre-deployment SQL validation to catch errors before deployment
- Querying monitoring tables - Accessing Lakehouse Monitoring profile and drift metrics
- Configuring parameters - Setting up time ranges, filters, and multi-select parameters
- Planning dashboard projects - Using templates to gather requirements and plan implementation
- Onboarding new developers - Teaching AI/BI dashboard best practices with working examples
Mandatory Skill Dependencies
Before building dashboard datasets, determine which query pattern the task requires:
| Query Pattern | Required Pre-Read | Trigger |
|---|
MEASURE() against Metric Views | MUST READ semantic-layer/01-metric-views-patterns/SKILL.md + references/metric-view-dashboard-queries.md | User mentions "MEASURE()", "Metric View", or semantic layer queries |
| Direct Gold table SQL | No additional skill needed | Querying fact/dimension tables directly |
| Monitoring / system table queries | MUST READ monitoring/01-lakehouse-monitoring-comprehensive/SKILL.md | Dashboard includes monitoring widgets |
Always load these common skills first (per AGENTS.md):
skills/databricks-expert-agent/SKILL.md — "Extract Don't Generate" principle, core SA behaviorcommon/naming-tagging-standards/SKILL.md — naming conventions for dashboards and datasets
Plan addendum filename: Dashboards are always planned in plans/phase1-addendum-1.5-aibi-dashboards.md. See planning/00-project-planning/assets/addendum-numbering.md for the canonical numbering table. The legacy name phase1-addendum-1.1-dashboards.md is forbidden — if you see it anywhere, replace it with 1.5-aibi-dashboards.md.
Complementary installed skills (check available_skills list):
databricks-lakeview-dashboard — comprehensive widget JSON patterns for 16+ chart types, mandatory "TEST EVERY QUERY" validation workflowdatabricks-lakeview-dashboard-analyzer — analyzing existing dashboards for patterns
🚀 Quick Start (2 Hours)
Goal: Create visual dashboards with AI-powered insights for business users
What You'll Create:
- SQL queries from Metric Views or Gold tables
- AI/BI Dashboard via UI (drag-and-drop layout)
- Auto-refresh schedule
Fast Track (UI-Based):
1. Navigate to: Databricks Workspace → Dashboards → Create AI/BI Dashboard
2. Add Data → Query Metric View or Gold table
3. Add Visualizations:
- Counter tiles for KPIs (Total Revenue, Units, Transactions)
- Bar charts for comparisons (Revenue by Store)
- Line charts for trends (Daily Revenue Trend)
- Tables for drill-down (Top Products Detail)
4. Add Filters (Date Range, Store, Category)
5. Configure Layout (Canvas: 1280px wide, tiles sized to fit)
6. Enable Auto-refresh (Hourly/Daily)
7. Share with business users
Query Pattern (Metric Views):
SELECT
store_name,
MEASURE(`Total Revenue`) as revenue,
MEASURE(`Total Units`) as units,
MEASURE(`Transaction Count`) as transactions
FROM sales_performance_metrics
WHERE transaction_date BETWEEN :start_date AND :end_date
ORDER BY revenue DESC
LIMIT 10
Best Practices:
- ✅ Use Metric Views (not raw tables) for consistent metrics
- ✅ Add filters for date range, key dimensions
- ✅ Counter tiles for top KPIs (large, prominent)
- ✅ Charts for trends and comparisons
- ✅ Auto-refresh for near real-time dashboards
Output: Professional dashboard with AI-powered insights
Time Estimate: 2-4 hours for complete dashboard
Production Example: See references/Jobs System Tables Dashboard.lvdash.json for a complete working example demonstrating all patterns from this skill.
📋 Project Planning Template
Use this template to gather requirements before building your dashboard.
Dashboard Purpose
- Dashboard Name: _________________ (e.g., "Sales Performance Dashboard", "Patient Outcomes Dashboard")
- Audience: _________________ (e.g., "Sales Managers", "Hospital Administrators", "Finance Team")
- Update Frequency: [ ] Real-time [ ] Hourly [ ] Daily [ ] Weekly
- Primary Goal: _________________ (e.g., "Track daily KPIs", "Monitor data quality", "Analyze trends")
Data Sources
- Catalog: _________________ (e.g., my_catalog)
- Schema: _________________ (e.g., my_project_gold)
- Primary Data Source: [ ] Metric View [ ] Gold Fact Table [ ] System Tables
- Table/View Name: _________________ (e.g., sales_performance_metrics, fact_sales_daily)
KPIs to Display (3-6 key metrics)
| # | KPI Name | Source Field | Format |
|---|
| 1 | Total Revenue | SUM(net_revenue) | Currency (USD) |
| 2 | _____________ | ______________ | _____________ |
| 3 | _____________ | ______________ | _____________ |
| 4 | _____________ | ______________ | _____________ |
Example - Retail:
- Total Revenue (Currency), Total Units (Number), Transaction Count (Number)
Example - Healthcare:
- Patient Count (Number), Readmission Rate (Percentage), Avg Length of Stay (Number)
Example - Finance:
- Transaction Volume (Number), Total Amount (Currency), Fraud Rate (Percentage)
Filters Required
| Filter Name | Type | Values Source |
|---|
| Date Range | Date Range | start_date, end_date |
| __________ | Single Select | Dimension table |
| __________ | Multi Select | Dimension table |
Common Filters:
- Date Range (always include)
- Location/Store/Facility (dimension)
- Category/Type (dimension)
- Status/State (dimension)
Charts to Include (3-5 visualizations)
| # | Chart Type | Purpose | Data |
|---|
| 1 | Line Chart | Revenue Trend | Daily revenue over time |
| 2 | Bar Chart | Top 10 by metric | Category comparison |
| 3 | _________ | ______________ | __________________ |
| 4 | _________ | ______________ | __________________ |
Chart Types Available:
- Line Chart (trends over time)
- Bar Chart (category comparisons)
- Pie Chart (distribution)
- Pivot Table (hierarchical drill-down with expand/collapse)
- Table (detailed flat data)
- Point Map (lat/lng store-level data)
- Choropleth Map (geographic aggregations by region)
- Sankey (flow/relationship diagrams)
- Counter/KPI (single metric)
Dashboard Pages
| Page Name | Purpose | Widgets |
|---|
| Overview | High-level KPIs | 6 KPIs + 2 charts |
| Details | Detailed analysis | 1 table + 2 charts |
| Global Filters | Cross-page filters | Date, dimensions |
Input Required Summary
- Gold layer tables or Metric Views
- KPI requirements (metrics to display)
- Filter requirements (date range, dimensions)
- Visualization preferences (charts, tables)
Quick Reference
Top 10 Critical Rules
| Rank | Issue | Prevention |
|---|
| 1 | Lakeview JSON Format | Datasets MUST use queryLines (array) + catalog + schema — NOT query (string). Textboxes MUST use multilineTextboxSpec.lines — NOT textbox_spec. Violating this causes ALL visuals to error. |
| 2 | Dataset columns for Hierarchy Pivots | Deep hierarchy pivots with cubeGroupingSets MUST use dataset columns array for ratio calculations + MEASURE() references in widget query + cell encodings (Pattern A). Do NOT use values encoding with cubeGroupingSets. |
| 3 | Widget-Query Column Mismatch | Always use explicit SQL aliases matching widget fieldName |
| 4 | Incorrect Number Formatting | Return raw numbers, not formatted strings |
| 5 | Missing Parameter Definitions | Define ALL parameters in dataset's parameters array |
| 6 | Ratio Metrics in Pivots | Use disaggregated: false. Always prefer Pattern A (dataset columns + MEASURE() + cell encoding) — it works in both the UI editor/draft mode and published view. Pattern B (inline expressions + values encoding) only renders when published and is invisible in the UI draft editor. Neither pattern uses transform. |
| 7 | Monitoring Table Schema | Use CASE pivots on column_name, access window.start |
| 8 | Map Coordinates Must Be Numeric | CAST lat/lng to DOUBLE upstream; STRING coordinates render blank maps |
| 9 | Metric View Column References | Use bare dimension name in queries — not source.col or dim.col prefixes |
| 10 | Bundle Deploy vs UI Draft | Bundle deploy updates published version; use API PATCH + publish to overwrite UI draft state |
Widget-Query Alignment
Rule: Widget fieldName MUST exactly match query output column alias.
SELECT COUNT(*) AS total_queries FROM ...
SELECT COUNT(*) AS query_count FROM ...
Number Formatting
| Format Type | Expects | Example |
|---|
number-plain | Raw number | 1234 → 1,234 |
number-percent | 0-1 decimal (×100) | 0.85 → 85% |
number-currency | Raw number | 1234.56 → $1,234.56 |
Never use: FORMAT_NUMBER(), CONCAT('$', ...), or CONCAT(..., '%') in queries.
Monitoring Table Patterns
SELECT window.start AS window_start FROM monitoring_table
WHERE window.start BETWEEN :time_range.min AND :time_range.max
SELECT
window.start AS window_start,
MAX(CASE WHEN column_name = 'success_rate' THEN avg END) AS success_rate_pct
FROM fact_job_run_timeline_profile_metrics
WHERE window.start BETWEEN :time_range.min AND :time_range.max
GROUP BY window.start
SELECT
window.start AS window_start,
success_rate_drift,
cost_drift_pct
FROM fact_job_run_timeline_drift_metrics
WHERE column_name = ':table' AND drift_type = 'CONSECUTIVE'
Chart Scale Requirements
Pie Charts:
{
"encodings": {
"color": { "fieldName": "category", "scale": { "type": "categorical" } },
"angle": { "fieldName": "value", "scale": { "type": "quantitative" } }
}
}
Bar Charts:
{
"encodings": {
"x": { "fieldName": "category", "scale": { "type": "categorical" } },
"y": { "fieldName": "value", "scale": { "type": "quantitative" } }
}
}
Critical Rules (Production-Grade)
⚠️ CRITICAL PRINCIPLES
These are non-negotiable for production dashboards:
- ✅ 6-Column Grid: NOT 12-column! Widths must be 1-6
- ✅ Version Specs: KPIs use v2, Charts use v3, Tables use v2, Pivots use v3, Choropleth Map (
choropleth-map) uses v1, Symbol Map (symbol-map) uses v2
- ✅ Global Filters: Cross-dashboard filtering on a dedicated page
- ✅ DATE Parameters: Static dates, not DATETIME with dynamic expressions
- ✅ Proper JOINs: Include workspace_id AND entity ID
- ❌ No 12-Column Grid: Widget widths are 1-6, never 1-12
- ❌ No Assumed Field Names: Verify system table schemas
Why This Matters:
- Visual insights for non-technical users
- Consistent metrics across the organization
- Self-service analytics (no SQL required)
- Professional, branded appearance
1. Grid System (6-Column, NOT 12!)
⚠️ ALWAYS Use 6-Column Grid (NOT 12!)
This is the #1 cause of widget snapping issues.
{
"position": {
"x": 0,
"y": 0,
"width": 3,
"height": 6
}
}
Grid Layout Patterns
{"x": 0, "y": 0, "width": 3, "height": 6}
{"x": 3, "y": 0, "width": 3, "height": 6}
{"x": 0, "y": 0, "width": 2, "height": 6}
{"x": 2, "y": 0, "width": 2, "height": 6}
{"x": 4, "y": 0, "width": 2, "height": 6}
{"x": 0, "y": 0, "width": 1, "height": 2}
{"x": 1, "y": 0, "width": 1, "height": 2}
{"x": 2, "y": 0, "width": 1, "height": 2}
{"x": 3, "y": 0, "width": 1, "height": 2}
{"x": 4, "y": 0, "width": 1, "height": 2}
{"x": 5, "y": 0, "width": 1, "height": 2}
{"x": 0, "y": 0, "width": 6, "height": 6}
Common Height Values
| Widget Type | Height |
|---|
| Filters | 1-2 |
| KPI Counters | 2 |
| Charts (standard) | 6 |
| Charts (large) | 9 |
| Tables | 6+ |
2. Widget-Query Column Alignment
MUST: Widget fieldName exactly matches query output alias.
Common Mismatches:
- Widget expects
total_queries, query returns query_count → Alias as total_queries
- Widget expects
warehouse_name, query returns compute_type → Alias as warehouse_name
- Widget expects
unique_users, query returns distinct_users → Alias as unique_users
Validation: Use validate_widget_encodings.py script before deployment.
3. Number Formatting
MUST: Return raw numbers, let widgets format them.
Rules:
- Percentages: Return 0-1 decimal (e.g.,
0.85 for 85%)
- Currency: Return raw numeric (e.g.,
1234.56 for $1,234.56)
- Never use
FORMAT_NUMBER() or string concatenation in queries
Example:
SELECT
COUNT(CASE WHEN status = 'success' THEN 1 END) * 1.0 / COUNT(*) AS success_rate
FROM job_runs
SELECT
CONCAT(ROUND(success_count * 100.0 / total_count, 2), '%') AS success_rate
FROM job_runs
4. Parameter Configuration
MUST: Define ALL parameters in dataset's parameters array.
Time Range Pattern
{
"keyword": "time_range",
"dataType": "DATETIME_RANGE",
"defaultSelection": {
"range": {
"min": { "dataType": "DATETIME", "value": "now-30d/d" },
"max": { "dataType": "DATETIME", "value": "now/d" }
}
}
}
SQL Access:
WHERE date BETWEEN :time_range.min AND :time_range.max
Date Parameters (Static Dates)
✅ Correct: DATE with Static Values
{
"displayName": "Start Date",
"keyword": "start_date",
"dataType": "DATE",
"defaultSelection": {
"values": {
"dataType": "DATE",
"values": [{"value": "2024-01-01"}]
}
}
}
❌ Wrong: DATETIME with Dynamic Expressions
{
"dataType": "DATETIME",
"values": [{"value": "now-12M/M"}]
}
5. Monitoring Table Schema
MUST: Use correct access patterns for monitoring tables.
Window Struct
- Use
window.start not window_start
- Access as
window.start AS window_start
Generic Metrics
- Use
CASE pivot on column_name field
- Pattern:
MAX(CASE WHEN column_name = 'metric_name' THEN avg END) AS metric_name
Custom Drift Metrics
- Stored as direct columns (not in
avg_delta)
- Filter:
WHERE column_name = ':table' AND drift_type = 'CONSECUTIVE'
Complete Example:
SELECT
window.start AS window_start,
MAX(CASE WHEN column_name = 'row_count' THEN avg END) AS avg_row_count,
MAX(CASE WHEN column_name = 'null_count' THEN avg END) AS avg_null_count
FROM ${catalog}.${gold_schema}_monitoring.fact_usage_profile_metrics
WHERE window.start BETWEEN :time_range.min AND :time_range.max
AND slice_key IS NULL
GROUP BY window.start
ORDER BY window.start
SELECT
window.start AS window_start,
success_rate_drift,
cost_per_dbu_drift
FROM ${catalog}.${gold_schema}_monitoring.fact_usage_drift_metrics
WHERE column_name = ':table'
AND drift_type = 'CONSECUTIVE'
AND window.start BETWEEN :time_range.min AND :time_range.max
ORDER BY window.start
6. Chart Scale Properties
MUST: Add explicit scale to chart encodings.
Required Scales
Pie Charts:
color.scale: categoricalangle.scale: quantitative
Bar Charts:
x.scale: categoricaly.scale: quantitative
Line Charts:
x.scale: temporaly.scale: quantitativecolor.scale: categorical (for multi-series)
Area Charts:
x.scale: temporaly.scale: quantitativecolor.scale: categorical (for multi-series)
Complete Widget Specifications
KPI Counter (Version 2)
{
"widget": {
"name": "kpi_total_revenue",
"queries": [
{
"name": "main_query",
"query": {
"datasetName": "kpi_totals",
"fields": [
{"name": "total_revenue", "expression": "`total_revenue`"}
],
"disaggregated": false
}
}
],
"spec": {
"version": 2,
"widgetType": "counter",
"encodings": {
"value": {
"fieldName": "total_revenue",
"displayName": "Total Revenue",
"booleanValues": ["False", "True"]
}
},
"frame": {
"showTitle": true,
"title": "Total Revenue",
"showDescription": true,
"description": "Total sales revenue for selected period"
}
}
},
"position": {"x": 0, "y": 2, "width": 2, "height": 2}
}
⚠️ Note: KPIs use version 2. Do NOT include period in encodings.
Widget version summary: KPIs=v2, Charts (bar/line/pie/area/scatter)=v3, Pivots=v3, Tables=v2, Sankey=v1, Filters=v2, Choropleth Map=v1 (choropleth-map), Symbol/Point Map=v2 (symbol-map).
Bar Chart (Version 3)
{
"widget": {
"name": "chart_revenue_by_category",
"queries": [
{
"name": "main_query",
"query": {
"datasetName": "revenue_by_category",
"fields": [
{"name": "category", "expression": "`category`"},
{"name": "revenue", "expression": "`revenue`"}
],
"disaggregated": false
}
}
],
"spec": {
"version": 3,
"widgetType": "bar",
"encodings": {
"x": {
"fieldName": "category",
"displayName": "Category",
"scale": {"type": "categorical"}
},
"y": {
"fieldName": "revenue",
"displayName": "Revenue",
"scale": {"type": "quantitative"}
}
},
"frame": {
"showTitle": true,
"title": "Revenue by Category",
"showDescription": true,
"description": "Sales revenue breakdown by product category"
}
}
},
"position": {"x": 3, "y": 4, "width": 3, "height": 6}
}
Line Chart (Version 3)
{
"widget": {
"name": "chart_revenue_trend",
"queries": [
{
"name": "main_query",
"query": {
"datasetName": "revenue_trend",
"fields": [
{"name": "transaction_date", "expression": "`transaction_date`"},
{"name": "revenue", "expression": "`revenue`"}
],
"disaggregated": false
}
}
],
"spec": {
"version": 3,
"widgetType": "line",
"encodings": {
"x": {
"fieldName": "transaction_date",
"displayName": "Date",
"scale": {"type": "temporal"}
},
"y": {
"fieldName": "revenue",
"displayName": "Revenue",
"scale": {"type": "quantitative"}
}
},
"frame": {
"showTitle": true,
"title": "Revenue Trend",
"showDescription": true,
"description": "Daily revenue over time"
}
}
},
"position": {"x": 0, "y": 4, "width": 3, "height": 6}
}
Pie Chart (Version 3)
{
"widget": {
"name": "chart_revenue_distribution",
"queries": [
{
"name": "main_query",
"query": {
"datasetName": "revenue_by_category",
"fields": [
{"name": "category", "expression": "`category`"},
{"name": "revenue", "expression": "`revenue`"}
],
"disaggregated": false
}
}
],
"spec": {
"version": 3,
"widgetType": "pie",
"encodings": {
"angle": {
"fieldName": "revenue",
"displayName": "Revenue",
"scale": {"type": "quantitative"}
},
"color": {
"fieldName": "category",
"displayName": "Category",
"scale": {"type": "categorical"}
}
},
"frame": {
"showTitle": true,
"title": "Revenue Distribution",
"showDescription": true,
"description": "Revenue share by category"
}
}
},
"position": {"x": 0, "y": 10, "width": 3, "height": 6}
}
Table Widget (Version 2)
{
"widget": {
"name": "table_sales_detail",
"queries": [
{
"name": "main_query",
"query": {
"datasetName": "sales_detail",
"fields": [
{"name": "store_name", "expression": "`store_name`"},
{"name": "product", "expression": "`product`"},
{"name": "revenue", "expression": "`revenue`"},
{"name": "units", "expression": "`units`"}
],
"disaggregated": false
}
}
],
"spec": {
"version": 2,
"widgetType": "table",
"encodings": {
"columns": [
{"fieldName": "store_name", "title": "Store"},
{"fieldName": "product", "title": "Product"},
{
"fieldName": "revenue",
"title": "Revenue",
"type": "number"
},
{"fieldName": "units", "title": "Units"}
]
},
"frame": {
"showTitle": true,
"title": "Sales Detail",
"showDescription": true,
"description": "Detailed sales by store and product"
}
}
},
"position": {"x": 0, "y": 16, "width": 6, "height": 6}
}
Pivot Table (Version 3)
Pivot tables display hierarchical data with expand/collapse drill-down (released Feb 2026). Use for Power BI matrix visual equivalents.
Two query modes — the choice of disaggregated determines how aggregation works:
disaggregated: true — Raw rows flow to the pivot; the pivot applies transform (SUM, AVG, etc.) at each hierarchy level. Use for additive measures only (totals, counts).disaggregated: false — Widget query fields contain aggregate expressions (SUM, computed ratios); the visualization controls the GROUP BY. Use when you need ratio metrics (averages, percentages) that must recompute at each hierarchy level.
Pivot with Additive Measures Only (disaggregated: true)
{
"widget": {
"name": "pivot_hierarchy_additive",
"queries": [
{
"name": "main_query",
"query": {
"datasetName": "ds_hierarchy",
"fields": [
{"name": "region", "expression": "`region`"},
{"name": "market", "expression": "`market`"},
{"name": "store", "expression": "`store`"},
{"name": "revenue", "expression": "`revenue`"},
{"name": "stores", "expression": "`stores`"}
],
"disaggregated": true
}
}
],
"spec": {
"version": 3,
"widgetType": "pivot",
"encodings": {
"rows": [
{"fieldName": "region", "displayName": "Region"},
{"fieldName": "market", "displayName": "Market"},
{"fieldName": "store", "displayName": "Store"}
],
"values": [
{"fieldName": "stores", "displayName": "Store#", "transform": "SUM"},
{"fieldName": "revenue", "displayName": "Revenue", "transform": "SUM"}
]
},
"frame": {
"showTitle": true,
"title": "Hierarchy Drill-Down"
}
}
},
"position": {"x": 0, "y": 0, "width": 6, "height": 8}
}
Pivot with Ratio Metrics — Two Patterns
Non-additive ratio metrics (e.g., avg_revenue = total_revenue / store_count) must recompute at each hierarchy level. Two working patterns exist:
Pattern A: Dataset columns + MEASURE() (for deep hierarchy drill-down)
Use when you need cubeGroupingSets for expand/collapse. Define custom calculations on the dataset via a columns array, then reference via MEASURE() in the widget query:
Dataset (note the columns array):
{
"name": "ds_hierarchy",
"displayName": "Store Hierarchy",
"queryLines": ["SELECT region, market, store_id,\n", " MEASURE(store_count) AS stores,\n", " MEASURE(total_revenue) AS revenue,\n", " MEASURE(store_day_count) AS store_days\n", "FROM catalog.schema.mv_store_metrics\n", "GROUP BY ALL"],
"columns": [
{"displayName": "Store#", "description": "Count of stores", "expression": "SUM(`stores`)"},
{"displayName": "Avg Revenue", "description": "Average revenue per store-day", "expression": "SUM(`revenue`) / NULLIF(SUM(`store_days`), 0)"}
],
"catalog": "my_catalog",
"schema": "my_schema"
}
Widget:
{
"widget": {
"name": "pivot_hierarchy_ratios_a",
"queries": [
{
"name": "main_query",
"query": {
"datasetName": "ds_hierarchy",
"fields": [
{"name": "region", "expression": "`region`"},
{"name": "market", "expression": "`market`"},
{"name": "store_id", "expression": "`store_id`"},
{"name": "measure(Store#)", "expression": "MEASURE(`Store#`)"},
{"name": "measure(Avg Revenue)", "expression": "MEASURE(`Avg Revenue`)"}
],
"cubeGroupingSets": {"sets": [{"fieldNames": ["region", "market", "store_id"]}, {}]},
"disaggregated": false
}
}
],
"spec": {
"version": 3,
"widgetType": "pivot",
"encodings": {
"rows": [
{"fieldName": "region", "displayName": "Region"},
{"fieldName": "market", "displayName": "Market"},
{"fieldName": "store_id", "displayName": "Store"}
],
"cell": {
"type": "multi-cell",
"fields": [
{"fieldName": "measure(Store#)", "cellType": "text"},
{"fieldName": "measure(Avg Revenue)", "cellType": "text"}
],
"displayAs": "columns"
}
},
"frame": {"showTitle": true, "title": "Hierarchy with Ratio Metrics"}
}
},
"position": {"x": 0, "y": 0, "width": 6, "height": 8}
}
Pattern B: Inline Widget Query Expressions (for flat/shallow groupings)
Use when you don't need cubeGroupingSets. Define aggregate expressions directly in widget query fields:
{
"widget": {
"name": "pivot_hierarchy_ratios_b",
"queries": [
{
"name": "main_query",
"query": {
"datasetName": "ds_hierarchy_detail",
"fields": [
{"name": "region", "expression": "`region`"},
{"name": "sum_stores", "expression": "SUM(`stores`)"},
{"name": "avg_revenue", "expression": "SUM(`revenue`) / NULLIF(SUM(`store_days`), 0)"}
],
"disaggregated": false
}
}
],
"spec": {
"version": 3,
"widgetType": "pivot",
"encodings": {
"rows": [
{"fieldName": "region", "displayName": "Region"}
],
"values": [
{"fieldName": "sum_stores", "displayName": "Store#"},
{"fieldName": "avg_revenue", "displayName": "Avg Revenue"}
]
},
"frame": {"showTitle": true, "title": "Flat Grouping with Ratios"}
}
},
"position": {"x": 0, "y": 0, "width": 6, "height": 8}
}
Pattern Comparison
| Feature | Pattern A: Dataset columns | Pattern B: Inline Expressions |
|---|
| Hierarchy drill-down | Yes (cubeGroupingSets) | No |
| Custom calculations | Dataset columns array | Widget query fields |
| Widget references | MEASURE(\Display Name`)` | Direct field names |
| Encoding type | cell (multi-cell) | values array |
transform property | Not used | Not used |
| Visible in UI draft mode | Yes — calculations appear in Values panel | No — panel is empty in draft, only renders when published |
| Best for | Deep hierarchies (3+ levels) | Flat/shallow groupings (1-2 levels) |
| Recommendation | Always prefer this pattern | Avoid unless you never need to edit in UI |
When sourcing from Metric Views, the additive components come from MEASURE() calls:
SELECT
region, market, store_id,
MEASURE(total_revenue) AS revenue,
MEASURE(store_day_count) AS store_days,
MEASURE(store_count) AS stores
FROM catalog.schema.mv_store_metrics
GROUP BY region, market, store_id
Point Map / Symbol Map (Version 2)
Requires numeric latitude/longitude columns (DOUBLE, not STRING). If upstream tables store coordinates as STRING, cast them to DOUBLE in the materialized view layer.
CRITICAL: The widget type is "symbol-map" (NOT "point"), version is 2 (NOT 3), and coordinates use a nested coordinates object.
{
"widget": {
"name": "map_store_locations",
"queries": [
{
"name": "main_query",
"query": {
"datasetName": "ds_store_points",
"fields": [
{"name": "latitude", "expression": "`latitude`"},
{"name": "longitude", "expression": "`longitude`"},
{"name": "region", "expression": "`region`"},
{"name": "revenue", "expression": "`revenue`"}
],
"disaggregated": true
}
}
],
"spec": {
"version": 2,
"widgetType": "symbol-map",
"encodings": {
"coordinates": {
"latitude": {"fieldName": "latitude"},
"longitude": {"fieldName": "longitude"}
},
"color": {"fieldName": "region", "scale": {"type": "categorical"}},
"size": {"fieldName": "revenue", "scale": {"type": "quantitative"}},
"extra": [{"fieldName": "region"}]
},
"frame": {
"showTitle": true,
"title": "Store Locations"
}
}
},
"position": {"x": 0, "y": 0, "width": 3, "height": 8}
}
Choropleth Map (Version 1)
CRITICAL: The widget type is "choropleth-map" (NOT "choropleth"), version is 1 (NOT 3), and the geographic encoding uses a region object with regionType and admin0/admin1 sub-objects. Queries MUST use disaggregated: false with aggregate expressions.
Geographic role values:
- Country by name:
geographicRole: "admin0-name"
- Country by 2-letter code:
geographicRole: "admin0-unit-code"
- State/Province by name: use
admin1 with geographicRole: "admin1-name"
State/Province (admin1) choropleths: When mapping at the state/province level, you MUST include BOTH admin0 (country) AND admin1 (state) in the region encoding. The admin0 field provides the country context required for Mapbox to resolve state names. State names must be full names (e.g., "California", "Ontario"), not abbreviations ("CA", "ON"). If your source data uses abbreviations, apply a CASE expression in the dataset query to convert them.
Country-level (admin0) example:
{
"widget": {
"name": "map_choropleth",
"queries": [
{
"name": "main_query",
"query": {
"datasetName": "ds_country_sales",
"fields": [
{"name": "country_name", "expression": "`country_name`"},
{"name": "sum(total_sales)", "expression": "SUM(`total_sales`)"}
],
"disaggregated": false
}
}
],
"spec": {
"version": 1,
"widgetType": "choropleth-map",
"encodings": {
"region": {
"regionType": "mapbox-v4-admin",
"admin0": {
"fieldName": "country_name",
"type": "field",
"geographicRole": "admin0-name"
}
},
"color": {
"fieldName": "sum(total_sales)",
"scale": {"type": "quantitative"}
},
"extra": [{"fieldName": "country_name"}]
},
"frame": {
"showTitle": true,
"title": "Sales by Country"
}
}
},
"position": {"x": 3, "y": 0, "width": 3, "height": 8}
}
State/Province-level (admin1) example — requires admin0 context:
{
"spec": {
"version": 1,
"widgetType": "choropleth-map",
"encodings": {
"region": {
"regionType": "mapbox-v4-admin",
"admin0": {
"fieldName": "country_name",
"type": "field",
"geographicRole": "admin0-name"
},
"admin1": {
"fieldName": "state_full_name",
"type": "field",
"geographicRole": "admin1-name"
}
},
"color": {
"fieldName": "total_sales",
"scale": {"type": "quantitative"}
}
}
}
}
State name CASE pattern for abbreviation-to-full-name conversion in dataset SQL:
CASE state_abbr
WHEN 'CA' THEN 'California'
WHEN 'TX' THEN 'Texas'
WHEN 'NY' THEN 'New York'
END AS state_full_name
Filter Widget (Single Select)
{
"widget": {
"name": "filter_store",
"queries": [
{
"name": "main_query",
"query": {
"datasetName": "store_filter_values",
"fields": [
{"name": "store_name", "expression": "`store_name`"}
],
"disaggregated": true
}
}
],
"spec": {
"version": 2,
"widgetType": "filter-single-select",
"encodings": {
"fields": [
{
"displayName": "Store",
"fieldName": "store_name",
"queryName": "main_query"
}
]
},
"frame": {
"showTitle": true,
"title": "Store Filter"
}
}
},
"position": {"x": 0, "y": 0, "width": 2, "height": 2}
}
Default Selection and disallowAll
To set a default value on a single-select filter (e.g., defaulting to "Day" instead of "All"), use the selection and disallowAll properties. The defaultSelection format requires a typed value structure — a bare string array will silently fall back to "All".
Correct defaultSelection format:
{
"spec": {
"version": 2,
"widgetType": "filter-single-select",
"selection": {
"defaultSelection": {
"values": {
"dataType": "STRING",
"values": [{"value": "Day"}]
}
}
},
"disallowAll": true
}
}
disallowAll: true removes the "All" option, forcing the user to pick one value. Without this, even with a defaultSelection, users can revert to "All" and see unfiltered results — which causes double-counting when multiple period rows exist.dataType must match the column type ("STRING", "NUMBER", etc.).- Each value is an object:
{"value": "Day"}, not a bare string.
Filter Widget (Multi Select)
{
"widget": {
"name": "filter_region",
"queries": [
{
"name": "main_query",
"query": {
"datasetName": "ds_region_data",
"fields": [
{"name": "region", "expression": "`region`"}
],
"disaggregated": true
}
}
],
"spec": {
"version": 2,
"widgetType": "filter-multi-select",
"encodings": {
"fields": [
{
"displayName": "Region",
"fieldName": "region",
"queryName": "main_query"
}
]
},
"frame": {
"showTitle": true,
"title": "Filter by Region"
}
}
}
}
Waterfall Chart (Version 3)
Waterfall charts show additive/subtractive contributions to a total — ideal for decomposing "who helped and who hurt?" (e.g., region contribution to total metric change).
{
"widget": {
"name": "region_contribution_waterfall",
"queries": [
{
"name": "main_query",
"query": {
"datasetName": "ds_region_performance",
"fields": [
{"name": "region", "expression": "`region`"},
{"name": "metric_change", "expression": "`metric_change`"}
],
"disaggregated": true
}
}
],
"spec": {
"version": 3,
"widgetType": "waterfall",
"encodings": {
"x": {
"fieldName": "region",
"displayName": "Region",
"scale": {"type": "categorical"}
},
"y": {
"fieldName": "metric_change",
"displayName": "$ Change",
"scale": {"type": "quantitative"}
}
},
"frame": {
"showTitle": true,
"title": "Region Contribution to Total Change"
}
}
}
}
Histogram (Version 3)
Histograms show distribution of a continuous variable — useful for identifying outliers or skewed metric distributions.
{
"widget": {
"name": "revenue_distribution",
"queries": [
{
"name": "main_query",
"query": {
"datasetName": "ds_store_detail",
"fields": [
{"name": "avg_revenue", "expression": "`avg_revenue`"}
],
"disaggregated": true
}
}
],
"spec": {
"version": 3,
"widgetType": "histogram",
"encodings": {
"x": {
"fieldName": "avg_revenue",
"displayName": "Avg Revenue per Store",
"scale": {"type": "quantitative"}
}
},
"frame": {
"showTitle": true,
"title": "Revenue Distribution Across Stores"
}
}
}
}
Cross-Filtering and Drill-Through Patterns
Cross-Filtering (Same Page, Automatic)
Golden Rule: Charts on the same page sharing the same datasetName automatically cross-filter when a user clicks a data point. Field-name matching alone is NOT sufficient — the charts must reference the identical dataset.
How it works: When a user clicks a bar (region "EAST") on a bar chart using ds_region_pulse, every other chart on the same page that also uses ds_region_pulse filters to rows where the clicked value matches. No additional configuration is required.
Shared dataset pattern for maps: A single store-level dataset can power both a choropleth map and a point/symbol map. The choropleth uses disaggregated: false (aggregates to state level) while the symbol map uses disaggregated: true (shows individual stores). Clicking a state on the choropleth filters the point map to only that state's stores.
UNION ALL multi-metric dataset: To enable cross-filtering between a bar chart and a heatmap showing different metrics, pivot N metrics into rows using UNION ALL:
SELECT region, 'Revenue %Chg' AS metric, pct_chg_revenue AS metric_value FROM ...
UNION ALL
SELECT region, 'Customer %Chg' AS metric, pct_chg_customers AS metric_value FROM ...
UNION ALL
SELECT region, 'Avg Txn %Chg' AS metric, pct_chg_avg_txn AS metric_value FROM ...
Drill-Through (Cross-Page Navigation)
How it works: Right-clicking a data point on a supported chart (bar, pie, scatter, heatmap, histogram, point map, box plot) shows a context menu: "Drill to → [Page Name]". The target page opens with filters auto-populated from the clicked data point.
Configuration requirement: The target page MUST have filter widgets whose datasetName matches the source chart's dataset AND whose fieldName matches the clicked dimension. Matching field names across different datasets does NOT enable drill-through.
Design for both simultaneously: Cross-filtering happens automatically within a page (same dataset). Drill-through navigates between pages (shared dataset + field on target filter). Plan your dataset topology to support both:
- Charts that should cross-filter on the same page → share one dataset.
- Charts that should drill to a target page → the target page needs a filter widget on the same dataset.
COUNT(DISTINCT pk_column) for Store Counts
In dashboard calculated columns (columns[]), prefer COUNT(DISTINCT location_id) over SUM(stores) for store counts. When a filter defaults to "All" and multiple period rows exist per store, SUM(stores) double-counts. COUNT(DISTINCT pk) remains correct regardless of filter state.
{"displayName": "Store#", "description": "Count of stores", "expression": "COUNT(DISTINCT `location_id`)"}
Dashboard Structure Patterns
Standard Dashboard Layout
Page 1: Overview
├── Row 0: Filters (height: 2)
│ ├── Date Range Filter (width: 2)
│ ├── Store Filter (width: 2)
│ └── Product Filter (width: 2)
│
├── Row 2: KPIs (height: 2)
│ ├── Total Revenue (width: 2)
│ ├── Total Units (width: 2)
│ └── Transaction Count (width: 2)
│
├── Row 4: Main Charts (height: 6)
│ ├── Revenue Trend (line, width: 3)
│ └── Revenue by Category (bar, width: 3)
│
└── Row 10: Detail Table (height: 6)
└── Transaction Details (width: 6)
Page: Global Filters
└── Cross-dashboard filters
Complete Dashboard Template
{
"datasets": [
{
"name": "kpi_totals",
"displayName": "KPI Totals",
"queryLines": ["SELECT SUM(net_revenue) as total_revenue, SUM(net_units) as total_units, SUM(transaction_count) as total_transactions FROM ${catalog}.${schema}.fact_sales_daily WHERE transaction_date BETWEEN :start_date AND :end_date"],
"catalog": "${catalog}",
"schema": "${schema}"
},
{
"name": "revenue_trend",
"displayName": "Revenue Trend",
"queryLines": ["SELECT transaction_date, SUM(net_revenue) as revenue FROM ${catalog}.${schema}.fact_sales_daily WHERE transaction_date BETWEEN :start_date AND :end_date GROUP BY transaction_date ORDER BY transaction_date"],
"catalog": "${catalog}",
"schema": "${schema}"
},
{
"name": "revenue_by_category",
"displayName": "Revenue by Category",
"queryLines": ["SELECT p.category, SUM(f.net_revenue) as revenue FROM ${catalog}.${schema}.fact_sales_daily f JOIN ${catalog}.${schema}.dim_product p ON f.upc_code = p.upc_code WHERE f.transaction_date BETWEEN :start_date AND :end_date GROUP BY p.category ORDER BY revenue DESC"],
"catalog": "${catalog}",
"schema": "${schema}"
},
{
"name": "store_filter_values",
"displayName": "Store Filter Values",
"queryLines": ["SELECT 'All' as store_name UNION ALL SELECT DISTINCT store_name FROM ${catalog}.${schema}.dim_store WHERE is_current = true ORDER BY store_name"],
"catalog": "${catalog}",
"schema": "${schema}"
}
],
"pages": [
{
"name": "page_overview",
"displayName": "Overview",
"layout": [
]
},
{
"name": "page_global_filters",
"displayName": "Global Filters",
"pageType": "PAGE_TYPE_GLOBAL_FILTERS",
"layout": [
]
}
],
"parameters": [
{
"displayName": "Start Date",
"keyword": "start_date",
"dataType": "DATE",
"defaultSelection": {
"values": {
"dataType": "DATE",
"values": [{"value": "2024-01-01"}]
}
}
},
{
"displayName": "End Date",
"keyword": "end_date",
"dataType": "DATE",
"defaultSelection": {
"values": {
"dataType": "DATE",
"values": [{"value": "2024-12-31"}]
}
}
}
],
"uiSettings": {
"theme": {
"canvasBackgroundColor": {"light": "#F7F9FA", "dark": "#0B0E11"},
"widgetBackgroundColor": {"light": "#FFFFFF", "dark": "#1A1D21"},
"widgetBorderColor": {"light": "#E0E4E8", "dark": "#2A2E33"},
"fontColor": {"light": "#11171C", "dark": "#E8ECF0"},
"selectionColor": {"light": "#077A9D", "dark": "#8ACAE7"},
"visualizationColors": [
"#077A9D", "#00A972", "#FFAB00", "#FF3621",
"#8BCAE7", "#99DDB4", "#FCA4A1", "#AB4057",
"#6B4FBB", "#BF7080"
],
"widgetHeaderAlignment": "LEFT"
},
"genieSpace": {"isEnabled": false}
}
}
⚠️ CRITICAL: Note that datasets use queryLines (array) + catalog + schema — NOT query (string). Using query instead of queryLines will cause ALL widgets to error silently.
Global Filters Page
Always include a Global Filters page for cross-dashboard filtering:
{
"name": "page_global_filters",
"displayName": "Global Filters",
"pageType": "PAGE_TYPE_GLOBAL_FILTERS",
"layout": [
{
"widget": {
"name": "global_date_range",
"spec": {
"version": 2,
"widgetType": "filter-date-range",
"frame": {
"showTitle": true,
"title": "Date Range"
}
}
},
"position": {"x": 0, "y": 0, "width": 2, "height": 2}
},
{
"widget": {
"name": "global_store_filter",
"spec": {
"version": 2,
"widgetType": "filter-single-select",
"frame": {
"showTitle": true,
"title": "Store"
}
}
},
"position": {"x": 2, "y": 0, "width": 2, "height": 2}
}
]
}
Query Patterns & Best Practices
Pattern: "All" Option for Filters
SELECT 'All' AS filter_value
UNION ALL
SELECT DISTINCT actual_value AS filter_value
FROM source_table
ORDER BY filter_value
Pattern: Dynamic Filtering
WHERE (:store_filter = 'All' OR store_name = :store_filter)
AND transaction_date BETWEEN :start_date AND :end_date
Pattern: Handle NULL Values
COALESCE(store_name, 'Unknown')
COALESCE(category, 'Uncategorized')
Pattern: Date Range
WHERE DATE(timestamp_field) >= :start_date
AND DATE(timestamp_field) <= :end_date
Pattern: SCD2 Latest Records
WITH latest AS (
SELECT *,
ROW_NUMBER() OVER(PARTITION BY entity_id ORDER BY change_time DESC) as rn
FROM source_table
WHERE delete_time IS NULL
QUALIFY rn = 1
)
SELECT * FROM latest
Pattern: Multi-Series Charts
Use UNION ALL to combine series:
SELECT date, 'Average' AS metric, AVG(value) AS value FROM table GROUP BY date
UNION ALL
SELECT date, 'P95' AS metric, PERCENTILE_APPROX(value, 0.95) AS value FROM table GROUP BY date
ORDER BY date, metric
Widget Encoding:
{
"x": { "fieldName": "date", "scale": { "type": "temporal" } },
"y": { "fieldName": "value", "scale": { "type": "quantitative" } },
"color": { "fieldName": "metric", "scale": { "type": "categorical" } }
}
Pattern: Stacked Area Charts
Use stack: "zero" in y encoding:
{
"y": {
"fieldName": "run_count",
"scale": { "type": "quantitative" },
"stack": "zero"
}
}
Pattern: Metric View Queries Using MEASURE()
When dashboards query Metric Views, use MEASURE() for aggregate datasets (KPIs, trends, breakdowns) and direct Gold SQL for detail tables and filter values. See Metric View Dashboard Queries for complete syntax, SQL templates, rules, and anti-patterns.
System Tables Reference
⚠️ Always Verify Field Names!
When using system tables, verify schema against official docs.
system.lakeflow.jobs (SCD2)
SELECT workspace_id, job_id, name, description, run_as
FROM system.lakeflow.jobs
WHERE delete_time IS NULL
system.lakeflow.job_task_run_timeline
SELECT jtr.*,
COALESCE(j.name, 'Job ' || jtr.job_id) AS job_name
FROM system.lakeflow.job_task_run_timeline jtr
LEFT JOIN (
SELECT workspace_id, job_id, name,
ROW_NUMBER() OVER(PARTITION BY workspace_id, job_id
ORDER BY change_time DESC) as rn
FROM system.lakeflow.jobs
WHERE delete_time IS NULL
QUALIFY rn = 1
) j ON jtr.workspace_id = j.workspace_id AND jtr.job_id = j.job_id
system.compute.clusters
SELECT workspace_id, cluster_id,
MAX_BY(dbr_version, change_time) AS dbr_version
FROM system.compute.clusters
WHERE delete_time IS NULL
GROUP BY workspace_id, cluster_id
Core Patterns (Deployment-Ready)
Variable Substitution
NEVER hardcode schemas. Always use variables:
FROM ${catalog}.${gold_schema}.fact_usage
FROM ${catalog}.${gold_schema}_monitoring.fact_usage_profile_metrics
FROM jane_catalog.gold.fact_usage
Substitution in Python:
json_str = json_str.replace('${catalog}', catalog)
json_str = json_str.replace('${gold_schema}', gold_schema)
UPDATE-or-CREATE Deployment
Pattern: Use Workspace Import API with overwrite: true
Benefits:
- Preserves dashboard URLs and permissions
- Single code path for CREATE and UPDATE
- No manual dashboard creation required
Implementation: See scripts/deploy_dashboard.py
⚠️ CRITICAL: Content MUST be base64-encoded (not raw UTF-8 bytes)
The Workspace Import API (ws.workspace.import_ / POST /api/2.0/workspace/import) expects the content field to be a base64-encoded ASCII string. Passing raw UTF-8 bytes silently produces a corrupt .lvdash.json that appears in the workspace tree but fails to open in the AI/BI editor with a generic parse error — the most frustrating failure mode because there is no import-time exception.
import base64
ws.workspace.import_(
path=target_path,
content=rendered.encode("utf-8"),
format=ImportFormat.AUTO,
overwrite=True,
)
b64_content = base64.b64encode(rendered.encode("utf-8")).decode("ascii")
ws.workspace.import_(
path=target_path,
content=b64_content,
format=ImportFormat.AUTO,
overwrite=True,
)
Reference: https://docs.databricks.com/api/workspace/workspace/import
⚠️ CRITICAL: Pre-loop variable enumeration
Before entering the per-dashboard deploy loop, scan every .lvdash.json file for ${...} placeholders and fail loud if the caller's variables dict does not cover every placeholder. This prevents the partial-deploy failure mode where dashboards N+1…K never get deployed because dashboard N referenced an unsupplied variable mid-loop.
import re
_VAR_RE = re.compile(r"(?<!\$)\$\{([A-Za-z_][A-Za-z0-9_]*)\}")
required = {}
for df in dashboard_files:
for name in _VAR_RE.findall(df.read_text()):
required.setdefault(name, []).append(df)
missing = {n: fs for n, fs in required.items() if variables.get(n) in (None, "")}
if missing:
raise RuntimeError(
"Dashboard deploy pre-flight FAILED — missing variable values:\n"
+ "\n".join(f" - ${{{n}}} required by {fs}" for n, fs in sorted(missing.items()))
)
The canonical implementation of both guards lives in scripts/deploy_dashboard.py (enumerate_required_variables, assert_all_variables_provided, base64-encoded deploy_dashboard). The Phase 0.5 pre-flight in semantic-layer/00-semantic-layer-setup/SKILL.md calls enumerate_required_variables directly so the failure surfaces BEFORE bundle validate.
Pre-Deployment Validation
Strategy: Validate queries with SELECT LIMIT 1 before deployment
Benefits:
- 90% reduction in dev loop time
- Catches ALL errors at once (not one-by-one)
- Validates runtime errors (not just syntax)
Scripts:
validate_dashboard_queries.py - SQL validationvalidate_widget_encodings.py - Widget-query alignment
Implementation Guide
Phase-by-Phase Checklist
Phase 0: Input Validation (5 min) — MANDATORY
Phase 1: Planning (30 min)
Phase 2: Datasets (30 min)
Phase 3: Widgets (1-2 hours)
Phase 4: Parameters (15 min)
Phase 5: Styling (15 min)
Phase 6: Testing (30 min)
Verification Checklist
Pre-Deployment
Widget-Query Alignment
Monitoring Query Validation
Chart Validation
Before Deploying Dashboard
Scripts & Automation
update_dashboard.py — Lakeview Dashboard JSON Utility
A Python utility for robust, programmatic manipulation of .lvdash.json files. Copy to your project's scripts/ folder when needed.
Location: scripts/update_dashboard.py
Key functions: load_dashboard, save_dashboard, find_dataset, find_widget, add_dataset_columns, update_widget_query_fields, add_widget_query_fields, rename_widget_query_field, set_widget_encoding_rows, set_widget_encoding_values, rename_encoding_field, reorder_widget_query_fields, reorder_cube_grouping_sets, replace_in_dataset_query, validate_dashboard.
CLI usage:
python scripts/update_dashboard.py input.lvdash.json output.lvdash.json \
--validate
Common operations:
from update_dashboard import *
data = load_dashboard("dashboard.lvdash.json")
data = reorder_widget_query_fields(data, "my-pivot", ["region", "market", "store_id"])
data = reorder_widget_encoding_rows(data, "my-pivot", ["region", "market", "store_id"])
data = add_dataset_columns(data, "ds_hierarchy", [
{"displayName": "Avg Revenue", "description": "Revenue per store-day", "expression": "SUM(`revenue`) / NULLIF(SUM(`store_days`), 0)"}
])
warnings = validate_dashboard(data)
save_dashboard("dashboard.lvdash.json", data)
deploy_dashboard.py
UPDATE-or-CREATE deployment with variable substitution:
from scripts.deploy_dashboard import deploy_dashboards
deploy_dashboards(workspace_client, dashboard_dir, catalog, gold_schema, warehouse_id)
validate_dashboard_queries.py
Pre-deployment SQL validation:
python scripts/validate_dashboard_queries.py
Catches: UNRESOLVED_COLUMN, TABLE_OR_VIEW_NOT_FOUND, UNBOUND_SQL_PARAMETER, DATATYPE_MISMATCH
validate_widget_encodings.py
Widget-query alignment validation:
python scripts/validate_widget_encodings.py
Catches: Column alias mismatches, missing columns
Assets & Templates
dashboard-template.json
Starter dashboard JSON with dataset, time range parameter, KPI widget, and page layout. Copy and modify for new dashboards.
Location: assets/templates/dashboard-template.json
Jobs System Tables Dashboard (Production Example)
Complete, production-ready dashboard demonstrating best practices for Databricks System Tables monitoring. This real-world example showcases:
- ✅ System Tables Integration - Queries
system.lakeflow.jobs, system.lakeflow.job_task_run_timeline, system.compute.clusters
- ✅ Proper JOIN Patterns - Demonstrates correct workspace_id + entity_id JOIN requirements
- ✅ SCD2 Handling - Shows QUALIFY pattern for latest records from SCD2 tables
- ✅ Widget Configuration - All widget types properly configured (counters, bar charts, line charts, tables)
- ✅ 6-Column Grid Layout - Correct positioning with 6-column grid system
- ✅ Parameter Configuration - Time range parameters with proper DATE type
- ✅ Variable Substitution - Uses ${catalog} and ${schema} patterns
- ✅ Multi-Page Dashboard - Overview + Global Filters pages
- ✅ Number Formatting - Raw numbers for proper widget formatting
- ✅ Field Name Verification - Uses actual system table column names (not assumptions)
Use this as a reference for:
- Building system tables dashboards
- Understanding complete dashboard JSON structure
- Seeing all patterns from this skill in action
- Learning proper widget-query alignment
- Implementing multi-page layouts
Location: references/Jobs System Tables Dashboard.lvdash.json
Dashboard Includes:
- Job success rate and failure tracking
- Task execution timeline and durations
- Cluster utilization and DBR version distribution
- Job run history with filters by workspace and date range
- Detailed tables for job configuration and task details
Common Mistakes & Error Messages
Common Mistakes to Avoid
❌ Mistake 1: 12-Column Grid
{"width": 6}
❌ Mistake 2: Wrong Widget Version
"version": 3,
"widgetType": "counter"
❌ Mistake 3: DATETIME Parameters
"dataType": "DATETIME"
❌ Mistake 4: Missing "All" Option
SELECT DISTINCT store_name FROM stores
❌ Mistake 5: Assuming Field Names
SELECT job_name FROM system.lakeflow.job_task_run_timeline
❌ Mistake 6: SUM on Ratio Metrics in Pivots
"values": [{"fieldName": "precomputed_avg", "transform": "SUM"}]
❌ Mistake 7: Using query Instead of queryLines in Datasets
{"name": "ds_example", "query": "SELECT * FROM table"}
{"name": "ds_example", "queryLines": ["SELECT * FROM table"], "catalog": "my_catalog", "schema": "my_schema"}
❌ Mistake 8: Using textbox_spec Instead of multilineTextboxSpec
{"textbox_spec": "### Title"}
{"multilineTextboxSpec": {"lines": ["### Title"]}}
❌ Mistake 9: Using values Encoding with cubeGroupingSets for Ratio Metrics
"cubeGroupingSets": {"sets": [{"fieldNames": ["region", "market"]}, {}]},
"encodings": {"values": [{"fieldName": "avg_revenue", "displayName": "Avg Revenue"}]}
"cubeGroupingSets": {"sets": [{"fieldNames": ["region", "market"]}, {}]},
"encodings": {"cell": {"type": "multi-cell", "fields": [{"fieldName": "measure(Avg Revenue)", "cellType": "text"}], "displayAs": "columns"}}
❌ Mistake 10: STRING Coordinates for Maps
SELECT latitude, longitude FROM dim_location
SELECT CAST(latitude AS DOUBLE) AS latitude, CAST(longitude AS DOUBLE) AS longitude
❌ Mistake 11: Using Source Table Prefix in Metric View Queries
WHERE dim_location.is_same_store = 1
WHERE is_same_store = 1
Common Error Messages
| Error | Cause | Fix |
|---|
| "no fields to visualize" | Widget fieldName doesn't match query alias | Align column aliases |
| "UNRESOLVED_COLUMN" | Column doesn't exist | Check schema, use correct column |
| "UNRESOLVED_COLUMN" with metric view | Using source table alias (e.g., dim_location.col) instead of dimension name | Drop table prefix — use bare dimension name from metric view YAML |
| "UNBOUND_SQL_PARAMETER" | Parameter not defined | Add to parameters array |
| "Unable to render visualization" | Pie chart missing scale | Add scale to encodings |
| Blank point map | lat/lng are STRING type | CAST to DOUBLE in upstream MV |
| Incorrect pivot rollup totals | Ratio metric summed incorrectly | Use disaggregated: false with aggregate expressions. For hierarchies with cubeGroupingSets, use Pattern A (dataset columns + MEASURE() + cell encoding). |
| Empty chart with data | Wrong number format | Return raw numbers, not strings |
| 0% or 0 in counter | Formatted string or wrong percentage | Return 0-1 decimal for percent |
| "Visualization has no fields selected" on counter | Invalid encoding key (e.g., comparison) in counter spec | Counter widgets (v2) ONLY support "value" in encodings. Do NOT add "comparison", "extra", or any other key — it silently breaks the entire widget. |
Lessons Learned (Production)
These patterns were discovered through production deployments and iterative dashboard refinement.
L1: Non-Additive Ratio Metrics in Pivot Hierarchies
Problem: Ratio metrics (e.g., avg_revenue = total_revenue / store_count) produce incorrect results when simply summed. The sum of ratios at leaf nodes does not equal the ratio at the rolled-up parent level.
Solution: Two patterns exist depending on whether you need hierarchy expand/collapse:
- Pattern A (Dataset
columns + MEASURE()) — ALWAYS PREFERRED: Define custom calculated measures in the dataset columns array, reference via MEASURE(\Display Name`)in widget query, usecellencodings withdisplayAs: "columns"`. Works in both the Lakeview UI editor/draft mode and published view.
- Pattern B (Inline widget expressions) — AVOID: Define aggregate expressions directly in widget query
fields, use values encodings. Only renders in published view — the UI editor/draft mode shows an empty Values panel and "No data".
Both patterns use disaggregated: false. Neither uses the transform property.
L2: Map Widget Types Use Different Names and Versions Than Standard Charts
Problem: Map visualizations show "Visualization has no fields selected" when using widgetType: "choropleth" / "point" with version: 3.
Fix:
- Choropleth:
widgetType: "choropleth-map", version: 1, use encodings.region with regionType: "mapbox-v4-admin" and admin0/admin1 sub-objects
- Point/Symbol Map:
widgetType: "symbol-map", version: 2, use encodings.coordinates with nested latitude/longitude objects
L3: Querying Metric Views in Dashboard Datasets
Problem: UNRESOLVED_COLUMN errors when dashboard SQL references dimensions with source table prefixes.
Fix: Reference dimensions by their metric view name (not the source table alias):
L4: Dashboard vs Metric View Measure Reusability
Architecture: The metric view remains the single source of truth for reusable, context-aware measures (Genie, ad-hoc SQL, KPI counters). Dashboard pivot widget aggregate expressions are a presentation layer adaptation specific to the interactive hierarchy, consuming additive components from the metric view.
- Genie/ad-hoc SQL:
SELECT MEASURE(avg_revenue) ... — context-aware, works natively
- Dashboard KPIs: Same as above — single-value counters work with MEASURE() directly
- Dashboard pivots: Must use additive components + aggregate expressions for correct hierarchy rollups
L5: Bundle Deployment State Management
Problem: Repeated lineage mismatch, Node already exists, or stale resource errors when iterating on dashboards via Databricks Asset Bundles.
Fix: Systematic cleanup before redeployment:
- Delete local
.databricks/bundle/dev/terraform/terraform.tfstate
- Delete remote bundle workspace directory
- Clean orphaned resources (pipelines, jobs, dashboards) by ID if needed
- Redeploy:
databricks bundle deploy -t dev --profile <profile>
L6: Dataset columns for Custom Calculated Measures
Problem: Hierarchy pivots with cubeGroupingSets cannot use inline widget query aggregate expressions for ratio metrics — the values appear as additional rows instead of columns.
Solution: Use the dataset columns array to define custom calculated measures that the Lakeview engine evaluates at each hierarchy level. The widget query references these via MEASURE(\Display Name`)and the encoding usescellwithdisplayAs: "columns"`.
L7: Cross-Filtering Is Dataset-Scoped, Not Field-Scoped
Problem: Two charts on the same page with the same field name but pointing to different datasets did not cross-filter when clicked.
Fix: Consolidate charts that should cross-filter onto a single shared dataset.
L8: disallowAll Prevents Silent Double-Counting
Problem: Aggregated metrics showed incorrect values because the period filter silently defaulted to "All", including both Day and MTD rows.
Fix: Use the correct defaultSelection format with disallowAll: true on single-select filters where "All" is not meaningful. Additionally, use COUNT(DISTINCT pk_column) instead of SUM(count_column) for robustness.
L9: Choropleth admin1 (State) Maps Require Explicit admin0 (Country) Context
Problem: A state-level choropleth map rendered blank even though state names were correctly provided.
Fix: Always include both admin0 and admin1 in the region encoding for state/province choropleths. Convert abbreviations to full names via CASE expressions in the dataset SQL.
L10: Silent Recovery from Missing Input Files
Problem: When a user-referenced file (plan, manifest, schema) is not found, the agent silently reconstructs requirements from secondary sources. The user has no visibility into the gap, and the dashboard may miss specific KPIs, charts, or layout preferences.
Fix: Always surface missing files to the user before proceeding. Search for similar filenames and nearby directories, then ask: "The file at [path] was not found. I found [alternatives]. Should I proceed with those, or is the file at a different path?"
L11: Substituting "Safer" Query Patterns for User-Requested Patterns
Problem: When uncertain about MEASURE() syntax, the agent fell back to direct Gold table SQL — contradicting the user's explicit request. The dashboard bypassed the semantic layer entirely.
Fix: When uncertain about a query pattern, load the owning skill (e.g., semantic-layer/01-metric-views-patterns/SKILL.md for MEASURE()). If the tradeoff involves contradicting a user instruction, surface it to the user — never resolve it unilaterally.
L12: Producing All Referenced Scripts
Problem: The skill references three companion scripts (validate_dashboard_queries.py, validate_widget_encodings.py, deploy_dashboard.py). Agents sometimes create only the "most important" one and skip the others, leaving the dev loop with no live SQL validation or no deployment path.
Fix: Treat the "Scripts & Automation" section as a checklist. All three scripts should be created (or copied from the skill's scripts/ templates) during dashboard builds. If a script is intentionally skipped, state which one and why — do not silently produce a partial deliverable.
L13: Reading Large Dashboard JSON Examples
Problem: Production .lvdash.json files can exceed 100K characters. Reading from line 1 returns dataset SQL but misses the widget/page structure — usually the more valuable reference for layout patterns, grid positions, and encodings.
Fix: Read strategically instead of from the top:
- Grep for
"pages" to locate where page/widget definitions begin
- Read from that line to see widget patterns, grid positions, encodings, and parameter bindings
- Read datasets separately only when you need query patterns (usually the SKILL.md and reference files already cover these)
- If the file exceeds read limits, prioritize the pages section — it has higher value per token than dataset SQL
Dashboard File Management
File Location
project/
├── docs/
│ └── dashboards/
│ └── {project}_dashboard.lvdash.json
Naming Convention
{project}_{purpose}_dashboard.lvdash.json
Examples:
- lakehouse_monitoring_dashboard.lvdash.json
- sales_analytics_dashboard.lvdash.json
- executive_kpi_dashboard.lvdash.json
Version Control
- Track dashboard JSON in git
- Use meaningful commit messages
- Document changes in comments
Key Principles Summary
1. 6-Column Grid (NOT 12!)
{"width": 3}
{"width": 6}
2. Version Numbers
| Widget | Version |
|---|
| KPI Counter | 2 |
| Bar Chart | 3 |
| Line Chart | 3 |
| Pie Chart | 3 |
| Area Chart | 3 |
| Scatter Chart | 3 |
| Pivot Table | 3 |
Symbol/Point Map (symbol-map) | 2 |
Choropleth Map (choropleth-map) | 1 |
| Table | 2 |
| Sankey | 1 |
| Waterfall | 3 |
| Histogram | 3 |
| Filter (single/multi) | 2 |
| Filter (date-range) | 2 |
3. DATE, Not DATETIME
"dataType": "DATE"
"dataType": "DATETIME"
4. Always Include Global Filters
"pageType": "PAGE_TYPE_GLOBAL_FILTERS"
5. Handle NULL Values
COALESCE(field, 'Default Value')
Reference Documentation
Detailed patterns and examples are organized in reference files:
- Dashboard JSON Reference - JSON structure, page layouts, dataset configuration, variable substitution, widget versions, naming conventions
- Widget Patterns - Widget-query alignment, number formatting, parameters, chart configurations, multi-series charts, error fixes
- Deployment Guide - UPDATE-or-CREATE pattern, variable substitution, SQL validation, monitoring queries, best practices, checklists
- Jobs System Tables Dashboard - Complete production example demonstrating all patterns from this skill with System Tables integration
References
Official Documentation
Related Skills
lakehouse-monitoring-comprehensive - Monitoring setup patternssql-alerting-patterns - SQL alert configurationmetric-views-patterns - Semantic layer metric views
Summary
What to Create
- Dashboard JSON file with proper structure
- Datasets (queries for each widget)
- Pages with widget layouts
- Parameters (date filters)
- Global Filters page
Critical Rules
- 6-column grid (NOT 12!)
- KPIs: v2, Charts/Pivots: v3, Tables: v2, Sankey: v1, Choropleth: v1, Symbol Map: v2, Waterfall: v3, Histogram: v3
- DATE type for parameters (not DATETIME)
- Include Global Filters page
- Verify system table field names
- Return raw numbers for formatting
- Match widget fieldNames to query aliases
- Ratio metrics in pivots: use
disaggregated: false with aggregate expressions
- Map widgets: ensure lat/lng are DOUBLE (not STRING)
- Metric view queries: reference dimensions by
name, not by source table alias
- Cross-filtering: charts must share the same
datasetName on the same page
- Drill-through: target page filter widgets must share source chart's dataset AND field
- Choropleth admin1 (state): always include admin0 (country) field; use full state names
- Filter
defaultSelection: use typed value format {"dataType":"STRING","values":[{"value":"..."}]}
- Use
disallowAll: true on single-select filters where "All" is not meaningful
- Store counts: prefer
COUNT(DISTINCT pk) over SUM(pre_aggregated_count)
- Datasets must use
queryLines (array) — NOT query (string)
- Textboxes must use
multilineTextboxSpec — NOT textbox_spec
Time Estimate
2-4 hours for complete dashboard
Next Action
- Fill out Project Planning Template
- Create datasets with validated queries
- Build widgets with correct specifications
- Test in Databricks
- Deploy via API with validation
