Data Migration practice questions

The integration user lacks access to the Name field.

Why it fails: A lack of field-level security would trigger an 'INSUFFICIENT_ACCESS' or 'INVALID_FIELD_FOR_INSERT_UPDATE' error, not a duplicate value error. Duplicate value errors are specific to unique constraints on fields defined in the schema or custom unique indexes.

The bulk API batch size is set too high for the server.

Why it fails: Batch size limits relate to 'LIMIT_EXCEEDED' or timeout errors. A duplicate value error is a data-specific validation failure, not an infrastructure or resource-constraint error. Adjusting batch sizes will not resolve underlying data integrity issues found within the CSV or source dataset.

The target organization has existing records with duplicate names.

Why it fails: While possible, the error message specifically references the 'duplicate value found' on the record identifier used for the upsert. If the Name field itself is not marked as unique, it would not cause this specific error during an upsert operation.

Query all records into memory and use a VLOOKUP in Excel.

Why it fails: Performing lookups in Excel is not scalable for large migrations involving millions of records. Memory constraints and potential for manual errors make this approach unreliable. Architects should prefer database-level staging to ensure consistency and repeatability throughout the multi-stage migration pipeline.

Migrate data in a single flat file to avoid relationship errors.

Why it fails: A single flat file cannot represent complex, hierarchical parent-child relationships effectively. Migrating in a single pass often leads to data loss or orphaned records. A structured multi-pass approach is necessary to ensure referential integrity is preserved throughout the data transition process.

Use the Salesforce ID generation tool during migration.

Why it fails: Salesforce IDs are generated by the platform upon insertion; attempting to pre-generate them externally is not supported and will lead to system errors. The correct strategy involves using external keys as anchors to bridge the gap between legacy systems and the Salesforce ID schema.

Delete all source data files.

Why it fails: Source files must be retained for audit and compliance purposes. Deleting them prematurely would be a violation of data management policies. They should be archived according to the organization's data retention policy, not destroyed immediately after the load, as they serve as the official record of migration success.

Reset all user passwords in the target org.

Why it fails: Resetting user passwords is a user management task, not a data migration task. It is unrelated to the integrity and completion of the data load. Such actions should be handled by the IAM team according to standard user provisioning processes, rather than being included in the post-migration checklist.

They allow for the automatic creation of custom fields during the bulk loading process.

Why it fails: External IDs identify records for upsert operations but do not perform schema changes. Schema changes must be completed prior to data loading using the Metadata API or Setup UI. Relying on data migration tools to automatically generate fields is not a supported feature of the standard data loading process.

They enable the bypass of validation rules during initial record insertion.

Why it fails: External IDs identify specific records for matching, but they do not provide an exemption from Salesforce validation rules. Even when using an upsert operation with an external ID, every record must still satisfy all active validation logic and required field constraints defined in the target object configuration.

They increase the throughput limit of the Bulk API by indexing the source data.

Why it fails: While indexed fields improve query performance in Salesforce, External IDs do not change the throughput limits of the Bulk API. API limits are governed by the Salesforce edition and concurrent processing quotas, which remain constant regardless of whether an external key index is applied to the data being uploaded.

Increase the batch size in the data loader settings to process more records per transaction.

Why it fails: Increasing batch size exacerbates record locking issues rather than solving them. Larger batches increase the window of time that a parent record is locked, which increases the likelihood of other threads attempting to access the same parent record simultaneously, leading to more frequent UNABLE_TO_LOCK_ROW errors during large migrations.

Disable all validation rules and triggers on the Account and Contact objects.

Why it fails: While disabling triggers and validations can improve performance, it does not address the fundamental database row locking contention caused by concurrent updates to parent records. The issue is a result of the database architecture's locking mechanism, not the overhead of the business logic executing on those specific records.

Upgrade the Salesforce edition to Unlimited to gain higher API concurrency limits.

Why it fails: API concurrency limits relate to the number of simultaneous batch jobs, not the row-level locking contention on specific records. Even with increased limits, the underlying database deadlock scenario remains if multiple threads attempt to modify the same parent record at the same time, regardless of the edition level.

Use the standard SOAP API for all operations to ensure transactional consistency.

Why it fails: The SOAP API is subject to standard governor limits, such as a 200-record batch size and tighter request timeouts. Attempting to load 10 million records via the SOAP API would be highly inefficient, prone to timeouts, and would likely hit apex execution limits and concurrent request quotas frequently.

Perform the migration using Apex batch classes to bypass all record limits.

