shield_lock
JinDaGe
← Back to skills
extension
Category: Content & MediaNo API key required

databricks-aibi-dashboards

Create Databricks AI/BI dashboards. CRITICAL: You MUST test ALL SQL queries via execute_sql BEFORE deploying. Follow guidelines strictly.

personAuthor: jakexiaohubgithub
open_in_newRepositorydownloadDownload package

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