docs/visualizations.md

Chart Computation and Visualization

This document describes the chart computation pipeline, covering all six chart types, the SQL-based computation strategy, the ECharts specification format, and the shared utility layer.

Design Principles

All chart computation in the Visual Notebook is performed entirely in DuckDB using SQL queries. This design choice has several important implications:

• Zero data movement to Python: The computation engine (DuckDB) and the storage engine (DuckDB) are the same, so no data is transferred between processes or serialized through Python. The SQL results are read directly into lightweight Python lists for JSON serialization.
• Columnar aggregation efficiency: DuckDB's vectorized execution engine processes aggregations efficiently, even on datasets with millions of rows.
• Consistent engine: Because all data resides in DuckDB tables (ensured by the materialization step after each operation), chart computation always has access to the full dataset without needing to convert between Polars and DuckDB.
• Server-side computation: Heavy aggregation and bucketing happen on the server, keeping the frontend lightweight. The frontend receives only the final chart specification.

Chart Types

The system supports six chart types, each with its own computation module under backend/visual_notebook/viz/:

Chart Type	Module	Description
histogram	histogram.py	Distribution of a numeric column
scatter	scatter.py	Relationship between two numeric columns
bar_topn	bar_topn.py	Top-N categories by count or sum
timeline	timeline.py	Time series with date truncation
heatmap	heatmap.py	2D aggregation grid
map	map.py	Geographic point visualization

Histogram

The histogram divides a numeric column into equal-width bins and counts the number of rows in each bin.

Computation

The histogram uses FLOOR-based bucket assignment rather than WIDTH_BUCKET (which is unavailable in DuckDB). The algorithm proceeds in two SQL queries:

Query 1: Compute the min and max values to determine bin width:

SELECT MIN("{column}") as min_val, MAX("{column}") as max_val
FROM "{table_name}"
WHERE "{column}" IS NOT NULL

Query 2: Assign each row to a bucket using floor division, then group by bucket index:

SELECT
    FLOOR(("{column}" - {min_val}) / {bin_width}) as bucket_idx,
    MIN("{column}") as bucket_min,
    MAX("{column}") as bucket_max,
    COUNT(*) as count
FROM "{table_name}"
WHERE "{column}" IS NOT NULL
GROUP BY bucket_idx
ORDER BY bucket_idx

The bin width is computed as (max_val - min_val) / bins where bins defaults to 20. Each bucket label is formatted as min-max using the fmt_num() utility.

ECharts Specification

The histogram renders as a bar chart with category x-axis (bin labels) and value y-axis (counts):

{
    "animation": True,
    "animationDuration": 600,
    "grid": {"left": 60, "right": 30, "top": 60, "bottom": 50, "containLabel": True},
    "title": {"text": "Distribution of {column}", "left": "center"},
    "tooltip": {"trigger": "item", "backgroundColor": "rgba(255,255,255,0.95)", ...},
    "xAxis": {"type": "category", "data": ["0-10", "10-20", ...]},
    "yAxis": {"type": "value", "name": "Count"},
    "series": [{"type": "bar", "data": [150, 230, ...], "itemStyle": {"color": "#5470c6"}}]
}

Edge Cases

• If all values are the same (min equals max), the bin width defaults to 1.0 to prevent division by zero.
• If the column contains only NULL values, a single "No data" category with count 0 is returned.

Scatter

The scatter plot displays the relationship between two numeric columns, with optional color grouping by a third column.

Computation

A single SQL query extracts the x and y columns (plus an optional color column). If the dataset exceeds 5,000 rows, DuckDB's USING SAMPLE clause is applied to reduce the point count for browser rendering performance:

SELECT "{x_col}", "{y_col}", "{color_col}"
FROM "{table_name}"
USING SAMPLE 5000
WHERE "{x_col}" IS NOT NULL AND "{y_col}" IS NOT NULL

When a color column is specified, the results are grouped by the color value in Python, and each group becomes a separate ECharts series.

