Star vs Snowflake for Uber Data | Dataford Interview Questions

Explain the differences between star schema and snowflake schema; when would you choose one over the other for Uber-scale data?

Using the provided Uber trip and location tables, produce the requested aggregate as a concrete SQL example. Keep the explanation focused on dimensional modeling, query behavior, and scale-related trade-offs.

Output

One row per city, with city_name, region_name, completed_trips, and gross_bookings
Include completed trips requested from 2025-01-01 through 2025-01-31
Sort by gross_bookings descending, then city_name ascending

Schema

trips

Column	Type	Description
trip_idPK	INT	Unique trip identifier
zone_id	INT	Pickup zone identifier
status	VARCHAR(20)	Trip lifecycle status
requested_at	DATE	Trip request date
fare_amount	NUMERIC(10,2)	Trip fare before adjustments

zones

Column	Type	Description
zone_idPK	INT	Unique pickup zone identifier
zone_name	VARCHAR(80)	Pickup zone name
city_id	INT	Parent city identifier

cities

Column	Type	Description
city_idPK	INT	Unique city identifier
city_name	VARCHAR(80)	City name
region_name	VARCHAR(80)	Operating region name

Your question is Star vs Snowflake for Uber Data. Start with the requirements and the three tables in the Question tab.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem