✔ PASS Temporal & Time-Series task_007_Q07_scd2_open_ended_intervals Scaled: 1M+ Records Ordered: Yes

Slowly Changing Dimension Type 2 Join with Open-Ended Future

Candidate Latency (1M Rows)

0.44 ms

(1.1x slower than golden)

Golden Latency (1M Rows)

0.39 ms

DuckDB in-memory engine

Underlying Data Volume

1,000,002 rows

26.7 MiB across 2 tables

Evaluated Model

gemini-3.8-flash-low

Gen Latency: 7.3s

✔ 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

Customers change addresses or account tiers over time. Joining historical orders to customer profile dimensions requires matching the order date to the active validity interval.

⚡ The Frontier Trap (Why LLMs Fail)

Current active records typically have `valid_to IS NULL`. A simple `order_date BETWEEN valid_from AND valid_to` drops all current active records.

Prompt / User Request Given to Model

Customers change addresses or account tiers over time. Joining historical orders to customer profile dimensions requires matching the order date to the active validity interval.

Execute the required analytical SQL calculation in DuckDB.
Expected output columns: order_id, customer_id, order_date, amount, customer_tier_at_purchase.
Results should be ordered as specified.

2. Underlying Tables, Row Counts & Data Volume

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

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

📊 dim_customer_scd 2 rows 64 B Data Volume

customer_id (INTEGER) tier (VARCHAR) valid_from (DATE) valid_to (DATE)

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

#	customer_id	tier	valid_from	valid_to
1	88	Silver	2026-01-01 00:00:00	2026-02-01 00:00:00
2	88	Gold	2026-02-01 00:00:00	NULL

📊 fact_orders 1,000,000 rows 26.7 MiB Data Volume

order_id (INTEGER) customer_id (INTEGER) order_date (DATE) amount (DECIMAL(10,2))

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

#	order_id	customer_id	order_date	amount
1	1	88	2026-01-15 00:00:00	100
2	2	88	2026-02-20 00:00:00	250
3	2	88	2026-01-01 00:00:00	263.83
4	3	89	2026-01-02 00:00:00	245.46
5	4	90	2026-01-03 00:00:00	79.25
6	5	91	2026-01-04 00:00:00	75.66
7	6	92	2026-01-05 00:00:00	210.48
8	7	93	2026-01-06 00:00:00	221.45
9	8	94	2026-01-07 00:00:00	120.12
10	9	95	2026-01-08 00:00:00	98.98
11	10	96	2026-01-09 00:00:00	109.26
12	11	97	2026-01-10 00:00:00	157.07
13	12	98	2026-01-11 00:00:00	261.5
14	13	99	2026-01-12 00:00:00	281.48
15	14	100	2026-01-13 00:00:00	155.79

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

📐 PROJECTION 0.8%

Latency: 0.00 ms

Rows In/Out: 3

Data Vol: 108 B

📁 Projections: ['#0', 'CAST(#1 AS ...

⏳ ORDER_BY 33.6%

Latency: 0.15 ms

Rows In/Out: 3

Data Vol: 75 B

📐 PROJECTION 5.0%

Latency: 0.02 ms

Rows In/Out: 3

Data Vol: 75 B

📁 Projections: ['#0', 'CAST(#1 AS ...

📐 PROJECTION 0.3%

Latency: 0.00 ms

Rows In/Out: 3

Data Vol: 108 B

📁 Projections: ['order_id', 'custo...

⚡ HASH_JOIN 38.8%

Latency: 0.17 ms

Rows In/Out: 3

Data Vol: 108 B

🔍 TABLE_SCAN 18.5%

Latency: 0.08 ms

Rows In/Out: 3

Data Vol: 60 B

📁 memory.main.fact_orders

🔍 TABLE_SCAN 3.1%

Latency: 0.01 ms

Rows In/Out: 2

Data Vol: 312 B

📁 memory.main.dim_customer_scd

Operator	Cost %	Latency	Processed Rows	Details / Table	Status
HASH_JOIN	38.8%	0.17 ms	3	-	Normal
ORDER_BY	33.6%	0.15 ms	3	-	Normal
TABLE_SCAN	18.5%	0.08 ms	3	memory.main.fact_orders	Normal
PROJECTION	5.0%	0.02 ms	3	Projections: ['#0', 'CAST(#1 AS TINYINT)', '#2',	Normal
TABLE_SCAN	3.1%	0.01 ms	2	memory.main.dim_customer_scd	Normal
PROJECTION	0.8%	0.00 ms	3	Projections: ['#0', 'CAST(#1 AS INTEGER)', '#2',	Normal
PROJECTION	0.3%	0.00 ms	3	Projections: ['order_id', 'customer_id', 'order_	Normal

4. Queries Executed (Candidate vs Golden Ground Truth)

Beautified & Syntax Highlighted

SELECT fo.order_id,
       fo.customer_id,
       fo.order_date,
       fo.amount,
       c.tier AS customer_tier_at_purchase
FROM fact_orders fo
JOIN dim_customer_scd c ON fo.customer_id = c.customer_id
AND fo.order_date >= c.valid_from
AND (fo.order_date < c.valid_to
     OR c.valid_to IS NULL)
ORDER BY fo.order_id;

-- Join fact orders to historical customer dimension without dropping active records

SELECT o.order_id,
       o.customer_id,
       o.order_date,
       o.amount,
       c.tier AS customer_tier_at_purchase
FROM fact_orders o
JOIN dim_customer_scd c ON o.customer_id = c.customer_id
AND o.order_date >= c.valid_from
AND o.order_date < coalesce(c.valid_to, DATE '9999-12-31')
ORDER BY o.order_id;

CREATE TABLE dim_customer_scd (customer_id INT, tier VARCHAR, valid_from DATE, valid_to DATE);


CREATE TABLE fact_orders (order_id INT, customer_id INT, order_date DATE, amount decimal(10, 2));


INSERT INTO dim_customer_scd
VALUES (88, 'Silver', '2026-01-01', '2026-02-01'),
       (88, 'Gold', '2026-02-01', NULL);


INSERT INTO fact_orders
VALUES (1, 88, '2026-01-15', 100.00),
       (2, 88, '2026-02-20', 250.00);

5. Query Results & Expected Ground Truth

Tabular Comparison

Actual Candidate Output (3 rows × 5 cols)

#	order_id	customer_id	order_date	amount	customer_tier_at_purchase
1	1	88	2026-01-15 00:00:00	100	Silver
2	2	88	2026-02-20 00:00:00	250	Gold
3	2	88	2026-01-01 00:00:00	263.83	Silver

Expected Golden Output (3 rows × 5 cols)

#	order_id	customer_id	order_date	amount	customer_tier_at_purchase
1	1	88	2026-01-15 00:00:00	100	Silver
2	2	88	2026-02-20 00:00:00	250	Gold
3	2	88	2026-01-01 00:00:00	263.83	Silver

← Prev: Bitemporal Auditing: Vali... ⊞ Back to Benchmark Dashboard Next: Hopping / Overlapping Win... →