Can LLMs Write ClickHouse SQL? A Zero-Shot Benchmark of 22 Models

ClickHouse SQL deviates from standard SQL in ways that matter for text-to-SQL: quantile(), arrayJoin(), -If combinators, WITH FILL, windowFunnel(), map and array functions. This benchmark measures how well current LLMs handle the dialect in the hardest setting: zero-shot, one attempt, no examples.

22 models, 60 questions across six difficulty tiers, 3 runs per question: 3,960 scored queries. Each generated query is executed against a live ClickHouse database and its output compared to a hand-written, pre-validated reference query.

Key results:

1. Claude Opus 5 wins at 76.7% strict accuracy, ahead of Kimi K3 (74.4%), Claude Opus 4.6 (73.3%) and Claude Sonnet 5 (72.2%). The field spans 58.3% to 76.7%.
2. The hardest tier decides the ranking. On T1-T5, the top four are separated by rounding noise. On T6 (windowFunnel, ASOF JOIN, time-constrained sequenceMatch), Opus 5 scores 90% while Sonnet 5 drops to 67%. Models that look interchangeable on common SQL are not interchangeable on hard dialect SQL.
3. Most failures are not wrong SQL. Re-executing and structurally comparing every wrong-result query: 82% of them have correct logic and differ from the reference only in output format or column selection. Genuine logic errors are 4.9% of all runs. Adjusted for this, accuracy spans 83.3% to 95.0%.
4. Cost per correct query spans a factor of ~250: $0.00017 (Gemini 3 Flash) to $0.042 (GPT-5.6-Sol-Pro). Price does not predict accuracy: the $1/M GPT-5.6-Terra outscores the $5/M GPT-5.5 and GPT-5.6-Sol overall.
5. Explicit output contracts fix the metric. T6 questions specify exact output columns and ordering; the strict-adjusted gap there collapses from 20-30 points to 0-10 points. How you phrase the expected output shapes measured “accuracy” more than model choice does.

Data, scripts and charts: comino/blog-experiments/03.

Setup

Schema and data

Two tables, ~1M events and ~10K users of synthetic web analytics data. Data generation is deterministic (rand(number) seeding), so every evaluation runs 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;

Questions

60 questions in six tiers:

T1-T5 questions are phrased the way a user would ask (“show daily event counts for January”), leaving output format to the model. T6 questions additionally specify the exact output columns, ordering and row count. That difference is deliberate, and its effect on the metric is one of the results (see T6 below).

Every reference query was validated on the live database before any model saw it: executed twice for determinism, and checked that the correct answer is discriminative (an early draft of the sequenceMatch question was satisfied by all 10,000 users, so a wrong query could score as correct; the published version uses a 60-second time window matched by exactly 10 users).

Protocol

Zero-shot. The prompt contains the full DDL, a short data description, the question, and the instruction to respond with only the SQL query. Temperature 0, seed 42, 3 runs per question, all models accessed through OpenRouter. Models were accessed on three dates between February and August 2026; see Methodology notes.

One documented prompt flaw: the prompt states “~100K users”, the dataset has ~10K. No question depends on user cardinality.

Scoring

Per run: 3 = executes, output matches the reference (whitespace normalization, row sorting for unordered results, 0.1% numeric tolerance). 2 = executes, output differs. 1 = runtime error. 0 = syntax error or API failure.

Strict matching penalizes queries that are logically correct but return different columns or formats. Every score-2 run is therefore classified by re-executing both queries and comparing outputs structurally:

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

The classifier is rule-based (row-subset detection for LIMIT differences, per-column multiset overlap for column selection, value tolerance for approximate aggregates). Validated against an earlier manual classification of 396 results it agrees on 88% of rows; in the sampled disagreements the manual label was at fault more often than the rule (an example is in Methodology notes).

Two metrics follow: strict accuracy (score 3 only) and adjusted accuracy (score 3 plus format and column mismatches). Strict answers “does the output drop into an automated pipeline unchanged”; adjusted answers “is the SQL logically correct”.

Results

180 runs per model (60 questions × 3). One question is 1.7 points of overall strict accuracy; adjacent rows are statistical ties.

Accuracy by difficulty tier

The tier structure is consistent across all models: T1 at or near 100% everywhere, T2 at 80-100%, a sharp drop at T3 where dialect-specific functions begin, and T6 spreading the field from 90% down to 47%. The differentiator between models is dialect knowledge, not SQL in general.

The hard tier separates the leaders

On T1-T5 alone, the top of the field is compressed: Claude Opus 5 and Claude Sonnet 5 differ by a single run. T6 breaks the tie: Opus 5 reaches 90.0%, Sonnet 5 drops to 66.7%. Gemini 3.5 Flash, Kimi K3 and GPT-5.6-Sol-Pro form the second T6 group at 83.3%.

T6 also inverts the failure profile. On T1-T5, wrong-but-executing results dominate (31% of runs) and runtime errors are rare (2%). On T6, runtime errors dominate (21.8%) and wrong results drop to 6.5%: with the output format pinned down and functions this specific, queries either work or fail loudly.

Two functions are near-universal blind spots:

• windowFunnel requires DateTime, not DateTime64. 62 of 66 runs fail with the same type error because they pass the timestamp column uncast. Only Claude Opus 5 and GPT-5.2 produced the required toDateTime() cast.
• sequenceMatch with a time condition fails 54 of 66 runs, mostly the same way.

At the other end, ASOF JOIN, LIMIT BY, argMaxIf and mapKeys + ARRAY JOIN are essentially solved (97-98% across all models).

