Encrypted Field Query Patterns
The Core Constraint
Shield Platform Encryption changes SOQL semantics. Plan the queries
FIRST, then choose the encryption scheme. Reversing that order means
rebuilding schemas later.
Scheme Matrix
| Scheme |
Use Case |
SOQL Support |
| Probabilistic |
Max security, display-only |
NO filter, NO sort, NO group by, NO index |
| Deterministic (case-sensitive) |
Exact match required |
=, IN, joins, unique indexes; NO range, NO LIKE |
| Deterministic (case-insensitive) |
Exact match, case-agnostic |
=, IN; NO range, NO LIKE |
No scheme supports LIKE on encrypted fields. No scheme supports range
(>, <, BETWEEN) operators.
Decision By Query Pattern
| Query |
Scheme |
| Display only (never filter) |
Probabilistic |
| Lookup by exact value |
Deterministic (case-sensitive if value is case-stable) |
| Customer search by name (case-agnostic) |
Deterministic case-insensitive |
| Range query (date, amount) |
Do not encrypt — use field-level security + masking |
| LIKE search |
Do not encrypt — or store a derived hash/index field |
| Sort by field |
Only if you rely on display order, not SOQL ORDER BY |
Indexing
- Custom indexes on deterministic encrypted fields are supported for
equality filters.
- Standard indexes (lookup, master-detail) are not automatically present
on encrypted fields — request a custom index.
- Query selectivity degrades if the encrypted field has low cardinality
post-encryption.
Aggregation And Reporting
- GROUP BY on encrypted field: supported only with deterministic.
- SUM / AVG on an encrypted number: generally not supported — leave
aggregatable numerics unencrypted unless compliance requires.
- Reports with filters on encrypted fields: equality works for
deterministic; others fail at runtime.
Apex Patterns
Encrypted fields read and write transparently in Apex for users with
"View Encrypted Data" permission. Without that permission, reads return
masked values (*********). Plan for:
- Test users with both modes.
- Avoid logging encrypted values in
System.debug even when you have
permission — Event Monitoring / debug logs may persist.
Recommended Workflow
- Enumerate every query / report / list view / filter that touches
each candidate field.
- Classify each query: exact, range, LIKE, aggregate, display.
- Apply the scheme matrix. Flag fields where encryption blocks a
required query.
- For blocked fields, decide: drop the requirement, skip encryption,
or derive a hashed index field (one-way).
- Request custom indexes on deterministic fields used as filters.
- Test with both "View Encrypted Data" and masked users.
- Document the scheme per field in a schema decision log.
Official Sources Used
1---2name: encrypted-field-query-patterns3description: Design SOQL, filters, reporting, and indexes against Shield Platform Encryption fields. Trigger keywords: Shield Platform Encryption, encrypted field query, probabilistic vs deterministic encryption, encrypted SOQL filter, encrypted field index. Does NOT cover: Classic Encryption (deprecated), field-level security policy, or tenant secret key rotation.4---5
6# Encrypted Field Query Patterns
7
8## The Core Constraint
9
10Shield Platform Encryption changes SOQL semantics. Plan the queries
11FIRST, then choose the encryption scheme. Reversing that order means
12rebuilding schemas later.
13
14## Scheme Matrix
15
16| Scheme | Use Case | SOQL Support |
17|---|---|---|
18| **Probabilistic** | Max security, display-only | NO filter, NO sort, NO group by, NO index |
19| **Deterministic (case-sensitive)** | Exact match required | `=`, `IN`, joins, unique indexes; NO range, NO LIKE |
20| **Deterministic (case-insensitive)** | Exact match, case-agnostic | `=`, `IN`; NO range, NO LIKE |
21
22No scheme supports `LIKE` on encrypted fields. No scheme supports range
23(`>`, `<`, `BETWEEN`) operators.
24
25## Decision By Query Pattern
26
27| Query | Scheme |
28|---|---|
29| Display only (never filter) | Probabilistic |
30| Lookup by exact value | Deterministic (case-sensitive if value is case-stable) |
31| Customer search by name (case-agnostic) | Deterministic case-insensitive |
32| Range query (date, amount) | **Do not encrypt** — use field-level security + masking |
33| LIKE search | **Do not encrypt** — or store a derived hash/index field |
34| Sort by field | Only if you rely on display order, not SOQL ORDER BY |
35
36## Indexing
37
38- Custom indexes on deterministic encrypted fields are supported for
39 equality filters.
40- Standard indexes (lookup, master-detail) are not automatically present
41 on encrypted fields — request a custom index.
42- Query selectivity degrades if the encrypted field has low cardinality
43 post-encryption.
44
45## Aggregation And Reporting
46
47- GROUP BY on encrypted field: supported only with deterministic.
48- SUM / AVG on an encrypted number: generally not supported — leave
49 aggregatable numerics unencrypted unless compliance requires.
50- Reports with filters on encrypted fields: equality works for
51 deterministic; others fail at runtime.
52
53## Apex Patterns
54
55Encrypted fields read and write transparently in Apex for users with
56"View Encrypted Data" permission. Without that permission, reads return
57masked values (`*********`). Plan for:
58
59- Test users with both modes.
60- Avoid logging encrypted values in `System.debug` even when you have
61 permission — Event Monitoring / debug logs may persist.
62
63## Recommended Workflow
64
651. Enumerate every query / report / list view / filter that touches
66 each candidate field.
672. Classify each query: exact, range, LIKE, aggregate, display.
683. Apply the scheme matrix. Flag fields where encryption blocks a
69 required query.
704. For blocked fields, decide: drop the requirement, skip encryption,
71 or derive a hashed index field (one-way).
725. Request custom indexes on deterministic fields used as filters.
736. Test with both "View Encrypted Data" and masked users.
747. Document the scheme per field in a schema decision log.
75
76## Official Sources Used
77
78- Shield Platform Encryption Overview —
79 https://help.salesforce.com/s/articleView?id=sf.security_pe_overview.htm
80- Deterministic Encryption —
81 https://help.salesforce.com/s/articleView?id=sf.security_pe_deterministic_encryption.htm
82- Encrypted Fields In SOQL —
83 https://help.salesforce.com/s/articleView?id=sf.security_pe_apps_soql.htm
aibot88