Today was Day 4 of working on our Capstone Project. I started the day working on the DDL and DML processes based on the schema we drew up yesterday. However, as I was inserting the data and building the pipeline from Alteryx to Snowflake, I realized I had some data integrity issues while performing sanity checks.
These issues were mainly related to the join I had done in Alteryx, which wasn't based on enough conditions. My join across both datasets was completely dependent on the song title, for which I had created a song key. However, many songs share the same title, such as "Love" by Lana Del Rey and "LOVE." by Kendrick Lamar featuring Zacari, both of which appeared on the Billboard charts. This meant that songs by different artists could potentially be matched incorrectly, affecting the integrity of my data.
One of the successes today was being able to organize my data in a way that would make it easier to extract insights in Tableau. I also added a bridge table to my schema, which allowed me to map multiple artists to the same song while also accounting for artists appearing on multiple songs. This is good practice when dealing with many-to-many relationships and helped me better structure my data for analysis.
I also spent some time building out my user stories further and defining the use cases for each stakeholder. These are the music label executive, who is looking to understand what and who has performed well on the charts, and the label analyst, who might be looking more closely into what characteristics are associated with a hit song and how those insights could inform future music production.
Although the data integrity issues meant I had to revisit some of the work I had done previously, I was ultimately able to insert all my data into Snowflake and create wireframes for my dashboards.
For Day 5, my focus will be on building out the executive and analyst dashboards in Tableau, followed by preparing the final presentations. With the data pipeline and schema now built out, I'm hoping to dedicate the day to visualizing the insights and making sure each dashboard answers the questions of its intended stakeholder.