Unified Lakehouse Retrieval: One SQL for Hybrid Keyword, Filter, and Vector Search

Architecture Narrative Over Technical Novelty

RRF, In-Filter, and Partial Update are individually not new. The value lies in placing them inside the same execution framework: one SQL expression, one optimizer for cost estimation, one cache and transaction layer, and one dataset without duplication. This shifts retrieval from multi-system assembly to a built-in query-engine capability.

Three Key Mechanisms

1. RRF: Compare Ranks, Not Scores

BM25 scores are unbounded floats; cosine similarity sits in 0–1 or 0–2. Adding them directly lets the larger range dominate. RRF (Reciprocal Rank Fusion) fuses by rank position only: score = Σ 1 / (k + rank_i) (k typically 60) Benefits: no score normalization, candidates high in both lists rise naturally, single-list hits aren't discarded early, and almost zero tuning parameters. Industry consensus treats RRF as the production baseline for hybrid search; weighted fusion is only added when offline evaluation shows a clear gap.

2. In-Filter: Push Scalar Predicates Into Vector Search

Traditional Pitfall

Vector search (e.g., HNSW) typically recalls first, then filters in the application layer. The graph index cannot see filter predicates during traversal, so high-selectivity filters (e.g., category='sneaker' AND in_stock=true AND price 50–200, covering 2% of data) leave too few results after filtering. Increasing K raises latency linearly with unstable recall.

In-Filter Approach

Apply scalar predicates (Bitmap/partition indexes) to define a candidate set.

Run ANN only inside that candidate set.

Results naturally satisfy the required K.

The Lake Optimizer automatically chooses Pre-Filter (high selectivity) or Post-Filter (low selectivity), which is more robust than hard-coded application logic.

Caveat: Pre-Filter requires Bitmap or partition indexes on the filtered columns; otherwise it degrades to a full scan slower than Post-Filter.

3. Partial Update: Close the Annotation Loop Without Leaving the Lake

Traditional flow: export hits → annotate → write back to business DB → sync to training jobs. Data is copied 3–4 times with version skew.

Partial Update updates only the target column (e.g., scene_tag), leaving other columns untouched. Write amplification is minimal; lake table transactions guarantee consistency; downstream Spark training reads the same table.

UPDATE ai_dataset.scene_data
SET scene_tag = 'corner_case_rain_urban_night'
WHERE id IN (
  SELECT id FROM ai_dataset.scene_data
  WHERE description MATCH 'construction zone'
    AND weather = 'heavy rain'
    AND road_type = 'urban road'
  ORDER BY approx_cosine_similarity(embedding, @query_vector) DESC
  LIMIT 100
);

Complete Hybrid Search Execution Chain (E-commerce Example)

User query: "commuter waterproof backpack, 300–500 CNY, in stock". The entire process runs in one engine on one lake table (Paimon/Iceberg/Lance) without application-level result stitching.

Vector index options:

HNSW : high recall, low latency, high memory – production default.

IVFPQ : clustering + quantization, suits massive scale with memory constraints.

DiskANN : index on disk, cost-sensitive scenarios.

Choose based on data volume, memory budget, and latency SLA; don't default to HNSW blindly.

Five Transferable Engineering Principles

Don't push filters downstream – apply scalar predicates at recall stage; high-selectivity post-filtering collapses.

Fuse by rank, not score – RRF is the production baseline; score normalization is brittle.

Layer indexes – high-frequency filter columns → Bitmap/partition; full-text → GIN + BM25; vector → HNSW/IVFPQ/DiskANN per scale.

Write back via column-level updates – annotations, scores, feedback are lightweight writes; Partial Update avoids full-row rewrites and redundant copies.

Lake table as single source of truth – structured fields, vectors, full-text, blob references in one Paimon/Iceberg table; ingestion, retrieval, annotation, training share the same data. AI functions (embedding, tagging, semantic filtering) become part of the SQL execution chain, not external pipelines.

Points to View Critically

Vendor-reported numbers (30%+ recall lift, 60% cost reduction, sub-second latency) are self-reported from the managed EMR Serverless StarRocks environment; validate with your own benchmarks.

Version sensitivity : SQL syntax ( MATCH, USING VECTOR/GIN/BITMAP indexes, Partial Update) verified mainly on EMR Serverless StarRocks 3.5+; community edition may lack parity.

Does not replace specialized systems :

Mature ES clusters for log full-text → keep.

Massive-scale, millisecond pure vector serving (e.g., homepage recommendation) → Milvus etc. remain better.

The unified engine targets multimodal fused queries + data-stays-in-lake .

Closing Takeaway

Multimodal retrieval evolution isn't "add another vector database"; it's letting scalar, full-text, and vector be recalled and fused synchronously in one engine on one lake dataset – filters don't move downstream, data doesn't leave the lake, ranking is explainable. Remember three mechanisms:

RRF : compare ranks, not scores.

In-Filter : narrow candidates first, then find similar.

Partial Update : tag columns, don't rewrite rows.