CODEWITHSUNDEEP
data-warehouse
rendybjunior
rendybjunior

Reputation: 602

data warehouse - non volatile vs change data capture

Some say data warehouse is non-volatile. It means no update of data is allowed.

However, sometime we have to capture changes in data. For example changes in transaction status.

Then change data capture comes as a solution.

My question is, should we rely on fundamental concept of data warehouse, to be non-volatile? If we should, then what is another alternatives to capture data changes?

Upvotes: 1

Views: 4699

Answers (2)

ben
ben

Reputation: 480

When we say the data warehouse is volatile, that simply means data is stable in a data warehouse. More data is added but data is never removed. This enables management to gain a consistent picture of the business.

Upvotes: 0

nsousa
nsousa

Reputation: 4544

Non volatile doesn't mean "no updates". An accumulating snapshot fact table usually uses updates. Non volatile pertains more to the notion that data is not discarded, it's not temporary. Even if old data is archived, there's still a way to retrieve it at some point. At least this is how I understand the recommendation.

I prefer to avoid updates entirely, mostly by inserting "correction facts". For example, you have a snapshot fact table with an account balance. On a given day the account balance is 1000; a late arriving fact changes that balance and it now should be 1100. Instead of updating the previously inserted fact, I'd rather insert a correction fact with value 100, the difference between the previously known value and the new value. However, for an accumulating snapshot fact table this may not be possible or recommended. Tracking status changes is, usually, modeled through accumulating snapshots, which will require updates.

Upvotes: 2

Related Questions