Design a Data Warehouse for Reporting and OLAP

Project rubric:

Staging:

Students will be able to (SWBAT) load local data files into a Snowflake staging schema, using the command-line snowsql tool: 01_staging_etl_snowsql_yelp_files 02_staging_stage_files_yelp 03_staging_stage_files_yelp_checkin 04_staging_stage_files_yelp_covid_features 05_staging_stage_files_yelp_tip 06_staging_stage_files_yelp_review 07_staging_stage_files_yelp_user 08_staging_stage_files_yelp_business 001_snowsql_proof SWBAT load data files smaller than 50 MB into Snowflake using browser. 09_staging_csv_files_temp_precipitation 010_staging_csv_files_temp_precipitation_proof 011_staging_csv_files_temp_precipitation_etl

SWBAT create a data architecture diagram showing the data files to stage to ODS to DWH to reporting.

00_data_architecture

Operational Data Store:

SWBAT use snowsql to transform data from staging to ODS. 10_ods_ddl_etl 13_ods_ddl_etl 14_ods_ddl_etl

SWBAT expand and spread JSON data into individual columns. 12_ods_ddl_etl

SWBAT calculate and compare data compression between raw files, staging, and ODS. 02_staging_stage_files_yelp 010_staging_csv_files_temp_precipitation_proof 17_ods_table_sizes 18_staging_table_sizes

SWBAT reverse engineer data sets into an entity relationship model. 15_ods_ER

SWBAT integrate climate and Yelp data sets by identifying a common data field. 19_yelp_climate_common_field_integration

Data Warehouse:

SWBAT design a star schema with dimensions and fact tables. 21_dwh_star_schema

SWBAT extract, transform and load from ODS model to DWH model. 20_dwh_ddl_etl

SWBAT write SQL queries to generate a correlation report between climate data and Yelp data. 22_dwh_query A23_dwh_query

Finally, the DataWarehouse can be used to do analytics on the business data.