databricks-aibi-dashboards

AI/BI Dashboard Skill

Create Databricks AI/BI dashboards (formerly Lakeview dashboards). Follow these guidelines strictly.

CRITICAL: MANDATORY VALIDATION WORKFLOW

You MUST follow this workflow exactly. Skipping validation causes broken dashboards.

┌─────────────────────────────────────────────────────────────────────┐
│  STEP 1: Get table schemas via get_table_stats_and_schema(catalog, schema)  │
├─────────────────────────────────────────────────────────────────────┤
│  STEP 2: Write SQL queries for each dataset                        │
├─────────────────────────────────────────────────────────────────────┤
│  STEP 3: TEST EVERY QUERY via execute_sql() ← DO NOT SKIP!         │
│          - If query fails, FIX IT before proceeding                │
│          - Verify column names match what widgets will reference   │
│          - Verify data types are correct (dates, numbers, strings) │
├─────────────────────────────────────────────────────────────────────┤
│  STEP 4: Build dashboard JSON using ONLY verified queries          │
├─────────────────────────────────────────────────────────────────────┤
│  STEP 5: Deploy via manage_dashboard(action="create_or_update")    │
└─────────────────────────────────────────────────────────────────────┘

WARNING: If you deploy without testing queries, widgets WILL show "Invalid widget definition" errors!

Available MCP Tools

Tool	Description
get_table_stats_and_schema	STEP 1: Get table schemas for designing queries
execute_sql	STEP 3: Test SQL queries - MANDATORY before deployment!
manage_warehouse (action="get_best")	Get available warehouse ID
manage_dashboard	STEP 5: Dashboard lifecycle management (see actions below)

manage_dashboard Actions

Action	Description	Required Params
create_or_update	Deploy dashboard JSON (only after validation!)	display_name, parent_path, serialized_dashboard, warehouse_id
get	Get dashboard details by ID	dashboard_id
list	List all dashboards	(none)
delete	Move dashboard to trash	dashboard_id
publish	Publish a dashboard	dashboard_id, warehouse_id
unpublish	Unpublish a dashboard	dashboard_id

Example usage:

# Create/update dashboard
manage_dashboard(
    action="create_or_update",
    display_name="Sales Dashboard",
    parent_path="/Workspace/Users/me/dashboards",
    serialized_dashboard=dashboard_json,
    warehouse_id="abc123",
    publish=True  # auto-publish after create
)

# Get dashboard details
manage_dashboard(action="get", dashboard_id="dashboard_123")

# List all dashboards
manage_dashboard(action="list")

Reference Files

What are you building?	Reference
Any widget (text, counter, table, chart)	1-widget-specifications.md
Dashboard with filters (global or page-level)	2-filters.md
Need a complete working template to adapt	3-examples.md
Debugging a broken dashboard	4-troubleshooting.md

---

Implementation Guidelines

1) DATASET ARCHITECTURE

• One dataset per domain (e.g., orders, customers, products)
• Exactly ONE valid SQL query per dataset (no multiple queries separated by ;)
• Always use fully-qualified table names: catalog.schema.table_name
• SELECT must include all dimensions needed by widgets and all derived columns via AS aliases
• Put ALL business logic (CASE/WHEN, COALESCE, ratios) into the dataset SELECT with explicit aliases
• Contract rule: Every widget fieldName must exactly match a dataset column or alias

2) WIDGET FIELD EXPRESSIONS

CRITICAL: Field Name Matching Rule The name in query.fields MUST exactly match the fieldName in encodings. If they don't match, the widget shows "no selected fields to visualize" error!

Correct pattern for aggregations:

// In query.fields:
{"name": "sum(spend)", "expression": "SUM(`spend`)"}

// In encodings (must match!):
{"fieldName": "sum(spend)", "displayName": "Total Spend"}

WRONG - names don't match:

// In query.fields:
{"name": "spend", "expression": "SUM(`spend`)"}  // name is "spend"

// In encodings:
{"fieldName": "sum(spend)", ...}  // ERROR: "sum(spend)" ≠ "spend"

Allowed expressions in widget queries (you CANNOT use CAST or other SQL in expressions):

For numbers:

