8.2 Chart Types & Visualization Selection

8.2 Chart Types & Visualization Selection

Exam Focus: Selecting the correct chart type based on data dimensionality, business questions, and performance constraints is heavily tested on the Databricks Data Analyst certification. Candidates must understand visual design principles, pie chart slice thresholds, dual-axis configuration, and server-side aggregation strategies required when dataset size exceeds visual rendering limits (10,000 data points).

Visualization Design Framework for Databricks Analysts

Effective visualization design transforms complex SQL query outputs into intuitive, actionable insights. In Databricks AI/BI Dashboards, visualization widgets receive data from defined dataset SQL queries. Selecting an inappropriate chart type can obscure data trends, mislead decision-makers, or degrade browser rendering performance. Analysts must evaluate three primary criteria when selecting a visualization:

1. Data Dimensionality: Continuous numeric variables, discrete categorical variables, or temporal (time-series) dimensions.
2. Analytical Objective: Categorical comparison, part-to-whole decomposition, trend identification, correlation analysis, or single-value KPI tracking.
3. Cardinality & Scale: The number of unique categories or total data points returned by the query.

---

Categorical & Part-to-Whole Visualizations

Bar and Column Charts

Bar and column charts are the workhorses of categorical data analysis:

• Vertical Column Charts: Best suited for comparing discrete categories across ordinal time intervals (e.g., quarterly revenue across 4 quarters).
• Horizontal Bar Charts: Recommended when category names are long (e.g., product titles or URL paths) or when dealing with numerous categories. Horizontal alignment allows readable text labels without truncation.
• Stacked Bar Charts: Display total category volumes while showing the relative contribution of sub-categories. However, comparing sub-category segments above the baseline becomes difficult due to varying starting points.
• 100% Stacked Bar Charts: Normalize category totals to 100%, focusing strictly on relative percentage composition rather than absolute volumes.

Pie and Donut Charts: The 5-Slice Rule

Pie and donut charts display part-to-whole relationships where all categories sum to 100% of a single total.

CRITICAL EXAM RULE: Pie and Donut charts must never exceed 5 slices. When a categorical dimension contains more than 5 slices, human visual perception fails to accurately compare relative slice angles and areas.

When dealing with high-cardinality categorical data (e.g., 15 sales regions), analysts must apply one of two remediation techniques:

1. SQL Grouping: Use a SQL CASE statement to group minor categories into a consolidated 'Other' bucket, ensuring total slices remain $\le 5$.
2. Chart Conversion: Convert the visualization into a horizontal bar chart sorted in descending order by value.

-- SQL Strategy: Binning minor categories into 'Other' for Pie Chart compliance
SELECT 
    CASE WHEN rank <= 4 THEN region ELSE 'Other' END AS display_region,
    SUM(sales_amount) AS total_sales
FROM (
    SELECT region, sales_amount, DENSE_RANK() OVER (ORDER BY SUM(sales_amount) DESC) AS rank
    FROM main.sales.gold_orders
    GROUP BY region, sales_amount
)
GROUP BY 1;

---

Time-Series, Dual-Axis & Correlation Visualizations

Line and Area Charts

Line charts represent continuous time-series data, enabling quick identification of trends, seasonality, and sudden anomalies. Area charts shade the region below the line, emphasizing cumulative volume over time.

Dual-Axis Line Charts

When comparing two continuous metrics measured in completely different units or magnitudes (e.g., Total Monthly Revenue in Millions of USD versus Customer Churn Rate %), plotting them on a single Y-axis makes the percentage line appear flat at zero. Analysts must configure a Dual-Axis Line Chart, placing Revenue on the primary left Y-axis and Churn Rate % on the secondary right Y-axis.

Scatter Plots and Bubble Charts

Scatter plots display relationships between two continuous numeric variables (e.g., Marketing Spend vs. Conversion Count), revealing linear correlation, clustering, or outliers. Bubble Charts extend scatter plots by introducing a third continuous variable encoded as bubble diameter, and an optional fourth categorical variable encoded by color.

---

Summary Metrics & Tabular Displays

Counter (KPI) widgets highlight critical business metrics at a glance. They display a prominent primary scalar value (e.g., $12.4M), an optional secondary comparison metric (e.g., Target: $10.0M), and an automated variance indicator (+24% vs Q1). Data tables and pivot tables provide detailed multi-column breakdowns, supporting client-side column sorting, conditional formatting (highlighting cells in green/red based on value thresholds), and searching.

---

Visual Rendering Limits & Server-Side Aggregation

To ensure web browser stability and responsive rendering, Databricks AI/BI enforces a maximum visual rendering threshold of 10,000 data points for high-cardinality line charts and scatter plots.

If a SQL dataset returns 500,000 raw rows to a line chart widget, the client browser cannot efficiently render 500,000 SVG DOM elements. Databricks will either downsample the points or truncate the visualization, resulting in misleading trend lines. To prevent data truncation, data analysts must execute server-side SQL aggregation on the SQL Warehouse before sending data to the visualization layer:

• Group raw timestamp data using DATE_TRUNC('hour', event_timestamp) or DATE_TRUNC('day', event_timestamp).
• Apply numeric binning using ROUND() or WIDTH_BUCKET() for scatter plot variables.