Data Model Reference

Schemas, columns, 12 KPIs, and cross-platform differences

title: “Data Model Reference” subtitle: “Schemas, columns, 12 KPIs, and cross-platform differences” —

Bronze Schema (19 columns)

Raw data from NYC TLC, unchanged except for metadata additions.

Column	Type	Description
VendorID	Integer	TPEP provider (1=CMT, 2=VeriFone)
tpep_pickup_datetime	Timestamp	Meter start time
tpep_dropoff_datetime	Timestamp	Meter stop time
passenger_count	Integer	Number of passengers
trip_distance	Double	Trip distance in miles
RatecodeID	Integer	Rate code (1=Standard, 2=JFK, etc.)
store_and_fwd_flag	String	Store-and-forward flag
PULocationID	Integer	Pickup taxi zone ID
DOLocationID	Integer	Dropoff taxi zone ID
payment_type	Integer	Payment method (1=Credit, 2=Cash, etc.)
fare_amount	Double	Base fare
extra	Double	Extra charges
mta_tax	Double	MTA tax
tip_amount	Double	Tip (credit card only)
tolls_amount	Double	Bridge/tunnel tolls
improvement_surcharge	Double	Improvement surcharge
total_amount	Double	Total charged to passenger
congestion_surcharge	Double	NYC congestion surcharge
airport_fee	Double	Airport pickup fee

Metadata columns (added during Bronze ingestion — names vary by ingest path):

Path	Typical metadata
Databricks	`_source_path`, `_bronze_loaded_at`
Snowflake SQL	`_source_file`, `_loaded_at`
Snowflake Snowpark	`_LOADED_AT`

Downstream Silver/Gold models do not depend on these columns.

Silver Schema (derived columns)

All Bronze columns plus these derived fields:

Column	Type	Derivation
trip_duration	Double	`(dropoff - pickup)` in minutes
fare_per_mile	Double	`fare_amount / trip_distance`
tip_percentage	Double	`tip_amount / fare_amount * 100`
avg_speed	Double	`trip_distance / (duration_hours)`
pickup_hour	Integer	`HOUR(pickup_datetime)`
pickup_day_of_week	Integer	`DAYOFWEEK(pickup_datetime)`
is_weekend	Boolean	Saturday or Sunday
is_peak_hour	Boolean	7-9 AM or 4-7 PM
time_of_day	String	Morning Rush / Midday / Evening Rush / Night
distance_band	String	Five bands — see Distance band labels
payment_type_desc	String	See Payment type labels
rate_code_desc	String	Standard Rate / JFK / Newark / etc.
pickup_zone	String	Zone name from lookup
pickup_borough	String	Borough from lookup
dropoff_zone	String	Zone name from lookup
dropoff_borough	String	Borough from lookup
is_same_borough	Boolean	`pickup_borough == dropoff_borough`

Data Quality Rules

Applied during Silver transformation:

Rule	Filter Condition
Valid timestamps	`pickup_datetime IS NOT NULL AND dropoff_datetime IS NOT NULL`
Pickup before dropoff	`pickup_datetime <= dropoff_datetime`
Reasonable duration	`trip_duration BETWEEN 1 AND 1440 minutes`
Positive distance	`trip_distance > 0`
Positive fare	`fare_amount > 0`
Positive total	`total_amount > 0`
Valid passengers	`passenger_count BETWEEN 1 AND 8`
Negative tip correction	`CASE WHEN tip_amount < 0 THEN 0 ELSE tip_amount END`
Deduplication	Remove exact duplicate rows (partition key: pickup, dropoff, PU/DO location, fare)

Workshop dataset volumes

Pipelines load yellow_tripdata_2024-10.parquet (see workshop-data.json). Verified counts for that month:

Stage	Table	Row count
Before (Bronze)	`nyc_taxi_trips`	3,646,319
After (Silver)	`silver_nyc_taxi_cleaned` / `silver_nyc_taxi_enriched`	3,146,710
Removed	Bronze − Silver	499,609 (13.7%)

