一键在 Manus 中运行任何 Skill

databricks-aibi-dashboards

星标5

分支6

更新时间2026年6月5日 23:12

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.

安装

用 Codex 或 Claude 帮你安装复制这段 Prompt，粘贴到 Codex、Claude 或其他助手里，让它检查 Skill 页面并帮你完成安装。

在 Manus 中运行

来源

databricks-solutions

databricks-solutions/vibe-coding-workshop-template

打开 GitHub 仓库查看创作者相关仓库

下载

在 Manus 中运行

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 behavior
common/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 workflow
databricks-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):

-- Use MEASURE() function for semantic metrics
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.

-- ✅ CORRECT
SELECT COUNT(*) AS total_queries FROM ...
-- Widget: "fieldName": "total_queries"

-- ❌ WRONG
SELECT COUNT(*) AS query_count FROM ...
-- Widget: "fieldName": "total_queries"  -- MISMATCH!

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

-- ✅ CORRECT - Access window struct
SELECT window.start AS window_start FROM monitoring_table
WHERE window.start BETWEEN :time_range.min AND :time_range.max

-- ✅ CORRECT - CASE pivot for generic metrics
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

-- ✅ CORRECT - Custom drift metrics are direct columns
SELECT 
  window.start AS window_start,
  success_rate_drift,  -- Direct column!
  cost_drift_pct       -- Direct column!
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,     // Column position: 0-5 (6-column grid)
    "y": 0,     // Row position: any positive integer
    "width": 3, // Width: 1, 2, 3, 4, or 6 (must sum to ≤6 per row)
    "height": 6 // Height: 1, 2, 6, 9 are common values
  }
}

Grid Layout Patterns

// Two widgets side-by-side (each 3 columns)
{"x": 0, "y": 0, "width": 3, "height": 6}  // Left
{"x": 3, "y": 0, "width": 3, "height": 6}  // Right

// Three widgets across (each 2 columns)
{"x": 0, "y": 0, "width": 2, "height": 6}  // Left
{"x": 2, "y": 0, "width": 2, "height": 6}  // Center
{"x": 4, "y": 0, "width": 2, "height": 6}  // Right

// KPI row (6 counters, 1 column each)
{"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}

// Full-width chart
{"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:

-- ✅ CORRECT - Return raw decimal
SELECT 
  COUNT(CASE WHEN status = 'success' THEN 1 END) * 1.0 / COUNT(*) AS success_rate
FROM job_runs
-- Returns: 0.85 → Widget displays as "85%"

-- ❌ WRONG - Formatted string
SELECT 
  CONCAT(ROUND(success_count * 100.0 / total_count, 2), '%') AS success_rate
FROM job_runs
-- Returns: "85.00%" → Widget cannot parse

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

// This will NOT work
{
  "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:

-- Generic metrics from profile table
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

-- Custom drift metrics (direct columns)
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: categorical
angle.scale: quantitative

Bar Charts:

x.scale: categorical
y.scale: quantitative

Line Charts:

x.scale: temporal
y.scale: quantitative
color.scale: categorical (for multi-series)

Area Charts:

x.scale: temporal
y.scale: quantitative
color.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'
  -- ... all states
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)

-- Note: Column is 'name', NOT 'job_name'
SELECT workspace_id, job_id, name, description, run_as
FROM system.lakeflow.jobs
WHERE delete_time IS NULL

`system.lakeflow.job_task_run_timeline`

-- Note: NO job_name column! JOIN with jobs table
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:

-- ✅ CORRECT
FROM ${catalog}.${gold_schema}.fact_usage
FROM ${catalog}.${gold_schema}_monitoring.fact_usage_profile_metrics

-- ❌ WRONG
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

# ❌ WRONG — uploads the wrong bytes; the file is corrupt on arrival.
ws.workspace.import_(
    path=target_path,
    content=rendered.encode("utf-8"),
    format=ImportFormat.AUTO,
    overwrite=True,
)

# ✅ CORRECT — base64-encode the UTF-8 bytes then decode to ASCII str.
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 validation
validate_widget_encodings.py - Widget-query alignment

Implementation Guide

Phase-by-Phase Checklist

Phase 0: Input Validation (5 min) — MANDATORY

Verify all user-referenced files exist (plan files, manifests, schemas)
- If a referenced file is missing: STOP and ask the user for the correct path or alternative
- NEVER silently reconstruct requirements from other sources
Read observability manifest (plans/manifests/observability-manifest.yaml) if it exists
- Check dashboards: section for pre-planned dashboard specs
Load prerequisite skills (see "Mandatory Skill Dependencies" above)

Phase 1: Planning (30 min)

Identify KPIs to display
List required filters (date, dimensions)
Plan page structure and layout
Sketch widget placement (grid positions)

Phase 2: Datasets (30 min)

Create dataset for each unique query
Use queryLines array format (NOT query string)
Add "All" option to filter datasets
Handle NULL values with COALESCE
Test queries in SQL editor first

Phase 3: Widgets (1-2 hours)

Create KPI counters (version 2)
Create charts (version 3)
Create tables (version 2)
Create filter widgets (version 2)
Position using 6-column grid

Phase 4: Parameters (15 min)

Add date parameters (DATE type, static defaults)
Link parameters to dataset queries
Test parameter binding

Phase 5: Styling (15 min)

Apply Databricks theme colors
Add titles and descriptions to all widgets
Verify consistent formatting

Phase 6: Testing (30 min)

Import dashboard JSON
Test all filters
Verify widget snapping (6-column grid)
Check data accuracy

Verification Checklist

Pre-Deployment

SQL Validation passed - All queries return results without error
Widget Encoding Validation passed - All fieldNames match query aliases
Git diff reviewed - No accidental deletions or regressions
Local testing done - Validated in dev environment

Widget-Query Alignment

Widget fieldName matches query output alias exactly
Format type matches expected value type (raw numbers for percent/currency)
All parameters are defined in dataset's parameters array
Time range uses correct access pattern (:time_range.min, :time_range.max)

Monitoring Query Validation

Uses window.start not window_start
Custom AGGREGATE/DERIVED metrics selected as direct columns
Schema is ${catalog}.${gold_schema}_monitoring
Includes WHERE column_name = ':table' AND slice_key IS NULL

Chart Validation

Pie charts: color.scale: categorical, angle.scale: quantitative
Bar charts: x.scale: categorical, y.scale: quantitative
Pivot tables: Ratio metrics use disaggregated: false. Always use Pattern A (dataset columns + MEASURE() + cell encoding) — it is the only pattern visible in the UI editor. Pattern B (inline expressions) is invisible in draft mode. Neither uses transform.
Point maps: Use widgetType: "symbol-map" (v2) with nested coordinates encoding; latitude/longitude fields are DOUBLE type (not STRING)
Choropleth maps: Use widgetType: "choropleth-map" (v1) with region encoding including geographicRole
Table widgets: Uses version: 2, no invalid properties

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")

# Fix hierarchy order
data = reorder_widget_query_fields(data, "my-pivot", ["region", "market", "store_id"])
data = reorder_widget_encoding_rows(data, "my-pivot", ["region", "market", "store_id"])

# Add dataset custom calculations (Pattern A)
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

// Wrong - widget won't position correctly
{"width": 6}  // This is FULL width, not half!

❌ Mistake 2: Wrong Widget Version

// Wrong - KPIs must use version 2
"version": 3,
"widgetType": "counter"

❌ Mistake 3: DATETIME Parameters

// Wrong - use DATE type
"dataType": "DATETIME"

❌ Mistake 4: Missing "All" Option

-- Wrong - no way to clear filter
SELECT DISTINCT store_name FROM stores

❌ Mistake 5: Assuming Field Names

-- Wrong - job_name doesn't exist in this table!
SELECT job_name FROM system.lakeflow.job_task_run_timeline

❌ Mistake 6: SUM on Ratio Metrics in Pivots

// Wrong - SUM of pre-computed averages is mathematically incorrect at rolled-up levels
"values": [{"fieldName": "precomputed_avg", "transform": "SUM"}]

// Correct (Pattern A — deep hierarchies) - use dataset columns + MEASURE()
// Dataset: "columns": [{"displayName": "Avg Rev", "expression": "SUM(`revenue`) / NULLIF(SUM(`store_days`), 0)"}]
// Widget: "fields": [{"name": "measure(Avg Rev)", "expression": "MEASURE(`Avg Rev`)"}]
// Encodings: "cell": {"fields": [{"fieldName": "measure(Avg Rev)", "cellType": "text"}], "displayAs": "columns"}

❌ Mistake 7: Using `query` Instead of `queryLines` in Datasets

// Wrong - Lakeview editor will not parse the dataset and ALL widgets will error
{"name": "ds_example", "query": "SELECT * FROM table"}

// Correct - use queryLines array + catalog + schema
{"name": "ds_example", "queryLines": ["SELECT * FROM table"], "catalog": "my_catalog", "schema": "my_schema"}

❌ Mistake 8: Using `textbox_spec` Instead of `multilineTextboxSpec`

// Wrong - Lakeview editor will not render the textbox
{"textbox_spec": "### Title"}

// Correct
{"multilineTextboxSpec": {"lines": ["### Title"]}}

❌ Mistake 9: Using `values` Encoding with `cubeGroupingSets` for Ratio Metrics

// Wrong - values with cubeGroupingSets causes ratio metrics to appear as extra rows
"cubeGroupingSets": {"sets": [{"fieldNames": ["region", "market"]}, {}]},
"encodings": {"values": [{"fieldName": "avg_revenue", "displayName": "Avg Revenue"}]}

// Correct - use dataset columns + MEASURE() + cell encoding (Pattern A)
"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

-- Wrong - point map will render blank
SELECT latitude, longitude FROM dim_location  -- STRING type

-- Correct - cast in upstream MV
SELECT CAST(latitude AS DOUBLE) AS latitude, CAST(longitude AS DOUBLE) AS longitude

❌ Mistake 11: Using Source Table Prefix in Metric View Queries

-- Wrong - metric views flatten dimensions into a single namespace
WHERE dim_location.is_same_store = 1

-- Correct - use bare dimension name
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):

-- ❌ WRONG: WHERE dim_location.is_same_store = 1
-- ✅ CORRECT: WHERE is_same_store = 1

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!)

