Develop ELT and Staging in Snowflake -- 3
Budget: $10 – $70 USD
create the necessary dimension tables (see Model Answer Key) and load them from the staging tables that you created last week. Remember, you are learning how to become a master builder so you want to take time to think about the steps you need to take to develop the dimension tables for your class project.
Use the following questions to think critically about how to add your dimension tables and load them. Think back to the work you did in the IT session.
Which table will I create first?
How do I find the information I need to load?
How do I Create and Delete tables?
How do I Insert the data into dimension tables?
How do I add unknown members?
First, explore creating a single table such as Dim_Location, loading it, and dropping it by hand. You can use the Snowflake interface or Visual Studio code. You have been shown both ways.
Remember to save your code somewhere safe! I prefer GitHub but putting it on a flash drive or a cloud drive works too.
SQL commands needed for this assignment:
Create Table
Drop Table
SELECT
INSERT
Additionally, here is code for your first table Dim_Date (https://cdn.inst-fs-iad-prod.inscloudgate.net/62e7d6ed-2cbe-4180-b83e-6393445f6d8d/load_dim_date.sql?token=eyJhbGciOiJIUzUxMiIsInR5cCI6IkpXVCIsImtpZCI6ImNkbiJ9.eyJyZXNvdXJjZSI6Ii82MmU3ZDZlZC0yY2JlLTQxODAtYjgzZS02MzkzNDQ1ZjZkOGQvbG9hZF9kaW1fZGF0ZS5zcWwiLCJ0ZW5hbnQiOiJjYW52YXMiLCJ1c2VyX2lkIjoiMTAwMDAwMDA0MTI0ODE4IiwiaWF0IjoxNjU4ODAxMzExLCJleHAiOjE2NTg4ODc3MTF9.xk5GAmWbH5MK4GhQN8LQ4slTyK2DPAM5lZIhWvx1b2PnN0ZtwNNYqP8pKkqXyX7Kba1YvbElC4WNfMd7OzT32A&content_type=text%2Fx-sql) . All you have to do is copy/paste the code into the Snowflake web interface and run it.
If you have any questions, remember to reach out to your peers, reader grader, instructor, and consult the materials provided to you. Everything you need is here in this module.
Requirements:
Data Warehouse is shared with the instructor and reader-grader.
Code is available in worksheet view.
Dimensions conform to the project dimension model answer key.
All data is loaded via script and not by interface.
Errors are handled.
Transformation of data occurs in the code; it does not occur in the spreadsheet.
Use the following questions to think critically about how to add your dimension tables and load them. Think back to the work you did in the IT session.
Which table will I create first?
How do I find the information I need to load?
How do I Create and Delete tables?
How do I Insert the data into dimension tables?
How do I add unknown members?
First, explore creating a single table such as Dim_Location, loading it, and dropping it by hand. You can use the Snowflake interface or Visual Studio code. You have been shown both ways.
Remember to save your code somewhere safe! I prefer GitHub but putting it on a flash drive or a cloud drive works too.
SQL commands needed for this assignment:
Create Table
Drop Table
SELECT
INSERT
Additionally, here is code for your first table Dim_Date (https://cdn.inst-fs-iad-prod.inscloudgate.net/62e7d6ed-2cbe-4180-b83e-6393445f6d8d/load_dim_date.sql?token=eyJhbGciOiJIUzUxMiIsInR5cCI6IkpXVCIsImtpZCI6ImNkbiJ9.eyJyZXNvdXJjZSI6Ii82MmU3ZDZlZC0yY2JlLTQxODAtYjgzZS02MzkzNDQ1ZjZkOGQvbG9hZF9kaW1fZGF0ZS5zcWwiLCJ0ZW5hbnQiOiJjYW52YXMiLCJ1c2VyX2lkIjoiMTAwMDAwMDA0MTI0ODE4IiwiaWF0IjoxNjU4ODAxMzExLCJleHAiOjE2NTg4ODc3MTF9.xk5GAmWbH5MK4GhQN8LQ4slTyK2DPAM5lZIhWvx1b2PnN0ZtwNNYqP8pKkqXyX7Kba1YvbElC4WNfMd7OzT32A&content_type=text%2Fx-sql) . All you have to do is copy/paste the code into the Snowflake web interface and run it.
If you have any questions, remember to reach out to your peers, reader grader, instructor, and consult the materials provided to you. Everything you need is here in this module.
Requirements:
Data Warehouse is shared with the instructor and reader-grader.
Code is available in worksheet view.
Dimensions conform to the project dimension model answer key.
All data is loaded via script and not by interface.
Errors are handled.
Transformation of data occurs in the code; it does not occur in the spreadsheet.