DB Lab Dashboard

Passed12

Needs service0

Failed0

Dashboard Metrics

Latest suite status 12 passed / 0 needs_service / 0 failed

Check availability before interpreting runtime.

lab_suite.md

Result validation 23 passed / 0 failed

Correctness comes before performance comparison.

result_validation.md

Service-backed coverage 6 / 6 service experiments passed

Shows whether ClickHouse and DolphinDB evidence is current.

lab_suite.md

Controlled sweep profiles 2 profiles, 2 passed, 0 failed

Confirms the variable scan is usable for learning.

controlled_sweep.md

Controlled variable market_rows: 1000, 5000

Keeps the current comparison to one changed input.

controlled_sweep.md

Slowest latest experiment market_ticks/duckdb/scale: 1370.59 ms

Start plan and storage inspection from the slowest repeated task.

lab_suite.md

Largest sweep delta market_ticks/duckdb/storage: -595.87 ms

Largest runtime movement after changing the controlled variable.

controlled_sweep.md

Experiment Console

Focus

Market rows

Ecommerce rows

Ecommerce users

IoT rows

IoT devices

Log rows

Log services

Stream rate

Sweep variable

Force data Start services Logs ClickHouse logs

Use Build to generate commands. Use Run Local from `db-lab serve-dashboard`.

Data Snapshot

Market ticks CSV: data/generated/market_ticks.csv
Market ticks action: generated
Market ticks rows: 1000
Market ticks symbols: 6
Market ticks time range: 2026-01-02T09:30:00.000 -> 2026-01-02T09:34:09.750
Ecommerce events CSV: data/generated/ecommerce_events.csv
Ecommerce events action: generated
Ecommerce events rows: 1000
Ecommerce events users: 97
Ecommerce events time range: 2026-01-02T00:00:00 -> 2026-01-02T00:49:57
IoT metrics CSV: data/generated/iot_metrics.csv
IoT metrics action: generated
IoT metrics rows: 2000
IoT metrics devices: 100
IoT metrics time range: 2026-01-02T00:00:00 -> 2026-01-02T02:46:35
IoT alerts CSV: data/generated/iot_alerts.csv
IoT alerts action: generated
IoT alerts rows: 12
IoT alerts devices: 12
IoT alerts time range: 2026-01-02T00:00:00 -> 2026-01-02T02:44:30

Next Actions

Run db-lab doctor --report experiments/reports/doctor.md before service-backed experiments.
Run db-lab capabilities to review coverage across DuckDB, ClickHouse, and DolphinDB.
Run db-lab journal after the suite to turn results into a learning record.
passed means the experiment completed and produced its report.
needs_service means a service-backed engine is not reachable.
Start services with docker compose up -d clickhouse dolphindb, then rerun the suite.
Use the per-experiment report and query comparison to record what each engine makes easy or difficult.

Experiment Status

duckdb

market_ticks / batch

passed

331.95 ms

Report: experiments/reports/duckdb_market_ticks.md

completed

duckdb

market_ticks / storage

passed

1232.12 ms

Report: experiments/reports/duckdb_storage_formats.md

completed

duckdb

market_ticks / plans

passed

197.48 ms

Report: experiments/reports/duckdb_query_plans.md

completed

duckdb

market_ticks / scale

passed

1370.59 ms

Report: experiments/reports/duckdb_scale.md

completed

clickhouse

market_ticks / batch

passed

57.30 ms

Report: experiments/reports/clickhouse_market_ticks.md

completed

dolphindb

market_ticks / batch

passed

57.42 ms

Report: experiments/reports/dolphindb_market_ticks.md

completed

dolphindb

market_ticks / stream

passed

1016.40 ms

Report: experiments/reports/dolphindb_market_stream.md

completed

duckdb

ecommerce_events / batch

passed

398.22 ms

Report: experiments/reports/duckdb_ecommerce_events.md

completed

clickhouse

ecommerce_events / materialized

passed

58.13 ms

Report: experiments/reports/clickhouse_ecommerce_events.md

completed

duckdb

iot_metrics / batch

passed

860.12 ms

Report: experiments/reports/duckdb_iot_metrics.md

completed

clickhouse

iot_metrics / batch

passed

49.73 ms

Report: experiments/reports/clickhouse_iot_metrics.md

completed

dolphindb

iot_metrics / batch

passed

112.76 ms

Report: experiments/reports/dolphindb_iot_metrics.md

completed

Capability Matrix

Current database skill coverage, generated from suite status and evidence files.

duckdb

Total: 7
Passed: 7
Needs service: 0
Other: 0

clickhouse

Total: 4
Passed: 4
Needs service: 0
Other: 0

dolphindb

Total: 3
Passed: 3
Needs service: 0
Other: 0

Engine	Capability	Status	Study focus	Evidence	Next action
duckdb	Embedded file analytics	passed	Direct CSV scans, local OLAP, VWAP, and minute bars.	`sql/duckdb/market_ticks/00_profile.sql`, `sql/duckdb/market_ticks/01_symbol_vwap.sql`, `sql/duckdb/market_ticks/02_minute_bars.sql`, `experiments/reports/duckdb_market_ticks.md`	Change row count and compare query timing.
duckdb	Ecommerce funnel analytics	passed	Session funnels, revenue segmentation, and Top product queries.	`sql/duckdb/ecommerce_events/00_profile.sql`, `sql/duckdb/ecommerce_events/01_funnel.sql`, `sql/duckdb/ecommerce_events/02_revenue_segments.sql`, `sql/duckdb/ecommerce_events/03_top_products.sql`, `experiments/reports/duckdb_ecommerce_events.md`	Add a cohort or retention query.
duckdb	IoT offline diagnostics	passed	Local device metrics, time-window aggregates, alert joins, and anomaly ranking.	`sql/duckdb/iot_metrics/00_profile.sql`, `sql/duckdb/iot_metrics/01_metric_windows.sql`, `sql/duckdb/iot_metrics/02_alert_join.sql`, `sql/duckdb/iot_metrics/03_device_health.sql`, `experiments/reports/duckdb_iot_metrics.md`	Change device count and compare the alert join with service-backed engines.
duckdb	Log observability triage	passed	Local log and trace triage with error-rate, latency percentile, and trace hotspot queries.	`sql/duckdb/log_observability/00_profile.sql`, `sql/duckdb/log_observability/01_error_rate.sql`, `sql/duckdb/log_observability/02_latency_percentiles.sql`, `sql/duckdb/log_observability/03_trace_hotspots.sql`, `experiments/reports/duckdb_log_observability.md`	Run `db-lab generate-logs` and `db-lab run-duckdb-logs`, then compare with ClickHouse on the VPS.
duckdb	CSV vs Parquet storage	passed	Typed columnar files, compression, column pruning, and local scan timing.	`experiments/reports/duckdb_storage_formats.md`, `docs/duckdb-storage.md`	Rerun with larger row counts and compare size ratios.
duckdb	Query plan inspection	passed	EXPLAIN, EXPLAIN ANALYZE, scan operators, filters, and projections.	`experiments/reports/duckdb_query_plans.md`, `src/db_lab/experiments/duckdb_query_plans.py`	Run `db-lab run-duckdb-plans --analyze` and compare operator timing.
duckdb	Scale sensitivity	passed	Row-count changes, CSV/Parquet size growth, and local scan timing direction.	`experiments/reports/duckdb_scale.md`, `src/db_lab/experiments/duckdb_scale.py`	Increase scale rows and compare when Parquet advantage becomes clearer.
clickhouse	MergeTree market OLAP	passed	Server OLAP, MergeTree schema, partitioning, sorting keys, and batch insert.	`sql/clickhouse/market_ticks/schema.sql`, `sql/clickhouse/market_ticks/00_profile.sql`, `sql/clickhouse/market_ticks/01_symbol_vwap.sql`, `sql/clickhouse/market_ticks/02_minute_bars.sql`, `docs/service-runbook.md`	Start ClickHouse and compare the same market queries with DuckDB.
clickhouse	Materialized ecommerce aggregation	passed	MergeTree raw events, SummingMergeTree aggregate table, and materialized view flow.	`sql/clickhouse/ecommerce_events/schema.sql`, `sql/clickhouse/ecommerce_events/01_funnel.sql`, `sql/clickhouse/ecommerce_events/04_materialized_revenue.sql`, `docs/service-runbook.md`	Start ClickHouse and verify the minute revenue aggregate after inserts.
clickhouse	IoT service metrics OLAP	passed	MergeTree device metrics, server-side joins, countIf anomaly scoring, and region windows.	`sql/clickhouse/iot_metrics/schema.sql`, `sql/clickhouse/iot_metrics/01_metric_windows.sql`, `sql/clickhouse/iot_metrics/02_alert_join.sql`, `sql/clickhouse/iot_metrics/03_device_health.sql`, `experiments/reports/clickhouse_iot_metrics.md`	Compare ClickHouse IoT windows with DuckDB and DolphinDB after changing row count.
clickhouse	Log observability OLAP	passed	MergeTree-backed logs and traces for service error-rate, tail-latency, and trace hotspot analysis.	`sql/clickhouse/log_observability/schema.sql`, `sql/clickhouse/log_observability/01_error_rate.sql`, `sql/clickhouse/log_observability/02_latency_percentiles.sql`, `sql/clickhouse/log_observability/03_trace_hotspots.sql`, `experiments/reports/clickhouse_log_observability.md`, `docs/service-runbook.md`	Run `db-lab run-clickhouse-logs` on the VPS after generating log observability CSV files.
dolphindb	TSDB market time-series batch	passed	DolphinDB script workflow, TSDB partitioning, and market time-series queries.	`sql/dolphindb/market_ticks/schema.dos`, `sql/dolphindb/market_ticks/import_csv.dos`, `sql/dolphindb/market_ticks/00_profile.dos`, `sql/dolphindb/market_ticks/01_symbol_vwap.dos`, `sql/dolphindb/market_ticks/02_minute_bars.dos`, `docs/service-runbook.md`	Start DolphinDB and verify batch minute bars.
dolphindb	Stream replay and realtime bars	passed	Shared stream tables, subscribeTable, createTimeSeriesEngine, replay, and realtime bars.	`sql/dolphindb/market_stream/setup.dos`, `sql/dolphindb/market_stream/replay_csv.dos`, `sql/dolphindb/market_stream/01_realtime_minute_bars.dos`, `docs/dolphindb-streaming.md`, `docs/service-runbook.md`	Start DolphinDB and compare stream output with batch minute bars.
dolphindb	IoT TSDB diagnostics	passed	DolphinDB TSDB partitioning, event-time windows, equality joins, and device anomaly scoring.	`sql/dolphindb/iot_metrics/schema.dos`, `sql/dolphindb/iot_metrics/import_csv.dos`, `sql/dolphindb/iot_metrics/01_metric_windows.dos`, `sql/dolphindb/iot_metrics/02_alert_join.dos`, `experiments/reports/dolphindb_iot_metrics.md`	Compare IoT batch diagnostics with a future stream-ingestion experiment.

Query Comparison

Same business questions, shown directly from the scripts used by each engine.

Market ticks

Data profile

Check row count, symbol count, time range, and total volume.

duckdb

sql/duckdb/market_ticks/00_profile.sql

SELECT
    count(*) AS row_count,
    count(DISTINCT symbol) AS symbol_count,
    min(ts) AS start_ts,
    max(ts) AS end_ts,
    sum(volume) AS total_volume
FROM market_ticks;

clickhouse

sql/clickhouse/market_ticks/00_profile.sql

SELECT
    count() AS row_count,
    uniqExact(symbol) AS symbol_count,
    min(ts) AS start_ts,
    max(ts) AS end_ts,
    sum(volume) AS total_volume
FROM db_lab.market_ticks

dolphindb

sql/dolphindb/market_ticks/00_profile.dos

pt = loadTable("dfs://db_lab_market", `market_ticks)

rowCount = exec count(*) from pt
symbols = exec distinct(sym) from pt
symbolCount = size(symbols)
startTs = exec min(ts) from pt
endTs = exec max(ts) from pt
totalVolume = exec sum(volume) from pt

table(
    rowCount as row_count,
    symbolCount as symbol_count,
    startTs as start_ts,
    endTs as end_ts,
    totalVolume as total_volume
)

Market ticks

Symbol VWAP

Aggregate trades by symbol and exchange, then calculate VWAP and price bounds.

duckdb

sql/duckdb/market_ticks/01_symbol_vwap.sql

SELECT
    symbol,
    exchange,
    count(*) AS trades,
    sum(volume) AS volume,
    round(sum(price * volume) / sum(volume), 4) AS vwap,
    round(min(price), 4) AS low_price,
    round(max(price), 4) AS high_price
FROM market_ticks
GROUP BY symbol, exchange
ORDER BY volume DESC;

clickhouse

sql/clickhouse/market_ticks/01_symbol_vwap.sql

SELECT
    symbol,
    exchange,
    count() AS trades,
    sum(volume) AS total_volume,
    round(sum(price * volume) / sum(volume), 4) AS vwap,
    round(min(price), 4) AS low_price,
    round(max(price), 4) AS high_price
FROM db_lab.market_ticks
GROUP BY symbol, exchange
ORDER BY total_volume DESC

dolphindb

sql/dolphindb/market_ticks/01_symbol_vwap.dos

select
    sym,
    exchange,
    count(*) as trades,
    sum(volume) as volume,
    round(sum(price * volume) / sum(volume), 4) as vwap,
    round(min(price), 4) as low_price,
    round(max(price), 4) as high_price
from loadTable("dfs://db_lab_market", `market_ticks)
group by sym, exchange
order by volume desc

Market ticks

Minute OHLC bars

Convert ticks into one-minute OHLC bars with volume.

duckdb

sql/duckdb/market_ticks/02_minute_bars.sql

SELECT
    date_trunc('minute', ts) AS minute,
    symbol,
    round(first(price ORDER BY ts), 4) AS open,
    round(max(price), 4) AS high,
    round(min(price), 4) AS low,
    round(last(price ORDER BY ts), 4) AS close,
    sum(volume) AS volume
FROM market_ticks
GROUP BY minute, symbol
ORDER BY minute, symbol
LIMIT 24;

clickhouse

sql/clickhouse/market_ticks/02_minute_bars.sql

SELECT
    toStartOfMinute(ts) AS minute,
    symbol,
    round(argMin(price, ts), 4) AS open,
    round(max(price), 4) AS high,
    round(min(price), 4) AS low,
    round(argMax(price, ts), 4) AS close,
    sum(volume) AS volume
FROM db_lab.market_ticks
GROUP BY minute, symbol
ORDER BY minute, symbol
LIMIT 24

dolphindb

sql/dolphindb/market_ticks/02_minute_bars.dos

ticks = select
    bar(ts, 1m) as minute,
    sym,
    price,
    volume
from loadTable("dfs://db_lab_market", `market_ticks)

select
    minute,
    sym,
    round(first(price), 4) as open,
    round(max(price), 4) as high,
    round(min(price), 4) as low,
    round(last(price), 4) as close,
    sum(volume) as volume
from ticks
group by minute, sym
order by minute, sym
limit 24

Ecommerce events

Data profile

Check event, user, session, time range, and revenue totals.

duckdb

sql/duckdb/ecommerce_events/00_profile.sql

SELECT
    count(*) AS event_count,
    count(DISTINCT user_id) AS user_count,
    count(DISTINCT session_id) AS session_count,
    min(ts) AS start_ts,
    max(ts) AS end_ts,
    round(sum(amount), 2) AS gross_revenue
FROM ecommerce_events;

clickhouse

sql/clickhouse/ecommerce_events/00_profile.sql

SELECT
    count() AS event_count,
    uniqExact(user_id) AS user_count,
    uniqExact(session_id) AS session_count,
    min(ts) AS start_ts,
    max(ts) AS end_ts,
    round(sum(amount), 2) AS gross_revenue
FROM db_lab.ecommerce_events

Ecommerce events

Session funnel

Compare sessions reaching page view, product view, cart, checkout, and purchase.

duckdb

sql/duckdb/ecommerce_events/01_funnel.sql

WITH step_order(event_name, step_no) AS (
    VALUES
        ('page_view', 1),
        ('view_product', 2),
        ('add_cart', 3),
        ('checkout', 4),
        ('purchase', 5)
),
step_counts AS (
    SELECT
        event_name,
        count(*) AS events,
        count(DISTINCT session_id) AS sessions,
        count(DISTINCT user_id) AS users
    FROM ecommerce_events
    GROUP BY event_name
)
SELECT
    o.step_no,
    o.event_name,
    coalesce(c.events, 0) AS events,
    coalesce(c.sessions, 0) AS sessions,
    coalesce(c.users, 0) AS users,
    round(
        100.0 * coalesce(c.sessions, 0)
        / nullif(first_value(coalesce(c.sessions, 0)) OVER (ORDER BY o.step_no), 0),
        2
    ) AS session_rate_pct
FROM step_order AS o
LEFT JOIN step_counts AS c USING (event_name)
ORDER BY o.step_no;

clickhouse

sql/clickhouse/ecommerce_events/01_funnel.sql

WITH
    (
        SELECT uniqExact(session_id)
        FROM db_lab.ecommerce_events
        WHERE event_name = 'page_view'
    ) AS base_sessions
SELECT
    indexOf(
        ['page_view', 'view_product', 'add_cart', 'checkout', 'purchase'],
        event_name
    ) AS step_no,
    event_name,
    count() AS events,
    uniqExact(session_id) AS sessions,
    uniqExact(user_id) AS users,
    round(100.0 * uniqExact(session_id) / nullIf(base_sessions, 0), 2) AS session_rate_pct
FROM db_lab.ecommerce_events
WHERE event_name IN ('page_view', 'view_product', 'add_cart', 'checkout', 'purchase')
GROUP BY event_name
ORDER BY step_no

Ecommerce events

Revenue segments

Aggregate users, purchases, revenue, and average order value by country and channel.

duckdb

sql/duckdb/ecommerce_events/02_revenue_segments.sql

SELECT
    country,
    channel,
    count(DISTINCT user_id) AS users,
    sum(CASE WHEN event_name = 'purchase' THEN 1 ELSE 0 END) AS purchases,
    round(sum(amount), 2) AS revenue,
    round(sum(amount) / nullif(sum(CASE WHEN event_name = 'purchase' THEN 1 ELSE 0 END), 0), 2) AS avg_order_value
FROM ecommerce_events
GROUP BY country, channel
ORDER BY revenue DESC
LIMIT 20;

clickhouse

sql/clickhouse/ecommerce_events/02_revenue_segments.sql

SELECT
    country,
    channel,
    uniqExact(user_id) AS users,
    countIf(event_name = 'purchase') AS purchases,
    round(sum(amount), 2) AS revenue,
    round(sum(amount) / nullIf(countIf(event_name = 'purchase'), 0), 2) AS avg_order_value
FROM db_lab.ecommerce_events
GROUP BY country, channel
ORDER BY revenue DESC
LIMIT 20

Ecommerce events

Top products

Rank products by purchased units, revenue, and distinct buyers.

duckdb

sql/duckdb/ecommerce_events/03_top_products.sql

SELECT
    sku_id,
    category,
    brand,
    sum(quantity) AS units,
    round(sum(amount), 2) AS revenue,
    count(DISTINCT user_id) AS buyers
FROM ecommerce_events
WHERE event_name = 'purchase'
GROUP BY sku_id, category, brand
ORDER BY revenue DESC
LIMIT 20;

clickhouse

sql/clickhouse/ecommerce_events/03_top_products.sql

SELECT
    sku_id,
    category,
    brand,
    sum(quantity) AS units,
    round(sum(amount), 2) AS revenue,
    uniqExact(user_id) AS buyers
FROM db_lab.ecommerce_events
WHERE event_name = 'purchase'
GROUP BY sku_id, category, brand
ORDER BY revenue DESC
LIMIT 20

Ecommerce events

Materialized revenue

Inspect the ClickHouse minute-level revenue aggregate maintained during inserts.

clickhouse

sql/clickhouse/ecommerce_events/04_materialized_revenue.sql

SELECT
    minute,
    country,
    channel,
    sum(purchases) AS purchases,
    round(sum(revenue), 2) AS revenue
FROM db_lab.ecommerce_revenue_by_minute
GROUP BY minute, country, channel
ORDER BY minute, revenue DESC
LIMIT 30

IoT metrics

Data profile

Check metric rows, devices, alerts, time range, and average value.

duckdb

sql/duckdb/iot_metrics/00_profile.sql

SELECT
    (SELECT count(*) FROM iot_metrics) AS metric_count,
    (SELECT count(DISTINCT device_id) FROM iot_metrics) AS device_count,
    (SELECT count(*) FROM iot_alerts) AS alert_count,
    min(ts) AS start_ts,
    max(ts) AS end_ts,
    round(avg(value), 4) AS avg_value
FROM iot_metrics;

clickhouse

sql/clickhouse/iot_metrics/00_profile.sql

SELECT
    (SELECT count() FROM db_lab.iot_metrics) AS metric_count,
    (SELECT uniqExact(device_id) FROM db_lab.iot_metrics) AS device_count,
    (SELECT count() FROM db_lab.iot_alerts) AS alert_count,
    min(ts) AS start_ts,
    max(ts) AS end_ts,
    round(avg(value), 4) AS avg_value
FROM db_lab.iot_metrics

dolphindb

sql/dolphindb/iot_metrics/00_profile.dos

metrics = loadTable("dfs://db_lab_iot", `iot_metrics)
alerts = loadTable("dfs://db_lab_iot", `iot_alerts)

metricCount = exec count(*) from metrics
devices = exec distinct(device_id) from metrics
deviceCount = size(devices)
alertCount = exec count(*) from alerts
startTs = exec min(ts) from metrics
endTs = exec max(ts) from metrics
avgValue = exec avg(value) from metrics

table(
    metricCount as metric_count,
    deviceCount as device_count,
    alertCount as alert_count,
    startTs as start_ts,
    endTs as end_ts,
    round(avgValue, 4) as avg_value
)

IoT metrics

Metric windows

Aggregate device metrics by minute, region, and metric name.

duckdb

sql/duckdb/iot_metrics/01_metric_windows.sql

SELECT
    date_trunc('minute', ts) AS minute,
    region,
    metric_name,
    count(*) AS samples,
    round(avg(value), 4) AS avg_value,
    round(max(value), 4) AS max_value
FROM iot_metrics
GROUP BY minute, region, metric_name
ORDER BY minute, region, metric_name
LIMIT 18;

clickhouse

sql/clickhouse/iot_metrics/01_metric_windows.sql

SELECT
    toStartOfMinute(ts) AS minute,
    region,
    metric_name,
    count() AS samples,
    round(avg(value), 4) AS avg_value,
    round(max(value), 4) AS max_value
FROM db_lab.iot_metrics
GROUP BY minute, region, metric_name
ORDER BY minute, region, metric_name
LIMIT 18

dolphindb

sql/dolphindb/iot_metrics/01_metric_windows.dos

windowed = select
    bar(ts, 1m) as minute,
    region,
    metric_name,
    value
from loadTable("dfs://db_lab_iot", `iot_metrics)

select
    minute,
    region,
    metric_name,
    count(*) as samples,
    round(avg(value), 4) as avg_value,
    round(max(value), 4) as max_value
from windowed
group by minute, region, metric_name
order by minute, region, metric_name
limit 18

IoT metrics

Alert join

Join alerts back to the triggering metric rows and compare alert pressure by region.

duckdb

sql/duckdb/iot_metrics/02_alert_join.sql

SELECT
    a.severity,
    m.region,
    count(*) AS alerts,
    round(avg(m.value), 4) AS avg_metric_value
FROM iot_alerts AS a
JOIN iot_metrics AS m
  ON a.ts = m.ts
 AND a.device_id = m.device_id
 AND a.metric_name = m.metric_name
GROUP BY a.severity, m.region
ORDER BY a.severity, m.region;

clickhouse

sql/clickhouse/iot_metrics/02_alert_join.sql

SELECT
    a.severity,
    m.region,
    count() AS alerts,
    round(avg(m.value), 4) AS avg_metric_value
FROM db_lab.iot_alerts AS a
INNER JOIN db_lab.iot_metrics AS m
    ON a.ts = m.ts
   AND a.device_id = m.device_id
   AND a.metric_name = m.metric_name
GROUP BY a.severity, m.region
ORDER BY a.severity, m.region

dolphindb

sql/dolphindb/iot_metrics/02_alert_join.dos

alerts = loadTable("dfs://db_lab_iot", `iot_alerts)
metrics = loadTable("dfs://db_lab_iot", `iot_metrics)

select
    severity,
    region,
    count(*) as alerts,
    round(avg(value), 4) as avg_metric_value
from alerts
inner join metrics
on alerts.ts = metrics.ts
and alerts.device_id = metrics.device_id
and alerts.metric_name = metrics.metric_name
group by severity, region
order by severity, region

IoT metrics

Device health

Rank devices by anomaly points and peak CPU or temperature.

duckdb

sql/duckdb/iot_metrics/03_device_health.sql

SELECT
    device_id,
    region,
    count(*) AS samples,
    sum(
        CASE
            WHEN metric_name = 'cpu_usage' AND value >= 78 THEN 1
            WHEN metric_name = 'memory_usage' AND value >= 82 THEN 1
            WHEN metric_name = 'temperature' AND value >= 68 THEN 1
            WHEN metric_name = 'network_latency' AND value >= 145 THEN 1
            ELSE 0
        END
    ) AS anomaly_points,
    round(max(CASE WHEN metric_name = 'temperature' THEN value ELSE 0 END), 4) AS max_temperature,
    round(max(CASE WHEN metric_name = 'cpu_usage' THEN value ELSE 0 END), 4) AS max_cpu
FROM iot_metrics
GROUP BY device_id, region
ORDER BY anomaly_points DESC, device_id
LIMIT 10;

clickhouse

sql/clickhouse/iot_metrics/03_device_health.sql

