Exercise: Power BI Dashboard

YellowLine NYC · five pages · all 12 Gold KPIs · self-paced after Module 4

Estimated time: 50–70 min (Snowflake key-pair setup + connect: 20 min · five pages: 30–45 min)

YellowLine NYC context (after Module 4)

Priya needs a five-page dashboard on the 12 Gold KPI tables Bob built in Databricks, Snowflake, and dbt. This exercise is self-paced — complete it after Exercise: dbt when all kpi_* tables exist.

Free Power BI Desktop only

This lab uses Power BI Desktop — a free Windows app. You do not need Power BI Pro or Premium.

Included (free)	Not required for this lab
Connect, model, build reports locally	Sharing reports with colleagues
Save a `.pbix` file on your PC	Premium capacity
Optional publish to My Workspace (personal)	On-premises data gateway
Shape map with bundled NYC borough boundaries	Premium capacity

Platform: Power BI Desktop runs on Windows only (system requirements). macOS/Linux users can follow along by reading the steps or using a Windows VM.

GitHub blocked? (emergency only)

The normal path is fork + Codespace (Prerequisites § Step 2). Use Lab source files only if your facilitator approved it — e.g. you cannot create or use GitHub before class.

Prerequisites

See also Power BI setup guide (install Desktop, connector steps).

Complete these exercises first so all Gold KPI tables exist:

Exercise: Databricks or Exercise: Snowflake
Exercise: dbt (recommended — same schema on Snowflake)

Verify in Snowflake (if you used the Snowflake path):

Path you completed	Gold schema in Navigator
dbt (recommended)	`{attendee_id}_DBT_GOLD`
Snowflake SQL Workspaces only (no dbt)	`{attendee_id}_SQL_GOLD`
Snowpark terminal only	`{attendee_id}_SP_GOLD`

There is no plain {attendee_id}_GOLD schema — see recommended workshop tracks.

SET attendee_id = 'de_XX_yourname';
-- dbt path (default for this exercise):
LET gold_schema := $attendee_id || '_DBT_GOLD';
-- SQL-only path: use $attendee_id || '_SQL_GOLD' instead
SHOW TABLES IN SCHEMA DE_MASTERCLASS.IDENTIFIER(:gold_schema);
-- Expect 12 tables named kpi_* (or KPI_* depending on casing)

All 12 KPI tables — Priya’s five questions

#	Priya’s question	Gold tables	Dashboard page
1	Peak revenue hours	`kpi_revenue_by_hour`, `kpi_trips_by_hour`	Time Analysis · Revenue & Payments
2	Top pickup zones	`kpi_top_pickup_zones`	Map
3	Popular routes	`kpi_popular_routes`	Map
4	Borough + distance band	`kpi_borough_analysis`, `kpi_distance_bands`	Map · Trip Efficiency
5	Bad data guardrails	Silver filters + `kpi_data_quality_metrics`	Overview

Story context: Use case — Priya’s five KPI questions · KPI catalog — business & design

KPI table	Dashboard page
`kpi_trips_by_hour`	Overview · Time Analysis
`kpi_trips_by_day`	Overview
`kpi_time_of_day_analysis`	Overview · Time Analysis
`kpi_data_quality_metrics`	Overview (Quality Score, Records Removed, Retention Rate)
`kpi_borough_analysis`	Map (shape map, revenue bar)
`kpi_top_pickup_zones`	Map (zone bubble map)
`kpi_popular_routes`	Map (routes table)
`kpi_revenue_by_hour`	Time Analysis · Revenue & Payments
`kpi_payment_type_analysis`	Revenue & Payments
`kpi_trip_efficiency`	Trip Efficiency
`kpi_distance_bands`	Trip Efficiency
`kpi_passenger_count_analysis`	Trip Efficiency

Extra DAX measures and theme JSON: Power BI DAX & theme reference (companion reference).

