Pages

2010/04/19

Synchronizing data between NetSuite and other systems


NetSuite truly is a complete business system, but every so often we have clients who use a company built database to store their customer data. They need to synchronize that data with their NetSuite instance.

The Talend Integration Suite comes with a CDC (Change Data Capture) component that was designed for enterprise-grade data synchronizations. We find it to be an extremely reliable tool. 

For the purposes of this post, we’ll have a look at the CDC component for synchronizing a PostgreSQL and NetSuite database. (NOTE: There is a CDC component available for most databases).

Talend will create a CDC table and a CDC view for each source table that it is fetching information from. Triggers will be set to watch for inserted, updated or deleted records. Multiple subscribers can watch for these events, that’s why there’s also a table created to manage subscribers.

This configuration is a one-time set-up. Once it’s done, the CDC component will read from the CDC tables associated to the source tables and extract all insertions, updates and deletions. 

These events are then transmitted to your middleware workflow so that it ends up in NetSuite, the target system.
Problems happen when the source schema is modified. For example, if a field is renamed, the CDC component needs to be adjusted, or else the middleware workflow will cease to function. But for a schema that is likely to be fixed over time, this component is very useful.

Let’s give it a dry run. Assuming that my source database is now in sync with my target system, the database will look like this.

A record is inserted (record 3), two updates are done (record 2) and a one record is deleted (record 1). The source database now looks like this:


The events are logged in the CDC table, and when the connector is run, the following output is produced (see image below). For the sake of the demonstration, the modified data is simply outputted on the screen, but it could be handled in many other different ways:


We see that the new information (record 3), the deleted information (record 1) and the updates for a given record (2) are properly outputted from the CDC component!

I hope that the overview of this tool will be of use to you!

No comments:

Post a Comment