Which Chart Type is Best - Selection Guide

Selecting the appropriate chart type is crucial for effective data
communication and AI-driven insights on Spartera. The right
visualization enhances comprehension, reveals patterns, and supports
decision-making, while the wrong choice can mislead or confuse your
audience.

Spartera Supported Chart Types

Spartera supports the following visualization types for your analytics
assets:

Line Chart - For trend analysis over time
Bar Chart - For categorical comparisons
Pie Chart - For part-to-whole relationships
Radar Chart - For multi-dimensional comparisons
Polar Chart - For cyclical or directional data
Doughnut Chart - For hierarchical part-to-whole relationships
Scatterplot - For correlation and relationship analysis
Histogram - For the distribution of a single continuous variable
Box / Violin Plot - For distribution shape and outliers across groups

Chart Selection Framework

1. Purpose-Driven Selection

Before choosing a chart type, clearly define your analytical purpose:

Purpose	Primary Question	Recommended Chart Types
Comparison	How do values compare across categories?	Bar Chart, Radar Chart
Trend Analysis	How do values change over time?	Line Chart
Distribution	How are values distributed?	Histogram, Box Plot, Violin Plot
Correlation	How do two variables relate?	Scatterplot
Composition	What are the parts of the whole?	Pie Chart, Doughnut Chart
Multi-dimensional	How do multiple metrics compare?	Radar Chart
Cyclical	How do values vary in cycles?	Polar Chart

2. Data Type Considerations

Categorical Data

-- Example: Product category sales analysis
SELECT
    product_category,
    SUM(sales_amount) as total_sales,
    COUNT(DISTINCT order_id) as order_count
FROM order_analytics
GROUP BY product_category
ORDER BY total_sales DESC;
-- Best visualized with: Bar Chart, Pie Chart (if &lt;7 categories)

Time Series Data

-- Example: Monthly revenue trends
SELECT
    DATE_TRUNC('month', order_date) as month,
    SUM(order_total) as monthly_revenue,
    COUNT(DISTINCT customer_id) as unique_customers
FROM order_analytics
WHERE order_date >= CURRENT_DATE - INTERVAL '12' MONTH
GROUP BY month
ORDER BY month;
-- Best visualized with: Line Chart

Continuous Numerical Data

-- Example: Customer lifetime value distribution
SELECT
    customer_id,
    customer_lifetime_value,
    customer_acquisition_cost,
    days_since_first_purchase
FROM customer_analytics
WHERE customer_lifetime_value > 0;
-- Best visualized with: Scatterplot

Detailed Chart Type Guide

Line Charts - Best for Time Series Analysis

When to use ?

Showing trends over time
Multiple series comparison
Continuous data points
Emphasizing rate of change

Spartera Configuration (legacy — see deprecation note below):

Data structure:

-- Multi-series trend data
SELECT
    DATE_TRUNC('week', activity_date) as week,
    customer_segment,
    AVG(daily_active_users) as avg_weekly_active_users,
    AVG(session_duration_minutes) as avg_session_duration
FROM user_activity_metrics
WHERE activity_date >= CURRENT_DATE - INTERVAL '90' DAY
GROUP BY week, customer_segment
ORDER BY week, customer_segment;

Best practices:

Limit to 5-7 lines for readability
Use clear date formatting on x-axis
Start y-axis at zero when comparing magnitudes
Include data point markers for sparse data

Bar Charts - Best for Categorical Comparisons

When to use ?

Comparing values across different categories
When category names are descriptive
When you have negative values
When precise value reading is important

Spartera Configuration (legacy — see deprecation note below):

Data structure:

-- Regional sales comparison
SELECT
    sales_region,
    SUM(annual_sales) as total_annual_sales,
    AVG(target_achievement_percentage) as avg_target_achievement
FROM regional_sales_summary
GROUP BY sales_region
ORDER BY total_annual_sales DESC;

Avoid when:

You have more than 10 categories (consider grouping)
Time series data (use line charts instead)
Part-to-whole relationships (use pie charts)

Counting rows per category (and the `SUM(X)` trap)

A very common need is "how many rows per category" — injuries per year, orders per region. The right way is to pre-aggregate in SQL and bind both axes:

-- Count per category, computed in SQL
SELECT year, COUNT(*) AS injury_count
FROM nfl_injuries
GROUP BY year
ORDER BY year;

Then map X = year, Y = injury_count. You get ten rows of payload, exact counts, and clean categorical spacing.

Pie Charts - Best for Simple Part-to-Whole Relationships

When to use ?

2-7 categories maximum
Clear part-to-whole relationships
Percentages are meaningful
Simple composition analysis