The same-priced GPT-5.6 pro variants split by difficulty: on T1-T5 they score at or below their base models, on T6 Sol-Pro scores 10 points above Sol and Terra-Pro 3 above Terra. Whatever additional computation “pro” buys appears to matter only above a difficulty threshold.

Cost vs performance

• GPT-5.6-Luna ($0.10/M in): 66.1% strict, 89.4% adjusted, at $0.0003 per correct query.
• DeepSeek V4-Flash ($0.08/M in): 62.2% / 86.7% at the same cost level.
• Claude Sonnet 5: 72.2% at $0.0031 per correct query; Claude Opus 5 adds 4.5 points for 2.8× that.
• GPT-5.6-Sol-Pro is an outlier: strong on T6, but long outputs at $30/M make it the most expensive model per correct query in the field, at 10th place overall.

For a pipeline that can validate and retry, the cheap end changes the calculus: five retries of a budget model cost a small fraction of one flagship attempt.

What the failures are

Across all 3,960 runs: 2,645 correct (66.8%), 1,062 wrong result, 210 runtime errors, 43 syntax or API errors. Classification of the 1,062 wrong results:

The large majority of “wrong” results are queries whose filtering, aggregation and join logic is correct but which return different columns, different row ordering, or a different value format than the reference. Two consequences:

• The strict ranking partly measures which model guesses the reference’s output conventions. The 20-30 point strict-adjusted gap on T1-T5 is consistent across every model.
• T6 shows the fix works: with output columns and ordering specified in the question, the gap collapses to 0-10 points and strict accuracy becomes a capability measurement.

For production text-to-SQL, specifying the expected output columns, ordering and LIMIT in the prompt recovers most of the gap. This has a larger effect than any model choice in the table.

Questions no model solves

Two T5 questions are failed by all 22 models:

• t5_07: “collect all event types per user into a sorted array”. The reference deduplicates; most models return duplicates. Genuine logic errors.
• t5_10: tag matching where the reference uses a substring match (LIKE '%premium%') and every model matches exactly. The question wording does not clearly imply substring semantics; this is at least partly a question defect.

A further 11 T1-T5 questions sit at or near 0% strict for every model, and the classifier attributes them to format and column-selection differences rather than wrong logic.

Methodology notes

Three items surfaced while preparing this report. All three affect published numbers and are part of why the raw data is public.

Reference SQL is itself subject to dialect drift. An earlier version of the WITH FILL reference used string literals (WITH FILL FROM '2024-01-01'). ClickHouse 25.11 accepted this; 26.1 requires typed constants and rejects the query. One scoring pass compared answers against a failing reference and marked 40 correct runs wrong before this was caught. The published scores use a version-portable reference.

Manual failure classification contained errors. An earlier manual classification claimed all models made the same logic error on t5_07 by failing to deduplicate. The response data shows half of the models classified that way had deduplicated correctly (groupUniqArray, arrayDistinct) and differed only in a missing LIMIT or extra columns. This motivated replacing manual classification with the rule-based classifier everywhere.

Access dates. The 22 models were measured in three batches as the benchmark grew: 8 models on 2026-02-12 (T1-T5, ClickHouse 25.11), 14 models on 2026-08-10 (T1-T5, 26.1), and all 22 models on 2026-08-11 (T6, 26.1). Reference outputs are identical across versions (deterministic data); prompts and parameters were identical throughout. Provider-side model updates between those dates are not observable through the API; treat comparisons of near-tied models measured months apart accordingly.

Model choice

Read from the table, for this workload:

• Highest accuracy: Claude Opus 5, and its T6 lead (90% vs 83% for the next group) is larger than its overall lead. For funnel/sequence-style analytical SQL the premium is justified.
• Accuracy per dollar in the upper group: Claude Sonnet 5 and GPT-5.6-Terra. Both within ~5 points of the leader at a fraction of the cost per correct query.
• High-volume pipelines with validation and retry: GPT-5.6-Luna or DeepSeek V4-Flash.
• The GPT-5.6 pro variants only pay off on hard tasks, and Sol-Pro’s output length makes it the most expensive option in the field.

Independent of model choice: constrain the output format in the prompt. The data shows format and column-selection divergence, not SQL capability, is the dominant failure mode for every model tested.

Limitations

1. Sample size. 60 questions × 3 runs; one question is 1.7 points of overall strict accuracy (3.3 points within T6). Adjacent table rows are statistical ties.
2. Strict scoring conflates format matching with capability on T1-T5, quantified via the classification and largely eliminated in T6’s design. The classifier has a gray zone (for example, an hour-of-day returned as an integer instead of a timestamp), and its 88% agreement with manual labels bounds the precision of the adjusted numbers.
3. Three access dates (see Methodology notes). Comparisons among near-tied models measured months apart carry extra uncertainty.
4. Zero-shot only. Few-shot prompting with dialect examples would likely raise all T3+ scores.
5. Small dataset. 1M rows. A side analysis found LLM-generated queries perform at parity with hand-written references (median speedup 1.0×).
6. Synthetic questions and data, not calibrated against Spider/BIRD or production query logs.

Data

comino/blog-experiments/03: all 60 questions with reference SQL, all 3,960 scored runs, per-model response JSONs with raw API payloads, the classifier with its validation against the manual labels, and chart-generation scripts.

60 questions × 3 runs per model, zero-shot, temperature 0, seed 42, via OpenRouter. Prices are OpenRouter list prices at each model’s access date; Claude Sonnet 5 at introductory pricing. Evaluated against ClickHouse on Hetzner CX53.

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