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

Assignment: Build Two Dashboards

The Scenario

Your data team has shipped the NYC taxi pipeline: dbt marts from Week 10. Now two audiences need to see the results. The analytics team wants a self-serve dashboard to explore trips and revenue, and you want a code-first metrics app that shows the headline numbers plus a "is this data complete and fresh?" panel before anyone asks. This assignment builds both, documents the metrics so nobody argues about the numbers, and presents them in five minutes.

You connect the dbt marts from Week 10 to two dashboards, document the metrics they show, and present the results.

The assignment has two tiers. Required must be completed to pass; Extra is bonus.

<aside> 💡 Get the core dashboards (3 Metabase Questions, the Streamlit KPI panel) working end-to-end with real data before building the freshness panel, filters, and presentation. A working core dashboard beats a half-built one with every feature attempted.

</aside>

Required

Build a Metabase analytical dashboard and a Streamlit metrics dashboard. Both must work end-to-end with real data from your Azure Postgres marts.

Metabase dashboard

1. Log in to the HYF-managed Metabase instance.
2. Build three Questions in SQL mode, each using one of your dbt mart tables (fct_trips or fct_daily_borough_stats). These are deliberately not the three Questions from Dashboards in Metabase: that chapter covers pickup_borough and pickup_date. Use columns the chapter never touches: (a) trip count by payment type (bar chart), from fct_trips.payment_type_label; (b) average fare per mile by dropoff borough (bar chart), from fct_trips.fare_per_mile and fct_trips.dropoff_borough; (c) average trip duration by hour of day (line chart), computing duration inline as EXTRACT(EPOCH FROM (dropoff_datetime - pickup_datetime)) / 60, grouped by EXTRACT(HOUR FROM pickup_datetime).
3. Arrange all three Questions into one Dashboard named "NYC Taxi Analytics: [Your Name]", and save it into the shared "Week 11 Submissions" collection (not your personal collection). Your teacher can only see and grade dashboards saved there.
4. Write a metric definition (five fields: name, description, calculation, data source, refresh frequency) for each Question in your metric_definitions.md file.
5. Write a short description (2-3 sentences) in the Metabase Question description field for each Question, explaining what it measures and what the audience should look for.

Streamlit metrics dashboard

Step 1: Don't start from a blank file. Your forked data-assignment-week-11 scaffold already has week11-streamlit/ with the project wired up (pyproject.toml, uv.lock, the cached run_query helper from Building a Metrics Dashboard) and three st.metric tiles stubbed as # TODO:

your-fork/
├── README.md
├── AI_ASSIST.md
├── metric_definitions.template.md   ← at the repo ROOT: copy to week11-streamlit/metric_definitions.md and fill in
└── week11-streamlit/
    ├── app.py                 ← fill in the three TODO-stubbed KPI queries
    ├── pyproject.toml
    ├── uv.lock
    └── .env.example           ← copy to .env, fill in your credentials

cd week11-streamlit
uv sync
cp .env.example .env   # fill in your credentials

Step 2: Implement the three stubbed KPI tiles: total trips, average trip distance, and average fare per mile, all from fct_trips. This is deliberately not the "total trips / average fare / total revenue" trio from Building a Metrics Dashboard: you're applying the same run_query + st.metric + st.columns pattern to different columns (trip_distance, fare_per_mile), not retyping the chapter's solution.

Step 3: Keep @st.cache_data on all database calls (already wired into run_query; keep it that way if you add more queries).

Step 4: Store credentials in environment variables or a .env file (never in app.py).

Step 5: Add Panel 2: a st.line_chart of trip count by hour of day (COUNT(*) grouped by EXTRACT(HOUR FROM pickup_datetime)) from fct_trips. This is a different angle from the daily-by-date trend in Building a Metrics Dashboard: same run_query + st.line_chart pattern, a new time grain.

Step 6: Add Panel 3: a data-freshness panel showing the row count and the latest pickup_datetime for fct_trips.

Step 7: Add a sidebar st.selectbox to filter every panel by payment_type_label (not pickup_borough, which Building a Metrics Dashboard already covers).

Step 8: Copy metric_definitions.template.md (at the repo root) to week11-streamlit/metric_definitions.md and write a metric definition (five fields: name, description, calculation, data source, refresh frequency) for every Metabase Question and every Streamlit panel you build.

