SF-Data-Arch Large Data Volume Considerations — 25 Questions

Question 1

Refer to the exhibit. A batch process updating 50,000 records frequently fails with the error shown. Which design change resolves this contention?

Accepted Answer

Group records by parent ID before processing.. The UNABLE_TO_LOCK_ROW error occurs when multiple threads attempt to update the same record or its parents simultaneously. By reordering the records to group them by parent ID before processing, you ensure that all children belonging to the same parent are updated in the same transaction or sequence, minimizing the duration and frequency of cross-thread lock contention on parent records.

Answer

Implement a retry mechanism using a custom setting.

Answer

Change the batch size to 1.

Answer

Disable all sharing rules on the object.

Question 2

An organization faces performance issues querying a large custom object. Which condition makes a SOQL query non-selective, forcing a full table scan?

Accepted Answer

The filter condition includes a leading wildcard on a text field.. A query is non-selective if the filter criteria do not use an indexed field, or if the filter is too broad, hitting more than the threshold of records (typically 300,000). Full table scans occur when the query engine cannot utilize indexes to narrow the search, leading to extreme latency. Proper indexing and selective filters are essential for maintaining performance on objects with millions of rows.

Answer

Using a filter on a custom indexed field.

Answer

The query returns fewer than 100,000 records.

Answer

The object has fewer than 100,000 records total.

Question 3

Which THREE factors influence the performance of a Salesforce object containing 50 million records?

Accepted Answer

The number of active indexes on the object.. Large Data Volume performance is influenced by indexing strategies, the complexity of triggers/automations, and the selectivity of SOQL queries. Efficient indexing speeds up retrieval, while optimized automation prevents excessive resource usage per DML operation. Finally, selective queries prevent full table scans, ensuring that the database engine can efficiently locate records without scanning the entire dataset, which is crucial for maintaining responsiveness at scale.

Answer

The total number of fields on the object.

Answer

The color scheme of the Lightning app.

Question 4

A company needs to archive 10 million records annually. What is the recommended best practice for archiving to maintain Salesforce performance?

Accepted Answer

Use the Bulk API to delete records once a year.. Archiving records to an external data store (like Heroku Postgres or AWS) removes them from the Salesforce production environment, which keeps the active dataset lean. This maintains query performance and ensures that the production org stays well within performance guidelines. Periodically exporting and deleting data is a standard lifecycle management task for high-volume Salesforce organizations to ensure sustained operational efficiency.

Answer

Move records to a custom Archive object within Salesforce.

Answer

Change the record ownership to a generic user.

Answer

Use Salesforce Data Masking to hide the records.

Question 5

A company is experiencing slow performance when performing bulk updates on the Opportunity object, which contains 10 million records. The updates trigger complex Apex logic and multiple flows. What is the most effective architectural strategy to optimize this process?

Accepted Answer

Offload processing logic to an asynchronous execution framework like Platform Events.. Decoupling logic using an asynchronous pattern is essential for high-volume data operations. By moving processing to a platform event or a queueable Apex job, you reduce the immediate transaction load and avoid hitting governor limits. This approach improves system responsiveness and allows the platform to process records in smaller, manageable batches, which is critical for maintaining performance integrity when dealing with massive datasets in the Salesforce multi-tenant environment.

Answer

Increase the batch size in the Data Loader settings to the maximum of 10,000.

Answer

Implement a custom indexing strategy on all fields used in filter criteria.

Answer

Disable all Validation Rules and Apex Triggers before the update process.

Question 6

An organization is planning to migrate 50 million records into Salesforce. Which TWO practices should be prioritized to ensure optimal data load performance?

Accepted Answer

Enable 'Parallel Processing' in Data Loader settings.. Efficient data loading requires minimizing the overhead of platform automation and maximizing the effectiveness of Salesforce APIs. By disabling unnecessary features that trigger upon record creation and utilizing parallel processing, the system can commit records faster and with fewer interruptions. These steps ensure that the data migration process adheres to platform resource constraints while achieving the throughput required for such large volumes.

Answer

Keep all Triggers and Flows active to ensure data quality.

Answer

Perform the load during peak business hours for real-time monitoring.

Answer

Insert all records in a single batch to minimize transaction count.

Question 7

Which THREE factors should be considered when implementing a 'Skinny Table' to improve performance?

Accepted Answer

Skinny tables eliminate the need for joins in complex queries.. Skinny tables are a specialized performance tool designed to reduce the need for joins between tables. Because they are not updated automatically by standard operations like other tables, they require careful maintenance. Their implementation is specifically targeted at read-heavy environments where query performance is critical for large objects, and they must be requested via Salesforce support to ensure they are architected correctly for the specific object and field set.

Answer

Skinny tables are maintained automatically by Salesforce.

Answer

They are ideal for frequently updated, transactional objects.

Question 8

Refer to the exhibit. The query above is performing poorly on an object with 50 million records. What is the most likely cause of the performance issue?

Accepted Answer

