CODEWITHSUNDEEP
etl
MaatDeamon
MaatDeamon

Reputation: 9761

How to implement an ETL Process

I would like to implement a synchronization between a source SQL base database and a target TripleStore.

However for matter of simplicity let say simply 2 databases. I wonder what approaches to use to have every change in the source database replicated in the target database. More specifically, I would like that each time some row changes in the source database that this can be seen by a process that will read the changes and populate the target database accordingly while applying some transformation in the middle.

I have seen suggestion around the mechanism of notification that can be available in the database, or building tables such that changes can be tracked (meaning doing it manually) and have the process polling it at different intervals, or the usage of Logs (change data capture, etc...)

I'm seriously puzzle about all of this. I wonder if anyone could give some guidance and explanation about the different approaches with respect to my objective. Meaning: name of methods and where to look.

My organization mostly uses: Postgres and Oracle database.

I have to take relational data and transform them in RDF so as to store them in a triplestore and keep that triplestore constantly synchronized with the data is the SQL Store.

Please,

Many thanks

PS:

A clarification between ETL and replication techniques as in Change Data capture, with respect to my overall objective would be appreciated.

Again i need to make sense of the subject, know what are the methods, so i can further start digging for myself. So far i have understood that CDC is the new way to go.

Upvotes: 0

Views: 1477

Answers (1)

Dirk Trilsbeek
Dirk Trilsbeek

Reputation: 6023

Assuming you can't use replication and you need to use some kind of ETL process to actually extract, transform and load all changes to the destination database, you could use insert, update and delete triggers to fill a (manually created) audit table. Columns GeneratedId, TableName, RowId, Action (insert, update, delete) and a boolean value to determine if your ETL process has already processed this change. Use that table to get all the changed rows in your database and transport them to the destination database. Then delete the processed rows from the audit table so that it doesn't grow too big. How often you have to run the ETL process depends on the amount of changes occurring in the source database.

Upvotes: 2

Related Questions