Step 0 — Install Power BI Desktop

Download from Get Power BI Desktop (Microsoft Store or direct installer).
Launch the app — you should see the Home ribbon, Report canvas, Data and Visualizations panes (getting started).

Step 1 — Connect to all 12 Gold KPI tables

Choose one warehouse — Snowflake is simplest if you completed the dbt exercise ({attendee_id}_DBT_GOLD).

Option A: Snowflake (recommended)

Official flow: Power Query Snowflake connector

Snowflake trial accounts require key-pair authentication in Power BI (username/password fails when MFA is enforced). Complete the one-time setup below, then load your Gold tables.

Snowflake trial accounts — use key-pair auth in Power BI

Snowflake trial accounts enforce multi-factor authentication (MFA) for password logins. Power BI Desktop cannot complete an MFA prompt, so username/password fails with errors such as:

390197: Multi-factor authentication is required for this account
Failed to parse PEM block containing the private key (if Key Pair was selected without a valid key)

Workshop path: set up key-pair authentication once, then connect from Power BI with Key Pair Auth. Both Snowflake and the Power BI Snowflake connector support this method.

Programmatic access tokens (PATs) in Snowsight are not supported for Power BI Desktop — see Snowflake partner authentication support.

Before you start

Requirement	Notes
MFA enrolled	Log in to Snowsight and complete MFA enrollment if prompted
OpenSSL	Needed to generate keys — included with Git for Windows
ACCOUNTADMIN	On a trial account you are admin — needed to run `ALTER USER … SET RSA_PUBLIC_KEY`
Private key file	Store `rsa_key.p8` on your PC only — never commit to git or share in chat

Official references: Snowflake key-pair auth · Power BI Snowflake connector · Snowflake MFA

Step 1 — Generate a key pair

Open PowerShell or Git Bash on your Windows PC. Choose a folder outside the repo (e.g. Documents\snowflake-keys).

Simplest for the workshop — no passphrase in Power BI.

openssl genrsa 2048 | openssl pkcs8 -topk8 -inform PEM -out rsa_key.p8 -nocrypt
openssl rsa -in rsa_key.p8 -pubout -out rsa_key.pub

Creates:

rsa_key.p8 — private key (keep secret; used in Power BI)
rsa_key.pub — public key (registered in Snowflake)

If you prefer a passphrase on the private key:

openssl genrsa 2048 | openssl pkcs8 -topk8 -v2 des3 -inform PEM -out rsa_key.p8
openssl rsa -in rsa_key.p8 -pubout -out rsa_key.pub

Enter a passphrase when prompted. You will need the same passphrase in Power BI’s Key Pair Auth dialog.

Verify the private key format — open rsa_key.p8 in Notepad. It must start with:

-----BEGIN PRIVATE KEY-----

(or -----BEGIN ENCRYPTED PRIVATE KEY----- for the encrypted option). If you see BEGIN RSA PRIVATE KEY, re-run the commands above — Power BI expects PKCS#8 format.

Step 2 — Register the public key in Snowflake

Open rsa_key.pub in Notepad.
Copy only the base64 text between the header and footer lines — exclude -----BEGIN PUBLIC KEY----- and -----END PUBLIC KEY-----.
In Snowsight, set role to ACCOUNTADMIN and run (replace placeholders):

ALTER USER <your_snowflake_username> SET RSA_PUBLIC_KEY='MIIBIjANBgkqh...';

Paste your single-line public key string inside the quotes.

Verify:

DESC USER <your_snowflake_username>;

Confirm RSA_PUBLIC_KEY_FP is populated.

Step 3 — Connect from Power BI Desktop

Official connector steps: Power Query Snowflake connector

