In today's installment of dashboard Week, we were set the challenge of utilising Snowflake and Tableau to unravel insights from a realisticly sized fake business's database. The task at hand demanded not only querying the database to build out different tables, but also transforming the raw data into visually appealing charts, dashboards, and stories. This blog with dive into the journey I took from the beginning of the day, when the task was revealed, all the way to presenting my attempt at it. Choosing the Business Element The challenge kicked off with the task to choose an element of the business to center our analysis around. For mine, I opted p to explore the company's revenue streams, of website, catalogue, and in-store sales. My end goal was not just revenue comparison, but also a deep dive into the most sold products within these streams and the demographics of the customers involved, finally aiming to see if the demographics that bought certain products the most lined up with the demographics which utilised each revenue stream most frequently. Deciphering the Database Schema With the schema of the company's database in hand, and the credentials to access the database within our Snowflake warehouse, the next hurdle was to decide which tables to query and how to join them to allow for a comprehensive analysis. This critical step laid the foundation for the subsequent data exploration. Planning and Visualization
Armed with insights from the schema, I began to plan my approach to the challenge using Excelidraw. The plan encompassed the tables to be queried in Snowflake, the specific analyses to be conducted on the data, and even preliminary sketches of the charts to be crafted in Tableau. Encountering Time Constraints
However, a significant challenge emerged as the database contained over 100 billion rows. Querying such a vast dataset in Snowflake proved extremely time-consuming, with some queries stretching beyond an hour followed by timing out completely. The processing power required to evaluate each row of data, with every WHERE statement and aggregation, along with the bandwidth limitations of our company's Snowflake Database became evident bottlenecks in the process. As a result, a substantial portion of the today's challenge quickly turned into a waiting game, staring at Snoflake's elapsed query time counter. Ultimately, these extended query times meant that a significant chunk of my day was spent in anticipation of working tables that could be moved over to Tableau, limiting the final output i to just three charts within a story, a far cry from the solution I had envisioned when I first saw the challenge on the way to work this morning. Reflections
Despite the time constraints and unforeseen challenges, the experience I gained today with Snowflake SQL and Tableau was invaluable. It not only allowed me to hone my technical skills, but also underscored the importance to me of strategic planning and adaptability in the face of real-world data challenges. The size of the dataset we were tasked with using today could, realistically, be similar to what I'll face during my placement, and therefore, thinking about ways to overcome these issues, and focussing on making my SQL queries as efficient as possible is a skill which I have now come to realise will be invaluable in the future.