SELECT
    device_id,
    region,
    count() AS samples,
    countIf(
        (metric_name = 'cpu_usage' AND value >= 78)
        OR (metric_name = 'memory_usage' AND value >= 82)
        OR (metric_name = 'temperature' AND value >= 68)
        OR (metric_name = 'network_latency' AND value >= 145)
    ) AS anomaly_points,
    round(maxIf(value, metric_name = 'temperature'), 4) AS max_temperature,
    round(maxIf(value, metric_name = 'cpu_usage'), 4) AS max_cpu
FROM db_lab.iot_metrics
GROUP BY device_id, region
ORDER BY anomaly_points DESC, device_id
LIMIT 10

dolphindb

sql/dolphindb/iot_metrics/03_device_health.dos

metrics = loadTable("dfs://db_lab_iot", `iot_metrics)

select
    device_id,
    region,
    count(*) as samples,
    sum(iif(
        (metric_name == "cpu_usage" && value >= 78)
        || (metric_name == "memory_usage" && value >= 82)
        || (metric_name == "temperature" && value >= 68)
        || (metric_name == "network_latency" && value >= 145),
        1,
        0
    )) as anomaly_points,
    round(max(iif(metric_name == "temperature", value, 0)), 4) as max_temperature,
    round(max(iif(metric_name == "cpu_usage", value, 0)), 4) as max_cpu
from metrics
group by device_id, region
order by anomaly_points desc, device_id
limit 10

Log observability

Data profile

Check log rows, traces, services, span count, errors, and latency baseline.

duckdb

sql/duckdb/log_observability/00_profile.sql

SELECT
    (SELECT count(*) FROM logs) AS log_count,
    (SELECT count(DISTINCT trace_id) FROM logs) AS trace_count,
    (SELECT count(DISTINCT service) FROM logs) AS service_count,
    (SELECT count(*) FROM traces) AS span_count,
    (SELECT count(*) FROM logs WHERE level = 'ERROR') AS error_count,
    round((SELECT avg(latency_ms) FROM logs), 2) AS avg_latency_ms,
    min(ts) AS start_ts,
    max(ts) AS end_ts
FROM logs;

clickhouse

sql/clickhouse/log_observability/00_profile.sql

SELECT
    (SELECT count() FROM db_lab.logs_observability) AS log_count,
    (SELECT uniqExact(trace_id) FROM db_lab.logs_observability) AS trace_count,
    (SELECT uniqExact(service) FROM db_lab.logs_observability) AS service_count,
    (SELECT count() FROM db_lab.traces_observability) AS span_count,
    (SELECT countIf(level = 'ERROR') FROM db_lab.logs_observability) AS error_count,
    round((SELECT avg(latency_ms) FROM db_lab.logs_observability), 2) AS avg_latency_ms,
    min(ts) AS start_ts,
    max(ts) AS end_ts
FROM db_lab.logs_observability;

Log observability

Error rate

Compare error and warning pressure by service.

duckdb

sql/duckdb/log_observability/01_error_rate.sql

SELECT
    service,
    count(*) AS log_count,
    sum(CASE WHEN level = 'ERROR' THEN 1 ELSE 0 END) AS error_count,
    sum(CASE WHEN level = 'WARN' THEN 1 ELSE 0 END) AS warn_count,
    round(100.0 * sum(CASE WHEN level = 'ERROR' THEN 1 ELSE 0 END) / count(*), 2) AS error_rate_pct,
    round(avg(latency_ms), 2) AS avg_latency_ms
FROM logs
GROUP BY service
ORDER BY error_count DESC, avg_latency_ms DESC, service
LIMIT 20;

clickhouse

sql/clickhouse/log_observability/01_error_rate.sql

SELECT
    service,
    count() AS log_count,
    countIf(level = 'ERROR') AS error_count,
    countIf(level = 'WARN') AS warn_count,
    round(100.0 * error_count / log_count, 2) AS error_rate_pct,
    round(avg(latency_ms), 2) AS avg_latency_ms
FROM db_lab.logs_observability
GROUP BY service
ORDER BY error_count DESC, avg_latency_ms DESC, service
LIMIT 20;

Log observability

Latency percentiles

Rank services by median, P95, and maximum latency.

duckdb

sql/duckdb/log_observability/02_latency_percentiles.sql

SELECT
    service,
    count(*) AS log_count,
    quantile_cont(latency_ms, 0.50) AS p50_latency_ms,
    quantile_cont(latency_ms, 0.95) AS p95_latency_ms,
    max(latency_ms) AS max_latency_ms
FROM logs
GROUP BY service
ORDER BY p95_latency_ms DESC, service;

clickhouse

sql/clickhouse/log_observability/02_latency_percentiles.sql

SELECT
    service,
    count() AS log_count,
    quantileExact(0.50)(latency_ms) AS p50_latency_ms,
    quantileExact(0.95)(latency_ms) AS p95_latency_ms,
    max(latency_ms) AS max_latency_ms
FROM db_lab.logs_observability
GROUP BY service
ORDER BY p95_latency_ms DESC, service;

Log observability

Trace hotspots

Join logs with trace spans to find slow or error-heavy traces.

duckdb

sql/duckdb/log_observability/03_trace_hotspots.sql

WITH log_rollup AS (
    SELECT
        trace_id,
        count(*) AS log_count,
        sum(CASE WHEN level = 'ERROR' THEN 1 ELSE 0 END) AS error_logs,
        max(latency_ms) AS max_log_latency_ms
    FROM logs
    GROUP BY trace_id
),
span_rollup AS (
    SELECT
        trace_id,
        count(*) AS span_count,
        sum(duration_ms) AS total_span_duration_ms,
        max(duration_ms) AS max_span_duration_ms
    FROM traces
    GROUP BY trace_id
)
SELECT
    l.trace_id,
    l.log_count,
    l.error_logs,
    s.span_count,
    l.max_log_latency_ms,
    s.total_span_duration_ms,
    s.max_span_duration_ms
FROM log_rollup AS l
INNER JOIN span_rollup AS s
    ON l.trace_id = s.trace_id
ORDER BY error_logs DESC, total_span_duration_ms DESC, l.trace_id
LIMIT 10;

clickhouse

sql/clickhouse/log_observability/03_trace_hotspots.sql

WITH log_rollup AS (
    SELECT
        trace_id,
        count() AS log_count,
        countIf(level = 'ERROR') AS error_logs,
        max(latency_ms) AS max_log_latency_ms
    FROM db_lab.logs_observability
    GROUP BY trace_id
),
span_rollup AS (
    SELECT
        trace_id,
        count() AS span_count,
        sum(duration_ms) AS total_span_duration_ms,
        max(duration_ms) AS max_span_duration_ms
    FROM db_lab.traces_observability
    GROUP BY trace_id
)
SELECT
    l.trace_id,
    l.log_count,
    l.error_logs,
    s.span_count,
    l.max_log_latency_ms,
    s.total_span_duration_ms,
    s.max_span_duration_ms
FROM log_rollup AS l
INNER JOIN span_rollup AS s
    ON l.trace_id = s.trace_id
ORDER BY error_logs DESC, total_span_duration_ms DESC, l.trace_id
LIMIT 10;

DolphinDB streaming

Stream pipeline setup

Create a shared tick stream, time-series engine, output table, and local subscription.

dolphindb

sql/dolphindb/market_stream/setup.dos

share streamTable(
    10000:0,
    `ts`trade_id`sym`exchange`price`volume`side,
    [TIMESTAMP, STRING, STRING, STRING, DOUBLE, LONG, STRING]
) as dbLabTickStream

share table(
    10000:0,
    `minute`sym`open`high`low`close`volume`vwap,
    [TIMESTAMP, STRING, DOUBLE, DOUBLE, DOUBLE, DOUBLE, LONG, DOUBLE]
) as dbLabMinuteBars

dbLabMinuteBarEngine = createTimeSeriesEngine(
    name="dbLabMinuteBarEngine",
    windowSize=60000,
    step=60000,
    metrics=<[
        first(price),
        max(price),
        min(price),
        last(price),
        sum(volume),
        wsum(volume, price) / sum(volume)
    ]>,
    dummyTable=dbLabTickStream,
    outputTable=dbLabMinuteBars,
    timeColumn=`ts,
    useSystemTime=false,
    keyColumn=`sym,
    garbageSize=1000,
    useWindowStartTime=true
)

subscribeTable(
    tableName="dbLabTickStream",
    actionName="dbLabMinuteBars",
    offset=0,
    handler=append!{dbLabMinuteBarEngine},
    msgAsTable=true
)

DolphinDB streaming

Historical replay

Replay sorted historical ticks into the shared stream table at a chosen rate.

dolphindb

sql/dolphindb/market_stream/replay_csv.dos

csvSchema = table(
    `ts`trade_id`sym`exchange`price`volume`side as name,
    `STRING`STRING`STRING`STRING`DOUBLE`LONG`STRING as type,
    0..6 as col
)

raw = loadText(csvPath, ",", csvSchema, 1)

ticks = select
    temporalParse(strReplace(ts, "T", " "), "yyyy-MM-dd HH:mm:ss.SSS") as ts,
    trade_id,
    sym,
    exchange,
    price,
    volume,
    side
from raw
order by ts

if (replayRate > 0) {
    replay(
        inputTables=ticks,
        outputTables=dbLabTickStream,
        dateColumn=`ts,
        timeColumn=`ts,
        replayRate=replayRate,
        absoluteRate=true
    )
} else {
    replay(
        inputTables=ticks,
        outputTables=dbLabTickStream,
        dateColumn=`ts,
        timeColumn=`ts
    )
}

sleep(1000)

publishedTickCount = exec count(*) from dbLabTickStream
generatedBarCount = exec count(*) from dbLabMinuteBars

table(publishedTickCount as published_ticks, generatedBarCount as generated_bars)

DolphinDB streaming

Realtime minute bars

Inspect OHLC, volume, and VWAP emitted by the time-series stream engine.

dolphindb

sql/dolphindb/market_stream/01_realtime_minute_bars.dos

select
    minute,
    sym,
    round(open, 4) as open,
    round(high, 4) as high,
    round(low, 4) as low,
    round(close, 4) as close,
    volume,
    round(vwap, 4) as vwap
from dbLabMinuteBars
order by minute, sym
limit 30

Suite Report: experiments/reports/lab_suite.md

# DB Lab Experiment Suite

## Data

- Market ticks CSV: `data/generated/market_ticks.csv`
- Market ticks action: generated
- Market ticks rows: 1000
- Market ticks symbols: 6
- Market ticks time range: 2026-01-02T09:30:00.000 -> 2026-01-02T09:34:09.750
- Ecommerce events CSV: `data/generated/ecommerce_events.csv`
- Ecommerce events action: generated
- Ecommerce events rows: 1000
- Ecommerce events users: 97
- Ecommerce events time range: 2026-01-02T00:00:00 -> 2026-01-02T00:49:57
- IoT metrics CSV: `data/generated/iot_metrics.csv`
- IoT metrics action: generated
- IoT metrics rows: 2000
- IoT metrics devices: 100
- IoT metrics time range: 2026-01-02T00:00:00 -> 2026-01-02T02:46:35
- IoT alerts CSV: `data/generated/iot_alerts.csv`
- IoT alerts action: generated
- IoT alerts rows: 12
- IoT alerts devices: 12
- IoT alerts time range: 2026-01-02T00:00:00 -> 2026-01-02T02:44:30

## Experiment Status

| Scenario | Engine | Mode | Status | Elapsed ms | Report | Message |
| --- | --- | --- | --- | ---: | --- | --- |
| market_ticks | duckdb | batch | passed | 331.95 | `experiments/reports/duckdb_market_ticks.md` | completed |
| market_ticks | duckdb | storage | passed | 1232.12 | `experiments/reports/duckdb_storage_formats.md` | completed |
| market_ticks | duckdb | plans | passed | 197.48 | `experiments/reports/duckdb_query_plans.md` | completed |
| market_ticks | duckdb | scale | passed | 1370.59 | `experiments/reports/duckdb_scale.md` | completed |
| market_ticks | clickhouse | batch | passed | 57.30 | `experiments/reports/clickhouse_market_ticks.md` | completed |
| market_ticks | dolphindb | batch | passed | 57.42 | `experiments/reports/dolphindb_market_ticks.md` | completed |
| market_ticks | dolphindb | stream | passed | 1016.40 | `experiments/reports/dolphindb_market_stream.md` | completed |
| ecommerce_events | duckdb | batch | passed | 398.22 | `experiments/reports/duckdb_ecommerce_events.md` | completed |
| ecommerce_events | clickhouse | materialized | passed | 58.13 | `experiments/reports/clickhouse_ecommerce_events.md` | completed |
| iot_metrics | duckdb | batch | passed | 860.12 | `experiments/reports/duckdb_iot_metrics.md` | completed |
| iot_metrics | clickhouse | batch | passed | 49.73 | `experiments/reports/clickhouse_iot_metrics.md` | completed |
| iot_metrics | dolphindb | batch | passed | 112.76 | `experiments/reports/dolphindb_iot_metrics.md` | completed |

## Next Actions

- Run `db-lab doctor --report experiments/reports/doctor.md` before service-backed experiments.
- Run `db-lab capabilities` to review coverage across DuckDB, ClickHouse, and DolphinDB.
- Run `db-lab journal` after the suite to turn results into a learning record.
- `passed` means the experiment completed and produced its report.
- `needs_service` means a service-backed engine is not reachable.
- Start services with `docker compose up -d clickhouse dolphindb`, then rerun the suite.
- Use the per-experiment report and query comparison to record what each engine makes easy or difficult.

market_ticks / duckdb / batch detail report

# DuckDB Market Ticks Experiment

Input CSV: `data/generated/market_ticks.csv`

## 00_profile.sql

Elapsed: 64.68 ms

| row_count | symbol_count | start_ts | end_ts | total_volume |
| --- | --- | --- | --- | --- |
| 1000 | 6 | 2026-01-02 09:30:00 | 2026-01-02 09:34:09.750000 | 4523300 |

## 01_symbol_vwap.sql

Elapsed: 64.35 ms

| symbol | exchange | trades | volume | vwap | low_price | high_price |
| --- | --- | --- | --- | --- | --- | --- |
| 0700.HK | HK | 177 | 914500 | 238.6874 | 233.5715 | 244.0305 |
| AAPL.US | US | 188 | 830800 | 289.6244 | 278.7564 | 300.3561 |
| 300750.SZ | SZ | 161 | 749200 | 65.8743 | 62.9452 | 68.5508 |
| 000001.SZ | SZ | 150 | 731200 | 12.7362 | 11.694 | 13.481 |
| 600000.SH | SH | 153 | 661800 | 147.5396 | 139.6432 | 153.26 |
| MSFT.US | US | 171 | 635800 | 263.1644 | 251.6349 | 274.7825 |

## 02_minute_bars.sql

Elapsed: 76.97 ms

| minute | symbol | open | high | low | close | volume |
| --- | --- | --- | --- | --- | --- | --- |
| 2026-01-02 09:30:00 | 000001.SZ | 13.3123 | 13.481 | 12.9775 | 13.1889 | 146400 |
| 2026-01-02 09:30:00 | 0700.HK | 239.318 | 244.0305 | 235.0882 | 236.375 | 218400 |
| 2026-01-02 09:30:00 | 300750.SZ | 66.3997 | 67.6626 | 66.3997 | 66.9395 | 139900 |
| 2026-01-02 09:30:00 | 600000.SH | 143.0596 | 143.7505 | 139.6432 | 139.6432 | 149100 |
| 2026-01-02 09:30:00 | AAPL.US | 281.9358 | 287.8035 | 281.4505 | 284.2867 | 180200 |
| 2026-01-02 09:30:00 | MSFT.US | 265.2417 | 274.5013 | 262.9045 | 274.3739 | 167500 |
| 2026-01-02 09:31:00 | 000001.SZ | 13.2044 | 13.2058 | 12.7296 | 12.8657 | 235100 |
| 2026-01-02 09:31:00 | 0700.HK | 237.2043 | 241.6359 | 236.5617 | 241.6359 | 209100 |
| 2026-01-02 09:31:00 | 300750.SZ | 67.0691 | 68.5508 | 66.8335 | 67.7026 | 217500 |
| 2026-01-02 09:31:00 | 600000.SH | 139.7934 | 148.5607 | 139.7934 | 148.5607 | 117500 |
| 2026-01-02 09:31:00 | AAPL.US | 284.1489 | 289.5647 | 278.7564 | 278.8568 | 159900 |
| 2026-01-02 09:31:00 | MSFT.US | 273.2249 | 274.7825 | 262.061 | 263.5377 | 173600 |
| 2026-01-02 09:32:00 | 000001.SZ | 12.8578 | 12.8578 | 12.4642 | 12.4694 | 212000 |
| 2026-01-02 09:32:00 | 0700.HK | 241.127 | 242.7481 | 239.0562 | 239.6684 | 171800 |
| 2026-01-02 09:32:00 | 300750.SZ | 67.7737 | 68.0648 | 64.2576 | 64.2576 | 206500 |
| 2026-01-02 09:32:00 | 600000.SH | 149.6306 | 153.26 | 149.2722 | 150.7427 | 191400 |
| 2026-01-02 09:32:00 | AAPL.US | 280.2127 | 296.7421 | 280.0694 | 295.8724 | 217400 |
| 2026-01-02 09:32:00 | MSFT.US | 263.1981 | 263.6461 | 253.3627 | 258.441 | 139500 |
| 2026-01-02 09:33:00 | 000001.SZ | 12.4321 | 12.4321 | 11.7035 | 11.7045 | 134500 |
| 2026-01-02 09:33:00 | 0700.HK | 239.9924 | 241.1034 | 233.9214 | 233.9214 | 246600 |
| 2026-01-02 09:33:00 | 300750.SZ | 64.343 | 64.343 | 62.9452 | 63.5812 | 132100 |
| 2026-01-02 09:33:00 | 600000.SH | 150.5008 | 150.7768 | 146.8496 | 150.7768 | 194500 |
| 2026-01-02 09:33:00 | AAPL.US | 295.174 | 300.0819 | 294.4277 | 300.0819 | 225300 |
| 2026-01-02 09:33:00 | MSFT.US | 257.3314 | 259.2117 | 251.6349 | 251.6349 | 146800 |

market_ticks / duckdb / storage detail report

# DuckDB CSV vs Parquet Experiment

Input CSV: `data/generated/market_ticks.csv`
Generated Parquet: `data/parquet/market_ticks.parquet`
Timing repetitions: 5

## Storage

| Format | Bytes | Size MiB |
| --- | ---: | ---: |
| CSV | 70612 | 0.0673 |
| Parquet (ZSTD) | 12388 | 0.0118 |

CSV / Parquet size ratio: 5.70x

## Query Timing

| Query | CSV median ms | Parquet median ms | Result match |
| --- | ---: | ---: | --- |
| full_profile | 63.590 | 1.644 | yes |
| filtered_vwap | 61.116 | 1.272 | yes |
| projected_scan | 60.695 | 1.190 | yes |

## Interpretation

- CSV is portable and easy to inspect, but types must be inferred or cast on every scan.
- Parquet stores typed columns and supports column pruning and predicate pushdown.
- Small files are dominated by startup and filesystem cache effects; increase row count before drawing performance conclusions.

market_ticks / duckdb / plans detail report

# DuckDB Query Plans Experiment

Input CSV: `data/generated/market_ticks.csv`
Generated Parquet: `data/parquet/market_ticks.parquet`
Plan mode: EXPLAIN

## What To Look For

- Scan operator: CSV plans should show file scanning through CSV readers; Parquet plans should show Parquet scans.
- Filters: check where `symbol = 'AAPL.US'` and `volume >= 10000` appear.
- Projection: check whether the plan needs every column or only the selected columns.
- Order and limit: check how `ORDER BY ts LIMIT 100` changes the physical plan.

## csv_filtered_vwap

SQL:

```sql
SELECT
            count(*) AS trades,
            round(sum(price * volume) / sum(volume), 4) AS vwap,
            sum(volume) AS volume
        FROM (
        SELECT
            CAST(ts AS TIMESTAMP) AS ts,
            trade_id,
            symbol,
            exchange,
            CAST(price AS DOUBLE) AS price,
            CAST(volume AS BIGINT) AS volume,
            side
        FROM read_csv_auto('data/generated/market_ticks.csv', header=true)
    )
        WHERE symbol = 'AAPL.US'
```

Plan key: `physical_plan`

```text
┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│           trades          │
│            vwap           │
│           volume          │
│                           │
│           ~1 row          │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│    UNGROUPED_AGGREGATE    │
│    ────────────────────   │
│        Aggregates:        │
│        count_star()       │
│          sum(#0)          │
│          sum(#1)          │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         PROJECTION        │
│    ────────────────────   │
│  (price * CAST(volume AS  │
│          DOUBLE))         │
│           volume          │
│                           │
│         ~403 rows         │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             #1            │
│             #2            │
│                           │
│         ~403 rows         │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│           FILTER          │
│    ────────────────────   │
│    (symbol = 'AAPL.US')   │
│                           │
│         ~403 rows         │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│       READ_CSV_AUTO       │
│    ────────────────────   │
│         Function:         │
│       READ_CSV_AUTO       │
│                           │
│        Projections:       │
│           symbol          │
│           price           │
│           volume          │
│                           │
│        ~2,017 rows        │
└───────────────────────────┘
```

## parquet_filtered_vwap

SQL:

```sql
SELECT
            count(*) AS trades,
            round(sum(price * volume) / sum(volume), 4) AS vwap,
            sum(volume) AS volume
        FROM read_parquet('data/parquet/market_ticks.parquet')
        WHERE symbol = 'AAPL.US'
```

Plan key: `physical_plan`

```text
┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│           trades          │
│            vwap           │
│           volume          │
│                           │
│           ~1 row          │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│    UNGROUPED_AGGREGATE    │
│    ────────────────────   │
│        Aggregates:        │
│        count_star()       │
│          sum(#0)          │
│          sum(#1)          │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         PROJECTION        │
│    ────────────────────   │
│  (price * CAST(volume AS  │
│          DOUBLE))         │
│           volume          │
│                           │
│         ~200 rows         │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│        READ_PARQUET       │
│    ────────────────────   │
│         Function:         │
│        READ_PARQUET       │
│                           │
│        Projections:       │
│           price           │
│           volume          │
│                           │
│          Filters:         │
│      symbol='AAPL.US'     │
│                           │
│         ~200 rows         │
└───────────────────────────┘
```

## csv_projected_scan

SQL:

```sql
SELECT symbol, price, volume
        FROM (
        SELECT
            CAST(ts AS TIMESTAMP) AS ts,
            trade_id,
            symbol,
            exchange,
            CAST(price AS DOUBLE) AS price,
            CAST(volume AS BIGINT) AS volume,
            side
        FROM read_csv_auto('data/generated/market_ticks.csv', header=true)
    )
        WHERE volume >= 10000
        ORDER BY ts
        LIMIT 100
```

Plan key: `physical_plan`

```text
┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             #0            │
│             #1            │
│             #2            │
│                           │
│         ~100 rows         │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│           TOP_N           │
│    ────────────────────   │
│          Top: 100         │
│      Order By: #3 ASC     │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         PROJECTION        │
│    ────────────────────   │
│           symbol          │
│           price           │
│           volume          │
│             ts            │
│                           │
│         ~403 rows         │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│           FILTER          │
│    ────────────────────   │
│     (volume >= 10000)     │
│                           │
│         ~403 rows         │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│       READ_CSV_AUTO       │
│    ────────────────────   │
│         Function:         │
│       READ_CSV_AUTO       │
│                           │
│        Projections:       │
│             ts            │
│           symbol          │
│           price           │
│           volume          │
│                           │
│        ~2,017 rows        │
└───────────────────────────┘
```

## parquet_projected_scan

SQL:

```sql
SELECT symbol, price, volume
        FROM read_parquet('data/parquet/market_ticks.parquet')
        WHERE volume >= 10000
        ORDER BY ts
        LIMIT 100
```

Plan key: `physical_plan`

```text
┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             #0            │
│             #1            │
│             #2            │
│                           │
│         ~100 rows         │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│           TOP_N           │
│    ────────────────────   │
│          Top: 100         │
│      Order By: #3 ASC     │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         PROJECTION        │
│    ────────────────────   │
│           symbol          │
│           price           │
│           volume          │
│             ts            │
│                           │
│         ~200 rows         │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│        READ_PARQUET       │
│    ────────────────────   │
│         Function:         │
│        READ_PARQUET       │
│                           │
│        Projections:       │
│           volume          │
│           symbol          │
│           price           │
│             ts            │
│                           │
│          Filters:         │
│       volume>=10000       │
│ optional: Dynamic Filter  │
│            (ts)           │
│                           │
│         ~200 rows         │
└───────────────────────────┘
```

## Interpretation

- Query plans are not performance results; use them to explain what work DuckDB intends to do.
- Compare this report with `duckdb_storage_formats.md` to connect physical work with observed timings.
- Rerun with `--analyze` when you want actual operator timings, not only the planned operators.

market_ticks / duckdb / scale detail report

# DuckDB Scale Experiment

Timing repetitions per query: 3

## Storage Growth

| Rows | CSV bytes | Parquet bytes | CSV / Parquet ratio | CSV path | Parquet path |
| ---: | ---: | ---: | ---: | --- | --- |
| 1000 | 70612 | 12388 | 5.70x | `data/generated/duckdb_scale/market_ticks_1000.csv` | `data/parquet/duckdb_scale/market_ticks_1000.parquet` |
| 5000 | 352893 | 48265 | 7.31x | `data/generated/duckdb_scale/market_ticks_5000.csv` | `data/parquet/duckdb_scale/market_ticks_5000.parquet` |

## Query Timing

| Rows | Query | CSV median ms | Parquet median ms | Result match |
| ---: | --- | ---: | ---: | --- |
| 1000 | full_profile | 64.135 | 1.818 | yes |
| 1000 | filtered_vwap | 62.043 | 1.799 | yes |
| 1000 | projected_scan | 66.725 | 1.341 | yes |
| 5000 | full_profile | 32.318 | 1.950 | yes |
| 5000 | filtered_vwap | 32.434 | 1.330 | yes |
| 5000 | projected_scan | 31.557 | 1.333 | yes |

## Interpretation

