Dimension Tables vs. Fact Tables
All data analysts should know a little data engineering 😉
Got BDE?⚡️
Hi BDEs!!⚡️ SO happy you’re here today. Guess what?! I just filmed 2 sample YOUTUBE 🎥 videos that are coming out soon so go follow me on @JessRamosData on the good ole YouTube 😉 I haven’t gotten my whole YouTube recording corner ready yet, but I filmed some “practice” vids to learn the ropes a little. I’ve been so scared to post the first one because I want it to be perfect, but I think it’s better to go ahead and post SOMETHING even if it’s not perfect. An imperfect step forward is better than staying stagnant ❤️
Supporting my partners helps me keep this newsletter FREE, so please show them some love!! ⬇️
Stop making PowerPoints completely from scratch and started leaning more on AI. Tools like Gamma🔗 make it SO easy to make PowerPoints, documents, and websites from scratch using a single AI prompt. You can also input text and other files to produce your output. Check out this document I made on remote work benefits with AI: Remote Work Benefits🔗
Today we’re talking about some data engineering concepts that I’ve personally found really useful in Data Analytics!
Dataset of the Week⚡️
Did you know you can ask ChatGPT to generate a dataset for you?! You can tell it what kind of industry and data you want to work with and POOF! You can download it immediately 😉
So this was a nice way of me saying “Go get your own dataset this week!” LOL
Social Highlights⚡️
Dimension vs. Fact Tables
Dim vs. Fact tables are a hot topic in Data Engineering because they’re essential when it comes to understanding how to model data and build relationships between them. Although I’ve never been an official data engineer, in the startup world, you wear a lot of hats. So I’m no stranger to putting on my DE hat to make views in the database or even to build an entire data model in Power BI.
At the start of my career, I broke MANY data models in Power BI. And I mean MANY! LOL! Version control is important ok 🤣 Power BI was a great way for me to learn the difference between a Dim table and a Fact table and how they can work together to create magic for reporting and querying. So if you want to dabble and get started, build a data model in Power BI. It’s a great visual way to learn the ropes!
You definitely need to understand what’s different about Dim vs. Fact tables to understand the relationships between them. This gets really important when it comes to writing queries in SQL because you need to know how tables are connected. Do they have a 1:1 relationship? 1:M (1 to many)? If you don’t know these answers, you may do some CRAZY SQL joins and end up with missing data or extra data in your output.
So what’s the difference??
Dim vs. Fact Difference
Talking about Dim & Fact tables is easiest by comparing them:
Dim tables contain descriptive data and fact tables contain quantitative data.
Dimension tables are tables that contain unique values whereas fact tables are tables that contain allll of the data.
Dimension Tables are used to filter, describe, and add context to fact tables.
Dimension tables are usually smaller, while fact tables are usually larger.
Fact tables generally have columns that serve as foreign keys and link to (or join to) the primary keys of the dimension tables.
Want me to explain it to you like you’re 5? 🍔🌮 🍕
In the simplest terms possible, a dimension table is like a menu that displays all the options (burger, taco, pizza, etc.) and a fact table is like a list of all the orders (52 burgers, 900 tacos, 34 pizzas, etc.). 🍔🌮 🍕
Now that I’m hungry talking about pizza, let’s talk about some examples:
Fact Tables:
Orders
Subscriptions
Event Log
Status Log
Web Traffic
Dim Tables:
Product
Customer
Users
Event Definitions
Employee
Fact tables usually carry the bulk of the data and the detailed, granular data points (like transactions) that are used to create metrics and KPIs. But what good are those without context? Maybe you have X% for your metric, but what does that mean? That’s when you pull in your context (hello Dim tables) to add more value. We can pull in product IDs, customer names, and user emails to now provide more context to our metric. Without Dim tables, we’d be completely lost!
A lot of data analysts don’t know much about basic data engineering concepts like Dim and Fact tables, but data modeling affects all of our work so much since we’re always querying tables and connecting them together.
Understand what’s under the hood or get out of the driver’s seat!
Well, I’ll c u next tuesday!
-xoxo,
jess💕
WHO AM I?⚡️
I'm Jess Ramos, a content creator, instructor, and leader in the data world. I'm super passionate about data and especially SQL! My 9-5 is being a Senior Data Analyst @ Crunchbase, a top tech startup that specializes in private company data. I'm also an Instructor with LinkedIn Learning and the founder of Big Data Energy Analytics. My 5-9 (aka outside of my regular job!) is creating amazing content for YOU on LinkedIn, Instagram, TikTok-- and this newsletter!🤩