Dynamic Queries on Dynamic Objects at Volume

Question

We are trying to create a mechanism that would process user dynamically created queries and create "Contributors" for each matching record.

The object model is as follows:

Account -< PO Number(s) -< PO Configurations

A PO Configuration is per Object Type (Shift, Expense, Timesheet, Deduction ect) and holds a JSON object that compiles into a SOQL query to match records. These configurations are created by an expressions UI that allows quite complex filters to be applied with AND/OR relationships between groups and conditions.

A PO Number can have multiple configurations for multiple different objects; the mechanism then expected to "walk through" each active PO Number on the system (potentially thousands) and execute all user-defined related queries - we expect a PO Number to have 5-10 such queries (possibly less).

When the mechanism executes the queries, it will create a "Burn Contribution" that links to the PO Number and keeps the id of the returned record in a text field.

For context, this is what the UI looks like:

And this is what the dynamic filter configurator looks like:

And this is what a Burn Configuration record looks like:

Now, as you might guess, we are playing around with potentially huge amounts of data and the necessity to execute SOQL within a for loop (in order to get all records for dynamically created configurations/conditions).

So the design is Calculator class is executed, assuming that we need to process all PO Numbers, we will launch a Batch Class that queries for all active PO Numbers and processes them 5 at a time. For each PO Number, we will launch a queueable that will execute 5 PO Configuration records at a time (i.e. at least 5 SOQL queries per queueable).

Class -> Batch (For PO Numbers) -> Queueable (for each configuration/soql)

Now, I know that we have 50 jobs limit and we can circumvent that by calling a queueable from a future and vice versa, but I am worried about the above design - we have yet to deal with queries that return hundreds of records within the queueable - I would ideally want to process them in Batches, but I am not sure how to call a batch with a dynamic SOQL query - generated within a for loop.

There are loads of limits that we can hit with the current design and although it works as a POC, I am perfectly aware that I need to re-think how to process all these records.

Lastly, we use Custom Metadata to define: What object is supported to create Burn Contributors, which field on that object provides the Burn Value, which field provides the date against which we match for PO Number suitability and finally which field provides Account matching.

My question is:

How would you best design the framework so that: a) You process hundreds of PO Numbers per operation (can be a nightly job) b) You assume that each PO Number has multiple PO Configs that result in multiple SOQL queries across mixed objects, not hardcoded c) You assume that each PO Config SOQL may return thousands of records to be processed d) You assume that the mechanism allows through configuration to add support for calculation for a new object (through Custom Metadata and then User adding conditions from UI) and thus the mechanism has to remain completely flexible.

This is how we build the dynamic SOQL query for even more context:

//Convert JSON condition object to Apex Class
    SIM_Burn_Utils.Filter filter = SIM_Burn_Utils.convertFilterJSON(burnConfig.JSON_Data__c);
    if(filter == null){
        //Invalid JSON Data for Config - Do not process.
        return;
    }
    //Get the relevant burn matcher by Id
    SIM_PO_Burn_Matcher__mdt burnMatcher = mapMatchers.get(burnConfig.Burn_Matcher_ID__c);
    //Build WHERE clause from conditions
    String filterClause = SIM_FilterComponent.buildSOQLFilter(burnMatcher.Match_Object__r.QualifiedApiName, filter.crossGroupOperator, filter.groups);

    system.debug('Built filterClause: ' + filterClause);   
    //Select fields to query
    String stringSOQL = 'SELECT ID, ' + 
                        burnMatcher.Date_Provider_Field__r.QualifiedApiName + ', ' + 
                        burnMatcher.Value_Provider_Field__r.QualifiedApiName + ', ' + 
                        burnMatcher.Account_Match_Provider__r.QualifiedApiName + ' ';
    //Select dynamic object
    stringSOQL += 'FROM '   + burnMatcher.Match_Object__r.QualifiedApiName + ' ';
    stringSOQL += 'WHERE '  + filterClause + ' ';
    //Add support for 15 and 18 character Id comparison. Needed where a formula returns an id 
    //E.g. Shift -> Client Id formula field is a text field of 15 chars - match burn contributor by client
    Id tempVar0 = burnConfig.PO_Number__r.Account__c;
    String tempVar1 = burnConfig.PO_Number__r.Account__c.to15();
    stringSOQL += 'AND '    + '(' + burnMatcher.Account_Match_Provider__r.QualifiedApiName + ' =: tempVar0 OR ' + burnMatcher.Account_Match_Provider__r.QualifiedApiName + ' =: tempVar1) ';
    Date tempVar2 = burnConfig.PO_Number__r.Start_Date__c;
    Date tempVar3 = burnConfig.PO_Number__r.End_Date__c;
    //Make sure we are within range
    stringSOQL += 'AND '    + '(' + burnMatcher.Date_Provider_Field__r.QualifiedApiName + ' >=: tempVar2 AND ' + burnMatcher.Date_Provider_Field__r.QualifiedApiName + ' <=: tempVar3) '; 
    stringSOQL += 'LIMIT 5000';

    system.debug('tempVar0: ' + tempVar0);
    system.debug('tempVar1: ' + tempVar1);
    system.debug('tempVar2: ' + tempVar2); 
    system.debug('tempVar3: ' + tempVar3); 
    system.debug('Built SOQL: ' + stringSOQL);  
    List<sObject> records;
    try{
        records = Database.query(stringSOQL);
        system.debug('Retreived Records: ' + records.size());   
    }catch(Exception e){
        system.debug('Error Querrying: ' + e.getMessage() + ' Stack: ' + e.getStackTraceString());
    }

And this is how I create contributors for each identified record

for(Id configId : this.recordsPerBurnConfig.keySet()){
        PO_Burn_Configuration__c burnConfig     = this.burnConfigsMap.get(configId);
        SIM_PO_Burn_Matcher__mdt burnMatcher    = this.mapMatchers.get(burnConfig.Burn_Matcher_ID__c);

        for(sObject record : this.recordsPerBurnConfig.get(configId)){
            List<PO_Burn_Contributor__c> oldContributors = oldBurnContributorsMap.containsKey(record.Id) ? oldBurnContributorsMap.get(record.Id) : null;
            //If the record already has contributors, overwrite the values
            PO_Burn_Contributor__c oldContributor = null;
            if(oldContributors != null){
                for(PO_Burn_Contributor__c oldC : oldContributors){
                    if(oldC.PO_Burn_Configuration__c == burnConfig.id){
                        oldContributor = oldC;
                        break;
                    }
                }
            }
            Decimal burnValue = (Decimal)record.get(burnMatcher.Value_Provider_Field__r.QualifiedApiName);

            if(burnValue == null){
                //If we already have a burn contributor for the record, queue to purge
                if(oldContributor != null){
                    contributorsToDelete.add(oldContributor);
                }
                //Do not create a null value contributor
                continue;
            }

            PO_Burn_Contributor__c newContributor = new PO_Burn_Contributor__c(
                id = oldContributor != null ? oldContributor.Id : null,    //Check if we need to update the record
                PO_Number__c = burnConfig.PO_Number__c,
                Date__c = (Date)record.get(burnMatcher.Date_Provider_Field__r.QualifiedApiName),
                Value__c = burnValue != null ? burnValue : 0,
                Is_Forecast__c = burnMatcher.Is_Forecast__c,
                Record_ID__c = record.id,
                PO_Burn_Configuration__c = burnConfig.id
            );
            system.debug('Creating new contributor: ' + newContributor);
            contributorsToUpsert.add(newContributor);
        }
    }

Answer 1

This is a tricky one, because a) it's rather an opinion-based question in a lot of ways and b) there are only so many design/implementation patterns available to you in the Salesforce context. It's likely none is ideal and you'll need some combination that satisfies your goals. Even then, expect edge and error cases to trip you up!

If I was doing this, and the code was able to handle Owner-setting for the created burn contributions, I would be tempted to start with a batch, as you suggest, that identifies the POs that need processing - this has the advantage of being able to scale to 50 million POs in principle. I'd then consider one of the following:

1. Have the batch create a Queueable that holds a list of the PO values still to process. This queueable would take (n, perhaps 1) POs from the start of the list, process them as needed to create the burn contributions, then chain passing in the remaining list (to allow this to work in a sandbox or scratch org too, you'll need to track the queueable depth and jump to a future method as needed). The main problem with this is that if the queueable hits an error your whole chain in broken (even with Transaction Finalizers, when they GA, you have no way to retrieve that remaining list and will have to effectively start again with the batch. This approach is likely not going to take you where you want to get.
2. Alternatively, have the batch create individual Platform Events, one (or n) per PO (the PO can be held in a field of the Platform Event itself; you could even have different events for different "object types" [shift, expense, timesheet etc.] using a second field, hence the "or n") and have an Apex-based Platform Event subscriber (a PE trigger), itself with a small chunk size. The event handler is then responsible for processing an individual PO to create the burn contributions. One of the nice things with this is that a failed event can be re-tried.

With both of these approaches you do have a problem - how to ensure that you don't spuriously process a given PO more than once (e.g. if two instances of the batches were created you don't want to generate two burn contributions from the same data). If your burn contributions can be uniquely identified you can guard against this via a generated external ID field or similar. If not, you need to think about how to prevent this somehow else.

Some other points of note:

• Check the performance of your Custom Metadata Type queries; we analyzed the performance (back around Summer '19 - I know schema performance has been improved recently) of Entity and Field Definition qualified API name resolution and it was shockingly poor. With just 40-50 CMT records with such querying the metadata loading was taking many 100s of milliseconds alone and this can cripple you before you're even started.
• Even though Platform Events appear to be asynchronously processed, the Apex-based subscriber implementation trigger invocations count as synchronous processing. This is great, on the one hand, because you don't burn (pardon the pun) your daily limits. On the other hand, however, the processing in the trigger is constrained by synchronous, rather than asynchronous, limits. That's why I suggest setting your chunk size to a very low (possibly singular) number.