Spartera Configuration (legacy — see deprecation note below):

Data structure:

-- Revenue composition by product line
SELECT
    product_line,
    SUM(revenue) as total_revenue,
    SUM(revenue) * 100.0 / (
        SELECT SUM(revenue) FROM product_sales
    ) as percentage
FROM product_sales
GROUP BY product_line
HAVING SUM(revenue) * 100.0 / (
    SELECT SUM(revenue) FROM product_sales
) >= 5 -- Only show segments >5%
ORDER BY total_revenue DESC;

Avoid when:

More than 7 categories
Values are similar in size
Comparing across multiple time periods

Doughnut Charts - Best for Hierarchical Compositions

When to use?

Nested part-to-whole relationships
Central metric display needed
Multi-level categorization
Space-efficient composition display

Spartera Configuration (legacy — see deprecation note below):

Data structure:

-- Sales by category with subcategories
SELECT
    CONCAT(main_category, ' - ', sub_category) as category_hierarchy,
    SUM(sales_amount) as category_sales
FROM product_hierarchy_sales
GROUP BY main_category, sub_category
ORDER BY main_category, category_sales DESC;

Scatterplot - Best for Relationship Analysis

When to use ?

Exploring relationships between two continuous variables
Identifying correlations and patterns
Outlier detection
Distribution analysis

Spartera Configuration (legacy — see deprecation note below):

Data structure

-- Customer acquisition cost vs lifetime value
SELECT
    customer_acquisition_cost,
    customer_lifetime_value,
    customer_segment,
    days_since_acquisition
FROM customer_analytics
WHERE customer_acquisition_cost > 0
  AND customer_lifetime_value > 0
  AND customer_acquisition_cost < 1000; -- Remove outliers

Enhancement techniques

Use customer_segment for color coding
Filter outliers for clearer patterns
Include meaningful axis labels
Limit data points for performance (use sampling if needed)

Histograms - Best for Distributions of a Single Variable

When to use ?

Showing how a single continuous variable is distributed (response times, order values, ages)
Spotting skew, modality, or outliers in one column
Binning a continuous range into buckets

Histograms count rows per bin automatically — you map a single column to X and Plotly computes the bin counts for the value axis. You do not supply a Y column (the value axis defaults to "Count").

Data structure:

-- Distribution of order values (one row per order — no GROUP BY)
SELECT order_value_usd
FROM orders
WHERE order_value_usd > 0;

Best practices:

Map the continuous column to X and leave Y empty — Plotly counts rows per bin.
Tune bin width with xbins.size in the spec if the auto-bins are too coarse or too fine.
Do not use a histogram for a low-cardinality discrete field (year, month number, category code).

Radar Charts - Best for Multi-Dimensional Comparisons

When to use ?

Comparing multiple metrics simultaneously
Performance scorecards
Multi-criteria analysis
Profile comparisons

Spartera Configuration (legacy — see deprecation note below):

Data structure:

-- Customer segment performance comparison
SELECT
    customer_segment,
    'Average Order Value' as metric_name,
    AVG(order_value) as metric_value
FROM customer_metrics
GROUP BY customer_segment

UNION ALL

SELECT
    customer_segment,
    'Purchase Frequency' as metric_name,
    AVG(monthly_purchase_count) as metric_value
FROM customer_metrics
GROUP BY customer_segment

UNION ALL

SELECT
    customer_segment,
    'Customer Satisfaction' as metric_name,
    AVG(satisfaction_score) as metric_value
FROM customer_metrics
GROUP BY customer_segment;

Polar Charts - Best for Cyclical Data

When to use ?

Time-of-day patterns
Seasonal analysis
Directional data
Cyclical trends

Spartera Configuration (legacy — see deprecation note below):

Data structure:

-- Daily activity patterns
SELECT
    EXTRACT(HOUR FROM activity_timestamp) as hour_of_day,
    COUNT(DISTINCT session_id) as activity_count,
    AVG(session_duration_minutes) as avg_session_duration
FROM user_activity_log
WHERE activity_timestamp >= CURRENT_DATE - INTERVAL '30' DAY
GROUP BY hour_of_day
ORDER BY hour_of_day;

Chart Display Options and Customization

Spartera provides comprehensive display options to customize the
appearance and behavior of your visualizations. These settings are
available in the Display Options tab when configuring your chart.

Data Transformation Settings

Data Aggregation

Controls how values are combined for each category in your
visualization.

Available Options:

None - Display raw data without aggregation (for scatterplots)
Sum - Add up all values for each category
Average - Calculate mean value for each category
Count - Count number of records for each category
Min - Show minimum value for each category
Max - Show maximum value for each category

