Data Modeling - Walking Through How To Data Model As A Data Engineer - Dimensional Modeling 101

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

What Is Change Data Capture - Understanding Data Engineering 101

If you would like to learn more about data engineering, then check out Googles GCP certificate

If you'd like to read up on my updates about the data field, then you can sign up for our newsletter here.

Or check out my blog

And if you want to support the channel, then you can become a paid member of my newsletter

Tags: Data engineering projects, Data engineer project ideas, data project sources, data analytics project sources, data project portfolio

_____________________________________________________________
_____________________________________________________________
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.

[HoneyCombAI]

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.

[psgredz]

the increase in quality and production is awesome

[MilhouseBS]

Yay, dimensional modeling. Kimball and Adamson and Reeves and Ross.

[jusaverage]

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.

[carstenklausman]

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.

[FelipeMaurer]

Snapshot tables are also used when you need to track the balance of a lot of accounts, like in an accounting system.

[firefoxmetzger]

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.

[ansonnn_]

Need that Slowly Changing Dimension video please!

[vikram--krishna]

Thanks for the video!

I would love to learn about One big table model and its use cases and its pros, cons.
Thanks

[chrisclarke]

Thanks very much for this great video!

[richardduncan]

Bridge table looks like the Excel version of link workbooks... always ends in tears :)

[alibachoy]

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?

[Aristocle]

Is there a service or scripting language that allows me to write relationships between tables/databases in a modern material design style?

[madbaumer]

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?

[elcoxeroni]

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!

[KPYCHIK]

Great video. What other books you may suggest to read on this topic?

[niniolarotimi]

Great content! Please what is the name of the book that was shown in the video?

[babuganesh]

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

[SnippetSensations]

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

[AngryWolf-sqwx]

Learn a lot from your content! But putting yellow bold text in the beginning really strains the eyes...