Exporting data from partial sandbox to developer sandbox for related objects

Question

I am trying to export Account and Opportunity data using data loader from partial sandbox to the developer sandbox. Currently, the hurdle is that in Opportunity Account is a lookup and required field.

So far what I have tried following steps

1. Export data separately from partial sandbox for Account and Opportunity objects

2. Later I had created external Id in developer sandbox for Account

3. Insert data to developer sandbox and mapped external id with Opportunity Id

Now I am confused how would i add the related objects here

do i need to export the Account data from developer sandbox and extract account Id and add it to Opportunity object csv ?

Answer 1

Data Loader

When importing multiple connected objects, it's critical that you retain the success files that Data Loader emits. These allow you to map from old Ids to new Ids as you're populating lookup fields in downstream objects.

For example, you'd load Accounts, and get back an Account success file. Then, in your Opportunities input file, you'd use an Excel construct like VLOOKUP() or INDEX()/MATCH() (preferable) to map from the old Account Id in your Opportunity.AccountId column to the corresponding new Id in the data you just loaded.

I always do this by maintaining a separate Excel sheet with just two columns: old Id on the left, new Id on the right. I accumulate that data from all of my success files so that I can map any old Id just by looking it up in that table, which you'll need if you're working with polymorphic lookups like Task.WhoId and Task.WhatId.

Using the success files to map old Id to new Id obviates the need for External Ids, although you can also use that approach. If you use External Ids, you may need to change your schema; each parent object would have to have an External Id field, whose value you have present on your child object rows in CSV. You'd then perform an Upsert operation rather than an insert and have Data Loader use the External Id field to select the parent relationship.

Other Tools

Disclaimer: I am the author of this free and open source tool.

Amaxa (documentation) is a multi-object data loader I wrote. Using a simple YAML definition file, it can load up connected objects in a single operation with no manual mapping required. The definition for an Accounts-and-Opportunities load would look something like this:

version: 2
operation:
    -
        sobject: Account

        field-group: smart
        extract:
            all: True
    -
        sobject: Opportunity
        field-group: smart
        extract:
            descendents: True

This would load (or extract) Accounts first, and then Opportunities, and maintain the relationships between them.

Answer 2

For this task, I recommend Salesforce DX. Simply export the records you want with:

sfdx force:data:tree:export -q "query" -p -u sourceOrg

This gets you a set of files (something like Accounts.json, Opportunities.json, and Opportunity.plan), which you can then import into another org:

sfdx force:data:tree:import p MyImport.plan -u destinationOrg

This automatically creates the relationships for you while creating new records. This is also incredibly helpful for importing to scratch orgs, should you move to that development model.