Example Usage:

Best Practices:

Use Sum for total sales, revenue, or quantity metrics
Use Average for performance metrics, ratings, or normalized values
Use Count for frequency analysis or occurrence tracking
Use None for scatterplots and individual data point analysis

Sort Direction

Controls the ordering of data in your visualization.

Available Options:

No Sorting - Display data in database order
Ascending - Sort from lowest to highest values
Descending - Sort from highest to lowest values

Example Usage:

Best Practices:

Use Descending for ranking top performers (e.g., top products)
Use Ascending for chronological time series data
Use No Sorting when category order has inherent meaning

Category Limit

Limits the number of categories displayed in your chart.

Configuration:

Enter a numeric value (e.g., 10, 20, 50)
Represents maximum bars, slices, or data series to display
Useful for focusing on top performers or preventing chart clutter

Example Usage:

Best Practices:

Use 5-10 for pie and doughnut charts
Use 10-20 for bar charts to maintain readability
Use 3-7 for line charts with multiple series
Consider using "Other" category for grouped remaining values

Filter Direction

Controls which categories are displayed when a category limit is
applied.

Available Options:

Top N - Show categories with highest values
Bottom N - Show categories with lowest values

Example Usage:

Use Cases:

Top N: Best sellers, highest revenue, top performers
Bottom N: Underperformers, improvement opportunities, tail analysis

Chart Appearance Settings

Color Scheme

Defines the color palette applied to chart elements.

Available Options:

Monochrome - Single color with varying shades
Default - Standard business-friendly color palette
Sequential - Gradient color scheme for progressive data
Diverging - Two-tone scheme for positive/negative values
Categorical - Distinct colors for different categories
Pastel - Soft, muted colors for gentle visual presentation
Dark - Bold, deep colors for high contrast displays
Light - Bright, airy colors for subtle visualizations

Example Usage:

Best Practices:

Use Monochrome for professional, minimalist presentations
Use Default for general business reporting
Use Categorical for distinct categories in bar/pie charts
Use Sequential for heat maps or graduated data
Use Diverging for profit/loss or above/below average analysis
Use Pastel for customer-facing reports or soft presentations
Use Dark for dashboards on dark backgrounds or high-contrast needs
Use Light for minimalist designs or when printing on white paper

Show Legend

Controls whether the chart legend is displayed.

Options:

Enabled - Display legend with data series labels
Disabled - Hide legend to maximize chart space

Example Usage:

Best Practices:

Enable for multi-series charts (multiple lines, grouped bars)
Enable for pie/doughnut charts to identify segments
Disable for single-series charts with clear labels
Disable when space is limited or categories are self-evident

Show Grid Lines

Controls whether reference grid lines are displayed on chart axes.

Options:

Enabled - Display horizontal/vertical grid lines
Disabled - Hide grid lines for cleaner appearance

Example Usage:

Best Practices:

Enable for precise value reading and comparison
Enable for line charts showing detailed trends
Disable for cleaner presentation or when exact values aren't
critical
Disable for pie/doughnut charts (not applicable)

Bar Chart Specific Options

Stacked Bars

Controls whether bars are stacked vertically to show cumulative totals.

Options:

Enabled - Stack bars to show composition and total
Disabled - Display bars side-by-side for direct comparison

Example Usage:

When to Use Stacked Bars:

Showing part-to-whole relationships over categories
Comparing total values while seeing component breakdown
Displaying cumulative metrics (e.g., total sales by product line per
region)

When to Avoid Stacked Bars:

Comparing individual component values precisely
When you have many stack segments (>5)
When precise value reading is critical

Example Query for Stacked Bar Chart:

-- Revenue by region and product category (stacked)
SELECT
    sales_region,
    product_category,
    SUM(revenue_amount) as total_revenue
FROM sales_data
WHERE fiscal_year = 2024
GROUP BY sales_region, product_category
ORDER BY sales_region, total_revenue DESC;

Complete Configuration Example

High-Performance Product Analysis

Corresponding SQL Query:

SELECT
    product_name,
    SUM(revenue_amount) as total_revenue,
    COUNT(DISTINCT order_id) as order_count
FROM product_sales
WHERE sale_date >= CURRENT_DATE - INTERVAL '90' DAY
GROUP BY product_name
ORDER BY total_revenue DESC
LIMIT 15;

Multi-Series Time Trend Analysis

Corresponding SQL Query:

SELECT
    DATE_TRUNC('month', order_date) as month,
    customer_segment,
    AVG(order_value) as monthly_revenue
FROM orders
WHERE order_date >= CURRENT_DATE - INTERVAL '12' MONTH
GROUP BY month, customer_segment
ORDER BY month ASC;

