Assignment: Build Two Dashboards
You have shipped both dashboards for the NYC taxi pipeline: a Metabase dashboard for the analytics team and a Streamlit metrics app for yourself. Before the team demo, your lead asks you to harden them: add a date filter so stakeholders can slice by period, document one metric so nobody argues about how it is calculated, and add a trend chart so the story is not just single numbers.
These exercises give you structured practice with both tools before the assignment. Work through Exercises 1-4 in order: each one builds on the previous. Exercises 5, 6, and 7 are optional and can be done any time after Exercise 1.
Each exercise is a branch in a reference repo, starting from a complete, runnable project. Clone the repo once, git switch to an exercise branch, follow its EXERCISE.md, then compare against the matching -solution branch.
nyc-taxi-metabase-reference. Each Question's SQL lives as a runnable .sql; the dashboard assembly stays in the Metabase UI.git clone <https://github.com/lassebenni/nyc-taxi-metabase-reference.git>
cd nyc-taxi-metabase-reference
git switch 01-exercise-sql-question # then read EXERCISE.md
nyc-taxi-streamlit-reference. A runnable metrics app; each exercise stubs one function (or, in Exercise 7, a drifted definition file) for you to complete. Exercise 5 extends that same app inline (no separate branch).git clone <https://github.com/lassebenni/nyc-taxi-streamlit-reference.git>
cd nyc-taxi-streamlit-reference
git switch practice-kpi-metrics # then read EXERCISE.md
uv sync # creates .venv and installs from uv.lock
cp .env.example .env # set POSTGRES_URL + DB_SCHEMA
uv run streamlit run app.py
Every branch-based exercise (1, 2, 3, 4, 6, 7) has a matching -solution branch (for example practice-kpi-metrics-solution). Attempt the exercise yourself first, then git switch to the solution branch to compare. Exercise 5 extends your own app inline and has no branch: its full code is in this chapter, so use that as the reference.
Write a Metabase Question in SQL mode that answers: "Which pickup zones have the most trips?"
SELECT
pickup_zone,
COUNT(*) AS trip_count
FROM dev_yourname.fct_trips
GROUP BY pickup_zone
ORDER BY trip_count DESC
LIMIT 10
Display it as a bar chart (or a horizontal row chart). Title it "Top 10 pickup zones". Save it to your Collection.
Then write its five-field metric definition now, so you have practised the format before the assignment grades it. The assignment requires one definition per Question and per panel; this is your first rep. Fill in all five fields for "Top 10 pickup zones":
| Field | Your answer |
|---|---|
| Name | top_pickup_zones |
| Description | (what does it measure, in plain English?) |
| Calculation | (the SQL that produces it: COUNT(*) grouped by pickup_zone, top 10) |
| Data source | (which table and schema?) |
| Refresh frequency | (when is the number current as of?) |
<aside>
📦 Branch: 01-exercise-sql-question: a worked SQL Question with an EXERCISE.md walkthrough for building it in the Metabase UI.
</aside>
Build the headline KPI row of the metrics app without looking back at Building a Metrics Dashboard. Implement render_kpi_panel so three st.metric tiles show total trips, average fare, and total revenue from fct_trips.
Requirements:
COUNT(*), AVG(fare_amount), and SUM(fare_amount) from {DB_SCHEMA}.fct_trips through the run_query helper already in the file.st.columns(3) and st.metric.run_query's @st.cache_data(ttl=300).Run it locally: uv run streamlit run app.py. Confirm the three tiles show real numbers from your mart.
<aside>
📦 Branch: practice-kpi-metrics: starter app.py with render_kpi_panel stubbed as a TODO, plus the practice-kpi-metrics-solution branch.
</aside>
Extend the app with a daily trip-volume line chart without looking back at the chapter. Implement render_daily_trend_panel so a st.line_chart shows trips per day. The headline-KPI and data-freshness panels are already provided.
Requirements:
SELECT date_trunc('day', pickup_datetime) AS day, COUNT(*) AS trips ... GROUP BY 1 ORDER BY 1 through run_query.day as the index and render with st.line_chart.<aside>
📦 Branch: practice-daily-trend: builds on the KPI app, with render_daily_trend_panel stubbed as a TODO and a practice-daily-trend-solution branch.
</aside>
The assignment's Required tier asks for a sidebar st.selectbox that filters every panel by payment_type_label. This is the one Required skill the other exercises don't drill, so practise it now on the app you built in Exercises 2-3.
<aside>
📦 Branch: practice-payment-filter: the KPI + trend + freshness app with the sidebar filter stubbed as a TODO, plus a practice-payment-filter-solution branch to compare against. Or just extend your own app from Exercises 2-3 with the snippets below.
</aside>
Add a sidebar dropdown, then rebuild every panel's SQL to filter on the selection:
# 1. Read the distinct payment types from the mart (cached like every other query).
payment_types = run_query(
f"SELECT DISTINCT payment_type_label FROM {DB_SCHEMA}.fct_trips ORDER BY 1"
)["payment_type_label"].tolist()
# 2. Offer them in the sidebar, with an "All" option that skips filtering.
selected = st.sidebar.selectbox("Payment type", ["All"] + payment_types)
# 3. Build a WHERE clause from the selection.
where = "" if selected == "All" else f"WHERE payment_type_label = '{selected}'"
Pass where into each panel's query. WHERE must come before GROUP BY, so splice {where} in between, not at the end:
# KPI panel (no GROUP BY): the clause goes at the end.
f"SELECT COUNT(*) FROM {DB_SCHEMA}.fct_trips {where}"
# Daily-trend panel (has GROUP BY): the clause goes before it.
f"SELECT date_trunc('day', pickup_datetime) AS day, COUNT(*) AS trips "
f"FROM {DB_SCHEMA}.fct_trips {where} GROUP BY 1 ORDER BY 1"
Because Streamlit reruns the whole script on any widget change, changing the dropdown rebuilds where and re-runs every panel with the new filter, no callback needed.
<aside>
⚠️ Interpolating selected straight into the SQL string is safe only because the value can only be one of the fixed dropdown options, never free-typed text. If you ever swap the st.selectbox for an st.text_input, that same f-string becomes an SQL-injection hole: switch to a parameterized query (the %s placeholders you used with psycopg2 in Week 3) before accepting typed input.
</aside>
Add an auto-refresh loop to your app.py using st.rerun():
import time
with st.sidebar:
auto_refresh = st.checkbox("Auto-refresh every 30s", value=False)
if auto_refresh:
time.sleep(30)
st.rerun()
Test it: enable the checkbox and confirm the metrics refresh without you manually reloading the page. Observe how @st.cache_data interacts with the refresh: the data only re-fetches after the TTL expires, not on every rerun.
<aside>
💡 The time.sleep(30) blocks the Streamlit thread for 30 seconds. For a production app you would use st.fragment with run_every=30 (Streamlit ≥ 1.35) instead, which avoids blocking the UI. For practice purposes, the sleep approach is fine.
</aside>
This one covers interactive filters, a concept the Dashboards in Metabase chapter deliberately leaves for the Going Further page rather than teaching as core material. It is optional for the assignment's Required tier (3 Questions + 1 Dashboard, no filter), but the same filter-wiring skill earns the assignment's Extra-tier bonus credit, so the real payoff is replicating this on fct_trips in the assignment.
Field-filter wiring is fiddly UI plumbing that is hard to discover by clicking around, so unlike Exercises 1-4 this one is a follow-along walkthrough: each step below is illustrated with the exact screen you should see. Read the prose for why each step is needed, then match your screen to the screenshot.
<aside>
💡 The screenshots use the shared nyc_taxi_reference schema (the same one Chapter 3 uses), so the SQL below reads from it too. That schema is always populated, so this exercise works even if you have not built the Week 10 assignment's fct_daily_borough_stats mart into your own dev_yourname schema yet.
</aside>
Build a small dashboard called "Practice Dashboard: [Your Name]" with two new Questions (this exercise does not reuse "Top 10 pickup zones" from Exercise 1: a per-day time series pairs better with a second breakdown than with another all-time ranking):
SELECT pickup_borough,
COUNT(*) AS trip_count
FROM nyc_taxi_reference.fct_trips
GROUP BY pickup_borough
ORDER BY trip_count DESC
Display it as a bar chart.
fct_daily_borough_stats.SELECT
pickup_date,
SUM(trip_count) AS total_trips
FROM nyc_taxi_reference.fct_daily_borough_stats
GROUP BY pickup_date
ORDER BY pickup_date
Display it as a line chart.
The Add questions sidebar in dashboard-edit mode, listing the two saved Questions ready to add
Both Questions arranged side by side on the dashboard grid: the borough bar chart on the left, the daily line chart on the right
A native SQL Question only accepts a dashboard filter if its SQL exposes a field filter template variable. Wiring one takes three steps: add the variable to the SQL, map it to a column, then connect the dashboard filter to it.
The Daily trip volume Question opened back in the SQL editor, ready to edit
WHERE clause, using the double-brace {{variable_name}} syntax:SELECT
pickup_date,
SUM(trip_count) AS total_trips
FROM nyc_taxi_reference.fct_daily_borough_stats
WHERE {{trip_date}}
GROUP BY pickup_date
ORDER BY pickup_date
Metabase detects the new {{trip_date}} variable as soon as you type it and opens a Variables and parameters panel on the right, defaulting the variable to type "Text":
The SQL editor with WHERE {{trip_date}} added and the Variables panel open, showing the variable defaulted to type Text
The Variable type dropdown open, showing Text, Number, Date, Boolean, Field Filter, Time grouping, and Table options