- Use this report to observe scaling direction, not to claim universal benchmark numbers.
- Small row counts are dominated by startup, parsing, and filesystem cache effects.
- Increase `--rows` and `--repetitions` when you want a stronger local signal.
- Compare this with `duckdb_query_plans.md` to connect timings with planned scan work.

market_ticks / clickhouse / batch detail report

# ClickHouse Market Ticks Experiment

Input CSV: `data/generated/market_ticks.csv`
ClickHouse URL: `http://localhost:8123`

## 00_profile.sql

Elapsed: 4.36 ms

| row_count | symbol_count | start_ts | end_ts | total_volume |
| --- | --- | --- | --- | --- |
| 1000 | 6 | 2026-01-02 09:30:00.000 | 2026-01-02 09:34:09.750 | 4523300 |

## 01_symbol_vwap.sql

Elapsed: 4.59 ms

| symbol | exchange | trades | total_volume | vwap | low_price | high_price |
| --- | --- | --- | --- | --- | --- | --- |
| 0700.HK | HK | 177 | 914500 | 238.6874 | 233.5715 | 244.0305 |
| AAPL.US | US | 188 | 830800 | 289.6244 | 278.7564 | 300.3561 |
| 300750.SZ | SZ | 161 | 749200 | 65.8743 | 62.9452 | 68.5508 |
| 000001.SZ | SZ | 150 | 731200 | 12.7362 | 11.694 | 13.481 |
| 600000.SH | SH | 153 | 661800 | 147.5396 | 139.6432 | 153.26 |
| MSFT.US | US | 171 | 635800 | 263.1644 | 251.6349 | 274.7825 |

## 02_minute_bars.sql

Elapsed: 4.79 ms

| minute | symbol | open | high | low | close | volume |
| --- | --- | --- | --- | --- | --- | --- |
| 2026-01-02 09:30:00 | 000001.SZ | 13.3123 | 13.481 | 12.9775 | 13.1889 | 146400 |
| 2026-01-02 09:30:00 | 0700.HK | 239.318 | 244.0305 | 235.0882 | 236.375 | 218400 |
| 2026-01-02 09:30:00 | 300750.SZ | 66.3997 | 67.6626 | 66.3997 | 66.9395 | 139900 |
| 2026-01-02 09:30:00 | 600000.SH | 143.0596 | 143.7505 | 139.6432 | 139.6432 | 149100 |
| 2026-01-02 09:30:00 | AAPL.US | 281.9358 | 287.8035 | 281.4505 | 284.2867 | 180200 |
| 2026-01-02 09:30:00 | MSFT.US | 265.2417 | 274.5013 | 262.9045 | 274.3739 | 167500 |
| 2026-01-02 09:31:00 | 000001.SZ | 13.2044 | 13.2058 | 12.7296 | 12.8657 | 235100 |
| 2026-01-02 09:31:00 | 0700.HK | 237.2043 | 241.6359 | 236.5617 | 241.6359 | 209100 |
| 2026-01-02 09:31:00 | 300750.SZ | 67.0691 | 68.5508 | 66.8335 | 67.7026 | 217500 |
| 2026-01-02 09:31:00 | 600000.SH | 139.7934 | 148.5607 | 139.7934 | 148.5607 | 117500 |
| 2026-01-02 09:31:00 | AAPL.US | 284.1489 | 289.5647 | 278.7564 | 278.8568 | 159900 |
| 2026-01-02 09:31:00 | MSFT.US | 273.2249 | 274.7825 | 262.061 | 263.5377 | 173600 |
| 2026-01-02 09:32:00 | 000001.SZ | 12.8578 | 12.8578 | 12.4642 | 12.4694 | 212000 |
| 2026-01-02 09:32:00 | 0700.HK | 241.127 | 242.7481 | 239.0562 | 239.6684 | 171800 |
| 2026-01-02 09:32:00 | 300750.SZ | 67.7737 | 68.0648 | 64.2576 | 64.2576 | 206500 |
| 2026-01-02 09:32:00 | 600000.SH | 149.6306 | 153.26 | 149.2722 | 150.7427 | 191400 |
| 2026-01-02 09:32:00 | AAPL.US | 280.2127 | 296.7421 | 280.0694 | 295.8724 | 217400 |
| 2026-01-02 09:32:00 | MSFT.US | 263.1981 | 263.6461 | 253.3627 | 258.441 | 139500 |
| 2026-01-02 09:33:00 | 000001.SZ | 12.4321 | 12.4321 | 11.7035 | 11.7045 | 134500 |
| 2026-01-02 09:33:00 | 0700.HK | 239.9924 | 241.1034 | 233.9214 | 233.9214 | 246600 |
| 2026-01-02 09:33:00 | 300750.SZ | 64.343 | 64.343 | 62.9452 | 63.5812 | 132100 |
| 2026-01-02 09:33:00 | 600000.SH | 150.5008 | 150.7768 | 146.8496 | 150.7768 | 194500 |
| 2026-01-02 09:33:00 | AAPL.US | 295.174 | 300.0819 | 294.4277 | 300.0819 | 225300 |
| 2026-01-02 09:33:00 | MSFT.US | 257.3314 | 259.2117 | 251.6349 | 251.6349 | 146800 |

market_ticks / dolphindb / batch detail report

# DolphinDB Market Ticks Experiment

Input CSV: `data/generated/market_ticks.csv`
Server CSV path: `/db_lab/data/generated/market_ticks.csv`
DolphinDB URL: `http://localhost:8848`

## 00_profile.dos

Elapsed: 7.24 ms

| row_count | symbol_count | start_ts | end_ts | total_volume |
| --- | --- | --- | --- | --- |
| 1000 | 6 | 2026.01.02 09:30:00.000 | 2026.01.02 09:34:09.750 | 4523300 |

## 01_symbol_vwap.dos

Elapsed: 3.29 ms

| sym | exchange | trades | volume | vwap | low_price | high_price |
| --- | --- | --- | --- | --- | --- | --- |
| 0700.HK | HK | 177 | 914500 | 238.6874 | 233.5715 | 244.0305 |
| AAPL.US | US | 188 | 830800 | 289.6244 | 278.7564 | 300.3561 |
| 300750.SZ | SZ | 161 | 749200 | 65.8743 | 62.9452 | 68.5508 |
| 000001.SZ | SZ | 150 | 731200 | 12.7362 | 11.694 | 13.481 |
| 600000.SH | SH | 153 | 661800 | 147.5396 | 139.6432 | 153.26 |
| MSFT.US | US | 171 | 635800 | 263.1644 | 251.6349 | 274.7825 |

## 02_minute_bars.dos

Elapsed: 2.99 ms

| minute | sym | open | high | low | close | volume |
| --- | --- | --- | --- | --- | --- | --- |
| 2026.01.02 09:30:00.000 | 000001.SZ | 13.3123 | 13.481 | 12.9775 | 13.1889 | 146400 |
| 2026.01.02 09:30:00.000 | 0700.HK | 239.318 | 244.0305 | 235.0882 | 236.375 | 218400 |
| 2026.01.02 09:30:00.000 | 300750.SZ | 66.3997 | 67.6626 | 66.3997 | 66.9395 | 139900 |
| 2026.01.02 09:30:00.000 | 600000.SH | 143.0596 | 143.7505 | 139.6432 | 139.6432 | 149100 |
| 2026.01.02 09:30:00.000 | AAPL.US | 281.9358 | 287.8035 | 281.4505 | 284.2867 | 180200 |
| 2026.01.02 09:30:00.000 | MSFT.US | 265.2417 | 274.5013 | 262.9045 | 274.3739 | 167500 |
| 2026.01.02 09:31:00.000 | 000001.SZ | 13.2044 | 13.205799999999998 | 12.7296 | 12.8657 | 235100 |
| 2026.01.02 09:31:00.000 | 0700.HK | 237.2043 | 241.6359 | 236.5617 | 241.6359 | 209100 |
| 2026.01.02 09:31:00.000 | 300750.SZ | 67.0691 | 68.5508 | 66.8335 | 67.7026 | 217500 |
| 2026.01.02 09:31:00.000 | 600000.SH | 139.7934 | 148.5607 | 139.7934 | 148.5607 | 117500 |
| 2026.01.02 09:31:00.000 | AAPL.US | 284.1489 | 289.5647 | 278.7564 | 278.8568 | 159900 |
| 2026.01.02 09:31:00.000 | MSFT.US | 273.2249 | 274.7825 | 262.061 | 263.5377 | 173600 |
| 2026.01.02 09:32:00.000 | 000001.SZ | 12.8578 | 12.8578 | 12.464199999999998 | 12.4694 | 212000 |
| 2026.01.02 09:32:00.000 | 0700.HK | 241.127 | 242.7481 | 239.0562 | 239.6684 | 171800 |
| 2026.01.02 09:32:00.000 | 300750.SZ | 67.7737 | 68.0648 | 64.2576 | 64.2576 | 206500 |
| 2026.01.02 09:32:00.000 | 600000.SH | 149.6306 | 153.26 | 149.2722 | 150.7427 | 191400 |
| 2026.01.02 09:32:00.000 | AAPL.US | 280.2127 | 296.7421 | 280.0694 | 295.8724 | 217400 |
| 2026.01.02 09:32:00.000 | MSFT.US | 263.1981 | 263.6461 | 253.3627 | 258.441 | 139500 |
| 2026.01.02 09:33:00.000 | 000001.SZ | 12.4321 | 12.4321 | 11.7035 | 11.7045 | 134500 |
| 2026.01.02 09:33:00.000 | 0700.HK | 239.9924 | 241.1034 | 233.9214 | 233.9214 | 246600 |
| 2026.01.02 09:33:00.000 | 300750.SZ | 64.343 | 64.343 | 62.9452 | 63.5812 | 132100 |
| 2026.01.02 09:33:00.000 | 600000.SH | 150.5008 | 150.7768 | 146.8496 | 150.7768 | 194500 |
| 2026.01.02 09:33:00.000 | AAPL.US | 295.174 | 300.0819 | 294.4277 | 300.0819 | 225300 |
| 2026.01.02 09:33:00.000 | MSFT.US | 257.3314 | 259.2117 | 251.6349 | 251.6349 | 146800 |

market_ticks / dolphindb / stream detail report

# DolphinDB Market Stream Experiment

Input CSV: `data/generated/market_ticks.csv`
Server CSV path: `/db_lab/data/generated/market_ticks.csv`
DolphinDB URL: `http://localhost:8848`
Replay rate: maximum speed

## replay_csv.dos

Elapsed: 1004.82 ms

| published_ticks | generated_bars |
| --- | --- |
| 1000 | 24 |

## 00_stream_status.dos

Elapsed: 1.93 ms

| published_ticks | symbols | generated_bars | start_ts | end_ts |
| --- | --- | --- | --- | --- |
| 1000 | 6 | 24 | 2026.01.02 09:30:00.000 | 2026.01.02 09:34:09.750 |

## 01_realtime_minute_bars.dos

Elapsed: 2.26 ms

| minute | sym | open | high | low | close | volume | vwap |
| --- | --- | --- | --- | --- | --- | --- | --- |
| 2026.01.02 09:30:00.000 | 000001.SZ | 13.3123 | 13.481 | 12.9775 | 13.1889 | 146400 | 13.1481 |
| 2026.01.02 09:30:00.000 | 0700.HK | 239.318 | 244.0305 | 235.0882 | 236.375 | 218400 | 240.0157 |
| 2026.01.02 09:30:00.000 | 300750.SZ | 66.3997 | 67.6626 | 66.3997 | 66.9395 | 139900 | 67.0378 |
| 2026.01.02 09:30:00.000 | 600000.SH | 143.0596 | 143.7505 | 139.6432 | 139.6432 | 149100 | 141.9595 |
| 2026.01.02 09:30:00.000 | AAPL.US | 281.9358 | 287.8035 | 281.4505 | 284.2867 | 180200 | 284.2992 |
| 2026.01.02 09:30:00.000 | MSFT.US | 265.2417 | 274.5013 | 262.9045 | 274.3739 | 167500 | 269.4541 |
| 2026.01.02 09:31:00.000 | 000001.SZ | 13.2044 | 13.205799999999998 | 12.7296 | 12.8657 | 235100 | 12.9102 |
| 2026.01.02 09:31:00.000 | 0700.HK | 237.2043 | 241.6359 | 236.5617 | 241.6359 | 209100 | 238.1023 |
| 2026.01.02 09:31:00.000 | 300750.SZ | 67.0691 | 68.5508 | 66.8335 | 67.7026 | 217500 | 67.4367 |
| 2026.01.02 09:31:00.000 | 600000.SH | 139.7934 | 148.5607 | 139.7934 | 148.5607 | 117500 | 144.8845 |
| 2026.01.02 09:31:00.000 | AAPL.US | 284.1489 | 289.5647 | 278.7564 | 278.8568 | 159900 | 285.3701 |
| 2026.01.02 09:31:00.000 | MSFT.US | 273.2249 | 274.7825 | 262.061 | 263.5377 | 173600 | 266.833 |
| 2026.01.02 09:32:00.000 | 000001.SZ | 12.8578 | 12.8578 | 12.464199999999998 | 12.4694 | 212000 | 12.7038 |
| 2026.01.02 09:32:00.000 | 0700.HK | 241.127 | 242.7481 | 239.0562 | 239.6684 | 171800 | 241.1513 |
| 2026.01.02 09:32:00.000 | 300750.SZ | 67.7737 | 68.0648 | 64.2576 | 64.2576 | 206500 | 65.4464 |
| 2026.01.02 09:32:00.000 | 600000.SH | 149.6306 | 153.26 | 149.2722 | 150.7427 | 191400 | 151.6906 |
| 2026.01.02 09:32:00.000 | AAPL.US | 280.2127 | 296.7421 | 280.0694 | 295.8724 | 217400 | 287.9444 |
| 2026.01.02 09:32:00.000 | MSFT.US | 263.1981 | 263.6461 | 253.3627 | 258.441 | 139500 | 259.1553 |
| 2026.01.02 09:33:00.000 | 000001.SZ | 12.4321 | 12.4321 | 11.7035 | 11.7045 | 134500 | 12.0596 |
| 2026.01.02 09:33:00.000 | 0700.HK | 239.9924 | 241.1034 | 233.9214 | 233.9214 | 246600 | 237.6688 |
| 2026.01.02 09:33:00.000 | 300750.SZ | 64.343 | 64.343 | 62.9452 | 63.5812 | 132100 | 63.7088 |
| 2026.01.02 09:33:00.000 | 600000.SH | 150.5008 | 150.7768 | 146.8496 | 150.7768 | 194500 | 149.2058 |
| 2026.01.02 09:33:00.000 | AAPL.US | 295.174 | 300.0819 | 294.4277 | 300.0819 | 225300 | 296.3176 |
| 2026.01.02 09:33:00.000 | MSFT.US | 257.3314 | 259.2117 | 251.6349 | 251.6349 | 146800 | 256.0624 |

ecommerce_events / duckdb / batch detail report

# DuckDB Ecommerce Events Experiment

Input CSV: `data/generated/ecommerce_events.csv`

## 00_profile.sql

Elapsed: 72.06 ms

| event_count | user_count | session_count | start_ts | end_ts | gross_revenue |
| --- | --- | --- | --- | --- | --- |
| 1000 | 97 | 416 | 2026-01-02 00:00:00 | 2026-01-02 00:49:57 | 24649.0 |

## 01_funnel.sql

Elapsed: 74.23 ms

| step_no | event_name | events | sessions | users | session_rate_pct |
| --- | --- | --- | --- | --- | --- |
| 1 | page_view | 416 | 416 | 97 | 100.0 |
| 2 | view_product | 275 | 275 | 94 | 66.11 |
| 3 | add_cart | 174 | 174 | 84 | 41.83 |
| 4 | checkout | 91 | 91 | 65 | 21.88 |
| 5 | purchase | 44 | 44 | 39 | 10.58 |

## 02_revenue_segments.sql

Elapsed: 72.78 ms

| country | channel | users | purchases | revenue | avg_order_value |
| --- | --- | --- | --- | --- | --- |
| CN | search | 5 | 4 | 6194.0 | 1548.5 |
| SG | search | 6 | 4 | 2406.0 | 601.5 |
| DE | organic | 3 | 4 | 2296.0 | 574.0 |
| DE | ads | 4 | 3 | 2247.0 | 749.0 |
| SG | ads | 5 | 5 | 1884.0 | 376.8 |
| JP | organic | 11 | 5 | 1463.0 | 292.6 |
| US | social | 4 | 2 | 1298.0 | 649.0 |
| BR | social | 4 | 2 | 998.0 | 499.0 |
| US | organic | 3 | 1 | 899.0 | 899.0 |
| CN | organic | 2 | 2 | 848.0 | 424.0 |
| BR | organic | 4 | 2 | 747.0 | 373.5 |
| JP | email | 3 | 1 | 649.0 | 649.0 |
| SG | organic | 3 | 1 | 598.0 | 598.0 |
| SG | email | 4 | 1 | 349.0 | 349.0 |
| BR | ads | 1 | 1 | 349.0 | 349.0 |
| JP | search | 6 | 1 | 299.0 | 299.0 |
| CN | social | 3 | 1 | 299.0 | 299.0 |
| US | ads | 5 | 1 | 299.0 | 299.0 |
| JP | social | 2 | 1 | 199.0 | 199.0 |
| DE | social | 4 | 1 | 199.0 | 199.0 |

## 03_top_products.sql

Elapsed: 73.36 ms

| sku_id | category | brand | units | revenue | buyers |
| --- | --- | --- | --- | --- | --- |
| SKU-1002 | phone | Northstar | 8 | 7192.0 | 6 |
| SKU-1001 | laptop | Northstar | 4 | 5196.0 | 4 |
| SKU-1004 | camera | Aster | 7 | 4543.0 | 7 |
| SKU-1007 | watch | Helio | 10 | 2990.0 | 8 |
| SKU-1006 | monitor | Orbit | 6 | 2094.0 | 6 |
| SKU-1003 | headphones | Aster | 6 | 1194.0 | 5 |
| SKU-1008 | speaker | Helio | 5 | 795.0 | 4 |
| SKU-1005 | keyboard | Orbit | 5 | 645.0 | 4 |

ecommerce_events / clickhouse / materialized detail report

# ClickHouse Ecommerce Events Experiment

Input CSV: `data/generated/ecommerce_events.csv`
ClickHouse URL: `http://localhost:8123`

## 00_profile.sql

Elapsed: 4.92 ms

| event_count | user_count | session_count | start_ts | end_ts | gross_revenue |
| --- | --- | --- | --- | --- | --- |
| 1000 | 97 | 416 | 2026-01-02 00:00:00 | 2026-01-02 00:49:57 | 24649 |

## 01_funnel.sql

Elapsed: 7.23 ms

| step_no | event_name | events | sessions | users | session_rate_pct |
| --- | --- | --- | --- | --- | --- |
| 1 | page_view | 416 | 416 | 97 | 100 |
| 2 | view_product | 275 | 275 | 94 | 66.11 |
| 3 | add_cart | 174 | 174 | 84 | 41.83 |
| 4 | checkout | 91 | 91 | 65 | 21.88 |
| 5 | purchase | 44 | 44 | 39 | 10.58 |

## 02_revenue_segments.sql

Elapsed: 4.59 ms

| country | channel | users | purchases | revenue | avg_order_value |
| --- | --- | --- | --- | --- | --- |
| CN | search | 5 | 4 | 6194 | 1548.5 |
| SG | search | 6 | 4 | 2406 | 601.5 |
| DE | organic | 3 | 4 | 2296 | 574 |
| DE | ads | 4 | 3 | 2247 | 749 |
| SG | ads | 5 | 5 | 1884 | 376.8 |
| JP | organic | 11 | 5 | 1463 | 292.6 |
| US | social | 4 | 2 | 1298 | 649 |
| BR | social | 4 | 2 | 998 | 499 |
| US | organic | 3 | 1 | 899 | 899 |
| CN | organic | 2 | 2 | 848 | 424 |
| BR | organic | 4 | 2 | 747 | 373.5 |
| JP | email | 3 | 1 | 649 | 649 |
| SG | organic | 3 | 1 | 598 | 598 |
| SG | email | 4 | 1 | 349 | 349 |
| BR | ads | 1 | 1 | 349 | 349 |
| US | ads | 5 | 1 | 299 | 299 |
| CN | social | 3 | 1 | 299 | 299 |
| JP | search | 6 | 1 | 299 | 299 |
| JP | social | 2 | 1 | 199 | 199 |
| DE | social | 4 | 1 | 199 | 199 |

## 03_top_products.sql

Elapsed: 4.59 ms

| sku_id | category | brand | units | revenue | buyers |
| --- | --- | --- | --- | --- | --- |
| SKU-1002 | phone | Northstar | 8 | 7192 | 6 |
| SKU-1001 | laptop | Northstar | 4 | 5196 | 4 |
| SKU-1004 | camera | Aster | 7 | 4543 | 7 |
| SKU-1007 | watch | Helio | 10 | 2990 | 8 |
| SKU-1006 | monitor | Orbit | 6 | 2094 | 6 |
| SKU-1003 | headphones | Aster | 6 | 1194 | 5 |
| SKU-1008 | speaker | Helio | 5 | 795 | 4 |
| SKU-1005 | keyboard | Orbit | 5 | 645 | 4 |

## 04_materialized_revenue.sql

Elapsed: 3.80 ms

| minute | country | channel | purchases | revenue |
| --- | --- | --- | --- | --- |
| 2026-01-02 00:00:00 | SG | ads | 1 | 318 |
| 2026-01-02 00:00:00 | JP | organic | 1 | 258 |
| 2026-01-02 00:02:00 | CN | organic | 1 | 199 |
| 2026-01-02 00:05:00 | DE | ads | 1 | 1299 |
| 2026-01-02 00:05:00 | US | social | 1 | 649 |
| 2026-01-02 00:06:00 | US | ads | 1 | 299 |
| 2026-01-02 00:09:00 | DE | organic | 1 | 299 |
| 2026-01-02 00:10:00 | CN | search | 1 | 1299 |
| 2026-01-02 00:10:00 | CN | social | 1 | 299 |
| 2026-01-02 00:11:00 | BR | social | 1 | 349 |
| 2026-01-02 00:12:00 | JP | organic | 1 | 129 |
| 2026-01-02 00:14:00 | SG | ads | 1 | 899 |
| 2026-01-02 00:16:00 | SG | ads | 1 | 159 |
| 2026-01-02 00:18:00 | JP | search | 1 | 299 |
| 2026-01-02 00:21:00 | SG | ads | 1 | 159 |
| 2026-01-02 00:22:00 | CN | search | 1 | 2697 |
| 2026-01-02 00:23:00 | SG | email | 1 | 349 |
| 2026-01-02 00:24:00 | DE | organic | 1 | 199 |
| 2026-01-02 00:25:00 | JP | organic | 1 | 129 |
| 2026-01-02 00:27:00 | CN | organic | 1 | 649 |
| 2026-01-02 00:27:00 | BR | ads | 1 | 349 |
| 2026-01-02 00:28:00 | BR | social | 1 | 649 |
| 2026-01-02 00:29:00 | DE | organic | 1 | 899 |
| 2026-01-02 00:30:00 | DE | organic | 1 | 899 |
| 2026-01-02 00:30:00 | SG | ads | 1 | 349 |
| 2026-01-02 00:30:00 | DE | ads | 1 | 299 |
| 2026-01-02 00:31:00 | SG | search | 1 | 649 |
| 2026-01-02 00:32:00 | SG | search | 1 | 159 |
| 2026-01-02 00:33:00 | JP | organic | 1 | 598 |
| 2026-01-02 00:33:00 | BR | organic | 1 | 349 |

iot_metrics / duckdb / batch detail report

# DuckDB IoT Metrics Experiment

Metrics CSV: `data/generated/iot_metrics.csv`
Alerts CSV: `data/generated/iot_alerts.csv`

## 00_profile.sql

Elapsed: 362.82 ms

| metric_count | device_count | alert_count | start_ts | end_ts | avg_value |
| --- | --- | --- | --- | --- | --- |
| 2000 | 100 | 12 | 2026-01-02 00:00:00 | 2026-01-02 02:46:35 | 49.1586 |

## 01_metric_windows.sql

Elapsed: 120.98 ms

| minute | region | metric_name | samples | avg_value | max_value |
| --- | --- | --- | --- | --- | --- |
| 2026-01-02 00:00:00 | ap-southeast | cpu_usage | 1 | 46.5074 | 46.5074 |
| 2026-01-02 00:00:00 | ap-southeast | memory_usage | 1 | 56.222 | 56.222 |
| 2026-01-02 00:00:00 | ap-southeast | network_latency | 1 | 50.6178 | 50.6178 |
| 2026-01-02 00:00:00 | eu-central | cpu_usage | 1 | 43.3631 | 43.3631 |
| 2026-01-02 00:00:00 | eu-central | temperature | 1 | 47.3134 | 47.3134 |
| 2026-01-02 00:00:00 | us-east | cpu_usage | 1 | 80.6596 | 80.6596 |
| 2026-01-02 00:00:00 | us-east | memory_usage | 1 | 56.5146 | 56.5146 |
| 2026-01-02 00:00:00 | us-east | network_latency | 2 | 49.5366 | 54.3717 |
| 2026-01-02 00:00:00 | us-west | memory_usage | 1 | 51.446 | 51.446 |
| 2026-01-02 00:00:00 | us-west | temperature | 2 | 46.9335 | 47.3137 |
| 2026-01-02 00:01:00 | ap-southeast | memory_usage | 1 | 60.6055 | 60.6055 |
| 2026-01-02 00:01:00 | ap-southeast | network_latency | 1 | 50.6713 | 50.6713 |
| 2026-01-02 00:01:00 | ap-southeast | temperature | 1 | 43.0074 | 43.0074 |
| 2026-01-02 00:01:00 | eu-central | cpu_usage | 1 | 40.4566 | 40.4566 |
| 2026-01-02 00:01:00 | eu-central | network_latency | 2 | 39.9204 | 41.3179 |
| 2026-01-02 00:01:00 | us-east | cpu_usage | 2 | 43.0121 | 46.3236 |
| 2026-01-02 00:01:00 | us-east | memory_usage | 1 | 51.5074 | 51.5074 |
| 2026-01-02 00:01:00 | us-west | memory_usage | 1 | 55.084 | 55.084 |

