[Image: Metabase and Panel logos/screenshots -- "Metabase and Panel are the two products we utilized to craft our visualizations for this project"]

Welcome back to our series where we are sharing a step by step walkthrough of our data stack development process. In the previous iteration of this series, we discussed how to transform raw data extracted from your sources into production level tables through the use of dbt. If you have not yet done so, we highly recommend reviewing Part 3 before diving in here. We'd also recommend taking a look at our companion piece on Information Visualization Theory where we discussed some of the best practices when it comes to displaying your data.

In this article, we will walk through the process of developing visualizations on top of our clean, production-level data using two tools: Metabase and Panel. We selected these tools because of their cost effectiveness and flexibility in our NHL data project, though the principles we cover here are broadly applicable regardless of which BI tool you choose.

Core Philosophy: Building with Intent

When building dashboards, the goal is to tell a clear, concise story while allowing the user to gain additional insights through interactivity. A user should be able to look at a dashboard and immediately understand the key message. From there, they can explore further through interactive elements like filters and drill-downs.

Selecting the right BI tool integrates seamlessly with data warehouses, enabling efficient use of production datasets. For this project, Metabase was selected due to its simple integration with Snowflake, robust visualization tools, and clean dashboard UI.

Dashboard Architecture Fundamentals

Most visualization tools contain two core elements:

  • Questions: Individual SQL queries rendered as tables or customizable visualizations (developed via the UI or custom SQL)
  • Dashboards: Organized collections of questions and filters

We recommend mapping out your dashboard structure before building. The NHL Dashboard example uses three tiers -- League, Team, and Player -- increasing in granularity as the user drills down.

Visualization Selection Process

Choosing the right visualization is a deliberate process. For example, when assessing league ranking evolution, the source table contains standings-by-day data with cumulative team statistics. After analyzing the data characteristics, multiple viable options emerged: line plots, index charts, and bump charts. We ultimately implemented both index and bump charts to provide different perspectives on relative team performance.

[Image: An index chart showing teams starting at equal values and progressing at relative rates over time]

