Week 11 - Dashboarding

Introduction to Dashboarding

Metabase Setup

Dashboards in Metabase

Streamlit Fundamentals

Building a Metrics Dashboard

Presenting Metrics

Practice

Gotchas & Pitfalls

Assignment: Build Two Dashboards

Slides (PDF)

Career relevance: Week 11

Glossary: Week 11

Going Further

History of Dashboarding

Streamlit Fundamentals

In Analytical Dashboards with Metabase you built a stakeholder-facing BI dashboard without writing application code. This chapter introduces a different approach: writing a Python app using Streamlit that you have full control over.

The Streamlit app you will build in Building a Metrics Dashboard reads a single data source: the fct_trips mart you built with dbt in Week 10, queried straight from Azure Postgres. This chapter covers the Streamlit primitives you need before wiring that up.

By the end of this chapter, you should be able to:

What Streamlit is

Streamlit is a Python library for building data applications. You write a regular Python script; Streamlit re-runs it top-to-bottom every time a user interacts with a widget, and renders the output as a web page. No HTML, no JavaScript, no Flask routing.

Here is a conceptual example of a simple app:

import streamlit as st

st.title("My first app")
st.write("Hello, World!")

Running a Streamlit script is as simple as launching it from your terminal (you will set up your environment and run this command in the next section):

streamlit run app.py

Streamlit opens a browser tab at http://localhost:8501 and renders your app.

<aside> 📘 Core program connection: In the Core program you queried databases with SQL and rendered the results in a web page. The metrics dashboard you build later is the same loop in Python: a SQL query against fct_trips returns rows, and Streamlit turns them into metric cards and charts. The query-then-display loop is the same; the language changed from JavaScript to Python.

</aside>

To understand why this query-then-display loop works so cleanly in Streamlit, it helps to understand its underlying execution model.

<aside> 🤓 Curious Geek: Why Streamlit reruns the whole script

Most web frameworks (Flask, Django, FastAPI) run a handler function once per HTTP request. Streamlit takes a simpler model: it reruns the entire Python script on every user interaction. This makes it easy to write reactive apps without callbacks or state machines, but it also means every function call (database query, API call) runs again. That is why @st.cache_data exists.

</aside>

The reference repository

To follow along with the examples in this chapter, you will use the nyc-taxi-streamlit-reference repository. This repository is already pre-configured with the dependencies, a starter file layout, and the practice exercises you will switch to later.

Clone the repository and move into it:

git clone <https://github.com/lassebenni/nyc-taxi-streamlit-reference.git>
cd nyc-taxi-streamlit-reference
git switch chapter-4-start

Install Streamlit

The repository already has a pyproject.toml listing the packages this chapter needs. Install them with uv sync, the dependency manager from Dependency Management:

uv sync

uv sync creates a .venv and installs the exact versions pinned in uv.lock, so you do not run pip install by hand. Here is why the project needs each package:

Prefix any command with uv run to run it inside that environment without activating it manually. Verify the install:

uv run streamlit hello

A demo app should open in your browser. Close it with Ctrl+C when you are done.

Core layout primitives

The chapter-4-start branch's app.py has just the imports, credential loading, and a title: a minimal starting point, not a spoiler. Open it now and follow along with each section below: add the snippets directly to app.py and run it with uv run streamlit run app.py. The next chapter, Building a Metrics Dashboard, continues on a new branch (chapter-5-start) that assembles these primitives into the real dashboard.

Titles and text

import streamlit as st

st.title("NYC Taxi metrics")            # large heading
st.header("Headline numbers")          # medium heading
st.write("Everything looks fine.")     # markdown-aware text

Here is how the layout rendering looks:

Streamlit Titles and Text

Streamlit Titles and Text

Metrics

st.metric shows a number with an optional delta (change from previous):

st.metric(label="Average fare", value="$13.42", delta="+$0.30")

Use st.columns to put multiple metrics side by side:

col1, col2, col3 = st.columns(3)
col1.metric("Total trips", "57,000")
col2.metric("Average fare", "$13.42", delta="+$0.30")
col3.metric("Total revenue", "$765,000", delta="+4%")

<aside> ⌨️ Hands on: Add the st.columns block above to your app.py. Run uv run streamlit run app.py and confirm three metric cards appear side by side. Change the delta value to a negative number and observe how the colour changes.

</aside>

Streamlit Metrics

Streamlit Metrics

DataFrames and charts

Streamlit integrates natively with the Python data science ecosystem. You do not need to manually convert your data to HTML or JSON: Streamlit directly understands Pandas DataFrames, NumPy arrays, and popular plotting libraries.

For data tables, passing a DataFrame to st.dataframe renders a rich, scrollable, and sortable table in the browser. For charts, Streamlit provides simple built-in shortcuts (st.line_chart, st.bar_chart, st.area_chart) that accept a DataFrame or Series directly.

If you need more advanced or customized charts, Streamlit also supports third-party plotting libraries including Plotly, Altair, Matplotlib/Seaborn, and Pydeck. You can see the full list of supported visualization types in the Streamlit Chart Elements Documentation.

import pandas as pd