Retention: 86.3%. Gold kpi_data_quality_metrics surfaces these as bronze_record_count, silver_record_count, records_removed, and retention_rate_pct.

Platform note — Silver row counts may differ slightly

Snowflake and dbt should match 3,146,710 Silver rows for Oct 2024. Databricks applies an extra cross-month filter (filter_correct_month_data) that Snowflake and dbt do not — expect Databricks Silver to be marginally smaller. Gold KPI shapes stay the same; absolute numbers may differ by a fraction of a percent.

Derived value catalogs

Payment type labels

`payment_type` code	`payment_type_desc`
1	`Credit card`
2	`Cash`
3	`No charge`
4	`Dispute`
5	`Unknown`
6	`Voided trip`
else	`Other`

Use exact casing in SQL filters and DAX ("Credit card", not "Credit Card").

Time of day

Hour (`pickup_hour`)	`time_of_day`
6–9	`Morning Rush`
10–15	`Midday`
16–19	`Evening Rush`
else	`Night`

Peak hours (is_peak_hour): 7, 8, 9, 16, 17, 18, 19.

Distance band labels

Silver / KPI 7 (five bands):

Miles	`distance_band`
≤ 1	`Very Short (≤1 mi)`
≤ 3	`Short (1-3 mi)`
≤ 10	`Medium (3-10 mi)`
≤ 25	`Long (10-25 mi)`
> 25	`Very Long (>25 mi)`

KPI 11 trip efficiency (four bands — collapses long trips):

Miles	`distance_band`
≤ 1	`Very Short (≤1 mi)`
≤ 3	`Short (1-3 mi)`
≤ 10	`Medium (3-10 mi)`
> 10	`Long (>10 mi)`

Zone Lookup (4 columns)

Column	Type	Description
LocationID	Integer	Zone identifier (1-265)
Borough	String	NYC borough name
Zone	String	Neighborhood/zone name
service_zone	String	Service zone category

Zone lookup edge cases

TLC assigns catch-all and out-of-area zones in the official lookup. The workshop joins them like any other zone — they are not Silver quality failures.

LocationID	Borough	Zone	Pipeline behavior
1	`EWR`	Newark Airport	Kept in Silver and Gold; exclude in Power BI for NYC-only maps
264	`Unknown`	`N/A`	Kept — valid “unknown zone” bucket
265	`N/A`	Outside of NYC	Kept — valid out-of-NYC bucket
Not in lookup	`NULL`	`NULL`	Join fails — row may still be in Silver enriched; some Gold KPIs exclude null zones; `kpi_data_quality_metrics` reports `null_pickup_zone_pct`

Silver quality rules remove bad trips (invalid fare, distance, timestamps). They do not remove trips whose geography is Unknown, N/A, or EWR when the location ID matches the lookup.

Priya’s five questions

Priya’s story arc uses five business questions. The workshop builds twelve Gold kpi_* tables so each question has the right aggregation grain. Consumption: Exercise: Power BI maps one dashboard page per question cluster.

#	Question	Primary Gold tables	Power BI page
1	When are peak revenue hours?	`kpi_revenue_by_hour`, `kpi_trips_by_hour`	Time Analysis · Revenue & Payments
2	Which pickup zones drive volume?	`kpi_top_pickup_zones`	Map
3	Which routes are most popular?	`kpi_popular_routes`	Map
4	Revenue by borough and distance band?	`kpi_borough_analysis`, `kpi_distance_bands`	Map · Trip Efficiency
5	Is bad data skewing decisions?	Silver quality rules + `kpi_data_quality_metrics`	Overview

Question 5 detail: Silver filters reject null fares (fare_amount > 0), zero-distance trips (trip_distance > 0), invalid timestamps, and other bad rows before Gold aggregates run. For Oct 2024, 499,609 of 3,646,319 Bronze trips are removed (13.7%); 3,146,710 remain in Silver. kpi_data_quality_metrics exposes records_removed (Bronze minus Silver), retention_rate_pct, and composite data_quality_score so Priya can show Marcus how much bad data was kept out of KPIs.

