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


    Previous articleПоднимаем Hadoop кластер локально | Скринкасты | Ok #1
    Next articleLibreNMS | How to create free network monitoring tool using a virtual machine


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

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

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

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

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

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

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