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?
www.theseattledataguy.com/wha...
Why invest in a data warehouse?
www.theseattledataguy.com/inv...

[@crisithink9509]

You make this feel like a comfortable career

[@iamnoobmaster69]

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.

[@SeattleDataGuy]

Yes, as long as you are looking to build a DW. I once did an interview where I assumed they wanted a DW but they were looking for a transactional database. I should have asked but they should have also stopped me once they realized I was designing the wrong thing.

[@thndesmondsaid]

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

[@mktheking221]

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!

[@SeattleDataGuy]

Thank you so much!

[@christofferbjorkwall1441]

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!

[@SeattleDataGuy]

That's a really common path. I feel like data science draws in most people into data and from there we find our path. I hope the channel takes off too! Please share with your friends

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

[@SeattleDataGuy]

I am glad this helped! Good luck with your data engineering interviews!

[@SeattleDataGuy]

Hey Saurabh, Thank you so much for the comment! I am curious. I have currently pivoted my channel and I would love to hear your thoughts on the new style of videos?

[@aaroncode2634]

Hey, How will the interview go?

[@Manu-hm1em]

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

[@SeattleDataGuy]

Will do! I need to put together some more videos!

[@prakharjain9068]

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

[@SeattleDataGuy]

glad you found it helpful!

[@nickarrigo5540]

really glad I found/subbed to this channel

[@SeattleDataGuy]

I am glad you enjoyed it! Are you preparing for interviews?

[@SeattleDataGuy]

Thank you Nick! I am curious. I have currently pivoted my channel and I would love to hear your thoughts on the new style of videos?

[@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.

[@SeattleDataGuy]

Yeah I think most people try to avoid putting data into arrays or maps in data warehouses. However, Google is very fond of nesting. So it also depends who you're interviewing for.

[@anisfatima2499]

can you give more details.. i am also preparing for the interview

[@maddieschulz3537]

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!

[@SeattleDataGuy]

Good luck on the shift to DE work!

[@Retrosenescent]

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

[@jayakrishna8121]

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! 👏

[@shidharthbammani5751]

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

[@SeattleDataGuy]

Thank you! I am working on it!

[@NguyenPhuong-nk4lm]

It is very useful for a student like me. Thank you so much

[@SeattleDataGuy]

I am glad you found it helpful. Hopefully you do well on future data interviews!

[@SeattleDataGuy]

Thank you! I am curious. I have currently pivoted my channel and I would love to hear your thoughts on the new style of videos?

[@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!

[@SeattleDataGuy]

Honestly I find it kind of fun. Again I picked this up from a friend who used it to try to predict exam questions.

[@echezonaazubike8054]

thank you

[@SeattleDataGuy]

You're welcome

[@aayushdesai532]

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

[@rickyrenner1872]

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?

[@sivatejakambham7939]

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.

[@SeattleDataGuy]

I totally agree! I have some plans to go through this while going through Kimball's book..I just need to get there

[@sivatejakambham7939]

@@SeattleDataGuy I am trying to find better schema design for handling order of events through Dimensional Modeling and perform analytics. I know the limitation of SQL as it is hard to perform analytics across rows. Do you have any good resource to research on this? Thanks, buddy.

[@VinhLe-gc8ws]

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

[@kushwanthkandala8244]

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

[@SeattleDataGuy]

Alright! I am thinking about a few ideas around how to show some good examples of data engineering interview questions, especially data warehouse design. Maybe I should go interview again. So I have some real examples! I want to make sure I provide the most value as possible.

[@SeattleDataGuy]

Hey! I have done a few interview videos. Were those helpful? Or should they be more focused on questions?

[@kushwanthkandala8244]

@@SeattleDataGuy let me check other videos

[@SeattleDataGuy]

@@kushwanthkandala8244 Here is one example kzbin.info/www/bejne/oXLSf6WPf9Bpq5o

[@kushwanthkandala8244]

@@SeattleDataGuy by the way I already selected to Amazon. 😊👍🏻

[@cleversachin]

Any idea from where I will get more such design scenarios ?

[@fanyang2181]

why don't you build menu and restaurant into one dimension table so to have dimensional modeling?

[@SelmanAy]

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!

[@SeattleDataGuy]

I do need to create a more in depth version of this. Specifically focusing on SQL and data modeling. But currently I don't.

[@danielanya3276]

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...

[@sivatejakambham7939]

As we know the cardinality of comments attribute is high. It doesn't fit into its own dimension as we don't want dimension table to grow on par with its associated fact/s. If the backend ETL that feeds Data Warehouse can classify/cluster comments and have attributes defined for each cluster, then comment_type can belong to its own dimension

[@thndesmondsaid]

also we pronounce gyro very differently on the east coast, but seems like your pronunciation is closer to the way to say the food in Greek

[@kphorce]

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.

[@SeattleDataGuy]

I don't know if I would put SQL in front of python. However, in most interviews I do find that they tend to be more SQL heavy. So I would say both are worth learning well. I may start thinking about setting up consultations and I am working on a data engineering project video. Let me get some stuff set up so I can start working on really getting more involved in the community aspect.

[@kphorce]

@@SeattleDataGuy thanks for the reply. I'm definently 1)learning SQL and 2) learning python. My confusion is apply it to real world scenarios. I do have Apache Spark, pyspark and jupyter notebook installed. I am just trying to see a real world project. To get the process. I'll just work on the foundational things in both SQL an python. Then I'll beggin on working with the pyspark Manual bellow.. spark.apache.org/docs/latest/api/python/getting_started/quickstart.html

[@SeattleDataGuy]

Hey! How goes the data engineering journey?

[@kphorce]

@@SeattleDataGuy sorry for the late response still working towards the goal been under the weather the last few days

[@anardana9731]

Why not drivers and users separate ? Can you dive deeper ?

[@SeattleDataGuy]

I would need to dive deeper. What if there were more user types than just these two? Would you create a unique table for each of them? Also, what if a driver is both a user and a driver? Truth be told, some of this will depend on the features you are putting together. But it is likely all the user types could be stored in one table.

[@anardana9731]

@@SeattleDataGuy Understood. But wouldn't the schema be very different for different users ?