Original subtitles

All right.

Time to talk about snowflakes and I'm not talking about whether I'm talking about schemas.

I consider this table structure here.

This should look extremely familiar because this is the exact table structure that we're working with

with our Adventure Works demo got one data table sales data table at the bottom and three product related

look at tables product subcategories and categories.

Now here's the thing.

The sales data table can connect to products using that product key column.

But we have no means of connecting the sales data directly to either subcategories or categories because

we don't have any foreign key that can map to one of the fields in those tables.

But fear not we've got an alternative.

And what we're going to do here is actually connect products to products subcategories because both

of those tables share products subcategory key and then by similar logic connects subcategories to categories

because those two share product category key.

And in doing so we've essentially connected sales data to each of those lookups in the chain.

So when you create those actual table relationships it looks something like this.

So little protip peer models that have chains of look up or dimension tables like this are often called

snowflake's schemas whereas star schemas generally have a bunch of individual lookup tables surrounding

one central data table.

So let's hop into our relationship view and build out these product relationships case or back to our

model in the relationships.

You got these set up nicely to create that snowflake chain and all we need to do here is Connect the

product subcategory key to the product subcategory key there and the category key to the category of

key.

And there you go.

We've created our snowflake our chain set of lookups that connects all the way down to that sales table.

That means we can filter or segment these order quantity values by fields in the product table or the

subcategory table or the category table.

Now interesting thing call out here is that you'll notice that a table like this product subcategory

or the product lookup table for that matter contains both primary keys.

In this case product key and foreign keys like the product subcategory key because in this case for

products unique values for product key and multiple values for subcategory and the same thing holds

in the subcategory table as well.

It's got a subcategory key which is primary and a category key which is foreign.

So just an interesting thing to call out there tables don't necessarily have to have only a primary

or foreign key.

They could potentially have both.

So there you have it.

We've created a little snowflake schema here and we've officially wired up all of the tables that currently

exist in our model.

So go ahead and give the file a save and then we'll move on to managing and editing these relationships.