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)
* Drop Off Locations
* Persons(Customers and drivers, since a driver in the future might be a customer and visa versa)
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?
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
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.
Great video! Would love if you uploaded a mock interview like this that's like 45 minutes in length or so
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!
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.
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!
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
Any idea from where I will get more such design scenarios ?
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!
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!
This video is so useful . Thank you and please create more such vidoes with real life examples over data science topics.
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!
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.
really glad I found/subbed to this channel
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?
Why not drivers and users separate ? Can you dive deeper ?
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 🙂
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.
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.
It is very useful for a student like me. Thank you so much <3
Hey Ben this is amazing. Please can you do more videos on the Data warehouse interview series for DE interviews?
Hi Ben, This was very helpful.. hit us with some more of these design problems!
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…
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