The demo goes well until someone from sales asks the assistant how many open opportunities Acme has. It answers immediately: five. Confident, well-phrased, cited.
Acme has nineteen. The assistant retrieved five chunks that happened to mention Acme, because five is roughly how many chunks a retrieval returns, and then it counted them. There is no bug. Nothing failed. Similarity search returned the most similar things and the model described what it was given.
That is the defining problem with preparing CRM data for Amazon Bedrock, and it is upstream of chunk sizes and embedding models. CRM data is structured. Retrieval-augmented generation is built for unstructured text. Feed a table of opportunities through an embedding pipeline and you get a system that is genuinely good at “what did we discuss with this account” and quietly terrible at “how many”, “how much” and “since when”.
This covers the split that fixes it, what a good document actually looks like, the permissions problem nobody notices until it is a problem, and how to know whether any of it works.
Route the question before you build anything
Your users will ask two kinds of question and they need two different mechanisms.
- Narrative questions. “What were the objections on the Acme renewal?” “Summarise our history with this account.” “What did the customer say about pricing?” These live in notes, emails, call summaries and descriptions. Semantic retrieval is exactly right for them.
- Analytical questions. “How many open deals over fifty thousand?” “What is total pipeline this quarter?” “Which accounts have had no activity in ninety days?” These need aggregation, filtering and joins. Retrieval cannot do them and will not tell you it cannot.
The useful part is that you do not have to build the second half yourself. Bedrock Knowledge Bases supports structured data retrieval: you point it at Amazon Redshift or a Glue Data Catalog, and it generates and executes SQL from natural language against your data in place, with no copying or embedding. The counting question goes to SQL, the narrative question goes to the vector store, and both answers are right for the right reasons.
So the first architectural decision is not which chunking strategy to use. It is: which of your fields are text worth embedding, and which are facts worth querying. Get that wrong and no amount of tuning downstream will save it.
One practical note if you take the structured route. Bedrock’s execution role authenticates to Redshift as an IAM identity, and you grant it access the same way you would any other reader. Point it at curated views rather than base tables, exactly as you would a BI tool:
-- Give the knowledge base the same narrow access you'd give a
-- reporting user. A view is a contract; a schema grant is not.
GRANT USAGE ON SCHEMA analytics
TO "IAMR:AmazonBedrockExecutionRoleForKnowledgeBase";
GRANT SELECT ON analytics.v_opportunities
TO "IAMR:AmazonBedrockExecutionRoleForKnowledgeBase";Named columns and clear table names matter more here than anywhere else, because the model is reading your schema to write the SQL. A column called flag_2 will produce exactly the query quality it deserves.
What a good document looks like
For the narrative half, the instinct is to export records to CSV and let the chunker deal with it. That produces chunks that are half a row of one record and half a row of another, with no context about which is which.
Build documents deliberately instead. Three rules that do most of the work:
One record, one document. An opportunity and its notes become a single file. A chunk from it is then always about one thing.
Denormalise the context in. A note that says “they want a two-year term” is useless in isolation. Embed the account name, the opportunity name, the stage and the owner into the same document, so a chunk carries enough context to be retrieved and to be understood once retrieved.
Write it as prose, not as fields. Embedding models were trained on language. Stage__c: Negotiation embeds worse than “The opportunity is at the Negotiation stage.” This feels silly and it measurably improves retrieval.
On chunking itself: start with the default strategy and change it only when evaluation tells you to. If your documents are one record each and reasonably short, semantic or hierarchical chunking buys you less than people expect. The gains that matter come from document construction and metadata, not from chunk size.
The permissions problem
This is the one that turns a nice project into an incident, and it is easy to miss because nothing about it looks broken.
Salesforce has an elaborate sharing model: role hierarchies, sharing rules, territory management, field-level security. A rep sees their accounts and not everyone else’s. Your vector store has none of that. Once a record is embedded, it is a vector like any other, and anyone who can query the knowledge base can retrieve it.
So a rep asks about a competitor deal they were never on the team for, and the assistant helpfully summarises it. Or someone asks about compensation-adjacent records and gets an answer. No alert fires, because from the system’s perspective retrieval worked perfectly.
Three ways out, in increasing order of effort:
- Only embed what everyone may see. Blunt, easy, and often correct for a first version. Exclude anything sensitive and be explicit that the assistant does not know about it.
- Filter at retrieval time using metadata. Attach owner, team or region to every document, and pass a filter with each query derived from the caller’s identity. This is the practical answer for most teams.
- Separate knowledge bases per audience. Heavier to operate, and the only option that gives you a hard boundary rather than a filter you have to remember to apply.
Whichever you pick, decide it before ingestion, because metadata has to be configured at ingest time. Retrofitting a filter you did not plan for means rebuilding the index.
Metadata is most of the value
Metadata filtering narrows the candidate set before similarity search runs. That improves relevance, reduces tokens in the prompt, and gives you the access control lever above. For S3 sources it lives in a sidecar file named after the object it describes:
// acme-renewal.txt.metadata.json, alongside acme-renewal.txt in S3
{
"metadataAttributes": {
"object_type": "Opportunity",
"owner_id": "0051t00000XYZ",
"account_id": "0011t00000ABC",
"region": "EMEA",
"is_closed": false,
"last_modified_epoch": 1750000000
}
}Include, at minimum: what kind of record it is, who owns it, which account it belongs to, whether it is still open, and when it last changed. Those five turn “search everything” into “search the open opportunities this person owns, changed recently”, which is a different product.
Freshness, and the vectors that outlive the record
An embedding is a copy. When the source record changes, the copy is stale. When the source record is deleted, the copy is not.
The consequences are worse in a CRM than in a document store, because CRM records change constantly and confidently answering with last quarter’s stage is indistinguishable from answering correctly. An assistant that cites a deal which closed-lost in March, or a contact who left the company, is not obviously wrong to the person reading it.
- Sync on a schedule that matches how the data is used. Daily is fine for account summaries and useless for live pipeline questions, which should be going to SQL anyway.
- Handle deletes explicitly. Whatever removes a record from your lake must also remove its document from the source bucket, or the vector survives.
- Put the record’s last-modified timestamp in the metadata and surface it in the answer. “As of three days ago” is a small change that prevents a large class of misunderstanding.
- Reconcile document count against source record count periodically. Divergence means orphans.
What not to embed
CRM free-text fields are where people put things. Notes fields contain personal details, occasionally payment information, opinions about customers that were never meant to leave a private conversation, and years of accumulated noise.
Once embedded, all of it is retrievable by anyone with query access, and a vector is not something you can easily grep for a mistake.
- Run detection over free-text fields before ingestion and redact rather than hope.
- Exclude fields nobody asks questions about. Every extra field is noise competing for retrieval slots.
- Consider excluding records closed long ago; they dilute results and rarely answer anything.
- Add Bedrock Guardrails on the output side as a second layer, not as your only control.
- Keep the whole thing on private networking with VPC endpoints if the data warrants it.
There is a governance question here too, and it is worth raising before someone else does: your customers’ personal data is now in a vector store feeding a language model. Whether your privacy notice covers that is not an engineering decision, but it is an engineering responsibility to ask.
Evaluate it before you show anyone
A demo proves the pipeline runs. It does not tell you whether the answers are right, and this is a system whose failure mode is being wrong fluently.
Write thirty real questions from the people who will use it, with correct answers you have verified by hand. Include the analytical ones specifically, because those are where retrieval fails silently. Then run the set after every meaningful change to chunking, metadata or the document format, and compare.
Use the Retrieve API rather than RetrieveAndGenerate while you are tuning. Seeing which chunks came back tells you whether a bad answer is a retrieval problem or a generation problem, and those have completely different fixes.
Common mistakes
- Embedding structured records and expecting aggregation to work.
- Exporting to CSV and letting the chunker split rows arbitrarily.
- Field-value dumps instead of prose, so embeddings have little language to work with.
- No metadata, so every query searches everything and access control is impossible.
- Assuming Salesforce sharing rules follow the data into the vector store.
- Deciding on filtering after ingestion, then discovering it has to be configured at ingest.
- Syncing changes but never removing documents for deleted records.
- Embedding every field because it was easier than choosing.
- No PII detection over free-text notes.
- Tuning chunk size before fixing document construction.
- Judging the system on a demo instead of a scored question set.
- Granting the execution role a whole schema rather than specific views.
Best practices
- Route analytical questions to structured retrieval and narrative questions to the vector store.
- One record per document, with context denormalised in, written as prose.
- Metadata on every document: type, owner, account, open state, last modified.
- Decide the access model before ingestion, and filter at retrieval time by the caller’s identity.
- Redact free-text fields before embedding, and exclude fields nobody queries.
- Delete documents when source records are deleted, and reconcile the counts.
- Surface record age in answers so staleness is visible rather than implied.
- Expose curated views to the execution role, never whole schemas.
- Meaningful table and column names, because the model reads your schema to write SQL.
- A scored evaluation set, run on every change, including questions you expect it to fail.
FAQ
Why does it get counts wrong?
Because retrieval returns the most similar chunks, not all matching records, and the model counts what it was handed. Counting is a query, not a similarity search. Send those questions to structured retrieval over your warehouse instead.
Should I use the Salesforce connector or export to S3 first?
The connector is quicker to stand up. Going via S3 gives you control over document construction, metadata and redaction, which is where most of the quality lives. Start with the connector to learn what people ask, then move to S3 once you know what the documents should look like.
Which chunking strategy is best?
Usually the default, honestly. If each document is one record, chunking has less work to do. Change it in response to evaluation results rather than in advance, because chunking is the most over-discussed and least decisive variable in a CRM RAG setup.
How do I stop people seeing records they shouldn’t?
Metadata filters applied per query based on the caller’s identity, separate knowledge bases per audience, or simply not embedding sensitive records. There is no automatic inheritance of CRM sharing rules, and assuming otherwise is the most consequential mistake in this post.
How often should I re-sync?
Match the questions. Account histories and notes tolerate a daily sync comfortably. Anything needing current numbers should not be coming from embeddings at all. Whatever the interval, make sure deletions propagate.
Do I need a vector database at all?
Only for the narrative half. If every question your users ask is analytical, structured retrieval over Redshift answers all of them with no embedding pipeline, no vector store and no staleness. Plenty of CRM assistants would be better products if they had noticed that.
The one thing to remember
The model will answer either way. It will answer the narrative question well and the counting question badly, in the same tone, with the same citations, and nothing in the response distinguishes them.
So the preparation work is mostly triage: decide which questions are retrieval and which are queries, build documents that carry their own context, attach metadata before you ingest rather than after, and test with real questions whose answers you already know. Chunk size is the last thing to worry about and the first thing everybody argues about.
Building one of these?
The pipeline is usually the easy part; the decisions before it are where these projects succeed or quietly disappoint. Work I take on:
- Designing the split between semantic retrieval and structured querying, so counting questions get correct answers.
- Building the document preparation pipeline from CRM data: record-level documents, denormalised context, metadata, redaction.
- Access control design for knowledge bases, including per-caller metadata filtering and audience separation.
- Freshness and deletion handling so the index does not diverge from the source.
- Curated Redshift views and IAM grants for structured data retrieval.
- Evaluation harnesses with scored question sets, run on every change rather than once before launch.
Send me ten questions your users would actually ask, and I will tell you which of them retrieval can answer.
