Twelve Million Assets, Three Filters, One Timeout
A global manufacturer stores 12 million Asset records against 400,000 Accounts. Field service agents open an Asset list view filtered by Status, Service_Region__c (picklist with 6 values) and Warranty_End_Date__c (date). The list view takes 45 seconds and frequently returns a query-timeout error. An Apex controller runs the same three filters plus ORDER BY CreatedDate. Record access is driven by a territory hierarchy: each agent sees roughly 2,000 Accounts, and ownership-based sharing rules plus manual shares recalculate nightly. The org also holds 1.1 million Cases and about 600 custom fields spread over five objects. Retention policy requires seven years of history, and roughly 30% of Assets are inactive. Three hundred managers need reporting by region and warranty month. The team's proposal is to request a custom index and to build a skinny table. You are asked to review that proposal before it goes to the platform team.
1. Explain how you would diagnose the timeouts and whether the proposed custom index plus skinny table would actually fix the list view and the Apex query.
Read the worked answer
Diagnosis first. Use the Query Plan tool on the Apex SOQL and on a stripped-down version of the list view filter to see cost, cardinality and whether a table scan occurs. Then examine each filter's selectivity: a 6-value picklist returns roughly 17% of 12 million rows for any single value, and nothing at all narrow, so it is non-selective. Warranty_End_Date__c is a standard date field and is not indexed by default. Status is likely a small picklist and equally non-selective. Combined with a sharing model where each user sees 2,000 Accounts and ownership-based rules force the platform to resolve sharing before returning rows, the query is scanning and then filtering.
On the proposal: a custom index helps only when the filter is selective; indexing a field that matches 17% of rows usually gets ignored by the optimiser. Salesforce support grants custom indexes sparingly and mostly on External ID or unique fields. A skinny table does help - but only for custom SOQL paths such as the Apex controller. It is not consulted by list views, standard pages or reports, so it cannot fix the agent-facing symptom.
So: keep the skinny table for the Apex path, drop the custom-index request unless you can identify a genuinely selective field, and fix the list view differently - default the view to My Assets, add a selective filter such as an indexed External ID or a bucketed warranty-period field, and move the 30% inactive rows out of the hot object.
2. The 30% inactive Assets are still inside the seven-year retention window. What data-management approach do you recommend, and what are its limits?
Read the worked answer
Split hot and cold data rather than deleting anything. Active Assets and Assets with open work stay in Asset, narrow and indexed. Inactive Assets move to a Big Object keyed on a composite of Asset identifier and a date, written asynchronously during the nightly load and queried by an async SOQL path in Apex or by an external reporting tool. Retention is satisfied because the Big Object holds the full seven years and is queryable on its indexed fields.
The limits matter and should be written into the design: Big Objects only support asynchronous SOQL, cannot appear in list views or standard reports, do not support all field types, and are effectively append-only, so a correction means a new row plus a version marker. Index fields are fixed at creation, so choose them from the actual query patterns (Asset ID, warranty date, region) rather than guessing.
A defensible alternative is archiving to the data warehouse and exposing the cold set through Salesforce Connect external objects, which keeps ad hoc reporting in one place but adds latency and an integration dependency. Whichever you choose, keep a rollup summary or denormalised flag on the Asset so agents can see that history exists without querying the archive. Also measure the win before declaring success: the goal is to shrink the scanned row count from 12 million toward 8 million with a selective index path on top, not just to relocate data.
3. The ERP reloads two million Asset records monthly by upserting on the Salesforce 15-character ID held in the ERP. Recommend an identifier and load strategy and justify each choice.
Read the worked answer
The Salesforce ID is the wrong key. It is org-specific, changes with a migration or sandbox refresh, and is blank on first insert, which is exactly how duplicate records appear. Replace it with a stable, unique External ID on Asset derived from the ERP's durable business key, mark it unique and case-sensitive as appropriate, and upsert against it. That gives idempotency: re-running a failed job updates rather than duplicates.
Load strategy: Bulk API 2.0 with CSV, jobs split into roughly 100,000-record chunks so a failure is contained and retried without reprocessing the whole file. Use serial mode if you observe lock contention with concurrent ERP writes, and note the throughput trade-off. Run off-peak, order the data so parents exist before children, and validate the external ID uniqueness in staging before submission.
Two performance levers people forget. First, defer sharing recalculation on Asset for the duration of the load and recalculate once afterwards, then confirm the recalculation actually completes - a deferred state left running is its own incident. Second, do not disable triggers and validation rules permanently; instead confirm the integration user's profile and permission sets are least-privilege and the same rules apply in every environment. Finally, log the job ID, records processed and row-level errors into a custom object so a failed monthly run is diagnosable without a support case.