Data Modelling: Star x Snowflake Schemas

-

 


The formation of snowflakes is a complex and delicate process 


This week, I added more tables to a data schema I created a few months ago. 

To ensure optimal query performance when checking data quality, I took a deeper look at the differences between the two most popular schemas: star and snowflake.

Now, if you are new to this, your first question may be: What is a data schema?

To answer that question, let's start with the origin of the word. 

"Schema" comes from the Greek word σχῆμα (skhēma), which means shape or plan.

A data schema is the blueprint that defines the structure of the database, including tables, columns, data types, constraints, and entity relationships.

Naturally, your next question may be: What is the difference between a star and a snowflake?

Well, that's a good question! I hope we can both agree that a star may look like a snowflake, but it's not a snowflake.

What makes a snowflake special is its tendency to branch out at the end. 

Your final question would be: What is the difference between a star schema and a snowflake schema?

Before I get into the characteristics of these two schemas, I must first introduce you to two key pieces of jargon.

Denormalization: Think of a pot of soup, you can find everything in the same place but, sometimes it might get a little messy. 

Normalization: Similar to putting Barbie dolls in one box and Hot Wheels cars in another, normalization is putting data into specific tables so that you know exactly where to find them.

Great! Now you are well-equipped to know what the differences are between them. 

Imagine a star! 🌟: Denormalization

  1. In a Star schema, you have a bright center (the fact table) surrounded by arms (the dimension tables).
  2. The fact table holds important numbers or data you want to analyze, like how many cookies you eat each day.
  3. The dimension tables provide extra details about the data, like the type of cookies, who made them, or where they came from.
  4. It's easy to understand and it makes queries faster because the data is organized in a straightforward way.

Now, think of a snowflake! ❄️: Normalization

  1. In a Snowflake schema, you still have a central part (the fact table) but the arms (dimension tables) have more branches, like a snowflake spreading out.
  2. The dimension tables are more detailed, broken down into smaller sub-tables, to avoid repeating information.
  3. It's like a fancy snowflake with more intricate patterns, but it might take a bit more time for queries because of the additional connections between the sub-tables.
In summary, a Star schema is simple like a star and great for faster queries, while a Snowflake schema is more complex like a snowflake with detailed patterns, but it optimizes storage and data consistency. Depending on your needs, you can choose the one that fits your data requirements!


Resources






Comments

Post a Comment

Popular posts from this blog

Missing Data : What to Do?

-
Image
  A missing tooth: that's not good (or is it?). Today's topic is inspired by a problem that one of my coworkers brought to my attention, and I think this is also something many people face on a day-to-day basis. As data professionals, we have all encountered datasets that contain fields with no data, which can be frustrating when building reports or machine learning models. Since we all know that a perfect dataset does not exist in the real world, the next best thing to do would be to have a game plan in place to tackle the inevitable. When posed with the question, "Toni, how should I go about solving this?" the first word that comes to mind is context. You see, just because something is missing doesn't mean it needs to be "fixed" for example, knowing why a tooth a missing is important before acting.  A 6-year old with a missing tooth doesn't require a filling.  With that being said, here is a strategy for dealing with the unknown: Why is the data no
Read more

Prompt Engineering : An Introduction

-
Image
  A stereotypical engineer Despite concerns about job changes due to ChatGPT, something interesting is happening: the emergence of a new job title known as the Prompt Engineer . Still skeptical? Take a moment to browse professional platforms like LinkedIn or Indeed—see it for yourself! Now, you might wonder, 'What is Prompt Engineering?' Well, I'll give you a prompt answer (okay, sorry for the pun). Essentially, prompt engineering involves using special words or hints to instruct computers without saying everything explicitly. Think of it like when your mom says, "Bring me my bag from on top of the table." Even if your house has many tables, you know it's not in her car. She assumes you know what her bag looks like, so she doesn't need to describe it. But if she tells you which room to search in, you'd find her bag faster, right? Having said that, here are some tips that can help you write robust prompts: Understand the Task This is where your domain
Read more

Upskilling: Certificates vs. Certifications

-
Image
  Fun Fact: Those LinkedIn courses you do are NOT  certifications. It's performance review time, and I had to differentiate between certificates and certifications for the first time.  As we approached our final pivotal meeting for the fiscal year, I was tasked with presenting the total count of certifications attained by our team throughout the year. Initially, I presumed this to be a straightforward task. I circulated a Microsoft Form among my colleagues, expecting a simple input of numbers.  However, upon aggregating the data, I found the resulting figure to be somewhat inflated. To gain a deeper understanding of this count, I requested my colleagues to provide a list of certifications they had completed. When I looked closely at the names of these "certifications," I found that about one out of every four was actually a certificate. This prompted a need for a clearer definition distinguishing between a certificate and a certification. So, today, I'm going to expla
Read more

Women In STEM : Challenges and Advantages

-
Image
  Women make up only 27% of the STEM workforce. Introduction Being a female in Technology, I can also identify as being a "Woman in STEM" and I can say being part of this group is much different from our male counterparts. When you think of an engineer, the immediate image that comes to mind is often that of a man earning a six-figure salary. Before directing any resentment towards men, it is crucial to pose this question:  "What caused this gender gap in the first place?"  Common Challenges Two terms frequently used to answer this question are stereotypes and gender roles. The stereotypical woman is caring, nurturing, and supportive. She is also expected to stay at home, to cook, clean, and raise children. However, in 2024, being a stay at home mom is rare, with more women actively participating in the workforce, some even serving as the primary breadwinners of their households. But, they are still expected to take on the responsibilities of a stay at home mom, whi
