Clear or erase a table completely before syncing

  • 51Views
  • Last Post 20 November 2017
  • Topic Is Solved
0
votes
Michael Fielding posted this 18 November 2017

I have a table in PostgreSQL which I want to keep sync'd with MailChimp. I currently use the UPSERT operation and it works okay.

But when subscribers are removed from MailChimp, how do I ensure they're removed from the PostgreSQL table, please? I looked at using a DELETE task beforehand, but it needs to know the IDs to delete - obviously the opposite to what I want (the IDs I have are the ones to keep).

1
votes
Mariia Zaharova posted this 20 November 2017

Unfortunately, Import package does not provide the truncate table functionality.

 

Please specify what exactly you want to do: delete the corresponding record from PostgreSQL table when the subscriber is removed from MailChimp (unsubscribed or completely deleted?) or just update the Status column from Subscribed to Unsubscribed/Cleaned?

 

In case, if you want to delete records from PostgreSQL you can use Import package with the DELETE operation. For this, you should specify the filter condition at the first step: 

At the next steps you will need to select the target table and specify the mapping. DELETE operation requires the Id column to be mapped. If you have the corresponding Id column in your PostgreSQL table, please use general column mapping. If you do not have the corresponding Id column, you can use Lookup mapping for this. Refer to: https://skyvia.com/resources/docs/index.html?performing_update_and_delete.htm

In case, if you want to keep the record in your PostgreSQL, you can use Update/Upsert task, but do not forget to add mapping for the Status column.

 

Also, we recommend yo to take a look at the Replication and Synchronization packages. These packages will track deletes automatically.

Replication package is used to create a copy of cloud application data in a relational database and keep it up-to-date. With the Incremental Updates check box, after the initial replication, only the data that was changed in the cloud CRM is sent to the database: deleted rows are deleted from the database table, new records are inserted and the changed records are modified.

Refer to: https://skyvia.com/resources/docs/index.html?replication.htm

 

Synchronization package. Skyvia synchronization operation is used to synchronize data between cloud CRMs and relational databases in both directions. It allows defining a synchronization operation as a package once, and then scheduling it for periodical execution to keep the data in different sources in sync automatically.

More information is available here: 

https://skyvia.com/resources/docs/index.html?synchronization_overview.htm

 

Please tell us if this information helps.

  • Supported by
  • Michael Fielding
Close