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?

Why invest in a data warehouse?

[iamnoobmaster]

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.

[MrSaurabhtheking]

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 :)

[christofferbjorkwall]

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!

[SidharthanPV]

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.

[thndesmondsaid]

Thank you!! Have been looking all over for data engineering system design interview content.

[maddieschulz]

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!

[mktheking]

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!

[LMGaming]

3 years later, still very interesting..

[farPositive]

Hi Ben, This was very helpful.. hit us with some more of these design problems!

[crazyphantom]

You make this feel like a comfortable career

[prakharjain]

Really Helpful!! Great examples of questions you have put. Thank you!!

[sunnyyshc]

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!

[jayakrishna]

Awesome content bro, I have been watching your videos and learning to an extent, I am a pure beginner. Thanks for this channel. My suggestion is I think viewers will be more comfortable if you slow down your explanation and they will be more focused and focused on the content and understand better. Its my suggestion but you are doing great! 👏

[nickarrigo]

really glad I found/subbed to this channel

[VinhLe-gcws]

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

[rickyrenner]

This is awesome - I really appreciate this, and discussing the nuances of a real life scenario. I get frustrated with the lack of depth in some example DWs online. Does anyone know of any places to practice "real life" data warehousing scenarios online?

[sivatejakambham]

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.

[Retrosenescent]

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

[shidharthbammani]

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

[kushwanthkandala]

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