All language subtitles for 4. Understanding Data Tables vs. Lookup Tables

All right.

Continue on with this concept of normalization.

Let's talk about data tables versus lookup tables.

Now generally speaking data models contain two different types of tables data sometimes known as fact

tables.

And look up or dimension tables and the way to kind of differentiate between the two is that data tables

contain those quantitative metrics the numbers or values that you really care about.

Typically these data tables are pretty granular detailed level with ID or key columns that can be used

to create table relationships to look ups.

Now on the other hand look up tables they provide descriptive often text based attributes or characteristics

about each dimension and table.

So you remember in the last lecture I talked about each table in a model serving a specific and distinct

purpose.

Well that's exactly what lookup tables are doing they're providing rich information and attributes about

things like customers like products like dates.

And that's exactly the role that they play within our model.

So let's walk through an example here this blue table is a data table that contains numerical values

those quantities along with two key columns that we can use to connect this data table with some lookups

a date column and a product ID column.

So what we'll be able to do is take that date column and connect it to a date column within a calendar

lookup table and the entire purpose of the calendar lookup table.

It's entire reason for existing is to provide additional information about each date.

So it's got one row per date and additional attributes like the day of the month the month number the

year the week day and so on and so forth.

So the bottom line is that if you know the date you also know all of this additional information about

months quarters years weeks etc. and the calendar Look-Up provides or gives us access to that information.

Same story holds for the Product ID that product ID column in our data table can connect to or relate

to it matching product ID in a product lookup table and that product lookup tables purpose is to provide

all sorts of product specific information about each ID.

So similar concept to the calendar table just in this case if you know the product ID then by definition

you know that brand you know the product name you know the skew the retail price the cost the weight

and any other product specific attributes captured by this product lookup table.

So in order to understand the real mechanics of how these relationships work.

One of the first things we need to dig into is the idea of primary versus foreign keys.

Now consider the same set of three tables or blue data table.

Our green calendar and our orange product lookup table consider the date and product ID columns in our

data table.

These are called foreign keys they're keys because they're used to create relationships with our lookup

tables and they're foreign because they contain multiple instances of each value.

And this is the case with many data tables.

Here we have multiple transactions and multiple quantity values per day and multiple transactions and

quantity values per product ID.

So we could potentially see many many duplicate values in both of those date and product ID columns.

Now when we look at the same columns in the lookup tables date in the calendar table and product ID

in the product lookup table These are called primary keys because they uniquely identify each row of

the table and they match the foreign keys in any related data tables.

So it's easy to mix these up.

But as a rule of thumb primary keys are unique foreign keys often contain duplicates.

So let's jump in a power be-I.

I want to open up the relationships view and see if we can identify some of these primary and foreign

keys in the Adventure Works tables that we're working with.

All right.

So once you've got your file open we're going to go ahead and navigate to the relationships.

And we're going to be spending a lot of time here because this is where the data modeling magic really

happens.

And the first thing I want to do is just looking at these tables that we've got I identify which are

data tables and which are look at tables.

Now we've already started hinting at this quite a bit.

We know that the one table that contains our quantitative metrics the values that we care about the

order quantities is our A.W. sales table.

So let's grab that and we're going to pull it down to kind of distinguish it from the lookup tables.

So now we know this is our data table looking up at our other tables.

Product Look-Up contains all sorts of information about products.

Customer is all obviously focused on customer level information.

Calendar is all about dates territory has information about sales regions countries and continents.

And then we have some additional product related category and subcategory tables that are almost an

orphan off here so come on guys.

So all of these tables serve very specific distinct purposes.

And I'm kind of an OCD person so I'm going to drag the product lookup table over here with its friends

and click and drag and just rearrange things a bit.

We're going to be doing more designing in here but there we go.

So at this point we know which tables are lookup tables and we know which tables or data table.

Let's take a quick pass through and see if we can identify our primary and foreign keys.

So starting with our look ups customer look up clearly has a customer key that uniquely identify each

row within this table and therefore uniquely identify as all of this information about customers calendar

look up.

Same story here.

It's not called a key but the date serves that purpose.

If you know the date you know all of this information about that date the month that week the day of

the week and so on and so forth territories we've got a clear sales territory key here which maps to

regions countries and continents and then products are a little bit more complicated.

We're going to get into this later when we talk about snowflake's agreements.

But what you may notice is that some of these tables actually contain more than one key but for the

time being pretend like you never even saw that.

Now shifting down to the data table let's talk about foreign keys.

So we know that multiple sales take place per day.

So both of these date fields order date and stock date are both foreign keys because there are multiple

instances of each value.

And those dates can tie into the date field or calendar look at similar starting with product key got

multiple sales per product.

Same with customer key.

Same with territory.

So all three of these keys plus the two dates make up our foreign keys within the data table.

So that's about enough for now.

That's your crash course in data versus look up tables and primary versus foreign keys.