Change in Primary Key on EDW table

UDA
Enthusiast

Change in Primary Key on EDW table

All,

There is a change in Primary key of a warehouse table holding data up to date.
Basically source system added an existing column in the table as a primary key and sequelly the same change need to be amended in warehouse(EDW) as well.

Any structured way of handling this to use the data with old primary keys as well new primary keys

Many thanks in advance for all of your inputs.
4 REPLIES
Enthusiast

Re: Change in Primary Key on EDW table

Hi,
Make the old primary key columns as primary index and new primary key columns as primary key..

Regds,
sachin
Enthusiast

Re: Change in Primary Key on EDW table

I think a surrogate key or an identity column can help you.
Add a column to your table making it the unique primary index.
Then reload your table with existing data.
Before reloading make sure u have a surrogate key logic being handled by the ETL or declare the primary index as identity column.
Thanks
Enthusiast

Re: Change in Primary Key on EDW table

There are 2 ways to perform this.

Present condition:

1)you have a table with rows.
2) Created a new column which will be the primary key.
3) This new column have NULL in the existing records.

If above condition is right then go for Insert or update.

Insert:

Reload entire data as suggested by bhattachary.

Update:

create a new column(dont make it primary key)
update this col using some sort of coding.
Then make this column as primary key.

This solve your issue i belive.
Enthusiast

Re: Change in Primary Key on EDW table

...also remember that if you re-define the Primary Index of your table, the skew of your data across amps will most likely change for the better or the worse. You can analyse skew with the following:

select hashamp(hashbucket(hashrow(col1, col2, col3))) ,count(*)
from tableName
group by 1;

Just insert the column/group of columns that you currently use and/or you intend to use for your primary index to get a before and after comparison.

Hope that helps.