Can LLMs Write ClickHouse SQL? 22 Models, Two Generations, One Benchmark

ClickHouse SQL is almost standard SQL, until it isn’t. Functions like quantile(), arrayJoin(), -If combinators and WITH FILL make it a dialect that rewards familiarity. In February 2026 I benchmarked 8 LLMs on 50 ClickHouse questions. Six months later the model landscape had turned over completely, so I reran the same benchmark on 14 successor models: same questions, same prompts, same scoring.

The short version:

1. Every model family improved. The generation-over-generation gains range from +2 points (Claude Opus) to +11.3 points (Claude Sonnet), with most families gaining 7 to 9 points of strict accuracy.
2. The mid-tier caught up with the flagships. Claude Sonnet 5 (73.3%) sits one correct run behind Claude Opus 5 (74.0%) at a third of the price. GPT-5.6-Terra, a $1/M mid-tier model, beats GPT-5.5, the $5/M flagship.
3. “Pro” buys nothing here. All three GPT-5.6 pro variants score lower on strict accuracy than their same-priced base models.
4. Two capability gaps closed, two remain. WITH FILL went from 0% to 93% correct and CTE + tuple subqueries from 0% to 88%. But the two questions that fooled every model in February still fool every model today.
5. The benchmark broke too. My own hand-written reference query stopped working after a ClickHouse upgrade, and re-checking the February data with an automated classifier exposed an error in my manual failure labels. Details below, because both changed the numbers.

Everything is reproducible: data, scripts and charts. Total API cost for the August rerun: about $9.

Setup

Two tables in a ClickHouse database, ~1M events and ~10K users of synthetic web analytics data (deterministically generated, so both waves ran against identical data):

CREATE TABLE events (
    event_id       UInt64,
    timestamp      DateTime64(3, 'UTC'),
    user_id        UInt64,
    event_type     LowCardinality(String),
    page           String,
    duration_ms    Nullable(UInt32),
    properties     Map(String, String),
    tags           Array(String),
    country        LowCardinality(String),
    device         LowCardinality(String),
    revenue_cents  Nullable(Int64)
) ENGINE = MergeTree()
ORDER BY (timestamp, user_id)
PARTITION BY toYYYYMM(timestamp);

CREATE TABLE users (
    user_id    UInt64,
    created_at DateTime64(3, 'UTC'),
    plan       LowCardinality(String),
    email      String,
    age        Nullable(UInt8),
    country    LowCardinality(String),
    metadata   Map(String, String)
) ENGINE = MergeTree()
ORDER BY user_id;

50 questions in 5 difficulty tiers, from “how many events are there?” (T1) up to window functions, WITH FILL and argMax() (T5). Tiers 1 and 2 are standard SQL; tiers 3 to 5 need ClickHouse-specific functions. Each model answers every question 3 times, zero-shot, temperature 0, seed 42, via OpenRouter. The prompt contains the full DDL, a short data description, and “Respond with ONLY the SQL query”.

One documented flaw carried over from February: the prompt says “~100K users” but the data has ~10K. No question depends on user count, but it is a confound I keep disclosing rather than silently fixing, because fixing it would break comparability between the waves.

The two waves

February lineup: Claude Opus 4.6, Claude Opus 4, Claude Sonnet 4.5, GPT-5.2, Kimi K2.5, DeepSeek V3.2, Gemini 3 Flash, MiniMax M2.5.

August lineup: each February model’s successor (Claude Opus 5, Claude Sonnet 5, GPT-5.5, Gemini 3.5 Flash, DeepSeek V4-Pro, Kimi K3, MiniMax M3), plus DeepSeek V4-Flash and the entire GPT-5.6 series: Luna, Terra, Sol, each in base and pro.

The February scores are kept as measured in February. Mixing measurement dates is a real limitation, and I flag it where it matters. All prices are OpenRouter list prices at the respective access date; Claude Sonnet 5 was at its introductory price in August.

Scoring

Each run gets a score: 3 if the query executes and its output matches the reference query’s output (with whitespace normalization, row sorting for unordered results, and 0.1% numeric tolerance), 2 if it executes but the output differs, 1 if it fails at runtime, 0 for syntax errors or API failures.