File → Options and settings → Data source settings — remove any stale Snowflake entries for your server (avoids cached username/password).
Home → Get data → More… → Database → Snowflake → Connect
Enter:
- Server: full account host from Snowsight View account details (e.g. el30551.west-europe.azure.snowflakecomputing.com) — not the short .env form (el30551.west-europe.azure)
- Warehouse: DE_WORKSHOP_WH
Select Connect → choose Key Pair Auth (not username/password)
Enter:
- Username: your Snowflake username (same as Snowsight login)
- Private key: browse to rsa_key.p8, or paste the full PEM contents including BEGIN / END lines
- Passphrase: leave blank for unencrypted keys; enter it if you used the encrypted option
In Navigator, expand DE_MASTERCLASS → open the Gold schema for your path:

Path Schema

dbt (recommended) {ATTENDEE_ID}_DBT_GOLD

Snowflake SQL only {ATTENDEE_ID}_SQL_GOLD

Snowpark only {ATTENDEE_ID}_SP_GOLD

Select all 12 kpi_* tables (or KPI_* — same data, different casing).
Choose Load (not Transform Data) → Import when prompted (Import vs DirectQuery)

Path	Schema
dbt (recommended)	`{ATTENDEE_ID}_DBT_GOLD`
Snowflake SQL only	`{ATTENDEE_ID}_SQL_GOLD`
Snowpark only	`{ATTENDEE_ID}_SP_GOLD`

Advanced options (optional)

In the Snowflake connection dialog, open Advanced options and set Role name to DE_WORKSHOP_ROLE if Navigator shows no schemas or access is denied.

Snowflake connection troubleshooting

