Salesforce SOQL and Large Data Volume Interview Questions

This set comes from the technical round for developers with a few years of delivery behind them. The panel has read a resume claiming production experience, and wants to know whether you have worked in an org with millions of rows or only in a developer edition with fifty Accounts. The thirty-three questions below cover query construction, selectivity and the indexes behind it, data skew, archival, and the options for data that should not sit in the core tables at all. Answers are written at the level the interviewer will push you to, not the level of a definition.

SOQL versus SOSL and query construction

Difference between SOQL and SOSL.

SOQL retrieves records from one object at a time, with related records pulled in through relationship queries. SOSL searches text across many objects in one call using the search index.

The rule: if you know the object and the field, use SOQL. If a user typed a word into a search box and you do not know where it lives, use SOSL. A filter written as LIKE '%mumbai%' cannot use an index and scans the table.

What are Semi-join queries?

A semi-join filters the parent object with an IN subquery on a related object. You get parents that have at least one matching child, without returning the child rows.

SELECT Id, Name, Industry
FROM Account
WHERE Id IN (
    SELECT AccountId
    FROM Opportunity
    WHERE StageName = 'Closed Won'
      AND CloseDate = THIS_YEAR
)

The rules the panel wants: a maximum of two semi-join or anti-join subqueries in one query, the subquery must select an Id or a reference field, you cannot nest one inside another, and OFFSET is not allowed in the subquery.

What are Anti-join queries?

Same construction with NOT IN. It returns parents with no matching child, which is how you find Accounts with no open Case.

SELECT Id, Name
FROM Account
WHERE Id NOT IN (
    SELECT AccountId
    FROM Case
    WHERE Status != 'Closed'
)

Anti-joins are the more expensive of the two, because the optimiser has to establish absence. On a large object this is often the query that times out in production after running fine in the sandbox. Where you need the pattern at scale, maintain an indexed flag field on the parent and filter on the flag.

What are Aggregate queries?

Any SOQL using COUNT(), COUNT_DISTINCT(), SUM(), AVG(), MIN() or MAX() returns AggregateResult objects rather than sObjects. You read values by alias.

SELECT OwnerId, StageName, COUNT(Id) dealCount, SUM(Amount) totalValue
FROM Opportunity
WHERE CloseDate = LAST_N_MONTHS:12
GROUP BY OwnerId, StageName
HAVING SUM(Amount) > 500000

In Apex you cast the value out: (Decimal) ar.get('totalValue'). Two limits matter. A query using GROUP BY returns at most 2,000 rows, and every AggregateResult counts against the 50,000 query rows limit.

What is GROUP BY ROLLUP?

GROUP BY ROLLUP adds subtotal rows and a grand total to a grouped result, so you do not compute them in Apex. GROUP BY CUBE produces subtotals for every combination instead. Both support up to three fields.

SELECT LeadSource, Rating, GROUPING(LeadSource) srcSubtotal,
       GROUPING(Rating) ratingSubtotal, COUNT(Id) leadCount
FROM Lead
GROUP BY ROLLUP(LeadSource, Rating)

GROUPING() returns 1 when the row is a subtotal for that field and 0 when it is a real value. Without it you cannot tell a subtotal row from a data row.

What is OFFSET limitation?

OFFSET skips rows before returning results, and the maximum offset is 2,000. Ask for more and the query fails with a number outside valid range error. It is also not supported inside the subquery of a relationship query.

So OFFSET is only good for shallow paging in a UI. For a large result set, page on the record Id, which stays selective because Id is indexed:

SELECT Id, Name
FROM Account
WHERE Id > :lastSeenId
ORDER BY Id
LIMIT 200

What is Query Rows limit?

A single Apex transaction can retrieve 50,000 rows through SOQL, and that figure is the same synchronous and asynchronous. Contrast it with the Query Locator: Database.getQueryLocator returns up to 10,000 rows in ordinary Apex, but returned from the start method of a Batch Apex class it can cover up to 50 million records.

That is why Batch Apex exists. Each execute gets a scope of records, 200 by default and up to 2,000, and runs as its own transaction with fresh limits, so the 50,000 ceiling applies per execution and not to the job. One correction candidates need: a SOQL for loop does not raise the row limit. It chunks records to keep heap under control, but all rows still count.

What the interviewer is actually checking

• Whether you reach for SOSL on a text search instead of a leading wildcard LIKE
• Whether you know the subquery restrictions before proposing a semi-join in design
• Whether you understand why OFFSET is not a paging strategy at scale
• Whether you can separate the 50,000 row transaction limit from the batch Query Locator limit

Selectivity and indexing

What is Indexing?

Salesforce maintains indexes on the underlying tables so the optimiser can jump to a set of rows instead of scanning everything.

