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

Question 1

What is the recommended approach to manage 'Skinny Tables' in an environment with Large Data Volumes?

Accepted Answer

Request them only for high-read-volume objects where performance is critical.. Skinny tables are a specialized feature used to improve performance by flattening data structures to avoid joins. They should only be used when standard indexing is insufficient for performance. Because they require Salesforce Support to maintain and are automatically synchronized, they are a powerful but rigid tool that should be applied sparingly to the most critical, high-read-volume scenarios only, to avoid unnecessary maintenance overhead.

Answer

Enable skinny tables for every object with more than 1 million records.

Answer

Manually update skinny tables using DML statements in Apex.

Answer

Always include every field of the object in the skinny table.

Question 2

Refer to the exhibit. In a 50 million record Account table, why might this query perform poorly?

Accepted Answer

The custom field is likely missing an index, causing a full table scan.. This query uses a filter on CreatedDate (a system-indexed field) and a custom field (Custom_External_ID__c). If Custom_External_ID__c is not indexed, the query optimizer must perform a full table scan. Even with a system index, if the combination of filters is not selective enough, the query will exceed the platform's selectivity threshold, leading to a non-selective query error or severe performance degradation.

Answer

The CreatedDate field is not indexed by default in Salesforce.

Answer

The Id field should be the only field in the WHERE clause.

Answer

Apex triggers are preventing the query from completing.

Question 3

What is a 'Selective Query' in the context of Salesforce LDV?

Accepted Answer

A query that utilizes indexed fields to filter within defined thresholds.. A selective query is one that hits an index and returns a small, efficient subset of records. Salesforce imposes selectivity thresholds; if a query exceeds these (e.g., usually 30% of the first million records), it is considered non-selective. Ensuring queries are selective is the single most important factor in maintaining performance for large datasets, as it allows the database to avoid exhaustive scanning of rows.

Answer

A query that uses exactly five fields in the WHERE clause.

Answer

A query that returns fewer than 100 records total.

Answer

A query that uses only SOQL and no Apex logic.

Question 4

Which object type is most likely to cause performance issues in an LDV environment if not managed correctly?

Accepted Answer

An object with many lookup relationships to other parent objects.. Highly relational objects, especially those with many lookup relationships, are most prone to performance issues. Each lookup relationship can act as a locking point, and queries involving these fields often require complex joins. By understanding which objects are 'hot' in terms of frequency of access and relationship density, architects can prioritize them for indexing, archiving, and skinny table strategies to maintain system stability.

Answer

A flat custom object with no relationships.

Answer

An object containing only standard picklist fields.

Answer

A setup object maintained by the system administrator.

Question 5

What is the consequence of having a 'non-selective' query running on an object with 50 million records?

Accepted Answer

The query will be blocked or timed out by the platform.. Non-selective queries force the system to perform a full table scan. In an environment with millions of records, this consumes massive resources, often leading to query timeouts, increased CPU usage, and potential platform instability. Preventing this is the primary goal of data architecture, as it protects the multi-tenant environment and ensures that individual system processes do not consume resources allocated for other tenants or internal tasks.

Answer

The query will fail immediately with a compilation error.

Answer

The query will run faster because it ignores indexes.

Answer

The query will automatically create an index for the fields used.

Question 6

When designing a system for LDV, what is the role of an 'Indexed Field'?

Accepted Answer

To speed up the retrieval of records by narrowing search space.. Indexes are structures that the database uses to find records quickly without scanning the entire table. In an LDV environment, they are the primary defense against query timeouts. By ensuring that frequently used filters in SOQL queries are indexed, architects can significantly reduce the amount of data the database engine must process, keeping performance within acceptable levels regardless of the total record count.

Answer

To increase storage capacity in the Salesforce database.

Answer

To enforce uniqueness constraints on every custom field.

Answer

To automate the conversion of records to external objects.

Question 7

A data architect is designing a solution for a custom object Order__c that will contain 30 million records. Users need to frequently query orders by Customer__c (a lookup to Account) and Order_Date__c. The architect plans to create a composite custom index on Customer__c and Order_Date__c. Which consideration is most critical for the index to be used by the query optimizer?

Accepted Answer

