Data Modeling For Interviews

ER diagrams for life!

Introduction

In my post on the structure of the normal data engineering interview, I mentioned that SQL is almost always going to pop up at some point. Onsite interviews might go a step further and do a data modeling scenario followed by SQL on the data model you just created. How to best be prepared for this?

You’ll see a lot of people mention The Data Warehouse Toolkit by Kimball. It’s basically the DE equivalent of Cracking The Coding Interview. Yes, this is a good resource if you want to be an expert on the subject, but you don’t have to be an expert to make your way through this portion of an interview (if you listen closely, that’s the sound of me getting blacklisted from any future interview). Your SQL normalization basics (and no, memorizing 3NF, BCF, and all those acronyms won’t be needed either) should be good enough.

Let’s tackle this head-on from a scenario that I actually have had (from my Meta interview): design a data model for the key entities involved in an application like Uber.

Brainstorming

I shouldn’t have to say this, but one of the worst things you can do in an interview is attempt to dive right into the answer. Brainstorming and asking questions so that you’re better prepared when you start to answer is the way to go. After all, you don’t dive right into designing a pipeline when approached with a new requirement.

What are the key entities that would be involved in something like Uber? At its core, Uber is a ride sharing service where users request a ride from point A to point B. A driver, using their own vehicle, takes the user from start to finish, after which they receive the fare, a rating, and possibly a tip.

Based on the paragraph above, I’ve identified some areas where we can get started: users, vehicles, and rides. This gives something to start with.

Users

Right away, one of the few caveats you’ll face in designing something like Uber comes up. Users of the app can be passengers, drivers, or even both. How can we distinguish between the two? Note that not all the same attributes will be useful for the case of a passenger (credit cards, passenger rating) as well as a driver (bank account details, driver rating, vehicle info).

In this case, it might make sense to design the user structure as something like this:

User
    User_id
    Name
Passenger
    User_id
    Passenger_rating
Driver
    User_id
    Vehicle_id
    Driver_rating
Payment
    User_id
    Payment_id
    Payment_type (credit card, PayPal, etc.)
    Payment_info

The payment table should be good enough to handle passenger payments vs. Uber paying drivers. By specifying the user ID, payment type, and its info, you can easily distinguish between the two. This table would have a composite key (user_id, payment_id).

Vehicles

This could be stored in the Passenger table, but it probably makes more sense to isolate it. Vehicle information could be represented as the following:

Vehicle
    Vehicle_id
    Year
    Make
    Model
    Color

One thing to consider with this entity is how to handle the situation of a driver having multiple vehicles. After all, Uber has requirements on cars being from at least a certain year, so a full-time driver might need to buy a new car over time to stay active. This means that in the Driver table above, we’d need to have multiple rows for the same user based on how many vehicles they’ve listed in the app.

Get Matt Weingarten’s stories in your inbox

Join Medium for free to get updates from this writer.

Remember me for faster sign in

The best way to normalize and handle this would be to move the vehicle_id field out of the Driver table and instead add the User_id field to the Vehicle table. We can then additionally add a is_current_vehicle flag to the table to distinguish between the current car and what is history. To summarize, this is what we have so far:

User
    User_id
    Name
Passenger
    User_id
    Passenger_rating
Driver
    User_id
    Driver_rating
Payment
    User_id
    Payment_id
    Payment_type (credit card, PayPal, etc.)
    Payment_info
Vehicle
    Vehicle_id
    User_id
    Is_current_vehicle
    Year
    Make
    Model
    Color

Rides

What information do we need to store for a ride? The critical attributes to consider are starting location, ending location, passenger_id, driver_id, price, rating (for passenger and driver), etc. Representationally, it can look like this:

Ride
    Ride_id
    Passenger_id
    Driver_id
    Vehicle_id
    Passenger_total
    Starting_location (GPS coordinates?)
    Ending_location (GPS coordinates?)
    Starting_time
    Ending_time
    Surge_factor
    Fare
    Tip
    Passenger_payment_id
    Driver_payment_id
    Passenger_rating
    Driver_rating
    Passenger_comments
    Driver_comments

This is a basic example, but it should cover every aspect of a ride (where and when it started, where and when it ended, how much it cost, who was involved, etc.). Things like time and length would be derived attributes. Putting every together, this is how we’re “modeling” Uber:

User
    User_id
    Name
Passenger
    User_id
    Passenger_rating
Driver
    User_id
    Driver_rating
Payment
    User_id
    Payment_id
    Payment_type (credit card, PayPal, etc.)
    Payment_info
Vehicle
    Vehicle_id
    User_id
    Is_current_vehicle
    Year
    Make
    Model
    Color
Ride
    Ride_id
    Passenger_id
    Driver_id
    Vehicle_id
    Passenger_total
    Starting_location (GPS coordinates?)
    Ending_location (GPS coordinates?)
    Starting_time
    Ending_time
    Surge_factor
    Fare
    Tip
    Passenger_payment_id
    Driver_payment_id
    Passenger_rating
    Driver_rating
    Passenger_comments
    Driver_comments

SQL Scenarios

Great, we have a model in place for the scenario we’ve been asked. Can we use our knowledge of SQL to do that? Some questions you might answer based on the model above are:

• How many miles did driver X complete in April?
• What was passenger Y’s average rating for all rides in March?
• How many rides were over an hour long on New Year’s Eve?

Obviously, there’s a lot (and my examples only scratch the surface of what to expect), but a good data model should be able to answer these questions with the SQL knowledge you possess. If it’s too complicated, that’s likely an inefficiency in the model itself.

Conclusion

I do want to point out that my model here was a lot more detailed than the one I came up with during the interview. After all, it’s easy to do this in retrospect than while on the clock. Therefore, don’t worry in an interview if your model isn’t perfect. If you nail the SQL portion (as well as do fine on the other portions of an interview(s)), you should be in good shape.

Best of luck!

Go from SELECT * to interview-worthy project. Get our free 5-page guide.