Rolling Up Opportunity Contact Roles with Change Data Capture and Async Apex Triggers25 Jun 2019
Salesforce admins, and perhaps especially nonprofit admins, have been wishing for a long time for the ability to build more functionality around Opportunity Contact Roles - like roll-up summary fields, validation rules, and triggers.
The new Change Data Capture offered intriguing possibilities because, unlike regular Apex triggers, the feature supports change notifications for
OpportunityContactRole. With the new ability, in Summer ‘19, to subscribe to Change Data Capture events in Async Apex Triggers, we now have a viable route to build completely on-platform features using these tools - and the first demo I wanted to build was a solution to roll up Opportunity Contact Roles.
If you want to drive straight into the code, find it here on GitHub.
The Simple Route and Why It Doesn’t Work
My first strategy was simply to call into Declarative Lookup Rollup Summaries from an Async Apex trigger. That approach runs afoul, though, of one of the key differences between Async Triggers and standard Apex Triggers:
Apex triggers operate on sObjects. Async Apex triggers operate on change events.
Change events aren’t sObjects, and don’t contain a complete snapshot of the sObject record at a point in time. Rather, since the underlying Change Data Capture feature targets synchronization of changes to some persistent store, each event only contains enough information to apply a specific change to a stored record.
The section Apex Change Event Messages in the Change Data Capture Developer Guide explains what data is actually available in each message:
For a new record, the event message contains all fields, whether populated or empty. […]
For an updated record, the event message contains field values only for changed fields. Unchanged fields are present and empty (null), even if they contain a value in the record. […]
For a deleted record, all record fields in the event message are empty (null).
For an undeleted record, the event message contains all fields from the original record, including empty (null) fields and system fields.
For roll-up applications, the key is the
delete events. Note that for
update, we don’t get old data, only new, and at the time we receive the event the records in the database have already been updated (we can’t query for old values). Upon
delete, we don’t get the field data for the deleted sObjects at all! It’s assumed that we, as consumers of this event stream, have some other persistent data store against which we’re applying the ordered stream of change events.
To build a roll-up, we need to know the parent objects for deleted children, the parent objects for changed records, and the old parents for children that are reparented.
So Change Data Capture + Async Apex Triggers is a dead end for building rollups? Not at all. There’s another strategy that allows us to take advantage of Change Data Capture’s inherent facility for synchronizing data stores: we’ll use a shadow table.
Rolling Up a Shadow Table
The SFDX project for everything we’re going to demonstrate here is available on GitHub.
We can’t roll up Opportunity Contact Roles to the Opportunity, but we easily can roll up some arbitrary Custom Object with a master-detail relationship. A shadow table, in which we mirror the Opportunity Contact Role object with a Custom Object, fits nicely as a solution: it both gives us the roll up we need without code, and provides us with an on-platform persistent data store against which Change Data Capture events can be applied as we create, update, and delete Opportunity Contact Role records.
Here’s the schema we’ll use: a custom object plus a native Rollup Summary Field on Opportunity.
Note the External Id field we’ve created to hold the Id of the corresponding Opportunity Contact Role. That’s the linchpin of our synchronization effort, and we’ll use it below to build an efficient sync trigger.
Synchronizing the Data
To synchronize data from
OpportunityContactRole into our shadow table, we’ll use an Async Apex trigger processing the
OpportunityContactRoleChangeEvent entity. First, we’ll select the needed object in Setup under Change Data Capture:
Then, we build a trigger. The code is here.
Our trigger’s operation is different from what we’d see in typical, synchronous Apex. We iterate in order over the change events we receive and use them to build up two data sets. The first is a
Map<Id, Shadow_Opportunity_Contact_Role__c>, which we use to store both new and updated shadow records derived from the change events. The keys, worth noting, are
OpportunityContactRole Ids, ensuring that we create or update exactly one record for each
OpportunityContactRole whose state incorporates all of the changes in our inbound event stream.
We also store a
Set<Id> of the Ids of deleted
OpportunityContactRole records. As we find delete events in our stream, we remove corresponding entries from our create and update
Map, and add those Ids to the
When we finish iterating through events - remember that this is an ordered time stream - these two data structures contain the union of all of the changes we need to apply to our shadow table. At that point, it’s two simple DML statements to persist the changes:
upsert createUpdateMap.values() Opportunity_Contact_Role_Id__c; delete [ SELECT Id FROM Shadow_Opportunity_Contact_Role__c WHERE Opportunity_Contact_Role_Id__c IN :deleteIds ];
The index on
Opportunity_Contact_Role_Id__c should keep these operations performant, and once they complete, the system updates our native Rollup Summary Field on the parent Opportunities.
There’s just a couple of extra wrinkles to testing Async Apex Triggers. We have a new method in the system
Test class to enable the Change Data Capture feature, and it overrides system CDC settings to ensure that the code under test executes regardless of org settings:
Then, we require that CDC events are delivered and processed synchronously, using the tried-and-true
Test.stopTest() calls, or by calling
Intermediate testing results, while working towards passage and full code coverage, can be tough to interpret: most logs are found under the
Automated Process user rather than the context user, requiring the use of trace flags, but some (possibly those from
@testSetup methods) do appear for the context user. Code coverage maps can also produce misleading results until full passage is achieved.
As part of the demo, I built a test class that achieves full coverage on the Async Apex Trigger. It’s also part of the GitHub project. (While the tests have good logic path coverage, they could stand to exercise bulk use cases better!)
The CDC + Async Apex Trigger solution doesn’t add everything we might want with Opportunity Contact Roles. We still cannot write Validation Rules against the Opportunity that take Roles into account, because the rollup operation is run asynchronously, after the original transaction commits. It’s also a near-real time, rather than real time, solution, so a brief delay may be perceptible before the rollup field updates. And lastly, because Async Apex Triggers run as the Automated Process user, Last Modified By fields won’t show actual users’ names once the rollup operation completes.
But what it does, it does well: we get all the functionality and performance of native roll-up summary fields against an object that’s never supported that feature, with a minimal investment in code. Users can see, and report on, rolled-up totals of Opportunity Contact Roles across their Opportunity pipeline.
And it’s a really neat way to apply some of the latest Salesforce technologies to solve real-world problems.