I've only been involved with such Salesforce integrations at one company, but based on that, I can give you my take. I'd be interested to hear others'.
One, the salesforce data changes all occur through APIs (ok) which various enterprise integration tools (Informatica, Mulesoft, etc) support (ok), but those tools typically dont support easy options for retrying a change to a specific row that causes an error. If you are updating 100 Accounts in a single Salesforce Bulk API call and "5" are busy/locked, you have to build a lot of custom error handling to notice and retry just those 5 rows. Its not part of most connectors or python libraries I've seen. Also, 3 of those errors might be fatal and 2 might be retriable but you have to learn which are which or blindly retry them all. In database terms, their API transactional semantics are not statement by statement ACID but row by row within an API request.
Second, no API or SOQL operations can pull back or process or update more than 50,000 rows.
Given those two things, unless the integration person is skilled about both error handling and testing, some of the object busy/contention failures only show up in production traffic with many jobs going on so a generic integration specialist doesn't know about these Salesforce-specific pitfalls and they are discovered after the integration goes live under strange production access patterns.
EDIT: a third issue is that most Salesforce developers are UI-centric in their thinking and training and don't have database or data modeling or data integration experience to draw on so the troubleshooting for data issues on their end tends to suffer.
I have been there and done that. In a complex SF org with a lot of triggers, any record update will get blocked.
The only solution is to refactor all the apex triggers processes and flows to something more orderly. Technically it is doable. Politically it is almost impossible. SF is an ERP in most companies and touches every department.
The regular REST api has request scoped transactions. Only the bulk api has the issues you describe. The bulk api is kind of a special thing annyway and has its quirks. The regular REST api works more like what you’d expect.
Great point. That said, the remaining problem is that regular API calls dont scale/perform well for integrations between systems involving lots of data syncing. A few records? Fine. Many? Not so much.
Yeah pushing or pulling large, according to salesforce, numbers of records is definitely harder than it should be. On the “push” side you have row lock errors and no way to disable APEX based triggers unless you’ve designed that into the code itself. On the pull side, if you’re trying to extract a large number of records and your soql query times out you’re out of luck. SF is good about creating custom indexes for you through a support case but it takes time. Even then, on the order of millions of records it’s still difficult.
My day job is implementing large SF projects. Multi-million record data migrations arent unusual. Even if the data is clean and mapped the migrations take weeks of planning. We go over every inch of the setup to make sure we have the best chance of getting a clean load on try #1. However, we schedule for 3 trial loads and verification before a “go live load” into actual production. Even after all that it’s still an all nighter with contingency plans and c-suite cell numbers on deck.
One, the salesforce data changes all occur through APIs (ok) which various enterprise integration tools (Informatica, Mulesoft, etc) support (ok), but those tools typically dont support easy options for retrying a change to a specific row that causes an error. If you are updating 100 Accounts in a single Salesforce Bulk API call and "5" are busy/locked, you have to build a lot of custom error handling to notice and retry just those 5 rows. Its not part of most connectors or python libraries I've seen. Also, 3 of those errors might be fatal and 2 might be retriable but you have to learn which are which or blindly retry them all. In database terms, their API transactional semantics are not statement by statement ACID but row by row within an API request.
Second, no API or SOQL operations can pull back or process or update more than 50,000 rows.
Given those two things, unless the integration person is skilled about both error handling and testing, some of the object busy/contention failures only show up in production traffic with many jobs going on so a generic integration specialist doesn't know about these Salesforce-specific pitfalls and they are discovered after the integration goes live under strange production access patterns.
EDIT: a third issue is that most Salesforce developers are UI-centric in their thinking and training and don't have database or data modeling or data integration experience to draw on so the troubleshooting for data issues on their end tends to suffer.