## 02_alert_join.sql

Elapsed: 121.54 ms

| severity | region | alerts | avg_metric_value |
| --- | --- | --- | --- |
| warning | ap-southeast | 3 | 69.5528 |
| warning | eu-central | 2 | 70.6982 |
| warning | us-east | 4 | 72.6894 |
| warning | us-west | 3 | 72.1952 |

## 03_device_health.sql

Elapsed: 121.88 ms

| device_id | region | samples | anomaly_points | max_temperature | max_cpu |
| --- | --- | --- | --- | --- | --- |
| D00001 | us-east | 20 | 1 | 0.0 | 80.6596 |
| D00011 | eu-central | 20 | 1 | 71.7211 | 0.0 |
| D00023 | us-west | 20 | 1 | 72.9476 | 0.0 |
| D00035 | eu-central | 20 | 1 | 69.6753 | 0.0 |
| D00047 | us-west | 20 | 1 | 71.8156 | 0.0 |
| D00059 | us-west | 20 | 1 | 71.8223 | 0.0 |
| D00071 | us-east | 20 | 1 | 73.4718 | 0.0 |
| D00075 | us-east | 20 | 1 | 68.4174 | 0.0 |
| D00083 | ap-southeast | 20 | 1 | 70.933 | 0.0 |
| D00087 | ap-southeast | 20 | 1 | 68.1647 | 0.0 |

iot_metrics / clickhouse / batch detail report

# ClickHouse IoT Metrics Experiment

Metrics CSV: `data/generated/iot_metrics.csv`
Alerts CSV: `data/generated/iot_alerts.csv`
ClickHouse URL: `http://localhost:8123`

## 00_profile.sql

Elapsed: 6.89 ms

| metric_count | device_count | alert_count | start_ts | end_ts | avg_value |
| --- | --- | --- | --- | --- | --- |
| 2000 | 100 | 12 | 2026-01-02 00:00:00.000 | 2026-01-02 02:46:35.000 | 49.1586 |

## 01_metric_windows.sql

Elapsed: 4.47 ms

| minute | region | metric_name | samples | avg_value | max_value |
| --- | --- | --- | --- | --- | --- |
| 2026-01-02 00:00:00 | ap-southeast | cpu_usage | 1 | 46.5074 | 46.5074 |
| 2026-01-02 00:00:00 | ap-southeast | memory_usage | 1 | 56.222 | 56.222 |
| 2026-01-02 00:00:00 | ap-southeast | network_latency | 1 | 50.6178 | 50.6178 |
| 2026-01-02 00:00:00 | eu-central | cpu_usage | 1 | 43.3631 | 43.3631 |
| 2026-01-02 00:00:00 | eu-central | temperature | 1 | 47.3134 | 47.3134 |
| 2026-01-02 00:00:00 | us-east | cpu_usage | 1 | 80.6596 | 80.6596 |
| 2026-01-02 00:00:00 | us-east | memory_usage | 1 | 56.5146 | 56.5146 |
| 2026-01-02 00:00:00 | us-east | network_latency | 2 | 49.5366 | 54.3717 |
| 2026-01-02 00:00:00 | us-west | memory_usage | 1 | 51.446 | 51.446 |
| 2026-01-02 00:00:00 | us-west | temperature | 2 | 46.9335 | 47.3137 |
| 2026-01-02 00:01:00 | ap-southeast | memory_usage | 1 | 60.6055 | 60.6055 |
| 2026-01-02 00:01:00 | ap-southeast | network_latency | 1 | 50.6713 | 50.6713 |
| 2026-01-02 00:01:00 | ap-southeast | temperature | 1 | 43.0074 | 43.0074 |
| 2026-01-02 00:01:00 | eu-central | cpu_usage | 1 | 40.4566 | 40.4566 |
| 2026-01-02 00:01:00 | eu-central | network_latency | 2 | 39.9204 | 41.3179 |
| 2026-01-02 00:01:00 | us-east | cpu_usage | 2 | 43.0121 | 46.3236 |
| 2026-01-02 00:01:00 | us-east | memory_usage | 1 | 51.5074 | 51.5074 |
| 2026-01-02 00:01:00 | us-west | memory_usage | 1 | 55.084 | 55.084 |

## 02_alert_join.sql

Elapsed: 6.49 ms

| severity | region | alerts | avg_metric_value |
| --- | --- | --- | --- |
| warning | ap-southeast | 3 | 69.5528 |
| warning | eu-central | 2 | 70.6982 |
| warning | us-east | 4 | 72.6894 |
| warning | us-west | 3 | 72.1952 |

## 03_device_health.sql

Elapsed: 5.69 ms

| device_id | region | samples | anomaly_points | max_temperature | max_cpu |
| --- | --- | --- | --- | --- | --- |
| D00001 | us-east | 20 | 1 | 0 | 80.6596 |
| D00011 | eu-central | 20 | 1 | 71.7211 | 0 |
| D00023 | us-west | 20 | 1 | 72.9476 | 0 |
| D00035 | eu-central | 20 | 1 | 69.6753 | 0 |
| D00047 | us-west | 20 | 1 | 71.8156 | 0 |
| D00059 | us-west | 20 | 1 | 71.8223 | 0 |
| D00071 | us-east | 20 | 1 | 73.4718 | 0 |
| D00075 | us-east | 20 | 1 | 68.4174 | 0 |
| D00083 | ap-southeast | 20 | 1 | 70.933 | 0 |
| D00087 | ap-southeast | 20 | 1 | 68.1647 | 0 |

iot_metrics / dolphindb / batch detail report

# DolphinDB IoT Metrics Experiment

Metrics CSV: `data/generated/iot_metrics.csv`
Alerts CSV: `data/generated/iot_alerts.csv`
Server metrics CSV path: `/db_lab/data/generated/iot_metrics.csv`
Server alerts CSV path: `/db_lab/data/generated/iot_alerts.csv`
DolphinDB URL: `http://localhost:8848`

## 00_profile.dos

Elapsed: 21.64 ms

| metric_count | device_count | alert_count | start_ts | end_ts | avg_value |
| --- | --- | --- | --- | --- | --- |
| 2000 | 100 | 12 | 2026.01.02 00:00:00.000 | 2026.01.02 02:46:35.000 | 49.1586 |

## 01_metric_windows.dos

Elapsed: 7.96 ms

| minute | region | metric_name | samples | avg_value | max_value |
| --- | --- | --- | --- | --- | --- |
| 2026.01.02 00:00:00.000 | ap-southeast | cpu_usage | 1 | 46.5074 | 46.5074 |
| 2026.01.02 00:00:00.000 | ap-southeast | memory_usage | 1 | 56.222 | 56.222 |
| 2026.01.02 00:00:00.000 | ap-southeast | network_latency | 1 | 50.6178 | 50.6178 |
| 2026.01.02 00:00:00.000 | eu-central | cpu_usage | 1 | 43.3631 | 43.3631 |
| 2026.01.02 00:00:00.000 | eu-central | temperature | 1 | 47.3134 | 47.3134 |
| 2026.01.02 00:00:00.000 | us-east | cpu_usage | 1 | 80.6596 | 80.6596 |
| 2026.01.02 00:00:00.000 | us-east | memory_usage | 1 | 56.5146 | 56.5146 |
| 2026.01.02 00:00:00.000 | us-east | network_latency | 2 | 49.5366 | 54.3717 |
| 2026.01.02 00:00:00.000 | us-west | memory_usage | 1 | 51.446 | 51.446 |
| 2026.01.02 00:00:00.000 | us-west | temperature | 2 | 46.9335 | 47.3137 |
| 2026.01.02 00:01:00.000 | ap-southeast | memory_usage | 1 | 60.6055 | 60.6055 |
| 2026.01.02 00:01:00.000 | ap-southeast | network_latency | 1 | 50.6713 | 50.6713 |
| 2026.01.02 00:01:00.000 | ap-southeast | temperature | 1 | 43.0074 | 43.0074 |
| 2026.01.02 00:01:00.000 | eu-central | cpu_usage | 1 | 40.4566 | 40.4566 |
| 2026.01.02 00:01:00.000 | eu-central | network_latency | 2 | 39.9204 | 41.3179 |
| 2026.01.02 00:01:00.000 | us-east | cpu_usage | 2 | 43.0121 | 46.3236 |
| 2026.01.02 00:01:00.000 | us-east | memory_usage | 1 | 51.5074 | 51.5074 |
| 2026.01.02 00:01:00.000 | us-west | memory_usage | 1 | 55.084 | 55.084 |

## 02_alert_join.dos

Elapsed: 9.73 ms

| severity | region | alerts | avg_metric_value |
| --- | --- | --- | --- |
| warning | ap-southeast | 3 | 69.5528 |
| warning | eu-central | 2 | 70.6982 |
| warning | us-east | 4 | 72.6894 |
| warning | us-west | 3 | 72.1952 |

## 03_device_health.dos

Elapsed: 9.44 ms

| device_id | region | samples | anomaly_points | max_temperature | max_cpu |
| --- | --- | --- | --- | --- | --- |
| D00001 | us-east | 20 | 1 | 0 | 80.6596 |
| D00011 | eu-central | 20 | 1 | 71.7211 | 0 |
| D00023 | us-west | 20 | 1 | 72.9476 | 0 |
| D00035 | eu-central | 20 | 1 | 69.6753 | 0 |
| D00047 | us-west | 20 | 1 | 71.8156 | 0 |
| D00059 | us-west | 20 | 1 | 71.8223 | 0 |
| D00071 | us-east | 20 | 1 | 73.4718 | 0 |
| D00075 | us-east | 20 | 1 | 68.4174 | 0 |
| D00083 | ap-southeast | 20 | 1 | 70.933 | 0 |
| D00087 | ap-southeast | 20 | 1 | 68.1647 | 0 |

experiments/reports/capability_matrix.md detail report

# DB Lab Capability Matrix

Suite report: `experiments/reports/lab_suite.md`

## Coverage Summary

| Engine | Capabilities | Passed | Needs service | Missing / planned |
| --- | ---: | ---: | ---: | ---: |
| duckdb | 7 | 7 | 0 | 0 |
| clickhouse | 4 | 4 | 0 | 0 |
| dolphindb | 3 | 3 | 0 | 0 |

## Capability Details

| Engine | Capability | Status | Study focus | Evidence | Missing evidence | Next action |
| --- | --- | --- | --- | --- | --- | --- |
| duckdb | Embedded file analytics | passed | Direct CSV scans, local OLAP, VWAP, and minute bars. | `sql/duckdb/market_ticks/00_profile.sql`, `sql/duckdb/market_ticks/01_symbol_vwap.sql`, `sql/duckdb/market_ticks/02_minute_bars.sql`, `experiments/reports/duckdb_market_ticks.md` |  | Change row count and compare query timing. |
| duckdb | Ecommerce funnel analytics | passed | Session funnels, revenue segmentation, and Top product queries. | `sql/duckdb/ecommerce_events/00_profile.sql`, `sql/duckdb/ecommerce_events/01_funnel.sql`, `sql/duckdb/ecommerce_events/02_revenue_segments.sql`, `sql/duckdb/ecommerce_events/03_top_products.sql`, `experiments/reports/duckdb_ecommerce_events.md` |  | Add a cohort or retention query. |
| duckdb | IoT offline diagnostics | passed | Local device metrics, time-window aggregates, alert joins, and anomaly ranking. | `sql/duckdb/iot_metrics/00_profile.sql`, `sql/duckdb/iot_metrics/01_metric_windows.sql`, `sql/duckdb/iot_metrics/02_alert_join.sql`, `sql/duckdb/iot_metrics/03_device_health.sql`, `experiments/reports/duckdb_iot_metrics.md` |  | Change device count and compare the alert join with service-backed engines. |
| duckdb | Log observability triage | passed | Local log and trace triage with error-rate, latency percentile, and trace hotspot queries. | `sql/duckdb/log_observability/00_profile.sql`, `sql/duckdb/log_observability/01_error_rate.sql`, `sql/duckdb/log_observability/02_latency_percentiles.sql`, `sql/duckdb/log_observability/03_trace_hotspots.sql`, `experiments/reports/duckdb_log_observability.md` |  | Run `db-lab generate-logs` and `db-lab run-duckdb-logs`, then compare with ClickHouse on the VPS. |
| duckdb | CSV vs Parquet storage | passed | Typed columnar files, compression, column pruning, and local scan timing. | `experiments/reports/duckdb_storage_formats.md`, `docs/duckdb-storage.md` |  | Rerun with larger row counts and compare size ratios. |
| duckdb | Query plan inspection | passed | EXPLAIN, EXPLAIN ANALYZE, scan operators, filters, and projections. | `experiments/reports/duckdb_query_plans.md`, `src/db_lab/experiments/duckdb_query_plans.py` |  | Run `db-lab run-duckdb-plans --analyze` and compare operator timing. |
| duckdb | Scale sensitivity | passed | Row-count changes, CSV/Parquet size growth, and local scan timing direction. | `experiments/reports/duckdb_scale.md`, `src/db_lab/experiments/duckdb_scale.py` |  | Increase scale rows and compare when Parquet advantage becomes clearer. |
| clickhouse | MergeTree market OLAP | passed | Server OLAP, MergeTree schema, partitioning, sorting keys, and batch insert. | `sql/clickhouse/market_ticks/schema.sql`, `sql/clickhouse/market_ticks/00_profile.sql`, `sql/clickhouse/market_ticks/01_symbol_vwap.sql`, `sql/clickhouse/market_ticks/02_minute_bars.sql`, `docs/service-runbook.md` |  | Start ClickHouse and compare the same market queries with DuckDB. |
| clickhouse | Materialized ecommerce aggregation | passed | MergeTree raw events, SummingMergeTree aggregate table, and materialized view flow. | `sql/clickhouse/ecommerce_events/schema.sql`, `sql/clickhouse/ecommerce_events/01_funnel.sql`, `sql/clickhouse/ecommerce_events/04_materialized_revenue.sql`, `docs/service-runbook.md` |  | Start ClickHouse and verify the minute revenue aggregate after inserts. |
| clickhouse | IoT service metrics OLAP | passed | MergeTree device metrics, server-side joins, countIf anomaly scoring, and region windows. | `sql/clickhouse/iot_metrics/schema.sql`, `sql/clickhouse/iot_metrics/01_metric_windows.sql`, `sql/clickhouse/iot_metrics/02_alert_join.sql`, `sql/clickhouse/iot_metrics/03_device_health.sql`, `experiments/reports/clickhouse_iot_metrics.md` |  | Compare ClickHouse IoT windows with DuckDB and DolphinDB after changing row count. |
| clickhouse | Log observability OLAP | passed | MergeTree-backed logs and traces for service error-rate, tail-latency, and trace hotspot analysis. | `sql/clickhouse/log_observability/schema.sql`, `sql/clickhouse/log_observability/01_error_rate.sql`, `sql/clickhouse/log_observability/02_latency_percentiles.sql`, `sql/clickhouse/log_observability/03_trace_hotspots.sql`, `experiments/reports/clickhouse_log_observability.md`, `docs/service-runbook.md` |  | Run `db-lab run-clickhouse-logs` on the VPS after generating log observability CSV files. |
| dolphindb | TSDB market time-series batch | passed | DolphinDB script workflow, TSDB partitioning, and market time-series queries. | `sql/dolphindb/market_ticks/schema.dos`, `sql/dolphindb/market_ticks/import_csv.dos`, `sql/dolphindb/market_ticks/00_profile.dos`, `sql/dolphindb/market_ticks/01_symbol_vwap.dos`, `sql/dolphindb/market_ticks/02_minute_bars.dos`, `docs/service-runbook.md` |  | Start DolphinDB and verify batch minute bars. |
| dolphindb | Stream replay and realtime bars | passed | Shared stream tables, subscribeTable, createTimeSeriesEngine, replay, and realtime bars. | `sql/dolphindb/market_stream/setup.dos`, `sql/dolphindb/market_stream/replay_csv.dos`, `sql/dolphindb/market_stream/01_realtime_minute_bars.dos`, `docs/dolphindb-streaming.md`, `docs/service-runbook.md` |  | Start DolphinDB and compare stream output with batch minute bars. |
| dolphindb | IoT TSDB diagnostics | passed | DolphinDB TSDB partitioning, event-time windows, equality joins, and device anomaly scoring. | `sql/dolphindb/iot_metrics/schema.dos`, `sql/dolphindb/iot_metrics/import_csv.dos`, `sql/dolphindb/iot_metrics/01_metric_windows.dos`, `sql/dolphindb/iot_metrics/02_alert_join.dos`, `experiments/reports/dolphindb_iot_metrics.md` |  | Compare IoT batch diagnostics with a future stream-ingestion experiment. |

## How To Use This Matrix

- `passed` means the latest suite proved that capability locally.
- `needs_service` means scripts and CLI entry points exist, but the database service is not reachable.
- `planned` means evidence files exist but the capability is not represented in the latest suite.
- Use the evidence paths as the reading list for the next focused study session.

experiments/reports/clickhouse_log_observability.md detail report

# ClickHouse Log Observability Experiment

Logs CSV: `data/generated/logs.csv`
Traces CSV: `data/generated/traces.csv`
ClickHouse URL: `http://localhost:8123`

## 00_profile.sql

Elapsed: 11.55 ms

| log_count | trace_count | service_count | span_count | error_count | avg_latency_ms | start_ts | end_ts |
| --- | --- | --- | --- | --- | --- | --- | --- |
| 2000 | 666 | 6 | 2095 | 90 | 224.6 | 2026-01-02 00:00:00.000 | 2026-01-02 01:06:38.000 |

## 01_error_rate.sql

Elapsed: 4.74 ms

| service | log_count | error_count | warn_count | error_rate_pct | avg_latency_ms |
| --- | --- | --- | --- | --- | --- |
| catalog | 333 | 21 | 41 | 6.31 | 143.96 |
| payment | 389 | 19 | 44 | 4.88 | 319.02 |
| worker | 324 | 15 | 40 | 4.63 | 271.87 |
| checkout | 341 | 14 | 49 | 4.11 | 237.02 |
| search | 295 | 11 | 38 | 3.73 | 190.41 |
| api | 318 | 10 | 35 | 3.14 | 163.81 |

## 02_latency_percentiles.sql

Elapsed: 3.73 ms

| service | log_count | p50_latency_ms | p95_latency_ms | max_latency_ms |
| --- | --- | --- | --- | --- |
| payment | 389 | 294 | 516 | 714 |
| worker | 324 | 253 | 443 | 606 |
| checkout | 341 | 225 | 376 | 521 |
| search | 295 | 187 | 305 | 415 |
| api | 318 | 167 | 248 | 348 |
| catalog | 333 | 143 | 236 | 303 |

## 03_trace_hotspots.sql

Elapsed: 7.34 ms

| trace_id | log_count | error_logs | span_count | max_log_latency_ms | total_span_duration_ms | max_span_duration_ms |
| --- | --- | --- | --- | --- | --- | --- |
| T04040000000525 | 3 | 2 | 4 | 590 | 377 | 144 |
| T04040000000538 | 3 | 2 | 3 | 518 | 366 | 216 |
| T04040000000200 | 3 | 2 | 3 | 236 | 347 | 151 |
| T04040000000332 | 3 | 2 | 2 | 606 | 222 | 176 |
| T04040000000074 | 3 | 2 | 2 | 453 | 219 | 130 |
| T04040000000216 | 3 | 1 | 4 | 293 | 794 | 213 |
| T04040000000378 | 3 | 1 | 5 | 650 | 737 | 218 |
| T04040000000118 | 3 | 1 | 5 | 487 | 709 | 164 |
| T04040000000593 | 3 | 1 | 5 | 369 | 700 | 189 |
| T04040000000239 | 3 | 1 | 4 | 356 | 635 | 213 |

experiments/reports/controlled_sweep.md detail report

# DB Lab Controlled Sweep

This report runs the same lab suite across several profiles while changing one variable.

## Sweep Setup

| Item | Value |
| --- | --- |
| Variable | market_rows |
| Values | 1000, 5000 |
| Engines | duckdb, clickhouse, dolphindb |
| Force data | true |
| Base market rows | 1000 |
| Base ecommerce rows | 1000 |
| Base ecommerce users | 100 |
| Base IoT rows | 2000 |
| Base IoT devices | 100 |
| Base DolphinDB stream rate | maximum speed |

## Profile Summary

| Profile | Variable value | Market rows | Ecommerce rows | Ecommerce users | IoT rows | IoT devices | Stream rate | Status | Passed | Needs service | Failed | Suite report |
| --- | ---: | ---: | ---: | ---: | ---: | ---: | --- | --- | ---: | ---: | ---: | --- |
| market-rows-1000 | 1000 | 1000 | 1000 | 100 | 2000 | 100 | maximum speed | passed | 12 | 0 | 0 | `experiments/reports/controlled_runs/market-rows-1000/lab_suite.md` |
| market-rows-5000 | 5000 | 5000 | 1000 | 100 | 2000 | 100 | maximum speed | passed | 12 | 0 | 0 | `experiments/reports/controlled_runs/market-rows-5000/lab_suite.md` |

## Runtime By Experiment

| Profile | Scenario | Engine | Mode | Status | Elapsed ms | Message |
| --- | --- | --- | --- | --- | ---: | --- |
| market-rows-1000 | market_ticks | duckdb | batch | passed | 337.72 | completed |
| market-rows-1000 | market_ticks | duckdb | storage | passed | 1247.06 | completed |
| market-rows-1000 | market_ticks | duckdb | plans | passed | 200.23 | completed |
| market-rows-1000 | market_ticks | duckdb | scale | passed | 1391.68 | completed |
| market-rows-1000 | market_ticks | clickhouse | batch | passed | 57.02 | completed |
| market-rows-1000 | market_ticks | dolphindb | batch | passed | 55.83 | completed |
| market-rows-1000 | market_ticks | dolphindb | stream | passed | 1014.79 | completed |
| market-rows-1000 | ecommerce_events | duckdb | batch | passed | 387.32 | completed |
| market-rows-1000 | ecommerce_events | clickhouse | materialized | passed | 69.51 | completed |
| market-rows-1000 | iot_metrics | duckdb | batch | passed | 898.67 | completed |
| market-rows-1000 | iot_metrics | clickhouse | batch | passed | 50.65 | completed |
| market-rows-1000 | iot_metrics | dolphindb | batch | passed | 115.08 | completed |
| market-rows-5000 | market_ticks | duckdb | batch | passed | 145.80 | completed |
| market-rows-5000 | market_ticks | duckdb | storage | passed | 651.19 | completed |
| market-rows-5000 | market_ticks | duckdb | plans | passed | 110.28 | completed |
| market-rows-5000 | market_ticks | duckdb | scale | passed | 1364.54 | completed |
| market-rows-5000 | market_ticks | clickhouse | batch | passed | 36.20 | completed |
| market-rows-5000 | market_ticks | dolphindb | batch | passed | 66.58 | completed |
| market-rows-5000 | market_ticks | dolphindb | stream | passed | 1023.22 | completed |
| market-rows-5000 | ecommerce_events | duckdb | batch | passed | 386.58 | completed |
| market-rows-5000 | ecommerce_events | clickhouse | materialized | passed | 63.07 | completed |
| market-rows-5000 | iot_metrics | duckdb | batch | passed | 921.13 | completed |
| market-rows-5000 | iot_metrics | clickhouse | batch | passed | 55.52 | completed |
| market-rows-5000 | iot_metrics | dolphindb | batch | passed | 109.33 | completed |

## Interpretation Prompts

- Compare runtime only after checking that the matching rows are `passed`.
- If one profile has `needs_service`, treat it as an environment check, not a database finding.
- If row counts changed but runtime did not, inspect caching, query plans, and whether the selected query scans the changed dataset.
- Keep the next sweep to one variable so the result stays explainable.

experiments/reports/doctor.md detail report

# DB Lab Doctor

| Area | Check | Status | Detail | Next action |
| --- | --- | --- | --- | --- |
| Runtime | Python | ok | 3.12.3 |  |
| Runtime | DuckDB package | ok | 1.5.3 |  |
| Runtime | Docker CLI | ok | Docker version 29.4.2, build 055a478 at /usr/bin/docker |  |
| Service | ClickHouse | ok | http://localhost:8123 returned '1' |  |
| Service | DolphinDB | ok | http://localhost:8848 returned [2] |  |
| Data | market_ticks.csv | ok | 300 rows, 21213 bytes |  |
| Data | ecommerce_events.csv | ok | 300 rows, 37621 bytes |  |
| Data | Market Parquet | ok | /root/projects/db-lab/data/parquet/market_ticks.parquet, 5363 bytes |  |
| Reports | Lab suite report | ok | /root/projects/db-lab/experiments/reports/lab_suite.md, 3022 bytes |  |
| App | Static dashboard | ok | /root/projects/db-lab/app/index.html, 221792 bytes |  |

## Status Meaning

- `ok` means this part is ready for the matching experiment.
- `missing` means a local dependency or generated artifact is absent.
- `needs_service` means Docker or a service-backed database is not reachable yet.

experiments/reports/duckdb_log_observability.md detail report

# DuckDB Log Observability Experiment

Logs CSV: `data/generated/logs.csv`
Traces CSV: `data/generated/traces.csv`

## 00_profile.sql

Elapsed: 711.28 ms

| log_count | trace_count | service_count | span_count | error_count | avg_latency_ms | start_ts | end_ts |
| --- | --- | --- | --- | --- | --- | --- | --- |
| 2000 | 666 | 6 | 2095 | 90 | 224.6 | 2026-01-02 00:00:00 | 2026-01-02 01:06:38 |

## 01_error_rate.sql

Elapsed: 118.84 ms