[Image: A bump chart displaying each team's daily ranking throughout the period]

Here is a four-question framework for visualization selection:

  1. What source table(s) are needed?
  2. What data type and granularity is required?
  3. Is this best shown as summary or time series?
  4. Does the result provide clear, decision-informing takeaways?

Building in Metabase: Step 1 -- Source Table Assessment

Production-level tables should be ready for direct consumption without additional Metabase modeling. This approach optimizes compute and reduces switching costs if visualization tools change. The example uses a daily standings table at date granularity with all relevant team statistics pre-built (referenced to dbt development practices from Part 3).

[Image: A snapshot table showing daily standings data with columns for dates, teams, and performance metrics]

Building in Metabase: Step 2 -- Creating Questions

Metabase supports two approaches: the built-in UI or custom SQL. For complex visualizations like index charts, custom SQL provides greater control over filter actions and dashboard linking.

Query syntax requires "Schema"."Table"."Field" formatting for Metabase, though other SQL syntax follows the connected data warehouse conventions.

Filters use two methods:

  1. Direct value input: Adding filters to WHERE clauses as column = {{field_name}}
  2. Field filters: Embedded directly in clauses, connecting to existing Metabase fields with widget-type options (dropdowns, text inputs, etc.)

[Image: A field filter configuration example showing dropdown and selection options]

Building the Index Chart

After running the query, select "line" chart type from the visualization menu. Configuration involves:

  • Y-axis: running points totals
  • X-axis: date column
  • Additional trace: categorized by team

Metabase auto-assigns colors based on default palettes. Users can modify color opacity and line appearance per trace. For the NHL Dashboard, we assigned team-specific colors using hex values through the Admin Panel's Appearance section.

[Image: Visualization settings interface for the rendered line chart showing axes configuration, color assignments, and appearance controls]

Building the Bump Chart

The bump chart example demonstrates UI-based building. The query selected team name, date, and rank columns with hard filters for date ranges. Since Metabase lacks native bump chart support, a modified line chart was created.

Critical technique: Inverting the rank column (multiplying by -1) ensures top-ranked teams appear at the chart top rather than bottom. This requires creating a custom column.

Configuration steps included:

  • Assigning colors to individual traces
  • Editing each trace to show data-point dots
  • Configuring line styling
  • Modifying or hiding Y-axis labels

[Image: Final bump chart showing team rankings throughout the season as flowing lines with dots at data points, inverted rank values, and clean axis treatment]

Building in Metabase: Step 3 -- Organizing into Dashboards

1. Logical Grouping

Categorize questions into meaningful sections (League, Team, Player in the NHL example). Use tabs or filters (Season, Date, Conference) for navigation without overwhelming users.

2. Metric Prioritization

Place critical metrics at the dashboard top, immediately visible. In the Team Overview dashboard, metrics like Goals per Game, Penalty Kill Percentage, and League Rank appear as prominent tiles. Supporting data follows below as users scroll.

3. Thoughtful Filtering

Dashboard filters should align with questions being answered. Filters like Season, Date, and Conference provide flexibility while maintaining consistency. Connect filters across all relevant visualizations for seamless interaction between widgets.

4. Visual Hierarchy

Maintain clean layout with clear flow. Use headings to separate sections (Division Standings, Statistical Leaders). Ensure visual consistency with spacing, alignment, and font sizes. Reserve complex charts with granular details for lower sections where users explore after reviewing summaries.

Overtime Bonus: Python Visualization with Panel and Plotly

Some visualizations require capabilities beyond what Metabase offers. The NHL data stack required comparing team performance across multiple statistics simultaneously. Initial two-dimensional scatter plots lacked clear takeaways even with team-color encoding.

Solution: Display team logos on data points with user-selectable statistics, enabling quick understanding of the team performance landscape while identifying details of interest teams.

Metabase limitations (programmatic image display over data points, efficient interactive selection) led to using Python's Plotly and Panel libraries.

At its simplest, Panel is a one file application that needs only declare a single object -- a servable object, which itself is a combination of Widgets and Panes.

Technical Implementation

Data retrieval: Queries to the data warehouse package results into Pandas DataFrames for graph building. An API handles data extraction, validation, and formatting, loading data on startup with daily updates, avoiding frequent database calls.

Dashboard components:

  • Multivariate visualization
  • Dropdowns for statistic toggling
  • Summary data table

Development approach: Start with static versions to simplify development before adding dynamic features.

Dashboard Assembly

pn.template.MaterialTemplate(
    site="SSA",
    title="NHL Dynamic Stat Comparisons",
    main=[pn.Column(
        pn.Row(xaxis_widget, yaxis_widget, season_widget),
        pn.FlexBox(
            pn.Column(plot_pane, css_classes=["flex-item-1"]),
            stats_tables,
            flex_direction='row', flex_wrap='wrap', gap='10px')
    )]
).servable()

This demonstrates organizing components into rows, columns, and flex boxes with responsive sizing.

Interactivity with pn.bind()

main_plot = pn.bind(
    main_scatter,
    x_stat=xaxis_widget,
    y_stat=yaxis_widget,
    season=season_widget
)
plot_pane = pn.pane.Plotly(main_plot, sizing_mode='stretch_both')

The pn.bind() function links widgets to panes by assigning them as inputs to functions. The function takes widget values as parameters and returns pane-compatible objects (like Plotly figures). This setup acts as the callback mechanism for interactivity, automatically updating plots when users interact with widgets.

Plotly customization: Using plotly_express.scatter() builds the base plot. The add_layout_image() method places team logos on data points. Reference lines for league averages enable easy performance comparison to benchmarks.

[Image: A scatter plot with team logos positioned at their corresponding data points, showing two selected statistics with league average reference lines and color-coded regions]

Data and Performance Optimization

DataFrame panes wrap Pandas DataFrames directly from API pulls, and Plotly panes integrate seamlessly with Plotly figures. Interactive widgets (such as Select widgets available through Panel) provide interaction flexibility. The dashboard uses clear variable naming and row-column organization for a servable Panel object.

Hosting and server deployment conclude the implementation, covered in the series' final part.

Conclusion

Dashboard building transcends data display -- it creates interactive tools that tell stories and inform decisions. Leveraging Metabase and Panel enables visually appealing, functional, and adaptable visualizations. Success requires thoughtful planning: structuring source data, designing intuitive layouts, layering interactivity, and customizing for use cases. The NHL dashboard example demonstrates transforming raw data into actionable insights through intentional design and appropriate technology choices.

Stay tuned for the final part of this series covering dashboard hosting and broader application integration.

Post Navigation: