BM25 and hybrid retrieval
Compare literal term ranking with embedding similarity and combine their ranks.
The practical problem and model
A developer knowledge base needs to answer both exact terminology searches and questions phrased in different language. BM25 uses term frequency, document length, and corpus statistics to rank lexical matches. Embeddings provide a complementary semantic signal.
This tutorial creates five short articles, builds a text index, and compares indexed ranking with the convenience scoring function. Its final query combines lexical and vector ranks using reciprocal rank fusion (RRF).
RRF combines positions rather than raw scores: 1 / (60 + lexical_rank) + 1 / (60 + vector_rank). That avoids treating a BM25 value and a cosine distance as though they shared a numeric scale.
Before you run
Complete the shared setup. This walkthrough uses synthetic data and recreates its tutorial objects. Use a sandbox tenant or schema, and run the steps in order.
Studio: Tutorial: BM25 Text Search + Hybrid Retrieval. Download the complete SQL.
The BM25 steps make no provider calls. The hybrid step embeds text with Gnok's embedding provider, within your organization's monthly embedding allowance. This tutorial makes no AI_* calls, so it doesn't need AI Governance.
Step 1: Create a corpus and inspect its text index
k1 controls term-frequency saturation; b controls document-length normalization. The selected tokenizer determines which words can match.
CREATE CATALOG IF NOT EXISTS tutorial;
CREATE SCHEMA IF NOT EXISTS tutorial.search;
CREATE OR REPLACE TABLE tutorial.search.articles (
article_id BIGINT,
title VARCHAR,
body VARCHAR,
embedding ARRAY<FLOAT>
);
INSERT INTO tutorial.search.articles (article_id, title, body) VALUES
(1, 'Iceberg snapshot isolation', 'Apache Iceberg uses snapshot isolation for concurrent writes via metadata pointer swaps.'),
(2, 'Vector indexes with HNSW', 'HNSW graphs enable approximate nearest neighbor search with logarithmic query complexity.'),
(3, 'Cost-based optimization', 'Query optimizers use cardinality estimates and operator costs to enumerate plans.'),
(4, 'Distributed shuffle exchanges', 'Hash and broadcast shuffles move data between workers to satisfy join and aggregation locality.'),
(5, 'BM25 ranking explained', 'BM25 scores documents by term frequency saturation and inverse document frequency normalization.');
CREATE OR REPLACE TEXT INDEX tutorial.search.idx_articles_body
ON tutorial.search.articles (body)
WITH (k1 = 1.2, b = 0.75, tokenizer = 'ascii_alphanum');
SHOW TEXT INDEXES;
Step 2: Compare lexical scoring paths
The query deliberately uses the literal token shuffles. Do not assume stemming or synonyms from an ascii_alphanum tokenizer. Indexed BM25_RANK and convenience BM25_SCORE need not have identical score scales.
SELECT
article_id,
title,
BM25_RANK('tutorial.search.idx_articles_body', 'shuffles join', body) AS bm25
FROM tutorial.search.articles
ORDER BY bm25 DESC
LIMIT 5;
SELECT article_id, BM25_SCORE('shuffles join', body) AS score
FROM tutorial.search.articles ORDER BY score DESC LIMIT 5;
REFRESH TEXT INDEX tutorial.search.idx_articles_body;
Step 3: Add semantic representations
This part calls Gnok's embedding provider, within your organization's monthly embedding allowance. All article and query embeddings must use the same model and dimension.
UPDATE tutorial.search.articles
SET embedding = EMBED(title || E'\n' || body)
WHERE embedding IS NULL;
CREATE VECTOR INDEX IF NOT EXISTS tutorial.search.idx_articles_embedding
ON tutorial.search.articles (embedding)
USING HNSW
WITH (m = 16, ef_construction = 200, metric = 'cosine');
Step 4: Fuse lexical and semantic ranks
The two CTEs rank the same article identifiers and the join combines their ranks. Larger fused score ranks earlier; smaller cosine distance ranked earlier inside the vector CTE.
WITH bm25_hits AS (
SELECT article_id, title,
BM25_RANK('tutorial.search.idx_articles_body', 'fast nearest neighbor search', body) AS bm25,
ROW_NUMBER() OVER (ORDER BY BM25_RANK('tutorial.search.idx_articles_body', 'fast nearest neighbor search', body) DESC) AS rank_bm25
FROM tutorial.search.articles
),
vec_hits AS (
SELECT article_id,
COSINE_DISTANCE(embedding, EMBED('fast nearest neighbor search')) AS dist,
ROW_NUMBER() OVER (ORDER BY COSINE_DISTANCE(embedding, EMBED('fast nearest neighbor search')) ASC) AS rank_vec
FROM tutorial.search.articles
)
SELECT
b.article_id,
b.title,
-- Reciprocal Rank Fusion with k = 60.
1.0 / (60.0 + b.rank_bm25) + 1.0 / (60.0 + v.rank_vec) AS fused_score
FROM bm25_hits b
JOIN vec_hits v ON b.article_id = v.article_id
ORDER BY fused_score DESC
LIMIT 5;
What to check in the results
For the shuffles join search, article 4 contains both matching terms and should lead the lexical ranking. The final result covers at most the five articles and is ordered by decreasing fused score.
Check each fused score from its two ranks. Ties can have different row orders because the example's window functions do not add an ID tie-breaker. As in the vector tutorial, the recorded semantic query used exact distance scans; index creation is not proof of ANN acceleration.
Adapt it to real data
Choose tokenization for the corpus language and the identifiers users search for. Evaluate lexical retrieval, semantic retrieval, and fusion against the same relevance judgments. Add a stable tie-breaker when reproducible ordering matters.
Refresh lexical statistics as the corpus changes and keep embeddings current. Do not tune a hybrid ranker solely on five hand-written documents.