Are Surrogate Primary Keys needed on a Fact table in a Data Warehouse?

Question

When I asked our DB designers why our Fact table do not have a PK, I was told that there is no set of columns in the table that would uniquely identify a record, even if all the columns were selected. Whenb I suggested that we an an identity column in that case I was told that "I'd just be wasting space and that it wasn't needed."

My feeling is that every table in the source system should have a PK, even if it is an identity column. Given that the data warehouse (DW) is a recipient of data from other system-how would I otherwise be able to ensure that the data in the DW accurately reflects what is in the source system if there is no way to tie individual records? If you have a runaway load program that screws up data and has run for a week, how would you reconcile the differences with a live transaction source system w/o some sort of unique constraint to compare?

Answer 1

Database table without primary key seems like a poor design choice and making lots of room for different types of anomalies i.e. how would you delete or update single record in such table?

Answer 2

You are correct--sort of. Without a primary key, a table does not meet the minimal definition of being relational. It's fundamental to being a relation that it must not permit duplicate rows. Tables in a Data Warehouse design should be relational, even if they're not strictly in normal form.

So there must be some column (or set of columns) in the row that serve to identify rows uniquely. But it doesn't necessarily have to be an identity column for a surrogate key.

If the Fact Table has no set of columns that can serve this role of being a candidate key, then more Dimension Tables are needed in this DW, and more columns are needed in the Fact Table.

This new Dimension alone may not be the primary key; it may be combined with existing columns in the Fact Table to create a candidate key.

Answer 3

I would agree with you.

"I was told that there is no set of columns in the table that would uniquely identify a record, even if all the columns were selected." - this seems to break something fundamental about relational databases as I understand them.

A fact consists of additive values plus foreign keys to dimensions. Time is an obvious dimension that is common to every dimensional model that I know. If nothing else, a composite key that contains timestamp would certainly be unique enough.

I wonder if your DBAs have much knowledge about dimensional modeling. It's a different way of thinking from the normal relational, transactional style.

Answer 4

An identity type column is a "surrogate" key that replaces one of your "candidate" keys (simply put). Adding a surrogate key columns adds nothing if you can't identify a row without it. Which requires a candidate key.

Answer 5

A data warehouse is not necessarily a relational data store, although you may choose to make it one, so relational definitions don't necessarily apply.

A primary key is only required if you want to do something with the data that requires a unique identifier (like trace it to a source, but that's not always required or necessary or even possible anyway); and data in a data warehouse can often be used in ways that don't require primary keys. Specifically, you may not need to distinguish rows from each other. Most often for constructing aggregate values.

Time is not a required dimension in constructing data warehouse tables.

It may be psychologically uncomfortable, and wasted space is a trivial issue, but your colleague is correct - PKs aren't necessary.

Answer 6

Not having a unique identifier for each row is even worse than it first seems. Sure, it is precarious and it's easy to inadvertently delete some rows.

But performance is much worse too. Each time you end up asking the database to get you the rows for Employees with EmployeeType = 'Manager' you are doing a string comparison. Identifiers are just faster and better.

Besides, storage is cheap and in this case I imagine the impact on space will be less than a quarter percentage point if that--as a data warehouse you are probably designing for terabytes of data.

Answer 7

If the fact table is at the center of a star schema, then there is in reality a candidate key. If you take all the foreign keys in the fact table together, the ones that point to rows in the dimension tables, that's a candidate key.

It probably would not do much good to declare it as a primary key. The only thing it would do is protect you against a rogue ETL process. The folks who run the warehouse might have the ETL processing well in hand.

As far as indexing and query speed is concerned, that's a whole different issue with star schemas than it is with OLTP oriented databases. The people who run the warehouse may have that in hand as well.

When designing a database for OLTP use, it's unwise to have a table without a primary key. The same considerations don't carry over into warehouses.

Answer 8

http://www.ralphkimball.com/html/controversies.html

Fable:

The primary key of a fact table consists of all the referenced dimension foreign keys.

Fact:

A fact table often has 10 or more foreign keys joining to the dimension tables’ primary keys. However, only a subset of the fact table’s foreign key references is typically needed for row uniqueness. Most fact tables have a primary key that consists of a concatenated/composite subset of the foreign keys.