✔ PASS Vector & Embeddings task_049_Q49_prototype_intent_classification Scaled: 1M+ Records Ordered: Yes

Zero-Shot Query Intent Classification via Prototype Vector Distance

Candidate Latency (1M Rows)

834.05 ms

(1.0x faster than golden)

Golden Latency (1M Rows)

834.20 ms

DuckDB in-memory engine

Underlying Data Volume

1,000,002 rows

30.5 MiB across 2 tables

Evaluated Model

gemini-3.8-flash-low

Gen Latency: 18.0s

✔ 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

Instead of training a BERT classifier, user query vectors are compared against pre-computed 'intent prototype vectors' (e.g. Navigational, Transactional, Informational). The closest prototype labels the query intent.

⚡ The Frontier Trap (Why LLMs Fail)

Maintaining external classifier microservices adds 50ms latency to query planning.

Prompt / User Request Given to Model

Instead of training a BERT classifier, user query vectors are compared against pre-computed 'intent prototype vectors' (e.g. Navigational, Transactional, Informational). The closest prototype labels the query intent.

Execute the required analytical SQL calculation in DuckDB.
Expected output columns: query_id, query_text, predicted_intent, confidence.
Results should be ordered as specified.

2. Underlying Tables, Row Counts & Data Volume

2 Tables (1,000,002 rows • 30.5 MiB)

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

📊 inbound_queries 1,000,000 rows 30.5 MiB Data Volume

query_id (INTEGER) query_text (VARCHAR) query_vector (FLOAT[2])

Data Preview (showing sample rows from 1,000,000 total rows):

#	query_id	query_text	query_vector
1	1	where to purchase macbook pro	[0.88 0.15]
2	2	how does duckdb storage work	[0.15 0.85]
3	2	where to purchase macbook pro	[0.8839 0.0919]
4	3	how does duckdb storage work	[0.8503 0.0883]
5	4	where to purchase macbook pro	[0.9261 0.1521]
6	5	how does duckdb storage work	[0.7999 0.1105]
7	6	where to purchase macbook pro	[0.8479 0.0584]
8	7	how does duckdb storage work	[0.8002 0.1175]
9	8	where to purchase macbook pro	[0.8532 0.0713]
10	9	how does duckdb storage work	[0.8655 0.1086]
11	10	where to purchase macbook pro	[0.9489 0.117 ]
12	11	how does duckdb storage work	[0.8562 0.0438]
13	12	where to purchase macbook pro	[0.8181 0.0974]
14	13	how does duckdb storage work	[0.8666 0.0953]
15	14	where to purchase macbook pro	[0.8238 0.0599]

📊 intent_prototypes 2 rows 56 B Data Volume

intent_name (VARCHAR) prototype_vector (FLOAT[2])

Data Preview (showing sample rows from 2 total rows):

#	intent_name	prototype_vector
1	TRANSACTIONAL_BUY	[0.9 0.1]
2	INFORMATIONAL_FAQ	[0.1 0.9]

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: 2270.22 ms) Nodes consuming ≥ 20% of query runtime are highlighted with glowing borders

⏳ ORDER_BY 🔥 5.3% Cost

Latency: 120.16 ms

Rows In/Out: 999,999

Data Vol: 38.15 MB

📐 PROJECTION 1.3%

Latency: 29.02 ms

Rows In/Out: 999,999

Data Vol: 96.15 MB

📁 Projections: ['query_id', 'query...

📐 PROJECTION 0.9%

Latency: 19.69 ms

Rows In/Out: 999,999

Data Vol: 96.15 MB

📁 Projections: ['query_id', 'query...

📐 PROJECTION 0.2%

Latency: 3.66 ms

Rows In/Out: 999,999

Data Vol: 107.59 MB

📁 Projections: ['#0', '#[1.1]', '#...

📊 HASH_GROUP_BY 🔥 89.9% Cost

Latency: 2040.84 ms

Rows In/Out: 999,999

Data Vol: 107.59 MB

📐 PROJECTION 1.7%

Latency: 38.49 ms

Rows In/Out: 2,000,000

Data Vol: 110.64 MB

📁 Projections: ['query_id', 'struc...

⚙️ CROSS_PRODUCT 0.2%

Latency: 4.58 ms

Rows In/Out: 2,000,000

Data Vol: 103.01 MB

🔍 TABLE_SCAN 0.6%

Latency: 13.76 ms

Rows In/Out: 1,000,000

Data Vol: 26.70 MB

📁 memory.main.inbound_queries

🔍 TABLE_SCAN 0.0%

Latency: 0.02 ms

Rows In/Out: 2

Data Vol: 48 B

📁 memory.main.intent_prototypes

Operator	Cost %	Latency	Processed Rows	Data Volume	Details / Table	Status
HASH_GROUP_BY	89.9%	2040.84 ms	999,999	107.59 MB	-	🔥 BOTTLENECK
ORDER_BY	5.3%	120.16 ms	999,999	38.15 MB	-	🔥 BOTTLENECK
PROJECTION	1.7%	38.49 ms	2,000,000	110.64 MB	Projections: ['query_id', 'struct_pack(#[1.1], #	Normal
PROJECTION	1.3%	29.02 ms	999,999	96.15 MB	Projections: ['query_id', 'query_text', 'predict	Normal
PROJECTION	0.9%	19.69 ms	999,999	96.15 MB	Projections: ['query_id', 'query_text', 'intent_	Normal
TABLE_SCAN	0.6%	13.76 ms	1,000,000	26.70 MB	memory.main.inbound_queries	Normal
CROSS_PRODUCT	0.2%	4.58 ms	2,000,000	103.01 MB	-	Normal
PROJECTION	0.2%	3.66 ms	999,999	107.59 MB	Projections: ['#0', '#[1.1]', '#[1.2]', '#[2.0]'	Normal
TABLE_SCAN	0.0%	0.02 ms	2	0.00 MB	memory.main.intent_prototypes	Normal

4. Queries Executed (Candidate vs Golden Ground Truth)

Beautified & Syntax Highlighted

WITH ranked_intents AS
  (SELECT q.query_id,
          q.query_text,
          p.intent_name,
          array_cosine_similarity(q.query_vector, p.prototype_vector) AS intent_similarity,
          row_number() OVER (PARTITION BY q.query_id
                             ORDER BY array_cosine_similarity(q.query_vector, p.prototype_vector) DESC) AS rank_match
   FROM inbound_queries q
   CROSS JOIN intent_prototypes p)
SELECT query_id,
       query_text,
       intent_name AS predicted_intent,
       round(intent_similarity, 4) AS confidence
FROM ranked_intents
WHERE rank_match = 1
ORDER BY query_id;

-- Classify each query to its closest intent prototype
 WITH ranked_intents AS
  (SELECT q.query_id,
          q.query_text,
          p.intent_name,
          array_cosine_similarity(q.query_vector, p.prototype_vector) AS intent_similarity,
          row_number() OVER (PARTITION BY q.query_id
                             ORDER BY array_cosine_similarity(q.query_vector, p.prototype_vector) DESC) AS rank_match
   FROM inbound_queries q
   CROSS JOIN intent_prototypes p)
SELECT query_id,
       query_text,
       intent_name AS predicted_intent,
       round(intent_similarity, 4) AS confidence
FROM ranked_intents
WHERE rank_match = 1
ORDER BY query_id;

CREATE TABLE intent_prototypes (intent_name VARCHAR, prototype_vector FLOAT[2]);


CREATE TABLE inbound_queries (query_id INT, query_text VARCHAR, query_vector FLOAT[2]);


INSERT INTO intent_prototypes
VALUES ('TRANSACTIONAL_BUY', [0.9, 0.1]::FLOAT[2]),
       ('INFORMATIONAL_FAQ', [0.1, 0.9]::FLOAT[2]);


INSERT INTO inbound_queries
VALUES (1, 'where to purchase macbook pro', [0.88, 0.15]::FLOAT[2]),
       (2, 'how does duckdb storage work', [0.15, 0.85]::FLOAT[2]);

5. Query Results & Expected Ground Truth

Tabular Comparison

Actual Candidate Output (999,999 rows × 4 cols)

#	query_id	query_text	predicted_intent	confidence
1	1	where to purchase macbook pro	TRANSACTIONAL_BUY	0.9983
2	2	where to purchase macbook pro	TRANSACTIONAL_BUY	1
3	3	how does duckdb storage work	TRANSACTIONAL_BUY	1
4	4	where to purchase macbook pro	TRANSACTIONAL_BUY	0.9986
5	5	how does duckdb storage work	TRANSACTIONAL_BUY	0.9996
6	6	where to purchase macbook pro	TRANSACTIONAL_BUY	0.9991
7	7	how does duckdb storage work	TRANSACTIONAL_BUY	0.9994
8	8	where to purchase macbook pro	TRANSACTIONAL_BUY	0.9996
9	9	how does duckdb storage work	TRANSACTIONAL_BUY	0.9999
10	10	where to purchase macbook pro	TRANSACTIONAL_BUY	0.9999
11	11	how does duckdb storage work	TRANSACTIONAL_BUY	0.9982
12	12	where to purchase macbook pro	TRANSACTIONAL_BUY	1
13	13	how does duckdb storage work	TRANSACTIONAL_BUY	1
14	14	where to purchase macbook pro	TRANSACTIONAL_BUY	0.9993
15	15	how does duckdb storage work	TRANSACTIONAL_BUY	0.9999
16	16	where to purchase macbook pro	TRANSACTIONAL_BUY	0.9998
17	17	how does duckdb storage work	TRANSACTIONAL_BUY	1
18	18	where to purchase macbook pro	TRANSACTIONAL_BUY	0.9997
19	19	how does duckdb storage work	TRANSACTIONAL_BUY	0.9993
20	20	where to purchase macbook pro	TRANSACTIONAL_BUY	0.9983
21	21	how does duckdb storage work	TRANSACTIONAL_BUY	0.9997
22	22	where to purchase macbook pro	TRANSACTIONAL_BUY	0.9971
23	23	how does duckdb storage work	TRANSACTIONAL_BUY	0.9993
24	24	where to purchase macbook pro	TRANSACTIONAL_BUY	1
25	25	how does duckdb storage work	TRANSACTIONAL_BUY	0.9994

Showing first 25 of 999,999 total rows.

Expected Golden Output (999,999 rows × 4 cols)

#	query_id	query_text	predicted_intent	confidence
1	1	where to purchase macbook pro	TRANSACTIONAL_BUY	0.9983
2	2	where to purchase macbook pro	TRANSACTIONAL_BUY	1
3	3	how does duckdb storage work	TRANSACTIONAL_BUY	1
4	4	where to purchase macbook pro	TRANSACTIONAL_BUY	0.9986
5	5	how does duckdb storage work	TRANSACTIONAL_BUY	0.9996
6	6	where to purchase macbook pro	TRANSACTIONAL_BUY	0.9991
7	7	how does duckdb storage work	TRANSACTIONAL_BUY	0.9994
8	8	where to purchase macbook pro	TRANSACTIONAL_BUY	0.9996
9	9	how does duckdb storage work	TRANSACTIONAL_BUY	0.9999
10	10	where to purchase macbook pro	TRANSACTIONAL_BUY	0.9999
11	11	how does duckdb storage work	TRANSACTIONAL_BUY	0.9982
12	12	where to purchase macbook pro	TRANSACTIONAL_BUY	1
13	13	how does duckdb storage work	TRANSACTIONAL_BUY	1
14	14	where to purchase macbook pro	TRANSACTIONAL_BUY	0.9993
15	15	how does duckdb storage work	TRANSACTIONAL_BUY	0.9999
16	16	where to purchase macbook pro	TRANSACTIONAL_BUY	0.9998
17	17	how does duckdb storage work	TRANSACTIONAL_BUY	1
18	18	where to purchase macbook pro	TRANSACTIONAL_BUY	0.9997
19	19	how does duckdb storage work	TRANSACTIONAL_BUY	0.9993
20	20	where to purchase macbook pro	TRANSACTIONAL_BUY	0.9983
21	21	how does duckdb storage work	TRANSACTIONAL_BUY	0.9997
22	22	where to purchase macbook pro	TRANSACTIONAL_BUY	0.9971
23	23	how does duckdb storage work	TRANSACTIONAL_BUY	0.9993
24	24	where to purchase macbook pro	TRANSACTIONAL_BUY	1
25	25	how does duckdb storage work	TRANSACTIONAL_BUY	0.9994

Showing first 25 of 999,999 total rows.

← Prev: Vector Quantization Preci... ⊞ Back to Benchmark Dashboard Next: Customer Anti-Join with T... →