Load-time data quality: keys, formats and cleansing
Keep bad data out of a migration: cleanse, standardize and deduplicate in staging, key every load on an external ID, predict how duplicate rules, validation rules and restricted, multi-select and address picklists treat API loads, set Data Loader's date, time zone, encoding and truncation options, and reconcile with error files.
Given a customer scenario, recommend appropriate techniques and methods for ensuring high data quality at load time.
~7 min read · 14 practice questions ·
What the exam expects
- Recommend a staging pipeline (profile, cleanse, standardize, deduplicate, enrich) so data is correct the day it lands.
- Key repeatable loads on an External ID with upsert, or use the Data Import Wizard's matching for small files.
- Predict how duplicate rules, validation rules and restricted, multi-select and address picklists treat API loads.
- Pick the Data Loader setting (European date format, Time Zone, UTF-8, truncation) that fixes a format symptom.
- Prove a load with sandbox trial runs and error files.
Key terms: staging (where extracts are fixed before loading); External ID (a custom field holding the source key, used for matching); upsert (insert or update in one call, matched on an ID or External ID); restricted picklist (accepts only admin-defined values); error file (Data Loader's CSV of failed rows and reasons).
Key facts
- Detail
- No match creates, one match updates, multiple matches error
- Detail
- No alert shown; users can't save the record
- Detail
- Active by default for business accounts, contacts, leads
- Detail
- Maximum 150,000,000 records per CSV file; beyond that use a partner tool
- Detail
- Match on account name and site, contact or lead email
- Detail
- Apply even when referenced fields aren't in the API call
- Detail
- Whole value replaced; no check for an existing value
- Detail
- Data Loader uses its host computer's time zone
- Detail
- Not available with Use Bulk API or Use Bulk API 2.0; row fails
Sources for this section (7)
- upsert()developer.salesforce.com (opens in a new tab)
- Duplicate Ruleshelp.salesforce.com (opens in a new tab)
- Stop Users from Creating Duplicateshelp.salesforce.com (opens in a new tab)
- When to Use Data Loaderdeveloper.salesforce.com (opens in a new tab)
- Validation Ruleshelp.salesforce.com (opens in a new tab)
- Picklist Limitationshelp.salesforce.com (opens in a new tab)
- Configure Data Loaderdeveloper.salesforce.com (opens in a new tab)
- Why does a rerun of an upsert job on an External ID not create copies?
- When does a duplicate rule set to Allow still stop a record from saving?
- Why can zone-less date-time values shift when Data Loader runs on a host in another time zone?
The questions are free to read. Answers with their sources and self-grading are included with the pack.
Keep reading with the Platform Data Architect pack
- Core concepts
- Exam traps
- Worked scenario
The pack includes:
- All 21 lessons of the Platform Data Architect guide, with reading progress
- Interactive decision walks and step-by-step sequence steppers
- Inline practice questions and one-click topic practice (14 questions for this lesson)
- An official source cited for every claim
- An AI tutor that answers from the guide
- Unlimited timed practice exams