| service | log_count | error_count | warn_count | error_rate_pct | avg_latency_ms |
| --- | --- | --- | --- | --- | --- |
| catalog | 333 | 21 | 41 | 6.31 | 143.96 |
| payment | 389 | 19 | 44 | 4.88 | 319.02 |
| worker | 324 | 15 | 40 | 4.63 | 271.87 |
| checkout | 341 | 14 | 49 | 4.11 | 237.02 |
| search | 295 | 11 | 38 | 3.73 | 190.41 |
| api | 318 | 10 | 35 | 3.14 | 163.81 |

## 02_latency_percentiles.sql

Elapsed: 117.28 ms

| service | log_count | p50_latency_ms | p95_latency_ms | max_latency_ms |
| --- | --- | --- | --- | --- |
| payment | 389 | 294.0 | 514.3999999999999 | 714 |
| worker | 324 | 252.5 | 443.0 | 606 |
| checkout | 341 | 225.0 | 376.0 | 521 |
| search | 295 | 187.0 | 296.60000000000014 | 415 |
| api | 318 | 167.0 | 247.14999999999998 | 348 |
| catalog | 333 | 143.0 | 232.39999999999986 | 303 |

## 03_trace_hotspots.sql

Elapsed: 131.75 ms

| trace_id | log_count | error_logs | span_count | max_log_latency_ms | total_span_duration_ms | max_span_duration_ms |
| --- | --- | --- | --- | --- | --- | --- |
| T04040000000525 | 3 | 2 | 4 | 590 | 377 | 144 |
| T04040000000538 | 3 | 2 | 3 | 518 | 366 | 216 |
| T04040000000200 | 3 | 2 | 3 | 236 | 347 | 151 |
| T04040000000332 | 3 | 2 | 2 | 606 | 222 | 176 |
| T04040000000074 | 3 | 2 | 2 | 453 | 219 | 130 |
| T04040000000216 | 3 | 1 | 4 | 293 | 794 | 213 |
| T04040000000378 | 3 | 1 | 5 | 650 | 737 | 218 |
| T04040000000118 | 3 | 1 | 5 | 487 | 709 | 164 |
| T04040000000593 | 3 | 1 | 5 | 369 | 700 | 189 |
| T04040000000239 | 3 | 1 | 4 | 356 | 635 | 213 |

experiments/reports/engine_notes.md detail report

# DB Lab Engine Notes

These notes are the per-engine study summaries for turning lab evidence into memory.

## Evidence Sources

- Suite report: `experiments/reports/lab_suite.md`
- Capability matrix: `experiments/reports/capability_matrix.md`
- Result validation: `experiments/reports/result_validation.md`
- Query catalog: `experiments/reports/query_catalog.md`
- Selection memo: `experiments/reports/selection_memo.md`

Correctness baseline: **23 passed / 0 failed**

## DuckDB

- Mental model: A local analytical engine that turns files into queryable tables with minimal operations.
- Best fit: CSV/Parquet exploration, quick business questions, local reproducible analysis, query-plan study.
- Watch-out: It is not a shared always-on ingestion service unless another application provides that layer.
- Starter command: `db-lab run-duckdb-storage --repetitions 5`
- Current validation context: 23 passed / 0 failed

### Verified Capabilities

| Capability | Status | Study focus | Evidence | Next action |
| --- | --- | --- | --- | --- |
| Embedded file analytics | passed | Direct CSV scans, local OLAP, VWAP, and minute bars. | `sql/duckdb/market_ticks/00_profile.sql`, `sql/duckdb/market_ticks/01_symbol_vwap.sql`, `sql/duckdb/market_ticks/02_minute_bars.sql`, `experiments/reports/duckdb_market_ticks.md` | Change row count and compare query timing. |
| Ecommerce funnel analytics | passed | Session funnels, revenue segmentation, and Top product queries. | `sql/duckdb/ecommerce_events/00_profile.sql`, `sql/duckdb/ecommerce_events/01_funnel.sql`, `sql/duckdb/ecommerce_events/02_revenue_segments.sql`, `sql/duckdb/ecommerce_events/03_top_products.sql`, `experiments/reports/duckdb_ecommerce_events.md` | Add a cohort or retention query. |
| IoT offline diagnostics | passed | Local device metrics, time-window aggregates, alert joins, and anomaly ranking. | `sql/duckdb/iot_metrics/00_profile.sql`, `sql/duckdb/iot_metrics/01_metric_windows.sql`, `sql/duckdb/iot_metrics/02_alert_join.sql`, `sql/duckdb/iot_metrics/03_device_health.sql`, `experiments/reports/duckdb_iot_metrics.md` | Change device count and compare the alert join with service-backed engines. |
| Log observability triage | passed | Local log and trace triage with error-rate, latency percentile, and trace hotspot queries. | `sql/duckdb/log_observability/00_profile.sql`, `sql/duckdb/log_observability/01_error_rate.sql`, `sql/duckdb/log_observability/02_latency_percentiles.sql`, `sql/duckdb/log_observability/03_trace_hotspots.sql`, `experiments/reports/duckdb_log_observability.md` | Run `db-lab generate-logs` and `db-lab run-duckdb-logs`, then compare with ClickHouse on the VPS. |
| CSV vs Parquet storage | passed | Typed columnar files, compression, column pruning, and local scan timing. | `experiments/reports/duckdb_storage_formats.md`, `docs/duckdb-storage.md` | Rerun with larger row counts and compare size ratios. |
| Query plan inspection | passed | EXPLAIN, EXPLAIN ANALYZE, scan operators, filters, and projections. | `experiments/reports/duckdb_query_plans.md`, `src/db_lab/experiments/duckdb_query_plans.py` | Run `db-lab run-duckdb-plans --analyze` and compare operator timing. |
| Scale sensitivity | passed | Row-count changes, CSV/Parquet size growth, and local scan timing direction. | `experiments/reports/duckdb_scale.md`, `src/db_lab/experiments/duckdb_scale.py` | Increase scale rows and compare when Parquet advantage becomes clearer. |

### Script And Concept Map

| Dataset | Script | Concepts |
| --- | --- | --- |
| ecommerce_events | `sql/duckdb/ecommerce_events/00_profile.sql` | Local file scan, Distinct counting |
| ecommerce_events | `sql/duckdb/ecommerce_events/01_funnel.sql` | Local file scan, Distinct counting, Join diagnostics, Session funnel |
| ecommerce_events | `sql/duckdb/ecommerce_events/02_revenue_segments.sql` | Local file scan, Distinct counting, Anomaly scoring |
| ecommerce_events | `sql/duckdb/ecommerce_events/03_top_products.sql` | Local file scan, Distinct counting |
| iot_metrics | `sql/duckdb/iot_metrics/00_profile.sql` | Local file scan, Distinct counting |
| iot_metrics | `sql/duckdb/iot_metrics/01_metric_windows.sql` | Local file scan, Time bucketing |
| iot_metrics | `sql/duckdb/iot_metrics/02_alert_join.sql` | Join diagnostics |
| iot_metrics | `sql/duckdb/iot_metrics/03_device_health.sql` | Local file scan, Anomaly scoring |
| log_observability | `sql/duckdb/log_observability/00_profile.sql` | Local file scan, Distinct counting, Observability diagnostics |
| log_observability | `sql/duckdb/log_observability/01_error_rate.sql` | Local file scan, Anomaly scoring, Observability diagnostics |
| log_observability | `sql/duckdb/log_observability/02_latency_percentiles.sql` | Local file scan, Observability diagnostics, Latency percentiles |
| log_observability | `sql/duckdb/log_observability/03_trace_hotspots.sql` | Local file scan, Join diagnostics, Anomaly scoring, Observability diagnostics |
| market_ticks | `sql/duckdb/market_ticks/00_profile.sql` | Local file scan, VWAP, Distinct counting |
| market_ticks | `sql/duckdb/market_ticks/01_symbol_vwap.sql` | Local file scan, VWAP |
| market_ticks | `sql/duckdb/market_ticks/02_minute_bars.sql` | Local file scan, Time bucketing, Ordered open and close, VWAP |

Concepts to remember: Anomaly scoring, Distinct counting, Join diagnostics, Latency percentiles, Local file scan, Observability diagnostics, Ordered open and close, Session funnel, Time bucketing, VWAP

### Latest Runs

| Scenario | Mode | Status | Elapsed ms | Report |
| --- | --- | --- | ---: | --- |
| market_ticks | batch | passed | 331.95 | `experiments/reports/duckdb_market_ticks.md` |
| market_ticks | storage | passed | 1232.12 | `experiments/reports/duckdb_storage_formats.md` |
| market_ticks | plans | passed | 197.48 | `experiments/reports/duckdb_query_plans.md` |
| market_ticks | scale | passed | 1370.59 | `experiments/reports/duckdb_scale.md` |
| ecommerce_events | batch | passed | 398.22 | `experiments/reports/duckdb_ecommerce_events.md` |
| iot_metrics | batch | passed | 860.12 | `experiments/reports/duckdb_iot_metrics.md` |

### Explain-Back Prompts

- Explain why DuckDB can answer from CSV/Parquet without a running service.
- Explain when Parquet and query plans change your decision.
- Explain why DuckDB is the local baseline for cross-engine validation.

## ClickHouse

- Mental model: A service OLAP engine where ingestion, table layout, and repeated aggregates are first-class concerns.
- Best fit: Event analytics, shared dashboards, MergeTree tables, high-throughput aggregations, materialized rollups.
- Watch-out: It has operational cost: service startup, ingestion paths, schemas, and table maintenance matter.
- Starter command: `db-lab run-clickhouse-ecommerce`
- Current validation context: 23 passed / 0 failed

### Verified Capabilities

| Capability | Status | Study focus | Evidence | Next action |
| --- | --- | --- | --- | --- |
| MergeTree market OLAP | passed | Server OLAP, MergeTree schema, partitioning, sorting keys, and batch insert. | `sql/clickhouse/market_ticks/schema.sql`, `sql/clickhouse/market_ticks/00_profile.sql`, `sql/clickhouse/market_ticks/01_symbol_vwap.sql`, `sql/clickhouse/market_ticks/02_minute_bars.sql`, `docs/service-runbook.md` | Start ClickHouse and compare the same market queries with DuckDB. |
| Materialized ecommerce aggregation | passed | MergeTree raw events, SummingMergeTree aggregate table, and materialized view flow. | `sql/clickhouse/ecommerce_events/schema.sql`, `sql/clickhouse/ecommerce_events/01_funnel.sql`, `sql/clickhouse/ecommerce_events/04_materialized_revenue.sql`, `docs/service-runbook.md` | Start ClickHouse and verify the minute revenue aggregate after inserts. |
| IoT service metrics OLAP | passed | MergeTree device metrics, server-side joins, countIf anomaly scoring, and region windows. | `sql/clickhouse/iot_metrics/schema.sql`, `sql/clickhouse/iot_metrics/01_metric_windows.sql`, `sql/clickhouse/iot_metrics/02_alert_join.sql`, `sql/clickhouse/iot_metrics/03_device_health.sql`, `experiments/reports/clickhouse_iot_metrics.md` | Compare ClickHouse IoT windows with DuckDB and DolphinDB after changing row count. |
| Log observability OLAP | passed | MergeTree-backed logs and traces for service error-rate, tail-latency, and trace hotspot analysis. | `sql/clickhouse/log_observability/schema.sql`, `sql/clickhouse/log_observability/01_error_rate.sql`, `sql/clickhouse/log_observability/02_latency_percentiles.sql`, `sql/clickhouse/log_observability/03_trace_hotspots.sql`, `experiments/reports/clickhouse_log_observability.md`, `docs/service-runbook.md` | Run `db-lab run-clickhouse-logs` on the VPS after generating log observability CSV files. |

### Script And Concept Map

| Dataset | Script | Concepts |
| --- | --- | --- |
| ecommerce_events | `sql/clickhouse/ecommerce_events/00_profile.sql` | Service table, Distinct counting |
| ecommerce_events | `sql/clickhouse/ecommerce_events/01_funnel.sql` | Service table, Distinct counting, Session funnel |
| ecommerce_events | `sql/clickhouse/ecommerce_events/02_revenue_segments.sql` | Service table, Distinct counting, Anomaly scoring |
| ecommerce_events | `sql/clickhouse/ecommerce_events/03_top_products.sql` | Service table, Distinct counting |
| ecommerce_events | `sql/clickhouse/ecommerce_events/04_materialized_revenue.sql` | Service table, Materialized aggregation |
| ecommerce_events | `sql/clickhouse/ecommerce_events/schema.sql` | Service table, Columnar storage layout, Materialized aggregation, Time bucketing |
| iot_metrics | `sql/clickhouse/iot_metrics/00_profile.sql` | Service table, Distinct counting |
| iot_metrics | `sql/clickhouse/iot_metrics/01_metric_windows.sql` | Service table, Time bucketing |
| iot_metrics | `sql/clickhouse/iot_metrics/02_alert_join.sql` | Service table, Join diagnostics |
| iot_metrics | `sql/clickhouse/iot_metrics/03_device_health.sql` | Service table, Anomaly scoring |
| iot_metrics | `sql/clickhouse/iot_metrics/schema.sql` | Service table, Columnar storage layout |
| log_observability | `sql/clickhouse/log_observability/00_profile.sql` | Service table, Distinct counting, Anomaly scoring, Observability diagnostics |
| log_observability | `sql/clickhouse/log_observability/01_error_rate.sql` | Service table, Anomaly scoring, Observability diagnostics |
| log_observability | `sql/clickhouse/log_observability/02_latency_percentiles.sql` | Service table, Observability diagnostics, Latency percentiles |
| log_observability | `sql/clickhouse/log_observability/03_trace_hotspots.sql` | Service table, Join diagnostics, Anomaly scoring, Observability diagnostics |
| log_observability | `sql/clickhouse/log_observability/schema.sql` | Service table, Columnar storage layout, Observability diagnostics |
| market_ticks | `sql/clickhouse/market_ticks/00_profile.sql` | Service table, VWAP, Distinct counting |
| market_ticks | `sql/clickhouse/market_ticks/01_symbol_vwap.sql` | Service table, VWAP |
| market_ticks | `sql/clickhouse/market_ticks/02_minute_bars.sql` | Service table, Time bucketing, Ordered open and close, VWAP |
| market_ticks | `sql/clickhouse/market_ticks/schema.sql` | Service table, Columnar storage layout |

Concepts to remember: Anomaly scoring, Columnar storage layout, Distinct counting, Join diagnostics, Latency percentiles, Materialized aggregation, Observability diagnostics, Ordered open and close, Service table, Session funnel, Time bucketing, VWAP

### Latest Runs

| Scenario | Mode | Status | Elapsed ms | Report |
| --- | --- | --- | ---: | --- |
| market_ticks | batch | passed | 57.30 | `experiments/reports/clickhouse_market_ticks.md` |
| ecommerce_events | materialized | passed | 58.13 | `experiments/reports/clickhouse_ecommerce_events.md` |
| iot_metrics | batch | passed | 49.73 | `experiments/reports/clickhouse_iot_metrics.md` |

### Explain-Back Prompts

- Explain how MergeTree ordering and partitioning shape OLAP work.
- Explain why materialized aggregation changes when work is computed.
- Explain what operational cost ClickHouse adds compared with DuckDB.

## DolphinDB

- Mental model: A time-series and stream-processing platform where batch tables and realtime windows can be compared.
- Best fit: Market ticks, OHLC/VWAP, TSDB partitioning, historical replay, stream-window consistency checks.
- Watch-out: Use it when time-series or streaming is central; generic event funnels are not its strongest learning case here.
- Starter command: `db-lab run-dolphindb-stream --rate 5000`
- Current validation context: 23 passed / 0 failed

### Verified Capabilities

| Capability | Status | Study focus | Evidence | Next action |
| --- | --- | --- | --- | --- |
| TSDB market time-series batch | passed | DolphinDB script workflow, TSDB partitioning, and market time-series queries. | `sql/dolphindb/market_ticks/schema.dos`, `sql/dolphindb/market_ticks/import_csv.dos`, `sql/dolphindb/market_ticks/00_profile.dos`, `sql/dolphindb/market_ticks/01_symbol_vwap.dos`, `sql/dolphindb/market_ticks/02_minute_bars.dos`, `docs/service-runbook.md` | Start DolphinDB and verify batch minute bars. |
| Stream replay and realtime bars | passed | Shared stream tables, subscribeTable, createTimeSeriesEngine, replay, and realtime bars. | `sql/dolphindb/market_stream/setup.dos`, `sql/dolphindb/market_stream/replay_csv.dos`, `sql/dolphindb/market_stream/01_realtime_minute_bars.dos`, `docs/dolphindb-streaming.md`, `docs/service-runbook.md` | Start DolphinDB and compare stream output with batch minute bars. |
| IoT TSDB diagnostics | passed | DolphinDB TSDB partitioning, event-time windows, equality joins, and device anomaly scoring. | `sql/dolphindb/iot_metrics/schema.dos`, `sql/dolphindb/iot_metrics/import_csv.dos`, `sql/dolphindb/iot_metrics/01_metric_windows.dos`, `sql/dolphindb/iot_metrics/02_alert_join.dos`, `experiments/reports/dolphindb_iot_metrics.md` | Compare IoT batch diagnostics with a future stream-ingestion experiment. |

### Script And Concept Map

| Dataset | Script | Concepts |
| --- | --- | --- |
| iot_metrics | `sql/dolphindb/iot_metrics/00_profile.dos` | Service table, Distinct counting |
| iot_metrics | `sql/dolphindb/iot_metrics/01_metric_windows.dos` | Service table, Time bucketing |
| iot_metrics | `sql/dolphindb/iot_metrics/02_alert_join.dos` | Service table, Join diagnostics |
| iot_metrics | `sql/dolphindb/iot_metrics/03_device_health.dos` | Service table, Anomaly scoring |
| iot_metrics | `sql/dolphindb/iot_metrics/import_csv.dos` | Service table, CSV import with schema |
| iot_metrics | `sql/dolphindb/iot_metrics/schema.dos` | Service table, Columnar storage layout |
| market_stream | `sql/dolphindb/market_stream/00_stream_status.dos` | Distinct counting, Stream processing |
| market_stream | `sql/dolphindb/market_stream/01_realtime_minute_bars.dos` | Ordered open and close, VWAP, Stream processing |
| market_stream | `sql/dolphindb/market_stream/cleanup.dos` | Stream processing |
| market_stream | `sql/dolphindb/market_stream/replay_csv.dos` | Stream processing, CSV import with schema |
| market_stream | `sql/dolphindb/market_stream/setup.dos` | Time bucketing, Ordered open and close, VWAP, Stream processing |
| market_ticks | `sql/dolphindb/market_ticks/00_profile.dos` | Service table, VWAP, Distinct counting |
| market_ticks | `sql/dolphindb/market_ticks/01_symbol_vwap.dos` | Service table, VWAP |
| market_ticks | `sql/dolphindb/market_ticks/02_minute_bars.dos` | Service table, Time bucketing, Ordered open and close, VWAP |
| market_ticks | `sql/dolphindb/market_ticks/import_csv.dos` | Service table, CSV import with schema |
| market_ticks | `sql/dolphindb/market_ticks/schema.dos` | Service table, Columnar storage layout |

Concepts to remember: Anomaly scoring, CSV import with schema, Columnar storage layout, Distinct counting, Join diagnostics, Ordered open and close, Service table, Stream processing, Time bucketing, VWAP

### Latest Runs

| Scenario | Mode | Status | Elapsed ms | Report |
| --- | --- | --- | ---: | --- |
| market_ticks | batch | passed | 57.42 | `experiments/reports/dolphindb_market_ticks.md` |
| market_ticks | stream | passed | 1016.40 | `experiments/reports/dolphindb_market_stream.md` |
| iot_metrics | batch | passed | 112.76 | `experiments/reports/dolphindb_iot_metrics.md` |

### Explain-Back Prompts

- Explain why TSDB partitioning fits market ticks.
- Explain how replay turns historical ticks into a repeatable stream experiment.
- Explain why batch and stream bars must match before tuning replay rate.

## Cross-Engine Memory Hooks

- Same metric, different dialect: VWAP and minute bars are the easiest way to remember syntax differences.
- Same business result before timing: result validation is the gate before interpreting performance.
- Same data, different operational shape: DuckDB reads files, ClickHouse serves tables, DolphinDB models time and stream state.

experiments/reports/learning_journal.md detail report

# DB Lab Learning Journal

This journal turns the latest lab reports into a practical study record.

## Source Reports

- Suite report: `experiments/reports/lab_suite.md`
- Doctor report: `experiments/reports/doctor.md`

## Current Data Snapshot

- Market ticks CSV: data/generated/market_ticks.csv
- Market ticks action: generated
- Market ticks rows: 1000
- Market ticks symbols: 6
- Market ticks time range: 2026-01-02T09:30:00.000 -> 2026-01-02T09:34:09.750
- Ecommerce events CSV: data/generated/ecommerce_events.csv
- Ecommerce events action: generated
- Ecommerce events rows: 1000
- Ecommerce events users: 97
- Ecommerce events time range: 2026-01-02T00:00:00 -> 2026-01-02T00:49:57
- IoT metrics CSV: data/generated/iot_metrics.csv
- IoT metrics action: generated
- IoT metrics rows: 2000
- IoT metrics devices: 100
- IoT metrics time range: 2026-01-02T00:00:00 -> 2026-01-02T02:46:35
- IoT alerts CSV: data/generated/iot_alerts.csv
- IoT alerts action: generated
- IoT alerts rows: 12
- IoT alerts devices: 12
- IoT alerts time range: 2026-01-02T00:00:00 -> 2026-01-02T02:44:30

## Environment Action Items

- No doctor action items recorded.

## Completed Experiments

| Scenario | Engine | Mode | Status | Report | Message |
| --- | --- | --- | --- | --- | --- |
| market_ticks | duckdb | batch | passed | `experiments/reports/duckdb_market_ticks.md` | completed |
| market_ticks | duckdb | storage | passed | `experiments/reports/duckdb_storage_formats.md` | completed |
| market_ticks | duckdb | plans | passed | `experiments/reports/duckdb_query_plans.md` | completed |
| market_ticks | duckdb | scale | passed | `experiments/reports/duckdb_scale.md` | completed |
| market_ticks | clickhouse | batch | passed | `experiments/reports/clickhouse_market_ticks.md` | completed |
| market_ticks | dolphindb | batch | passed | `experiments/reports/dolphindb_market_ticks.md` | completed |
| market_ticks | dolphindb | stream | passed | `experiments/reports/dolphindb_market_stream.md` | completed |
| ecommerce_events | duckdb | batch | passed | `experiments/reports/duckdb_ecommerce_events.md` | completed |
| ecommerce_events | clickhouse | materialized | passed | `experiments/reports/clickhouse_ecommerce_events.md` | completed |
| iot_metrics | duckdb | batch | passed | `experiments/reports/duckdb_iot_metrics.md` | completed |
| iot_metrics | clickhouse | batch | passed | `experiments/reports/clickhouse_iot_metrics.md` | completed |
| iot_metrics | dolphindb | batch | passed | `experiments/reports/dolphindb_iot_metrics.md` | completed |

## Experiments Waiting For Services

- No pending or failed experiments in the latest suite report.

## Engine Takeaways

### duckdb
- Current role: local embedded analytics for generated CSV and Parquet files.
- Evidence: market_ticks/batch/passed, market_ticks/storage/passed, market_ticks/plans/passed, market_ticks/scale/passed, ecommerce_events/batch/passed, iot_metrics/batch/passed
- Study focus: file scanning, columnar storage, local OLAP, and Python-driven exploration.

### clickhouse
- Current role: service-backed OLAP for event ingestion, `MergeTree`, and materialized aggregation.
- Evidence: market_ticks/batch/passed, ecommerce_events/materialized/passed, iot_metrics/batch/passed
- Study focus: table engines, sort keys, batch insert behavior, and materialized views.

### dolphindb
- Current role: time-series and stream processing for market tick data.
- Evidence: market_ticks/batch/passed, market_ticks/stream/passed, iot_metrics/batch/passed
- Study focus: TSDB partitions, script workflow, stream tables, subscriptions, and replay.

## Practice Loop

1. Run `db-lab archive-run --label before-next-run`.
2. Run `db-lab doctor --report experiments/reports/doctor.md`.
3. Run `db-lab run-lab-suite --force-data --market-rows 1000 --ecommerce-rows 1000 --ecommerce-users 100 --iot-rows 2000 --iot-devices 100`.
4. Run `db-lab generate-logs --rows 2000 --services 6`.
5. Run `db-lab run-duckdb-logs`.
6. Run `db-lab capabilities` to see which database skills are covered or blocked.
7. Run `db-lab compare-results` to compare returned result shapes across engines.
8. Run `db-lab validate-results` to separate correctness checks from runtime observations.
9. Run `db-lab compare-runs` to compare the latest run with the archived baseline.
10. Run `db-lab query-catalog` to refresh the personal SQL/script handbook.
11. Run `db-lab selection-memo` to turn evidence into database-choice rules.
12. Run `db-lab engine-notes` to refresh the per-database learning summaries.
13. Run `db-lab controlled-sweep --variable market_rows --values 1000,5000 --base-iot-rows 2000 --base-iot-devices 100` to compare two controlled profiles.
14. Run `db-lab metrics` to turn the latest reports into dashboard metrics.
15. Run `db-lab plan-next` to decide the next controlled variable and command sequence.
16. Run `db-lab study-guide` to turn current evidence into practice tasks.
17. Run `db-lab journal` and compare the new journal with the previous one.
18. Open `app/index.html` after `db-lab render-dashboard`.
19. If local Docker/WSL is unavailable, run `db-lab run-remote-suite --host your-vps-host --user root --project-dir /root/projects/db-lab`.
20. Change one variable only: row count, selected query, storage format, service availability, or remote runtime.
21. Write down what changed in runtime, result shape, storage size, or operational friction.

## Questions To Answer Next

- DuckDB: how do CSV and Parquet size/timing curves change as row count grows?
- DuckDB: when does Parquet become visibly better than CSV for this dataset, and which plan operators explain it?
- ClickHouse: how do `MergeTree` ordering keys change query speed and ingestion behavior?
- DolphinDB: do batch minute bars and streaming minute bars produce the same business result?
- Capability matrix: which row should move from `needs_service` to `passed` next?
- Platform: which result should become a dashboard metric instead of raw Markdown?

experiments/reports/metrics_summary.md detail report

# DB Lab Metrics Summary

This report turns the latest Markdown reports into dashboard-ready learning metrics.

## Source Reports

- Suite report: `experiments/reports/lab_suite.md`
- Result validation: `experiments/reports/result_validation.md` (found)
- Controlled sweep: `experiments/reports/controlled_sweep.md` (found)

## Metric Summary

| Metric | Value | Source | Use |
| --- | --- | --- | --- |
| Latest suite status | 12 passed / 0 needs_service / 0 failed | `lab_suite.md` | Check availability before interpreting runtime. |
| Result validation | 23 passed / 0 failed | `result_validation.md` | Correctness comes before performance comparison. |
| Service-backed coverage | 6 / 6 service experiments passed | `lab_suite.md` | Shows whether ClickHouse and DolphinDB evidence is current. |
| Controlled sweep profiles | 2 profiles, 2 passed, 0 failed | `controlled_sweep.md` | Confirms the variable scan is usable for learning. |
| Controlled variable | market_rows: 1000, 5000 | `controlled_sweep.md` | Keeps the current comparison to one changed input. |
| Slowest latest experiment | market_ticks/duckdb/scale: 1370.59 ms | `lab_suite.md` | Start plan and storage inspection from the slowest repeated task. |
| Largest sweep delta | market_ticks/duckdb/storage: -595.87 ms | `controlled_sweep.md` | Largest runtime movement after changing the controlled variable. |

## Latest Suite Runtime

| Scenario | Engine | Mode | Status | Elapsed ms |
| --- | --- | --- | --- | ---: |
| ecommerce_events | clickhouse | materialized | passed | 58.13 |
| ecommerce_events | duckdb | batch | passed | 398.22 |
| iot_metrics | clickhouse | batch | passed | 49.73 |
| iot_metrics | dolphindb | batch | passed | 112.76 |
| iot_metrics | duckdb | batch | passed | 860.12 |
| market_ticks | clickhouse | batch | passed | 57.30 |
| market_ticks | dolphindb | batch | passed | 57.42 |
| market_ticks | dolphindb | stream | passed | 1016.40 |
| market_ticks | duckdb | batch | passed | 331.95 |
| market_ticks | duckdb | plans | passed | 197.48 |
| market_ticks | duckdb | scale | passed | 1370.59 |
| market_ticks | duckdb | storage | passed | 1232.12 |

## Controlled Sweep Runtime Delta

| Scenario | Engine | Mode | First profile | Last profile | First ms | Last ms | Delta ms |
| --- | --- | --- | --- | --- | ---: | ---: | ---: |
| market_ticks | duckdb | storage | market-rows-1000 | market-rows-5000 | 1247.06 | 651.19 | -595.87 |
| market_ticks | duckdb | batch | market-rows-1000 | market-rows-5000 | 337.72 | 145.80 | -191.92 |
| market_ticks | duckdb | plans | market-rows-1000 | market-rows-5000 | 200.23 | 110.28 | -89.95 |
| market_ticks | duckdb | scale | market-rows-1000 | market-rows-5000 | 1391.68 | 1364.54 | -27.14 |
| iot_metrics | duckdb | batch | market-rows-1000 | market-rows-5000 | 898.67 | 921.13 | +22.46 |
| market_ticks | clickhouse | batch | market-rows-1000 | market-rows-5000 | 57.02 | 36.20 | -20.82 |
| market_ticks | dolphindb | batch | market-rows-1000 | market-rows-5000 | 55.83 | 66.58 | +10.75 |
| market_ticks | dolphindb | stream | market-rows-1000 | market-rows-5000 | 1014.79 | 1023.22 | +8.43 |
| ecommerce_events | clickhouse | materialized | market-rows-1000 | market-rows-5000 | 69.51 | 63.07 | -6.44 |
| iot_metrics | dolphindb | batch | market-rows-1000 | market-rows-5000 | 115.08 | 109.33 | -5.75 |
| iot_metrics | clickhouse | batch | market-rows-1000 | market-rows-5000 | 50.65 | 55.52 | +4.87 |
| ecommerce_events | duckdb | batch | market-rows-1000 | market-rows-5000 | 387.32 | 386.58 | -0.74 |

## Interpretation Prompts

- If validation is not `0 failed`, ignore runtime changes until correctness is fixed.
- If service-backed coverage is incomplete, classify the finding as environment readiness.
- If the largest delta is in DuckDB storage or plan inspection, compare CSV and Parquet reports first.
- If the largest delta is in ClickHouse or DolphinDB, separate ingestion, service cache, and query execution effects.

experiments/reports/next_experiment_plan.md detail report

# DB Lab Next Experiment Plan

This plan turns the latest lab evidence into one concrete next run.

## Source Reports

- Suite report: `experiments/reports/lab_suite.md`
- Capability matrix: `experiments/reports/capability_matrix.md` (found)
- Result validation: `experiments/reports/result_validation.md` (found)

## Current State

| Metric | Value |
| --- | --- |
| Focus | all |
| Completed experiments | 12 / 12 |
| Experiments needing action | 0 |
| Result validation | 23 passed / 0 failed |
| Current market rows | 1000 |
| Current ecommerce rows | 1000 |
| Current ecommerce users | 97 |
| Current IoT rows | 2000 |
| Current IoT devices | 100 |

## Next Run Profile

| Variable | Current | Next run | Reason |
| --- | ---: | ---: | --- |
| Market ticks rows | 1000 | 5000 | Scale the time-series workload while preserving the same query set. |
| Ecommerce events rows | 1000 | 5000 | Scale the event analytics workload while preserving funnel and revenue semantics. |
| Ecommerce users | 97 | 500 | Change user cardinality separately from raw event count. |
| IoT metrics rows | 2000 | 2000 | Scale device metric volume while preserving the same anomaly and join queries. |
| IoT devices | 100 | 100 | Change device cardinality separately from raw metric count. |
| DolphinDB stream rate | latest suite setting | maximum speed | Observe replay latency without changing final bar values. |

## Command Plan

```powershell
db-lab archive-run --label before-all-m5000-e5000-u500-iot2000-dev100
db-lab doctor --report experiments/reports/doctor.md
db-lab run-lab-suite `
  --force-data `
  --market-rows 5000 `
  --ecommerce-rows 5000 `
  --ecommerce-users 500 `
  --iot-rows 2000 `
  --iot-devices 100 `
  --dolphindb-stream-rate -1
db-lab capabilities
db-lab compare-results
db-lab validate-results --strict
db-lab compare-runs
db-lab query-catalog
db-lab selection-memo
db-lab engine-notes
db-lab controlled-sweep `
  --variable market_rows `
  --values 5000,10000 `
  --base-market-rows 5000 `
  --base-ecommerce-rows 5000 `
  --base-ecommerce-users 500 `
  --base-iot-rows 2000 `
  --base-iot-devices 100 `
  --base-dolphindb-stream-rate -1 `
  --engines duckdb,clickhouse,dolphindb
db-lab metrics
db-lab plan-next
db-lab study-guide
db-lab journal
db-lab render-dashboard
```

## Focused Learning Tasks

- duckdb: deepen `Embedded file analytics` by changing one variable and rereading `sql/duckdb/market_ticks/00_profile.sql`, `sql/duckdb/market_ticks/01_symbol_vwap.sql`, `sql/duckdb/market_ticks/02_minute_bars.sql`, `experiments/reports/duckdb_market_ticks.md`.
- duckdb: deepen `Ecommerce funnel analytics` by changing one variable and rereading `sql/duckdb/ecommerce_events/00_profile.sql`, `sql/duckdb/ecommerce_events/01_funnel.sql`, `sql/duckdb/ecommerce_events/02_revenue_segments.sql`, `sql/duckdb/ecommerce_events/03_top_products.sql`, `experiments/reports/duckdb_ecommerce_events.md`.
- duckdb: deepen `IoT offline diagnostics` by changing one variable and rereading `sql/duckdb/iot_metrics/00_profile.sql`, `sql/duckdb/iot_metrics/01_metric_windows.sql`, `sql/duckdb/iot_metrics/02_alert_join.sql`, `sql/duckdb/iot_metrics/03_device_health.sql`, `experiments/reports/duckdb_iot_metrics.md`.
- duckdb: deepen `Log observability triage` by changing one variable and rereading `sql/duckdb/log_observability/00_profile.sql`, `sql/duckdb/log_observability/01_error_rate.sql`, `sql/duckdb/log_observability/02_latency_percentiles.sql`, `sql/duckdb/log_observability/03_trace_hotspots.sql`, `experiments/reports/duckdb_log_observability.md`.
- duckdb: deepen `CSV vs Parquet storage` by changing one variable and rereading `experiments/reports/duckdb_storage_formats.md`, `docs/duckdb-storage.md`.
- duckdb: deepen `Query plan inspection` by changing one variable and rereading `experiments/reports/duckdb_query_plans.md`, `src/db_lab/experiments/duckdb_query_plans.py`.

## Observation Checklist

- Correctness: read `experiments/reports/result_validation.md` before comparing runtime.
- Runtime: compare `Elapsed ms` in `experiments/reports/lab_suite.md` against the previous run.
- Result shape: read `experiments/reports/result_comparison.md` for alias, timestamp, and column-shape differences.
- Database fit: write one sentence for each engine explaining whether this run made it easier or harder to use.
- Next variable: choose only one for the following run: row count, user cardinality, stream rate, storage format, or service availability.

experiments/reports/query_catalog.md detail report

# DB Lab Query Catalog

Source SQL root: `sql`

This catalog turns the real SQL and DolphinDB scripts into a reviewable learning map.
Use it as the personal SQL/script handbook for the lab.

## How To Use

1. Pick one query group and read the scripts side by side.
2. Run `db-lab compare-results` and `db-lab validate-results --strict` before interpreting timing.
3. Change one script in a scratch branch and regenerate this catalog to see which concept changed.

## Query Groups

| Scenario | Query | Goal | Engines | Files | Concepts |
| --- | --- | --- | --- | --- | --- |
| Market ticks | Data profile | Check row count, symbol count, time range, and total volume. | duckdb, clickhouse, dolphindb | `sql/duckdb/market_ticks/00_profile.sql`<br>`sql/clickhouse/market_ticks/00_profile.sql`<br>`sql/dolphindb/market_ticks/00_profile.dos` | Distinct counting, Local file scan, Service table, VWAP |
| Market ticks | Symbol VWAP | Aggregate trades by symbol and exchange, then calculate VWAP and price bounds. | duckdb, clickhouse, dolphindb | `sql/duckdb/market_ticks/01_symbol_vwap.sql`<br>`sql/clickhouse/market_ticks/01_symbol_vwap.sql`<br>`sql/dolphindb/market_ticks/01_symbol_vwap.dos` | Local file scan, Service table, VWAP |
| Market ticks | Minute OHLC bars | Convert ticks into one-minute OHLC bars with volume. | duckdb, clickhouse, dolphindb | `sql/duckdb/market_ticks/02_minute_bars.sql`<br>`sql/clickhouse/market_ticks/02_minute_bars.sql`<br>`sql/dolphindb/market_ticks/02_minute_bars.dos` | Local file scan, Ordered open and close, Service table, Time bucketing, VWAP |
| Ecommerce events | Data profile | Check event, user, session, time range, and revenue totals. | duckdb, clickhouse | `sql/duckdb/ecommerce_events/00_profile.sql`<br>`sql/clickhouse/ecommerce_events/00_profile.sql` | Distinct counting, Local file scan, Service table |
| Ecommerce events | Session funnel | Compare sessions reaching page view, product view, cart, checkout, and purchase. | duckdb, clickhouse | `sql/duckdb/ecommerce_events/01_funnel.sql`<br>`sql/clickhouse/ecommerce_events/01_funnel.sql` | Distinct counting, Join diagnostics, Local file scan, Service table, Session funnel |
| Ecommerce events | Revenue segments | Aggregate users, purchases, revenue, and average order value by country and channel. | duckdb, clickhouse | `sql/duckdb/ecommerce_events/02_revenue_segments.sql`<br>`sql/clickhouse/ecommerce_events/02_revenue_segments.sql` | Anomaly scoring, Distinct counting, Local file scan, Service table |
| Ecommerce events | Top products | Rank products by purchased units, revenue, and distinct buyers. | duckdb, clickhouse | `sql/duckdb/ecommerce_events/03_top_products.sql`<br>`sql/clickhouse/ecommerce_events/03_top_products.sql` | Distinct counting, Local file scan, Service table |
| Ecommerce events | Materialized revenue | Inspect the ClickHouse minute-level revenue aggregate maintained during inserts. | clickhouse | `sql/clickhouse/ecommerce_events/04_materialized_revenue.sql` | Materialized aggregation, Service table |
| IoT metrics | Data profile | Check metric rows, devices, alerts, time range, and average value. | duckdb, clickhouse, dolphindb | `sql/duckdb/iot_metrics/00_profile.sql`<br>`sql/clickhouse/iot_metrics/00_profile.sql`<br>`sql/dolphindb/iot_metrics/00_profile.dos` | Distinct counting, Local file scan, Service table |
| IoT metrics | Metric windows | Aggregate device metrics by minute, region, and metric name. | duckdb, clickhouse, dolphindb | `sql/duckdb/iot_metrics/01_metric_windows.sql`<br>`sql/clickhouse/iot_metrics/01_metric_windows.sql`<br>`sql/dolphindb/iot_metrics/01_metric_windows.dos` | Local file scan, Service table, Time bucketing |
| IoT metrics | Alert join | Join alerts back to the triggering metric rows and compare alert pressure by region. | duckdb, clickhouse, dolphindb | `sql/duckdb/iot_metrics/02_alert_join.sql`<br>`sql/clickhouse/iot_metrics/02_alert_join.sql`<br>`sql/dolphindb/iot_metrics/02_alert_join.dos` | Join diagnostics, Service table |
| IoT metrics | Device health | Rank devices by anomaly points and peak CPU or temperature. | duckdb, clickhouse, dolphindb | `sql/duckdb/iot_metrics/03_device_health.sql`<br>`sql/clickhouse/iot_metrics/03_device_health.sql`<br>`sql/dolphindb/iot_metrics/03_device_health.dos` | Anomaly scoring, Local file scan, Service table |
| Log observability | Data profile | Check log rows, traces, services, span count, errors, and latency baseline. | duckdb, clickhouse | `sql/duckdb/log_observability/00_profile.sql`<br>`sql/clickhouse/log_observability/00_profile.sql` | Anomaly scoring, Distinct counting, Local file scan, Observability diagnostics, Service table |
| Log observability | Error rate | Compare error and warning pressure by service. | duckdb, clickhouse | `sql/duckdb/log_observability/01_error_rate.sql`<br>`sql/clickhouse/log_observability/01_error_rate.sql` | Anomaly scoring, Local file scan, Observability diagnostics, Service table |
| Log observability | Latency percentiles | Rank services by median, P95, and maximum latency. | duckdb, clickhouse | `sql/duckdb/log_observability/02_latency_percentiles.sql`<br>`sql/clickhouse/log_observability/02_latency_percentiles.sql` | Latency percentiles, Local file scan, Observability diagnostics, Service table |
| Log observability | Trace hotspots | Join logs with trace spans to find slow or error-heavy traces. | duckdb, clickhouse | `sql/duckdb/log_observability/03_trace_hotspots.sql`<br>`sql/clickhouse/log_observability/03_trace_hotspots.sql` | Anomaly scoring, Join diagnostics, Local file scan, Observability diagnostics, Service table |
| DolphinDB streaming | Stream pipeline setup | Create a shared tick stream, time-series engine, output table, and local subscription. | dolphindb | `sql/dolphindb/market_stream/setup.dos` | Ordered open and close, Stream processing, Time bucketing, VWAP |
| DolphinDB streaming | Historical replay | Replay sorted historical ticks into the shared stream table at a chosen rate. | dolphindb | `sql/dolphindb/market_stream/replay_csv.dos` | CSV import with schema, Stream processing |
| DolphinDB streaming | Realtime minute bars | Inspect OHLC, volume, and VWAP emitted by the time-series stream engine. | dolphindb | `sql/dolphindb/market_stream/01_realtime_minute_bars.dos` | Ordered open and close, Stream processing, VWAP |

## Engine Script Inventory

| Engine | Dataset | Script | Lines | Concepts |
| --- | --- | --- | ---: | --- |
| clickhouse | ecommerce_events | `sql/clickhouse/ecommerce_events/00_profile.sql` | 8 | Service table, Distinct counting |
| clickhouse | ecommerce_events | `sql/clickhouse/ecommerce_events/01_funnel.sql` | 20 | Service table, Distinct counting, Session funnel |
| clickhouse | ecommerce_events | `sql/clickhouse/ecommerce_events/02_revenue_segments.sql` | 11 | Service table, Distinct counting, Anomaly scoring |
| clickhouse | ecommerce_events | `sql/clickhouse/ecommerce_events/03_top_products.sql` | 12 | Service table, Distinct counting |
| clickhouse | ecommerce_events | `sql/clickhouse/ecommerce_events/04_materialized_revenue.sql` | 10 | Service table, Materialized aggregation |
| clickhouse | ecommerce_events | `sql/clickhouse/ecommerce_events/schema.sql` | 47 | Service table, Columnar storage layout, Materialized aggregation, Time bucketing |
| clickhouse | iot_metrics | `sql/clickhouse/iot_metrics/00_profile.sql` | 8 | Service table, Distinct counting |
| clickhouse | iot_metrics | `sql/clickhouse/iot_metrics/01_metric_windows.sql` | 11 | Service table, Time bucketing |
| clickhouse | iot_metrics | `sql/clickhouse/iot_metrics/02_alert_join.sql` | 12 | Service table, Join diagnostics |
| clickhouse | iot_metrics | `sql/clickhouse/iot_metrics/03_device_health.sql` | 16 | Service table, Anomaly scoring |
| clickhouse | iot_metrics | `sql/clickhouse/iot_metrics/schema.sql` | 27 | Service table, Columnar storage layout |
| clickhouse | log_observability | `sql/clickhouse/log_observability/00_profile.sql` | 10 | Service table, Distinct counting, Anomaly scoring, Observability diagnostics |
| clickhouse | log_observability | `sql/clickhouse/log_observability/01_error_rate.sql` | 11 | Service table, Anomaly scoring, Observability diagnostics |
| clickhouse | log_observability | `sql/clickhouse/log_observability/02_latency_percentiles.sql` | 9 | Service table, Observability diagnostics, Latency percentiles |
| clickhouse | log_observability | `sql/clickhouse/log_observability/03_trace_hotspots.sql` | 31 | Service table, Join diagnostics, Anomaly scoring, Observability diagnostics |
| clickhouse | log_observability | `sql/clickhouse/log_observability/schema.sql` | 25 | Service table, Columnar storage layout, Observability diagnostics |
| clickhouse | market_ticks | `sql/clickhouse/market_ticks/00_profile.sql` | 7 | Service table, VWAP, Distinct counting |
| clickhouse | market_ticks | `sql/clickhouse/market_ticks/01_symbol_vwap.sql` | 11 | Service table, VWAP |
| clickhouse | market_ticks | `sql/clickhouse/market_ticks/02_minute_bars.sql` | 12 | Service table, Time bucketing, Ordered open and close, VWAP |
| clickhouse | market_ticks | `sql/clickhouse/market_ticks/schema.sql` | 15 | Service table, Columnar storage layout |
| dolphindb | iot_metrics | `sql/dolphindb/iot_metrics/00_profile.dos` | 19 | Service table, Distinct counting |
| dolphindb | iot_metrics | `sql/dolphindb/iot_metrics/01_metric_windows.dos` | 18 | Service table, Time bucketing |
| dolphindb | iot_metrics | `sql/dolphindb/iot_metrics/02_alert_join.dos` | 15 | Service table, Join diagnostics |
| dolphindb | iot_metrics | `sql/dolphindb/iot_metrics/03_device_health.dos` | 20 | Service table, Anomaly scoring |
| dolphindb | iot_metrics | `sql/dolphindb/iot_metrics/import_csv.dos` | 46 | Service table, CSV import with schema |
| dolphindb | iot_metrics | `sql/dolphindb/iot_metrics/schema.dos` | 33 | Service table, Columnar storage layout |
| dolphindb | market_stream | `sql/dolphindb/market_stream/00_stream_status.dos` | 13 | Distinct counting, Stream processing |
| dolphindb | market_stream | `sql/dolphindb/market_stream/01_realtime_minute_bars.dos` | 12 | Ordered open and close, VWAP, Stream processing |
| dolphindb | market_stream | `sql/dolphindb/market_stream/cleanup.dos` | 26 | Stream processing |
| dolphindb | market_stream | `sql/dolphindb/market_stream/replay_csv.dos` | 43 | Stream processing, CSV import with schema |
| dolphindb | market_stream | `sql/dolphindb/market_stream/setup.dos` | 40 | Time bucketing, Ordered open and close, VWAP, Stream processing |
| dolphindb | market_ticks | `sql/dolphindb/market_ticks/00_profile.dos` | 16 | Service table, VWAP, Distinct counting |
| dolphindb | market_ticks | `sql/dolphindb/market_ticks/01_symbol_vwap.dos` | 11 | Service table, VWAP |
| dolphindb | market_ticks | `sql/dolphindb/market_ticks/02_minute_bars.dos` | 19 | Service table, Time bucketing, Ordered open and close, VWAP |
| dolphindb | market_ticks | `sql/dolphindb/market_ticks/import_csv.dos` | 23 | Service table, CSV import with schema |
| dolphindb | market_ticks | `sql/dolphindb/market_ticks/schema.dos` | 21 | Service table, Columnar storage layout |
| duckdb | ecommerce_events | `sql/duckdb/ecommerce_events/00_profile.sql` | 8 | Local file scan, Distinct counting |
| duckdb | ecommerce_events | `sql/duckdb/ecommerce_events/01_funnel.sql` | 31 | Local file scan, Distinct counting, Join diagnostics, Session funnel |
| duckdb | ecommerce_events | `sql/duckdb/ecommerce_events/02_revenue_segments.sql` | 11 | Local file scan, Distinct counting, Anomaly scoring |
| duckdb | ecommerce_events | `sql/duckdb/ecommerce_events/03_top_products.sql` | 12 | Local file scan, Distinct counting |
| duckdb | iot_metrics | `sql/duckdb/iot_metrics/00_profile.sql` | 8 | Local file scan, Distinct counting |
| duckdb | iot_metrics | `sql/duckdb/iot_metrics/01_metric_windows.sql` | 11 | Local file scan, Time bucketing |
| duckdb | iot_metrics | `sql/duckdb/iot_metrics/02_alert_join.sql` | 12 | Join diagnostics |
| duckdb | iot_metrics | `sql/duckdb/iot_metrics/03_device_health.sql` | 19 | Local file scan, Anomaly scoring |
| duckdb | log_observability | `sql/duckdb/log_observability/00_profile.sql` | 10 | Local file scan, Distinct counting, Observability diagnostics |
| duckdb | log_observability | `sql/duckdb/log_observability/01_error_rate.sql` | 11 | Local file scan, Anomaly scoring, Observability diagnostics |
| duckdb | log_observability | `sql/duckdb/log_observability/02_latency_percentiles.sql` | 9 | Local file scan, Observability diagnostics, Latency percentiles |
| duckdb | log_observability | `sql/duckdb/log_observability/03_trace_hotspots.sql` | 31 | Local file scan, Join diagnostics, Anomaly scoring, Observability diagnostics |
| duckdb | market_ticks | `sql/duckdb/market_ticks/00_profile.sql` | 7 | Local file scan, VWAP, Distinct counting |
| duckdb | market_ticks | `sql/duckdb/market_ticks/01_symbol_vwap.sql` | 11 | Local file scan, VWAP |
| duckdb | market_ticks | `sql/duckdb/market_ticks/02_minute_bars.sql` | 12 | Local file scan, Time bucketing, Ordered open and close, VWAP |

## Concept Index

