# Build it: RAG in SQL on Databricks with vector_search and ai_query, and a retrieval check On Databricks, retrieval-augmented generation fits in 1 SQL query. vector_search(index => ..., query_text => question, num_results => 3, query_type => 'HYBRID') returns the closest chunks with all their columns; a LATERAL join runs it for every question in a table. Join the chunks' text into the prompt, tell ai_query to answer only from it, and return the chunk ids as the sources. Before trusting the answers, check retrieval: for a set of test questions, count how often the right article is in the top 3. - Page: https://tecyfy.com/data-engineering/course/data-for-ai/build-it - Course: Data engineering course (https://tecyfy.com/data-engineering/course), Module 13: Data for AI, Build it: answer a question from the help articles - Parts of this module: Chunk documents for retrieval (https://tecyfy.com/data-engineering/course/data-for-ai); A vector index in sync with its table (https://tecyfy.com/data-engineering/course/data-for-ai/vector-index); AI Functions, a model called from SQL (https://tecyfy.com/data-engineering/course/data-for-ai/ai-functions); Build it: answer a question from the help articles (https://tecyfy.com/data-engineering/course/data-for-ai/build-it) At the end of this part you can: - Search an AI Search index from SQL with vector_search, for 1 question or a table of them - Answer a question from retrieved chunks with ai_query, with the sources the answer came from - Measure retrieval on a set of test questions, and keep the index current from a job [Part 3](/data-engineering/course/data-for-ai/ai-functions) called a model from SQL on a table. This part joins the module's pieces into the assistant the shop asked for: a customer's question in, an answer from the help articles out, with the articles it came from. It needs the Databricks side of [part 2](/data-engineering/course/data-for-ai/vector-index): the table `workspace.shop.help_chunks` and its index `workspace.shop.help_chunks_index`, synced. To rebuild from nothing, run part 1's 2 scripts in the workspace, with the help files in a volume and the table named `workspace.shop.help_chunks`, then part 2's notebook cells. The queries run in a SQL editor on a serverless SQL warehouse. ## Build it: find the chunks `vector_search` searches an index from SQL. Every argument is passed by name: ```sql SELECT chunk_id, heading FROM vector_search( index => 'workspace.shop.help_chunks_index', query_text => 'When do I get my money back?', num_results => 3, query_type => 'HYBRID' ) ``` `query_text` is embedded with the index's own model, so the question and the chunks are compared in the same space. `query_type => 'HYBRID'` combines that similarity search with a keyword search, which catches exact words a meaning search can miss, such as an order number or a product code; Databricks recommends it as the starting point for most uses. The function returns all the columns the index synced, by default every column of the table, so `article`, `title`, `heading` and `text` come back with each `chunk_id`. For the returns question, the chunk to hope for is `returns-2`, "How long a refund takes". Part 1 put "Returns and refunds" into its text for exactly this kind of question, which never says "refund". ## Answer from them The answer is 1 more step: hand the chunks' text to a model with the question, and tell it to use nothing else. The test questions go in a table, so the same query serves 1 question or a thousand: ```sql CREATE OR REPLACE TABLE workspace.shop.help_questions (question_id INT, question STRING, expected_article STRING); INSERT INTO workspace.shop.help_questions VALUES (1, 'When do I get my money back?', 'returns'), (2, 'How much is delivery on an order of 25?', 'delivery'), (3, 'My parcel never came', 'delivery'), (4, 'Can I use 2 ways to pay for 1 order?', 'payments'), (5, 'Can I send back opened food?', 'returns'), (6, 'Do gift cards expire?', NULL); ``` Question 6 has no expected article on purpose: part 2 removed the section that answered it. Then the answer query: ```sql WITH retrieved AS ( SELECT q.question_id, q.question, array_join(collect_list(s.text), '\n\n') AS context, collect_list(s.chunk_id) AS sources FROM workspace.shop.help_questions AS q, LATERAL ( SELECT * FROM vector_search( index => 'workspace.shop.help_chunks_index', query_text => q.question, num_results => 3, query_type => 'HYBRID') ) AS s GROUP BY q.question_id, q.question ) SELECT question_id, question, ai_query( 'system.ai.meta-llama-3-3-70b-instruct', 'You answer customer questions for an online shop, using only the help articles below. ' || 'If they do not contain the answer, say so and suggest contacting support. ' || 'Answer in at most 3 sentences.\n\nHelp articles:\n' || context || '\n\nQuestion: ' || question, failOnError => false ).response AS answer, sources FROM retrieved ORDER BY question_id ``` - **`LATERAL`** runs the search once for each question, with that row's `question` as `query_text`, as Databricks' own example of many searches does. - **`context`** joins the 3 chunks' text into 1 block; each chunk starts with its title and heading, from part 1, so the model sees what each passage is about. `collect_list` does not promise an order, which is fine here, because the prompt treats the 3 passages alike. - **The prompt** limits the model to the passages and says what to do when they do not hold the answer. Without that line, a model asked about gift card expiry may answer from what shops usually do, or from an old version of the article it never saw. - **`sources`** are the chunk ids the search returned, from the index, not from the model, so they cannot be made up. Show them under the answer, with the headings, so a customer or an agent can read the article itself. Read the answers to questions 2 and 6. Question 2 needs the rule that standard delivery is free over 40.00 and costs 4.50 below it, after part 2's change; question 6 should say the articles do not answer it. ## Build it: measure the retrieval The model can only answer from what the search hands it. So check the search on its own, before judging any answer: for each question with an expected article, is that article in the top 3? ```sql WITH hits AS ( SELECT q.question_id, q.question, q.expected_article, array_contains(collect_list(s.article), q.expected_article) AS found, collect_list(s.chunk_id) AS returned FROM workspace.shop.help_questions AS q, LATERAL ( SELECT * FROM vector_search( index => 'workspace.shop.help_chunks_index', query_text => q.question, num_results => 3, query_type => 'HYBRID') ) AS s WHERE q.expected_article IS NOT NULL GROUP BY q.question_id, q.question, q.expected_article ) SELECT question_id, question, expected_article, found, returned, round(avg(CAST(found AS INT)) OVER (), 2) AS share_found FROM hits ORDER BY question_id ``` `share_found` is the share of questions whose article came back, the number to watch; the rows with `found` false are the ones to read. The [Retrieval Evaluation](https://tecyfy.com/concepts/retrieval-evaluation) concept covers the measures beyond this one, such as where in the list the right chunk ranks. 5 questions are a start, not a measure. A real set holds the questions customers actually ask, from the contact form of part 3, a few dozen or more, each with the article that answers it. Run this query after every change to the chunking, the embedding model or the search type, and keep the change only if `share_found` does not fall. A miss is usually fixed in the chunks: a heading without the words customers use, or 2 answers in 1 section. ## Keep it current The index is only as current as its last sync. In the bundle from [module 11](/data-engineering/course/testing-and-ci-cd/bundles), a second job reloads the chunks and then syncs the index, every night: ```yaml help_index: name: help_index schedule: quartz_cron_expression: "0 30 2 * * ?" timezone_id: UTC max_concurrent_runs: 1 tasks: - task_key: load_help spark_python_task: python_file: src/chunk_help.py environment_key: default - task_key: sync_index depends_on: [{ task_key: load_help }] spark_python_task: python_file: src/sync_index.py environment_key: default environments: - environment_key: default spec: environment_version: "2" dependencies: - databricks-ai-search ``` It sits under `resources.jobs` beside `shop_daily`, and the `prod` target needs a matching entry for `help_index`: its schedule again, with `pause_status: UNPAUSED`, as module 11 does for `shop_daily`. `src/chunk_help.py` is part 2's script with module 10's changes for Databricks: the help files read from a volume, the table named `workspace.shop.help_chunks`, and `SparkSession.builder.getOrCreate()` for the session. `src/sync_index.py` is part 2's 1 line: ```python from databricks.ai_search.client import AISearchClient AISearchClient().get_index(index_name="workspace.shop.help_chunks_index").sync() ``` The MERGE changes only the chunks whose text changed, so on most nights the sync has nothing to embed. The endpoint is billed from the moment an index exists on it, whether or not anyone searches. On a paid workspace, delete the index when you finish the module; the endpoint stops charging 24 hours after its last index is deleted. ## What you built - A table of help chunks, 1 per section, each with a stable id and the title and heading in its text. - A load that changes only the chunks that changed, and an AI Search index that re-embeds only those. - Customer messages routed and summarised in SQL, each processed and paid for once. - An answer to a customer's question from the help articles, with its sources, and a retrieval check to measure every change against. The [AI engineering course](/ai-engineering/rag) builds the same assistant from the application side, in Python, with its prompts and evaluations. Module 14 puts the whole course together: the shop's pipeline from raw files to a dashboard and an AI answer, end to end. - Course: [AI engineering course, Module 5: RAG that answers correctly](https://tecyfy.com/ai-engineering/rag). Retrieval-augmented generation (RAG) answers a question in 2 steps: a search finds the passages in your documents most likely to hold the answer, and a model… ## Check yourself 1. A customer asks "Do gift cards expire?" after part 2 removed the section that answered it. vector_search still returns the gift cards chunk about using a card. What should the assistant say? A. That gift cards last 2 years, as the old article said B. That gift cards never expire, because no chunk mentions an expiry C. That the help articles do not say, and the customer can contact support D. Nothing, because vector_search found no rows 2. The retrieval check finds the expected article in the top 3 for 4 of 5 questions. The 5th, "my parcel never came", returns the delivery times section instead of the missing parcel one. What is the first thing to try? A. A larger model in ai_query B. Look at the chunk that should have matched, and give it the words customers use, in its heading or text C. Raise num_results to 50 D. Turn off hybrid search ### Answers 1. C. The search always returns its closest chunks, even when none of them holds the answer. The prompt tells the model to answer only from those chunks and to say when they do not contain the answer, which is what keeps it from repeating a retired policy or guessing. 2. B. A retrieval miss is fixed on the retrieval side. The model in ai_query only sees what the search returns, so a larger one cannot use a chunk it was never given. Reading the chunk that should have matched usually shows the gap, here a heading that says "has not arrived" where the customer says "never came". ## Questions ### How do I build RAG with SQL on Databricks? Put your document chunks in a Delta table, create an AI Search Delta Sync index on it, then in SQL call vector_search with the question as query_text to get the closest chunks, join their text into a prompt, and call ai_query with a hosted model, telling it to answer only from that text. A LATERAL join runs the search for every question in a table. Neither function is available on a classic SQL warehouse. ### How do I evaluate retrieval for RAG? Write a set of test questions with the document that holds each answer, run the search for each, and count how often that document is among the top results, such as the top 3. Read every miss, because a miss is usually a chunk without the customer's words or a question the documents do not answer. Measure again after every change to the chunking, the embedding model or the search type. ## Sources - [vector_search function](https://docs.databricks.com/aws/en/sql/language-manual/functions/vector_search) - [Query an AI Search index](https://docs.databricks.com/aws/en/vector-search/query-vector-search) - [Create AI Search endpoints and indexes](https://docs.databricks.com/aws/en/vector-search/create-vector-search) - [ai_query function](https://docs.databricks.com/aws/en/sql/language-manual/functions/ai_query) - [AI Search cost management guide](https://docs.databricks.com/aws/en/vector-search/vector-search-cost-management)