Why it fails: Apex batch classes are subject to governor limits within each execution context. While they help process large volumes, they are not intended for bulk data migration. They are best suited for programmatic data manipulation after the records are already in Salesforce, rather than for the initial high-volume data load.

Execute the migration during peak hours to utilize the maximum available resources.

Why it fails: Migrating large volumes during peak hours increases the risk of resource contention with end-users. It is best practice to perform high-volume migrations during off-peak hours to minimize the impact on system performance and reduce the risk of hitting concurrency limits that occur when users and automated tasks compete.

Populate all fields with placeholder data to ensure the record is complete.

Why it fails: Populating fields with placeholder data can pollute the data set and lead to inaccurate reports. Data should only be migrated if it is relevant and valid. If a field is required in Salesforce but not present in the source, the business should define a default value instead of placeholders.

Increase the Salesforce API limit for the migration user.

Why it fails: API limits are determined by the Salesforce edition and license count. Users cannot arbitrarily increase their own API limits. The architect must work within the existing limits or plan the migration timeline to distribute the load over time, rather than assuming they can manually adjust system-level API throughput quotas.

It will exceed the daily API request limit faster than Serial mode.

Why it fails: The parallel mode does not affect the total count of API requests, only the speed at which they are processed. The daily API limit is based on the number of requests, regardless of whether they are executed in parallel or serial mode. Parallelism simply utilizes more concurrent processing threads.

It prevents the usage of External IDs for record matching.

Why it fails: External IDs can be used regardless of whether the processing mode is serial or parallel. The choice of serial mode is an orchestration decision for the execution engine, while the external ID is a functional requirement for the database upsert operation, and both are independent of one another.

It requires the target object to have a custom index on the External ID.

Why it fails: While indexing External IDs is a best practice for query performance and matching, the Bulk API does not strictly require it for the upsert operation to function. Parallel mode can still be invoked without an index, though performance would be degraded regardless of the processing mode used.

Store all attachments as base64 strings in a custom Text field.

Why it fails: Storing binary data in a text field is extremely inefficient and will quickly hit character limits (32,768 characters). It also makes files unusable, as they cannot be downloaded or viewed. This approach violates basic data architecture principles and will lead to system instability and severe performance degradation.

Use the Bulk API to upload binary files directly as attachments.

Why it fails: The Bulk API is optimized for record data, not binary file uploads. Attempting to upload large files through the Bulk API will fail or time out. Files should be migrated using the Files API or specialized data migration tools designed to handle binary content and file versioning appropriately.

Migrate all attachments to a public cloud storage and store URLs in Salesforce.

Why it fails: While offloading files to external storage is a valid architecture, it requires a significant shift in how users access files and how developers build integrations. Unless the company specifically mandates this approach, the standard migration strategy is to move the files into Salesforce using the appropriate Files API.

Number field to ensure only numeric IDs are stored.

Why it fails: Legacy IDs often contain alphanumeric characters. Restricting the field to a 'Number' type would cause the migration to fail for any ID containing a letter. Furthermore, 'Number' fields have different indexing behaviors and are not the standard approach for mapping external system identifiers in Salesforce migrations.

Long Text Area field to accommodate varying ID lengths.

Why it fails: Long Text Area fields cannot be indexed and therefore cannot be used as an External ID in Salesforce. This would make it impossible to use the field for matching during upsert operations, rendering it useless for the primary purpose of an external identifier during a data migration project.

Encrypted Text field for security compliance.

Why it fails: Encrypted fields have significant limitations in Salesforce, including the inability to be used as an External ID. Using an encrypted field would prevent the architect from using the ID for upsert operations, making it a poor choice for mapping legacy identifiers during a data migration process.

User records must be migrated using a CSV file without headers.

Why it fails: Salesforce data loading tools require headers to map columns to fields correctly. Using a file without headers would prevent the mapping process and lead to import failures. The file format for users is the same as for any other object; headers are mandatory for successful ingestion.

Users should be loaded into the system as inactive by default.

Why it fails: While loading as inactive is a common strategy to prevent immediate access, it is not a requirement. It is a configuration choice. Some migrations may require users to be active immediately for testing. Therefore, it is not a fundamental difference in the migration process itself compared to other objects.

Users must be migrated before any other data objects.

Why it fails: Users are a prerequisite for ownership, but they don't necessarily have to be migrated first if the system is already configured with placeholder owners. While best practice often suggests users, roles, and profiles be set up early, it is not a hard requirement that they are the very first objects.

Manually delete all workflow rules before the migration.

