CODEWITHSUNDEEP
data-warehouse
The Beast
The Beast

Reputation: 133

Primary and Foreign Key in DW tables

I've read that dimension tables hold the primary key and and fact tables contain the foreign key which references the primary key of Dimension tables.

Now the confusion I am having is this - suppose I have an ETL pipeline which populates the dimension table (let's say customer) from a source (say another DB). Let's assume this is a frequently changing table and has over 200 columns. How do I incorporate these changes in the dimension tables? I want to have only the latest record for each customer (type 1 SCD) in the DWH.

One thing what I could do is delete the row in the dimension table and re-insert the new updated row. But this approach won't work because of the primary key - foreign key constraint (which will not allow me to delete the record).

Should I write an update statement with all 200 columns in the ETL script? Or is there any other approach?

Upvotes: 0

Views: 296

Answers (1)

nsousa
nsousa

Reputation: 4544

Strictly speaking you just need to update the fields that changed. But the cost of updating all in a single record is probably similar (assuming it’s row based storage), and it’s probably easier to write.

You can’t delete and re-insert, as the new row will have a new PK and old facts will no longer be linked.

Upvotes: 0

Related Questions