Assignment: Build Two Dashboards
In Metabase Setup you connected Metabase to your dbt mart tables and confirmed the data is there. Now you build something useful with it.
This chapter covers Metabase's core building blocks: Questions and Dashboards. You will write three Questions against fct_trips and fct_daily_borough_stats, and arrange them into a dashboard.
By the end of this chapter, you should be able to:
Metabase has two core objects:
The workflow is always: build Questions first, then assemble them into a Dashboard.
<aside> 💡 Key insight: A Question is reusable. The same Question showing "monthly trip count" can appear on a revenue dashboard, an operations dashboard, and a stakeholder report, without copying the SQL.
</aside>
Metabase offers two ways to query data:
| Mode | When to use | Notes |
|---|---|---|
| Question | Point-and-click exploration | Visual query builder. Good for non-technical users. Supports joins, custom columns, and basic filters. |
| SQL query | Raw SQL, exact control, advanced logic | Write SQL directly. Best when you need CTEs, custom window functions, or precise query-level optimization. |
If you create a standard visual Question, Metabase guides you through selecting a database, a schema, and a table visually:
Metabase prompting the user to pick a table when starting a visual Question
Once you select your schema, you can see all available tables:
Selecting the dev_yourname schema in Metabase showing available tables
Selecting a table (like fct_trips) opens the visual query builder, where you can filter, group, or join tables using a point-and-click interface:
The visual query builder in Metabase showing point-and-click options
For this week, you will write your questions as raw SQL queries. Since your dbt marts in Week 10 are already joined and cleaned, your queries will be simple SELECT statements with basic grouping and filters. Writing SQL gives you precise, developer-level control over what the chart displays.
To open SQL query mode:
team1.Here is the visual navigation flow to open the query editor and connect to your database:
The + New dropdown menu in Metabase showing Question, SQL query, Dashboard, and Document options
Selecting the team1 database in the SQL query editor dropdown
You will land on a blank query editor pane ready to write raw SQL:
A blank native SQL query editor window in Metabase connected to the team1 database
Build the following three Questions. Save each to your personal Collection as you go.
SELECT
pickup_borough,
COUNT(*) AS trip_count
FROM nyc_taxi_reference.fct_trips
WHERE pickup_borough IS NOT NULL
GROUP BY pickup_borough
ORDER BY trip_count DESC
Type the SQL query into the editor and click the blue Run query play button in the bottom-right corner:
Writing and running the trips by pickup borough query in Metabase
By default, Metabase displays query results as a table. Keep it as a table for now. Click the blue Save button in the top-right corner:
Clicking the Save button in the Metabase query editor
Name the question "Trips by pickup borough" and save it into your personal Collection:
The Save Question modal dialog showing Name and Collection settings
<aside>
⌨️ Hands on: Run this query. Confirm that Manhattan, Queens, and Brooklyn appear in the top three. If pickup*borough is mostly NULL, your stg*zones join may not have completed. Re-run dbt run for your Week 10 project first.
</aside>
SELECT
pickup_date,
SUM(total_fare) AS daily_revenue
FROM nyc_taxi_reference.fct_daily_borough_stats
GROUP BY pickup_date
ORDER BY pickup_date
Chart type: Line chart. X-axis: pickup_date, Y-axis: daily_revenue. Title: "Daily revenue (all boroughs)".
Open a new SQL query editor tab and run the query. Click Visualization and select Line:
The daily revenue line chart visualization in Metabase
To format the axes or customise the lines, click the gear icon next to the visualization selection (bottom-left) to open the Visualization options pane:
Visual options gear button in the query editor bottom bar
Here you can configure labels, scales, and colors:
Configuring the line chart visualization options in the Metabase side panel
Save your second question to your Collection:
Saving the daily revenue line chart Question to your Collection
SELECT
pickup_borough,
ROUND((AVG(avg_tip_pct) * 100)::numeric, 1) AS avg_tip_pct
FROM nyc_taxi_reference.fct_daily_borough_stats
WHERE pickup_borough NOT IN ('Unknown', 'EWR', 'NaN')
GROUP BY pickup_borough
ORDER BY avg_tip_pct DESC
Run the SQL query and visualize it as a Bar chart (or a horizontal Row chart). Give it the title "Average tip % by borough":
The average tip percentage by borough bar chart visualization in Metabase
Click the blue Save button in the top-right corner. Name the question "Average tip % by borough" and save it to your personal Collection.
Two details in this query are easy to miss:
avg_tip_pct is a floating-point column, and Postgres ROUND(value, decimals) only accepts numeric. Cast with ::numeric (as above) or you get ERROR: function round(double precision, integer) does not exist.Unknown, EWR (Newark airport), and a dirty NaN. Those are tiny, low-sample groups whose average tip swings wildly, so without the NOT IN (...) filter, Unknown sorts to the top with a meaningless ~28% and buries the five real boroughs. Filtering them keeps the chart honest: Manhattan and Brooklyn lead at roughly 16-17%.<aside> 🤓 Curious Geek: Why Metabase reruns the SQL on every load
Metabase does not cache query results by default. Every time someone opens a dashboard, Metabase re-executes each Question's SQL against the database. For small datasets this is instant. For large marts, you can enable question-level caching in Metabase's admin settings. The right fix is usually an index on the columns in your WHERE and GROUP BY clauses, not dashboard-side caching. dbt's config(materialized='table') also helps: materializing a mart as a table (not a view) means the aggregation work is already done.
</aside>
With three Questions saved, create a Dashboard:
Here is the visual step-by-step dashboard creation flow:
Step 1: Create the dashboard
Creating a new Dashboard modal in Metabase
Step 2: Add a question
The Add Question button in the dashboard editor top toolbar
Step 3: Browse and select saved Questions
Browsing available saved Questions in the Collection side panel
Step 4 & 5: Position and resize Question cards
Arranging and resizing the question cards on the dashboard editor grid
Your dashboard now shows three panels from two different mart tables. Once arranged, save your changes to see the final interactive view:
The completed NYC Taxi Analytics dashboard displaying three visual cards
<aside> ⌨️ Hands on: Arrange your three Questions into a single dashboard. Save it. Open it in a new browser tab and confirm all three panels load without errors.
</aside>
(Note: To learn how to add interactive filters to your dashboard, see the optional Going Further page.)
Since you are not an administrator, you cannot generate a public URL link to share your dashboard outside of Metabase. Instead, there are two ways to share your work with your team and teacher:
Open the sharing menu using the sharing icon in the top-right corner:
The sharing options menu on a Metabase dashboard
Clicking Export as PDF lets you save a document layout of your dashboard:
Exporting the Metabase dashboard layout as a PDF
Before moving on, fill in the five-field metric definition for each Question.
<aside> 💡 Best practice: Documenting metrics using a structured five-field definition is an industry best practice for data governance and cataloging, and is not a built-in feature of Metabase. In a production environment, you would catalog these definitions in a central repository or data catalog tool so business stakeholders understand exactly how each chart is calculated.
</aside>
Example definition for Question 1:
| --- | --- |
You will present these definitions in Presenting Metrics and submit them with the assignment.