Custom Visualizations in AI/BI Dashboards: Build a Pareto Chart with Vega-Lite

[pstyld]

Databricks AI/BI dashboards ship with a solid set of visualization types. But every so often you want something the point-and-click widgets can't quite do. 

That's what custom visualizations (currently Public Preview) are for. You describe the chart in a Vega-Lite spec (a block of JSON), and that spec controls every part of the chart in one place. Since your custom viz reads your Unity Catalog (UC) data through the dashboards dataset, you get all the governance of UC you’ve come to expect on Databricks.

To show it off, we'll build a custom Pareto chart of manufacturing defects. It has the usual bars and cumulative line, but the focus is on what a built-in combo widget in AI/BI Dashboards can’t do in the UI today: 

• Sort the bars by value and add markers.
• Shade a band over the key drivers and minor issues.
• Add multiple text annotations, placed in specific spots, that label themselves from the data.

The final custom visualization we will create below. Feel free to follow along in Databricks Free Edition!

Vega-Lite Overview

Vega-Lite is a JSON grammar for charts. Instead of writing drawing code, you describe the chart it renders. To summarize what’s needed for this post, we draw attention to the following key ideas:

• mark is the shape to draw: "bar", "line", "point", "area", "rule", "text".
• encoding maps a column to a visual channel: x, y, color, and so on.
• layer stacks multiple marks on the same axes (this is how bars and a line share one chart).
• The width, height, and config settings make a chart resize to fit its container. 

Connect your data to the custom visualization

• Point the spec at your dataset with "data": { "name": "databricks_query" }.
• Every column you use must first be added to the widget's Fields section in the AI/BI dashboard.
• Reference columns with "field": "columnName".

Create the Custom Pareto Chart

Step 1: Create the dashboard

1. In the main navigation bar, select Dashboards.
2. Click Create dashboard.
3. At the top left of the dashboard definition panel, you'll see a placeholder name like New Dashboard 20XX-01-01 12:00:00
4. Click the placeholder name and change it to FirstName-LastInitials - Custom Pareto
5. At the top right, select a Serverless SQL Warehouse.

Step 2: Create the data

1. In the Dashboard, select the Data tab.
2. Choose Add SQL dataset.
3. Add the following SQL code:

   SELECT * FROM VALUES
     ('Surface Scratch',   450, 28.13,  28.13, 'Key Issue Drivers', 1),
     ('Paint Defect',      320, 20.00,  48.13, 'Key Issue Drivers', 2),
     ('Missing Component', 240, 15.00,  63.13, 'Key Issue Drivers', 3),
     ('Alignment Issue',   180, 11.25,  74.38, 'Key Issue Drivers', 4),
     ('Loose Fastener',    140,  8.75,  83.13, 'Key Issue Drivers', 5),
     ('Electrical Fault',   95,  5.94,  89.06, 'Minor Issues',      6),
     ('Packaging Damage',   75,  4.69,  93.75, 'Minor Issues',      7),
     ('Label Error',        55,  3.44,  97.19, 'Minor Issues',      8),
     ('Sensor Failure',     35,  2.19,  99.38, 'Minor Issues',      9),
     ('Other',              10,  0.63, 100.00, 'Minor Issues',     10)
   AS t(defect_category, defect_count, defect_percent, cumulative_percent, pareto_group, sort_order);

    
4. Rename the dataset to pareto_summary.
5. Select Run to execute the cell and view the data. Your table will look like this:

A custom viz draws what's in your dataset to start, so for a Pareto chart the data summary is where the majority of work happens.

Unlike a plain bar chart, the summary data in a Pareto is order-dependent. It requires:

• The categories have to be ranked largest to smallest first, because the cumulative-percentage line is a running total computed in that order (the first category, then the first two, then the first three, up to 100%).
• Each row needs its count, cumulative percentage, sort order, and group flag computed up front. This has been completed for you in the example down below.
• Sort it wrong and the line, the split, and the whole chart go with it.
• The 80% split between the key drivers and the minor issues then falls out of that running total.

The columns that matter for the chart

• defect_category specifies the specific defect.
• defect_count drives the bar height.
• defect_percent is the percentage of defects by category.
• cumulative_percent is the running total, which becomes the line.
• pareto_group flags each category as a Key Issue Driver or a Minor Issue, used for bar color. Determined by the 80% threshold.
• sort_order ranks categories by count so the bars stay in descending order (the defining feature of a Pareto chart).

