LIVE SQL LAB · DUCKDB × CLICKHOUSE

The same model intent, two different boundaries.

DuckDB owns a reproducible feature-and-scoring pipeline. ClickHouse goes further: logistic training becomes aggregation, and aggregate state becomes the model.

01 · DATAsales events
02 · CONTRACTtyped ML features
03 · TRAINexternal / aggregate
04 · STATEcoefficients / state
05 · SCORESQL prediction

RUN THE CONTRAST

Do not compare only milliseconds. Compare ownership.

Both engines use the same deterministic sales relation. Target: high_value = revenue ≥ 700. Features: quantity, scaled unit price and discount. This is instruction, not a business model.

DuckDB · SQL-first ML

FEATURE CONTRACT + VECTORIZED SCORING

DuckDB creates typed features and scores supplied coefficients; it does not claim general-purpose model training here.

——

EXECUTED SQL

Select an experiment.
—
SQL owns feature meaning and inference, while training remains outside this engine boundary.

ClickHouse · Aggregate ML

TRAINING STATE + evalMLMethod

ClickHouse trains logistic regression as an aggregate function and applies its compact state through SQL.

——

EXECUTED SQL

Select an experiment.
—
Inspect the weight array. Scoring rebuilds bounded state in one statement so the public lab remains reproducible.
Timing note DuckDB starts a subprocess per request. ClickHouse runs through HTTP and reports engine elapsed time. This is not a product ranking.

LAB SEQUENCE

Six steps from relation to model state.

The lab is complete only when students can explain every boundary—not merely produce a probability.

Read the contract

Identify target, types, units and scaling.

Run DuckDB features

Confirm typed, reproducible output.

Run DuckDB scoring

Find where supplied coefficients enter SQL.

Train in ClickHouse

Observe many rows becoming one weight array.

Apply aggregate state

Trace state creation into evalMLMethod.

Explain the boundary

State who owns features, training, state and inference.

CONTINUE

Connect the showcase to the full lesson.

MADlib remains the deeper classical in-database ML lab. This adds embedded SQL and columnar aggregate-state contrasts.

Open MADlib Lab →Read full lesson →Data Systems Labs →