i recently received the following question about an operational data vault, to which i will attempt to formulate an answer: this customer is using cdc to transfer source system changes to a staging area then a scheduled process (every 5 mins/24 hours) runs to populate the data vault. it was developed to process each table independently from all other tables, so there is no dependency issues.
a solution architect has a design that calls for “real-time” loading of an ods which will be in the implementation of data as a service which will use the tibco bus to publish data to applications to consume.
could this ods be the data vault layer of the data warehouse if we sped up the loading of certain data – maybe use triggers on the staging tables?
triggers on the staging tables are usually bad ideas – especially in a batch situation as they would slow down the batch loading process by a factor of 10x or more. however, triggers in an odv staging area should be just fine, as triggers are meant to be operational and transactional in nature, and it sounds as if the data will be flowing as a transaction into the odv staging area. if the transactions are small, and they flow quickly in & out of the staging area, then yes – you can use the odv staging area with triggers.
but the question is deeper than this… the question that really should be asked is: why isn’t the real-time loading process sending data directly to the data vault in the first place? what are the drivers/needs to place the data in to the staging area before feeding it to the data vault?
if it’s for reasons of backup and restore, then yes – the staging area can exist for a rolling backup (hot backup) piece and it makes sense, if it’s to join the data with other information, then yes – landing in a staging area is a good idea.
i would suggest the following solution:
run the data to the staging area for backup, but in parallel, run the data directly in to the data vault without stopping at the staging area; this way no triggers are necessary. if however, you have requirements that dictate the need to stop in a staging area anyhow, then it would be ok to use triggers on the staging tables to move the transactions in to the data vault at the time of arrival.
anyone else have thoughts on this process?