The index on OwnerId is not selective enough for the query.. The query filters on two fields, 'CreatedDate' and 'OwnerId', but the query optimizer may not be selecting the most selective index. In Salesforce, if an index is not sufficiently selective (i.e., it returns too many records), the system will ignore it and perform a full table scan. With 50 million records, the selectivity threshold is crucial. Ensuring that the filters target indexed fields and that those indexes are selective is vital.

Answer

The query is selecting too many fields.

Answer

The query lacks a ORDER BY clause.

Answer

The query is missing a LIMIT clause.

Question 9

Refer to the exhibit. A developer is receiving this error in a synchronous Apex controller when querying an object with 10 million records. What is the correct architectural change?

Accepted Answer

Use Batch Apex to process the records in smaller chunks.. The error occurs because synchronous Apex has a governor limit of 50,000 records that can be queried in a single transaction. For objects with millions of records, querying them all at once is not feasible. The architecture must be updated to use batch processing or paginated result sets, allowing the application to process data in chunks that fit within the platform's execution constraints, ensuring reliability at scale.

Answer

Increase the limit in the Organization-Wide settings.

Answer

Wrap the query in a try-catch block to ignore the error.

Answer

Disable the governor limit for the specific user.

Question 10

When designing an integration to sync data from an external ERP into Salesforce, which strategy best handles a sudden spike of 1 million records?

Accepted Answer

Use the Bulk API 2.0 to load the data in batches.. For high-volume spikes, the integration should use an asynchronous, queue-based approach rather than direct, synchronous API calls. By pushing the data into a staging object or a message queue, the system can process the records incrementally. This prevents the integration from overwhelming the Salesforce API or hitting governor limits, ensuring that the data is eventually consistent without degrading real-time system performance for end-users.

Answer

Use the REST API to send records one by one.

Answer

Directly update the Salesforce database using a JDBC driver.

Answer

Trigger a custom Apex class to perform a callout to the ERP.

Question 11

Refer to the exhibit. A developer encounters this error after migrating 10 million records. Which data architecture best practice was likely ignored?

Accepted Answer

Failure to utilize an index on the fields used in the SOQL WHERE clause.. The 'List has more than 1 row' error indicates that a SOQL query intended to return a single record returned multiple results. In large data volumes, filters that were previously selective become non-selective. When an index is missing or a filter does not hit a selective threshold, the query engine scans the entire table, leading to unexpected result sets and performance degradation in high-volume environments.

Answer

Exceeding the governor limit for total records retrieved in a single transaction.

Answer

Implementing heavy Apex logic inside the loop processing the data.

Answer

Using a standard report to update records in the database.

Question 12

An organization is experiencing slow performance on reports involving an object with 20 million records. Which TWO actions can improve query performance?

Accepted Answer

Filter reports using indexed fields rather than non-indexed fields.. Reporting performance on large datasets is primarily dependent on query selectivity and database indexing. By reducing the volume of data scanned and optimizing the filters to use indexed fields, the Salesforce query optimizer can efficiently retrieve the subset of data required. These two actions are foundational to maintaining system responsiveness as data volumes grow, ensuring that queries complete within reasonable timeframes without timing out.

Answer

Increase the record limit in the report settings to show all records.

Answer

Change the report format to a matrix report with grouping.

Answer

Use custom formula fields for all filtering criteria in reports.

Question 13

Which THREE strategies are effective for handling LDV data loading to avoid 'UNABLE_TO_LOCK_ROW' errors?

Accepted Answer

Process records in smaller batches to reduce the time a lock is held.. Managing concurrency in large data loads is critical. By grouping records, using serial modes, and reducing the number of automated processes triggered during the load, you decrease the likelihood of contention. These three strategies allow the system to process data in a controlled, predictable sequence, preventing multiple threads from accessing the same database rows or parent records simultaneously, which is the root cause of row-level lock errors.

Answer

Increase the number of concurrent batches to maximize throughput.

Answer

Enable all workflow rules and triggers to ensure data quality.

Question 14

Which TWO of the following are true regarding the Salesforce Bulk API?

Accepted Answer

It supports both parallel and serial execution modes.. The Bulk API is the cornerstone of LDV operations. It provides an asynchronous, efficient way to handle massive data volumes by breaking jobs into batches. Understanding that it is intended for large loads—not real-time updates—is key. It offloads processing from the main interactive thread, allowing for higher throughput and better management of system resources during intensive data movement tasks.

Answer

It is optimized for real-time, low-latency transaction processing.

Answer

It automatically bypasses all Apex triggers and validation rules.

Answer

It is limited to 1,000 records per batch globally.

Question 15

A global logistics company stores 8 million active records in a custom object called Shipment__c. Each Shipment__c record has a lookup to Account and a master-detail to Carrier__c. The operations team runs a nightly Bulk API 2.0 job that upserts 250,000 Shipment__c records using an external ID field. The job intermittently fails with UNABLE_TO_LOCK_ROW errors on Carrier__c parent records, and analysts report that queries filtering on Carrier__c are slower after each load. Which architectural change should the data architect recommend to reduce lock contention while preserving the nightly load?

