Creating a data model is useful for data integrity, organization, and performance. Separating a wide table into fact and dimension tables defines clear relationships between fields and allows you to be frugal with data, using only tables that are relevant for your analysis.
This image nicely presents the concept behind a star schema, where a fact table containing all measures and foreign keys links to dimension tables containing descriptive details (image source). A star schema is considered a type of data model.
I created a star schema for a dataset focused on patient visits to different health clinics, where each row represents a visit by a patient. The following is my sketch:
And the following is my user story:
Defining a user story allowed me to understand which types of data sources I'd need to create for different types of analysis. For example, I might not need the same combination of tables to explore visit traffic by villages versus investigating health burdens by different patient demographics. That's where my plan for creating different data sources came into play:
Creating clear documentation allowed me to fairly easily create each table in Snowflake with functions such as create table, select, and from. I also leveraged update and set for making changes to tables after I created them. To create foreign keys (or unique identifiers) for fields in the fact table, I used the md5 function, which generates a 32-character hex-encoded string.
Once my fact and dimension tables had been created in Snowflake, I accessed the Snowflake server in Tableau Desktop and pulled in my tables to relate them to one another:
Now my data model is ready for visualization! Continuously prioritizing documentation throughout this project proved invaluable, both in creating structure and formulating a clear final product ready for handover.