Change replication using full refresh

Overview

Sometimes the simplest approach is the best one. If the source datasets are relatively small (100K or one hundred thousand records), it makes sense to consider a full refresh, an approach when the system polls all the records from the source and refreshes the destination. The records in the destination can be deleted or truncated prior to reloading, or the full refresh can be combined with the MERGE (UPSERT). The full refresh also makes sense when other options are not available.

Read about other change replication techniques available in the Etlworks.

Create a full refresh Flow

This example is for replicating data from one database to another, but the technique works exactly the same if the source, the target, or both is not a relational database. 

Step 1. Create a source database Connection.

Step 2. Create a destination database Connection. Typically all the default settings work just fine depending on the type of database where you might consider disabling or enabling Auto Commit . For example, if the target database is an SQL Server and you are planning to execute TRUNCATE TABLE SQL within the transaction, it can lock the table for an extended period of time.

Step 3. Start creating a Flow by selecting Database to database in the Gallery.

Step 4. Add a new source to destination transformation.

Step 5. If you want to DELETE or TRUNCATE all the records from the destination table before migrating the data from the source to the destination, follow the steps below; otherwise, go to step 6.

1. Click Mappings and select the Parameters tab.
2. Enter TRUNCATE or DELETE SQL in the Before SQL field.

Step 6. If you want to merge the existing data in the target with the data from the source, follow the steps below:

1. Change Action to MERGE, or if native MERGE is not supported, by the target database to IfExist.
2. In the Lookup Fields, enter a name or names of the columns which uniquely identify the record. PostgreSQL and MySQL require a unique index on these columns.