DDL(DataDefinitionLanguage).sql: SQL script for creating the schema and tables in the Snowflake data warehouse.DML(DataManipulationLanguage).sql: SQL script for the ETL process to transform the data from the original tables to the target tables in the data model.
- Load the Sakila dataset into Snowflake data warehouse using the
DDL(DataDefinitionLanguage).sqlscript. - Run the ETL process using the
DML(DataManipulationLanguage).sqlscript to transform the data from the original tables to the target tables in the data model. - Query the data model using SQL statements to retrieve the information needed for the business requirements.
This project is about creating a data model for the Sakila database of an online DVD store, to meet the following business requirements:
- List the total revenue of each store everyday.
- List the total revenue of totally everyday.
- List the top store according to their weekly revenue every week.
- List top sales clerk who have the most sales each day/week/month.
- Which film is the top film each week/month in each store/totally?
- Who are our top 10 customers each month/year?
- Is there any store the sales is in a decline trend (within the recent 4 weeks the avg sales of each week is declining)
- Load original data: Load the Sakila dataset into Snowflake data warehouse using a script.
- Analyze the business requirements and translate them into technical requirements.
- Consider the grain of the data model and the atomic row of a fact table.
- Decide what dimension tables to use in the data model and what columns to include in those dimensions.
- Define the fact table and determine what columns to include.
- Create the dimension and fact tables in Snowflake data warehouse, making sure the data model is in a different schema.
- Write ETL script to transform data from the original tables to the target tables in the data model.