Why it fails: Deleting production workflows is a destructive and dangerous practice. It would disrupt existing business processes for active users. The correct approach is to use the configuration settings in the migration tool to disable the triggering of these rules during the batch load, rather than permanently removing them.

Use the 'Trigger Workflow Rules' setting in the Data Loader.

Why it fails: Enabling the 'Trigger Workflow Rules' setting would cause all applicable workflows to fire for every record inserted. This is the opposite of the requirement to ensure historical data does not trigger automated actions. The architect must ensure these settings are unchecked to prevent unintended behavior during the load.

Set the email deliverability to 'No Access' for all users.

Why it fails: While setting email deliverability to 'No Access' prevents emails from being sent, it is a global change that affects all system operations, not just the migration. It is an overly broad approach compared to simply disabling the workflow triggers within the data loading tool settings during the migration process.

External ID

Why it fails: External ID is not a field type but a attribute that can be applied to Text, Number, or Email fields. By itself, it does not define the data type or length. While marking a field as External ID is useful for upsert operations, the underlying field must still be created with an appropriate type and length. Choosing External ID alone does not solve the data type requirement.

AutoNumber

Why it fails: AutoNumber fields automatically generate a unique numeric string for each new record, which would not match the legacy Account Number. This would break the mapping to the ERP system, as the original identifier would be lost. AutoNumber is designed for system-generated identifiers, not for preserving external unique keys. It cannot store alphanumeric values from the legacy system.

Number(15,0)

Why it fails: A Number field stores numeric values only, so any alphanumeric characters in the legacy Account Number would cause data load errors or be truncated. Even if all characters were digits, leading zeros would be stripped, breaking the unique identifier. This field type is unsuitable for preserving an alphanumeric key that must remain exactly as in the legacy system for ERP integration.

Enable 'Serial' mode and reduce the batch size to 1 record.

Why it fails: Serial mode changes how batches are processed but does not provide transactional rollback. Reducing batch size to 1 would make each record its own batch, so a failure would only affect that single record, not roll back a group of 200. This does not meet the all-or-nothing per batch requirement.

Set the 'DuplicateRule' header to include duplicate rules during load.

Why it fails: The DuplicateRule header controls whether duplicate rules are evaluated, not transaction rollback. Including duplicate rules may cause records to fail, but it does not ensure that a failed batch is rolled back. This setting is unrelated to the atomicity requirement.

Set the 'Assignment Rule' header to skip assignment rules.

Why it fails: Skipping assignment rules only prevents ownership assignment logic from running; it does not affect transaction rollback behavior. This setting is unrelated to the all-or-nothing requirement and would not cause a batch to roll back if one record fails validation.

Use the Data Loader in Batch mode with a batch size of 200.

Why it fails: Batch size is a performance tuning parameter, not a solution for duplicate prevention or bypassing automation. While a batch size of 200 is default, it does not address the requirements. Disabling triggers and enabling duplicate rules are the necessary steps. Batch size alone would not prevent duplicates or avoid Apex execution.

Schedule the migration during off-peak hours to reduce impact on users.

Why it fails: Scheduling during off-peak hours is an operational best practice to minimize performance impact, but it does not fulfill the specific requirements of preventing duplicates or bypassing triggers. The question asks for actions to ensure duplicate prevention and avoid automation. Timing alone does not achieve those goals.

Create a custom External ID field on Contact for the legacy system ID.

Why it fails: While an External ID is useful for upserts and matching, it does not directly prevent duplicates based on Email or bypass triggers. The requirement is to prevent duplicates by Email and avoid automation. External ID would help with updates but not with duplicate blocking. It is a good practice but not one of the two required actions here.

Number (18, 0)

Why it fails: A Number field stores numeric values and cannot hold hexadecimal strings containing letters A-F. Even if the identifier were numeric, Number(18,0) supports only 18 digits and would truncate or reject a 32-character value. This field type is fundamentally incompatible with the hexadecimal format of the legacy identifier and would cause data loss or load failures during migration.

External ID (Text, 32)

Why it fails: An External ID is a Text field with the External ID attribute enabled, which creates a case-insensitive unique index. However, hexadecimal identifiers are case-sensitive by nature, and the case-insensitive indexing can cause collisions or unexpected matches. Additionally, External ID fields are limited to 255 characters, but the case-insensitivity is the critical flaw for a hexadecimal key that must preserve exact case for reconciliation.

Text (80)

