External Objects: Lessons learnt (A list of gotcha's that could be useful to know before you start your implementation)

Salesforce connect or External Objects are promised by Salesforce to provide seamless integration to external data without the need for copying that data into your org. Salesforce recommends external objects when the traditional approach of copying data into your org using a Extract, Transform and Load (ETL) tool is not enough. The reasons could be amount of data that with latent data that is not used all the time, and near realtime access to data from the external system.

The one undeniable advantage of external objects is seamless integration with standard out of the box salesforce tools such as global search, lookup relationships (really useful in building business logic), reports and dashboards, standard SOAP and REST APIs.

Overall external objects provide a great tool to for Salesforce Developers to integrate external systems into their solutions without much if any assistance from middle ware tools. But there are certain considerations that solution architects and developers should be aware of before choosing external objects for your next integration project. Below I have compiled a list (in no particular order) that is mentioned in the documentation but might not become apparent until it is encountered in testing or worse in production.

Database.insertAsync() methods can't be executed in the context of a portal user

Lets say you have done all the work to make your interactions with external objects asynchronous with callback methods to handle results and you are ready to roll out. Your solution is served internally as well as through the community. As a developer you test your solution internally and assume it will work the same served through a community. You would be wrong you will have to change your solution and result handling to use Database.insertImmediate() instead :(.

Batch Apex on External Objects using the standard way of Database.QueryLocator doesn't work out of the box

In order to use Database.QueryLocator on a Batchable on an external object the external data source needs to have the "Request Row Counts" enabled and the external data source needs to provide the row count with every response for the total result set returned for each query. Easier way is to use an iterable batch apex job, since the data is queried and stored in salesforce until the batch job finishes and then deleted. 

Maximum new rows retrieved or created per hour of 50000 records

There is a limit of 50000 new records queried or created per hour through all external objects and external data sources. When you consider this limit it doesn't seem like a lot but it is important to notice the use of the word "new" in the limit. Once a record is queried once or once it is created it no longer counts against that limit. And since the limit is per hour it will be very hard to reach this limit in steady state operation.

But there are cases when this limit can be reached and cause issues. Two prime cases where this can happen are if you are batching (querying) on too many new external object records from an external system or if a large volume of users are accessing external object records at the same time (in this situation the users would have to access completely different result sets to see an issue). 

Solution for batch jobs: Don't run batch jobs in salesforce on external object records do the batch changes to the external object records in the external system. If you are using the external object to  update other internal (custom or standard objects) it is better to handle that in the middle ware and then update/create the records back in your salesforce org.

For the case when a you have a rollout when multiple new users might access/create more than the limit of external object records. It might be worthwhile to prime the records already in the external system for the rollout. This can be done by running a batch job (just for this occasion) to query 50000 records at a time from the external system at an hourly interval until all records in the external system are queried. Since the records are already accessed they won't count against the limit and make hitting the limit more unlikely.

SOQL relationship queries (joins) make multiple callouts to the external data source to get results for the query

When you perform relationship queries (joins) on external objects with external lookup relationships there are multiple callouts to the external system to retrieve the data. This definitely affects response times and based on the complexity of the query can be above accepted (Service Level Agreement). 

In the above screenshots you can see that 7 additional callouts were made to the external data source to fetch child (orderdetail) records but the counts for the two external objects do not indicate that many additional callouts will be needed to fetch the records. 

Remedy: If you have control over your external data sources and costs are not too much of a concern then consolidating summarized data in a view (materialized or normal) on the database and mapping that as a separate external object might do the trick. But if that is not an option the try avoiding relationship queries and proactively load parent data when it is anticipated that child data will be queried. 

Conclusion

It is important to be aware of the above considerations along with other considerations mentioned in this link if you are considering external objects for your project. I will keep updating this article with additional consideration which I consider as "gotchas" (might not be apparent). Please feel free to comment on send me a note if you have encountered any additional ones you might consider as gotchas too.