Preparing For A Data Engineering Interview: How To Design A Data Warehouse For.A Food Delivery App



Data warehouses (in all their forms and iterations) have become the backbone of almost every organizations analytics, data science and BI departments.

Over the years they have evolved and taken on new shapes as modern infrastructure has been developed to better manage analytical queries and workloads.

In our last few articles and videos we discussed why data warehouses are used as well as a high level for what they are used for.

Now we wanted to provide more depth to a data warehouse.

Beyond just talking about the high level reasons why you might want to incorporate a data warehouse into your infrastructure. We wanted to discuss the key design components.

A common interview question for data engineers and BI developers is to develop a data warehouse. Personally, I have been asked to design a parking lot data warehouse, a college courses data warehouse and several others for interviews.

I suggest that most people watch a few videos on the topic of data modeling as well as read up on Kimball’s data warehouse design book. After that, then you should think about a few workflows you might enjoy and practice modeling them.

For example, let’s walk through designing a data warehouse for a food delivery app.
How would you approach this design?

Generally, a good way to start is to list out the entities you would consider being part of a food delivery app.

For example here is a list:
* Menu Items(And possibly add-ons)
* Restaurants
* Drop Off Locations 
* Cars
* Persons(Customers and drivers, since a driver in the future might be a customer and visa versa)
* Orders

This would be a good high level set of entities to start with. Especially in an interview. You don’t want to focus on every possible issue and detail. 

I would list these out and prod the interviewer to see if this is all the entities they were concerned with. Often times interviewers have a specific set of questions they want to ask. So if you don’t include all the entities, they may ask about different parts of the workflow you might have forgotten.
From here, the dimensions it may be obvious. You have menu items, restaurants, drop off locations, cars and persons.

These are all dimensional items because they represent entities that don’t change often, don’t contain measurable data and can be used to pivot and break down your future reports.

If you want to learn more, then watch the rest of the video! Or consider watching some of our other content.

What is a data warehouse and why build it?

What Is A Data Warehouse And Why We Build Them – A Video

Why invest in a data warehouse?

Why Invest In A Data Warehouse



source

25 thoughts on “Preparing For A Data Engineering Interview: How To Design A Data Warehouse For.A Food Delivery App”
  1. Hi, Great Video! Do you have a playlist that focuses on data modeling use cases? It is mentioned in the video that you will come up with few more examples but I am not finding anything

  2. At an amazon interview, I was asked a similar question.
    The one point they wanted me to consider is that the restaurant gives combo food for which I couldn't answer properly. I went with a array of prod items and use the explode method but the better way was to keep a separate table and join for combo items.

  3. Great video! Would love if you uploaded a mock interview like this that's like 45 minutes in length or so

  4. This video is pure gold. I was recommended to apply for a junior data engineer role by one of my HR people, being a rotational IT intern transitioning to full time. I have college training in SQL and about 2 semesters in database concepts. Not sure if it's a total fit, but I'm interested and willing to put in the work if they think I have potential!

  5. A lot of the modeling issues here would be solved by not thinking of doordash et al as restaurant services but really as delivery services. They're not really in the business of worrying about what restaurants are selling but how to make sure people order through them and not directly from the restaurant and that only happens because of convenience. I would have designed this thinking about the drivers, their schedules, their trip length, coverage areas etc and worried about what they were actually delivering later. I had a similar design question in an interview before and that's the route I went and was able to answer all the questions they had with my model never having given any thought to what was being delivered specifically.

  6. I am preparing for a Data Engineer interview and I think this video really help put me into the head of a data engineer. I really like that you suggest we should think about the questions that will be asked during the design process. Thanks!

  7. Is there a series with more complex data modeling for the food delivery app in addition to this video? Please leave link if there is, very interested. Thanks

  8. This video is an amazing resource! Can't wait for more similar videos for interview prep. Thanks Ben! I hope you know the videos you post are very insightful, well thought out, and help many of us prepare. Keep killing it with these videos!

  9. You have an excellent way of conceptualizing Data Engineering. After having studied Data Science for a while I am currently leaning into Data Engineering and your videos are truly helpful to get that much needed overview. I hope your channel will take off!

  10. This video is so useful . Thank you and please create more such vidoes with real life examples over data science topics.

  11. Hi Ben, that's so great content, I'm preparing my onsite amazon interview and this was so helpful but do you have an example video where you deep a bit more into it? like defining tables, writing sql queries based on the model/questions you have, so more like interview process. Thanks again!

  12. Thank you Ben! it was really good. My takeaway is that we build the entity-relationship model based on the questions we anticipate from the customer side, but always start with the fact table and move out.

  13. Really useful video !

    How would this be modelled if an order contained several menu items? Is it ever possible to have multiple rows of a dimension table referring to one row in a fact table, as in can we have a many to one relationship between a dimension table and a fact?

  14. I am preparing for a Data Engineering role right now, and I have a few interviews lined up. This video is really helpful as I can now think about different questions that I can expect, and the direction I should think in. This is amazing. thank you 🙂

  15. Is this interview questions for a junior data engineer role. I also watch more of the course on udemy you recommended, the concepts are dope. 1) Would you say practicing SQL is the most important language to learn? 2) would then be python?

    Also, do you do any cosoltation in terms of phone calls/zoom chats? I already have pyspark on my computer and am using jupyter notebook. I am still learning SQL(mysql). However, I would like to start extracting, transforming and loading data. I'm trying to find a course on real world projects. There is only so many ways one can do and ETL or ELT. I am just looking for practical real world projects. I just want to dive into data engineering. I Need some guidance.

  16. Great Video. Another alternative mechanism to associate items with Restaurants, Restaurants with its Restaurant Chain is to define a hierarchal attributes(if the hierarchy length is fixed and many to one relationship holds) inside item dimension.
    If we want to have same natural key for item across restaurants, Another Key design suggestion would be to implement item-restaurant bridge table between item and restaurant dimensions.

  17. Hey Ben this is amazing. Please can you do more videos on the Data warehouse interview series for DE interviews?

  18. Would you consider a "Facebook comments" table a dimension or fact table? I feel like a "comment" is a noun/entity/dimension but it could also be thought of as a verb/action/fact…

  19. Great tutorial. I am currently studying in preparation for interviewing for Data Engineering roles. There isn't a lot of content that focuses on data engineering primarily. But this channel has good content on data engineering topics coming out regularly. For anyone studying for data engineering interviews here is a study guide I found online, but I have added more points (such as Spark) It may be of help. You will have to copy it and then you can make your own changes to it. https://docs.google.com/spreadsheets/d/1y4KnEjdeF8aBMJe06VMp_ipkQmBz7Pno1uUM0mPUNqw/edit?usp=sharing

Leave a Reply

Your email address will not be published.

Captcha loading...