Issue	What to try
`390197` MFA required	Use Key Pair Auth, not username/password
`Failed to parse PEM block containing the private key`	Regenerate with the OpenSSL commands above (PKCS#8); browse to the `.p8` file; clear Data source settings
Power BI June 2026 (`2.155.x`) auth failures	Known regression — install May 2026 Desktop (`2.154.x`) from Get Power BI Desktop
Navigator empty / access denied	Resume `DE_WORKSHOP_WH`; set Role name to `DE_WORKSHOP_ROLE`; confirm Gold schema (`_DBT_GOLD`, `_SQL_GOLD`, or `_SP_GOLD` — not plain `_GOLD`); confirm all 12 `kpi_*` tables exist
Wrong server format	Use full `*.snowflakecomputing.com` host — not the short `.env` form (`el30551.west-europe.azure`)

Checkpoint: The Data pane lists 12 kpi_* tables. No relationships are required — each KPI table is self-contained.

Import vs DirectQuery

For this workshop, choose Import when prompted (Import and DirectQuery). KPI tables are small; Import avoids gateway setup and works fully offline after refresh.

DirectQuery is optional for Module 8 streaming scenarios — not needed here.

Option B: Azure Databricks

Official flow: Azure Databricks connector · Databricks partner guide

Home → Get data → search Azure Databricks
Enter Server hostname and HTTP path from your SQL warehouse (Databricks → SQL Warehouses → Connection details)
Expand Advanced options → set Catalog to mhpdeworkshop_databricks_2026
Authentication → Personal Access Token → paste your PAT
Navigator → {attendee_id}_gold → select all 12 kpi_* tables → Load

Checkpoint: The Data pane lists 12 kpi_* tables. No relationships are required — each KPI table is self-contained.

Step 2 — Create DAX measures

Official guide: Create measures in Power BI Desktop

Modeling → New Table:

_Measures = DATATABLE("helper", STRING, {{"_"}})

Right-click _Measures → Hide in report view
Home → New measure — add these (one at a time):

Total Trips = SUM(kpi_trips_by_hour[total_trips])

Total Revenue = SUM(kpi_trips_by_hour[total_revenue])

Avg Fare = AVERAGE(kpi_trips_by_hour[avg_fare])

Quality Score =
    CALCULATE(
        MAX(kpi_data_quality_metrics[metric_value]),
        kpi_data_quality_metrics[metric_name] = "data_quality_score"
    )

Records Removed =
    CALCULATE(
        MAX(kpi_data_quality_metrics[metric_value]),
        kpi_data_quality_metrics[metric_name] = "records_removed"
    )

Retention Rate Pct =
    CALCULATE(
        MAX(kpi_data_quality_metrics[metric_value]),
        kpi_data_quality_metrics[metric_name] = "retention_rate_pct"
    )

Note

kpi_data_quality_metrics is long-format: columns are metric_name and metric_value (not data_quality_score). Filter with CALCULATE + metric_name as shown above.

Priya Q5: Silver drops null fares, zero-distance trips, and other invalid rows before Gold runs. For Oct 2024: 499,609 of 3,646,319 Bronze rows removed (13.7%); 3,146,710 kept. records_removed = Bronze minus Silver; retention_rate_pct ≈ 86.3%. See Data model — Priya Q5 and Workshop dataset volumes.

Credit card Avg Tip =
    CALCULATE(
        AVERAGE(kpi_payment_type_analysis[avg_tip]),
        kpi_payment_type_analysis[payment_type_desc] = "Credit card"
    )

More optional measures: DAX reference.

Step 3 — Apply theme (optional)

Copy theme JSON from MHP theme into a file named mhp-theme.json
View → Themes → Browse for themes → select the file

Step 4 — Page 1: Overview

Add a new report page and title it Overview.

6 × Card — drag from _Measures: Total Trips, Total Revenue, Avg Fare, Quality Score, Records Removed, Retention Rate Pct
- Format Retention Rate Pct as Percentage with 1 decimal (shows how much Bronze data survived Silver cleaning)
- Records Removed answers Priya’s bad-data question — includes null fares, zero-distance trips, and other rows Silver filtered out
Line chart — kpi_trips_by_hour: X-axis pickup_hour, Y-axis total_trips (Sum)
Clustered bar chart — kpi_trips_by_day: Y-axis day_of_week, X-axis total_trips (Sum) — sort day_of_week by day_order ascending (Mon → Sun)
Donut chart — kpi_time_of_day_analysis: Legend time_of_day, Values total_trips (Sum) — sort by period_order ascending

Checkpoint: Six cards and three charts show non-empty values. Records Removed should be > 0 if Silver filtered bad trips.

KPI tables used on this page: kpi_trips_by_hour, kpi_trips_by_day, kpi_time_of_day_analysis, kpi_data_quality_metrics.

Step 5 — Page 2: Map — Borough & Zone Analysis

Add a page titled Map.

Borough boundary file (Step 5a — custom map)

Two Shape maps on this page

Visual	Custom map?	Role
5a-1 — Revenue shape map	No (default world map)	Shows borough revenue shading
5a-2 — Borough selector	Yes (`nyc-boroughs.topojson`)	Click a borough here to cross-filter the page

Clicking a borough on 5a-2 highlights the same borough on 5a-1, the borough revenue bar (Step 5d), the routes table, and the zone maps.

Get the TopoJSON file

Option	Steps
A — Use the workshop repo (recommended)	Upload `powerbi/nyc-boroughs.topojson` — already converted for Power BI.
B — Download and convert yourself	See Manual conversion below.

Manual conversion (if you are not using the bundled repo file)

Download GeoJSON — open NYC Open Data — Borough Boundaries → Export → GeoJSON → save as nyc-boroughs.geojson.
(Or use powerbi/nyc-boroughs.geojson from the workshop repo as the starting file.)
Convert to TopoJSON — open mapshaper.org → drag in the GeoJSON (or Import → select file) → Export → TopoJSON → save as nyc-boroughs.topojson.
Upload nyc-boroughs.topojson on the 5a-2 Shape map only (steps in Step 5a below).

Borough names in the file (boroname) match pickup_borough in your KPI tables: Manhattan, Brooklyn, Queens, Bronx, Staten Island.

Enable Shape map (if missing from the Visualizations pane): File → Options and settings → Options → Preview features → Shape map visual → restart Desktop.

Load the custom map on 5a-2

Build the 5a-2 Shape map — set Location = pickup_borough first.
Select that Shape map → Format visual (paint brush) → expand Map settings → Map type → Custom map.
Click Add a map type → browse to nyc-boroughs.topojson → Open.
Click View map type key — confirm borough names match your data.
Click a borough — 5a-1, the bar chart, table, and zone maps should highlight or filter to that borough.

5a — Borough shape maps (two visuals) — kpi_borough_analysis

This table has one row per borough pair (pickup_borough + dropoff_borough). Both maps use pickup_borough — Power BI Sum rolls up all outbound routes from each borough.

Build two Shape maps from the same table. Only the second one needs the custom boundary file.

5a-1 — Revenue shape map (default map — no custom file)

Insert → Shape map
Location: pickup_borough
Color saturation (or Values): total_revenue (Sum)
Leave Map type at the default — do not upload a custom map
Visual-level filter on pickup_borough → exclude EWR if present

5a-2 — Borough selector (custom map — click here to filter the page)

Boundary file: upload powerbi/nyc-boroughs.topojson from the repo. To build it yourself: download GeoJSON from NYC Open Data — Borough Boundaries (Export → GeoJSON), then convert at mapshaper.org (Export → TopoJSON). See the callout above.

Insert → Shape map (second visual)
Location: pickup_borough
Color saturation: total_revenue (Sum) (optional — keeps the selector visually consistent)
Format visual → Map settings → Map type → Custom map → Add a map type → select nyc-boroughs.topojson
Click View map type key — borough names should match: Manhattan, Brooklyn, Queens, Bronx, Staten Island
Same EWR filter as 5a-1
Click a borough on this map — 5a-1 and the Step 5d bar chart should highlight that borough; the routes table and zone maps should cross-filter

5b — Zone map (bubble points) — kpi_top_pickup_zones

One row per zone (top 20). Use the built-in Map visual (bubble map).

Add a calculated column on kpi_top_pickup_zones:

Zone_Location =
    kpi_top_pickup_zones[pickup_zone]
        & ", "
        & kpi_top_pickup_zones[pickup_borough]
        & ", New York, NY"

Insert → Map
Location: Zone_Location
Size: total_trips (Sum)
Legend: pickup_borough (optional)
Tooltips: avg_fare, trip_rank, total_revenue

Checkpoint: Bubbles clustered around NYC (not upstate New York).

5c — Popular routes table — kpi_popular_routes

Insert → Table
Columns: pickup_zone, dropoff_zone, total_trips, avg_fare, avg_distance — Don’t summarize on each column
Visual-level filter: top 15 by total_trips
Format visual → Cell elements → total_trips → Conditional formatting → Data bars

5d — Borough revenue bar — kpi_borough_analysis

Insert → Clustered bar chart
Y-axis: pickup_borough, X-axis: total_revenue (Sum)

Checkpoint: Two shape maps show borough revenue; clicking a borough on 5a-2 (custom map) highlights 5a-1 and the 5d bar chart and cross-filters other page visuals; zone map shows NYC bubbles.

KPI tables used on this page: kpi_borough_analysis, kpi_top_pickup_zones, kpi_popular_routes.

Step 6 — Page 3: Time Analysis

Add a page titled Time Analysis.

Matrix — kpi_trips_by_hour: Rows day_type, Columns pickup_hour, Values total_trips (Sum)
- Format → Cell elements → Background color → Gradient (#E6F0FF → #003366)
Donut chart — kpi_time_of_day_analysis: Legend time_of_day, Values total_trips (Sum) — sort by period_order
Column chart — kpi_revenue_by_hour: X-axis pickup_hour, Y-axis total_revenue (Sum)

KPI tables used on this page: kpi_trips_by_hour, kpi_time_of_day_analysis, kpi_revenue_by_hour.

Step 7 — Page 4: Revenue & Payments

Add a page titled Revenue & Payments.

Pie chart — kpi_payment_type_analysis: Legend payment_type_desc, Values total_trips (Sum)
Table — columns: payment_type_desc, total_trips, total_revenue, avg_fare, avg_tip, pct_of_total — Don’t summarize on each column
Column chart — kpi_revenue_by_hour: X-axis pickup_hour, Y-axis total_revenue (Sum)
Card — _Measures: Credit card Avg Tip

KPI tables used on this page: kpi_payment_type_analysis, kpi_revenue_by_hour.

Step 8 — Page 5: Trip Efficiency

Add a page titled Efficiency.

Scatter chart — kpi_trip_efficiency: X-axis avg_fare_per_mile, Y-axis avg_speed_mph, Size total_trips — Don’t summarize on X/Y (four rows, one per distance band)
Funnel chart — kpi_distance_bands: Group distance_band, Values total_trips (Sum) — sort by band_order
Stacked bar chart — kpi_passenger_count_analysis: Y-axis passenger_count, X-axis total_trips (Sum) — sort passenger_count ascending

KPI tables used on this page: kpi_trip_efficiency, kpi_distance_bands, kpi_passenger_count_analysis.

Step 9 — Save and publish (optional)

File → Save As → YellowLine-NYC-KPIs.pbix

Saving locally is enough for the self-paced lab. To publish (upload from Desktop):

Home → Publish
Sign in with a Microsoft account
Choose My workspace (free personal workspace)

Free My workspace is personal — you cannot share the report with other users without a Pro license. Your facilitator may screen-share a pre-built .pbix during the demo.

Troubleshooting

Issue	What to try
Snowflake login fails (MFA / PEM / 390197)	Use key-pair auth — full steps in Step 1 Option A and Snowflake connection troubleshooting
Snowflake Navigator empty / no `kpi_*`	Wrong Gold schema — use `_DBT_GOLD` (dbt), `_SQL_GOLD` (SQL only), or `_SP_GOLD` (Snowpark); not plain `_GOLD`
Snowflake timeout / empty Navigator	Warehouse running? Role `DE_WORKSHOP_ROLE` and database `DE_MASTERCLASS` exist?
Connector error after Desktop update	See Snowflake connector notes — try latest Desktop build
Databricks catalog error in Navigator	Advanced options → Catalog = `mhpdeworkshop_databricks_2026`
Databricks connection fails	SQL warehouse running; PAT not expired; use Azure Databricks connector (not generic Databricks)
Shape map blank / upload fails	Use `powerbi/nyc-boroughs.topojson`; or download GeoJSON from NYC Open Data and convert at mapshaper.org; enable Shape map in Preview features; Location = `pickup_borough`
Zone map plots outside NYC	Use `Zone_Location` with borough in the formula (Step 5b)
Map disabled by organisation	Use Step 5d bar chart for borough KPIs; zone Table instead of bubble map
Blank visuals	Data pane → confirm table loaded; check field names match Gold schema (`pickup_hour` not `hour_of_day`); Snowpark path may show UPPERCASE — use names as displayed in Fields
Quality Score measure error	Table is long-format — use the `CALCULATE` measure in Step 2, not `[data_quality_score]`
Day bar chart order wrong	Sort `day_of_week` by `day_order` (1=Mon … 7=Sun), not alphabetically

What you learned

Consumption layer — Power BI reads all 12 Gold KPI tables; it does not replace Databricks, Snowflake, or dbt
Priya’s five questions — five dashboard pages answer Marcus’s business questions; Overview shows data quality (records_removed, retention_rate_pct) so bad rows never skew Gold KPIs
Same schema — connect to Databricks or Snowflake Gold; Priya’s five-page layout stays identical
Import mode — simplest path for workshop KPIs on free Desktop
Maps — two Shape maps (default + custom TopoJSON selector) for borough revenue with click-to-filter; Map visual with Zone_Location for top pickup zones

Reference — What the Silver layer did (read later)

Not part of the lab steps. Your dashboard reads Gold tables built from Silver — use this to explain values like Unknown, N/A, or EWR on the Map page.

Silver outputs

Table	Contents
`silver_nyc_taxi_cleaned`	Quality filters, corrections, derived columns — before zone join
`silver_nyc_taxi_enriched`	Cleaned trips plus zone lookup — Gold KPIs read this table

1. Filtered out (rows removed)

Silver drops trips that would distort KPIs. Workshop month (Oct 2024): 499,609 of 3,646,319 Bronze rows removed (13.7%); 3,146,710 remain in Silver. See Workshop dataset volumes.

Rule	Condition
Valid timestamps	Pickup and dropoff not null; pickup ≤ dropoff
Reasonable duration	Trip ≤ 24 hours (1–1440 minutes)
Positive distance	`trip_distance > 0`
Positive fare & total	`fare_amount > 0`, `total_amount > 0`
Valid passengers	`passenger_count` between 1 and 8
Airport sanity	No zero-distance trips with airport fee
Deduplication	Exact duplicate rows removed

Databricks only: extra cross-month filter on raw Parquet (adjacent-month outliers).

Full list: Data Model — Data Quality Rules.

2. Corrected (row kept, value fixed)

Field	Change
`tip_amount`	Negative tips set to 0
`payment_type_desc`	Code → label (`Credit card`, `Cash`, `Unknown`, …)
`rate_code_desc`	Code → label (`Standard rate`, `JFK`, `Unknown`, …)

3. Derived (new columns)

Examples: trip_duration_minutes, fare_per_mile, tip_percentage, avg_speed, pickup_hour, day_of_week, is_weekend, is_peak_hour, time_of_day, distance_band.

Catalog: Data Model — Silver schema.

4. Joined / enriched

LEFT JOIN taxi_zone_lookup on pickup_location_id and dropoff_location_id:

pickup_zone, pickup_borough, pickup_service_zone
dropoff_zone, dropoff_borough, dropoff_service_zone
is_same_borough

Join succeeds for TLC zone IDs 1–265 (including catch-all zones below).

5. Kept on purpose (not filtered in Silver)

LocationID	Borough	Zone	Why you may see it in Gold / Power BI
264	`Unknown`	`N/A`	Valid TLC “unknown zone” bucket
265	`N/A`	Outside of NYC	Valid out-of-NYC bucket
1	`EWR`	Newark Airport	Valid airport zone — not a NYC borough

True null zones (ID not in lookup, e.g. corrupt 0) → pickup_zone IS NULL — tracked in kpi_data_quality_metrics; excluded from some Gold KPIs (kpi_top_pickup_zones, kpi_borough_analysis when borough is null).
Power BI Map page: filter EWR, Unknown, and N/A on pickup_borough if you want NYC boroughs only — optional visual filter, not a pipeline change.

Detail: Data Model — Zone lookup edge cases.

6. Silver → Gold → Power BI

Bronze (raw) → Silver cleaned → Silver enriched → 12 Gold kpi_* tables → Power BI

Gold aggregates enriched trips (hour, zone, borough, payment, …). It does not re-apply Silver quality rules — rows in silver_nyc_taxi_enriched can include Unknown / N/A boroughs unless a Gold model filters them. Per-table business meaning and design: Data Model — KPI catalog.

Next steps

Module 7 — compare your dashboard with the live demo; join the open tool discussion for YellowLine NYC
Optional modules — Module 8 adds DirectQuery streaming page; Module 9 adds predicted-tip visuals
DAX deep dive — Power BI DAX & theme reference

Return to module

Module 7 — Comparison & Wrap-up
Module 4 — dbt Pipeline (Power BI connects after Gold KPIs)