SQL-FRONTIER Benchmark Deep-Dive / Search & Retrieval / task_072_Q72_fuzzy_entity_resolution_dedup
← Prev: Customer Search-to-Purcha... ⊞ All 120 Queries Next: Search Term Co-Occurrence... →
✔ PASS Search & Retrieval task_072_Q72_fuzzy_entity_resolution_dedup Scaled: 1M+ Records Ordered: Yes

Fuzzy Customer Entity Resolution across Disparate Data Sources

Candidate Latency (1M Rows)
2891.32 ms
(1.0x faster than golden)
Golden Latency (1M Rows)
2907.34 ms
DuckDB in-memory engine
Underlying Data Volume
5,000 rows
214.8 KiB across 2 tables
Evaluated Model
gemini-3.8-flash-low
Gen Latency: 12.2s
✔ Correctness Verified on 1M+ Records
Candidate SQL output matches ground truth golden query across all multiset rows and numeric tolerances (tol=1e-3). Zero data skew or anti-patterns detected on full dataset scale.

1. Analytical Significance & What It Means

Scenario & Frontier Trap
Business Scenario & Problem

Merging CRM and billing databases requires linking customers with slight name and address variations (e.g. 'Robert Smith, 123 Main St' vs 'Bob Smith, 123 Main Street').

⚡ The Frontier Trap (Why LLMs Fail)

Pairwise fuzzy string matching over millions of records is computationally prohibitive without blocking/canopy clustering.

Prompt / User Request Given to Model
Merging CRM and billing databases requires linking customers with slight name and address variations (e.g. 'Robert Smith, 123 Main St' vs 'Bob Smith, 123 Main Street').

Execute the required analytical SQL calculation in DuckDB.
Expected output columns: crm_id, crm_name, bill_id, billing_name, zip_code, name_similarity.
Results should be ordered as specified.

2. Underlying Tables, Row Counts & Data Volume

2 Tables (5,000 rows • 214.8 KiB)

Production-scale fixtures loaded into the isolated DuckDB sandbox with at least 1,000,000 records per table:

📊 billing_records 2,500 rows 107.4 KiB Data Volume
bill_id (INTEGER) name (VARCHAR) zip_code (VARCHAR)
Data Preview (showing sample rows from 2,500 total rows):
# bill_id name zip_code
1 101 Bob Johnson 94107
2 102 Sara Williams 10001
3 102 Bob Johnson 94107
4 103 Sara Williams 10001
5 104 Bob Johnson 94107
6 105 Sara Williams 10001
7 106 Bob Johnson 94107
8 107 Sara Williams 10001
9 108 Bob Johnson 94107
10 109 Sara Williams 10001
11 110 Bob Johnson 94107
12 111 Sara Williams 10001
13 112 Bob Johnson 94107
14 113 Sara Williams 10001
15 114 Bob Johnson 94107
📊 crm_records 2,500 rows 107.4 KiB Data Volume
crm_id (INTEGER) name (VARCHAR) zip_code (VARCHAR)
Data Preview (showing sample rows from 2,500 total rows):
# crm_id name zip_code
1 1 Robert Johnson 94107
2 2 Sarah Williams 10001
3 2 Robert Johnson 94107
4 3 Sarah Williams 10001
5 4 Robert Johnson 94107
6 5 Sarah Williams 10001
7 6 Robert Johnson 94107
8 7 Sarah Williams 10001
9 8 Robert Johnson 94107
10 9 Sarah Williams 10001
11 10 Robert Johnson 94107
12 11 Sarah Williams 10001
13 12 Robert Johnson 94107
14 13 Sarah Williams 10001
15 14 Robert Johnson 94107

3. Graphical Query Execution Plan & Cost Bottlenecks

Physical Operator Tree (DuckDB 2.0 Profiler)

Visual flowchart of the physical execution pipeline. Operators with high relative costs (≥ 20% of query runtime or heavy latency) are visually highlighted:

Pipeline Latency Distribution (Total Plan Time: 1812.18 ms) Nodes consuming ≥ 20% of query runtime are highlighted with glowing borders
📐 PROJECTION 🔥 4.4% Cost
Latency: 80.42 ms
Rows In/Out: 3,125,000
Data Vol: 190.73 MB
📁 Projections: ['CAST(#0 AS INTEGE...
⚠️ Heavy Execution Bottleneck (4.4% of query runtime)
⏳ ORDER_BY 🔥 24.0% Cost
Latency: 434.02 ms
Rows In/Out: 3,125,000
Data Vol: 154.97 MB
⚠️ Heavy Execution Bottleneck (24.0% of query runtime)
📐 PROJECTION 🔥 4.2% Cost
Latency: 76.85 ms
Rows In/Out: 3,125,000
Data Vol: 154.97 MB
📁 Projections: ['CAST(#0 AS SMALLI...
⚠️ Heavy Execution Bottleneck (4.2% of query runtime)
📐 PROJECTION 🔥 35.1% Cost
Latency: 635.53 ms
Rows In/Out: 3,125,000
Data Vol: 190.73 MB
📁 Projections: ['crm_id', 'crm_nam...
⚠️ Heavy Execution Bottleneck (35.1% of query runtime)
⚡ HASH_JOIN 🔥 32.3% Cost
Latency: 585.12 ms
Rows In/Out: 3,125,000
Data Vol: 166.89 MB
⚠️ Heavy Execution Bottleneck (32.3% of query runtime)
🔍 TABLE_SCAN 0.0%
Latency: 0.18 ms
Rows In/Out: 2,500
Data Vol: 0.09 MB
📁 memory.main.crm_records
🔍 TABLE_SCAN 0.0%
Latency: 0.07 ms
Rows In/Out: 2,500
Data Vol: 0.09 MB
📁 memory.main.billing_records
Operator Cost % Latency Processed Rows Data Volume Details / Table Status
PROJECTION 35.1% 635.53 ms 3,125,000 190.73 MB Projections: ['crm_id', 'crm_name', 'bill_id', ' 🔥 BOTTLENECK
HASH_JOIN 32.3% 585.12 ms 3,125,000 166.89 MB - 🔥 BOTTLENECK
ORDER_BY 24.0% 434.02 ms 3,125,000 154.97 MB - 🔥 BOTTLENECK
PROJECTION 4.4% 80.42 ms 3,125,000 190.73 MB Projections: ['CAST(#0 AS INTEGER)', '__internal 🔥 BOTTLENECK
PROJECTION 4.2% 76.85 ms 3,125,000 154.97 MB Projections: ['CAST(#0 AS SMALLINT)', '__interna 🔥 BOTTLENECK
TABLE_SCAN 0.0% 0.18 ms 2,500 0.09 MB memory.main.crm_records Normal
TABLE_SCAN 0.0% 0.07 ms 2,500 0.09 MB memory.main.billing_records Normal

4. Queries Executed (Candidate vs Golden Ground Truth)

Beautified & Syntax Highlighted
Candidate SQL Executed by gemini-3.8-flash-low (Runtime on 1M Rows: 2891.32 ms) 
-- Entity resolution: Match records with identical zip code and Jaro-Winkler name similarity >= 0.80

SELECT c.crm_id,
       c.name AS crm_name,
       b.bill_id,
       b.name AS billing_name,
       c.zip_code,
       round(jaro_winkler_similarity(c.name, b.name), 4) AS name_similarity
FROM crm_records c
JOIN billing_records b ON c.zip_code = b.zip_code
AND jaro_winkler_similarity(c.name, b.name) >= 0.80
ORDER BY name_similarity DESC;
Golden Reference Ground Truth SQL (Runtime on 1M Rows: 2907.34 ms) 
-- Entity resolution: Match records with identical zip code and Jaro-Winkler name similarity >= 0.80

SELECT c.crm_id,
       c.name AS crm_name,
       b.bill_id,
       b.name AS billing_name,
       c.zip_code,
       round(jaro_winkler_similarity(c.name, b.name), 4) AS name_similarity
FROM crm_records c
JOIN billing_records b ON c.zip_code = b.zip_code
AND jaro_winkler_similarity(c.name, b.name) >= 0.80
ORDER BY name_similarity DESC;
DuckDB Fixture Setup DDL & Data Population 
CREATE TABLE crm_records (crm_id INT, name VARCHAR, zip_code VARCHAR);


CREATE TABLE billing_records (bill_id INT, name VARCHAR, zip_code VARCHAR);


INSERT INTO crm_records
VALUES (1, 'Robert Johnson', '94107'),
       (2, 'Sarah Williams', '10001');


INSERT INTO billing_records
VALUES (101, 'Bob Johnson', '94107'),
       (102, 'Sara Williams', '10001');

5. Query Results & Expected Ground Truth

Tabular Comparison
Actual Candidate Output (3,125,000 rows × 6 cols)
# crm_id crm_name bill_id billing_name zip_code name_similarity
1 2 Sarah Williams 2,599 Sara Williams 10001 0.9857
2 3 Sarah Williams 2,599 Sara Williams 10001 0.9857
3 5 Sarah Williams 2,599 Sara Williams 10001 0.9857
4 7 Sarah Williams 2,599 Sara Williams 10001 0.9857
5 9 Sarah Williams 2,599 Sara Williams 10001 0.9857
6 11 Sarah Williams 2,599 Sara Williams 10001 0.9857
7 13 Sarah Williams 2,599 Sara Williams 10001 0.9857
8 15 Sarah Williams 2,599 Sara Williams 10001 0.9857
9 17 Sarah Williams 2,599 Sara Williams 10001 0.9857
10 19 Sarah Williams 2,599 Sara Williams 10001 0.9857
11 21 Sarah Williams 2,599 Sara Williams 10001 0.9857
12 23 Sarah Williams 2,599 Sara Williams 10001 0.9857
13 25 Sarah Williams 2,599 Sara Williams 10001 0.9857
14 27 Sarah Williams 2,599 Sara Williams 10001 0.9857
15 29 Sarah Williams 2,599 Sara Williams 10001 0.9857
16 31 Sarah Williams 2,599 Sara Williams 10001 0.9857
17 33 Sarah Williams 2,599 Sara Williams 10001 0.9857
18 35 Sarah Williams 2,599 Sara Williams 10001 0.9857
19 37 Sarah Williams 2,599 Sara Williams 10001 0.9857
20 39 Sarah Williams 2,599 Sara Williams 10001 0.9857
21 41 Sarah Williams 2,599 Sara Williams 10001 0.9857
22 43 Sarah Williams 2,599 Sara Williams 10001 0.9857
23 45 Sarah Williams 2,599 Sara Williams 10001 0.9857
24 47 Sarah Williams 2,599 Sara Williams 10001 0.9857
25 49 Sarah Williams 2,599 Sara Williams 10001 0.9857
Showing first 25 of 3,125,000 total rows.
Expected Golden Output (3,125,000 rows × 6 cols)
# crm_id crm_name bill_id billing_name zip_code name_similarity
1 2 Sarah Williams 2,599 Sara Williams 10001 0.9857
2 3 Sarah Williams 2,599 Sara Williams 10001 0.9857
3 5 Sarah Williams 2,599 Sara Williams 10001 0.9857
4 7 Sarah Williams 2,599 Sara Williams 10001 0.9857
5 9 Sarah Williams 2,599 Sara Williams 10001 0.9857
6 11 Sarah Williams 2,599 Sara Williams 10001 0.9857
7 13 Sarah Williams 2,599 Sara Williams 10001 0.9857
8 15 Sarah Williams 2,599 Sara Williams 10001 0.9857
9 17 Sarah Williams 2,599 Sara Williams 10001 0.9857
10 19 Sarah Williams 2,599 Sara Williams 10001 0.9857
11 21 Sarah Williams 2,599 Sara Williams 10001 0.9857
12 23 Sarah Williams 2,599 Sara Williams 10001 0.9857
13 25 Sarah Williams 2,599 Sara Williams 10001 0.9857
14 27 Sarah Williams 2,599 Sara Williams 10001 0.9857
15 29 Sarah Williams 2,599 Sara Williams 10001 0.9857
16 31 Sarah Williams 2,599 Sara Williams 10001 0.9857
17 33 Sarah Williams 2,599 Sara Williams 10001 0.9857
18 35 Sarah Williams 2,599 Sara Williams 10001 0.9857
19 37 Sarah Williams 2,599 Sara Williams 10001 0.9857
20 39 Sarah Williams 2,599 Sara Williams 10001 0.9857
21 41 Sarah Williams 2,599 Sara Williams 10001 0.9857
22 43 Sarah Williams 2,599 Sara Williams 10001 0.9857
23 45 Sarah Williams 2,599 Sara Williams 10001 0.9857
24 47 Sarah Williams 2,599 Sara Williams 10001 0.9857
25 49 Sarah Williams 2,599 Sara Williams 10001 0.9857
Showing first 25 of 3,125,000 total rows.
← Prev: Customer Search-to-Purcha... ⊞ Back to Benchmark Dashboard Next: Search Term Co-Occurrence... →