| Concept | Why it matters | DuckDB | ClickHouse | DolphinDB |
| --- | --- | --- | --- | --- |
| Local file scan | DuckDB can query local files directly without a service import step. | `sql/duckdb/ecommerce_events/00_profile.sql`<br>`sql/duckdb/ecommerce_events/01_funnel.sql`<br>`sql/duckdb/ecommerce_events/02_revenue_segments.sql`<br>+11 more |  |  |
| Service table | ClickHouse and DolphinDB experiments query tables loaded into a running service. |  | `sql/clickhouse/ecommerce_events/00_profile.sql`<br>`sql/clickhouse/ecommerce_events/01_funnel.sql`<br>`sql/clickhouse/ecommerce_events/02_revenue_segments.sql`<br>+17 more | `sql/dolphindb/iot_metrics/00_profile.dos`<br>`sql/dolphindb/iot_metrics/01_metric_windows.dos`<br>`sql/dolphindb/iot_metrics/02_alert_join.dos`<br>+8 more |
| Columnar storage layout | Sorting, partitioning, and typed columns decide how service engines store and scan data. |  | `sql/clickhouse/ecommerce_events/schema.sql`<br>`sql/clickhouse/iot_metrics/schema.sql`<br>`sql/clickhouse/log_observability/schema.sql`<br>+1 more | `sql/dolphindb/iot_metrics/schema.dos`<br>`sql/dolphindb/market_ticks/schema.dos` |
| Materialized aggregation | ClickHouse can maintain aggregate tables during ingestion instead of recalculating every query. |  | `sql/clickhouse/ecommerce_events/04_materialized_revenue.sql`<br>`sql/clickhouse/ecommerce_events/schema.sql` |  |
| Time bucketing | Time-series questions depend on each engine's way to convert events into fixed windows. | `sql/duckdb/iot_metrics/01_metric_windows.sql`<br>`sql/duckdb/market_ticks/02_minute_bars.sql` | `sql/clickhouse/ecommerce_events/schema.sql`<br>`sql/clickhouse/iot_metrics/01_metric_windows.sql`<br>`sql/clickhouse/market_ticks/02_minute_bars.sql` | `sql/dolphindb/iot_metrics/01_metric_windows.dos`<br>`sql/dolphindb/market_stream/setup.dos`<br>`sql/dolphindb/market_ticks/02_minute_bars.dos` |
| Ordered open and close | OHLC bars need first and last prices by event time, not arbitrary aggregate order. | `sql/duckdb/market_ticks/02_minute_bars.sql` | `sql/clickhouse/market_ticks/02_minute_bars.sql` | `sql/dolphindb/market_stream/01_realtime_minute_bars.dos`<br>`sql/dolphindb/market_stream/setup.dos`<br>`sql/dolphindb/market_ticks/02_minute_bars.dos` |
| VWAP | VWAP is the shared business metric that proves arithmetic results can match across engines. | `sql/duckdb/market_ticks/00_profile.sql`<br>`sql/duckdb/market_ticks/01_symbol_vwap.sql`<br>`sql/duckdb/market_ticks/02_minute_bars.sql` | `sql/clickhouse/market_ticks/00_profile.sql`<br>`sql/clickhouse/market_ticks/01_symbol_vwap.sql`<br>`sql/clickhouse/market_ticks/02_minute_bars.sql` | `sql/dolphindb/market_stream/01_realtime_minute_bars.dos`<br>`sql/dolphindb/market_stream/setup.dos`<br>`sql/dolphindb/market_ticks/00_profile.dos`<br>+2 more |
| Distinct counting | User, session, and symbol counts reveal function differences between SQL dialects. | `sql/duckdb/ecommerce_events/00_profile.sql`<br>`sql/duckdb/ecommerce_events/01_funnel.sql`<br>`sql/duckdb/ecommerce_events/02_revenue_segments.sql`<br>+4 more | `sql/clickhouse/ecommerce_events/00_profile.sql`<br>`sql/clickhouse/ecommerce_events/01_funnel.sql`<br>`sql/clickhouse/ecommerce_events/02_revenue_segments.sql`<br>+4 more | `sql/dolphindb/iot_metrics/00_profile.dos`<br>`sql/dolphindb/market_stream/00_stream_status.dos`<br>`sql/dolphindb/market_ticks/00_profile.dos` |
| Join diagnostics | Operational investigations often need to join events back to metrics or dimensions. | `sql/duckdb/ecommerce_events/01_funnel.sql`<br>`sql/duckdb/iot_metrics/02_alert_join.sql`<br>`sql/duckdb/log_observability/03_trace_hotspots.sql` | `sql/clickhouse/iot_metrics/02_alert_join.sql`<br>`sql/clickhouse/log_observability/03_trace_hotspots.sql` | `sql/dolphindb/iot_metrics/02_alert_join.dos` |
| Anomaly scoring | IoT and operations data often becomes useful after threshold or health-score rules are encoded. | `sql/duckdb/ecommerce_events/02_revenue_segments.sql`<br>`sql/duckdb/iot_metrics/03_device_health.sql`<br>`sql/duckdb/log_observability/01_error_rate.sql`<br>+1 more | `sql/clickhouse/ecommerce_events/02_revenue_segments.sql`<br>`sql/clickhouse/iot_metrics/03_device_health.sql`<br>`sql/clickhouse/log_observability/00_profile.sql`<br>+2 more | `sql/dolphindb/iot_metrics/03_device_health.dos` |
| Session funnel | Event-step analysis shows how each OLAP engine expresses ordered business funnels. | `sql/duckdb/ecommerce_events/01_funnel.sql` | `sql/clickhouse/ecommerce_events/01_funnel.sql` |  |
| Stream processing | DolphinDB's stream tables and time-series engine turn replayed ticks into realtime bars. |  |  | `sql/dolphindb/market_stream/00_stream_status.dos`<br>`sql/dolphindb/market_stream/01_realtime_minute_bars.dos`<br>`sql/dolphindb/market_stream/cleanup.dos`<br>+2 more |
| CSV import with schema | Service-backed ingestion needs explicit type control and a server-visible file path. |  |  | `sql/dolphindb/iot_metrics/import_csv.dos`<br>`sql/dolphindb/market_stream/replay_csv.dos`<br>`sql/dolphindb/market_ticks/import_csv.dos` |
| Observability diagnostics | Logs and traces become useful when error pressure, latency, and trace hotspots are made queryable. | `sql/duckdb/log_observability/00_profile.sql`<br>`sql/duckdb/log_observability/01_error_rate.sql`<br>`sql/duckdb/log_observability/02_latency_percentiles.sql`<br>+1 more | `sql/clickhouse/log_observability/00_profile.sql`<br>`sql/clickhouse/log_observability/01_error_rate.sql`<br>`sql/clickhouse/log_observability/02_latency_percentiles.sql`<br>+2 more |  |
| Latency percentiles | Operational analysis usually compares tail latency, not only average latency. | `sql/duckdb/log_observability/02_latency_percentiles.sql` | `sql/clickhouse/log_observability/02_latency_percentiles.sql` |  |

## Practice Prompts

- DuckDB: find every local file scan and explain why no service import is needed.
- ClickHouse: trace `MergeTree`, `SummingMergeTree`, and materialized view scripts from schema to report.
- DolphinDB: trace batch TSDB scripts and stream replay scripts to explain when computation happens.
- Cross-engine: choose one shared metric, then map the matching functions and result-validation checks.

experiments/reports/result_comparison.md detail report

# DB Lab Result Comparison

This report compares the latest result tables emitted by DuckDB, ClickHouse, and DolphinDB.

## Source Reports

- Suite report: `experiments/reports/lab_suite.md`
- Reports directory: `experiments/reports`

## Suite Status

| Scenario | Engine | Mode | Status | Report |
| --- | --- | --- | --- | --- |
| market_ticks | duckdb | batch | passed | `experiments/reports/duckdb_market_ticks.md` |
| market_ticks | duckdb | storage | passed | `experiments/reports/duckdb_storage_formats.md` |
| market_ticks | duckdb | plans | passed | `experiments/reports/duckdb_query_plans.md` |
| market_ticks | duckdb | scale | passed | `experiments/reports/duckdb_scale.md` |
| market_ticks | clickhouse | batch | passed | `experiments/reports/clickhouse_market_ticks.md` |
| market_ticks | dolphindb | batch | passed | `experiments/reports/dolphindb_market_ticks.md` |
| market_ticks | dolphindb | stream | passed | `experiments/reports/dolphindb_market_stream.md` |
| ecommerce_events | duckdb | batch | passed | `experiments/reports/duckdb_ecommerce_events.md` |
| ecommerce_events | clickhouse | materialized | passed | `experiments/reports/clickhouse_ecommerce_events.md` |
| iot_metrics | duckdb | batch | passed | `experiments/reports/duckdb_iot_metrics.md` |
| iot_metrics | clickhouse | batch | passed | `experiments/reports/clickhouse_iot_metrics.md` |
| iot_metrics | dolphindb | batch | passed | `experiments/reports/dolphindb_iot_metrics.md` |

## Consistency Snapshots

### Market Ticks Profile

| Engine | Row count | Symbol count | Total volume | Time range |
| --- | ---: | ---: | ---: | --- |
| duckdb | 1000 | 6 | 4523300 | 2026-01-02 09:30:00 -> 2026-01-02 09:34:09.750000 |
| clickhouse | 1000 | 6 | 4523300 | 2026-01-02 09:30:00.000 -> 2026-01-02 09:34:09.750 |
| dolphindb | 1000 | 6 | 4523300 | 2026.01.02 09:30:00.000 -> 2026.01.02 09:34:09.750 |

### Ecommerce Profile

| Engine | Events | Users | Sessions | Gross revenue | Time range |
| --- | ---: | ---: | ---: | ---: | --- |
| duckdb | 1000 | 97 | 416 | 24649.0 | 2026-01-02 00:00:00 -> 2026-01-02 00:49:57 |
| clickhouse | 1000 | 97 | 416 | 24649 | 2026-01-02 00:00:00 -> 2026-01-02 00:49:57 |

### IoT Metrics Profile

| Engine | Metrics | Devices | Alerts | Avg value | Time range |
| --- | ---: | ---: | ---: | ---: | --- |
| duckdb | 2000 | 100 | 12 | 49.1586 | 2026-01-02 00:00:00 -> 2026-01-02 02:46:35 |
| clickhouse | 2000 | 100 | 12 | 49.1586 | 2026-01-02 00:00:00.000 -> 2026-01-02 02:46:35.000 |
| dolphindb | 2000 | 100 | 12 | 49.1586 | 2026.01.02 00:00:00.000 -> 2026.01.02 02:46:35.000 |

### DolphinDB Stream Replay

| Published ticks | Symbols | Generated bars | Time range |
| ---: | ---: | ---: | --- |
| 1000 | 6 | 24 | 2026.01.02 09:30:00.000 -> 2026.01.02 09:34:09.750 |

## Query Result Shapes

### Market Ticks Batch

#### 00_profile

| Engine | Elapsed ms | Columns | First row |
| --- | ---: | --- | --- |
| duckdb | 64.68 | `row_count, symbol_count, start_ts, end_ts, total_volume` | 1000 / 6 / 2026-01-02 09:30:00 / 2026-01-02 09:34:09.750000 / 4523300 |
| clickhouse | 4.36 | `row_count, symbol_count, start_ts, end_ts, total_volume` | 1000 / 6 / 2026-01-02 09:30:00.000 / 2026-01-02 09:34:09.750 / 4523300 |
| dolphindb | 7.24 | `row_count, symbol_count, start_ts, end_ts, total_volume` | 1000 / 6 / 2026.01.02 09:30:00.000 / 2026.01.02 09:34:09.750 / 4523300 |

#### 01_symbol_vwap

| Engine | Elapsed ms | Columns | First row |
| --- | ---: | --- | --- |
| duckdb | 64.35 | `symbol, exchange, trades, volume, vwap, low_price, high_price` | 0700.HK / HK / 177 / 914500 / 238.6874 / 233.5715 / 244.0305 |
| clickhouse | 4.59 | `symbol, exchange, trades, total_volume, vwap, low_price, high_price` | 0700.HK / HK / 177 / 914500 / 238.6874 / 233.5715 / 244.0305 |
| dolphindb | 3.29 | `sym, exchange, trades, volume, vwap, low_price, high_price` | 0700.HK / HK / 177 / 914500 / 238.6874 / 233.5715 / 244.0305 |

#### 02_minute_bars

| Engine | Elapsed ms | Columns | First row |
| --- | ---: | --- | --- |
| duckdb | 76.97 | `minute, symbol, open, high, low, close, volume` | 2026-01-02 09:30:00 / 000001.SZ / 13.3123 / 13.481 / 12.9775 / 13.1889 / 146400 |
| clickhouse | 4.79 | `minute, symbol, open, high, low, close, volume` | 2026-01-02 09:30:00 / 000001.SZ / 13.3123 / 13.481 / 12.9775 / 13.1889 / 146400 |
| dolphindb | 2.99 | `minute, sym, open, high, low, close, volume` | 2026.01.02 09:30:00.000 / 000001.SZ / 13.3123 / 13.481 / 12.9775 / 13.1889 / 146400 |

### Ecommerce Events Batch

#### 00_profile

| Engine | Elapsed ms | Columns | First row |
| --- | ---: | --- | --- |
| duckdb | 72.06 | `event_count, user_count, session_count, start_ts, end_ts, gross_revenue` | 1000 / 97 / 416 / 2026-01-02 00:00:00 / 2026-01-02 00:49:57 / 24649.0 |
| clickhouse | 4.92 | `event_count, user_count, session_count, start_ts, end_ts, gross_revenue` | 1000 / 97 / 416 / 2026-01-02 00:00:00 / 2026-01-02 00:49:57 / 24649 |

#### 01_funnel

| Engine | Elapsed ms | Columns | First row |
| --- | ---: | --- | --- |
| duckdb | 74.23 | `step_no, event_name, events, sessions, users, session_rate_pct` | 1 / page_view / 416 / 416 / 97 / 100.0 |
| clickhouse | 7.23 | `step_no, event_name, events, sessions, users, session_rate_pct` | 1 / page_view / 416 / 416 / 97 / 100 |

#### 02_revenue_segments

| Engine | Elapsed ms | Columns | First row |
| --- | ---: | --- | --- |
| duckdb | 72.78 | `country, channel, users, purchases, revenue, avg_order_value` | CN / search / 5 / 4 / 6194.0 / 1548.5 |
| clickhouse | 4.59 | `country, channel, users, purchases, revenue, avg_order_value` | CN / search / 5 / 4 / 6194 / 1548.5 |

#### 03_top_products

| Engine | Elapsed ms | Columns | First row |
| --- | ---: | --- | --- |
| duckdb | 73.36 | `sku_id, category, brand, units, revenue, buyers` | SKU-1002 / phone / Northstar / 8 / 7192.0 / 6 |
| clickhouse | 4.59 | `sku_id, category, brand, units, revenue, buyers` | SKU-1002 / phone / Northstar / 8 / 7192 / 6 |

#### 04_materialized_revenue

| Engine | Elapsed ms | Columns | First row |
| --- | ---: | --- | --- |
| clickhouse | 3.80 | `minute, country, channel, purchases, revenue` | 2026-01-02 00:00:00 / SG / ads / 1 / 318 |

### IoT Metrics Batch

#### 00_profile

| Engine | Elapsed ms | Columns | First row |
| --- | ---: | --- | --- |
| duckdb | 362.82 | `metric_count, device_count, alert_count, start_ts, end_ts, avg_value` | 2000 / 100 / 12 / 2026-01-02 00:00:00 / 2026-01-02 02:46:35 / 49.1586 |
| clickhouse | 6.89 | `metric_count, device_count, alert_count, start_ts, end_ts, avg_value` | 2000 / 100 / 12 / 2026-01-02 00:00:00.000 / 2026-01-02 02:46:35.000 / 49.1586 |
| dolphindb | 21.64 | `metric_count, device_count, alert_count, start_ts, end_ts, avg_value` | 2000 / 100 / 12 / 2026.01.02 00:00:00.000 / 2026.01.02 02:46:35.000 / 49.1586 |

#### 01_metric_windows

| Engine | Elapsed ms | Columns | First row |
| --- | ---: | --- | --- |
| duckdb | 120.98 | `minute, region, metric_name, samples, avg_value, max_value` | 2026-01-02 00:00:00 / ap-southeast / cpu_usage / 1 / 46.5074 / 46.5074 |
| clickhouse | 4.47 | `minute, region, metric_name, samples, avg_value, max_value` | 2026-01-02 00:00:00 / ap-southeast / cpu_usage / 1 / 46.5074 / 46.5074 |
| dolphindb | 7.96 | `minute, region, metric_name, samples, avg_value, max_value` | 2026.01.02 00:00:00.000 / ap-southeast / cpu_usage / 1 / 46.5074 / 46.5074 |

#### 02_alert_join

| Engine | Elapsed ms | Columns | First row |
| --- | ---: | --- | --- |
| duckdb | 121.54 | `severity, region, alerts, avg_metric_value` | warning / ap-southeast / 3 / 69.5528 |
| clickhouse | 6.49 | `severity, region, alerts, avg_metric_value` | warning / ap-southeast / 3 / 69.5528 |
| dolphindb | 9.73 | `severity, region, alerts, avg_metric_value` | warning / ap-southeast / 3 / 69.5528 |

#### 03_device_health

| Engine | Elapsed ms | Columns | First row |
| --- | ---: | --- | --- |
| duckdb | 121.88 | `device_id, region, samples, anomaly_points, max_temperature, max_cpu` | D00001 / us-east / 20 / 1 / 0.0 / 80.6596 |
| clickhouse | 5.69 | `device_id, region, samples, anomaly_points, max_temperature, max_cpu` | D00001 / us-east / 20 / 1 / 0 / 80.6596 |
| dolphindb | 9.44 | `device_id, region, samples, anomaly_points, max_temperature, max_cpu` | D00001 / us-east / 20 / 1 / 0 / 80.6596 |

### Log Observability Batch

#### 00_profile

| Engine | Elapsed ms | Columns | First row |
| --- | ---: | --- | --- |
| duckdb | 711.28 | `log_count, trace_count, service_count, span_count, error_count, avg_latency_ms, start_ts, end_ts` | 2000 / 666 / 6 / 2095 / 90 / 224.6 / 2026-01-02 00:00:00 / 2026-01-02 01:06:38 |
| clickhouse | 11.55 | `log_count, trace_count, service_count, span_count, error_count, avg_latency_ms, start_ts, end_ts` | 2000 / 666 / 6 / 2095 / 90 / 224.6 / 2026-01-02 00:00:00.000 / 2026-01-02 01:06:38.000 |

#### 01_error_rate

| Engine | Elapsed ms | Columns | First row |
| --- | ---: | --- | --- |
| duckdb | 118.84 | `service, log_count, error_count, warn_count, error_rate_pct, avg_latency_ms` | catalog / 333 / 21 / 41 / 6.31 / 143.96 |
| clickhouse | 4.74 | `service, log_count, error_count, warn_count, error_rate_pct, avg_latency_ms` | catalog / 333 / 21 / 41 / 6.31 / 143.96 |

#### 02_latency_percentiles

| Engine | Elapsed ms | Columns | First row |
| --- | ---: | --- | --- |
| duckdb | 117.28 | `service, log_count, p50_latency_ms, p95_latency_ms, max_latency_ms` | payment / 389 / 294.0 / 514.3999999999999 / 714 |
| clickhouse | 3.73 | `service, log_count, p50_latency_ms, p95_latency_ms, max_latency_ms` | payment / 389 / 294 / 516 / 714 |

#### 03_trace_hotspots

| Engine | Elapsed ms | Columns | First row |
| --- | ---: | --- | --- |
| duckdb | 131.75 | `trace_id, log_count, error_logs, span_count, max_log_latency_ms, total_span_duration_ms, max_span_duration_ms` | T04040000000525 / 3 / 2 / 4 / 590 / 377 / 144 |
| clickhouse | 7.34 | `trace_id, log_count, error_logs, span_count, max_log_latency_ms, total_span_duration_ms, max_span_duration_ms` | T04040000000525 / 3 / 2 / 4 / 590 / 377 / 144 |

### DolphinDB Stream

#### 00_stream_status

| Engine | Elapsed ms | Columns | First row |
| --- | ---: | --- | --- |
| dolphindb | 1.93 | `published_ticks, symbols, generated_bars, start_ts, end_ts` | 1000 / 6 / 24 / 2026.01.02 09:30:00.000 / 2026.01.02 09:34:09.750 |

#### 01_realtime_minute_bars

| Engine | Elapsed ms | Columns | First row |
| --- | ---: | --- | --- |
| dolphindb | 2.26 | `minute, sym, open, high, low, close, volume, vwap` | 2026.01.02 09:30:00.000 / 000001.SZ / 13.3123 / 13.481 / 12.9775 / 13.1889 / 146400 / 13.1481 |

#### replay_csv

| Engine | Elapsed ms | Columns | First row |
| --- | ---: | --- | --- |
| dolphindb | 1004.82 | `published_ticks, generated_bars` | 1000 / 24 |


## Study Notes

- DolphinDB market scripts use `sym` for the instrument column because `symbol` is also a DolphinDB type keyword in some expressions.
- ClickHouse market VWAP uses `total_volume` as the output alias to avoid aggregate alias substitution in newer ClickHouse versions.
- Timestamp text differs across engines: DuckDB/ClickHouse render ISO-like timestamps, while DolphinDB renders `yyyy.MM.dd` timestamps.
- Small runtime numbers are useful for smoke testing, but larger row counts are needed before drawing performance conclusions.

experiments/reports/result_validation.md detail report

# DB Lab Result Validation

This report validates business-result consistency across the latest experiment reports.

## Source Reports

- Reports directory: `experiments/reports`
- market/duckdb: `experiments/reports/duckdb_market_ticks.md` (found)
- market/clickhouse: `experiments/reports/clickhouse_market_ticks.md` (found)
- market/dolphindb: `experiments/reports/dolphindb_market_ticks.md` (found)
- ecommerce/duckdb: `experiments/reports/duckdb_ecommerce_events.md` (found)
- ecommerce/clickhouse: `experiments/reports/clickhouse_ecommerce_events.md` (found)
- iot/duckdb: `experiments/reports/duckdb_iot_metrics.md` (found)
- iot/clickhouse: `experiments/reports/clickhouse_iot_metrics.md` (found)
- iot/dolphindb: `experiments/reports/dolphindb_iot_metrics.md` (found)
- logs/duckdb: `experiments/reports/duckdb_log_observability.md` (found)
- logs/clickhouse: `experiments/reports/clickhouse_log_observability.md` (found)
- stream/dolphindb: `experiments/reports/dolphindb_market_stream.md` (found)

## Summary

| Status | Checks |
| --- | ---: |
| passed | 23 |
| failed | 0 |

## Validation Checks

| Check | Status | Scope | Baseline | Compared | Detail | Evidence |
| --- | --- | --- | --- | --- | --- | --- |
| Market profile | passed | market_ticks | duckdb | clickhouse | matched 1 row on row_count, symbol_count, start_ts, end_ts, total_volume | duckdb: `experiments/reports/duckdb_market_ticks.md`; clickhouse: `experiments/reports/clickhouse_market_ticks.md` |
| Market profile | passed | market_ticks | duckdb | dolphindb | matched 1 row on row_count, symbol_count, start_ts, end_ts, total_volume | duckdb: `experiments/reports/duckdb_market_ticks.md`; dolphindb: `experiments/reports/dolphindb_market_ticks.md` |
| Market symbol VWAP | passed | market_ticks | duckdb | clickhouse | matched 6 keyed rows on trades, volume, vwap, low_price, high_price | duckdb: `experiments/reports/duckdb_market_ticks.md`; clickhouse: `experiments/reports/clickhouse_market_ticks.md` |
| Market symbol VWAP | passed | market_ticks | duckdb | dolphindb | matched 6 keyed rows on trades, volume, vwap, low_price, high_price | duckdb: `experiments/reports/duckdb_market_ticks.md`; dolphindb: `experiments/reports/dolphindb_market_ticks.md` |
| Market minute bars | passed | market_ticks | duckdb | clickhouse | matched 24 keyed rows on open, high, low, close, volume | duckdb: `experiments/reports/duckdb_market_ticks.md`; clickhouse: `experiments/reports/clickhouse_market_ticks.md` |
| Market minute bars | passed | market_ticks | duckdb | dolphindb | matched 24 keyed rows on open, high, low, close, volume | duckdb: `experiments/reports/duckdb_market_ticks.md`; dolphindb: `experiments/reports/dolphindb_market_ticks.md` |
| Ecommerce profile | passed | ecommerce_events | duckdb | clickhouse | matched 1 row on event_count, user_count, session_count, start_ts, end_ts, gross_revenue | duckdb: `experiments/reports/duckdb_ecommerce_events.md`; clickhouse: `experiments/reports/clickhouse_ecommerce_events.md` |
| Ecommerce funnel | passed | ecommerce_events | duckdb | clickhouse | matched 5 keyed rows on events, sessions, users, session_rate_pct | duckdb: `experiments/reports/duckdb_ecommerce_events.md`; clickhouse: `experiments/reports/clickhouse_ecommerce_events.md` |
| Ecommerce revenue segments | passed | ecommerce_events | duckdb | clickhouse | matched 20 keyed rows on users, purchases, revenue, avg_order_value | duckdb: `experiments/reports/duckdb_ecommerce_events.md`; clickhouse: `experiments/reports/clickhouse_ecommerce_events.md` |
| Ecommerce top products | passed | ecommerce_events | duckdb | clickhouse | matched 8 keyed rows on category, brand, units, revenue, buyers | duckdb: `experiments/reports/duckdb_ecommerce_events.md`; clickhouse: `experiments/reports/clickhouse_ecommerce_events.md` |
| IoT profile | passed | iot_metrics | duckdb | clickhouse | matched 1 row on metric_count, device_count, alert_count, start_ts, end_ts, avg_value | duckdb: `experiments/reports/duckdb_iot_metrics.md`; clickhouse: `experiments/reports/clickhouse_iot_metrics.md` |
| IoT profile | passed | iot_metrics | duckdb | dolphindb | matched 1 row on metric_count, device_count, alert_count, start_ts, end_ts, avg_value | duckdb: `experiments/reports/duckdb_iot_metrics.md`; dolphindb: `experiments/reports/dolphindb_iot_metrics.md` |
| IoT metric windows | passed | iot_metrics | duckdb | clickhouse | matched 18 keyed rows on samples, avg_value, max_value | duckdb: `experiments/reports/duckdb_iot_metrics.md`; clickhouse: `experiments/reports/clickhouse_iot_metrics.md` |
| IoT metric windows | passed | iot_metrics | duckdb | dolphindb | matched 18 keyed rows on samples, avg_value, max_value | duckdb: `experiments/reports/duckdb_iot_metrics.md`; dolphindb: `experiments/reports/dolphindb_iot_metrics.md` |
| IoT alert join | passed | iot_metrics | duckdb | clickhouse | matched 4 keyed rows on alerts, avg_metric_value | duckdb: `experiments/reports/duckdb_iot_metrics.md`; clickhouse: `experiments/reports/clickhouse_iot_metrics.md` |
| IoT alert join | passed | iot_metrics | duckdb | dolphindb | matched 4 keyed rows on alerts, avg_metric_value | duckdb: `experiments/reports/duckdb_iot_metrics.md`; dolphindb: `experiments/reports/dolphindb_iot_metrics.md` |
| IoT device health | passed | iot_metrics | duckdb | clickhouse | matched 10 keyed rows on samples, anomaly_points, max_temperature, max_cpu | duckdb: `experiments/reports/duckdb_iot_metrics.md`; clickhouse: `experiments/reports/clickhouse_iot_metrics.md` |
| IoT device health | passed | iot_metrics | duckdb | dolphindb | matched 10 keyed rows on samples, anomaly_points, max_temperature, max_cpu | duckdb: `experiments/reports/duckdb_iot_metrics.md`; dolphindb: `experiments/reports/dolphindb_iot_metrics.md` |
| Log observability profile | passed | log_observability | duckdb | clickhouse | matched 1 row on log_count, trace_count, service_count, span_count, error_count, avg_latency_ms, start_ts, end_ts | duckdb: `experiments/reports/duckdb_log_observability.md`; clickhouse: `experiments/reports/clickhouse_log_observability.md` |
| Log observability error rate | passed | log_observability | duckdb | clickhouse | matched 6 keyed rows on log_count, error_count, warn_count, error_rate_pct, avg_latency_ms | duckdb: `experiments/reports/duckdb_log_observability.md`; clickhouse: `experiments/reports/clickhouse_log_observability.md` |
| Log observability trace hotspots | passed | log_observability | duckdb | clickhouse | matched 10 keyed rows on log_count, error_logs, span_count, max_log_latency_ms, total_span_duration_ms, max_span_duration_ms | duckdb: `experiments/reports/duckdb_log_observability.md`; clickhouse: `experiments/reports/clickhouse_log_observability.md` |
| DolphinDB stream status vs batch | passed | market_ticks_stream | dolphindb batch | dolphindb stream | matched published ticks, symbols, time range, and generated bar count | dolphindb: `experiments/reports/dolphindb_market_ticks.md`; dolphindb: `experiments/reports/dolphindb_market_stream.md` |
| DolphinDB stream bars vs batch | passed | market_ticks_stream | dolphindb batch | dolphindb stream | matched 24 keyed rows on open, high, low, close, volume | dolphindb: `experiments/reports/dolphindb_market_ticks.md`; dolphindb: `experiments/reports/dolphindb_market_stream.md` |

