# Design a Data Warehouse Schema for a Ride-Sharing Service

Aced (formerly Exponent) publishes a public search API over its indexed pages; the guide is at https://www.aced.io/agents/agent-access.

Source: https://www.aced.io/questions/4586/ride-sharing-data-model
Source observed: 2026-11-04T23:47:43.865Z
Last checked: 2026-11-04T23:47:43.865+00:00
Indexed: 2026-11-05T19:27:07.499+00:00

---

# Design a Data Warehouse Schema for a Ride-Sharing Service

We provide this Aced (formerly Exponent) data-engineering mock-interview question for practicing data modeling. The task is to design a data warehouse schema for a ride-sharing service, including entities such as drivers, users, trips, and payments, while considering query efficiency and performance.

- **Role:** [Data Engineer](https://www.aced.io/questions?role=data-engineer)
- **Category:** [Data Modeling](https://www.aced.io/questions?type=data-modeling)
- **Interview listing:** 1 interview; the interface labels it “Reported last asked: a year ago.”
- **Community answers:** 3

## Member access

The video answer is available through membership. Membership includes:

- 100+ expert answer videos
- Access to all courses
- Peer-to-peer practice
- Private Discord community
- Full question database

[Upgrade](https://www.aced.io/upgrade?src=questions) or [upgrade now](https://www.aced.io/upgrade?src=questions).

## Community answers

### Nilanjan D. — December 6, 2024

[Nilanjan D.](https://www.aced.io/users/751a40f3-c1f9-4dde-9f58-dcf103bec6c9) — [617 karma](https://www.aced.io/karma)

Nilanjan suggests the following data-model improvements:

1. Separate `Driver` and `Approval` into different tables.
2. Model `Approval` and `Document` with a tuple of `user_id` and `document_id`. Add rejection comments identifying the document that triggered rejection. This preserves the approval-workflow history—initiation, pending, approval, or rejection—and supports analysis such as average approval time and documents associated with rejection.
3. Add vehicle type—small, medium, or large—and permissible capacity, meaning the maximum number of passengers. This can help identify overloading by checking trip status for a specific `(driver_id, vehicle_id)` tuple and support analysis of overloading trends and frequency.
4. Store a vehicle registration number. Nilanjan proposes querying it against a Motor Vehicle Department database to obtain vehicle status, including outdated documents, pollution-check status, or a criminal case, and to analyze trends and the percentage of non-confirming or ineligible vehicles.
5. Include these fields in `Trip`: `trip_id`, `start_time`, `end_time`, `total_fare_collected`, and `incentives`.
6. Add source and destination coordinates to `Offer`. Add `start_time` and `end_time` only when the offer status is accepted, and add `payment_id` only when the status is accepted.
7. Use `Trip` and `Offer` as a join table to analyze covered coordinates, rejection rate, coordinates covered during a trip, and payments for each participating user, including possible overcharging. Suggested fields include `payment_id`, `driver_rating` as a foreign key to a rating entity, and `rider_comment` as a foreign key to a rating entity.
8. Define `Rating` with `rating_id` as the primary key, `rating_dimension` on a 0-to-5 scale, and `comments`.

### Venkata sai krishna A. — July 14, 2025

[Venkata sai krishna A.](https://www.aced.io/users/8a153570-bb6d-4141-8ed7-94c72d8d1df2) — [242 karma](https://www.aced.io/karma)

Venkata sai krishna A. agrees with structuring the process around the problem, metrics, facts, dimensions, and queries, but questions treating `driver` as a dimension because driver onboarding is a process involving document uploads, verification, and approval.

For monitoring driver-onboarding efficiency—specifically, identifying which steps take the longest on average—the suggested design is:

- A `driver_details` dimension table containing a driver's personal information.
- A `driver_onboarding` accumulating fact table recording onboarding events such as document-upload time.
- The driver's `is_active` flag maintained as a Type 2 slowly changing dimension in `driver_details`.
- Contact-detail changes handled as either SCD2 or SCD1, depending on the desired history.

The commenter asks for this point of view to be verified.

### Anonymous Alpaca — August 2, 2025

[Anonymous Alpaca](https://www.aced.io/users/e4b0ef5d-71aa-469f-8895-929abf0c6d0c) — [223 karma](https://www.aced.io/karma)

The commenter observes that the current structure puts `trip_uuid` in the `offer` table, although a trip is born from an offer and is therefore dependent on it. The suggested revision is to remove `trip_uuid` from `offer` and add `offer_uuid` as a foreign key in the trip fact table.

## Related questions

- [Design a database schema for a ride sharing app.](https://www.aced.io/questions/5545/design-data-schema-ride-sharing)
- [Design a data warehouse schema for Amazon.](https://www.aced.io/questions/4943/design-data-schema-amazon)
- [Design a Data Warehouse Schema for Airbnb](https://www.aced.io/questions/4588/airbnb-data-model)

## Related courses

### [Data Engineering Interview Prep](https://www.aced.io/courses/data-engineering)

![Data Engineering Interview Prep](https://images.ctfassets.net/x78yjrjc11pq/5XoOCgXlGqN2fa1zaroNWd/9a2e23df48a212bc00d352873feb2d6a/Data_Engineer_Interview_Prep_Hero_Image.jpg?w=800&h=800&fm=webp&q=75)

We cover mock interviews, frameworks, and advice from senior candidates for data engineering roles. Practice data modeling, pipeline design, SQL, coding, and behavioral interviews. The course description names Meta, Google, Amazon, Microsoft, Walmart, and DoorDash among target companies.

### [Behavioral Interviews: A Practical Guide for Engineers](https://www.aced.io/courses/eng-behavioral)

![Behavioral Interviews: A Practical Guide for Engineers](https://images.ctfassets.net/x78yjrjc11pq/47qcoHjzFZXE37GoacedkB/9d8e3f4910d8b06b21e76745cd9cbff3/AdobeStock_1142146335.jpeg?w=800&h=800&fm=webp&q=75)

We provide preparation for senior-plus behavioral interviews at FAANG and Tier-1 companies. The course is made with staff and principal engineers from Meta, Netflix, Google, and more, and covers avoiding downleveling, elevating past projects, and communicating naturally.

## Submit an answer

Use the answer editor to submit a response. It supports bold, italics, bulleted lists, numbered lists, quotes, inline code, and code blocks. Submitting marks the question complete.

---

[Read this page’s summary](https://www.aced.io/agents/api/public/v1/page-tldr?url=https%3A%2F%2Fwww.aced.io%2Fquestions%2F4586%2Fride-sharing-data-model&trace=t_679ff9ab98f3)

## Ask a follow-up about this page

Need a more specific answer? Ask the exact question you have. It returns a fresh, cited answer from Aced (formerly Exponent)’s knowledge base. No account or API key required.

Open this URL with your own question, URL-encoded. Include your question in q. All research inputs are optional; no account or setup.

`GET https://www.aced.io/agents/agent/faq?q={question}&trace=t_679ff9ab98f3`

[Tool guide and examples](https://www.aced.io/agents/agent-access) · [OpenAPI](https://www.aced.io/agents/api/public/v1/openapi.json) · [Page TLDR form](https://www.aced.io/agents/agent-tools/page-tldr?url=https%3A%2F%2Fwww.aced.io%2Fquestions%2F4586%2Fride-sharing-data-model). Opening a form does not execute a tool.