Assignment: Build Two Dashboards
In Streamlit Fundamentals you learned the primitives: st.metric, st.columns, @st.cache_data, and querying Postgres with sqlalchemy. This chapter combines them into a real metrics dashboard on your fct_trips mart.
The goal is one Streamlit app that answers the question: "What do the trips look like, and is my data complete and fresh?" It is the code-first counterpart to the Metabase dashboard you built earlier: same fct_trips table, but you control every panel in Python.
By the end of this chapter, you should be able to:
fct_trips on Azure Postgres and render headline KPIs with st.metric.st.line_chart.Metabase answered business questions for a stakeholder. This Streamlit app answers the same kind of question in code, and adds a "can I trust this data?" panel that is awkward to build in point-and-click tools. Every panel reads the same fct_trips mart:
| Panel | Query | What it shows |
|---|---|---|
| Headline KPIs | COUNT(*), AVG(fare_amount), SUM(fare_amount) |
Total trips, average fare, total revenue |
| Daily trip volume | COUNT(*) grouped by day |
Line chart of trips per day |
| Data freshness | COUNT(*), MAX(pickup_datetime) |
Row count and how recent the latest trip is |
| Dataset health | pg_total_relation_size, information_schema |
Mart size on disk, date range, and table count from Postgres's catalog |
Build these panels in order. By the end, you have a complete dashboard in roughly 70 lines of Python.
Here is what the finished metrics dashboard looks like:
Finished Metrics Dashboard
Continue in your nyc-taxi-streamlit-reference clone from the previous chapter, switched to the chapter-5-start branch:
cd nyc-taxi-streamlit-reference # or clone it again if you're starting fresh
git switch chapter-5-start
uv sync
cp .env.example .env # set your Week 9/10 POSTGRES_URL + DB_SCHEMA
chapter-5-start's app.py already has the imports, credential loading, and a run_query helper:
import os
import streamlit as st
import pandas as pd
import sqlalchemy
from dotenv import load_dotenv
load_dotenv() # reads .env file if present
POSTGRES_URL = os.environ["POSTGRES_URL"]
DB_SCHEMA = os.environ.get("DB_SCHEMA", "dev_yourname")
st.set_page_config(page_title="NYC Taxi Metrics", layout="wide")
st.title("NYC Taxi Metrics")
@st.cache_data(ttl=300)
def run_query(sql: str) -> pd.DataFrame:
engine = sqlalchemy.create_engine(POSTGRES_URL)
with engine.connect() as conn:
return pd.read_sql(sql, conn)
One small run_query helper keeps every panel to a single line. Because it is wrapped in @st.cache_data, repeated reruns do not re-hit the database. Add each panel below directly underneath it.
<aside>
📦 Reference repo: the finished dashboard for this chapter is on chapter-5-solution if you want to compare your work or peek when stuck.
</aside>
st.subheader("Headline KPIs")
kpis = run_query(f"""
SELECT COUNT(*) AS trip_count,
AVG(fare_amount) AS avg_fare,
SUM(fare_amount) AS total_fare
FROM {DB_SCHEMA}.fct_trips
""").iloc[0]
col1, col2, col3 = st.columns(3)
col1.metric("Total trips", f"{int(kpis['trip_count']):,}")
col2.metric("Average fare", f"${kpis['avg_fare']:.2f}")
col3.metric("Total revenue", f"${kpis['total_fare']:,.0f}")
Run this and you should see three KPI cards with real numbers from your mart:
Headline KPIs panel
<aside>
⌨️ Hands on: Add Panel 1 to your app.py. Put POSTGRES_URL and DB_SCHEMA in your .env file and run uv run streamlit run app.py. Confirm three KPI cards appear with real numbers from your mart. If you get UndefinedTable, your DB_SCHEMA does not match the dev_<name> where your Week 10 dbt build wrote fct_trips.
</aside>
st.subheader("Daily trip volume")
daily = run_query(f"""
SELECT date_trunc('day', pickup_datetime) AS day,
COUNT(*) AS trips
FROM {DB_SCHEMA}.fct_trips
GROUP BY 1
ORDER BY 1
""")
if not daily.empty:
st.line_chart(daily.set_index("day")["trips"])
else:
st.info("No trips found in fct_trips yet.")
date_trunc('day', pickup_datetime) collapses every timestamp to its calendar day, so the GROUP BY counts trips per day. st.line_chart wants the date as the index, which is why we set_index("day").
Daily trip volume chart
<aside> 🤓 Curious Geek: Why aggregate in SQL, not in Python
You could pull all 57,000 rows into a DataFrame and group them with pandas. Do not. Pushing the GROUP BY into Postgres means the database returns roughly 30 daily rows instead of 57,000, so the query is faster, the app uses less memory, and less data crosses the network. As tables grow into the millions of rows, "aggregate at the source" is the difference between a dashboard that loads instantly and one that times out.
</aside>
An analytical chart is only trustworthy if the data behind it is complete and recent. This panel makes that visible.
st.subheader("Data freshness")
fresh = run_query(f"""
SELECT COUNT(*) AS row_count,
MAX(pickup_datetime) AS last_pickup
FROM {DB_SCHEMA}.fct_trips
""").iloc[0]
col1, col2 = st.columns(2)
col1.metric("Row count", f"{int(fresh['row_count']):,}")
col2.metric("Last pickup", str(fresh["last_pickup"])[:16] if fresh["last_pickup"] else "unknown")
Data freshness panel
<aside>
⚠️ MAX(pickup_datetime) is a freshness proxy: it tells you the newest trip in the data, not when the table was last rebuilt. For the class's static NYC dataset that is exactly what you want. In a live pipeline you would also track when the mart itself was refreshed (later you will schedule that rebuild with an orchestrator, and could surface its last run here).
</aside>
The three numbers above describe your rows. Postgres also knows things about the table itself: how big it is on disk, and how many tables live in your schema. These come from Postgres's own catalog (pg_total_relation_size, information_schema), not from fct_trips rows. Reading them is a nice example of something a code-first tool does easily and point-and-click BI usually cannot.
try:
size = run_query(
f"SELECT pg_size_pretty(pg_total_relation_size('{DB_SCHEMA}.fct_trips')) AS mart_size"
).iloc[0]
span = run_query(f"""
SELECT MIN(pickup_datetime)::date AS first_day,
MAX(pickup_datetime)::date AS last_day,
COUNT(DISTINCT pickup_datetime::date) AS n_days
FROM {DB_SCHEMA}.fct_trips
""").iloc[0]
tables = run_query(f"""
SELECT COUNT(*) AS n
FROM information_schema.tables
WHERE table_schema = '{DB_SCHEMA}'
""").iloc[0]
col1, col2, col3 = st.columns(3)
col1.metric("Mart size on disk", size["mart_size"])
col2.metric("Date range", f"{span['first_day']} to {span['last_day']}")
col3.metric("Tables in schema", int(tables["n"]))
st.caption(f"Covers {int(span['n_days'])} days of trips.")
except Exception:
st.caption("Database-side metrics unavailable: your role may lack catalog access.")
Dataset health panel
The try/except matters here: your mart queries always work, but a locked-down database role can be denied access to catalog functions. Wrapping the block means the rest of the dashboard still renders if these three numbers are unavailable.
These are table-level metrics, so unlike the three panels above they ignore the borough filter you add next: the mart's size on disk and the number of tables in your schema do not change when you pick a borough.
<aside>
🤓 Curious Geek: pg_total_relation_size counts more than you think
The size it reports includes the table's rows plus its indexes and TOAST storage (Postgres's overflow area for large values). That is why "mart size on disk" is usually bigger than you would guess from row count alone: you are seeing the full on-disk footprint, not just the raw data.
</aside>
Add a sidebar so the dashboard can focus on a single pickup borough. Place this block near the top of the script, above every panel, and feed the selected borough into each query with a WHERE clause.
# Place near the top, before the panels.
with st.sidebar:
st.header("Filters")
boroughs = run_query(f"""
SELECT DISTINCT pickup_borough
FROM {DB_SCHEMA}.fct_trips
WHERE pickup_borough IS NOT NULL
ORDER BY 1
""")["pickup_borough"].tolist()
selected = st.selectbox("Pickup borough", options=["All"] + boroughs)
# Build a reusable WHERE clause from the selection.
where = "" if selected == "All" else f"WHERE pickup_borough = '{selected}'"
<aside>
⚠️ This f-string is safe only because selected always comes from the fixed borough dropdown, never free-typed text. Never interpolate user-entered input straight into SQL: that is an injection hole. When the value comes from a text box, use query parameters instead (the %s placeholders you saw with psycopg2 in Week 3).
</aside>
Now wire {where} into Panel 1, 2, and 3's FROM line. Only that one line in each query changes; the dataset-health block stays unfiltered, since it describes the table itself, not its rows:
# Panel 1: Headline KPIs
kpis = run_query(f"""
SELECT COUNT(*) AS trip_count,
AVG(fare_amount) AS avg_fare,
SUM(fare_amount) AS total_fare
FROM {DB_SCHEMA}.fct_trips {where}
""").iloc[0]
# Panel 2: Daily trip volume
daily = run_query(f"""
SELECT date_trunc('day', pickup_datetime) AS day,
COUNT(*) AS trips
FROM {DB_SCHEMA}.fct_trips {where}
GROUP BY 1
ORDER BY 1
""")
# Panel 3: Data freshness
fresh = run_query(f"""
SELECT COUNT(*) AS row_count,
MAX(pickup_datetime) AS last_pickup
FROM {DB_SCHEMA}.fct_trips {where}
""").iloc[0]
<aside>
⌨️ Hands on: Add the sidebar block and wire {where} into the three panels. Run the app and switch boroughs. Confirm the KPIs, chart, and freshness numbers all update. Selecting "All" should return to the full totals.
</aside>
Because Streamlit reruns the script on every widget change, picking a different borough automatically re-runs all three panels. Here is the dashboard filtered to Manhattan: total trips drops from 56,364 to 32,768 and the KPIs, chart, and freshness numbers all update, but the dataset-health panel stays put, exactly as predicted above.
Dashboard filtered to a single borough via the sidebar
You have now built the same kind of dashboard twice: once in Metabase, once in Streamlit. Both read the same fct_trips mart. The choice is about audience and how much custom logic you need:
| Need | Use |
|---|---|
| Non-technical stakeholder, no code | Metabase |
| Custom Python logic (computed fields, bespoke layout) | Streamlit |
| Self-serve: stakeholders build their own views | Metabase |
| A metric that needs code to calculate | Streamlit |
| Quick sharing with a public URL | Either: Metabase's public share is simpler; Streamlit Community Cloud is free |
| Needs to run on a schedule and email reports | Metabase (native scheduling) |
For most business dashboards, reach for Metabase first: it is faster and non-engineers can maintain it. Reach for Streamlit when a dashboard needs real code, or when it is a tool for engineers rather than stakeholders. In a real team, the same data engineer often maintains both.
<aside>
⚠️ Out of scope: Streamlit Community Cloud can host this app on a public URL for free, but it means pasting POSTGRES_URL (which contains the shared class database password) into a third-party platform's secrets store. Skip it for this assignment: run the app locally and submit the code. Week 12 covers Azure Key Vault and Managed Identity, the proper way to hand credentials to a hosted app without ever pasting them into a config file.
</aside>
The next chapter, Presenting Metrics, turns your dashboards into a clear 5-minute story. Confirm your metrics app works end to end:
COUNT, AVG, SUM) from fct_trips with st.metric.st.line_chart.MAX(pickup_datetime)).<aside>
💡 Using AI to help: Paste a panel bug into an LLM to debug it (for example, a sidebar filter that won't update the charts, or a st.line_chart that renders empty because the date column isn't the index). ⚠️ Never include real database passwords or personal data.
</aside>
GROUP BY in SQL instead of pulling all rows and grouping in pandas?