# Databricks Aibi Dashboards

> 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.

- Skill: `databricks-solutions/databricks-aibi-dashboards` (Agent Skill, multi-file: 11 files)
- Install (CLI): `npx skillmds@latest add databricks-solutions/databricks-aibi-dashboards`
- Raw SKILL.md: https://api.skillmd.com/api/skills/databricks-solutions/databricks-aibi-dashboards/raw
- Safety review: pending
- Works with: Claude Code, Claude.ai, OpenAI Codex
- Category: Coding & Dev Tools, Data & Analytics
- Author: databricks-solutions (https://skillmd.com/u/databricks-solutions)
- Updated: 2026-09-17
- Page: https://skillmd.com/skills/databricks-solutions/databricks-aibi-dashboards

---


# 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](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`](../../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:**
1. SQL queries from Metric Views or Gold tables
2. AI/BI Dashboard via UI (drag-and-drop layout)
3. 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):**
```sql
-- 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.

```sql
-- ✅ 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

```sql
-- ✅ 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:**
```json
{
  "encodings": {
    "color": { "fieldName": "category", "scale": { "type": "categorical" } },
    "angle": { "fieldName": "value", "scale": { "type": "quantitative" } }
  }
}
```

**Bar Charts:**
```json
{
  "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.

```json
{
  "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

```json
// 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:**
```sql
-- ✅ 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

```json
{
  "keyword": "time_range",
  "dataType": "DATETIME_RANGE",
  "defaultSelection": {
    "range": {
      "min": { "dataType": "DATETIME", "value": "now-30d/d" },
      "max": { "dataType": "DATETIME", "value": "now/d" }
    }
  }
}
```

**SQL Access:**
```sql
WHERE date BETWEEN :time_range.min AND :time_range.max
```

#### Date Parameters (Static Dates)

##### ✅ Correct: DATE with Static Values

```json
{
  "displayName": "Start Date",
  "keyword": "start_date",
  "dataType": "DATE",
  "defaultSelection": {
    "values": {
      "dataType": "DATE",
      "values": [{"value": "2024-01-01"}]
    }
  }
}
```

##### ❌ Wrong: DATETIME with Dynamic Expressions

```json
// 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:**
```sql
-- 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)

```json
{
  "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)

```json
{
  "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)

```json
{
  "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)

```json
{
  "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)

```json
{
  "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)

```json
{
  "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):
```json
{
  "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**:
```json
{
  "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`:

```json
{
  "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:
```sql
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.

```json
{
  "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:**

```json
{
  "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:**

```json
{
  "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:
```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)

```json
{
  "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:**
```json
{
  "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)

```json
{
  "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).

```json
{
  "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.

```json
{
  "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-Filteri

…(truncated)