Strict matching penalizes queries that are logically right but return different columns or formats. So every score-2 result gets classified into three buckets:

• format_mismatch: right logic, different output format (row order, missing LIMIT, date type, approximate vs exact aggregate)
• column_mismatch: right logic, different column selection (extra or missing columns)
• logic_error: genuinely wrong SQL

In February I classified all 396 score-2 results by hand. For this update I replaced the hand labels with an automated classifier that re-executes both queries and compares outputs structurally (row subsets for LIMIT differences, per-column multiset overlap for column selection, value tolerance for approximate aggregates). Validated against my February manual labels it agrees on 88% of rows, and where they disagree, spot checks mostly found the manual label at fault (one example below). Both waves in this article use the automated classification, so the numbers are computed by one uniform, reproducible method.

Adjusted accuracy counts format and column mismatches as correct: it measures “did the model understand the question”, while strict accuracy measures “would this drop into an automated pipeline unchanged”.

Results

August 2026 ranking

For reference, February’s ranking under the same classification method: Claude Opus 4.6 and Opus 4 at 72.0% strict (98.0% adjusted), Kimi K2.5 at 64.0%, Claude Sonnet 4.5 at 62.0%, MiniMax M2.5 at 61.3%, DeepSeek V3.2 and GPT-5.2 at 60.7%, Gemini 3 Flash at 60.0%.

Six months of progress

Every family improved, but not evenly. Claude Sonnet made the biggest jump (+11.3 points) and now effectively ties Opus. The Opus tier itself barely moved (+2.0): it was already close to what strict scoring allows on this benchmark, since a large share of the remaining “failures” are format choices, not capability gaps.

The tier pattern from February survived intact: everything at T1, near-everything at T2, and a cliff at T3 where ClickHouse-specific functions start. The cliff just moved up a bit: T4 improved most visibly (Claude models at 73% vs 60% in February), and T5 stays the hardest, with nobody above 43%.

Strict vs adjusted: the gap is the story

Of August’s 623 score-2 results, the classifier labels 270 format mismatches, 243 column mismatches, and only 110 genuine logic errors. That is 110 truly wrong queries out of 2,100, or 5.2%. The picture from February holds: models rarely write wrong SQL for these questions; they mostly make different, defensible choices about what to return. Adjusted accuracy compresses the whole field into the 89 to 95% band, which means the strict-accuracy ranking is largely a ranking of “who guesses my output format”, not “who knows ClickHouse”.

If you take one thing from this article for production text-to-SQL: specify the expected output columns and format in the prompt. It is worth more than a model upgrade.

Cost

The cost-per-correct-query spread is a factor of about 240 within the August wave alone: GPT-5.6-Luna at $0.0002 and DeepSeek V4-Flash at $0.00016 on one end, GPT-5.6-Sol-Pro at $0.038 on the other. Luna answers two thirds of the questions correctly at a fifth of a cent per correct query, and over 90% correct by adjusted accuracy. For a pipeline with retry logic, the cheap end of this chart is very hard to argue against.

The strangest data points are the GPT-5.6 pro variants. Same list price as their base models, consistently lower strict accuracy (Terra 71.3 vs Terra-Pro 70.0, Luna 66.0 vs 64.7, Sol 65.3 vs 63.3), and Sol-Pro’s longer answers made it the most expensive model in the entire eval per correct query. Whatever “pro” optimizes for, it is not one-shot SQL generation. Also note the tier inversion: mid-tier Terra beats flagship Sol by 6 points.

What changed, what didn’t

In February, 13 of the 50 questions had 0% strict accuracy across all 8 models. In August, 11 of those 13 remain essentially at zero (none above 7%), and the classifier says they are what they were in February: format and column-selection artifacts, where every model makes a reasonable choice that differs from my reference.

Two questions genuinely flipped:

• t5_06, WITH FILL: 0% to 93%. In February, models guessed at this ClickHouse-only syntax and mostly got it wrong. The August generation writes it correctly almost every time. This is the clearest real capability gain in the rerun.
• t4_10, CTE + tuple subquery: 0% to 88%. Same story for tuple-IN-subquery patterns.

And two questions still fool everything with a model number:

• t5_07: “collect all event types per user into a sorted array”. The reference deduplicates (groupArray(DISTINCT ...)); most models return duplicates. 0/42 in August, and the failures are genuine logic errors, not formatting.
• t5_10: tag matching where the reference treats “premium” as a substring (LIKE '%premium%') but every model matches exactly. Arguably an ambiguous question, and after 66 failed attempts across two generations I lean toward blaming my question more than the models.

Two corrections to the February article

Being honest about these matters more to me than the rankings.

My reference SQL broke on a ClickHouse upgrade. The February reference for the WITH FILL question used string literals: WITH FILL FROM '2024-01-01'. ClickHouse 25.11 accepted that; 26.1 requires typed constants (toDate('2024-01-01')) and rejects the query. My first August scoring pass compared every answer against a reference that no longer executed, and silently marked 40 correct answers wrong. The benchmark author fell into the same dialect-drift trap the benchmark measures. The published scores use the fixed, version-portable reference.

My manual classification had errors. The February draft claimed that all 8 models made the same logic error on t5_07 by forgetting to deduplicate. Re-checking the actual responses shows 4 of the 8 deduplicated correctly (via groupUniqArray or arrayDistinct) and only differed in a missing LIMIT or an extra column. Half of that “universal logic error” was my labeling, not their SQL. The automated classifier caught this, which is why both waves now use it, and why the per-model adjusted numbers for February differ slightly from what my draft said (Claude models 98% rather than 96%, GPT-5.2 86.7% rather than 90%).

Which model should write your ClickHouse SQL?

• Best accuracy, price no object: Claude Opus 5, but the honest answer is that its lead over Claude Sonnet 5 is a single run out of 150, and Sonnet costs a third as much.
• Best value in the top group: Claude Sonnet 5 or GPT-5.6-Terra. Terra gets within 3 points of Opus 5 at an eighth of the cost per correct query.
• Pipelines with retries: GPT-5.6-Luna or DeepSeek V4-Flash. At $0.0002 per correct query you can retry five times and still be an order of magnitude cheaper than a single flagship shot.
• Skip the pro variants for this workload. Same price, less accuracy, and in Sol-Pro’s case much higher output-token bills.
• Whatever you pick: put the expected output format in the prompt. The strict-adjusted gap (20 to 30 points for every model) is mostly recoverable with explicit instructions about columns, ordering and LIMIT.

Limitations

1. The two waves were measured six months apart on different ClickHouse versions (25.11 vs 26.1) with prices from their respective dates. Deterministic data generation keeps the reference results identical, but provider-side model updates within a wave are invisible to me.
2. Strict scoring measures format matching as much as capability. That is why adjusted accuracy is reported everywhere. The classifier that computes it agrees with my manual labels on 88% of February rows; the disagreements cut both ways and a residual gray zone (is an hour returned as 0..23 instead of a timestamp a format choice or an error?) remains judgment.
3. 50 questions, 3 runs. One question is 2 percentage points of strict accuracy. Ranking neighbors within a couple of points should be read as ties.
4. Zero-shot only. Few-shot examples of ClickHouse idioms would likely close part of the T3+ gap for every model.
5. Small dataset (1M rows). The February query-performance analysis found LLM queries essentially as fast as hand-written references (median speedup 1.0×); I did not rerun it for August.
6. The prompt says ~100K users; the data has ~10K. Documented confound, kept for comparability.

Data

Everything is in comino/blog-experiments/03: the 50 questions with reference SQL, all 3,300 scored runs (scores.csv, scores_v2.csv), the classifier and its validation against the manual labels, per-model response JSONs including raw API responses, and the chart-generation scripts.

50 questions × 3 runs, zero-shot, temperature 0, seed 42, via OpenRouter. February wave: 8 models on ClickHouse 25.11, accessed 2026-02-12. August wave: 14 models on ClickHouse 26.1.2, accessed 2026-08-10. Evaluation on Hetzner CX53.

Questions, corrections, or a consulting project? Mail me or find me on GitHub.