Regional Sales Composition (Stacked)

Corresponding SQL Query:

SELECT
    sales_region as region,
    product_category,
    SUM(revenue_amount) as revenue
FROM regional_product_sales
WHERE fiscal_quarter = 'Q4 2024'
GROUP BY sales_region, product_category
ORDER BY sales_region, revenue DESC;

Display Options Best Practices Summary

For Professional Reports

Use Monochrome or Default color schemes
Enable Grid Lines for precise value reading
Enable Legend for multi-series charts
Use Top N filtering to focus on key insights

For Executive Dashboards

Use Categorical colors for visual distinction
Disable Grid Lines for cleaner appearance
Limit categories to 5-10 for quick scanning
Use Stacked Bars to show totals and composition

For Analytical Deep Dives

Enable Grid Lines for detailed analysis
Use No Category Limit or high limits (50+)
Enable Legend for all multi-series charts
Use Sequential colors for gradient data

Performance Optimization

Limit categories to <100 for optimal rendering
Use appropriate aggregation to reduce data volume
Apply Top N filtering for large datasets
Consider No Sorting if database order is pre-optimized

Chart Selection Decision Tree

1. What is your primary analysis goal?
   ├─ Show trends over time → Line Chart
   ├─ Compare categories → Bar Chart
   ├─ Count rows per category → pre-aggregated Bar Chart (GROUP BY ... COUNT(*))
   ├─ Distribution of one continuous variable → Histogram (Box / Violin across groups)
   ├─ Show composition → Go to 2a
   ├─ Analyze relationships → Scatterplot
   ├─ Multi-dimensional comparison → Radar Chart
   └─ Cyclical patterns → Polar Chart

2a. Composition Analysis:
    ├─ Simple parts (&lt;7 categories) → Pie Chart
    ├─ Hierarchical parts → Doughnut Chart
    └─ Many categories (>7) → Bar Chart

Spartera-Specific Best Practices

1. Data Preparation for Visualizations

Prepare clean, aggregated data

-- Good: Clean data ready for visualization
CREATE VIEW chart_ready_sales_data AS
SELECT
    sales_quarter_name,                    -- Q1 2024, Q2 2024, etc.
    product_category_description,          -- Electronics, Clothing, etc.
    ROUND(total_revenue_usd, 2) as revenue,
    ROUND(revenue_growth_percentage_qoq, 1) as growth_rate,
    sales_rep_count
FROM quarterly_sales_analysis
WHERE sales_year >= 2023
  AND total_revenue_usd > 0
ORDER BY sales_quarter_name;

-- Avoid: Raw, unfiltered data with nulls and outliers
SELECT * FROM raw_transaction_log; -- Don't use this directly

2. Optimal Record Counts

Visualization rendering scales with the number of rows fetched, so size the
result set to what each chart shape actually needs:

Chart shape	Reasonable row count
Composition charts (pie, donut)	5–50
Detailed analysis (small multiples, ranked bars)	up to 100
Trend lines	up to 500
Scatter plots	up to 1,000

The platform caps visualization queries at 10,000 rows by default. Set
viz_data_limit on the asset to cap lower (1–10,000) — useful when you
know a smaller sample is sufficient and want faster renders. You can also
control record counts via:

A LIMIT (and supporting ORDER BY) on the source SQL view feeding the connection
Pre-aggregating the source view to the granularity the chart actually needs
Pushable filter transforms in the spec (=, !=, <, <=, >, >=, {}, }{) — these translate to a SQL WHERE clause; see Visualization Spec Reference

3. Aggregation Strategies

Choose the right aggregation for your chart type:

-- Bar Charts: Use Sum or Count for totals
SELECT
    product_category,
    SUM(sales_amount) as total_sales    -- Sum aggregation
FROM sales_data
GROUP BY product_category;

-- Line Charts: Use Average for trends
SELECT
    DATE_TRUNC('month', order_date) as month,
    AVG(order_value) as avg_monthly_order_value  -- Average aggregation
FROM orders
GROUP BY month;

-- Scatterplots: Use individual records (no aggregation)
SELECT
    customer_acquisition_cost,
    customer_lifetime_value
FROM customer_metrics
WHERE customer_acquisition_cost IS NOT NULL
  AND customer_lifetime_value IS NOT NULL;

4. Color Scheme Guidelines

Default: Use for most business charts
Categorical: Use for distinct categories in bar/pie charts
Sequential: Use for line charts showing progression
Diverging: Use for data with positive/negative values

Performance Optimization

Efficient Queries for Charts