Standard indexed fields include the record Id, Name, OwnerId, CreatedDate, SystemModstamp, RecordTypeId, Division, the Email field on Lead and Contact, and every foreign key, which means all lookup and master-detail relationship fields.

Custom fields get an index when you mark them as External ID or Unique. Beyond that, Salesforce Support can create a custom index on request, including a two-column index or one on a deterministic formula field. Multi-select picklists, long text areas, encrypted text and non-deterministic formula fields cannot be indexed normally.

What is Selective Query?

A query is selective when the filter uses an indexed field and the rows that filter matches fall under the index selectivity threshold. Both halves are required. Filtering on an indexed field that matches most of the table is still not selective.

Several things knock a filter off the index even when the field is indexed: negative operators such as !=, NOT IN and NOT LIKE, a leading wildcard, comparing one field with another, and null checks, since a standard index does not hold null rows. For an OR condition, every field must be indexed and each must clear its threshold, or the query falls back to a scan. Selectivity is also what prevents the non-selective query error on objects above 200,000 rows.

What is Query Plan Tool?

The Query Plan tool lives in the Developer Console, switched on under Help, then Preferences, then Enable Query Plan.

Each plan shows four things. Leading Operation Type is what the optimiser chose, where Index is good and TableScan means it reads everything. Cost is relative, and below 1.0 is treated as selective. Cardinality is the estimated rows returned, and sObject Cardinality the approximate total on the object.

The trainer's usual observation applies here. Candidates fail on demonstration, not knowledge. Many can define a selective query and then cannot open this tool and point at the filter in their own WHERE clause that caused the table scan.

What is External ID?

An External ID is a custom field flagged to hold the primary key of a record in an outside system. Flagging it gives the field a custom index, makes it available as an upsert match key, and lets the REST API address a record by that key instead of the Salesforce Id.

It is available on Text, Number, Email and Auto Number fields, with an optional case sensitive setting on text. The integration value is real: you can insert a Contact and set its Account in the same payload by quoting the Account's external key, with no query and no round trip to fetch Ids.

What are Skinny Tables?

A skinny table is a copy of the frequently used fields of one object, created and maintained by Salesforce Support and kept in sync with the source. Reads get faster because the platform avoids joining the base table to the custom field table, and because the skinny table holds no soft deleted rows.

The constraints are the exam material: up to 100 columns, fields from one object only, no cross object fields, a restricted list of field types, and no self service switch. You raise a case and Salesforce decides. They are copied into a Full sandbox; for other sandbox types you ask again after refresh. Treat them as the last step, after you have fixed the query, added the index and archived what you do not need.

What the interviewer is actually checking

• Whether you can list the standard indexed fields without hesitating
• Whether you know an indexed field and a selective query are not the same thing
• Whether you have actually run the Query Plan tool on a slow query
• Whether you treat skinny tables as a support request rather than a config option

Large data volumes and skew

What is Data Skew?

Data skew is an uneven distribution where a very large number of child records point to one parent. The working guideline is more than 10,000 children under a single parent, usually a catch-all Account holding unassigned Contacts or Cases.

The damage is lock contention. Updating a child locks the parent so sharing and rollup information stays correct, so with thousands of children under one parent, parallel loads collide and you see UNABLE_TO_LOCK_ROW. Fixes are structural: spread children across multiple parents, load in serial mode, or group records by parent so one batch does not hit the same parent from several threads.

What is Ownership Skew?

Ownership skew is the same shape on the owner field: more than 10,000 records of one object owned by a single user, typically an integration user or a default queue owner.

The cost appears when the ownership picture changes. Moving that user in the role hierarchy or a sharing recalculation forces the platform to adjust share records for everything they own and for every role above them, which can run for hours. The standard mitigation is to keep that user out of the role hierarchy, since a user with no role has no upward sharing to recalculate.

Memorised definitions collapse the moment the interviewer asks for a real project example, so have one ready: which object, roughly how many records, what error you saw, what you changed.

How do you handle large data volumes?

The tactical answer, in order:

1. Make every query selective, with an index on the filter field and a filter returning a small share of the table.
2. Move processing to Batch Apex so each chunk gets its own governor limits.
3. Keep triggers light during bulk loads and defer heavy cross object work to asynchronous processing.
4. Load with Bulk API, in serial mode where parent skew makes lock errors likely.
5. Remove what you do not need with hard delete, so soft deleted rows stop slowing queries.
6. Ask Support for a custom index or a skinny table once the design work is done.

What is LDV strategy?

The strategy question is architectural. It covers how you stop the org reaching a painful size at all.

• Data model. Keep fast growing objects narrow, away from heavy formula and rollup fields.
• Divisions. In very large orgs, divisions partition data so queries and reports work on one slice.
• Archival. Decide at design time what leaves the core objects and where it goes.
• Mashup instead of copy. If the data only needs to be displayed, keep it in the source system and surface it through external objects.
• Deferred sharing maintenance. During a large load, sharing recalculation is suspended and run once at the end.
• PK chunking. For extracting large objects through Bulk API, chunk the query by record Id ranges.

What is Batch data processing?

Batch Apex implements Database.Batchable with three methods. start returns the scope, normally a Database.QueryLocator covering up to 50 million records. execute runs once per chunk, default 200 and maximum 2,000 records, each an independent transaction with fresh limits. finish runs once at the end.

Two details get asked. State does not carry between executions unless the class implements Database.Stateful, and the number of batch jobs queued or active at one time is capped at five.

The trainer's other observation shows up here. Candidates know the syntax but not the execution: when it runs, where it runs, and what data it can reach. Be ready to say which user context the batch runs in and what happens to records that fail inside one chunk.

What is Upsert?

Upsert is a single DML operation that inserts or updates based on a match key, either the record Id or a field marked as External ID.

upsert accountsToLoad Account.ERP_Account_Id__c;

No match creates a record, one match updates it, more than one match throws an error on that row rather than guessing. In an integration this removes the query-then-decide step and makes the load idempotent, so replaying the same file creates no duplicates.

What is Data Loader vs Import Wizard?

Short version: the wizard is for a business user loading a clean file of Contacts, Data Loader is for a developer doing a migration or a scheduled load.

What is Data Archival?

Archival moves records out of the transactional objects once they stop being operational, so queries, reports and sharing calculations work over a smaller table.

The usual destinations are Big Objects inside the platform, an external warehouse surfaced through Salesforce Connect if users still need to see the data, or an export followed by hard delete where a regulator only requires a file. Two things make the design credible: a retention rule agreed with the business, and a job that removes the archived rows from the source object. An archive that copies data but never deletes the original has solved nothing.

What is Reparenting?

Reparenting is changing the parent of a child record in a master-detail relationship. By default master-detail does not allow it, and you enable it with the Allow reparenting option on the relationship field. Lookup relationships are reparentable already.

At scale this is not free. Reparenting rewrites the child's sharing, because a detail record inherits sharing from its master, and it forces rollup summary recalculation on both the old and the new parent.

What is Cascade Delete?

In a master-detail relationship, deleting the master deletes its detail records, and the deletion cascades down the chain. Those children go to the Recycle Bin with the parent and return together if the parent is restored.

Lookup relationships do not cascade by default; the field is cleared. You can configure a lookup either to block the delete or to cascade it, and where cascade delete is configured on a lookup it bypasses the sharing settings of the user doing the delete. Deleting a parent on top of a skewed set of children puts all that work into one transaction, so delete the children in batches first.

What is Hard Delete?

A normal delete is soft. The row moves to the Recycle Bin, stays recoverable for 15 days, and still occupies space in the underlying table. Hard delete skips the Recycle Bin and the record cannot be recovered.

It is available through the Bulk API hardDelete operation and through Data Loader, and it requires the Bulk API Hard Delete permission, which is not on by default. Soft deleted rows keep slowing queries on large objects until they are purged, so hard delete is what actually restores performance after a cleanup.

What is Field History Tracking?

Field History Tracking records the old and new value each time a tracked field changes. You can track up to 20 fields per object, and entries land in the object's History object, which you can query.

Retention is the part candidates miss. Standard history is retained for 18 months in the org and up to 24 months through the API. Field Audit Trail, part of Shield, extends that to as long as 10 years by moving entries into the FieldHistoryArchive Big Object under a retention policy you define. Formula, roll-up summary and auto number fields cannot be tracked.

What is Audit Trail?

Setup Audit Trail logs configuration changes: who changed what in Setup, and when. The Setup page shows recent entries and lets you download roughly the last six months, and the same data is available through the SetupAuditTrail object. For a longer window you export on a schedule.

The distinction worth stating is that Setup Audit Trail covers metadata and configuration changes, while Field History Tracking covers data changes.

What is Shield Event Monitoring?

Event Monitoring is one part of Salesforce Shield, alongside Platform Encryption and Field Audit Trail. It exposes what users actually did: logins, API calls, Apex execution, report exports, page views.

It comes in two forms. EventLogFile records are generated as files on a daily cycle, hourly with the hourly option, and are usually pulled into an external monitoring tool. Real-Time Event Monitoring streams selected events as they happen, can store them in Big Objects, and works with Transaction Security policies that block or challenge an action, such as a large report export from an unknown IP.

What the interviewer is actually checking

• Whether you can state the 10,000 record guideline for skew and explain the locking behind it
• Whether you know ownership skew is fixed in the role hierarchy, not in the data
• Whether you can sequence an LDV fix instead of jumping to a support case for an index
• Whether you understand that soft deleted rows keep costing you until they are purged

External and streaming data

What is External Objects?

External objects behave like custom objects in the user interface and in SOQL, but hold no data in Salesforce. They carry the __x suffix, and every read goes out to the source system when the user opens the page.

They support three relationship types. A lookup works between external objects, an external lookup points from a child to an external object parent, and an indirect lookup points from an external object child to a standard or custom object parent by matching on an External ID field. The trade offs are no triggers, limited automation, reporting restrictions, and performance that depends entirely on the external system.

What is Salesforce Connect?

Salesforce Connect is the feature that makes external objects work. It uses an adapter to reach the source: OData 2.0 and OData 4.0 adapters for a service that speaks OData, the cross org adapter for another Salesforce org, and a custom adapter written with the Apex Connector Framework for anything else.

It is a separately licensed add on, the number of external objects per org is capped, and calls are metered per hour. The comparison to make: Salesforce Connect reads live and stores nothing, while an ETL tool copies data in and you then own the storage and the sync.

What is Big Object?

A Big Object stores hundreds of millions or billions of records with a query and storage model built for that scale. There are standard ones, such as FieldHistoryArchive used by Field Audit Trail, and custom ones with a __b suffix. What makes it different from a custom object:

• It is defined by a composite index of up to five fields, set at creation and effectively fixed once records exist.
• Queries must filter on the index fields from left to right, starting with the first.
• No triggers, no flows, no standard list views, no Recycle Bin.
• Records are written with Database.insertImmediate, Bulk API or the async path, with no transactional rollback.

That is the trade. You get scale and give up the platform features that assume a normal object.

What is Async SOQL?

Async SOQL runs a query in the background over a very large data set and writes the result into a target object instead of returning rows to your code. You submit it through a REST endpoint and poll for status.

It exists because a synchronous query cannot summarise a Big Object holding a billion rows inside a transaction. Typical use is aggregating archived history into a reporting object overnight. Two caveats: it is not real time, so nothing a user waits on should depend on it, and availability depends on what your org is entitled to.

What is CDC (Change Data Capture)?

Change Data Capture publishes an event whenever a record is created, updated, deleted or undeleted on an enabled object. The event carries the change type, the changed fields and the record Ids affected, and events from one transaction share a transaction key.

Subscribers can be an external system over the Pub/Sub API or CometD, a Lightning component using empApi, or a change event trigger in Apex. Change event triggers run asynchronously, after the transaction that caused them, under the Automated Process user, and events stay in the event bus for up to 72 hours for replay. If you claim CDC on your resume, expect the follow up on how you handled the trigger running outside the original transaction.

What is Streaming API?

Streaming API is the family of push based event channels: PushTopic events driven by a SOQL query, generic events, platform events you define and publish, and change data capture events. Clients subscribe over CometD long polling, or over the Pub/Sub API using gRPC, which is the current recommendation for new work.

The mechanism to explain is durability. Every event carries a replay Id, so a subscriber that drops off resumes from the last Id it processed. The retention window differs by event type, shorter for PushTopic and generic events and longer for high volume platform events and change events, and a subscriber down past the window loses those events for good.

What is Bulk API?

Bulk API is the asynchronous, REST based API for loading, updating, deleting and extracting large volumes. You submit the data, the platform processes it in the background, and you poll the job for results.

Version 1.0 uses an explicit job and batch model, where you split the data yourself into batches of up to 10,000 records. Version 2.0 is simpler: create the job, upload the whole CSV, close it, and the platform does the chunking and retries. Parallel processing is the default and is faster, while serial mode processes one batch at a time and is what you switch to when parent skew causes lock errors. Hard delete is available as an operation, and PK chunking splits a large extract into Id ranges.

What the interviewer is actually checking

• Whether you can say when data should stay outside Salesforce instead of being copied in
• Whether you know what a Big Object gives up in exchange for scale
• Whether you understand that a change event trigger runs after the original transaction
• Whether you know why and when a bulk load is switched to serial mode

Closing

These thirty-three questions separate people who have written SOQL from people who have run it against a table with twenty million rows. The pattern is consistent: know the limit, know the index behind it, and know what you did in a real org when it went wrong.

Prepare one story per topic, with the object, the row count, the error and the fix. Interviewers go several levels deep on anything your resume claims, and a specific answer holds up where a definition does not.

For structured practice on the data side of the platform, with real org exercises rather than slides, look at the Salesforce training in Pune with placement support from RizeX Labs.