Principle:Ucbepic Docetl LLM Powered Data Joining
| Knowledge Sources | |
|---|---|
| Domains | LLM_Data_Processing, Data_Integration |
| Last Updated | 2026-02-08 00:00 GMT |
Overview
LLM-based semantic joining combines embedding-driven blocking with LLM pairwise comparison to join two datasets on semantic similarity rather than exact key matching, enabling fuzzy joins that traditional equijoins cannot handle.
Theoretical Basis
Traditional database joins match records based on exact equality of key columns. However, many real-world integration tasks require matching records that refer to the same entity but use different representations -- different name spellings, paraphrased descriptions, or varying levels of detail. These semantic joins require understanding meaning rather than comparing strings character by character.
DocETL's equijoin operation addresses this with a three-phase approach. First, code-based blocking conditions (optional Python expressions evaluated on left/right pairs) filter out obviously non-matching pairs using rule-based logic. Second, embedding-based blocking computes dense vector embeddings for both datasets and retains only pairs whose cosine similarity exceeds a threshold, dramatically reducing the candidate set. If no blocking configuration is provided, the system automatically invokes the RuntimeBlockingOptimizer to sample pairs, perform LLM comparisons on the sample, and find an optimal threshold targeting 95% recall. Third, each surviving candidate pair is sent to an LLM comparison that evaluates a user-defined Jinja2 prompt template with the left and right records, returning a boolean is_match decision.
The operation supports configurable match limits per side (e.g., one-to-one or one-to-many joins), comparison limits for cost control, and stratified sampling of excess pairs based on document length to maintain representativeness when sampling is required. The final output merges matched left and right records, automatically disambiguating shared column names with _left and _right suffixes.
Key Design Decisions
| Decision | Choice | Rationale |
|---|---|---|
| Blocking pipeline | Code conditions then embedding threshold then LLM comparison | Progressive filtering from cheapest to most expensive; each stage reduces the candidate set for the next |
| Auto-threshold optimization | RuntimeBlockingOptimizer with recall-targeted sampling when no blocking config is provided | Removes the need for manual threshold tuning; provides statistical recall guarantees while keeping calibration cost low |
| Match limits | Configurable per-side cardinality limits (left_limit, right_limit) | Supports one-to-one, one-to-many, and many-to-many join semantics depending on the use case |