Supporting KPIs are listed in the KPI catalog below (Priya Q marked “support”).

12 Core KPIs

All paths materialise the same 12 table names (kpi_*). Column names are lowercase snake_case in Databricks and Snowflake SQL/dbt; Snowpark may store them as UPPERCASE — Power BI and Snowflake SQL treat these as equivalent when unquoted.

Design principle: One Gold table per business question grain — not one wide dashboard table. Each kpi_* table can refresh, test, and be consumed independently (Power BI, exports, ML features). Priya maps them to five dashboard pages; see Priya’s five questions for the story layer.

KPI catalog (business + design)

KPI table	Grain	Priya Q	Business meaning	Power BI page
`kpi_trips_by_hour`	`pickup_hour` × peak × weekday/weekend	1 (primary)	When is demand highest — fleet positioning, shift planning	Overview · Time Analysis
`kpi_trips_by_day`	`day_of_week`	1 (support)	Which days are busiest — weekly ops rhythm	Overview
`kpi_time_of_day_analysis`	`time_of_day` (4 periods)	1 (support)	Rush vs midday vs night — executive-friendly time buckets	Overview · Time Analysis
`kpi_top_pickup_zones`	`pickup_zone` + borough (top 20)	2 (primary)	Where to stage taxis — highest pickup demand by neighbourhood	Map
`kpi_borough_analysis`	`pickup_borough` × `dropoff_borough`	4 (primary)	Origin–destination patterns — borough revenue and cross-borough flow	Map
`kpi_popular_routes`	zone pair (top 20 routes)	3 (primary)	Most common A→B trips — corridor planning, not just pickup hot spots	Map
`kpi_distance_bands`	`distance_band` (5 bands)	4 (primary)	How trip length drives revenue — short hops vs long hauls	Trip Efficiency
`kpi_passenger_count_analysis`	`passenger_count`	4 (support)	Load factor — share of 1-passenger vs group trips	Trip Efficiency
`kpi_revenue_by_hour`	`pickup_hour`	1 (primary)	When money peaks — fare, tips, tolls, surcharges by hour	Time Analysis · Revenue & Payments
`kpi_payment_type_analysis`	`payment_type_desc`	— (support)	Cash vs card mix — tips, fare levels, digital payment share	Revenue & Payments
`kpi_trip_efficiency`	`distance_band` (4 bands)	4 (support)	Yield — speed, fare/mile, revenue/minute by trip length (ops efficiency)	Trip Efficiency
`kpi_data_quality_metrics`	`metric_name` (long format)	5 (primary)	Can Marcus trust the dashboard — rows removed, retention, zone nulls	Overview

KPI 1: `kpi_trips_by_hour`

Business: Answers “How many trips happen each hour?” — Marcus uses this for fleet supply and peak staffing; Priya’s Overview line chart and Time Analysis page.
Design: Grain = hour × peak_status × day_type; pre-aggregated counts and simple revenue averages. Split from kpi_revenue_by_hour so volume charts stay lightweight while the revenue table holds fare/tip/toll decomposition.

Column	Description
`pickup_hour`	Hour of day (0–23)
`peak_status`	`Peak Hour` or `Off-Peak Hour`
`day_type`	`Weekday` or `Weekend`
`total_trips`	Trip count
`total_revenue`	Sum of `total_amount`
`avg_fare`	Mean `total_amount`
`avg_distance`	Mean trip distance
`avg_duration`	Mean trip duration (minutes)

KPI 2: `kpi_trips_by_day`

Business: “Which day of the week is busiest?” — weekly planning (maintenance windows, driver schedules).
Design: Grain = day_of_week with day_order for correct Mon→Sun sort in Power BI; complements hourly KPIs without duplicating hour-level rows.

Column	Description
`day_of_week`	`Mon` … `Sun` (all paths)
`day_order`	Sort key (1 = Mon … 7 = Sun) — use in Power BI
`total_trips`	Trip count
`total_revenue`	Sum of `total_amount`
`avg_fare_per_mile`	Mean fare per mile

KPI 3: `kpi_time_of_day_analysis`

Business: “When do rushes happen?” in four buckets Marcus can read without studying a 24-hour chart.
Design: Collapses hours into time_of_day + period_order; donut/summary visuals on Overview and Time Analysis. Different grain than kpi_trips_by_hour — period labels are the product requirement.

Column	Description
`time_of_day`	Morning Rush / Midday / Evening Rush / Night
`total_trips`	Trip count
`total_revenue`	Sum of `total_amount`
`avg_distance`	Mean trip distance
`avg_tip_amount`	Mean tip amount
`avg_duration`	Mean trip duration
`period_order`	Sort key (1–4)

KPI 4: `kpi_top_pickup_zones`

Business: “Where should we position empty taxis?” — top neighbourhoods by pickup volume and revenue.
Design: Top 20 zones by trip count (trip_rank); filters pickup_zone IS NOT NULL. One row per pickup zone, not route — paired with kpi_popular_routes for A→B corridors.

Column	Description
`pickup_zone`	Zone name
`pickup_borough`	Borough
`total_trips`	Trip count
`total_revenue`	Sum of `total_amount`
`avg_fare`	Mean `total_amount`
`avg_distance`	Mean trip distance
`avg_tip_pct`	Mean tip percentage
`trip_rank`	Rank by trip count (top 20) — not `rank`

KPI 5: `kpi_borough_analysis`

Business: “How does revenue flow between boroughs?” — origin–destination patterns for dispatch and marketing.
Design: Grain = borough pair (pickup_borough × dropoff_borough). Power BI shape maps use pickup_borough only — Sum rolls up all outbound routes from each borough. Includes Unknown / N/A / EWR if present in Silver (zone edge cases).

Column	Description
`pickup_borough`	Origin borough
`dropoff_borough`	Destination borough
`total_trips`	Trip count
`total_revenue`	Sum of `total_amount`
`avg_fare`	Mean `total_amount`
`avg_distance`	Mean trip distance
`avg_duration`	Mean trip duration
`avg_tip_pct`	Mean tip percentage
`peak_hour_trips`	Trips during peak hours
`peak_hour_pct`	Peak-hour share (%)

KPI 6: `kpi_popular_routes`

Business: “What are the most common trips?” — zone-to-zone corridors (e.g. JFK → Midtown), not just busy pickup points.
Design: Top 20 routes by trip count (route_rank); requires both pickup and dropoff zones. Wider grain than kpi_top_pickup_zones — two zone columns per row.

Column	Description
`pickup_zone` / `dropoff_zone`	Route endpoints
`pickup_borough` / `dropoff_borough`	Boroughs
`total_trips`	Trip count
`total_revenue`	Sum of `total_amount`
`avg_fare`	Mean `total_amount`
`avg_distance`	Mean trip distance
`avg_duration`	Mean trip duration
`route_rank`	Rank by trip count (top 20)

KPI 7: `kpi_distance_bands`

Business: “How does revenue change with trip length?” — five distance buckets for pricing and fleet mix decisions.
Design: Uses five Silver distance bands (band_order 1–5). Separate from kpi_trip_efficiency, which uses four collapsed bands and speed/yield metrics — do not merge; Power BI Trip Efficiency page uses both.

Column	Description
`distance_band`	Five-band labels (see above)
`total_trips`	Trip count
`total_revenue`	Sum of `total_amount`
`avg_fare`	Mean `total_amount`
`avg_fare_per_mile`	Mean fare per mile
`avg_duration`	Mean trip duration
`avg_tip_pct`	Mean tip percentage
`avg_speed`	Mean speed (mph)
`band_order`	Sort key (1–5)

KPI 8: `kpi_passenger_count_analysis`

Business: “How often do we carry more than one passenger?” — vehicle utilisation and fare patterns by load.
Design: Grain = passenger_count (1–8 after Silver filters); includes pct_of_total for share-of-fleet context on Trip Efficiency page.

Column	Description
`passenger_count`	Number of passengers (> 0)
`total_trips`	Trip count
`total_revenue`	Sum of `total_amount`
`avg_fare`	Mean `total_amount`
`avg_distance`	Mean trip distance
`avg_tip_pct`	Mean tip percentage
`avg_duration`	Mean trip duration
`pct_of_total`	Share of all trips (%)

KPI 9: `kpi_revenue_by_hour`

Business: “When do we earn the most money?” — peak revenue hours with fare, tip, toll, and surcharge breakdown for finance and ops.
Design: Grain = pickup_hour only (no peak/weekend split here — see kpi_trips_by_hour). Wider column set for Revenue & Payments page; avoids bloating the trips-by-hour table used for simple line charts.

Column	Description
`pickup_hour`	Hour of day
`total_trips`	Trip count
`total_revenue`	Sum of `total_amount`
`avg_fare`	Mean `total_amount`
`total_tips`	Sum of `tip_amount`
`avg_tip_pct`	Mean tip percentage
`total_base_fare`	Sum of `fare_amount`
`total_tolls`	Sum of `tolls_amount`
`total_congestion_surcharge`	Sum of `congestion_surcharge`
`revenue_per_trip`	Mean revenue per trip

KPI 10: `kpi_payment_type_analysis`

Business: “Cash vs card — how do customers pay?” — tip behaviour, digital share, revenue mix (supports incentive and payment-policy decisions).
Design: Grain = payment_type_desc; not one of Priya’s five headline questions but required for Revenue & Payments visuals. Unknown payment type is a label mapping, not a borough (payment type labels).

Column	Description
`payment_type_desc`	Credit card / Cash / …
`total_trips`	Trip count
`total_revenue`	Sum of `total_amount`
`avg_fare`	Mean `total_amount`
`avg_tip`	Mean tip amount
`avg_tip_pct`	Mean tip percentage
`avg_distance`	Mean trip distance
`pct_of_total`	Share of all trips (%)
`trips_with_tip`	Trips with tip > 0
`tip_rate_pct`	Share of trips with tips (%)

KPI 11: `kpi_trip_efficiency`

Business: “Are we earning efficiently per mile and per minute?” — speed and yield by trip length for ops tuning (not the same as raw revenue by band).
Design: Four distance bands (trips >10 mi collapsed to Long) with avg_speed_mph, revenue_per_minute, avg_fare_per_mile. Complements kpi_distance_bands (five bands, revenue-focused) on the same Trip Efficiency page.

Column	Description
`distance_band`	Four-band labels (see above)
`total_trips`	Trip count
`avg_speed_mph`	Mean speed
`avg_fare_per_mile`	Mean fare per mile
`revenue_per_minute`	Mean revenue per trip minute
`avg_duration_min`	Mean trip duration

There is no avg_distance on this table — Power BI scatter charts should use avg_fare_per_mile on the X-axis.

KPI 12: `kpi_data_quality_metrics`

Business: “Can we trust these KPIs?” — Marcus and the board see how much bad data Silver removed before Gold ran (question 5).
Design: Long-format scorecard (metric_name / metric_value) — one row per metric, not wide columns. Powers Overview cards (data_quality_score, records_removed, retention_rate_pct) via DAX filters on metric_name. James validates thresholds in SQL; Priya displays cards.

Long-format table: each row is one metric.

Column	Description
`metric_name`	Metric key (see below)
`metric_value`	Numeric value

`metric_name`	Meaning
`bronze_record_count`	Rows in Bronze trips table
`silver_record_count`	Rows in Silver enriched
`records_removed`	Bronze minus Silver — includes null fares, zero-distance trips, and other rows Silver filters out
`retention_rate_pct`	Silver / Bronze × 100
`null_pickup_zone_pct`	% rows missing pickup zone
`null_dropoff_zone_pct`	% rows missing dropoff zone
`avg_trip_distance`	Mean trip distance in Silver
`avg_fare_amount`	Mean total amount in Silver
`data_quality_score`	Composite non-null score (0–100)

Oct 2024 expected values: bronze_record_count 3,646,319 · silver_record_count 3,146,710 · records_removed 499,609 · retention_rate_pct 86.3

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"
    )

Display all three on the Power BI Overview page — see Priya Q5.

Cross-platform data model

Databricks, Snowflake, and dbt implement the same logical medallion (Bronze → Silver → 12 Gold KPIs → ML features). Downstream tools (Power BI, ML, AI) must use column names from your path, but the business meaning is identical when you follow the recommended tracks.

What is aligned (logical contract)

Element	Canonical rule
Layer flow	Bronze → `silver_nyc_taxi_cleaned` → `silver_nyc_taxi_enriched` → 12 `kpi_*`
Payment labels	See payment type labels
Time of day	See time of day
Day-of-week labels	`Mon`–`Sun` + `day_order` in all Gold batch paths
ML filters	`'Credit card'`, outlier caps `fare_amount < 200`, `tip_amount < 100`
12 KPI names	Same table names on every batch path

Physical naming by platform

Layer	Databricks	Snowflake Snowpark + dbt	Snowflake SQL scripts
Catalog / database	`mhpdeworkshop_databricks_2026`	`DE_MASTERCLASS`	`DE_MASTERCLASS`
Bronze schema	`{attendee_id}_bronze`	`{ATTENDEE_ID}_SP_BRONZE`	`{attendee_id}_SQL_BRONZE`
Bronze trips	`nyc_taxi_trips`	`BRONZE_NYC_TAXI_TRIPS`	`bronze_nyc_taxi_trips`
Silver / Gold tables	`silver_`, `kpi_` (lowercase)	`SILVER_` in Snowpark; `kpi_` lowercase in SQL CTAS	lowercase
Column casing	lowercase	UPPERCASE in Snowpark DataFrames; lowercase when written via SQL/dbt	lowercase
dbt `source()` compatible?	Yes (`dbt run --target databricks`)	Yes (Snowpark bronze)	No — bronze table names differ

Recommended workshop tracks

Goal	Run this
Databricks only	`databricks/notebooks/01–03`
Snowflake only (no dbt)	`snowflake/snowpark/01–03` or `snowflake/sql/*`
Snowflake + dbt	`snowflake/snowpark/01–03` → `dbt run --target snowflake`
Databricks + dbt	Databricks notebooks 01–03 → `dbt run --target databricks`
Power BI	Any path that completes all 12 Gold KPIs, then connect Import mode

Why differences exist

The workshop deliberately uses multiple engines (Spark, Snowflake SQL, Snowpark, dbt Jinja). Identical logic does not require identical syntax or storage conventions. Differences fall into a few explainable categories.

1. Platform identifier conventions

Why	Detail
Unity Catalog (Databricks) prefers lowercase schema and table names; unquoted identifiers fold to lower case.	`{attendee_id}_bronze.nyc_taxi_trips`
Snowflake stores unquoted identifiers as UPPERCASE; Snowpark Python APIs often expose columns in uppercase.	`BRONZE_NYC_TAXI_TRIPS`, `PICKUP_HOUR`
dbt abstracts both via `generate_schema_name` and conditional `identifier` in `_staging.yml`.	Same model SQL compiles to different physical names per `target.type`

Impact: Power BI and Workspaces SQL files may show TOTAL_TRIPS or total_trips — same data. Use the name your connector displays; do not assume one global casing.

2. Two Snowflake Bronze ingest paths (teaching vs dbt)

Path	Bronze table	Reason it exists
SQL scripts (`snowflake/sql/bronze/`)	`bronze_nyc_taxi_trips`	Familiar CTAS/COPY INTO pattern for SQL-first learners
Snowpark (`snowflake/snowpark/01_*.py`)	`BRONZE_NYC_TAXI_TRIPS`	Matches dbt `source('bronze', 'nyc_taxi_trips')` identifiers

dbt does not ingest Bronze — it reads tables your pipeline already created. If you run SQL bronze then dbt run --target snowflake, dbt looks for BRONZE_NYC_TAXI_TRIPS and fails. That is not a bug; it reflects real-world ownership boundaries (ingestion team vs transformation team).

3. dbt as transformation layer, not ingestion

dbt models Silver and Gold from existing Bronze/Silver tables. Batch notebooks and Snowpark scripts materialise tables; dbt documents, tests, and optionally rebuilds them with version-controlled SQL. Extra dbt-only affordances (schema tests, data_quality_score macro, tagged ML/streaming models) exist because dbt is the contract layer — not because Databricks or Snowflake cannot compute the same values (batch paths now include data_quality_score too).

4. Engine-specific functions (same semantics, different functions)

Concept	Databricks / Spark	Snowflake / dbt
Day name in Gold	`date_format(..., 'EEE')` → `Mon`	`dayname()` → `Mon`
Weekend flag	`dayofweek in (1, 7)` (Sun = 1)	`dayofweek in (0, 6)` (Sun = 0)
`pickup_day_of_week` integer	Spark convention	Snowflake convention

Impact: ML features use the integer pickup_day_of_week — values differ by engine (0–6 vs 1–7) but remain consistent within each platform. Cross-platform RMSE comparison uses the same platform’s Silver table end-to-end.

5. Optional pipeline rules (small row-count deltas)

Rule	Where	Why
Cross-month filter	Databricks Silver only	Removes trips whose pickup month ≠ file month (TLC file quirk)
DLT production pipeline	`databricks/production/dlt_pipeline.py`	Streaming demo — 4 simplified KPIs, not the full 12-table contract
SQL vs Snowpark bronze metadata	`_source_file` vs `_LOADED_AT`	Different ingest templates; not used downstream

These explain why absolute KPI numbers may differ slightly between Databricks and Snowflake while rankings and patterns stay the same.

6. Consumption-layer expectations (Power BI / ML / AI)

Tool	Reads	Must match
Power BI	All 12 `kpi_*` tables	Field names from connected warehouse; sort days by `day_order`
ML notebooks	Silver enriched (or dbt `ml_features_tip_prediction`)	Unified filters documented above
AI / Cortex	Silver enriched + selected Gold KPIs	Lowercase SQL in SQL files; Snowflake folds case

Quick reference — downstream columns

KPI table	Key columns for Power BI / AI
`kpi_payment_type_analysis`	`payment_type_desc`, `avg_tip`, `pct_of_total`
`kpi_trips_by_hour`	`pickup_hour`, `day_type`, `total_trips`
`kpi_trips_by_day`	`day_of_week`, `day_order`, `total_trips`
`kpi_trip_efficiency`	`avg_fare_per_mile`, `avg_speed_mph`, `revenue_per_minute`
`kpi_top_pickup_zones`	`trip_rank`, `pickup_zone`, `total_trips`
`kpi_data_quality_metrics`	`data_quality_score` via `metric_name` / `metric_value`

ML feature table

ml_features_tip_prediction (dbt) and training notebooks share:

Features: trip_distance, fare_amount, passenger_count, pickup_hour, pickup_day_of_week, is_weekend, time_of_day, pickup_borough, dropoff_borough

Target: tip_amount (dbt column target_tip_amount)

Filters:

WHERE payment_type_desc = 'Credit card'
  AND tip_amount >= 0 AND fare_amount > 0 AND trip_distance > 0
  AND passenger_count BETWEEN 1 AND 8
  AND fare_amount < 200 AND tip_amount < 100