ECharts Specification

Each series is a scatter type with symbolSize: 6. When a color column is present, a legend is added showing the group names:

{
    "series": [
        {"type": "scatter", "name": "Group A", "data": [[1, 2], [3, 4]], "symbolSize": 6, "itemStyle": {"color": "#5470c6"}},
        {"type": "scatter", "name": "Group B", "data": [[5, 6], [7, 8]], "symbolSize": 6, "itemStyle": {"color": "#91cc75"}}
    ],
    "legend": {"top": 30, "data": ["Group A", "Group B"]}
}

Bar Top-N

The bar top-N chart shows the most frequent or highest-value categories.

Computation

Two SQL patterns are used depending on whether a value column is provided:

Without value column (count mode):

SELECT "{category_col}" as cat, COUNT(*) as val
FROM "{table_name}"
WHERE "{category_col}" IS NOT NULL
GROUP BY cat
ORDER BY val DESC
LIMIT {n}

With value column (sum mode):

SELECT "{category_col}" as cat, SUM("{value_col}") as val
FROM "{table_name}"
WHERE "{category_col}" IS NOT NULL
GROUP BY cat
ORDER BY val DESC
LIMIT {n}

The default N is 15, and the sort order defaults to descending. The sort direction can be toggled via the sort_desc parameter.

ECharts Specification

The bar chart uses a linear gradient fill from the primary palette color to a lighter accent:

"itemStyle": {
    "color": {
        "type": "linear",
        "x": 0, "y": 0, "x2": 0, "y2": 1,
        "colorStops": [
            {"offset": 0, "color": "#5470c6"},
            {"offset": 1, "color": "#73c0de"}
        ]
    },
    "borderRadius": [4, 4, 0, 0]
}

Each bar also shows a value label above it ("label": {"show": True, "position": "top"}).

Timeline

The timeline chart aggregates a numeric column over a date column, producing a time series.

Computation

The date column is grouped directly (DuckDB's type system handles date truncation if the column is a DATE or TIMESTAMP type). The value column is aggregated using the specified function (default: sum):

SELECT "{date_col}" as dt, SUM("{value_col}") as val
FROM "{table_name}"
WHERE "{date_col}" IS NOT NULL
GROUP BY dt
ORDER BY dt

Supported aggregation functions: sum, mean, min, max, count. The count mode uses COUNT(*) instead of aggregating the value column.

ECharts Specification

The timeline renders as a smooth line chart with an area fill:

{
    "series": [{
        "type": "line",
        "data": [120, 200, 150, ...],
        "smooth": True,
        "symbol": "none",
        "lineStyle": {"width": 2, "color": "#5470c6"},
        "areaStyle": {
            "color": {
                "type": "linear",
                "x": 0, "y": 0, "x2": 0, "y2": 1,
                "colorStops": [
                    {"offset": 0, "color": "rgba(84,112,198,0.3)"},
                    {"offset": 1, "color": "rgba(84,112,198,0.05)"}
                ]
            }
        }
    }]
}

The x-axis uses boundaryGap: False to align the line with the axis tick marks, and the tooltip triggers on the axis for cleaner crosshair behavior.

Heatmap

The heatmap shows the aggregation of a value column across two categorical dimensions.

Computation

A two-dimensional GROUP BY with aggregation:

SELECT "{x_col}", "{y_col}", MEAN("{value_col}") as val
FROM "{table_name}"
WHERE "{x_col}" IS NOT NULL AND "{y_col}" IS NOT NULL
GROUP BY "{x_col}", "{y_col}"
ORDER BY "{x_col}", "{y_col}"

The x and y values are extracted into sorted unique lists and mapped to integer indices. Each data point is represented as [x_index, y_index, value].

ECharts Specification

The heatmap uses ECharts' built-in heatmap series type with a diverging color scale:

{
    "visualMap": {
        "min": min_val,
        "max": max_val,
        "calculable": True,
        "orient": "horizontal",
        "left": "center",
        "bottom": 0,
        "inRange": {
            "color": ["#313695", "#4575b4", "#74add1", "#abd9e9", "#fee090", "#fdae61", "#f46d43", "#d73027", "#a50026"]
        }
    },
    "series": [{
        "type": "heatmap",
        "data": [[0, 0, 12.5], [0, 1, 8.3], ...],
        "label": {"show": True, "fontSize": 10, "formatter": "{c}"},
        "emphasis": {"itemStyle": {"shadowBlur": 10, "shadowColor": "rgba(0,0,0,0.5)"}}
    }]
}

The color range transitions from deep blue (low) through yellow to deep red (high), following the classic spectral color scheme. The calculable property allows users to filter the value range interactively.

Map

The map visualization extracts latitude and longitude columns and computes geographic bounds and center point.

Computation

Similar to scatter, a SQL query extracts the coordinates with optional sampling:

SELECT "{lat_col}", "{lon_col}", "{label_col}"
FROM "{table_name}"
USING SAMPLE 5000
WHERE "{lat_col}" IS NOT NULL AND "{lon_col}" IS NOT NULL

The results are processed in Python to compute:

• Points: A list of {lat, lon, label?} dictionaries.
• Bounds: The minimum and maximum latitude and longitude.
• Center: The midpoint of the bounds.

Output Format

Unlike the other chart types, the map does not produce an ECharts specification. Instead, it returns a Leaflet-compatible data structure:

{
    "type": "map",
    "points": [{"lat": 40.7, "lon": -74.0, "label": "NYC"}, ...],
    "bounds": {"minLat": 25.0, "maxLat": 48.0, "minLon": -125.0, "maxLon": -67.0},
    "center": {"lat": 36.5, "lon": -96.0},
    "pointCount": 1500
}

The frontend renders this data using an SVG-based point plot (with a future migration path to full Leaflet rendering).

Common Utilities (_common.py)

All chart modules share a set of utility functions that ensure consistent visual styling:

base_chart() -> dict

Returns the base ECharts configuration shared by all chart types:

{
    "animation": True,
    "animationDuration": 600,
    "grid": {"left": 60, "right": 30, "top": 60, "bottom": 50, "containLabel": True}
}

The grid settings provide adequate margins for axis labels and titles.

PALETTE and palette_colors(n)

A 10-color professional palette derived from ECharts' default theme:

PALETTE = [
    "#5470c6", "#91cc75", "#fac858", "#ee6666", "#73c0de",
    "#3ba272", "#fc8452", "#9a60b4", "#ea7ccc", "#48b8d0",
]

palette_colors(n) returns the first n colors, cycling through the palette if n > 10.

axis_style(name, rotate=0) -> dict

Returns a standardized axis configuration with rotated labels, dashed grid lines, and centered axis names:

{
    "name": name,
    "nameLocation": "middle",
    "nameGap": 30,
    "axisLabel": {"rotate": rotate, "fontSize": 11},
    "axisLine": {"lineStyle": {"color": "#999"}},
    "splitLine": {"lineStyle": {"type": "dashed", "color": "#eee"}}
}

fmt_num(value, decimals=2) -> str

Formats numbers for display, abbreviating large values:

• Values >= 1,000,000 are displayed as 1.23M
• Values >= 1,000 are displayed as 1.23K
• Smaller values use the specified decimal places

title(text, subtitle="") -> dict

Returns a centered title configuration with optional subtitle styling.

tooltip_style() -> dict

Returns a white-background tooltip with border, suitable for all chart types. The default trigger is "item", but timeline charts override it to "axis".

Error Handling in Chart Computation

The route handler wraps chart computation in a try-except block that converts errors to HTTP 500 responses:

• KeyError: Missing required parameters produce a 422 error with the missing parameter name.
• General exceptions: Computation failures produce a 500 error with the failure reason.
• Empty datasets: Each chart type handles the empty-data case gracefully, returning a chart with "No data" labels or zero values.