Data Modeling - Walking Through How To Data Model As A Data Engineer - Dimensional Modeling 101
ฝัง
- เผยแพร่เมื่อ 3 ก.ค. 2024
- Data modeling is key skill data engineers need to know.
In particular dimensional modeling. Whether you eventually decide to go into one big table or you just need to go through and think about your data model.
You'll likely put together a dimensional model.
So here is a quick rundown of dimensional modeling with a walk through.
If you enjoyed this video, check out some of my other top videos.
Data Modeling Challenges - The Issues Data Engineers & Architects Face When Implementing Data Models
• Data Modeling Challeng...
What Is Change Data Capture - Understanding Data Engineering 101
• What Is Change Data Ca...
If you would like to learn more about data engineering, then check out Googles GCP certificate
bit.ly/3NQVn7V
If you'd like to read up on my updates about the data field, then you can sign up for our newsletter here.
seattledataguy.substack.com/
Or check out my blog
www.theseattledataguy.com/
And if you want to support the channel, then you can become a paid member of my newsletter
seattledataguy.substack.com/s...
Tags: Data engineering projects, Data engineer project ideas, data project sources, data analytics project sources, data project portfolio
_____________________________________________________________
Subscribe: / @seattledataguy
_____________________________________________________________
About me:
I have spent my career focused on all forms of data. I have focused on developing algorithms to detect fraud, reduce patient readmission and redesign insurance provider policy to help reduce the overall cost of healthcare. I have also helped develop analytics for marketing and IT operations in order to optimize limited resources such as employees and budget. I privately consult on data science and engineering problems both solo as well as with a company called Acheron Analytics. I have experience both working hands-on with technical problems as well as helping leadership teams develop strategies to maximize their data.
*I do participate in affiliate programs, if a link has an "*" by it, then I may receive a small portion of the proceeds at no extra cost to you.
![[UNCUT] ย้อนฟัง 2 บิ๊กตำรวจปะทะกัน "บิ๊กโจ๊ก VS รองแต้ม" ฟ้องเรียก 10 ล้าน!! I คนดังนั่งเคลียร์](http://i.ytimg.com/vi/vNpTtVjdcBo/mqdefault.jpg)
Yay, dimensional modeling. Kimball and Adamson and Reeves and Ross.
I’ve been thinking about talking on the same subject (dimensional modeling), excited to take a look at this and seeing this knowledge propagated correctly. Nice work.
Thank you, I am doing my best to stay true to the sources and add in some real life experience
If you guys want to learn more about data engineering, then sign up for my newsletter here seattledataguy.substack.com/ or join the discord here discord.gg/2yRJq7Eg3k
Nice video that touch on the basics - but maybe some reflection on today’s capabilities e.g. compression/ column store, that were not around when he wrote the book, that could change some of the design patterns. But overall his principles are still valid today.
I’ve actually used “bridging” or “linking” tables in databases I’ve designed. I just have logic included that makes sure to grab the latest item when bridging/linking like an IS_Current or IS_Latest column.
Thanks very much for this great video!
My pleasure!
great video thank you so much
Thanks for the video!
I would love to learn about One big table model and its use cases and its pros, cons.
Thanks
size, redundancy, performance, compute time.. - few of the factors to be mindful of when dealing with big flat tables
Great video. What other books you may suggest to read on this topic?
Need that Slowly Changing Dimension video please!
Thanks for the reminder!
An example of where a bridge table is necessary (and I havend found a good way around it): UserID and DeviceID stitching in clickstream data.
You want to follow user behavior across devices, but you will likely only discover the link between two devices much later. I.e. both devices will generate data in the big fact table, but only much later (often days) will you realize that this was actually the same user. You don't want to run "update" queries against a clickstream fact table for cost reasons, but you need some way to say that this data is all from the same user.
can we have two dimensions instead of a bridge table - users and devices. Both of these are connected to the fact table in a 1:N relationship with user_id and device_id, as the FKs. So, from the users' slicer when you select a specific user - corresponding devices will appear in the table view and when you select a device from devices' slicer corresponding users will appear in the table view. Is my understand correct or is the requirement different?
@@poojasikdar3583 whay happens if a visitor browses the site without being logged in (no userID available) and then logs in on the same device 1 week later?
You have stored events with userID 'null' in your fact table (you didn't know the correct value at insertion time). Any slicing and joining on userID will now filter out these events, but they are crutial to track aquisitions.
You can fix this by doing a self-join of the fact table to work out the missing userID at query time ... but the performance is dysmal and you are essentially building a dynamic bridge table.
Thank you for this really great content! Which is the book you are referring to in your video? I like the structure much and am considering buying it. Thanks in advance!
Bridge table looks like the Excel version of link workbooks... always ends in tears :)
Is there a service or scripting language that allows me to write relationships between tables/databases in a modern material design style?
what's the difference of a bridge table on a star scheme? Is bridging like 2 databases that have no relevance with one another like for example a PK(customer ID) on one table and just a reference transaction number PK on the other?
The question is what makes bridging different from a star scheme/snowflake scheme?
Good info. One related question that I cannot seem to get a straight answer to online - how do interviews work in terms of laying out your data model? I understand the interviewee talks through their thought process and that's the main point, but is it on pen and paper? A whiteboard? A lucid diagram? How do you actually display your tables and do they give you a prompt right then and there or ahead of time?
it depends - they would give you a problem statement and would ask you to design a data model around it- that can be on paper and you just have to walk them through your thought process or they would ask you to share your system and structure it on a notepad or that could be over a shared file.
@@poojasikdar3583i have hard time in understanding data model vs database model, can you explain it?
Learn a lot from your content! But putting yellow bold text in the beginning really strains the eyes...
Great content! Please what is the name of the book that was shown in the video?
Yay even I need the name there are multiple books by Kimball
can you make videos on these 2 topics
1. Day in the life of a Data Engineer (where you'll actually show your routine by also going in your company and stuff like that)
2. Your programming setup
Can you make a video about Databricks for Students who are interested to learn on how much it would cost for learning it using pay as you go and what all the techniques we can use to reduce the cost. There are many videos in youtube unless we do hands on nothing goes into head
Do you recommend any books on data modelling for relational databases?
(Besides the text mentioned in the video)
Sql is the most important 👀