SQL or vector search for AI agents? Start with the question

Choose the operation, then the retrieval tool

“Which customers paid us in August?” asks for a defined set of records. “What concerns are customers expressing about onboarding?” asks for relevant evidence in language. Both are business questions, but treating them as the same retrieval problem makes evaluation difficult.

A nearest-neighbor result containing ten relevant records does not establish that those are all qualifying records. Conversely, an exact substring match can miss a passage that expresses the same idea with different words. Evaluate the operation against the question’s requirements.

Where does a context store fit?

A context store is a product or architecture label; the name alone does not specify its retrieval operations. For example, Airbyte's Context Store supports structured filtering and aggregation, with semantic search on supported connectors. Evaluate entity lookup, native aggregation, passage retrieval and complete cross-source calculations separately. For account totals, inspect the identity map and every qualifying input. For narrative evidence, inspect the returned passages. The Airbyte comparison provides a concrete account-total evaluation.

A small example you can reproduce

Suppose the question is: How much did currently active customers pay in August 2026? The example below contains three synthetic customers and six synthetic invoices. All amounts are USD; “paid” is the invoice state, and “active” is the customer’s current status. It does not model refunds, taxes or historical status changes.

Run this single query in a local DuckDB SQL session, or download the SQL example. The inline values are a teaching fixture. For real Combined data, discover and use your granted logical relations instead.

WITH customers(customer_id, customer_name, status) AS (
  VALUES ('a', 'Acme', 'active'),
         ('b', 'Birch', 'active'),
         ('c', 'Cedar', 'paused')
), invoices(invoice_id, customer_id, amount_usd, state, paid_on) AS (
  VALUES (1001, 'a', 1200, 'paid', DATE '2026-08-10'),
         (1002, 'a',  800, 'paid', DATE '2026-08-20'),
         (1003, 'b',  400, 'paid', DATE '2026-08-12'),
         (1004, 'b',  700, 'open', NULL),
         (1005, 'c',  900, 'paid', DATE '2026-08-08'),
         (1006, 'a',  300, 'paid', DATE '2026-09-02')
)
SELECT c.customer_name, SUM(i.amount_usd) AS paid_usd
FROM customers c
JOIN invoices i ON c.customer_id = i.customer_id
WHERE c.status = 'active' AND i.state = 'paid'
  AND i.paid_on >= DATE '2026-08-01'
  AND i.paid_on < DATE '2026-09-01'
GROUP BY c.customer_name
ORDER BY paid_usd DESC;

The combined total is $2,400. The query excludes Birch’s open invoice, Cedar’s payment because Cedar is currently paused, and Acme’s September payment. These exclusions are part of the definition, not details for the language model to infer.

Change the question to “customers who were active when they paid” and the schema is insufficient: it has no customer-status history. A reliable agent should ask for that history or disclose the limitation. Correct SQL cannot recover facts that are absent from the data.

Test correctness and coverage separately

Use a small fixture with a known answer before connecting a large production dataset. Include a record outside the time range, an excluded status, a missing value and a second record for one customer. Check the resulting rows as well as the total.

For a cross-source test, use the Salesforce–Stripe fixture and expected rows. Its independent account aggregates expose duplicated joins, partial-payment mistakes and lost accounts without invoice activity. A native aggregate can pass the same contract as SQL; compare its complete input scope and final merge, not just the first matching records. The planner and evaluation worksheet capture the setup and evidence requirements.

For semantic retrieval, build a different evaluation: representative questions, passages judged relevant, cases with no useful evidence, and permission boundaries. Measure whether the required evidence was retrieved before judging the final prose.

For a hybrid agent, retain the SQL definition of a cohort and the identifiers of retrieved passages. A support note can suggest a reason for churn, but it does not establish causation or change the numeric cohort. Keep that distinction visible in the answer.

This example illustrates query semantics. It is not a benchmark comparing model accuracy or a claim that any retrieval technique always wins.

Where Combined fits

Combined exposes permitted structured datasets through read-only SQL, MCP and SDKs. An agent can discover a dataset’s logical relation and fields, inspect freshness, and run a bounded query. That makes the SQL portion of this workflow a natural evaluation target.

Combined’s MCP search_context tool performs bounded literal substring search. It is not vector search or semantic retrieval. If your application also needs semantic similarity, evaluate a separate semantic retrieval component and preserve source permissions across both paths.

Start with the API quickstart or the Claude Code connection guide. Use discovered identifiers and parameterized values. Report source freshness and query truncation when they affect the conclusion.

Sources and further reading

Explore the documentation behind this guide. Product details checked on September 11, 2026.

• Pinecone semantic search concepts
• DuckDB SELECT documentation
• Combined SQL concepts and restrictions
• Combined MCP: query_sql and search_context
• Airbyte Context Store operations