Skip to main content

Delete records in a data synchronization

See also the design patterns for the Qlik Application Automation for OEM CDP, where we describe how to delete records when using the CDP.

Rules when deleting records

Deleting records should be handled with care. You need to e.g. make sure that you do not delete records that were created outside the scope of the integration. For example: when a contact is deleted in a source CRM with the email address john@doe.com, this does not always mean that contact with the same email address should be deleted in the destination CRM because this contact might have been created outside the scope of the current integration.

As a rule of thumb, only delete records in a destination platform, that was created by the same integration. Always use a hard match based on id to delete a record (so-called external id), and not a fuzzy match such as an email address or VAT number for a company.

IMPORTANT:

  • Add a check in the below automations to stop the automation when the number of records to delete is unexpectedly high (e.g. count > 10). This is to make sure that no records are deleted if for some reason the source block list records does not return any results (e.g. temporary issue with the API of the source platform).
  • Set the list block's error handling to Stop on error to make sure that no records are deleted by accident.
  • Our advice is to be extremely careful when implementing automations that delete records.

Delete record on webhook event Record deleted

Use the On delete webhook event from the source platform, to delete records in the destination:

Deleting records.

an automation consisting of an On Contact Deleted block and a Delete Contact block.

Note: make sure to store the id from the destination in the source platform (in a custom) field or vice versa, so that you can delete the exact record in the destination, based on its id.

If this is not possible, use the Qlik Application Automation for OEM data store to keep track of external ids (insert a record in the Data Store for each creation of a new record in the destination). Example:

Keeping track of ids with a datastore.

an automation consisting of a List New And Updated Contacts Incrementally block containing an Add Or Update Contact By Email block and an Add Item block.

Delete records based on a compare

If the source platform does not provide on delete webhook events, you will have to implement a compare automation that compares all records from the source with all records from the destination. Records from the destination that are missing from the source can be deleted in the destination. Example:

Comparing records.

an automation consisting of two List Contacts blocks and a Compare Lists block containing a Delete Contact block.

We are assuming that the id of the source is stored in the destination as an external id in a custom field, and this field is used in the compare:

Comparing an external id.

The Settings tab of the Compare Lists block. List 1 Unique Key is set to customfields.source_id, List 2 Unique Key is set to id, and Compare Results is set to Unique in list 1.

Note: this pattern is only valid when the destination platform is a slave of the source platform (master). In other words, we assume that no records are created by anyone or any other process because they would be deleted based on this pattern.

A solution is to filter the list of the destination on e.g. a custom field that holds the external id from the source. In the Compare list block, the filtered list is then used to compare the source and destination. If this is not possible, use the Data Store (see below).

Delete records based on a compare - using the Data Store

If it is not possible to store the id of the source in the destination (external id), the Data Store can be used to keep track of records that were created in the destination (see above). In this case, the compare is done between the source platform and the Data Store:

Comparing between a source platform and datastore.

an automation consisting of a List Contacts block, a List Items block, and a Compare Lists block containing a Delete Contact block and a Delete Item block.

Note that the item is also deleted from the Data Store when it is deleted in the destination.