Accepted Answer

Sort the incoming records by Carrier__c before submitting the job and configure the job to process sequentially.. Lock contention on parent records during bulk upserts is typically caused by concurrent transactions competing for the same parent row. Processing sequentially and grouping records by the parent value means each parent lock is held briefly and not contested. This preserves the nightly load while reducing UNABLE_TO_LOCK_ROW errors and limiting the extra index churn that slows queries filtering on Carrier__c.

Answer

Convert the Carrier__c master-detail relationship to a lookup and make the field optional.

Answer

Enable parallel mode in the Bulk API 2.0 job and increase the batch size to 10,000.

Answer

Create a formula field on Shipment__c that derives the carrier name and use it in the nightly load instead of the lookup.

Question 16

A Salesforce org has a custom object Invoice__c with 15 million records. Users frequently run reports that filter on Status__c (picklist with 5 values) and Invoice_Date__c (date). The reports are timing out. The data architect decides to create a custom index on Status__c. After creating the index, performance remains poor. What is the most likely reason the index did not improve performance?

Accepted Answer

The filter on Status__c is not selective enough because it returns more than 30% of the records.. The query optimizer ignores an index when the filter is not selective. With only five status values, a filter on Status__c likely returns about 20% of the 15 million records, which exceeds the selectivity threshold. Thus, even with a custom index, the query performs a full table scan. The correct answer identifies the lack of selectivity as the reason the index was ineffective.

Answer

The custom index was not activated because the object has more than 10 million records.

Answer

Custom indexes cannot be created on picklist fields.

Answer

The index was created on the wrong field; it should have been on Invoice_Date__c instead.

Question 17

A data architect is troubleshooting a performance issue on a custom object Transaction__c with 20 million records. A nightly batch job runs a SOQL query that filters on Transaction_Date__c (a date field) and Status__c (a picklist with 3 values). The query returns approximately 500,000 records. The architect creates a custom index on Transaction_Date__c, but the query still times out. What is the most likely explanation?

Accepted Answer

The index on Transaction_Date__c is not selective because the date range spans more than 30% of the first million records.. The query optimizer determines selectivity by examining the first million records. For a date filter, if the date range covers more than 30% of those records, the index is considered non-selective and may not be used. Even though the query returns only 500,000 records out of 20 million, the date range might still be too broad for the first million. Thus, the index is ignored, and the query performs a full scan, causing a timeout.

Answer

Custom indexes are not supported on date fields; only standard indexes can be used.

Answer

The index on Transaction_Date__c is not being used because the filter on Status__c is not indexed.

Answer

The query returns more than 30% of the records, making the filter non-selective despite the index.

Question 18

A Salesforce org has a custom object Invoice__c with 8 million records. Users report that list views and reports filtering on a custom date field Invoice_Date__c and a picklist Status__c are slow. The fields are not indexed. What is the most effective first step to improve query performance?

Accepted Answer

Create a custom index on Invoice_Date__c and Status__c.. When an object has millions of records and queries filter on unindexed fields, the database performs a full table scan, causing slow performance. Creating custom indexes on those fields allows the query optimizer to use index-based access, significantly reducing query time. This is the most direct and effective first step.

Answer

Increase the number of records displayed in list views.

Answer

Enable skinny table for Invoice__c.

Answer

Create a formula field that concatenates Invoice_Date__c and Status__c.

Question 19

A data architect is designing a nightly batch process that updates 2 million Account records. The process runs in a single transaction and frequently fails with UNABLE_TO_LOCK_ROW errors. The updates are performed by a single integration user. What should the architect do to resolve the locking errors?

Accepted Answer

Use a smaller batch size and sort records by a field that distributes updates across different parents.. Lock contention on parent records occurs when multiple transactions try to update child records that reference the same parent. By reducing batch size and ordering updates so that records referencing different parents are processed together, the integration minimizes the chance of simultaneous lock requests on the same parent. This reduces UNABLE_TO_LOCK_ROW errors while maintaining data integrity.

Answer

Reduce the batch size and sort the records by parent ID to group updates on the same parent.

Answer

Increase the batch size to process all records in a single transaction to avoid multiple lock requests.

Answer

Disable record locking on the Account object by enabling the 'Bypass Locking' setting.

Question 20

A Salesforce org has a custom object Log__c with 30 million records. The object is used for storing application logs and is write-heavy. Users rarely query the data, but when they do, they need to retrieve records by a unique identifier. What is the best practice for the unique identifier field to ensure efficient retrieval?

Accepted Answer

Mark the field as an external ID and enable indexing.. For a write-heavy object with occasional lookups by a unique identifier, the field should be indexed to avoid full table scans. Marking the field as an external ID automatically creates an index, providing efficient retrieval while maintaining data integrity.

Answer

Enable field history tracking on the unique identifier field.

Answer

Create a formula field that returns the unique identifier.

Answer

Use a text field with a unique constraint.