Design a Data Warehouse for Reporting and OLAP
Budget: ₹600 – ₹1,500 INR
In this project, You need to build a complete data pipeline in Snowflake that connects Yelp review data with climate data to analyze how weather impacts customer reviews of restaurants, specifically in Las Vegas.
The project involves three major stages: Staging → ODS (Operational Data Store) → DWH (Data Warehouse) → Reporting.
What You Need To Do
1. Data Collection & Staging
Download and load 6 Yelp JSON files (Business, Review, Tips, Check-in, Covid, User/Customer) into the staging schema in Snowflake.
Also load 2 CSV files for climate data (Temperature and Precipitation for Las Vegas).
Take screenshots after uploading all 8 files, showing the tables in staging.
2. Build the Operational Data Store (ODS)
Design an ER diagram showing how the Yelp and climate tables relate to each other (e.g., one-to-many between Business and Reviews, etc.).
Write SQL queries to transform raw staging data into clean, structured tables in the ODS.
Use Snowflake JSON functions to extract values from nested JSON fields into columns.
Take screenshots showing successful execution of these queries and resulting ODS tables.
Also capture and compare row counts or sizes for raw, staging, and ODS data to show compression.
Integrate Yelp and climate data by aligning them on dates, and matching climate conditions with review timestamps.
3. Build the Data Warehouse (DWH)
Create a star schema with a central fact table (like Fact_Reviews) and several dimension tables (Business, Customer, Date, Weather).
Write SQL ETL queries to move data from ODS to DWH.
Show screenshots proving that data was loaded successfully into the DWH.
4. Reporting
Write a final SQL query to generate a report that includes:
Business Name
Temperature
Precipitation
Review Ratings
Take a screenshot of the output showing this correlation report.
Final Deliverable
One Google Doc based on the provided template
It will include:
All screenshots
All SQL queries
ER diagram and star schema diagram
Final report output
The focus is not just on loading data, but also on cleaning, transforming, modeling, and reporting insights that connect customer reviews with local weather conditions.
The project involves three major stages: Staging → ODS (Operational Data Store) → DWH (Data Warehouse) → Reporting.
What You Need To Do
1. Data Collection & Staging
Download and load 6 Yelp JSON files (Business, Review, Tips, Check-in, Covid, User/Customer) into the staging schema in Snowflake.
Also load 2 CSV files for climate data (Temperature and Precipitation for Las Vegas).
Take screenshots after uploading all 8 files, showing the tables in staging.
2. Build the Operational Data Store (ODS)
Design an ER diagram showing how the Yelp and climate tables relate to each other (e.g., one-to-many between Business and Reviews, etc.).
Write SQL queries to transform raw staging data into clean, structured tables in the ODS.
Use Snowflake JSON functions to extract values from nested JSON fields into columns.
Take screenshots showing successful execution of these queries and resulting ODS tables.
Also capture and compare row counts or sizes for raw, staging, and ODS data to show compression.
Integrate Yelp and climate data by aligning them on dates, and matching climate conditions with review timestamps.
3. Build the Data Warehouse (DWH)
Create a star schema with a central fact table (like Fact_Reviews) and several dimension tables (Business, Customer, Date, Weather).
Write SQL ETL queries to move data from ODS to DWH.
Show screenshots proving that data was loaded successfully into the DWH.
4. Reporting
Write a final SQL query to generate a report that includes:
Business Name
Temperature
Precipitation
Review Ratings
Take a screenshot of the output showing this correlation report.
Final Deliverable
One Google Doc based on the provided template
It will include:
All screenshots
All SQL queries
ER diagram and star schema diagram
Final report output
The focus is not just on loading data, but also on cleaning, transforming, modeling, and reporting insights that connect customer reviews with local weather conditions.
Related categories:
Python
SQL
Oracle
Database Administration
Database Programming
Data Analysis
ETL
Database Design
Data Modeling
Snowflake