Why it fails: Text fields are stored as variable-length strings and are not indexed by default. For 15 million records, exact-match SOQL filters on an unindexed Text field would cause full table scans, degrading performance. While Text can store a 32-character hexadecimal string, it fails the stated requirement for efficient exact-match lookups and list views at this scale without a custom index, which is not available on standard Text fields.

Load Cases, then use the FeedItem object to insert comments, setting the ParentId to the Case ID and Type to 'TextPost'.

Why it fails: FeedItem can be used for Case feed posts, but it is not the standard object for Case Comments. CaseComment is the dedicated object for comments and is what the standard UI uses. While FeedItem can appear in the feed, it may not respect the chronological ordering of legacy comments and can complicate data management. The CaseComment object is the correct choice for preserving comment history.

Load Cases with a rich text field containing all comments concatenated, and use a formula to display them in the feed.

Why it fails: Concatenating comments into a single rich text field loses individual timestamps and authorship, and the Case feed does not automatically display arbitrary rich text fields. This approach fails to preserve chronological order and does not integrate with the standard Case feed, which expects CaseComment or FeedItem records. It also complicates future updates and reporting.

Load Cases and then use the Attachment object to store each comment as a separate text file attached to the Case.

Why it fails: Storing comments as attachments does not make them visible in the Case feed as comments; attachments are separate from the feed and would require users to open each file. This approach fails to preserve chronological order in a user-friendly way and does not meet the requirement for comments to be visible in the Case feed. The CaseComment object is purpose-built for this scenario.

Load all records and rely on Salesforce validation rules to reject invalid MRN__c values; then query the error log to reprocess failures.

Why it fails: Loading all records would cause invalid ones to fail, but the job would still process and commit valid records. However, the invalid records would be scattered in error logs and require extraction, which is less controlled than pre-splitting. The requirement is to quarantine invalid records without failing the job, and this method adds complexity.

Use Data Loader with the 'Allow Field Truncation' option and set MRN__c to a default placeholder value for missing entries.

Why it fails: Setting a placeholder value would corrupt data quality and violate the requirement to quarantine invalid records for later correction. Allow Field Truncation is unrelated and does not address missing required values. This approach would load bad data into production.

Enable AllOrNone and load all records in a single batch so that invalid records cause a full rollback.

Why it fails: AllOrNone would cause the entire batch to fail when any invalid MRN__c is encountered, preventing the valid records from being loaded. This directly contradicts the requirement to load valid records immediately and quarantine only the invalid ones.

Load the Opportunities with a future close date and then update them to the historical dates in a second pass, so automation only fires on the update.

Why it fails: This approach would still trigger automation during the second pass when dates are updated, potentially sending emails or updating records. It adds unnecessary complexity and risk. The goal is to avoid automation entirely for historical data, not to shift when it occurs. This method does not satisfy the requirement and could cause data inconsistencies.

Use the Bulk API with the 'Serial' mode and set the batch size to 1 to process records individually and bypass automation.

Why it fails: Bulk API serial mode and batch size affect performance and order, not automation execution. Workflow rules, process builders, and triggers still fire regardless of batch size. This option misunderstands the Bulk API's behavior; it does not provide a way to suppress automation. The architect must use a different mechanism to disable automation during load.

Use the Data Loader's 'Bulk API' option with the 'Allow Field Truncation' setting enabled to skip automation.

Why it fails: The 'Allow Field Truncation' setting relates to handling data that exceeds field length, not to automation. It has no effect on whether workflow rules or triggers execute. This option confuses a data quality setting with automation control, and would not prevent the undesired actions during migration.

Set the Bulk API 2.0 job to use parallel processing mode to increase throughput.

Why it fails: Parallel processing can increase throughput but may cause record locking errors when loading related records, especially if parent and child records are processed simultaneously. It is not a critical consideration for referential integrity; in fact, it can introduce issues. The critical considerations are order of load and using external IDs. Parallel mode is optional and depends on the data model.

Disable all validation rules and triggers on the objects being loaded to improve performance.

Why it fails: Disabling validation rules and triggers can improve performance, but it is not a critical consideration for ensuring referential integrity. In fact, disabling them might allow invalid data to be loaded, which could break relationships or cause data quality issues. The question asks for critical considerations for loading related records, not for performance optimization. This is a common distractor.

Ensure that all lookup relationships are optional (not required) to avoid load failures.

Why it fails: Making lookups optional is not a valid solution because it changes the data model and may violate business requirements. Required lookups are often necessary for data integrity. The correct approach is to load parents first and use external IDs, not to alter the schema. This option suggests a workaround that could compromise data quality.