The leading field in the composite index must be highly selective for the queries being executed.. For a composite index to be used by the query optimizer, the leading field must be selective. In this scenario, queries filter by Customer__c and Order_Date__c. If Customer__c is highly selective (e.g., a specific customer), the index will be used. If Customer__c is not selective, the optimizer may perform a full scan. Therefore, the most critical consideration is the selectivity of the leading field.

Answer

The composite index must include at least three fields to be effective for large data volumes.

Answer

The composite index must be created on fields that are not already indexed by default.

Answer

The fields in the composite index must be in the same order as they appear in the SOQL WHERE clause.

Question 8

A Salesforce org has a custom object Log__c with 5 million records. The object has a lookup to Case. Users report that when they view a Case record, the related list of Log__c records takes a long time to load. The data architect decides to create a custom index on the Case lookup field. After the index is created, performance improves. Which statement best explains why the index improved performance?

Accepted Answer

The index allows the related list query to use an index to quickly retrieve Log__c records for the specific Case.. The related list on Case queries Log__c records filtered by the Case lookup. This filter is highly selective because it returns only records for one Case. Creating a custom index on the lookup field allows the query optimizer to use the index, avoiding a full table scan. Thus, the index directly improves the performance of the related list query.

Answer

The index reduces the number of records that need to be queried by pre-filtering the Log__c object.

Answer

The index enables the related list to be loaded from a cache instead of the database.

Answer

The index sorts the Log__c records by Case, which speeds up the display order in the related list.

Question 9

A custom object named Invoice__c contains 15 million records. Reports and list views frequently filter on a custom date field, Invoice_Date__c, and are timing out. The field is not indexed. Which action should a data architect take to improve query performance while keeping the field available for filtering?

Accepted Answer

Create a custom index on Invoice_Date__c because it is used as a filter condition.. For large data volumes, selective queries require indexed filter fields. Requesting a custom index on a frequently filtered custom date field enables the query optimizer to use the index and avoid full table scans. This directly addresses the report and list view timeouts without changing the data model or introducing external dependencies.

Answer

Add a formula field that returns the value of Invoice_Date__c and filter on it instead.

Answer

Convert Invoice_Date__c to an external lookup field using Salesforce Connect.

Answer

Enable Divisions on the Invoice__c object to partition the data.

Question 10

A Salesforce org has a custom object Invoice__c with 8 million records. The business requires a dashboard that shows the total invoice amount grouped by Account and by Fiscal Year. The dashboard must refresh quickly, even during peak usage. The Invoice__c object has a lookup to Account and a formula field Fiscal_Year__c that extracts the year from Invoice_Date__c. What is the most appropriate design to support this dashboard efficiently?

Accepted Answer

Create a custom object Invoice_Summary__c that stores pre-aggregated totals per Account and Fiscal Year, and update it via batch Apex or scheduled flow when invoices change.. Pre-aggregating data into a summary object is the most reliable way to keep a dashboard fast when the source object has millions of records. By storing totals per Account and Fiscal Year, the dashboard queries a small, indexed dataset instead of scanning Invoice__c. Asynchronous maintenance via batch Apex or scheduled flow avoids impacting user transactions and keeps the summary current enough for reporting.

Answer

Enable Big Object indexing on Invoice__c and point the dashboard to a Big Object-backed report.

Answer

Build a custom Lightning Web Component that calls Apex to aggregate Invoice__c records on demand and display the results on the dashboard.

Answer

Create a summary report grouped by Account and Fiscal_Year__c, and add it as a dashboard component with a scheduled refresh.

Question 11

A custom object 'Invoice__c' contains 8 million records and has a lookup to Account. A nightly batch job deletes approximately 2 million old invoices using Database.delete() in batches of 200. Users report that the batch sometimes fails with 'UNABLE_TO_LOCK_ROW' errors when running concurrently with account updates. What is the most likely cause of these lock contention errors?

Accepted Answer

The delete operation is acquiring exclusive locks on the parent Account records referenced by the deleted invoices.. Deleting child records in a lookup relationship acquires exclusive locks on the parent records to maintain referential integrity. When concurrent updates target those same parent Accounts, lock contention triggers UNABLE_TO_LOCK_ROW errors. This is a common issue with high-volume deletes on objects with lookups to frequently updated parents.

Answer

