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
Primary Key for Staging Tables
Moderators: chulett, rschirm, roy
Primary Key for Staging Tables
Aim high
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.
If your source has duplicates and you need to retain them, then dont create a primary key. Or just create a surrogate key.
Creativity is allowing yourself to make mistakes. Art is knowing which ones to keep.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
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.
Last edited by ray.wurlod on Tue Jun 12, 2007 5:12 am, edited 2 times in total.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Participant
- Posts: 54
- Joined: Mon May 28, 2007 12:41 am
- Location: Chennai
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.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers