CDC and ETL help/recommendations

Question

Here's the background.

We have a few different customers, each with a different backend source database. We want to be back to pick up real time changes to the backend database, then transform the data to a target schema in our target database. After that broadcast a message to other apps alerting the change.

To do this we need CDC software for the change detection and then a good ETL tool to transform and load the data into the target database.

So far we have looked at IBM's CDC. Research is still in the preliminary stages but I just wanted to get other people's take on this. What programs do you recommend for this? Are we even going about this the right way...meaning is CDC software overkill?

Answer 1

Most databases have some sort of built-in CDC mechanism(Oracle does for sure). I would consider applying the Pub/Sub pattern and making each DB a publisher and have the target subscribe to all of changes. Other apps could also subscribe to further notifications. NServiceBus is a good option for this kind of communication. If you don't want to use a native DB CDC mechanism then you could do something primitive and build something to detect changes per DB vendor(triggers, inspecting redo logs, or other).

Answer 2

I am part of the CDC sales team for IBM - if you wish we can arrange some time to speak - if you are still in the market.