<aside> ⚠️ Connect with your own personal Postgres login (the one you've used since Week 9/10), and add ?sslmode=require to your connection string. A wrong DB_SCHEMA (not your dev_<name>) is the most common cause of an empty dashboard.

</aside>

Presentation

Record a short walkthrough of both dashboards, then upload it. Use the what/why/action structure from Presenting Metrics:

• What changed: summarise the key findings and data-freshness status (30 seconds)
• What it means: walk through 2-3 panels with metric definitions (3 minutes)
• What happens next: one action you would take based on the data (30 seconds)

Then complete these two steps:

1. Record your screen and voice in a 5-minute (maximum) screen recording (Teams/Zoom/QuickTime/OBS) that saves an .mp4 to your computer, presenting both dashboards.
2. Upload the .mp4 to the shared Azure student-submissions container (steps below) and put the link in your PR.

<aside> ⚠️ Keep the recording private. It shows your screen and voice, so don't make it public unless you want to, and don't commit the .mp4 to the repo (git keeps files in history, and repos can become public). Host it in Azure Blob Storage as described below (teachers get read access, nothing is public), and put just the link in your PR. Before uploading, check the frame for anything sensitive: no passwords, .env contents, or connection strings on screen.

</aside>

Host the recording on Azure Blob Storage

Upload the recording to the shared student-submissions container: teachers (the HYF-Teachers group) have read access, students (HYF-Students) can upload, and nothing is public. This keeps your recording inside the same Azure environment as the rest of your work and practises a real data-engineering skill: putting a file in object storage and sharing a scoped, private link.

Everyone shares one container, so put your full name in the file path (for example week-11/jane-doe.mp4). A file named presentation.mp4 or week-11.mp4 is impossible for a teacher to match to you, and could overwrite a classmate's upload. Use lowercase and hyphens, no spaces.

You can do this two ways: with the az CLI, or entirely by clicking in the Azure Portal. Both leave you with a link to paste into your README.md.

Option A: the az CLI

Upload it (the --content-type makes it stream as video, not download):

az login --tenant 07a14c4e-d88c-42f7-83b3-13af7e57ff3d   # once, into the HYF tenant

az storage blob upload \
  --account-name hyfstoragedev --container-name student-submissions \
  --name "week-11/jane-doe.mp4" --file presentation.mp4 \
  --content-type video/mp4 --auth-mode login --overwrite

Then paste the blob URL into your README.md:

https://hyfstoragedev.blob.core.windows.net/student-submissions/week-11/jane-doe.mp4

Only HYF-Teachers members can open it, and only through the Azure Portal storage browser while signed in. Pasting the URL into a plain browser tab returns 409 PublicAccessNotPermitted: that is expected, not a bug, it means the file is private. Your teacher opens it from the portal to review it.

Option B: the Azure Portal (no CLI)

Prefer clicking to typing? Do the whole thing in the browser:

1. Rename your recording to <your-name>.mp4 (for example jane-doe.mp4) on your computer first: the upload keeps your file's name as the blob name.
2. Open the hyfstoragedev Storage browser in the Azure Portal and sign in with your HYF account. (If the link lands on a different page, search the top bar for hyfstoragedev, open the storage account, and click Storage browser in the left menu.)
3. Go to Blob containers → student-submissions. Click Upload, expand Advanced, set Upload to folder to week-11, pick your file, and upload.
4. Click your uploaded blob and copy its URL (shown in the blob's properties, in the form https://hyfstoragedev.blob.core.windows.net/student-submissions/week-11/<your-name>.mp4) into your README.md.

<aside> ⚠️ The blob URL is private: only HYF-Teachers members can open it, and only through the Azure Portal storage browser while signed in. Pasting it into a plain browser tab returns 409 PublicAccessNotPermitted, which is expected. Never make the blob or container public to get around this.

</aside>

Deliverables (Required)

• Your Metabase dashboard saved in the shared Week 11 Submissions collection (non-admin accounts can't generate a public URL; see Dashboards in Metabase). Add its link plus screenshots or a PDF export to the repo so your teacher can also review it without opening Metabase
• week11-streamlit/ folder pushed to your GitHub repo
• metric_definitions.md with five-field definitions for all Metabase panels and all Streamlit panels
• AI_ASSIST.md documenting how you used an LLM on this assignment (which tool, what you asked, what you accepted or rejected)
• 5-minute presentation recording link

<aside> 💡 Using AI to help: Record your LLM usage in AI_ASSIST.md as you go: a debugging prompt, a SQL snippet you asked it to explain, a metric definition you had it critique. ⚠️ Never paste real credentials, personal data, or unapproved company data into an LLM.

</aside>

Extra

For students who want to go further. Extra work is not required.

1. Add a date-range filter to your Metabase dashboard that connects to at least two of your three Questions.
2. Export your Metabase dashboard as a PDF and attach it to a Notion page (public share URLs aren't available to non-admin accounts, so a live embed isn't possible; see Dashboards in Metabase).
3. Add auto-refresh to your Streamlit app using st.rerun() with a configurable interval (30-second default), so the metrics update without a manual page reload.

Submission

Fork the assignment scaffold and submit your work as a Pull Request:

1. Fork data-assignment-week-11 and clone your fork. The scaffold's README.md shows where each file goes.
2. Fill in the stubbed week11-streamlit/app.py, add metric_definitions.md (from the template at the repo root) and AI_ASSIST.md. Save your Metabase dashboard into the shared Week 11 Submissions collection, and put its link plus screenshots or a PDF export in the repo README.md.
3. Self-check before you submit: run bash .hyf/test.sh from the repo root. The untouched scaffold fails on purpose; a complete Required solution passes. Fix whatever it flags.
4. Create a branch week11/your-name, commit your work, push, and open a Pull Request against the scaffold repo.
5. Fill in the PR description. The scaffold ships a template that loads automatically when you open the PR, complete every section (see "Package your pull request for review" below).

Your submission must include: