Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I am looking for patterns that will allow us to solve for following use cases-
many source tables->one target table
many source tables ( across multiple databases)->one target table
*the source and target data models are different
*source is oracle, target is sql
thanks,
Hi Duma,
Okay, if there are different tables on different sources and you are going to replicate them into a single target that should work out fine.
Just create a task for each source and use the same target endpoint.
Thanks,
Michael
I don't see the problem. You'll need one task per distinct source db. You can fold multiple tables into one target but of course you have to use transformations to get to a common target data model. If that gets too tricky then you can use a staging table and use target procedures to sort out complex data model issues such as 1 row into 2. Sometimes, as pointed out, a change-table becomes the only way to handle the 'merge' on the target side, notably to handle PK transformations/collisions.
One thing is for sure, you must take the utmost care to have all task touching the shared target table be set to 'do nothing' on full load other wise any task full-load accidental or not, will blow away all data - not just the data which originated from that task. An alternative to the 'do nothing' is a clever private SQL syntax redefining TRUNCATE to become a selective delete for the data originated by the task.... not the easiest thing to do, and even harder to maintain.
Good luck,
Hein