7

We would like to keep Salesforce synced with data from our organization's back-end. The organizational data gets updated by nightly batch processes, so "real-time" syncing to Salesforce isn't in view. We intend to refresh Salesforce nightly, after our batch processes complete.

We will have somewhere around 1 million records in Salesforce (some are Accounts, some are Contacts, and some belong to custom objects).

We want the refresh to be efficient, so it would be nice to send only updated records to Salesforce. One thought is to use Salesforce's Bulk API to first get all records, then compare to our data, and only send updated records to Salesforce. But this might be an expensive GET.

Another thought is to just send all 1 million records through the Bulk API as upserts to Salesforce - as a "full refresh".

What we'd like to avoid is the burden/complexity of keeping track of what's in Salesforce ourselves (i.e. tables that attempt to reflect what's in Salesforce, so that we can determine the changes to send to Salesforce).

Thoughts/recommendations? Many thanks in advance.

6

You can use External ID fields to track the primary key (PK) of the external system in salesforce.com. Then, each night, you simply scan your backend for any changes, and use the upsert function to update the related records.

Assuming this is intended as a one-way synchronization with the backend as the final authority on what data is "correct", this is a simple two step process: (1) query the backend, and (2) update salesforce.com.

Of course, with this system in place, you could decide to batch submit all 1,000,000+ records from the backend, and salesforce.com will automatically sort out the details for you. Note that the backend data will replace the salesforce.com data for any fields submitted for update if you blindly post updates.

I would advise against querying for a million rows of data just to do a comparison and post back. It would be far more efficient to ask the external system what's changed, and post those changes to salesforce.com using the upsert function call, unless, of course, the system is itself incapable of determining what changes have occurred since the last nightly batch.

Using External ID values, posting all the data as a full refresh would still be far faster than an all row retrieve, comparison, then update of all records that have changed (the query/post method is 1,000,000 rows plus changed data, the upsert method is 1,000,000 rows without any extras, plus removing the need for client-side processing).

| improve this answer | |
  • Thx @sfdcfox! Right, this is a 1-way sync from our backend to Saleforce, and in general we don't have a great way to ask our backend what data has changed. (Even if we knew what changed in the backend between yesterday & today and sent this to Salesforce, this is flawed because what if yesterday's transmission to Salesforce failed for any reason ... those gaps will never be filled.) I thought that doing a full query from Salesforce and checking for deltas ourselves before upserting would be most efficient - but it seems you're saying that a simple full upsert to Salesforce is best. – Dave Clausen Nov 19 '13 at 15:20
  • 1
    Just using upsert as opposed to query/upsert will reduce bandwidth usage by 50%, making it more efficient by a large margin, especially if you're using an ISP that complains when you use more than a couple hundred gigabytes a month. This only requires a one-time commitment to match up the records in salesforce, and going forward, you'll not have to go through that again. – sfdcfox Nov 19 '13 at 15:31
  • Thanks again. When Salesforce receives an upsert that requires no change to the record, I wonder if Salesforce is smart enough to (mostly) ignore this and not take the time to write a record-update to disk... I'm probably thinking too hard now. :) Anyway, thanks once again! – Dave Clausen Nov 19 '13 at 15:51
  • 2
    The triggers would still execute, etc, but it's really not any of our concern, per se. Using the Bulk API reduces the problem though by letting the platform offload updates into a queue that will run as resources permit. – sfdcfox Nov 19 '13 at 15:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.