// ✅ Correct
{"width": 3}  // Half width

// ❌ Wrong
{"width": 6}  // This is full width in 6-column grid!

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

// ✅ Correct
"dataType": "DATE"

// ❌ Wrong
"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 patterns
sql-alerting-patterns - SQL alert configuration
metric-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

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"}]}

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-aibi-dashboards

同仓库更多 Skills

同仓库更多 Skills

Databricks AI/BI Dashboards

Overview

When to Use This Skill

Mandatory Skill Dependencies

🚀 Quick Start (2 Hours)

📋 Project Planning Template

Dashboard Purpose

Data Sources

KPIs to Display (3-6 key metrics)

Filters Required

Charts to Include (3-5 visualizations)

Dashboard Pages

Input Required Summary

Quick Reference

Top 10 Critical Rules

Widget-Query Alignment

Number Formatting

Monitoring Table Patterns

Chart Scale Requirements

Critical Rules (Production-Grade)

⚠️ CRITICAL PRINCIPLES

1. Grid System (6-Column, NOT 12!)

⚠️ ALWAYS Use 6-Column Grid (NOT 12!)

Grid Layout Patterns

Common Height Values

2. Widget-Query Column Alignment

3. Number Formatting

4. Parameter Configuration

Time Range Pattern

Date Parameters (Static Dates)

✅ Correct: DATE with Static Values

❌ Wrong: DATETIME with Dynamic Expressions

5. Monitoring Table Schema

Window Struct

Generic Metrics

Custom Drift Metrics

6. Chart Scale Properties

Required Scales

Complete Widget Specifications

KPI Counter (Version 2)

Bar Chart (Version 3)

Line Chart (Version 3)

Pie Chart (Version 3)

Table Widget (Version 2)

Pivot Table (Version 3)

Pivot with Additive Measures Only (disaggregated: true)

Pivot with Ratio Metrics — Two Patterns

Pattern A: Dataset columns + MEASURE() (for deep hierarchy drill-down)

Pattern B: Inline Widget Query Expressions (for flat/shallow groupings)

Pattern Comparison

Point Map / Symbol Map (Version 2)

Choropleth Map (Version 1)

Filter Widget (Single Select)

Default Selection and disallowAll

Filter Widget (Multi Select)

Waterfall Chart (Version 3)

Histogram (Version 3)

Cross-Filtering and Drill-Through Patterns

Cross-Filtering (Same Page, Automatic)

Drill-Through (Cross-Page Navigation)

COUNT(DISTINCT pk_column) for Store Counts

Dashboard Structure Patterns

Standard Dashboard Layout

Complete Dashboard Template

Global Filters Page

Query Patterns & Best Practices

Pattern: "All" Option for Filters

Pattern: Dynamic Filtering

Pattern: Handle NULL Values

Pattern: Date Range

Pattern: SCD2 Latest Records

Pattern: Multi-Series Charts

Pattern: Stacked Area Charts

Pattern: Metric View Queries Using MEASURE()

System Tables Reference

⚠️ Always Verify Field Names!

system.lakeflow.jobs (SCD2)

Pattern A: Dataset `columns` + `MEASURE()` (for deep hierarchy drill-down)

Default Selection and `disallowAll`

`COUNT(DISTINCT pk_column)` for Store Counts

`system.lakeflow.jobs` (SCD2)

`system.lakeflow.job_task_run_timeline`

`system.compute.clusters`

❌ Mistake 7: Using `query` Instead of `queryLines` in Datasets

❌ Mistake 8: Using `textbox_spec` Instead of `multilineTextboxSpec`

❌ Mistake 9: Using `values` Encoding with `cubeGroupingSets` for Ratio Metrics

L6: Dataset `columns` for Custom Calculated Measures

L8: `disallowAll` Prevents Silent Double-Counting