✔ PASS Warehouse & Statistical task_092_Q92_dynamic_unpivot_wide_metrics Scaled: 1M+ Records Ordered: No

Dynamic UNPIVOT of Wide Tabular Sensor / Survey Metrics

Candidate Latency (1M Rows)

784.96 ms

(1.1x slower than golden)

Golden Latency (1M Rows)

739.91 ms

DuckDB in-memory engine

Underlying Data Volume

1,000,000 rows

45.8 MiB across 1 table

Evaluated Model

gemini-3.8-flash-low

Gen Latency: 4.9s

✔ 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

Legacy data or spreadsheet exports often arrive with metric names as column headers (cpu, memory, disk). Normalizing them into key-value pairs is necessary for time-series modeling.

⚡ The Frontier Trap (Why LLMs Fail)

Chaining 10 UNION ALL queries multiplies read passes over the source data.

Prompt / User Request Given to Model

Legacy data or spreadsheet exports often arrive with metric names as column headers (cpu, memory, disk). Normalizing them into key-value pairs is necessary for time-series modeling.

Execute the required analytical SQL calculation in DuckDB.
Expected output columns: host_id, captured_date, metric_name, metric_value.
Row order does not matter.

2. Underlying Tables, Row Counts & Data Volume

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

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

📊 wide_telemetry 1,000,000 rows 45.8 MiB Data Volume

host_id (VARCHAR) captured_date (DATE) cpu_metric (DOUBLE) mem_metric (DOUBLE) disk_metric (DOUBLE)

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

#	host_id	captured_date	cpu_metric	mem_metric	disk_metric
1	srv-01	2026-03-01 00:00:00	45.2	78.5	92.1
2	srv-01	2026-01-01 00:00:00	53.95	93.7	109.92
3	srv-01	2026-01-02 00:00:00	49.06	85.21	99.96
4	srv-01	2026-01-03 00:00:00	43.65	75.8	88.94
5	srv-01	2026-01-04 00:00:00	36.91	64.09	75.21
6	srv-01	2026-01-05 00:00:00	54.01	93.81	110.05
7	srv-01	2026-01-06 00:00:00	39.96	69.39	81.42
8	srv-01	2026-01-07 00:00:00	52.53	91.24	107.04
9	srv-01	2026-01-08 00:00:00	41.27	71.67	84.09
10	srv-01	2026-01-09 00:00:00	46.29	80.4	94.32
11	srv-01	2026-01-10 00:00:00	49.37	85.74	100.59
12	srv-01	2026-01-11 00:00:00	49.18	85.42	100.21
13	srv-01	2026-01-12 00:00:00	35.27	61.24	71.86
14	srv-01	2026-01-13 00:00:00	50.51	87.72	102.91
15	srv-01	2026-01-14 00:00:00	42.98	74.64	87.58

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

📐 PROJECTION 1.3%

Latency: 0.64 ms

Rows In/Out: 3,000,000

Data Vol: 125.89 MB

