This is an end to end Extract, Load & Transform (ELT) project that uses data from the Zomato Restaurant, an Indian multinational restaurant aggregator and food delivery company. Founded in 2008 by Deepinder Goyal and Pankaj Chadda, the Zomato app is an online food ordering and delivery platform that helps you place a food order from the restaurant registered on Zomato, and get it delivered on your doorsteps.
The purpose of this project is to make an end to end data pipeline which:
- Extracts the Zomato data from a Kaggle dataset
- Loads this data into Snowflake data warehouse
- Transforms loaded data using dbt applying Kimbal Dimensional Modeling
- Build a Tableau report to visualize data
- Snowflake for data warehouse
- dbt for transformation and data modeling
- Tableau for data visualization
This project leveraged the Kimball Dimensional Modeling principle to construct facts and dimension tables from initial staging models within the dbt framework. By utilizing a combination of advanced techniques including aggregations, Common Table Expressions, appropriate table joins, window functions and detailed SQL logic, the raw data went through extensive transformation and modeling. The Directed Acyclic Graph (DAG) below shows the dependencies and logical flow of transformations in this pipeline, from the source tables to the four distinct data marts, each built to address a specific component of the operations within the Zomato company.
Dimension table holding data for the different dishes offered in the Zomato app and the country they originate from. It takes the cuisine column from the menu table, extracts the demonymns, then matches the respective country in the countries table. It also records the price of each dish on the app.
Dimension table holding aggregated data of popular food items used in the zomato app by their frequency of occurence, and whether they're vegetarian or not. References the food table.
Dimension table showing data on each user on the Zomato app as well as their lifetime order history. It finds the earliest & latest order date of each user that made a purchase on the orders table; then matches that user on the users table to get their demographic.
Fact table holding data for each order made on the Zomato app, and to which restaurant. It collects the order date and restaurant id from the orders table; then matches it with the restaurant table for location, ratings and review data of the particular restaurant.
The project culminated in the development of four dynamic dashboards within Tableau, each derived from the data marts built within dbt framework. These dashboards were seamlessly integrated into an interactive report hosted on Tableau Public. Through the incorporation of controls, filters, legends, and narratives, users can easily explore and analyze the insights contained within the data.
© 2024 Kingsley Izima