{"name": "sum(revenue)", "expression": "SUM(`revenue`)"}
{"name": "avg(price)", "expression": "AVG(`price`)"}
{"name": "count(orders)", "expression": "COUNT(`order_id`)"}
{"name": "countdistinct(customers)", "expression": "COUNT(DISTINCT `customer_id`)"}
{"name": "min(date)", "expression": "MIN(`order_date`)"}
{"name": "max(date)", "expression": "MAX(`order_date`)"}

For dates (use daily for timeseries, weekly/monthly for grouped comparisons):

{"name": "daily(date)", "expression": "DATE_TRUNC(\"DAY\", `date`)"}
{"name": "weekly(date)", "expression": "DATE_TRUNC(\"WEEK\", `date`)"}
{"name": "monthly(date)", "expression": "DATE_TRUNC(\"MONTH\", `date`)"}

Simple field reference (for pre-aggregated data):

{"name": "category", "expression": "`category`"}

If you need conditional logic or multi-field formulas, compute a derived column in the dataset SQL first.

3) SPARK SQL PATTERNS

• Date math: date_sub(current_date(), N) for days, add_months(current_date(), -N) for months
• Date truncation: DATE_TRUNC('DAY'|'WEEK'|'MONTH'|'QUARTER'|'YEAR', column)
• AVOID INTERVAL syntax - use functions instead

4) LAYOUT (12-Column Grid, NO GAPS)

Every page must include "layoutVersion": "GRID_V1" alongside pageType.

{
  "name": "overview",
  "displayName": "Overview",
  "pageType": "PAGE_TYPE_CANVAS",
  "layoutVersion": "GRID_V1",
  "layout": [...]
}

Each widget has a position: {"x": 0, "y": 0, "width": 4, "height": 4}

CRITICAL: Each row must fill width=12 exactly. No gaps allowed.

Recommended widget sizes:

Widget Type	Width	Height	Notes
Text header	12	1	Full width; use SEPARATE widgets for title and subtitle
Counter/KPI	4	3-4	NEVER height=2 - too cramped!
Line/Bar chart	6	5-6	Pair side-by-side to fill row
Pie chart	6	5-6	Needs space for legend
Full-width chart	12	5-7	For detailed time series
Table	12	5-8	Full width for readability

Standard dashboard structure:

y=0:  Title (w=12, h=1) - Dashboard title (use separate widget!)
y=1:  Subtitle (w=12, h=1) - Description (use separate widget!)
y=2:  KPIs (w=4 each, h=3) - 3 key metrics side-by-side
y=5:  Section header (w=12, h=1) - "Trends" or similar
y=6:  Charts (w=6 each, h=5) - Two charts side-by-side
y=11: Section header (w=12, h=1) - "Details"
y=12: Table (w=12, h=6) - Detailed data

5) CARDINALITY & READABILITY (CRITICAL)

Dashboard readability depends on limiting distinct values:

Dimension Type	Max Values	Examples
Chart color/groups	3-8	4 regions, 5 product lines, 3 tiers
Filters	4-10	8 countries, 5 channels
High cardinality	Table only	customer_id, order_id, SKU

Before creating any chart with color/grouping:

1. Check column cardinality (use get_table_stats_and_schema to see distinct values)
2. If >10 distinct values, aggregate to higher level OR use TOP-N + "Other" bucket
3. For high-cardinality dimensions, use a table widget instead of a chart

6) QUALITY CHECKLIST

Before deploying, verify:

1. All widget names use only alphanumeric + hyphens + underscores
2. Every page has "layoutVersion": "GRID_V1"
3. All rows sum to width=12 with no gaps
4. KPIs use height 3-4, charts use height 5-6
5. Chart dimensions have ≤8 distinct values
6. All widget fieldNames match dataset columns exactly
7. Field name in query.fields matches fieldName in encodings exactly (e.g., both "sum(spend)")
8. Counter datasets: use disaggregated: true for 1-row datasets, disaggregated: false with aggregation for multi-row
9. Percent values are 0-1 (not 0-100)
10. SQL uses Spark syntax (date_sub, not INTERVAL)
11. All SQL queries tested via execute_sql and return expected data

---

Related Skills

• databricks-unity-catalog - for querying the underlying data and system tables
• databricks-spark-declarative-pipelines - for building the data pipelines that feed dashboards
• databricks-jobs - for scheduling dashboard data refreshes