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 quality datasets using dbt. If you have not read this or have not already configured your production database, we highly recommend reviewing Part 3 of this series before beginning to create any visualizations or analytical dashboards. Finally, before you dive in, we would recommend reading our latest post on Information Visualization Theory — which walks through some general best practices and things to keep in mind when displaying data.
In this part of the series, we arrive at the component all your end-users have been waiting for … the dashboards! Here, we will focus on how best to visualize your data using Metabase and Panel specifically. There are quite a few great data visualization softwares on the market and these concepts do apply more broadly, but we love recommending Metabase and Panel for their cost effectiveness and flexibility. With that, let’s get started!
A Quick Reminder: Always Build with the End Goal in Mind
Before developing a visualization of any kind, it’s essential to understand your data — which is why we’ve spent so much time in this series discussing how to build a proper foundation. Now that it’s time to put pen to paper, we find that it’s helpful to remind yourself of this maxim: “when building dashboards, the goal is to tell a clear, concise story while allowing the user to gain additional insights through interactivity”. Put another way, end users should grasp the key message immediately, with the option to explore further as they engage with the visual.
As discussed in Part 1, selecting the right dashboard or BI tool is a critical component of achieving this goal. Your chosen software should integrate seamlessly with your data warehouse — allowing you to build on production datasets effectively. For this series, we chose Metabase for its simple integration with Snowflake, robust visualization tools, and clean dashboard UI.
Getting Started with Your Dashboard
Most visualization tools have two core elements, often referred to by different names. Typically, these are individual views and the Dashboards into which they are embedded.
In Metabase, these are defined as follows:
- Questions: Individual SQL queries, which are rendered as a table or a customizable visualization. They can be developed via Metabase’s user interface or directly using custom SQL
- Dashboards: An organized collection of questions and filters
While it’s tempting to hop into your newly connected visualization instance and start building, our advice is to map out how you want the final Dashboard to be structured before getting started with any work. When it came to our NHL Dashboard, we knew the end goal was to provide a robust and unbiased perspective across three buckets of increasing granularity: League, Team, and Player. To achieve this, we put together a roadmap — outlining the key analyses and metrics that would allow us to tell an interesting and cohesive story. More often than not, once you consider the characteristics of the data alongside the story you ultimately want to tell, the appropriate choice of visualization will reveal itself.
For example, one of our initial goals in the league bucket was to assess how league rankings evolved during the course of the regular season. Before building the visualization, we first considered the source data table — standings by day. This table contains an entry for each day and team combination within the season — with cumulative team stat fields as features. Taking this into account, it was clear that the best choice would be a graph displaying trends for multiple categories (aka teams) over a period of time. With this in mind, we only had a few viable options: line plots, index charts, and bump charts. Ultimately we decided to build both an index and a bump chart — as each provides a slightly different visual perspective and context on team performance relative to the peer set over time.
The above represents a specific example related to our project, but the thought process is broadly applicable. For each of the questions your end users will ultimately want to answer, ask yourself:
- What is the source table(s) I will need to rely on to build this?
- What is the type and granularity of the data needed?
- Is this question best addressed as a summary view, or in a time series?
- Does the end result provide a clear takeaway that can inform decision making?
If you can hit on all four, chances are your visualization will be a great value add to the final dashboard!
How to Build in Metabase: Step 1 — Assess your Source Table
Now that we’ve hit on a few general principles around building visualizations, let’s dive into some specifics on how to best utilize Metabase specifically. For this example, we’ll walk through how we built two visualizations referenced above — highlighting some key tips and tricks along the way!
While Metabase does offer a useful modeling tool, generally our preference is to have our production level tables be ready for direct consumption and visualization. This helps from a compute optimization perspective, and also means less work on our end if we ever need to switch visualization tools. In this case, the table we are relying on is the aforementioned daily standings table. Before building in Metabase, we made sure this table was at the date granularity required, and that it contained all the relevant team stats we would want to weave into our analyses. For more information on how we build these production tables, feel free to revisit our post on dbt development.
How to Build in Metabase: Step 2 — Building Your Questions
Generally in Metabase, there are two ways to build a question. You can leverage Metabase’s built in UI (which is more than sufficient for most use cases), or you can rely on custom SQL. As an index chart is a bit more complex, we opted to use an SQL query to extract the key columns (day, team, points) and have direct control on the filter actions for linking to the dashboard. In alignment with our goal to not distort the data and provide a clear picture, the query validates the rows of the standings table based on the each season’s start and end dates in the join clause and the select case statements. This ensures that all entries are in the correct time frame, which enables the filters on season ID, date ranges, and conferences to work smoothly.
Note the syntax for accessing a table or field: “Schema”.”Table”.”Field”
This applies to all Metabase queries, yet most other SQL syntax is dictated by your data warehouse
Note that in Metabase SQL Questions, filters are added using the {{filter_name}} syntax, and you have two main ways to use them. One approach is treating the variable as a direct value input, where you add it to your WHERE clause like column = {{field_name}} and specify the input type in the side tab. Our query, however, uses a different method: a field filter. Field filters are embedded directly in the clause and then configured in the side tab. Unlike direct value inputs, field filters connect to an existing field in one of your Metabase questions. This connection lets you select a filter widget type—dropdown, text input, etc.—based on the data type of the connected field. These filters seamlessly integrate with your dashboard, functioning just like column filters in standard Metabase questions, as shown below.
After running our query and entering the Visualization Editor, we set the chart type to line, found in the bottom left visualization menu, to be the basis for our chart. In that settings sections seen below, we assigned running points totals the Y axis and set the X axis to the date column with an additional trace to categorize by Team. Once a categorical trace such as team is selected, Metabase auto assigns colors based on the default color palette. By clicking on the color icon by each trace you can modify its color opacity and line appearance of each trace. To further tailor our visual’s appearance for our use case, we assigned a 24 color palette by entering hex values for each team’s color using the rgb pickers in the Appearance section of the Admin Panel.
To provide an example of how to build a question directly in the UI rather than via custom SQL, we’ll next walk through how we set up our bump chart. Since our dataset already included the conference rankings, making the bump chart in Metabase was straightforward. In the Query editor, we selected the team name, date, and rank columns and set a hard filter to adjust start dates, as seen below.
Since Metabase does not have a built in Bump Chart type, we have to build a modified line chart. The key to implementing this properly is to ensure your rank column is inverted, meaning it starts at -1 and decreases, instead of starting from 1. Doing this ensures that the top ranked line runs at the top and not the bottom of the visualization and can be done quickly by creating a custom column that multiplies an existing rank column by -1. With this in place for our line chart, we had to assign a color to and edit each trace individually to show the dots at each data point and to configure the style to fit the final visual. Since the chart now displays the negative ranks, you can modify or hide the y axis labels entirely, which all put together yields a clean, working bump chart like the example below.
How to Build in Metabase: Step 3 — Organizing your Questions into a Dashboard
Once you’ve built your individual questions, the next step is to organize them into a cohesive dashboard. A well-structured dashboard is essential for making data easy to interpret, engaging, and actionable. Here are some key best practices to follow when organizing your dashboard:
- Group Questions Logically: Start by categorizing your questions into meaningful sections. For example, in our NHL dashboard, we divided the content into League Statistics, Team Statistics, and Player Statistics. This structure mirrors the natural granularity of our data and makes navigation intuitive for users. Use tabs or filters (like those for Season, Date, and Conference in our example) to allow users to toggle between related views without overwhelming them with too much information at once.
- Prioritize Key Metrics: Place the most critical metrics at the top of the dashboard, where they are immediately visible. For example, in our Team Overview dashboard (see screenshot), metrics like Goals per Game, Penalty Kill Percentage, and League Rank are displayed prominently in tiles, making them the focal point. Supporting data, such as detailed player statistics or goalie save percentages, can follow below as users scroll for deeper insights.
- Use Filters Thoughtfully: Dashboard filters should align with the questions you’re answering. In our case, filters like Season, Date, and Conference provide users with flexibility while keeping the views consistent. Connect filters across all relevant visualizations to ensure seamless interaction between widgets, as seen in the Division Standings and Team Overview sections.
- Design for Visual Hierarchy: Maintain a clean layout with a clear flow. Use headings to separate sections (e.g., Division Standings and Statistical Leaders) and ensure visual consistency with spacing, alignment, and font sizes. Reserve charts with the most complex or granular details, like Skater Efficiency Graphs, for lower sections, where users can explore deeper insights after reviewing summary metrics.
Overtime Bonus: Creating Visualizations with Python
As we have discussed, building concise, coherent multivariate statistical graphs helps tell a unique and intricate story of the data that is not apparent without an efficient visualization. In the case of our NHL data stack, we had envisioned a method or tool to be able to neatly and quickly compare the relative performance of NHL teams across the wide array of key team statistics that encapsulates a team performance — from simple team stats to imputed aggregate metrics. In doing so, our rough draft of a two dimensional scatter plot comparing two user selected statistics was a good starting point, yet lacked a clear takeaway from viewing the visual. Even when encoding team colors on the the data points, as done with our index and bump charts, the visual still lacked in conveying a quick and coherent summary of the two statistical metrics.
In the end, we settled on the following solution — to display the team logos on each of their data points and have the user navigate between different statistics. This would quickly provide the landscape of team performance across the two selected statistics and enable the viewer to pin point details for teams of interest while maintaining a clear understanding of that team’s relevant performance. In trying to accomplish this, we were limited by Metabase’s capability to programmatically display images over data points and to efficiently integrate the interactive touchpoint to select data views. Thus we opted to use Python’s Plotly and Panel libraries to develop our own visualization and host it in a dedicated dashboard.
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.
Plotly is perfect for our needs because it offers complete control over figure customization. This allowed us to layer team logos on data points and create custom tooltips based on selected metrics. Using the plotly_express.scatter() function, we built the basic scatter plot, and with add_layout_image(), we placed team logos on their corresponding data points. To enhance clarity, we added reference lines for league averages, making it easy to compare each team’s performance to the overall benchmarks:
To retrieve the data, we query our Data Warehouse and package the results into Pandas DataFrames, which we then use to build our graph objects. To streamline the process, we created an API that handles data extraction, validation, and formatting. This API loads data for our dashboard on startup and updates it daily, avoiding frequent database calls and improving performance.
Our dashboard app is built on three main components: a multivariate visualization, dropdowns to toggle between statistics, and a summary data table. For complex interactivity, we start with a static version to simplify development before adding dynamic features. The Panel framework makes this process straightforward — DataFrame panes wrap Pandas DataFrames directly (like those pulled from the API), and Plotly panes integrate seamlessly with Plotly figures. Dropdowns and other interactive widgets, such as those available through Panel, provide the flexibility needed for user interaction.
To assemble the dashboard itself, we then reference each component as a variable with clear names and organize them into rows and columns, creating a servable Panel object (example shown below).
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()
Next, to layer in interactivity that updates a widget or pane, we use the pn.bind() function. This function links a specific widget (or multiple widgets) to a pane by assigning them as inputs to a function. The function itself takes these widget values as parameters and returns an object compatible with the pane type—such as a Plotly figure for a Plotly pane.
For instance, we created widgets for the x and y axes, which are passed as parameters to the pn.bind() function. These widgets dynamically control the transformations and visualizations in the bound function. In our example, the scatter plot pane is bound to both axis widgets, allowing it to generate a customized figure based on the user’s selected statistics. This setup acts as the callback for interactivity, ensuring the plot updates automatically when the user interacts with the widgets.
# main scatter plot widget
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')
Once you have thoroughly developed and tested your dashboard running on your local instance, you can finally host your dashboard on a remote server and embed it to your other applications, which we will cover in the final part of our series!
Conclusion
Building dashboards is more than just displaying data — it’s about creating a powerful, interactive tool that tells a story, informs decisions, and empowers users. By leveraging tools like Metabase and Panel, you can craft visualizations that are not only visually appealing but also deeply functional and adaptable to various use cases. From structuring your source data and designing intuitive layouts, to layering in interactivity and customization – each step contributes to a seamless user experience. As we’ve shown through our NHL dashboard example, thoughtful planning and the right technologies can transform raw data into actionable insights. Stay tuned for the final part of our series, where we’ll cover how to host and integrate your dashboards for broader accessibility!