Design a Data Warehouse Schema for a Ride-Sharing Service

Here, we outline the solution and provide guidance on how a candidate should tackle the problem.

Identifying the requirements

The first step is to clarify the requirements by asking targeted questions. This ensures a clear understanding of the problem and its context. Key questions a candidate should ask include:

1. What are the use cases we need to design data models for? Understanding the purpose of the models helps align the design with business objectives.
2. What is the business impact of these use cases? This provides insight into the priorities and expectations for the data model.
3. Who are the end users? Knowing the users helps tailor the solution to meet their specific needs.

For example, let’s consider the use cases shared by the interviewer:

Given the use cases outlined above, it is clear that the interviewer expects the candidate to design a data model using data warehouse schemas. This approach is well-suited for handling large datasets and supporting complex analytical queries.

Defining entities

After clarifying the requirements, the candidate should identify the key entities involved in the system. In this case, the entities include:

1. Driver
2. User
3. Trips
4. Vehicle
5. Partner
6. Payment
7. Location
8. Ratings

Data model design

For a data warehouse, candidates should focus on identifying dimensions and facts to structure the data model accordingly. The solution should prioritize optimizing data retrieval for analytical queries, as ETL compute optimizations are not part of the data model design scope.

Key considerations

When designing the data model, candidates should keep the following factors in mind:

1. Anticipate Future Use Cases. Design with scalability in mind. For example, if the current requirement is to track activation rates, anticipate that the business may later require metrics like inactivation or reactivation rates. The solution should be flexible enough to accommodate such future needs.
2. Trends Analysis. When an interviewer mentions “trends,” they are typically referring to the analysis of data over a period of time. Ensure the data model supports time-series analysis effectively.
3. Think Out Loud. Explain your reasoning while choosing one data model approach over another. Share your thought process with the interviewer, weighing different options and justifying the selection of the most optimized solution. This demonstrates both technical knowledge and problem-solving skills.

Dimensions

The following entities should be modeled as dimensions:

Driver

The Driver dimension is a Type 1 dimension that contains all attributes related to drivers. This table captures point-in-time data for each driver, enabling analysis of key metrics such as the driver churn rate at various stages and the driver activation rate. By maintaining a historical view of driver attributes, this dimension supports detailed operational and analytical insights into the driver lifecycle.

User

A User is defined as a person who interacts with the ride-sharing app to book a cab, either directly or after signing up on the platform. This dimension contains all attributes related to the user.

Vehicle

The Vehicle dimension includes all attributes related to the vehicles used on the platform.

Partner

The Partner dimension contains attributes related to partners who either own the fleet (vehicles provided to the drivers) or directly deploy the drivers themselves.

Location

The Location dimension supports geographical trend analysis. It enables insights into:

• Locations with higher pickup and drop-off trends
• Average trip times from popular locations
• Areas with higher ride requests where drivers could be incentivized to operate (e.g., airports).

This analysis helps the business optimize operations by understanding what, when, and where ride requests are higher.

Offer

The Offer dimension captures all details related to trip offers. When a rider requests a trip, an offer is generated. If the driver accepts, the offer is converted into a trip.

Rating

The Rating dimension is a Type 1 table designed to analyze user and driver satisfaction rates. By storing both user and driver ratings in the same table, this dimension creates a unified view of feedback for each trip. This structure simplifies data management and enhances analytical capabilities, enabling the evaluation of satisfaction trends and improving overall service quality.

Facts

The following entities should be modeled as facts:

Trips

The Trips fact table contains all information related to a trip, starting from when the driver picks up the rider and the trip is initiated. This table does not include information about rides that were declined by the driver at the request stage.

Payment

The Payment fact table captures all payment information made by the user for a trip. It tracks the financial transactions associated with each ride.

Query analysis

Here the interviewer may ask follow-up questions such as Given the use cases above, write queries for each of the use cases mentioned above using the data model candidate has created.

Following are some of the queries which can be created based on the data model above:

Query 1

Use case: Track the driver's conversion rate at each stage of the onboarding process (signup to activation).

SQL
select 
  DATE_TRUNC('{{time_window}}', signup_timestamp) as        signup_week
  , count(distinct case when signup_timestamp is not null then driver_uuid end) signups
  , count(distinct case when DATE_DIFF('day',signup_timestamp, first_document_uploaded_timestamp) <= 14  then driver_uuid end) first_docs_uploads 
  , count(distinct case when DATE_DIFF('day', signup_timestamp_utc, first_documents_approved_timestamp) <= 14  then driver_uuid end) all_docs_approved 
  , count(distinct case when DATE_DIFF('day', signup_timestamp, bgc_approved_timestamp) <= 14  then earner_uuid end) bgc_pass
from driver  
group by 1,2

Query 2

Use case: Analyze user trip data: trip requests vs. completions.

SQL
select 
  offer.user_uuid,
  count(case when offer.status=’rejected’ then ) as rejected_offer,
  count(case when trip.status=’completed’) as completed_trip,
from offer
left join trip on offer.trip_uuid=trip.trip_uuid 
left join user  on user.user_uuid=offer.user_uuid
Where user.status=’active’
group by 1