📁 Projections: ['#0', '#1', '#4', ...

🎯 FILTER 1.6%

Latency: 0.76 ms

Rows In/Out: 3,000,000

Data Vol: 286.14 MB

⚙️ UNNEST 🔥 47.0% Cost

Latency: 22.67 ms

Rows In/Out: 3,000,000

Data Vol: 286.14 MB

📐 PROJECTION 🔥 23.0% Cost

Latency: 11.07 ms

Rows In/Out: 1,000,000

Data Vol: 72.50 MB

📁 Projections: ['host_id', 'captur...

🔍 TABLE_SCAN 🔥 27.1% Cost

Latency: 13.06 ms

Rows In/Out: 1,000,000

Data Vol: 41.96 MB

📁 memory.main.wide_telemetry

Operator	Cost %	Latency	Processed Rows	Data Volume	Details / Table	Status
UNNEST	47.0%	22.67 ms	3,000,000	286.14 MB	-	🔥 BOTTLENECK
TABLE_SCAN	27.1%	13.06 ms	1,000,000	41.96 MB	memory.main.wide_telemetry	🔥 BOTTLENECK
PROJECTION	23.0%	11.07 ms	1,000,000	72.50 MB	Projections: ['host_id', 'captured_date', 'unpiv	🔥 BOTTLENECK
FILTER	1.6%	0.76 ms	3,000,000	286.14 MB	-	Normal
PROJECTION	1.3%	0.64 ms	3,000,000	125.89 MB	Projections: ['#0', '#1', '#4', '#5']	Normal

4. Queries Executed (Candidate vs Golden Ground Truth)

Beautified & Syntax Highlighted

UNPIVOT wide_telemetry ON cpu_metric,
                          mem_metric,
                          disk_metric INTO name metric_name value metric_value;

-- Transform wide metric columns into normalized key-value rows
 UNPIVOT wide_telemetry ON cpu_metric,
                           mem_metric,
                           disk_metric INTO name metric_name value metric_value;

CREATE TABLE wide_telemetry (host_id VARCHAR, captured_date DATE, cpu_metric DOUBLE,
                                                                             mem_metric DOUBLE,
                                                                                        disk_metric DOUBLE);


INSERT INTO wide_telemetry
VALUES ('srv-01', '2026-03-01', 45.2, 78.5, 92.1);

5. Query Results & Expected Ground Truth

Tabular Comparison

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

#	host_id	captured_date	metric_name	metric_value
1	srv-01	2026-03-01 00:00:00	cpu_metric	45.2
2	srv-01	2026-03-01 00:00:00	mem_metric	78.5
3	srv-01	2026-03-01 00:00:00	disk_metric	92.1
4	srv-01	2026-01-01 00:00:00	cpu_metric	53.95
5	srv-01	2026-01-01 00:00:00	mem_metric	93.7
6	srv-01	2026-01-01 00:00:00	disk_metric	109.92
7	srv-01	2026-01-02 00:00:00	cpu_metric	49.06
8	srv-01	2026-01-02 00:00:00	mem_metric	85.21
9	srv-01	2026-01-02 00:00:00	disk_metric	99.96
10	srv-01	2026-01-03 00:00:00	cpu_metric	43.65
11	srv-01	2026-01-03 00:00:00	mem_metric	75.8
12	srv-01	2026-01-03 00:00:00	disk_metric	88.94
13	srv-01	2026-01-04 00:00:00	cpu_metric	36.91
14	srv-01	2026-01-04 00:00:00	mem_metric	64.09
15	srv-01	2026-01-04 00:00:00	disk_metric	75.21
16	srv-01	2026-01-05 00:00:00	cpu_metric	54.01
17	srv-01	2026-01-05 00:00:00	mem_metric	93.81
18	srv-01	2026-01-05 00:00:00	disk_metric	110.05
19	srv-01	2026-01-06 00:00:00	cpu_metric	39.96
20	srv-01	2026-01-06 00:00:00	mem_metric	69.39
21	srv-01	2026-01-06 00:00:00	disk_metric	81.42
22	srv-01	2026-01-07 00:00:00	cpu_metric	52.53
23	srv-01	2026-01-07 00:00:00	mem_metric	91.24
24	srv-01	2026-01-07 00:00:00	disk_metric	107.04
25	srv-01	2026-01-08 00:00:00	cpu_metric	41.27

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

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

#	host_id	captured_date	metric_name	metric_value
1	srv-01	2026-03-01 00:00:00	cpu_metric	45.2
2	srv-01	2026-03-01 00:00:00	mem_metric	78.5
3	srv-01	2026-03-01 00:00:00	disk_metric	92.1
4	srv-01	2026-01-01 00:00:00	cpu_metric	53.95
5	srv-01	2026-01-01 00:00:00	mem_metric	93.7
6	srv-01	2026-01-01 00:00:00	disk_metric	109.92
7	srv-01	2026-01-02 00:00:00	cpu_metric	49.06
8	srv-01	2026-01-02 00:00:00	mem_metric	85.21
9	srv-01	2026-01-02 00:00:00	disk_metric	99.96
10	srv-01	2026-01-03 00:00:00	cpu_metric	43.65
11	srv-01	2026-01-03 00:00:00	mem_metric	75.8
12	srv-01	2026-01-03 00:00:00	disk_metric	88.94
13	srv-01	2026-01-04 00:00:00	cpu_metric	36.91
14	srv-01	2026-01-04 00:00:00	mem_metric	64.09
15	srv-01	2026-01-04 00:00:00	disk_metric	75.21
16	srv-01	2026-01-05 00:00:00	cpu_metric	54.01
17	srv-01	2026-01-05 00:00:00	mem_metric	93.81
18	srv-01	2026-01-05 00:00:00	disk_metric	110.05
19	srv-01	2026-01-06 00:00:00	cpu_metric	39.96
20	srv-01	2026-01-06 00:00:00	mem_metric	69.39
21	srv-01	2026-01-06 00:00:00	disk_metric	81.42
22	srv-01	2026-01-07 00:00:00	cpu_metric	52.53
23	srv-01	2026-01-07 00:00:00	mem_metric	91.24
24	srv-01	2026-01-07 00:00:00	disk_metric	107.04
25	srv-01	2026-01-08 00:00:00	cpu_metric	41.27

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

← Prev: Dynamic PIVOT of Monthly ... ⊞ Back to Benchmark Dashboard Next: List Lambdas for High-Per... →