Read more

SQL Server Reporting Services vs. Power BI

-
Image
  When I think of Reporting, I think of the news.  Throughout my career in Technology, I have encountered two Microsoft products, Power BI and SQL Server Reporting Services (SSRS), both designed to serve the same purpose: Reporting.  Today, I decided to delve into their origins and explore their respective strengths and weaknesses. A Brief History on SSRS  If you were to ask me, "What is SSRS?" before my current job, I'd probably just stare blankly and shrug my shoulders. However, now I can confidently tell you that SSRS stands for SQL Server Reporting Services. Here's a fun fact: It started in 2004 as an add-on for SQL Server 2000. People really liked it because it could work with all sorts of data, such as tables from SQL Server databases, Excel, and XML files. And you know what's interesting? Even after all these years, people are still using it. SSRS Strengths SSRS is better suited for generating paginated reports, such as invoices and statements. Report subs
Read more

5 Authentication Methods

-
Image
  This piece of plastic tells people who you are.  The verb authenticate originated from the Latin word authenticus , meaning genuine. Confirming that someone is really who they claim to be has always been a problem that we humans have tried to solve. You see, just because someone says they are Michael Jackson, it doesn't mean they are, because they could also be lying (who doesn't lie to get what they want?) . Before the advent of modern technology, various authentication methods were used to verify the identities of individuals before granting access to resources, from signatures to special passphrases. As a child, I can remember devising specific ways of knocking on the door with my siblings and cousins to confirm each other's identities before granting access to our top-secret room. However, knocking on digital doors requires more robust methods of accessing resources, such as your social media profile or your bank account. Today, I will mention some authentication meth
Read more

There Has Been a Data Breach: Now What?

-
Image
  Hacker : a person who uses computers to gain unauthorized access to data. They say that prevention is better than finding a cure, but in the case of the Telecommunication Services of Trinidad and Tobago (TSTT), that is no longer an option.  Six gigabytes of sensitive data are now up for grabs on the dark web (the internet's equivalent of the black market).  From internal administrative credentials to customer names and addresses, you can access it all using The Onion Router (Tor). RansomEXX has successfully bypassed all the controls that were meant to mitigate TSTT's risk of a cyberattack.  I deliberately italicized the word 'controls' since, based on what Alex Samm said in his post on this topic, passwords were not encrypted, and RansomEXX's modus operandi usually involves gaining access through phishing. Now, I'm not saying that TSTT did nothing to prevent this, but storing passwords as plain text in a text file does make me raise my eyebrows. Alex also mad
Read more

Inductive and Deductive Reasoning

-
Image
Reasoning : the action of thinking about something in a logical, sensible way   Earlier this week, a DataCamp course sparked an interesting thought about how we reach conclusions. In data analysis, we ask key questions, gather information, and draw insights from what we find. This happens across many STEM fields. But something I recently noticed is how we actually get to those conclusions – there are two main methods: inductive and deductive reasoning. Let's break it down: Inductive Reasoning:  Imagine you see all your friends going into tech or medicine. Based on this limited view (your friends), you might guess (general conclusion) that young people aren't interested in plumbing careers. However, this might not be entirely true! Deductive Reasoning:  This approach is more like a detective story. We use established facts (like employment data) and analysis tools to reach specific conclusions. For example, we could analyze data over time to see if there's actually been a d
Read more

Improving SQL Query Performance : Indexes

-
Image
  An index is analogous to the Table of Contents in a book.  Prior to exploring what indexes entail, I'd like to begin by discussing 'what SQL is.' SQL, often pronounced as 'sequel,' stands for Structured Query Language .  It is known for being declarative, enabling you to specify what you want without needing to focus on how to achieve it. However, beyond your initial query (SELECT * FROM menu WHERE food = 'burger'), the computer still needs to know how to go about finding those burgers, and that's where an Execution Plan comes into play. An SQL Execution Plan is a detailed outline or strategy devised by the database management system (DBMS) to execute a query efficiently, specifying the steps required such as scanning, filtering, and joining data to retrieve the requested information. Now, imagine scanning a table like flipping through pages in a book – the more pages, the longer it takes. So, keep in mind that scanning time is directly proportional
Read more

Don't Be Bland : Spice Up Your Personal Brand

-
Image
   If you don't define your brand, other people will.  In today’s fast-paced digital landscape, personal branding is no longer a luxury; it’s a necessity.  These are the sentiments echoed by Keron Rose on an episode of his podcast, Digipreneur (check out the links below).  Whether you’re an aspiring developer, a seasoned IT professional, or a tech entrepreneur, your personal brand can significantly impact your career trajectory.  But before I get into that, I wanted to touch on a key difference between personal brand and reputation, which was also mentioned on the podcast. Personal Brand  Your personal brand is a deliberately crafted identity that you strategically create for yourself. You have direct control over your personal brand. It’s the image you project intentionally through your online presence, interactions, and content. Building a personal brand allows you to showcase your expertise, values, and unique qualities. It’s an ongoing process that evolves as you grow. Reputat
Read more