SF-Data-Arch Large Data Volume Considerations Practice Test 2 — 15 Questions

Question 1

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?

Choose an answer to begin — your selection is scored in the full session.

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 2

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 3

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 4

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 5

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.

Question 6

A data architect is optimizing a custom object Shipment__c with 8 million records. Users frequently run reports that filter by Destination_Country__c (a text field) and Ship_Date__c (a date field). The architect plans to create custom indexes to improve report performance. Which two considerations are most important when creating these indexes? (Choose two.)

Accepted Answer

A composite index on Destination_Country__c and Ship_Date__c can be used, but the leading field must be selective.. When creating custom indexes for large data volumes, selectivity is paramount. A field with high cardinality, such as Destination_Country__c, is more likely to be selective. For composite indexes, the leading field must be selective for the index to be used. Therefore, the two correct considerations are evaluating the distinct values of the text field and ensuring the leading field of a composite index is selective.

Answer

Indexes on date fields are always selective because dates are unique.

Answer

Custom indexes should be created on all fields used in report filters to maximize performance.

Answer

The maximum number of custom indexes per object is 10, so indexes must be prioritized.

Question 7

A data architect is designing a solution to display real-time inventory data from an external ERP system in Salesforce. The external system exposes a REST API that supports filtering and pagination. The inventory data changes frequently and must not be stored in Salesforce due to data residency requirements. Which Salesforce feature should the architect use to meet these requirements?

Accepted Answer

Salesforce Connect with a custom Apex adapter. Salesforce Connect with a custom Apex adapter enables integration with external REST APIs, supporting real-time data access without persisting data in Salesforce. This satisfies data residency requirements and leverages the ERP's existing filtering and pagination capabilities. Other adapter types either require specific protocols not available or connect to different systems.

Answer

Salesforce Connect with an OData adapter

Answer

Salesforce Connect with a GraphQL adapter

Answer

External Objects with a cross-org adapter

Question 8

A Salesforce org has a custom object Log__c with 15 million records. Users report that list views and reports on Log__c are slow, and the object has many fields (over 200). You are asked to improve performance. Which two actions should you recommend? (Choose two.)

Accepted Answer

Reduce the number of fields on Log__c by moving less frequently used fields to a related custom object.. For a wide object with 15 million records, a skinny table that includes only the fields used in list views and reports can dramatically reduce query time because it stores a narrow copy of the data. Reducing the number of fields on the object by moving rarely used fields to a related object also improves performance by decreasing row width. Together, these actions address the root cause: the volume of data scanned per query.

Answer

Enable Divisions on Log__c to partition the data and improve query performance.

Answer

Add custom indexes to all fields that are used in list view filters.

Answer

Set the Log__c object to be a Big Object to improve query performance for list views.

Question 9

A company has a custom object 'Case_Comment__c' with 30 million records. They need to archive records older than 2 years to an external system to improve performance. The archiving process must run daily and delete the archived records from Salesforce. Which approach should the data architect recommend to minimize the impact on users during business hours?

Accepted Answer

Use a Batch Apex job that queries and deletes records in batches of 200, scheduled to run during off-peak hours.. Batch Apex is designed for processing large data volumes asynchronously. By running in batches and scheduling off-peak, it minimizes user impact and avoids governor limit issues. The other methods lack automation, scalability, or proper integration for a daily archiving job.

Answer

Use a scheduled Flow that queries and deletes records one by one using a loop.

Answer

Use the Bulk API to delete records directly from the external system without querying Salesforce.

Answer

Use the Data Loader to export and delete records in a single operation during a maintenance window.

Question 10

A Salesforce org has a custom object Transaction__c with 10 million records. The object has a lookup to Account and a master-detail to Opportunity. Users need to see a related list of Transactions on the Account page, and they frequently filter by Transaction_Date__c and Amount__c. The current related list is slow to load. What should an architect recommend to improve the related list performance?

Accepted Answer

Replace the related list with a Lightning component that queries Transaction__c using a selective SOQL query with a LIMIT clause and displays the results.. Standard related lists on objects with millions of child records can be slow because they may retrieve more data than necessary. A custom Lightning component with a selective SOQL query and a LIMIT clause gives control over the query, ensuring it uses indexes and returns only the needed rows. This approach reduces load time and improves user experience. Indexes alone may not be enough if the query is not selective or if the related list retrieves many records.

Answer

Create a custom index on the Account lookup field and on Transaction_Date__c, and ensure the related list sorts by Transaction_Date__c.

Answer

Create a roll-up summary field on Account that counts Transactions, and display that instead of the related list.

Answer

Enable Big Object indexing on Transaction__c to improve related list performance.

Question 11

Universal Containers has 50 million records in a custom object. A report summarizing these records times out. Which strategy is most effective for improving report performance?

Accepted Answer

Create a summary object to store pre-aggregated data.. Summarizing 50 million records directly in a standard report is inefficient due to the overhead of the reporting engine. Using a Summary Object allows Salesforce to pre-aggregate data asynchronously, moving the heavy lifting from query time to insert time. This approach ensures reports operate on a significantly smaller dataset, preventing timeout errors and improving user experience for large-scale data analysis scenarios.

Answer

Increase the report timeout limit in Setup.

Answer

Implement a custom lightning web component for reporting.

Answer

Add more indexes to the custom object fields.

Question 12

A company is migrating 100 million records into Salesforce. Which TWO actions should the architect take to optimize performance and prevent row locking?

Accepted Answer

Disable unnecessary triggers during the data load.. High-volume data loads require careful management of record locking and indexing. Disabling triggers during the load prevents unnecessary processing and lock contention. Furthermore, reducing the number of indexes on the target object minimizes the overhead required for every insert operation, as Salesforce must update every index on the object for each new record processed during the migration process.

Answer

Enable all custom indexes before the data load.

Answer

Use the Bulk API 2.0 with serial mode.

Answer

Increase the batch size to 10,000.

Question 13

Refer to the exhibit. What is the most likely reason this query fails?

Accepted Answer

The field 'Status__c' is not indexed.. The query fails because 'Status__c' is likely not indexed, or the 'Pending' value is too common, exceeding the selectivity threshold. On objects with millions of records, Salesforce requires that filters target a small enough subset to be efficient. When a filter is too broad or lacks an index, the system rejects the query to prevent performance degradation for the entire multi-tenant environment.

Answer

The LIMIT clause is too high for the object size.

Answer

The query includes too many fields in the SELECT clause.

Answer

The record count exceeds the object storage limit.

Question 14

Which platform feature is best used to move data off-platform to avoid Large Data Volume issues?

Accepted Answer

Salesforce Connect.. Salesforce Connect allows organizations to display data from external systems as if it were stored natively in Salesforce, without actually consuming storage or impacting governor limits. By using external objects, companies can maintain massive amounts of historical data off-platform while still providing users with a seamless interface for viewing and reporting on that data within the native environment.

Answer

Salesforce BigObjects.

Answer

Data Loader command line.

Answer

Platform Events.

Question 15

When dealing with high-frequency updates on a parent object, what is the best practice to prevent lock contention?

Accepted Answer

Use an asynchronous approach to aggregate parent updates.. To minimize lock contention, avoid updating the parent record every time a child record is updated. Instead, use an asynchronous pattern like a batch job or a queueable apex to aggregate updates and perform a single update to the parent. This reduces the number of locks requested on the parent object, significantly decreasing the likelihood of row-locking errors during high-volume operations.

Answer

Always update the parent in the child's trigger.

Answer

Set the parent record to read-only.

Answer

Convert the lookup relationship to a master-detail.