This project was developed as a solution for the Analytics Engineer Certification challenge from Indicium. The objective was to build a data warehouse and a dashboard for the sales area of Adventure Works, a bicycle manufacturer, following Modern Data Stack project practices. The project used the ELT approach, in which the raw data extracted and loaded into a "raw" schema in Snowflake was transformed in dbt, creating a dimensional model after the "staging", "intermediate" and "marts" layers. After the last layer, the data warehouse was finalized, with the fact and dimension tables created in the data warehouse schema in Snowflake. Finally, Power BI was connected to the data warehouse for the development of the sales area dashboard, meeting the business needs of Adventure Works.
The project used public data from the Adventure Works transactional database, which contains 68 tables distributed across 5 schemas: HR, Sales, Production, and Purchasing. The complete database diagram can be viewed below.
All tables in the database were previously loaded into a "raw" schema in Snowflake, the platform used to host the data warehouse in the cloud.
To develop a project using Adventure Works data, the data can be obtained from the GitHub repository: https://github.com/microsoft/sql-server-samples/tree/master/samples/databases/adventure-works.
The data warehouse was built following the entity-relationship diagram developed for this project, based on a Star Schema. Five dimensions were created: "dim_customers," "dim_products," "dim_sellers," "dim_address," and "dim_dates," and one fact table: "fact_orders_details."
To transform the data and create the data warehouse, dbt was used in conjunction with Snowflake. The data went through three transformation layers: "staging," "intermediate," and "marts."
Initially, the necessary tables with the raw data were cleaned in the "staging" layer. Then, they were transformed in the "intermediate" layer, where the dimensions were consolidated, and the business metrics of the fact table were created. Finally, in the "marts" layer, the dimension and fact tables were created, according to the data warehouse diagram. All layers and models developed can be found in the "models" folder of this repository.
Additionally, documentation was created for the models in the .yml files, and generic and singular tests were applied.
The interactive dashboard was developed in Power BI, connected to the data warehouse in Snowflake. The page design was created in Figma, and the data visualizations were designed to meet the business needs of the Adventure Works sales area, as specified in the certification challenge.
The complete dashboard can be viewed by clicking here.
This project is under MIT License, see the LICENSE.txt file for more details.