df = pd.DataFrame({
    "borough": ["Manhattan", "Queens", "Brooklyn"],
    "trips": [45000, 21000, 18000],
})

st.dataframe(df)       # scrollable table
st.bar_chart(df.set_index("borough")["trips"])

Streamlit DataFrame and Bar Chart

Streamlit DataFrame and Bar Chart

For time-series data, st.line_chart expects a DataFrame with the date as the index:

ts = pd.DataFrame({"row_count": [11200, 12100, 12400]},
                  index=pd.date_range("2024-01-01", periods=3))
st.line_chart(ts)

Streamlit Line Chart

Streamlit Line Chart

Connecting to Azure Postgres

To connect securely, you must store your credentials outside of your code. Create a .env file in the root of your nyc-taxi-streamlit-reference repository and define your POSTGRES_URL.

Use the same Postgres username and password that you used in Week 10.

.env:

POSTGRES_URL="postgresql://your_username:[email protected]:5432/team1?sslmode=require"

Now, use sqlalchemy and python-dotenv to query your mart tables securely and load the results into a DataFrame:

import os
import pandas as pd
import sqlalchemy
import streamlit as st
from dotenv import load_dotenv

load_dotenv()  # load environment variables from a .env file

@st.cache_data(ttl=300)  # cache for 5 minutes
def load_fct_trips_summary():
    engine = sqlalchemy.create_engine(os.environ["POSTGRES_URL"])
    query = """
        SELECT pickup_borough, COUNT(*) AS trip_count
        FROM dev_yourname.fct_trips
        WHERE pickup_borough IS NOT NULL
        GROUP BY pickup_borough
        ORDER BY trip_count DESC
        LIMIT 10
    """
    with engine.connect() as conn:
        return pd.read_sql(query, conn)

df = load_fct_trips_summary()
st.dataframe(df)

Run your app:

uv run streamlit run app.py

Streamlit will load the environment variables and render the table:

Streamlit Postgres Data Table

Streamlit Postgres Data Table

<aside> ⚠️ Never hardcode database credentials directly in your Python file. By calling load_dotenv() and fetching variables from os.environ, you keep sensitive database credentials in a separate .env file (which is excluded from Git using .gitignore so they never leak).

</aside>

Caching with @st.cache_data

Without caching, every widget click reruns the entire script, including every database query and API call. For a dashboard that queries Postgres on every interaction, this creates visible lag and unnecessary database load.

Proving why caching is needed

To see this behavior in action, let us run a quick experiment:

  1. Add a print statement inside your database query function, and add an interactive button at the bottom of your app.py file:
def load_fct_trips_summary():
    # Print to the terminal to see when this function runs
    print("Running database query...")
    engine = sqlalchemy.create_engine(os.environ["POSTGRES_URL"])
    query = """
        SELECT pickup_borough, COUNT(*) AS trip_count
        FROM dev_yourname.fct_trips
        WHERE pickup_borough IS NOT NULL
        GROUP BY pickup_borough
        ORDER BY trip_count DESC
        LIMIT 10
    """
    with engine.connect() as conn:
        return pd.read_sql(query, conn)

st.title("NYC Taxi metrics")

# Add an interactive widget
if st.button("Click me to trigger a rerun"):
    st.write("Rerun triggered!")

df = load_fct_trips_summary()
st.dataframe(df)
  1. Save the file and look at the terminal where uv run streamlit run app.py is running.
  2. Click the Click me to trigger a rerun button on the page a few times.

Look at your terminal output. You will see:

Running database query...
Running database query...
Running database query...

Every button click reruns the entire script top-to-bottom. If your query takes a few seconds or your database is busy, this will make your app extremely sluggish.

Here is what the execution log looks like without caching after clicking the button multiple times (each rerun forces a new database query):

Uncached Database Queries Log

Uncached Database Queries Log

The Fix: @st.cache_data

Now, let us fix it. Decorate the function with @st.cache_data(ttl=300) (which caches the result for 5 minutes / 300 seconds):

@st.cache_data(ttl=300)  # cache for 5 minutes
def load_fct_trips_summary():
    print("Running database query...")
    # ... query code ...

Save the file and click the button again.

Observe the terminal output. Notice that Running database query... is not printed anymore when you click the button! Streamlit remembers the return value of the function, bypasses the database query, and instantly returns the cached DataFrame.

Here is the log after multiple clicks with caching enabled (only a single initial database query is executed, and subsequent reruns return the cached data instantly):

Cached Database Queries Log

Cached Database Queries Log

Use different TTLs for different queries:

<aside> ⚠️ @st.cache_data tracks the function's own code and its arguments. Editing the function body invalidates the cache on the next rerun, but a value the function reads from outside its body (a module-level query string, a global setting) is not tracked: change that, and the stale cached result keeps serving until the TTL expires. Pass changing inputs in as arguments so they become part of the cache key, and keep st.cache_data.clear() (a "Clear cache" button) as the escape hatch.

</aside>

Troubleshooting

| --- | --- | --- |

Ready for the next chapter when

The next chapter, Building a Metrics Dashboard, assembles these primitives into a real dashboard on your fct_trips mart. Make sure the basics run first: