Accumulating snapshot table and bridge

[tim_goodsell]

Hi

I have a loan application accumulating snapshot table which when changes creates a new record with valid from/to dates The PK can either be

a) LoanId + ValidFromDate

or

b) A surrogate key

which is the better method to use ?

Now a loan application can have one or more applicants. I was going to create a bridge table (applicants) that links the customer dimension (PK CustomerKey) with the accumulating snapshot fact table. If I am adding new records to the fact table every time a loan application changes do I need to add new set of records to the bridge table ?

Thanks

Tim

[BoxesAndLines]

If you are adding a new fact row for every change what you really have is a transaction fact. If you update the row, then you have an accumulating snapshot.

I always include a single column surrogate key in my fact tables. If you do that you will have two valid keys, which is fine by me.

Your bridge table will need to be updated for all new fact rows. I would go type 1 to avoid carrying unnecessary history.

[ngalemmo]

That depends on how you build the bridge table.

If the bridge is between application version (fact row) and customer, then yes you do.

If the bridge is between customer group and customer (where customer group represents a unique combination of customers) then no, you do not.  The fact would contain a customer group foreign key which would be universally applicable to any row. Given you may have multiple versions of the same application, this approach would result in a much smaller bridge table and better performance most of the time.

It is best practice to use surrogate keys at all times.

[Sponsored content]