Study guide

यह कंटेंट अभी तक आपकी भाषा में उपलब्ध नहीं है।

Also in 19 languages

Español · 日本語 · Português (Brasil) · Français · Deutsch · Italiano · 한국어 · 简体中文 · Português (Portugal) · 繁體中文 · Bahasa Indonesia · Türkçe · Polski · Tiếng Việt · ไทย · Українська · Español (México) · Français (Canada) · Deutsch (Schweiz)

An open sketchbook on a drafting table at dawn, a building sketched on one page and a data model on the other, with a pencil, an eraser, a cup and a lamp. — Image generated with AI

Study guide for chapter 8 of the Data Architect course. Read the five modules first; this page helps you check what stuck.

About this study guide. It’s built only from this course’s own content and the published Salesforce exam guide’s list of objectives. It is never based on real exam questions, and it doesn’t claim to know what’s on the exam. If you’ve taken the exam, please don’t send me questions from it: the certification agreement forbids sharing them, and memorizing answers is the wrong way to become an architect anyway.

This chapter is the query and search half of the exam’s Large Data Volume Considerations section (20% of the exam). The three checks and the thresholds are where I’d spend the most time: they’re the ideas people find hardest, and the thresholds are worth being able to work out on paper. Platform numbers change, so check the current ones before you rely on them.

For the Exam

Tick them off as you go. Each one links to where it’s taught.

Inside the engine (module 1)

I know Salesforce is multitenant and metadata-driven: many orgs share one platform, kept apart by the org ID, and adding a field adds metadata, not a database column. (One Building, Many Tenants, No New Tables, Just Metadata)
I know standard and custom fields are stored separately, so a query that uses both does a join, and that skinny tables avoid it. (The Join You Never Wrote)
I know custom fields can’t use a native database index, so Salesforce copies indexed values into index tables. The internal table names aren’t exam material. (Why a Normal Index Doesn’t Fit)

Indexes (module 2)

I can explain a table scan and an index (the library and the card catalog), and that an index is a copy: it makes finding faster and every save slower. (The Advanced Technology: Card Catalogs, Every Copy Has to Be Kept Up to Date)
I know SOQL asks by structure and SOSL asks by words. (Two Ways to Ask)
I know the standard indexed fields: RecordTypeId, Division, CreatedDate, Systemmodstamp (LastModifiedDate), Name, Email on contacts and leads, lookup and master-detail fields, and the record ID. (Standard Indexes)
I know a custom index comes from Salesforce Customer Support or the Metadata API, and that marking a custom field as an External ID (Auto Number, Email, Number or Text) or Unique indexes it. (Custom Indexes)
I know which fields can’t have a custom index: long and rich text areas, multi-select picklists, encrypted text, currency in a multi-currency org, and non-deterministic formulas. A formula can be indexed only if it’s deterministic: no TODAY() or NOW(), and no values from another object. (Which Fields Can Be Indexed)

When Salesforce uses an index (module 3)

I know the three checks, the nitro: the field is indexed; I ask in a selective way; and few enough records match. (The Nitro Boost, All Three: the Boost)
I know what asking in a selective way means. Selective: equals, starts with (LIKE 'abc%'), a short IN list, a range on a date or number. Not selective: contains and ends with (a leading %), negative operators (!=, NOT LIKE, NOT IN) and comparisons with an empty value. (Asking the Index the Right Way)
I can work out the thresholds: standard index, 30% of the first million records plus 15% beyond, capped at 1 million; custom index, 10% plus 5%, capped at 333,333. A standard index on 2 million records allows 450,000; a custom index on 1 million allows 100,000. (The Thresholds)
I know a filter can stop being selective without the query changing: cases close, history piles up. (The Closed-Cases Trap)
I plan with 30% growth a year, and I don’t confuse it with Well-Architected’s 30% reserve under governor limits. (Add 30% Growth)

How Salesforce executes a query (module 4)

Search (module 5)

I know when to use SOSL instead of a pile of SOQL ORs. (Query Starts With Structure; Search Starts With a Term)
I know search finds tokens in its own index first, then fetches the records, and that the index is asynchronous: about 15 minutes or more for new text, and hours after a bulk load. (How Search Works: Tokens First, Then the Fetch, Freshness Is Part of the Design)
I know SOSL’s limits: at most 2,000 records searched, then permissions applied; 250 for one object unless you add WHERE or ORDER BY; up to 2,000 divided by the number of objects (and no more than 250 each) when you search several; 20 SOSL queries per Apex transaction. (Asking Search Well)
I can explain search crowding: the cut at 2,000 comes before sharing, so records a user can’t see take up places, and a record they can see can be left out. (Search Crowding, Step by Step)
I know the levers: search groups (NAME, EMAIL, PHONE), RETURNING, specific terms over wildcards, and wildcards only in the middle or at the end. (Asking Search Well)
I know how to reduce crowding: decide which objects each profile searches, take noisy fields out of the index, archive history, and check as the user. (Mitigating Search Crowding: A Playbook)

Beyond the Exam

The exam is the floor, not the ceiling. These are the habits from this chapter that an architect needs, whether or not a question ever asks about them.

Design against the behavior Salesforce documents, not a table layout. Salesforce rebuilt the engine underneath once already.
An index is a copy. Every one makes finding faster and saving slower.
“Don’t worry, that field is indexed” is never the end of the conversation. Ask the three questions, then do the math.
A query can go bad without changing.
Measure before you optimize, and test at the zeros you’ll actually have.
Index when it’s a clean, general solution; redesign instead of indexing a one-off.
Metadata should tell the truth. Don’t mark a field as an External ID just for the index.
Sometimes the best query optimization is a smaller active population.
Search to discover; query to operate. Search isn’t a data model.
Data can deserve to exist without deserving to appear in search.
Search runs as someone. Check as the user, not as an admin.

