Process billions of records with Async SOQL

Christmas time is closer but I would like to deliver this last post before end of the year.

Some months ago, I wrote an entry talking about BigObjects, a feature that was General Available by Winter ’18 and via a use case, I tried to explain it. Now, I would like to follow that post with a new way to create records, Async SOQL.

Async SOQL is half GA by Winter ’18 and half still in Pilot 

The use case talked about moving Code Review custom object records, into Code Review History big objects records, and release storage of custom objects one. We want to Archive records, and Async SOQL will help us to deal with big amounts of data.

What is Async SOQL?

Basically Async SOQL allows you to run SOQL in the background so that, you will get a response after a period of time, but at the same time, helps you to deal with millions or even billions of records without hitting time outs or governor limits.

How can I execute Async SOQL?

Async SOQL is implemented as REST full API and in order to execute this SOQL in the background we need to run a post request:

Captura de pantalla 2017-11-29 a las 21.51.22

And provide a body in JSON format in order to make the call. Find bellow a really easy example that helps you to create Vendor__c custom object records based on Accounts.

Captura de pantalla 2017-12-19 a las 9.40.12What can we highlight?

query: allows you to define a SOQL. On our case, the object I’m going to read, Account and the field I want to recover, Name.

operation: help us to define what we want to do, insert or upsert (please check big objects entry to understand Primary Key and upsert, as it works in the same way)

Basically that is the main difference between simple SOQL and Async SOQL. With this new feature we can read and create or update records in one go.

targetobject: object we want to use to create new records. In our case, Vendor__c.

Then, we need to specify from where we want to get record field values and where we want to store this info. For that we have 2 keys in this JSON code.

targetFieldMapping: allows you to define source field and target fields


targetValueMap: allows you to map a target field with a literal value, for instance, we want all records have Spain as Country__c field value.

How is the response?

When we do the post call, we get a similar response:

Captura de pantalla 2017-11-29 a las 22.01.12

It is also in JSON format and basically it provides similar information that you passed in the post call. Just highlight 3 keys:

jobId: remember this is an asynchronous call so there is an Id related to the background job. You can also use it in the post call via the global variable $Job_Id as part of key value on targetValueMap.

status: helps you to know where is the job. Moving from New to Running, Complete, Failed etc.

message: before executing the call, the code is analyzed. If it realizes about a possible issue, it doesn’t start the call and provides an error message in that field. For instance, below message. My VendorName__c field size was too short to store all Account Names.

Captura de pantalla 2017-11-29 a las 22.11.03.png

How can I stop the execution?

Async SOQL doesn’t provide a UI in Salesforce like other background jobs like Batch Apex or Queueable. But we can make an http delete call passing the jobId as part of the url.

Captura de pantalla 2017-11-29 a las 22.22.02

How can I check the progress?

Similar as before, as we do not have UI, we need to look for another way in order to get this information. For that, we have 2 options:

1º – Make a get post call: Similar to the cancel action, if we pass the JobId as part of the url, and do a get call, the response shows you information abut the background execution that is running in the system.

Captura de pantalla 2017-11-29 a las 22.32.33

2º – Make a SOQL against BatckgroundOperation object: This is a new object where we can see extra information about the job execution.

Captura de pantalla 2017-11-29 a las 22.32.42

But this is not the only new object that we can get in the system. We also have BackgroundOperationResult that will help you to identify any issue during the execution if the result is not the expected one. For instance. Bellow image shows an issue related to a field that is required but the source value is empty. In that case, a new error is logged on this table, but the execution doesn’t stop, it continues till the end.

Just keep in mind that this information would be removed after 7 days.

Captura de pantalla 2017-11-29 a las 22.32.55

And what about Archiving?

Yes, you are right. We promised to show you how to archive Code Review records but tried to explain the whole functionality with a really simple use case. Now it’s time to move to Code Review History use case.

As I mentioned before, we would like to create Code Review History records reading Code Review records. Similar as before we will do a post call but the body would be like this one:

Captura de pantalla 2017-11-29 a las 22.51.40

On BigObjects post, the object was called Test1CodeReviewHistory__b instead of CodeReviewHistory__b

And … that’s all. Simple, isn’t it?

Can I integrate this functionality into Apex?