The nightly batch job is running in parallel mode, which causes multiple batch threads to compete for the same parent Account locks.

Answer

The Invoice__c records have a master-detail relationship to Account, so deleting them cascades and locks all child records simultaneously.

Answer

The batch size of 200 is too large, causing the database to lock the entire Invoice__c table during each batch execution.

Question 12

A Salesforce org has a custom object Shipment__c with 12 million records. Users frequently run a SOQL query that filters on Status__c and Order_Date__c, and sorts by CreatedDate. The query is timing out. The org has an index on Status__c and a custom index on Order_Date__c. What is the most likely reason the query is still slow?

Accepted Answer

The query is non-selective because the combination of filters does not reduce the result set below the selectivity threshold, and sorting by CreatedDate requires additional processing.. For a 12-million-record object, selectivity is the critical factor. Salesforce uses a threshold based on object size to decide whether to use an index. Even with indexes on Status__c and Order_Date__c, if the filters return too many rows, the optimizer performs a full scan. Sorting by CreatedDate adds overhead. The correct diagnosis is that the query is non-selective and the sort compounds the cost.

Answer

The query is slow because it sorts by CreatedDate, which is not indexed, and Salesforce cannot sort large result sets without an index on the sort field.

Answer

The custom index on Order_Date__c is not used because Salesforce does not support custom indexes on date fields.

Answer

The query is slow because Status__c is a picklist field, and picklist fields cannot be indexed.

Question 13

A data architect is planning to implement a Skinny Table for a custom object with 10 million records to improve query performance. Which two considerations are critical when designing the Skinny Table? (Choose two.)

Accepted Answer

The Skinny Table requires a synchronization mechanism to keep data current with the main object.. A Skinny Table is a denormalized copy of a subset of fields from a large object, used to accelerate specific queries. The two critical considerations are selecting only the fields needed for common queries to minimize row size, and implementing a synchronization mechanism to keep the copy up to date. Without these, the Skinny Table would either be too large to be effective or become inconsistent with the source data.

Answer

The Skinny Table can be used as the primary data source for all reporting to replace the main object.

Answer

The Skinny Table should be indexed on all fields to maximize query flexibility.

Answer

The Skinny Table must include all fields from the original object to maintain data integrity.

Question 14

A company maintains a custom object Asset__c with 5 million records. They need to archive records older than 7 years to a Big Object for compliance. After archiving, the records must be queryable via SOQL for audits, but not editable. Which approach should an architect recommend?

Accepted Answer

Create a Big Object with the same fields, use a batch Apex job to copy old records, and then delete them from Asset__c. Provide audit access via a Lightning component that uses Async SOQL to query the Big Object.. Big Objects are the Salesforce-native solution for archiving massive datasets. They support high-volume storage and are queried asynchronously using Async SOQL, which is suitable for audit scenarios where real-time access is not required. Copying old records to a Big Object and deleting them from the transactional object reduces the object's size, improving query and DML performance. A Lightning component can invoke Async SOQL to retrieve archived records for audits.

Answer

Create a Big Object with the same fields as Asset__c, use a batch Apex job to copy old records, and then delete them from Asset__c. Provide audit access via a Visualforce page that queries the Big Object using SOQL.

Answer

Create a custom object Asset_Archive__c to store old records, and use a batch Apex job to move them. Provide audit access via standard reports and list views.

Answer

Use Salesforce Connect to create an external object that points to an external database containing the archived records, and provide audit access via external object list views.

Question 15

A data architect is reviewing a custom object that has grown to 8 million records. Users report that list views and reports are slow because they sort on a text field, Priority__c, which has only three possible values. What should the architect recommend to improve performance?

Accepted Answer

Remove sorting on Priority__c from list views and reports, and instead sort on an indexed field or filter on a more selective field.. For large data volumes, query performance is heavily influenced by field selectivity. A field with only three distinct values is not selective, so sorting or filtering on it forces the platform to scan many records. The best approach is to avoid sorting on such fields and instead use an indexed, high-cardinality field for sorting or filtering. This reduces the data set the query must process.

Answer

Enable Divisions on the object and assign records to divisions based on Priority__c.

Answer

Request a custom index on Priority__c to speed up sorting.

Answer

Convert Priority__c to a picklist field with a restricted set of values to improve selectivity.