Business Key and Source Id naming conventions

I am working on a project that has both and ODS and DW that will be fed from the ODS.

The tables in the ODS will have their own identity columns for the primary key.  Most if not all the ODS tables carry the source primary key which is generally named sometablename + id

So when creating the ODS tables is it best to rename the Source PrimaryKey if its name would collide with  the ODS primary key or is it better to leave the sourceId name unchanged? And if so I was wondering what others have generally used ?  Also what happens then if we are moving data then from the ODS to DW?

I was considering the following for the three environments:

OLTP Source    
SampleId                                                                                                                          
                           
ODS destination  
SampleId              int identity
SampleSourceId    (SampleId from OLTP)              



DW destination
SampleId              int Identity
SourceSampleId     (Sample Id from ODS)
                   

This would mean that the source "Whatever" id is always the identity(i.e pk) from the source table.  And if for some reason I needed to trace back from DW to OLTP then I would
have to join sampleSourceId from DW to SampleId from ODS and then SampleSourceId from ODS to SampleId from OLTP.

Is there a more elegant or better way to name the source Id's and PK's  ?

Thanks,

Do what you feel makes sense. However, it's not a good idea to use source surrogate keys as a means of identifying rows in the DW. You should rely on business keys.

I agree I will rely on business keys I just wanted to know if there was some "best convention" for giving them names vs identity columns.

Thanks,

I've been following the convention from the Kimball Group's Dimensional Modeling Workbook, which for your example would look something like:

OLTP Source
SampleId

DW destination (Dim_Sample)
Dim_Sample_SKey int Identity
Dim_Sample_ID (Sample Id from ODS)

The guiding principle (as I understand it) is that the DW defines the names, not the source system. So if the DW designer decides that Dim_Sample would be better named as Dim_Samples, that would result in columns Dim_Samples_SKey and Dim_Samples_ID.

From a technical perspective, I find the underscore word separators more readable and work well with most downstream reporting/cube tools (they automatically present to users with spaces in place of the underscores).

I found your renaming of columns quite confusing (e.g. OLTP SampleId = ODS SampleSourceId = DW SourceSampleId).

Sorry this doesnt provide any specific help on ODS naming. I tend to follow the DW naming where DW attributes exist, and follow the Source system naming where they do not.

Mike