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:

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:

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

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

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

Common Filters:

• Date Range (always include)
• Location/Store/Facility (dimension)
• Category/Type (dimension)
• Status/State (dimension)

Charts to Include (3-5 visualizations)

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

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

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

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

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

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

…(truncated)