Step 3: Build the custom visualization

In the dashboard UI

1. Select the Untitled page.
2. Add a visualization widget to the canvas. 
3. Expand its width across ¾ of the canvas and to a height of about 8 blocks.
4. Add the title: Defect Category Pareto Analysis
5. Add the description: Focus improvement efforts on the categories contributing to the first 80% of defects. These key issue drivers offer the greatest opportunity for impact for improvement.
6. Set its Dataset to pareto_summary (if you have only one table, it will automatically be set).
7. In the visualization field, under the Advanced section, select Custom Viz.
8. In the Fields section, add these columns (the names are how the spec references them): 

• cumulative_percent
• defect_count
• sort_order
• defect_category
• pareto_group

With your widget set, now paste the spec below into the Vega-Lite Specification editor.

{
  "$schema": "https://vega.github.io/schema/vega-lite/v6.json",
  "width": "container",
  "height": "container",
  "data": { "name": "databricks_query" },
  "encoding": {
    "x": {
      "field": "defect_category",
      "type": "nominal",
      "sort": { "op": "min", "field": "sort_order", "order": "ascending" },
      "axis": { "title": "Defect category", "labelAngle": -40 }
    }
  },
  "layer": [
    {
      "transform": [{ "filter": "datum.pareto_group === 'Key Issue Drivers'" }],
      "mark": { "type": "rect", "color": "#FF5F46", "opacity": 0.15 }
    },
    {
      "mark": { "type": "bar", "width": { "band": 0.8 } },
      "encoding": {
        "y": {
          "field": "defect_count",
          "type": "quantitative",
          "axis": { "title": "Defect count" }
        },
        "color": {
          "field": "pareto_group",
          "type": "nominal",
          "scale": {
            "domain": ["Key Issue Drivers", "Minor Issues", "Cumulative %"],
            "range": ["#FF5F46", "#C4CCD6", "#1B5162"]
          },
          "legend": { "title": null, "orient": "top" }
        }
      }
    },
    {
      "layer": [
        {
          "mark": { "type": "area", "color": "#1B5162", "opacity": 0.05, "line": false },
          "encoding": {
            "y": { "field": "cumulative_percent", "type": "quantitative", "axis": { "title": "Cumulative %", "orient": "right" } }
          }
        },
        {
          "mark": { "type": "line", "point": { "size": 80, "filled": true } },
          "encoding": {
            "y": { "field": "cumulative_percent", "type": "quantitative" },
            "color": { "datum": "Cumulative %", "type": "nominal" }
          }
        },
        {
          "transform": [
            { "calculate": "round(datum.cumulative_percent) + '%'", "as": "cum_label" }
          ],
          "mark": { "type": "text", "dy": -12, "fontSize": 12, "fontWeight": "bold", "color": "#1B5162" },
          "encoding": {
            "y": { "field": "cumulative_percent", "type": "quantitative" },
            "text": { "field": "cum_label", "type": "nominal" }
          }
        },
        {
          "mark": { "type": "rule", "color": "#0b2026", "strokeDash": [6, 4], "size": 2 },
          "encoding": {
            "x": null,
            "y": { "datum": 80 }
          }
        },
        {
          "transform": [
            { "filter": "datum.defect_category === 'Label Error'" }
          ],
          "mark": { "type": "text", "text": "80% of defects", "align": "center", "baseline": "bottom", "dy": -1, "fontSize": 13, "fontWeight": "bold", "color": "#0b2026" },
          "encoding": {
            "y": { "datum": 80 }
          }
        },
        {
          "transform": [
            { "filter": "datum.pareto_group === 'Key Issue Drivers'" },
            { "joinaggregate": [{ "op": "max", "field": "sort_order", "as": "max_rank" }] },
            { "filter": "datum.sort_order === round((1 + datum.max_rank) / 2)" }
          ],
          "mark": { "type": "text", "text": "Fix these first", "align": "center", "baseline": "middle", "fontSize": 15, "fontWeight": "bold", "color": "#98102A" },
          "encoding": {
            "y": { "datum": 92 }
          }
        },
        {
          "transform": [
            { "calculate": "datum.pareto_group === 'Key Issue Drivers' ? 1 : 0", "as": "is_kd" },
            { "calculate": "datum.pareto_group === 'Key Issue Drivers' ? datum.cumulative_percent : 0", "as": "kd_cum_val" },
            { "joinaggregate": [
                { "op": "count", "as": "total_cats" },
                { "op": "sum", "field": "is_kd", "as": "kd_count" },
                { "op": "max", "field": "kd_cum_val", "as": "kd_cum" }
            ] },
            { "filter": "datum.sort_order === round(datum.total_cats * 0.7)" },
            { "calculate": "datum.kd_count + ' of ' + datum.total_cats + ' categories drive ' + round(datum.kd_cum) + '% of all defects'", "as": "summary" }
          ],
          "mark": { "type": "text", "align": "center", "baseline": "middle", "fontSize": 15, "fontWeight": "bold", "color": "#1B3139" },
          "encoding": {
            "y": { "datum": 55 },
            "text": { "field": "summary", "type": "nominal" }
          }
        }
      ]
    }
  ],
  "resolve": { "scale": { "y": "independent" } },
  "config": { "autosize": { "type": "fit", "contains": "padding" } }
}

