Hybrid Search & BM25 + Dense Retrieval for Enterprise Data Catalogs

The Problem with Naive Vector Search in Data Engineering

Vector search (e.g., OpenAI text-embedding models + cosine similarity) has become the default architecture for RAG (Retrieval-Augmented Generation). However, in enterprise data engineering environments, pure vector search routinely fails.

Where Dense Embeddings Break:

1. Exact Identifier Mismatch: Searching for fct_billing_v2_202409 frequently matches fct_billing_v1_202308 because the semantic embeddings are almost identical, despite referencing completely different tables.
2. Error Code Precision: An error string like ORA-00054: resource busy and acquire with NOWAIT specified gets semantically blurred into general database concurrency texts rather than finding the exact resolution ticket.
3. Out-of-Vocabulary (OOV) Acronyms: Enterprise-specific acronyms (COD, MDR, PUDO) carry no semantic weights in generic pre-trained embedding spaces.

The Solution: Hybrid Retrieval via Reciprocal Rank Fusion (RRF)

Drawing from research in UIUC CS 410 (Text Information Systems), this project implements a production-grade Hybrid Retrieval engine uniting sparse lexical frequency (Okapi BM25) with dense semantic similarity, re-ranked through Reciprocal Rank Fusion (RRF):

The Mathematical Foundation: BM25 vs. Dense Vectors

Okapi BM25 Formula

Given a query $Q$ containing keywords $q_1, \dots, q_n$, the BM25 score of a document $D$ is:

Score(D, Q) = Σ [ IDF(q_i) · ( f(q_i, D) · (k1 + 1) ) / ( f(q_i, D) + k1 · (1 - b + b · (|D| / avgdl)) ) ]

Where:

• k1 controls term frequency saturation.
• b controls document length normalization penalty.

Reciprocal Rank Fusion (RRF)

To combine non-comparable scores (BM25 unbounded log-odds vs. cosine [-1, 1]), we rank items ordinally and apply RRF:

RRF(d) = Σ [ 1 / (60 + rank_m(d)) ]

Where 60 is the standard smoothing constant, and rank_m(d) is the document’s rank in retriever m.

Python Implementation Snippet

from typing import List, Dict

def reciprocal_rank_fusion(
    sparse_ranks: List[str], 
    dense_ranks: List[str], 
    k: int = 60
) -> Dict[str, float]:
    """Combines sparse and dense rank lists using Reciprocal Rank Fusion."""
    scores: Dict[str, float] = {}

    for rank, doc_id in enumerate(sparse_ranks, start=1):
        scores[doc_id] = scores.get(doc_id, 0.0) + (1.0 / (k + rank))

    for rank, doc_id in enumerate(dense_ranks, start=1):
        scores[doc_id] = scores.get(doc_id, 0.0) + (1.0 / (k + rank))

    # Sort descending by merged RRF score
    return dict(sorted(scores.items(), key=lambda item: item[1], reverse=True))

Benchmark Comparison on Technical Schemas

On a test dataset of 2,500 enterprise BigQuery table definitions, DDL scripts, and Airflow DAG logs:

Academic Context

Developed as an applied extension of coursework in UIUC MCS CS 410 (Text Information Systems) under the guidance of information retrieval literature.