Referential integrity in generated test data means every lookup and master-detail field on a synthetic record points to a record that genuinely exists at insert time. It sounds obvious. It is also the single most common way bulk-generated Salesforce test data quietly falls apart, usually a week after the sandbox refresh, when a QA analyst opens a Case and finds an Account field pointing at nothing. Getting this right is not about the volume of records you create. It is about the order and the mapping you build before the first Bulk API v2 job ever fires.
Copying production data sidesteps this problem because the relationships already exist and travel with the records. Generating data from scratch does not get that luxury. You are building the graph yourself, object by object, and Salesforce will not tell you it is wrong until a batch fails or, worse, until it silently succeeds with a null lookup that nobody notices for months.
What Referential Integrity Actually Means Here
Every Salesforce object sits inside a web of relationships: lookups, master-detail links, hierarchical self-relationships on Account and Employee-style objects, and junction objects that exist purely to connect two other objects in a many-to-many pattern. Referential integrity means that web stays intact after you generate ten thousand fake Accounts, fifty thousand fake Contacts, and whatever Opportunities and Cases sit downstream of them.
It also means field-level consistency, not just row existence. A Contact"s AccountId has to point at an Account that was actually inserted, but the RecordTypeId on that Contact also has to correspond to a record type that is active for the Contact object in that specific org. Get one wrong and validation rules or page layout assignments break in ways that have nothing to do with the lookup itself.
Where Generated Data Breaks the Graph
Most integrity failures cluster around a handful of predictable spots. Self-relationships are the first: Account.ParentId, Contact.ReportsToId, and any custom hierarchy field where a record can reference another record of the same object. Generate Accounts in a single flat batch and there is nothing to point the ParentId at yet, unless you deliberately stage a second pass.
Junction objects are the second trap. An object like OpportunityContactRole or a custom junction between Projects and Resources needs both parent IDs to exist before the junction record inserts, and it often needs a specific combination to be unique. Generate the junction rows before both parents are committed and Bulk API v2 will reject the batch outright, or accept it and leave you with junction rows tied to placeholder IDs that were never real.
Cross-object formula fields and roll-up summaries are the third quiet failure point. They will not throw an error on insert, but they will return blank or zero values if the parent record referenced doesn't match what the formula expects, which then cascades into failed assertions in whatever test class or validation suite is reading that data.
Sequencing the Load: Build a Dependency Graph First
Before generating a single record, map the schema as a directed graph. Every object with a required lookup or master-detail field has an edge pointing to its parent object. Accounts have no required parent, so they load first. Contacts and Opportunities depend on Account, so they load second. OpportunityLineItems depend on both Opportunity and PricebookEntry, so they load third, and so on until every object with a dependency has its parents already sitting in the org.
This is a topological sort, the same algorithm used for build systems and package dependencies, applied to an org"s schema instead of a codebase. SproutEzee reads the schema through the metadata API specifically to build this graph automatically, because doing it by hand across an org with sixty custom objects is a good way to lose an afternoon and still miss an edge case.
Self-relationships need a second pass within the same object. Insert Accounts without ParentId first, capture the generated IDs, then run an update batch that populates ParentId for the subset of Accounts that should sit under a parent. Trying to do both in one pass forces you into placeholder logic that adds complexity for no real benefit.
External IDs Are the Backbone of Bulk API v2 Relationship Mapping
Salesforce record IDs do not exist until after insert, which creates an obvious chicken-and-egg problem for bulk-generated data: how do you set a Contact"s AccountId before you know what ID the Account will get? The answer is external ID fields and the relationship-by-external-ID syntax that Bulk API v2 supports on lookup and master-detail fields.
Tag every generated parent record with a unique external ID at creation time, something like a synthetic key that your generation tool controls rather than one Salesforce assigns. When you build the child batch, reference the parent through that external ID field instead of a raw Salesforce ID. Bulk API v2 resolves the relationship server-side during the load, which means you never have to round-trip the job to pull back generated IDs before building the next batch.
This matters more at volume. Round-tripping IDs between batches works fine for a thousand records. At five hundred thousand, the query-then-map-then-load cycle adds real time and real risk of a mismatch if any record in the first batch fails partial success. External ID resolution removes that entire step.
Master-Detail, Lookup, and Self-Referencing Fields Behave Differently
Not every relationship field enforces integrity the same way, and treating them identically is where a lot of generation scripts go wrong.
| Relationship Type | Integrity Behavior | Generation Implication |
|---|---|---|
| Master-Detail | Parent must exist before insert; child inherits sharing and can cascade-delete | Parent batch must fully complete before child batch starts |
| Lookup (required) | Parent must exist; no cascade delete by default | Same load-order requirement, but parent deletion won't remove children |
| Lookup (optional) | Can be null, but if populated must point to a valid record | Safe to load in parallel if the field stays null for a first pass |
| Self-relationship | Parent and child are the same object | Requires two-pass load: insert flat, then update the hierarchy field |
The practical takeaway is that not every dependency needs strict sequencing. Optional lookups can be left null on the first pass and backfilled later, which opens up parallel batch processing for objects that would otherwise sit needlessly on a critical path.
Validating Integrity After the Load Finishes
A successful Bulk API v2 job status does not mean the data is relationally sound. Batches report row-level success and failure, but a batch can succeed while still leaving a lookup null because your mapping logic skipped a record it should have matched.
Run a targeted SOQL sweep after every generation job: query for child records where the expected lookup field is null but shouldn't be, and query for lookup values that don't resolve to any existing parent ID. Both are fast checks and both catch the two most common failure modes without needing to eyeball individual records.
For junction objects, add a uniqueness check on the parent-pair combination, since duplicate junction rows are a frequent side effect of re-running a generation job without clearing prior test data first. I'd rather catch that with a five-minute query than have a tester file a bug against a feature that was never actually broken.
Frequently Asked Questions
What is referential integrity in the context of Salesforce test data?
It means every lookup and master-detail field on a generated record points to a parent record that actually exists in the org at the time of insert. It also covers consistency of related fields, such as a record type ID matching an active record type for that object. Without it, generated data creates orphaned lookups, broken roll-up summaries, and test failures unrelated to the feature actually being tested.
Why does referential integrity break more often with generated data than with data copied from production?
Production data already has real relationships baked in because those records exist together in the live org. Generated data starts from nothing, so the tool creating it has to build the entire relationship graph correctly, in the right order, before inserting anything. Any gap in load sequencing or ID mapping shows up as a broken lookup that production-clone data would never have.
How do external IDs help with Bulk API v2 relationship mapping?
External IDs let you reference a parent record by a synthetic key you control, instead of waiting for Salesforce to generate an internal record ID after insert. Bulk API v2 resolves the relationship server-side using that external ID field, which removes the need to query back generated IDs between batches. This is significantly faster at high volume and reduces the risk of ID mismatches when a batch has partial failures.
How should self-relationships like Account hierarchies be generated?
Self-relationships need a two-pass load. First insert the records with the hierarchy field left null, then run a separate update batch that populates the field, such as ParentId, using the IDs generated in the first pass. Attempting both in a single pass usually forces placeholder logic that adds unnecessary complexity.
What is the fastest way to check if a test data load has broken referential integrity?
Run targeted SOQL queries right after the load finishes: one for child records with a null lookup field that should be populated, and one for lookup values that don't match any existing parent record ID. For junction objects, add a check for duplicate parent-pair combinations, since re-running a generation job without clearing prior data commonly produces duplicates. These checks take minutes and catch the failure modes a successful batch status won't reveal.