-- Optimize with proper indexing and filtering
CREATE INDEX idx_sales_date_category
    ON sales_data(sale_date, product_category);

-- Pre-aggregate data for better performance
CREATE MATERIALIZED VIEW daily_sales_chart_data AS
SELECT
    sales_date,
    product_category,
    SUM(sales_amount) as daily_sales,
    COUNT(DISTINCT customer_id) as unique_customers
FROM sales_data
WHERE sales_date >= CURRENT_DATE - INTERVAL '90' DAY
GROUP BY sales_date, product_category;

-- Use the materialized view in Spartera
SELECT * FROM daily_sales_chart_data;

Data Size Management

-- Use sampling for large datasets in scatterplots
SELECT
    customer_acquisition_cost,
    customer_lifetime_value,
    customer_segment
FROM customer_analytics
WHERE RAND() < 0.1  -- 10% sample
  AND customer_lifetime_value > 0
ORDER BY RAND()
LIMIT 1000;

Common Chart Selection Mistakes

1. Wrong Chart for Data Type

-- WRONG: Using pie chart for time series data
SELECT
    EXTRACT(MONTH FROM order_date) as month,
    SUM(revenue) as monthly_revenue
FROM orders;
-- This should be a line chart, not pie chart!

-- CORRECT: Using line chart for time trends
SELECT
    DATE_TRUNC('month', order_date) as month,
    SUM(revenue) as monthly_revenue
FROM orders
GROUP BY month
ORDER BY month;
-- Configure as LINE chart in Spartera

2. Too Many Categories

-- WRONG: Pie chart with 15+ categories
SELECT
    individual_product_sku,  -- 50+ different SKUs
    SUM(sales_amount)
FROM product_sales
GROUP BY individual_product_sku;

-- CORRECT: Group smaller categories
SELECT
    CASE
        WHEN product_sales_rank <= 5 THEN product_category
        ELSE 'Other'
    END as grouped_category,
    SUM(sales_amount) as total_sales
FROM (
    SELECT
        product_category,
        SUM(sales_amount) as category_sales,
        ROW_NUMBER() OVER (ORDER BY SUM(sales_amount) DESC)
            as product_sales_rank
    FROM product_sales
    GROUP BY product_category
) ranked_categories
GROUP BY grouped_category
ORDER BY total_sales DESC;

3. Unclear Data Labels

-- WRONG: Cryptic column names
SELECT
    cat_id,    -- Unclear
    amt,       -- No units
    pct        -- Percentage of what?
FROM sales_summary;

-- CORRECT: Descriptive names for AI understanding
SELECT
    product_category_name,
    total_sales_amount_usd,
    market_share_percentage
FROM sales_summary;

4. Bar Chart with X but no Y (implicit `SUM(X)`)

-- WRONG: only selecting the category, expecting a Bar chart to "count" it
SELECT year FROM nfl_injuries WHERE injury = 'Shoulder';
-- A Bar trace with X=year and no Y renders SUM(year) grouped by year,
-- NOT the row count. Bars come out ~2000x too tall and the error scales
-- with data volume, so it looks fine on a small editor sample.

-- CORRECT: pre-aggregate, then map X=year, Y=injury_count
SELECT year, COUNT(*) AS injury_count
FROM nfl_injuries
WHERE injury = 'Shoulder'
GROUP BY year
ORDER BY year;

For a continuous variable, a Histogram is the alternative — it counts rows per bin without a Y column. For a discrete field like year, prefer the pre-aggregated Bar above.

Validation and Testing

Chart Data Quality Checks

Validate the columns referenced by your viz_spec *src keys before
publishing. Replace category_column and value_column with the actual
column names you reference in the spec.

-- Validate chart data before visualization
CREATE PROCEDURE validate_chart_data(table_name VARCHAR(100))
BEGIN
    -- Check for null values in key columns
    SELECT
        'null_check' as validation_type,
        COUNT(*) as total_rows,
        COUNT(*) - COUNT(value_column)    as null_value_column,
        COUNT(*) - COUNT(category_column) as null_category_column
    FROM chart_data_table;

    -- Check for reasonable data ranges
    SELECT
        'range_check' as validation_type,
        MIN(value_column) as min_value,
        MAX(value_column) as max_value,
        AVG(value_column) as avg_value,
        COUNT(CASE WHEN value_column < 0 THEN 1 END)
            as negative_values
    FROM chart_data_table;
END;

By following this comprehensive chart selection guide, you'll ensure
that your Spartera visualizations deliver clear, accurate, and
impactful insights that effectively communicate patterns in your data.
Remember that the best chart type depends on your data structure,
analytical purpose, and audience needs - always prioritize clarity and
accuracy over visual complexity.