DSXchange

I think that this Question is not directly related to this forum. But out of curiosity I am posting this Question.

Do Staging tables in Data Warehouse/DataMarts need Primary Key?

I feel primary keys are not needed for Staging Table as if they are any duplicates, they get rejected in Fact Table and Dimension Table.

but one of my colleagues says that primary keys are must for tables in Data Warehouse ( he even says primary key is must for all tables in a Data Warehouse)

Any inputs on this

It really depends upon what you are using the staging tables for. It also depends upon where the staging layer comes in. Right in the begining of the cycle before transformations or after transformations and right before loading the target.
If your source has duplicates and you need to retain them, then dont create a primary key. Or just create a surrogate key.

I primarily use text files (or Data Sets in parallel jobs) for staging because they are FAST.

What does your "must have primary key" colleague think of that ?!!

I agree that primary key is a must for all tables in a Data Warehouse; this is one of the fundamental principles of star schema design. But not necessarily for staging areas.

Incidentally, the metadata forum probably would have been appropriate; whether or not a column is a Key is metadata.

Yes, you have to have a primary key in all the tables in a Datawarehouse. In practice we replace the natural primary key with the surrogate key. Otherwise you can't load data into dimensions and fact tables.

The question is "Do staging tables in DW/DM need a Primary Key"? And the short answer is no. They don't fall under the general rule of being "a must for tables in a DW" as to me the staging area / back room "tables" are separate structures outside of the core DW itself.

NO. There is no hard and fast rule that a primary Key should be defined in Staging Table