In the era of AI Agents and LLMs, you should expect to not have to write this by hand and the same is true on Databricks. This was generated with Genie Code and refined from there, so treat the JSON as something to read and modify as you would with any piece of code generated by AI.

Spec overview

We will highlight a few main areas of interest in this task:

Data binding and sorting

• The spec uses databricks_query as its dataset and sorts defect categories using sort_order to control the x-axis order.

Layering builds the visualization

• Bars, the cumulative line, shaded regions, labels, reference lines, and annotations are separate layers drawn together into one chart.

Independent y-axes

• Defect counts use the left axis while cumulative percentage uses the right. 
• Independent y-scales allow both measures to coexist.

Transforms add logic 

• Filters, calculations, and aggregations identify key issue drivers, shading, calculate percentages, and determine where annotations should appear.

Dynamic annotations

• Summary text like 5 of 10 categories drive 83% of all defects is calculated from the data, so it updates automatically when the underlying data changes.

Step 3: Polish with Genie Code (Output can vary)

You don't have to manually write or update the JSON. Genie Code can create or edit the spec for you in natural language throughout this entire process.

Use Genie Code to update the current visualization.

1. Select the Genie Code icon at the top right of the workspace.
2. Then with the custom viz selected, try a prompt like:

This visualization is a Vega-Lite custom viz. Edit the JSON spec directly. Apply these formatting changes and leave everything else unchanged:
1. Remove the horizontal gridlines (the y-axis grid).
2. Set the axis titles ("Defect category", "Defect count", "Cumulative %") to font size 14.
3. Set the axis tick labels on all three axes to font size 13.
4. On the right Cumulative % axis only, append a "%" to each tick value.
5. Set the legend label font size to 13.

Genie applies the edits and you keep iterating from there!

Building it with AI (Genie Code here): what to watch for

AI output varies. If you build this from scratch or update with AI, keep an eye on a few things:

• You may get a combo chart, not a custom viz. Combo widgets have some limitations when creating a Pareto chart. Some options live only in the widget JSON spec, not the UI. For example, sort-by-value and line markers can exist only in the widget's JSON, and editing the widget in the UI can silently revert them. 
• Dynamic, computed annotations and shaded bands aren't available in the combo chart, those need a custom viz.
• Make sure you sort the bars by their values (or a computed rank), not a custom sort order. A manual sort looks right today but won't re-rank when the data changes.
• When creating a custom visualization, it's important to understand your data. AI may hardcode values in the JSON spec, so if your backend data is updated the visualization can be off. Always check the work.

Your turn! Can you make it better?

The Vega-Lite spec above is one solution, but it can be improved. See what you can come up with. Try it in Databricks Free Edition!

Made it better? Share your spec and a screenshot in the comments. The best ideas help everyone learn new techniques for building custom visualizations.

Learn more

• Custom visualizations in AI/BI dashboards
• Vega-Lite documentation
• Genie Code
• Databricks Free Edition
• New to AI/BI Dashboards? The AI/BI for Data Analysts course covers building interactive dashboards, creating visualizations, using Genie Code and AI-assisted analytics, publishing, scheduling, and more.

Special thanks to Maggie Li, Matthew McCoy and Marcelino Mayorga.