Words to Know

Term	In one line
Multitenant	Many companies’ orgs share one platform, kept apart by the org ID
Metadata-driven	Objects and fields are described as data, not built as database tables
Table scan	Touching every record to find the ones you want
Index	A copy of one field’s values, with each record’s location, for finding them fast
Index table	Where Salesforce copies indexed custom-field values
Query optimizer	The part of the platform that chooses the cheapest path for every query, report and list view
Selective	Asked in a way an index can use, and matching few enough records
Selectivity threshold	The most records an indexed filter can match and still be used
Query	Finding which records match
Fetch	Carrying back the fields of the records found
Join	Crossing from one table or object to another
Query Plan	A tool showing the optimizer’s possible paths and their relative cost
Custom index	An index on a custom field, from Customer Support, the Metadata API or an External ID
Two-column index	One index over two fields used together
Skinny table	A copy of selected fields from one object, kept by Salesforce, for fast reads without the join
Division	A partition of a large org’s data that reduces the records queries return
Database statistics	The counts the optimizer uses to choose a plan, gathered nightly
SOQL	Salesforce’s query language for structured questions
SOSL	Salesforce’s search language for finding words across objects
Search crowding	Matches the user doesn’t want, or can’t see, filling the 2,000 places before the record they need

Check Yourself

Short scenarios in the style of the course. Decide your answer before you open it.

1. An object has 10 million records. A filter on a custom-indexed field returns 400,000. Will the optimizer use the index?

Probably not. The custom-index threshold is 10% of the first million plus 5% beyond, capped at 333,333; at 10 million the cap applies, and 400,000 is over it. Check with the Query Plan. Reread The Thresholds.

2. An object has 2 million records. A filter on Name, a standard index, returns 500,000. Selective?

No. A standard index allows 30% of the first million (300,000) plus 15% of the next million (150,000): 450,000. At 500,000 the filter is over the threshold, and the optimizer will look elsewhere or scan the table. Reread As the Object Grows.

3. A list view on 8 million cases filters on Status not equal to Closed. Status has a custom index. Why is it slow?

A negative operator isn’t asked in a selective way, so the index can’t help, and the optimizer runs for list views just as it does for code. Ask positively instead, for the open statuses, and check that the open cases stay under the threshold. Reread Asking the Index the Right Way and When Is the Librarian Working? All the Time.

4. A query on IsClosed = true was fast at launch and is slow three years later. Nothing in the code changed. Why?

The data aged: cases close, so a filter that once matched a small slice now matches almost everything. Selectivity depends on distribution, which changes over time. Reread The Closed-Cases Trap.

5. Someone asks for an index for a report run once a year. What do you ask?

Why it’s slow, and whether a permanent index is worth its cost for a one-off. Measure the distribution and read the Query Plan first; consider rewriting the query, redesigning, or archiving history so the active population is smaller. Reread Before You Ask for an Index.

6. An important query needs recent line items for one Retail Partner, but the partner is on the parent work order, and there are 14.5 million line items. Options?

Redesign: copy the partner onto the line item when it’s created (and index it if the filter needs it), so the query filters the child directly. Weigh the benefit against the duplicate data and its upkeep; here the partner never changes, which helps. Reread When It’s Slow: Index, Rewrite, Redesign or Archive.

7. "Let's ask Support for a skinny table index on Account." What's wrong with that sentence?

A skinny table isn’t an index. It’s a copy of selected fields from one object, which avoids the join between standard and custom fields and makes the fetch lighter. It’s used automatically only when a query, report or list view asks for its fields alone. Reread Skinny Tables.

8. Last night the team loaded 2 million records through the Bulk API. This morning some queries are slow and new records can't be found in search. Broken?

Probably not. The optimizer’s statistics are gathered nightly, so they may not reflect a very large load yet; and the search index is asynchronous, so a bulk load can take hours to become searchable. Plan loads with both in mind. Reread Database Statistics: The Librarian’s Notebook and Freshness Is Part of the Design.

9. A user types any text and expects to find the customer by name, email, phone or notes. The developer builds a SOQL query with ten LIKE clauses joined by OR. Better?

It’s a search problem: use SOSL, which is built for words across fields and objects. A long OR chain also needs every filter to be selective and indexed on its own, and contains-style LIKE never is. Reread Query Starts With Structure; Search Starts With a Term.

10. A care agent searches for a customer's last name and gets 1,900 results, but not the case she needs, which she can open from a list view. Why?

Search crowding. The name matched more than 2,000 records, and search keeps the first 2,000 before it checks what she can see: 100 of those she can’t see, so 1,900 come back, and her case may be among the matches left out. Use a more specific term, search one object, and reduce the noise at the source; then check as her, not as an admin. Reread Search Crowding, Step by Step and Mitigating Search Crowding: A Playbook.

11. Automation must find a delivery exception the moment it's created. Use search?

No. The search index updates asynchronously and can lag by 15 minutes or more. The process knows the structure, so a structured query is the deterministic choice. Reread Freshness Is Part of the Design.

Explore

Salesforce Certified Platform Data Architect Exam Guide (opens in a new tab), Salesforce Help, checked 2026-11-01: the official list of objectives and their weights. Always check the current version before you book the exam.
Each module’s own Explore section lists the Salesforce documentation behind its facts, with the date checked.