# Load-time data quality: keys, formats and cleansing

*Platform Data Architect Study Guide · Data Migration · Last updated October 26, 2026 · https://www.certifyforce.com/study-guide/platform-data-architect/data-migration/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.

- Certification: Salesforce Platform Data Architect
- Exam section: Data Migration
- Study guide: https://www.certifyforce.com/study-guide/platform-data-architect

**Official exam objective:**

> Given a customer scenario, recommend appropriate techniques and methods for ensuring high data quality at load time.

## 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

| Fact | Detail | Source |
|---|---|---|
| Upsert outcomes | No match creates, one match updates, multiple matches error | [upsert()](https://developer.salesforce.com/docs/platform/api/guide/sforce-api-calls-upsert.html) |
| Duplicate rules on API or import loads | No alert shown; users can't save the record | [Duplicate Rules](https://help.salesforce.com/s/articleView?language=en_US&id=sales.duplicate_rules_overview.htm&type=5) |
| Standard duplicate rules | Active by default for business accounts, contacts, leads | [Stop Users from Creating Duplicates](https://help.salesforce.com/s/articleView?id=sales.duplicate_prevention.htm&language=en_US&type=5) |
| Data Loader CSV size | Maximum 150,000,000 records per CSV file; beyond that use a partner tool | [When to Use Data Loader](https://developer.salesforce.com/docs/atlas.en-us.dataLoader.meta/dataLoader/when_to_use_the_data_loader.htm) |
| Wizard duplicate prevention | Match on account name and site, contact or lead email | [When to Use Data Loader](https://developer.salesforce.com/docs/atlas.en-us.dataLoader.meta/dataLoader/when_to_use_the_data_loader.htm) |
| Validation rules | Apply even when referenced fields aren't in the API call | [Validation Rules](https://help.salesforce.com/s/articleView?id=platform.fields_about_field_validation.htm&type=5) |
| Multi-select picklist via API | Whole value replaced; no check for an existing value | [Picklist Limitations](https://help.salesforce.com/s/articleView?id=platform.picklist_limitations.htm&type=5) |
| Time Zone blank | Data Loader uses its host computer's time zone | [Configure Data Loader](https://developer.salesforce.com/docs/platform/data-loader-impl/guide/configuring-the-data-loader.html) |
| Allow field truncation | Not available with Use Bulk API or Use Bulk API 2.0; row fails | [Configure Data Loader](https://developer.salesforce.com/docs/platform/data-loader-impl/guide/configuring-the-data-loader.html) |

## Check yourself

- 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?

## Sources

- [Things to Know About Duplicate Rules](https://help.salesforce.com/s/articleView?language=en_US&id=sales.duplicate_rules_overview.htm&type=5)
- [Configure Data Loader](https://developer.salesforce.com/docs/platform/data-loader-impl/guide/configuring-the-data-loader.html)
- [Enhance Business Growth by Improving Data Quality](https://trailhead.salesforce.com/content/learn/modules/data_quality/data_quality_improve_quality)
- [Loading Data from the API](https://developer.salesforce.com/docs/platform/salesforce-large-data-volumes-bp/guide/ldv-deployments-best-practices-api-loading-data.html)
- [upsert()](https://developer.salesforce.com/docs/platform/api/guide/sforce-api-calls-upsert.html)
- [When to Use Data Loader](https://developer.salesforce.com/docs/atlas.en-us.dataLoader.meta/dataLoader/when_to_use_the_data_loader.htm)
- [When to Use Data Loader (earlier version, doc 244.0)](https://developer.salesforce.com/docs/atlas.en-us.244.0.dataLoader.meta/dataLoader/when_to_use_the_data_loader.htm)
- [Stop Users from Creating Duplicate Records](https://help.salesforce.com/s/articleView?id=sales.duplicate_prevention.htm&language=en_US&type=5)
- [Let Users Select States, Countries, and Territories from Picklists](https://help.salesforce.com/s/articleView?id=sf.admin_state_country_picklists_overview.htm&type=5)
- [Picklist Limitations](https://help.salesforce.com/s/articleView?id=platform.picklist_limitations.htm&type=5)
- [Validation Rules](https://help.salesforce.com/s/articleView?id=platform.fields_about_field_validation.htm&type=5)
- [Insert, Update, or Delete Data Using Data Loader](https://developer.salesforce.com/docs/platform/data-loader-impl/guide/inserting-updating-or-deleting-data.html)
- [General Guidelines for Data Loads](https://developer.salesforce.com/docs/platform/api-asynch/guide/asynch-api-planning-guidelines.html)
- [Import Data with the Data Import Wizard](https://help.salesforce.com/s/articleView?id=sf.import_with_data_import_wizard.htm&language=en_US&type=5)

## About the full unit

The "Core concepts", "Exam traps" and "Worked scenario" sections of this unit, its 14 linked practice questions, and unlimited timed mock exams are included with the Platform Data Architect pack (USD $19): https://www.certifyforce.com/practice-exams