Authors: kubilay Skip navigation

Cloud Data Integration

2 Posts authored by: kubilay

It is easy to load data to an empty Salesforce Org. Often this can be achieved with minimum effort and using native Salesforce tools like Data Loader or the free Informatica Cloud Data Loader for Salesforce. But, what approach should be taken if the project requires continuous synchronization between the external data sources like other cloud applications, on-premise applications, databases or CSV files and Salesforce?

 

This can be done by using a special field in the Salesforce object called the External ID. Combining the UPDATE and INSERT tasks to create UPSERT, an Informatica Cloud UPSERT task can be used with the Salesforce External ID, populated with a unique value from the external data source system, to keep the data sources and Salesforce synchronized.

 

Salesforce Delete Problem

 

Unfortunately, the Salesforce application programming interface (API) does not permit you to delete records in Salesforce, unless you know their original Salesforce ID. That is, an External ID from an external system is not enough to be able to delete records in Salesforce. Only if you know the Salesforce ID of a record you can delete it in Salesforce.

 

This can be a major obstacle if you are looking to build data synchronization interfaces - which include deletes - between tyour data sources and Salesforce. Despair not, there is a solution!.

 

Let’s walk through how we can implement a delete operation between a CSV (comma-separated values) file and Salesforce using Informatica Cloud and a SQL staging database.


Solution

 

Imagine you are given a CSV file F every night and you are told to refresh your Salesforce data with it and the ‘refresh’ requires you to do an INSERT, UPDATE and a DELETE.

 

Next, you can quickly set up an Informatica Cloud task where you can read form F and UPSERT into Salesforce custom object C via your chosen External ID field as seen below (External ID in red).

1.jpg

Job done; but what about a DELETE?

 

We now understand that UPSERT handles only updates of existing records and inserts of new records and that it doesn’t delete records.

 

So, how would it be possible to delete a record from Salesforce if on Day 2 your CSV file F was delivered like below with the record ‘Mary’ - ‘002’ now deleted and you were expected to reflect this change to Salesforce?

 

How would you delete?

2.jpg

There is an easy way to overcome this with a staging database. By introducing a SQL staging database where you will load data from both sources, you can then perform an outer join and use the original Salesforce ID to delete the record. [1]

 

Two Informatica tasks can be used to extract data from the source and target into the staging database tables F,C like this:

3.jpg

Once the data is extracted in the database, then with a RIGHT OUTER JOIN view as in the statement above we can join the tables F,C and obtain the data set which will provide us the Salesforce ID (in red). This database view will also indicate by the presence of NULL values in the columns of the table F the fact that data has been deleted. The result of the above SQL in the staging database would look like below:

4.png

Now, with the above database view from the Staging DB as a source in an Informatica Cloud task, we can easily delete the Salesforce record. All we have to do is to create the Informatica Cloud task with a data filter on F.F_ID where we will select only records which have NULL F.F_ID column. By doing this, the returned set will only contain data which has been deleted in the file but now with the Salesforce ID of the deleted record.

5.png

The above database view with the NULL filter is now our delete task in Informatica Cloud. This view contains the Salesforce ID of the deleted record enabling you to delete a record in Salesforce.

6.png

 

The above solution can be easily automated with an Informatica Cloud schedule and task flow. An extract of the full Salesforce custom object C and the load of it to the staging database table C can be automated with an Informatica Cloud schedule, the same way we can automate the load of the CSV file F into the staging database table F. The database view V will always contain records which have been deleted. Propagating this view to Salesforce with an Informatica task will keep Salesforce synchronized with the CSV file at all times and it will also delete records which have been deleted in the CSV file from Salesforce automatically.