## Reading The Results

- `passed` means equivalent business outputs matched after normalizing aliases, timestamp rendering, and numeric precision.
- `failed` means the reports disagree or an expected query/report is missing; inspect the evidence files before comparing runtime.
- Runtime is intentionally not validated here. This report checks correctness first, then performance can be interpreted separately.

experiments/reports/run_comparison.md detail report

# DB Lab Run Comparison

This report compares the latest run with an archived run.

## Source Reports

- Current suite: `experiments/reports/lab_suite.md`
- Current validation: `experiments/reports/result_validation.md`
- History directory: `experiments/history`
- Compared archive: `experiments/history/20260607T042144Z`

## Summary

| Item | Archived | Current | Delta |
| --- | --- | --- | ---: |
| Market ticks rows | 1000 | 1000 | 0 |
| Ecommerce events rows | 1000 | 1000 | 0 |
| Ecommerce events users | 97 | 97 | 0 |
| Validation | 20 passed / 0 failed | 23 passed / 0 failed |  |

## Experiment Runtime And Status

| Scenario | Engine | Mode | Archived status | Current status | Archived ms | Current ms | Delta ms |
| --- | --- | --- | --- | --- | ---: | ---: | ---: |
| ecommerce_events | clickhouse | materialized | passed | passed | 58.71 | 58.13 | -0.58 |
| ecommerce_events | duckdb | batch | passed | passed | 380.05 | 398.22 | +18.17 |
| iot_metrics | clickhouse | batch | passed | passed | 170.76 | 49.73 | -121.03 |
| iot_metrics | dolphindb | batch | passed | passed | 124.42 | 112.76 | -11.66 |
| iot_metrics | duckdb | batch | passed | passed | 874.31 | 860.12 | -14.19 |
| market_ticks | clickhouse | batch | passed | passed | 62.83 | 57.30 | -5.53 |
| market_ticks | dolphindb | batch | passed | passed | 52.17 | 57.42 | +5.25 |
| market_ticks | dolphindb | stream | passed | passed | 1016.37 | 1016.40 | +0.03 |
| market_ticks | duckdb | batch | passed | passed | 311.33 | 331.95 | +20.62 |
| market_ticks | duckdb | plans | passed | passed | 195.69 | 197.48 | +1.79 |
| market_ticks | duckdb | scale | passed | passed | 1323.00 | 1370.59 | +47.59 |
| market_ticks | duckdb | storage | passed | passed | 1212.51 | 1232.12 | +19.61 |

## Interpretation Prompts

- If validation changed from `0 failed` to a non-zero failed count, inspect correctness before runtime.
- If rows increased but runtime barely changed, inspect query plans and service-side caching.
- If one service-backed engine slowed down more than DuckDB, compare ingestion, table engine, and network path.
- If current and archived row counts are identical, treat this as a reproducibility check rather than a scale test.

experiments/reports/run_history.md detail report

# DB Lab Run History

This report preserves prior experiment evidence before a new run overwrites current reports.

## Source

- History directory: `experiments/history`

## Archived Runs

| Archive | Label | Created at | Market rows | Ecommerce rows | Status | Validation | Reports | Dashboard |
| --- | --- | --- | ---: | ---: | --- | --- | ---: | --- |
| `experiments/history/20260606T183850Z-before-remote-all-m1000-e1000-u100-iot` | before-remote-all-m1000-e1000-u100-iot | 2026-06-06T18:38:50+00:00 | 1000 | 1000 | passed: 9 | 20 passed / 0 failed | 28 | `experiments/history/20260606T183850Z-before-remote-all-m1000-e1000-u100-iot/app/index.html` |
| `experiments/history/20260606T161315Z-before-remote-all-m1000-e1000-u100` | before-remote-all-m1000-e1000-u100 | 2026-06-06T16:13:15+00:00 | 1000 | 1000 | passed: 9 | 12 passed / 0 failed | 23 | `experiments/history/20260606T161315Z-before-remote-all-m1000-e1000-u100/app/index.html` |
| `experiments/history/20260606T130555Z-before-remote-all-m1000-e1000-u100` | before-remote-all-m1000-e1000-u100 | 2026-06-06T13:05:55+00:00 | 1000 | 1000 | passed: 9 | 12 passed / 0 failed | 23 | `experiments/history/20260606T130555Z-before-remote-all-m1000-e1000-u100/app/index.html` |
| `experiments/history/20260606T125004Z-before-remote-all-m1000-e1000-u100` | before-remote-all-m1000-e1000-u100 | 2026-06-06T12:50:04+00:00 | 1000 | 1000 | passed: 9 | 12 passed / 0 failed | 22 | `experiments/history/20260606T125004Z-before-remote-all-m1000-e1000-u100/app/index.html` |
| `experiments/history/20260606T123624Z-before-remote-all-m1000-e1000-u100` | before-remote-all-m1000-e1000-u100 | 2026-06-06T12:36:24+00:00 | 1000 | 1000 | passed: 9 | 12 passed / 0 failed | 21 | `experiments/history/20260606T123624Z-before-remote-all-m1000-e1000-u100/app/index.html` |
| `experiments/history/20260606T122053Z-before-remote-all-m1000-e1000-u100` | before-remote-all-m1000-e1000-u100 | 2026-06-06T12:20:53+00:00 | 1000 | 1000 | passed: 9 | 12 passed / 0 failed | 20 | `experiments/history/20260606T122053Z-before-remote-all-m1000-e1000-u100/app/index.html` |
| `experiments/history/20260606T121756Z-before-remote-all-m1000-e1000-u100` | before-remote-all-m1000-e1000-u100 | 2026-06-06T12:17:56+00:00 | 1000 | 1000 | passed: 9 | 12 passed / 0 failed | 20 | `experiments/history/20260606T121756Z-before-remote-all-m1000-e1000-u100/app/index.html` |
| `experiments/history/20260606T113105Z-baseline-1k` | baseline-1k | 2026-06-06T11:31:05+00:00 | 1000 | 1000 | passed: 9 | 12 passed / 0 failed | 18 | `experiments/history/20260606T113105Z-baseline-1k/app/index.html` |

## Practice Use

- Compare the latest `experiments/reports/lab_suite.md` with the most recent archive after each controlled run.
- Treat `result_validation` as the correctness gate before interpreting elapsed time changes.
- Keep the label short, for example `baseline-1k`, `scale-5k`, or `stream-rate-1000`.

experiments/reports/selection_memo.md detail report

# DB Lab Selection Memo

This memo converts the latest lab evidence into database-selection rules you can reuse.

## Evidence Sources

- Suite report: `experiments/reports/lab_suite.md`
- Capability matrix: `experiments/reports/capability_matrix.md`
- Result validation: `experiments/reports/result_validation.md`
- Query catalog: `experiments/reports/query_catalog.md`

## Current Readiness

| Engine | Lab role | Capabilities passed | Service gaps | Scripts | Concepts | Correctness signal |
| --- | --- | ---: | ---: | ---: | --- | --- |
| duckdb | Embedded local analytics over CSV/Parquet and fast exploratory SQL. | 7 / 7 | 0 | 15 | Anomaly scoring, Distinct counting, Join diagnostics, Latency percentiles, Local file scan, Observability diagnostics, Ordered open and close, Session funnel, Time bucketing, VWAP | 23 passed / 0 failed |
| clickhouse | Service OLAP over ingested event tables, MergeTree layouts, and materialized aggregation. | 4 / 4 | 0 | 20 | Anomaly scoring, Columnar storage layout, Distinct counting, Join diagnostics, Latency percentiles, Materialized aggregation, Observability diagnostics, Ordered open and close, Service table, Session funnel, Time bucketing, VWAP | 23 passed / 0 failed |
| dolphindb | Time-series database and stream replay platform for market-style data. | 3 / 3 | 0 | 16 | Anomaly scoring, CSV import with schema, Columnar storage layout, Distinct counting, Join diagnostics, Ordered open and close, Service table, Stream processing, Time bucketing, VWAP | 23 passed / 0 failed |

## Scenario Decision Matrix

| Situation | Default choice | Why this engine fits | Evidence to reread | Watch-outs |
| --- | --- | --- | --- | --- |
| Local one-off file analytics, CSV/Parquet comparison, quick SQL exploration | DuckDB | Embedded engine with direct file scans and local plan inspection. | Embedded file analytics: `sql/duckdb/market_ticks/00_profile.sql`, `sql/duckdb/market_ticks/01_symbol_vwap.sql`, `sql/duckdb/market_ticks/02_minute_bars.sql`, `experiments/reports/duckdb_market_ticks.md`; CSV vs Parquet storage: `experiments/reports/duckdb_storage_formats.md`, `docs/duckdb-storage.md`; Query plan inspection: `experiments/reports/duckdb_query_plans.md`, `src/db_lab/experiments/duckdb_query_plans.py` | Add a service only when repeated ingestion or sharing matters. |
| Product/event analytics, recurring aggregations, materialized revenue rollups | ClickHouse | Server OLAP with MergeTree storage and materialized aggregation evidence. | MergeTree market OLAP: `sql/clickhouse/market_ticks/schema.sql`, `sql/clickhouse/market_ticks/00_profile.sql`, `sql/clickhouse/market_ticks/01_symbol_vwap.sql`, `sql/clickhouse/market_ticks/02_minute_bars.sql`, `docs/service-runbook.md`; Materialized ecommerce aggregation: `sql/clickhouse/ecommerce_events/schema.sql`, `sql/clickhouse/ecommerce_events/01_funnel.sql`, `sql/clickhouse/ecommerce_events/04_materialized_revenue.sql`, `docs/service-runbook.md` | Needs Docker/VPS service operations and ingestion management. |
| Market ticks, time buckets, OHLC/VWAP, stream replay matching batch results | DolphinDB | TSDB tables and stream engine are represented by batch and realtime-bar experiments. | TSDB market time-series batch: `sql/dolphindb/market_ticks/schema.dos`, `sql/dolphindb/market_ticks/import_csv.dos`, `sql/dolphindb/market_ticks/00_profile.dos`, `sql/dolphindb/market_ticks/01_symbol_vwap.dos`, `sql/dolphindb/market_ticks/02_minute_bars.dos`, `docs/service-runbook.md`; Stream replay and realtime bars: `sql/dolphindb/market_stream/setup.dos`, `sql/dolphindb/market_stream/replay_csv.dos`, `sql/dolphindb/market_stream/01_realtime_minute_bars.dos`, `docs/dolphindb-streaming.md`, `docs/service-runbook.md` | Use it when time-series or streaming is central, not just because the data has timestamps. |
| Cross-engine learning or benchmark interpretation | All engines with DuckDB as the local baseline | Validation proves business results match before runtime observations are compared. | `experiments/reports/result_validation.md`, `experiments/reports/query_catalog.md` | Keep result correctness separate from performance preference. |

## Current Suite Evidence

| Scenario | Engine | Mode | Status | Elapsed ms | Report |
| --- | --- | --- | --- | ---: | --- |
| market_ticks | duckdb | batch | passed | 331.95 | `experiments/reports/duckdb_market_ticks.md` |
| market_ticks | duckdb | storage | passed | 1232.12 | `experiments/reports/duckdb_storage_formats.md` |
| market_ticks | duckdb | plans | passed | 197.48 | `experiments/reports/duckdb_query_plans.md` |
| market_ticks | duckdb | scale | passed | 1370.59 | `experiments/reports/duckdb_scale.md` |
| market_ticks | clickhouse | batch | passed | 57.30 | `experiments/reports/clickhouse_market_ticks.md` |
| market_ticks | dolphindb | batch | passed | 57.42 | `experiments/reports/dolphindb_market_ticks.md` |
| market_ticks | dolphindb | stream | passed | 1016.40 | `experiments/reports/dolphindb_market_stream.md` |
| ecommerce_events | duckdb | batch | passed | 398.22 | `experiments/reports/duckdb_ecommerce_events.md` |
| ecommerce_events | clickhouse | materialized | passed | 58.13 | `experiments/reports/clickhouse_ecommerce_events.md` |
| iot_metrics | duckdb | batch | passed | 860.12 | `experiments/reports/duckdb_iot_metrics.md` |
| iot_metrics | clickhouse | batch | passed | 49.73 | `experiments/reports/clickhouse_iot_metrics.md` |
| iot_metrics | dolphindb | batch | passed | 112.76 | `experiments/reports/dolphindb_iot_metrics.md` |

## Selection Rules To Internalize

- Choose DuckDB first when the data already lives in local files and the work is exploratory, reproducible, or Python-adjacent.
- Choose ClickHouse when the data should live in a shared service and the recurring workload is high-throughput OLAP, event analytics, or materialized aggregation.
- Choose DolphinDB when the business question is naturally time-series oriented and especially when batch results must line up with stream replay or realtime windowing.
- Use result validation before timing comparisons; a faster query with mismatched business output is not a valid win.

## Avoid These Misuses

- Do not choose ClickHouse just because a query is SQL; use it when service ingestion, shared access, or repeated OLAP justify the operational cost.
- Do not choose DuckDB for a workload that needs continuous multi-user ingestion or always-on serving unless an outer application provides that layer.
- Do not choose DolphinDB for generic event funnels before checking whether the time-series or stream-processing model is actually central to the problem.
- Do not compare runtimes across engines until the validation report says the result shape and business metrics match.

## Practice Cases

- A data scientist receives one CSV and needs a same-day answer: start with DuckDB and record whether Parquet improves the follow-up run.
- A product team asks for a dashboard over continuously arriving clickstream events: model it in ClickHouse and inspect the materialized revenue aggregate.
- A trading workflow needs historical ticks replayed through realtime bar logic: use DolphinDB batch and stream reports together.
- A stakeholder asks which engine is best: answer with workload shape, operational constraints, and the current evidence table instead of a single global ranking.

experiments/reports/study_guide.md detail report

# DB Lab Study Guide

This guide turns the latest lab evidence into a practical study plan.

## Source Reports

- Capability matrix: `experiments/reports/capability_matrix.md`
- Result comparison: `experiments/reports/result_comparison.md`
- Result validation: `experiments/reports/result_validation.md` (available)

## Current Coverage

| Engine | Capabilities | Passed | Needs service | Missing / planned |
| --- | ---: | ---: | ---: | ---: |
| duckdb | 7 | 7 | 0 | 0 |
| clickhouse | 4 | 4 | 0 | 0 |
| dolphindb | 3 | 3 | 0 | 0 |

## Engine Study Map

### duckdb

| Capability | Status | Study focus | Evidence | Next action |
| --- | --- | --- | --- | --- |
| Embedded file analytics | passed | Direct CSV scans, local OLAP, VWAP, and minute bars. | `sql/duckdb/market_ticks/00_profile.sql`, `sql/duckdb/market_ticks/01_symbol_vwap.sql`, `sql/duckdb/market_ticks/02_minute_bars.sql`, `experiments/reports/duckdb_market_ticks.md` | Change row count and compare query timing. |
| Ecommerce funnel analytics | passed | Session funnels, revenue segmentation, and Top product queries. | `sql/duckdb/ecommerce_events/00_profile.sql`, `sql/duckdb/ecommerce_events/01_funnel.sql`, `sql/duckdb/ecommerce_events/02_revenue_segments.sql`, `sql/duckdb/ecommerce_events/03_top_products.sql`, `experiments/reports/duckdb_ecommerce_events.md` | Add a cohort or retention query. |
| IoT offline diagnostics | passed | Local device metrics, time-window aggregates, alert joins, and anomaly ranking. | `sql/duckdb/iot_metrics/00_profile.sql`, `sql/duckdb/iot_metrics/01_metric_windows.sql`, `sql/duckdb/iot_metrics/02_alert_join.sql`, `sql/duckdb/iot_metrics/03_device_health.sql`, `experiments/reports/duckdb_iot_metrics.md` | Change device count and compare the alert join with service-backed engines. |
| Log observability triage | passed | Local log and trace triage with error-rate, latency percentile, and trace hotspot queries. | `sql/duckdb/log_observability/00_profile.sql`, `sql/duckdb/log_observability/01_error_rate.sql`, `sql/duckdb/log_observability/02_latency_percentiles.sql`, `sql/duckdb/log_observability/03_trace_hotspots.sql`, `experiments/reports/duckdb_log_observability.md` | Run `db-lab generate-logs` and `db-lab run-duckdb-logs`, then compare with ClickHouse on the VPS. |
| CSV vs Parquet storage | passed | Typed columnar files, compression, column pruning, and local scan timing. | `experiments/reports/duckdb_storage_formats.md`, `docs/duckdb-storage.md` | Rerun with larger row counts and compare size ratios. |
| Query plan inspection | passed | EXPLAIN, EXPLAIN ANALYZE, scan operators, filters, and projections. | `experiments/reports/duckdb_query_plans.md`, `src/db_lab/experiments/duckdb_query_plans.py` | Run `db-lab run-duckdb-plans --analyze` and compare operator timing. |
| Scale sensitivity | passed | Row-count changes, CSV/Parquet size growth, and local scan timing direction. | `experiments/reports/duckdb_scale.md`, `src/db_lab/experiments/duckdb_scale.py` | Increase scale rows and compare when Parquet advantage becomes clearer. |

### clickhouse

| Capability | Status | Study focus | Evidence | Next action |
| --- | --- | --- | --- | --- |
| MergeTree market OLAP | passed | Server OLAP, MergeTree schema, partitioning, sorting keys, and batch insert. | `sql/clickhouse/market_ticks/schema.sql`, `sql/clickhouse/market_ticks/00_profile.sql`, `sql/clickhouse/market_ticks/01_symbol_vwap.sql`, `sql/clickhouse/market_ticks/02_minute_bars.sql`, `docs/service-runbook.md` | Start ClickHouse and compare the same market queries with DuckDB. |
| Materialized ecommerce aggregation | passed | MergeTree raw events, SummingMergeTree aggregate table, and materialized view flow. | `sql/clickhouse/ecommerce_events/schema.sql`, `sql/clickhouse/ecommerce_events/01_funnel.sql`, `sql/clickhouse/ecommerce_events/04_materialized_revenue.sql`, `docs/service-runbook.md` | Start ClickHouse and verify the minute revenue aggregate after inserts. |
| IoT service metrics OLAP | passed | MergeTree device metrics, server-side joins, countIf anomaly scoring, and region windows. | `sql/clickhouse/iot_metrics/schema.sql`, `sql/clickhouse/iot_metrics/01_metric_windows.sql`, `sql/clickhouse/iot_metrics/02_alert_join.sql`, `sql/clickhouse/iot_metrics/03_device_health.sql`, `experiments/reports/clickhouse_iot_metrics.md` | Compare ClickHouse IoT windows with DuckDB and DolphinDB after changing row count. |
| Log observability OLAP | passed | MergeTree-backed logs and traces for service error-rate, tail-latency, and trace hotspot analysis. | `sql/clickhouse/log_observability/schema.sql`, `sql/clickhouse/log_observability/01_error_rate.sql`, `sql/clickhouse/log_observability/02_latency_percentiles.sql`, `sql/clickhouse/log_observability/03_trace_hotspots.sql`, `experiments/reports/clickhouse_log_observability.md`, `docs/service-runbook.md` | Run `db-lab run-clickhouse-logs` on the VPS after generating log observability CSV files. |

### dolphindb

| Capability | Status | Study focus | Evidence | Next action |
| --- | --- | --- | --- | --- |
| TSDB market time-series batch | passed | DolphinDB script workflow, TSDB partitioning, and market time-series queries. | `sql/dolphindb/market_ticks/schema.dos`, `sql/dolphindb/market_ticks/import_csv.dos`, `sql/dolphindb/market_ticks/00_profile.dos`, `sql/dolphindb/market_ticks/01_symbol_vwap.dos`, `sql/dolphindb/market_ticks/02_minute_bars.dos`, `docs/service-runbook.md` | Start DolphinDB and verify batch minute bars. |
| Stream replay and realtime bars | passed | Shared stream tables, subscribeTable, createTimeSeriesEngine, replay, and realtime bars. | `sql/dolphindb/market_stream/setup.dos`, `sql/dolphindb/market_stream/replay_csv.dos`, `sql/dolphindb/market_stream/01_realtime_minute_bars.dos`, `docs/dolphindb-streaming.md`, `docs/service-runbook.md` | Start DolphinDB and compare stream output with batch minute bars. |
| IoT TSDB diagnostics | passed | DolphinDB TSDB partitioning, event-time windows, equality joins, and device anomaly scoring. | `sql/dolphindb/iot_metrics/schema.dos`, `sql/dolphindb/iot_metrics/import_csv.dos`, `sql/dolphindb/iot_metrics/01_metric_windows.dos`, `sql/dolphindb/iot_metrics/02_alert_join.dos`, `experiments/reports/dolphindb_iot_metrics.md` | Compare IoT batch diagnostics with a future stream-ingestion experiment. |


## Result Observations To Internalize

- DolphinDB market scripts use `sym` for the instrument column because `symbol` is also a DolphinDB type keyword in some expressions.
- ClickHouse market VWAP uses `total_volume` as the output alias to avoid aggregate alias substitution in newer ClickHouse versions.
- Timestamp text differs across engines: DuckDB/ClickHouse render ISO-like timestamps, while DolphinDB renders `yyyy.MM.dd` timestamps.
- Small runtime numbers are useful for smoke testing, but larger row counts are needed before drawing performance conclusions.

## Result Validation Signals

- All 23 implemented result validation checks are currently passed.

## Practice Tracks

### 1. Same Question, Three Engines

- Read `sql/duckdb/market_ticks/01_symbol_vwap.sql`, `sql/clickhouse/market_ticks/01_symbol_vwap.sql`, and `sql/dolphindb/market_ticks/01_symbol_vwap.dos` side by side.
- Run `db-lab compare-results` and `db-lab validate-results` to verify that business results match even when column names differ.
- Change one output alias in each engine and confirm the report shape changes as expected.

### 2. DuckDB Local Analytics

- Run `db-lab run-duckdb-storage --repetitions 5` and compare CSV vs Parquet file size and scan timing.
- Run `db-lab run-duckdb-plans --analyze` and map plan operators to the storage timing report.
- Increase `db-lab run-duckdb-scale --rows 1000,10000,50000` when the local machine can handle it.

### 3. ClickHouse Service OLAP

- Inspect `sql/clickhouse/market_ticks/schema.sql` before and after loading data.
- Change one `MergeTree` ordering choice in a scratch copy, reload, and compare query runtime.
- Read the materialized revenue report and trace raw event inserts into `SummingMergeTree` output.

### 4. DolphinDB Time-Series And Stream Processing

- Compare `sql/dolphindb/market_ticks/02_minute_bars.dos` with `sql/dolphindb/market_stream/01_realtime_minute_bars.dos`.
- Verify that batch bars and stream bars agree on open, high, low, close, and volume for the first minute.
- Change `--dolphindb-stream-rate` and observe how replay latency changes without changing the final bar values.

## Repeatable Study Loop

```powershell
db-lab archive-run --label before-next-run
db-lab doctor --report experiments/reports/doctor.md
db-lab run-lab-suite --force-data --market-rows 1000 --ecommerce-rows 1000 --ecommerce-users 100 --iot-rows 2000 --iot-devices 100
db-lab generate-logs --rows 2000 --services 6
db-lab run-duckdb-logs
db-lab capabilities
db-lab compare-results
db-lab validate-results
db-lab compare-runs
db-lab query-catalog
db-lab selection-memo
db-lab engine-notes
db-lab controlled-sweep --variable market_rows --values 1000,5000 --base-iot-rows 2000 --base-iot-devices 100 --engines duckdb,clickhouse,dolphindb
db-lab metrics
db-lab plan-next
db-lab study-guide
db-lab journal
db-lab render-dashboard
```

If local Docker/WSL is unavailable, run the same service-backed loop on the VPS:

```powershell
db-lab run-remote-suite `
  --host your-vps-host `
  --user root `
  --port 22 `
  --identity-file C:\path\to\id_ed25519 `
  --project-dir /root/projects/db-lab `
  --market-rows 1000 `
  --ecommerce-rows 1000 `
  --ecommerce-users 100 `
  --iot-rows 2000 `
  --iot-devices 100
```

## Skill Check

- Explain when DuckDB is enough and when a service database is worth the operational cost.
- Explain why ClickHouse uses `MergeTree` and materialized aggregation in this lab.
- Explain why DolphinDB has both batch TSDB tables and stream replay in this lab.
- Explain why logs and traces are a natural ClickHouse/DuckDB comparison but not necessarily a DolphinDB-first scenario.
- Explain what a failed result validation means before interpreting benchmark timing.
- Reproduce one query in all applicable engines without looking at the original script.

Generated by db-lab render-dashboard.