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

Temporal Semantic Drift Tracking across Document Embedding Centroids

Candidate Latency (1M Rows)

1151.99 ms

(1.0x slower than golden)

Golden Latency (1M Rows)

1126.27 ms

DuckDB in-memory engine

Underlying Data Volume

1,000,000 rows

11.4 MiB across 1 table

Evaluated Model

gemini-3.8-flash-low

Gen Latency: 11.4s

✔ 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

As companies evolve, the meaning of terms like 'security' or 'remote work' shifts. Tracking the cosine distance between monthly embedding centroids reveals semantic concept drift.

⚡ The Frontier Trap (Why LLMs Fail)

Ignoring semantic drift causes older documentation to silently diverge from current employee search queries.

Prompt / User Request Given to Model

As companies evolve, the meaning of terms like 'security' or 'remote work' shifts. Tracking the cosine distance between monthly embedding centroids reveals semantic concept drift.

Execute the required analytical SQL calculation in DuckDB.
Expected output columns: month_date, centroid, prev_month_centroid, drift_distance_from_prev_month.
Results should be ordered as specified.

2. Underlying Tables, Row Counts & Data Volume

1 Tables (1,000,000 rows • 11.4 MiB)

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

📊 monthly_centroids 1,000,000 rows 11.4 MiB Data Volume

month_date (DATE) centroid (FLOAT[2])

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

#	month_date	centroid
1	2026-01-01 00:00:00	[0.9 0.1]
2	2026-02-01 00:00:00	[0.88 0.12]
3	2026-03-01 00:00:00	[0.7 0.35]
4	2026-01-01 00:00:00	[0.8424 0.0694]
5	2026-01-02 00:00:00	[0.8895 0.1201]
6	2026-01-03 00:00:00	[0.8591 0.1216]
7	2026-01-04 00:00:00	[0.887 0.0911]
8	2026-01-05 00:00:00	[0.8526 0.0982]
9	2026-01-06 00:00:00	[0.8441 0.0441]
10	2026-01-07 00:00:00	[0.8703 0.0642]
11	2026-01-08 00:00:00	[0.8224 0.1119]
12	2026-01-09 00:00:00	[0.8206 0.1089]
13	2026-01-10 00:00:00	[0.8558 0.0955]
14	2026-01-11 00:00:00	[0.8805 0.1414]
15	2026-01-12 00:00:00	[0.7967 0.0943]

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

⏳ ORDER_BY 🔥 36.0% Cost

Latency: 116.49 ms

Rows In/Out: 1,000,000

Data Vol: 22.89 MB

📐 PROJECTION 5.5%

Latency: 17.69 ms

Rows In/Out: 1,000,000

Data Vol: 22.89 MB

📁 Projections: ['month_date', 'cen...

📐 PROJECTION 0.0%

Latency: 0.13 ms

Rows In/Out: 1,000,000

Data Vol: 19.07 MB

📁 Projections: ['#0', '#1', '#2']

🪟 WINDOW 🔥 57.7% Cost

Latency: 186.54 ms

Rows In/Out: 1,000,000

Data Vol: 19.07 MB

📁 Projections: lag(centroid, 1, NU...

🔍 TABLE_SCAN 0.8%

Latency: 2.52 ms

Rows In/Out: 1,000,000

Data Vol: 11.44 MB

📁 memory.main.monthly_centroids

Operator	Cost %	Latency	Processed Rows	Data Volume	Details / Table	Status
WINDOW	57.7%	186.54 ms	1,000,000	19.07 MB	Projections: lag(centroid, 1, NULL::FLOAT[2]) OV	🔥 BOTTLENECK
ORDER_BY	36.0%	116.49 ms	1,000,000	22.89 MB	-	🔥 BOTTLENECK
PROJECTION	5.5%	17.69 ms	1,000,000	22.89 MB	Projections: ['month_date', 'centroid', 'prev_mo	Normal
TABLE_SCAN	0.8%	2.52 ms	1,000,000	11.44 MB	memory.main.monthly_centroids	Normal
PROJECTION	0.0%	0.13 ms	1,000,000	19.07 MB	Projections: ['#0', '#1', '#2']	Normal

4. Queries Executed (Candidate vs Golden Ground Truth)

Beautified & Syntax Highlighted

WITH lagged_centroids AS
  (SELECT month_date,
          centroid,
          lag(centroid) OVER (
                              ORDER BY month_date) AS prev_month_centroid
   FROM monthly_centroids)
SELECT month_date,
       centroid,
       prev_month_centroid,
       CASE
           WHEN prev_month_centroid IS NOT NULL THEN list_cosine_distance(centroid, prev_month_centroid)
           ELSE NULL
       END AS drift_distance_from_prev_month
FROM lagged_centroids
ORDER BY month_date;

-- Track month-over-month semantic drift in concept centroids

SELECT month_date,
       centroid,
       lag(centroid) OVER (
                           ORDER BY month_date) AS prev_month_centroid,
       round(1.0 - array_cosine_similarity(centroid, lag(centroid) OVER (
                                                                         ORDER BY month_date)), 4) AS drift_distance_from_prev_month
FROM monthly_centroids
ORDER BY month_date;

CREATE TABLE monthly_centroids (month_date DATE, centroid FLOAT[2]);


INSERT INTO monthly_centroids
VALUES ('2026-01-01', [0.90, 0.10]::FLOAT[2]),
       ('2026-02-01', [0.88, 0.12]::FLOAT[2]),
       ('2026-03-01', [0.70, 0.35]::FLOAT[2]); -- Significant semantic drift

5. Query Results & Expected Ground Truth

Tabular Comparison

Actual Candidate Output (1,000,000 rows × 4 cols)

#	month_date	centroid	prev_month_centroid	drift_distance_from_prev_month
1	2026-01-01 00:00:00	[0.8322 0.0805]	NULL	NULL
2	2026-01-01 00:00:00	[0.8994 0.1083]	[0.8322 0.0805]	0.0003
3	2026-01-01 00:00:00	[0.8692 0.0593]	[0.8994 0.1083]	0.0013
4	2026-01-01 00:00:00	[0.8524 0.0679]	[0.8692 0.0593]	0.0001
5	2026-01-01 00:00:00	[0.8498 0.1249]	[0.8524 0.0679]	0.0022
6	2026-01-01 00:00:00	[0.8205 0.1207]	[0.8498 0.1249]	0
7	2026-01-01 00:00:00	[0.8476 0.0533]	[0.8205 0.1207]	0.0035
8	2026-01-01 00:00:00	[0.854 0.0783]	[0.8476 0.0533]	0.0004
9	2026-01-01 00:00:00	[0.8575 0.1427]	[0.854 0.0783]	0.0027
10	2026-01-01 00:00:00	[0.8497 0.1003]	[0.8575 0.1427]	0.0011
11	2026-01-01 00:00:00	[0.8028 0.1028]	[0.8497 0.1003]	0
12	2026-01-01 00:00:00	[0.8284 0.1292]	[0.8028 0.1028]	0.0004
13	2026-01-01 00:00:00	[0.8786 0.0806]	[0.8284 0.1292]	0.002
14	2026-01-01 00:00:00	[0.7655 0.0758]	[0.8786 0.0806]	0
15	2026-01-01 00:00:00	[0.8243 0.1812]	[0.7655 0.0758]	0.0069
16	2026-01-01 00:00:00	[0.8158 0.0795]	[0.8243 0.1812]	0.0071
17	2026-01-01 00:00:00	[0.887 0.0606]	[0.8158 0.0795]	0.0004
18	2026-01-01 00:00:00	[0.8053 0.0281]	[0.887 0.0606]	0.0006
19	2026-01-01 00:00:00	[0.7947 0.1044]	[0.8053 0.0281]	0.0046
20	2026-01-01 00:00:00	[0.9172 0.1684]	[0.7947 0.1044]	0.0013
21	2026-01-01 00:00:00	[0.857 0.12 ]	[0.9172 0.1684]	0.0009
22	2026-01-01 00:00:00	[0.8194 0.1577]	[0.857 0.12 ]	0.0013
23	2026-01-01 00:00:00	[0.8825 0.1043]	[0.8194 0.1577]	0.0026
24	2026-01-01 00:00:00	[0.872 0.1186]	[0.8825 0.1043]	0.0002
25	2026-01-01 00:00:00	[0.7697 0.1248]	[0.872 0.1186]	0.0003

Showing first 25 of 1,000,000 total rows.

Expected Golden Output (1,000,000 rows × 4 cols)

#	month_date	centroid	prev_month_centroid	drift_distance_from_prev_month
1	2026-01-01 00:00:00	[0.8322 0.0805]	NULL	NULL
2	2026-01-01 00:00:00	[0.8994 0.1083]	[0.8322 0.0805]	0.0003
3	2026-01-01 00:00:00	[0.8692 0.0593]	[0.8994 0.1083]	0.0013
4	2026-01-01 00:00:00	[0.8524 0.0679]	[0.8692 0.0593]	0.0001
5	2026-01-01 00:00:00	[0.8498 0.1249]	[0.8524 0.0679]	0.0022
6	2026-01-01 00:00:00	[0.8205 0.1207]	[0.8498 0.1249]	0
7	2026-01-01 00:00:00	[0.8476 0.0533]	[0.8205 0.1207]	0.0035
8	2026-01-01 00:00:00	[0.854 0.0783]	[0.8476 0.0533]	0.0004
9	2026-01-01 00:00:00	[0.8575 0.1427]	[0.854 0.0783]	0.0027
10	2026-01-01 00:00:00	[0.8497 0.1003]	[0.8575 0.1427]	0.0011
11	2026-01-01 00:00:00	[0.8028 0.1028]	[0.8497 0.1003]	0
12	2026-01-01 00:00:00	[0.8284 0.1292]	[0.8028 0.1028]	0.0004
13	2026-01-01 00:00:00	[0.8786 0.0806]	[0.8284 0.1292]	0.002
14	2026-01-01 00:00:00	[0.7655 0.0758]	[0.8786 0.0806]	0
15	2026-01-01 00:00:00	[0.8243 0.1812]	[0.7655 0.0758]	0.0069
16	2026-01-01 00:00:00	[0.8158 0.0795]	[0.8243 0.1812]	0.0071
17	2026-01-01 00:00:00	[0.887 0.0606]	[0.8158 0.0795]	0.0004
18	2026-01-01 00:00:00	[0.8053 0.0281]	[0.887 0.0606]	0.0006
19	2026-01-01 00:00:00	[0.7947 0.1044]	[0.8053 0.0281]	0.0046
20	2026-01-01 00:00:00	[0.9172 0.1684]	[0.7947 0.1044]	0.0013
21	2026-01-01 00:00:00	[0.857 0.12 ]	[0.9172 0.1684]	0.0009
22	2026-01-01 00:00:00	[0.8194 0.1577]	[0.857 0.12 ]	0.0013
23	2026-01-01 00:00:00	[0.8825 0.1043]	[0.8194 0.1577]	0.0026
24	2026-01-01 00:00:00	[0.872 0.1186]	[0.8825 0.1043]	0.0002
25	2026-01-01 00:00:00	[0.7697 0.1248]	[0.872 0.1186]	0.0003

Showing first 25 of 1,000,000 total rows.

← Prev: Two-Stage Retrieval: Bi-E... ⊞ Back to Benchmark Dashboard Next: Hierarchical Coarse-to-Fi... →