Yes, of course, at the end we are just making http calls, so we only need to set the proper url, add it on my remote settings and create a string in JSON format for the body.

Captura de pantalla 2017-11-29 a las 22.42.57


Finally I would like to sum up some key concepts we have talked about.

  1. Async SOQL allows you to run SOQL in the background
  2. It takes some times but allows you to process millions or even billions of records
  3. You do not need to worry about governor or time out limitations
  4. It is implemented as REST full API
  5. You can make as many calls as you want per day but just a single one at a time
  6. You can read and create records in one go. Delete is out of scope
  7. This feature is part GA and part still in Pilot:
    1. Read Standard or Custom objects and create Standard, Custom or Big Objects is in Pilot
    2. Read Big Objects and create Standard, Custom or Big Objects is GA

Captura de pantalla 2017-11-29 a las 22.43.44

Salesforce Elevate – Bilbao ’17

During Dreamforce ’17 Erika Kuhl, Holly Firestone and their team showed us how they are working really hard to get more content ready for our Developer and User Groups, and some other amazing ideas that can help us to get more attendees, like Global gathering events.

But in the meantime their ideas take effect, I had to think about something to do it now, as every single day, the number of guests at North Spain Developer User Group, is lower. And to run a second Elevate in Bilbao didn’t sound like a bad idea. And that’s what I did, prepare a 1 day event to talk about Salesforce.

What did I do?

First thing to keep in mind, the main objective is to get audience that could be interested to continue attending SDG talks … and where can I get this?

Facultad IngenieriaUniversity could be a good option. There are lot of students keen to know more about new technologies, solutions, and furthermore, that could be a good scenario to promote the open door that Salesforce can give them in their job career. And that is what I did, talked with the University of Deusto in order to run the event there.

Secondly, I needed some sponsors that could help me to support the event.

Salesforce Iberia, helped me with catering and Jon Ibáñez came to the event in order to talk about Salesforce platform. I really appreciated this introduction talk as it was really useful, even for me, that I’m on this world since almost 10 years ago.


NTS with Carlos Polo and Esteban Ugarte, was really helpful with catering expenses too, and also running a couple of sessions, like the one that Gorka run about Salesforce and Chatbots. Looking forward to know more about this topic, that allows you to create chats able to answer questions.

But I also needed some more help with sessions. FinancialForce was also an amazing help. Miguel Moro and Agustín Jimenez came from our Granada office to talk about the company itself, and also run a really good talk about Separation of Concerns (SoC) #qualitycode guide, that can help us to enhance our code and make it more readable and useful via the FinancialForce open source ApexLib.

Also, I wanted to provide a success case to the audience. Not only explain all benefit that Salesforce could bring to us. I wanted to show a real use case and I got it closer than expected. University of Deusto is a Salesforce customer too, and we had 2 good examples. First of all how they use Salesforce as a solution provider to the needs that the University can have. And secondly how they use and configure Salesforce without technical knowledge, with just #clicksnotcode.

Finally, the main goal of the event, promote other events, but we needed to start talking about Salesforce Community, our #ohana spirit , how we help each others, that we need some others to continue with this job, and how events like SDG or dreamin events could help. And guess what? As part of dreamOlé organizers, obviously I talked about this event that will be run in Barcelona next 27th of April.

 After a nice lunch…

We moved to the afternoon sessions, Chatbots one run by NTS and Process Automation with and without Code that I also ran.

And finally, time to dirty our hands working with Salesforce via Trailhead. Thanks Christie and your team to help me with this section and sending to me such nice stickers!!

What have I learnt on this event?

2 years ago I run a similar event but Trailhead just appeared few months ago and sessions we run were workshops to learn how to starts. Because of the low level of the content, some attendees complained. They were expecting something more than how to create an App with #clicksnotcode.

That made me think to prepare this Elevate with sessions with higher level, and Trailhead allowed newbies to start from scratch.

However, this year feedback was that it would be useful to have a quick overview of the platform, a demo or something similar as a first session just for starters, and then move to other sessions.

So keep in mind my feedback if you are thinking on running something similar for your group.

If you want to take a look at slides, find them here as well as some others. And don’t forget to join us at North Spain Developer User Group.  Next talks about Data Mining and Einstein, Javascript and Salesforce and working on a Salesforce DX workshop.

Thanks to everyone that helped me to make this Elevate event real