The hard validation gate for an Atlys ClickHouse DDL, run on the live ClickHouse Cloud service via the clickhouse_write_tools MCP (run_query) — no shell/chdb needed. Static-lint the .sql for escaped double-quoted literals, then create the DDL against a THROWAWAY validation table (spec_table + a __val suffix), INSERT one wrapped representative row per event type, assert the typed ORDER BY paths + ch_insert_time are readable and any MV backing tables received rows, then DROP the throwaway objects. Invoke after the DDL is drafted and before the production Cloud create (atlys-cloud-test) or git step. Full algorithm + error table in references/chdb-validation.md.
The hard validation gate for an Atlys ClickHouse DDL, run on the live ClickHouse Cloud service via the clickhouse_write_tools MCP (run_query) — no shell/chdb needed. Static-lint the .sql for escaped double-quoted literals, then create the DDL against a THROWAWAY validation table (spec_table + a __val suffix), INSERT one wrapped representative row per event type, assert the typed ORDER BY paths + ch_insert_time are readable and any MV backing tables received rows, then DROP the throwaway objects. Invoke after the DDL is drafted and before the production Cloud create (atlys-cloud-test) or git step. Full algorithm + error table in references/chdb-validation.md.
Skill: DDL Validation Gate (on Cloud, via MCP)
Hard gate. You may NOT run the production create (atlys-cloud-test), commit, or push until
this passes. This runs entirely over the clickhouse_write_tools MCP (run_query) — there is
NO shell, NO python, NO chdb in this runtime. Do not stop and hand the user a script; validate
here using the write MCP against a throwaway validation table, then clean it up.
Why a throwaway table? It lets you exercise the exact DDL on real ClickHouse (the same engine
Cloud runs) without touching the production {spec_table}. The validation table name is the
production name plus a __val suffix; every object created here is dropped at the end.
Tools
Purpose
MCP tool
Run every statement (CREATE / INSERT / SELECT / DROP)
clickhouse_write_tools → run_query (one raw SQL statement per call)
The MCP owns the Cloud connection (host / HTTPS / cert). You pass one SQL statement per call and
read the returned rows out of the tool result. No CLI, no TLS flags.
1 — Static lint FIRST (catches the #1 generation bug)
Before any tool call, scan the drafted Atlys/schemas/{schema_name}.sqlin your own reasoning
for escaped double-quoted literals — the top DDL-generation bug:
They must be single-quoted: 'UTC', 'express_checkout_shown', DateTime64(3, 'UTC').
If you find any, fix the .sql before continuing. Only proceed once the DDL is free of escaped
double-quoted literals.
2 — Build the throwaway validation DDL
Take the exact statements from the drafted .sql and produce a validation copy in which every
object name gets a __val suffix so nothing collides with production:
base table atlys.{spec_table} → atlys.{spec_table}__val
each MV backing table atlys.{mv_backing} → atlys.{mv_backing}__val
each CREATE MATERIALIZED VIEW atlys.{mv_name} → atlys.{mv_name}__val, and rewrite its
TO atlys.{mv_backing} target and its FROM atlys.{spec_table} source to the __val names.
Do not otherwise edit the DDL (same JSON typed hints, ch_insert_time, ORDER BY, PARTITION,
TTL, engines) — the point is to validate what will actually ship. Keep CREATE DATABASE IF NOT EXISTS atlys unsuffixed (the DB is shared).
3 — Create on Cloud (one run_query call per statement, in order)
Send each statement as its own clickhouse_write_tools → run_query call, in dependency order:
CREATE DATABASE IF NOT EXISTS atlys
CREATE TABLE IF NOT EXISTS atlys.{spec_table}__val ( ... ) ← the base table
each MV backing table CREATE TABLE IF NOT EXISTS atlys.{mv_backing}__val ( ... ) (if any)
each CREATE MATERIALIZED VIEW IF NOT EXISTS atlys.{mv_name}__val TO atlys.{mv_backing}__val AS SELECT ... FROM atlys.{spec_table}__val ... (if any)
Stop and enter the auto-fix loop (§6) on the first call that returns isError: true.
4 — Insert one wrapped representative row per event type
From the input NDJSON, take one representative raw row per event type (the row with the most
non-null paths for that type). Wrap each so it lands in the payload JSON column — the raw NDJSON
row IS the event object, so it nests under payload:
INSERT INTO atlys.{spec_table}__val (payload) FORMAT JSONEachRow
{"payload": <one raw NDJSON row object>}
Send each as one run_query call (pass the whole INSERT ... FORMAT JSONEachRow\n{...} string
verbatim). Ensure the event's timestamp path is a value parseable as DateTime64(3)
(e.g. "2026-07-27T11:34:45.123Z"); normalise only if a call rejects it.
5 — Assert (read the results out of each run_query)
Run these as run_query calls and check the returned rows:
-- row count >= number of event types insertedSELECTcount() AS n FROM atlys.{spec_table}__val;
-- every typed ORDER BY path is readable & non-NULL, and ch_insert_time auto-populatedSELECT payload.event, payload.{feature_dim_1}, payload.user_id,
payload.{timestamp_path} AS ts, ch_insert_time
FROM atlys.{spec_table}__val LIMIT 1;
-- if MVs were created, each backing table received aggregated rowsSELECTcount() AS n FROM atlys.{mv_backing}__val; -- expected >= 1 per MV-- any boolean-typed path reads back as a real bool, not a stringSELECT payload.{bool_path} FROM atlys.{spec_table}__val LIMIT 1; -- if any boolean paths exist-- any skip-indexed path is actually filterableSELECTcount() FROM atlys.{spec_table}__val
WHERE payload.{indexed_path} =<sample value>; -- if any INDEX clauses exist, must run without error
Pass criteria:
CREATE succeeded, including any INDEX clauses and Bool/UInt8 typed paths,
count() ≥ number of event types inserted,
no ORDER BY path column comes back NULL (keys cannot be Nullable),
ch_insert_time is non-NULL (proves MATERIALIZED now64(3) fired),
each MV backing table n ≥ 1 (if MVs exist),
any boolean-typed path reads back as a real bool (not a string),
any skip-indexed path is filterable (the WHERE query above runs without error).
Record these results as text — the git step embeds them as the validation evidence.
6 — Auto-fix loop (max 5 iterations)
Create + insert + assert (all via run_query on the __val table)
├─ ✅ pass → clean up (§7) → proceed to atlys-cloud-test (production create)
└─ ❌ FAIL → read the run_query error → identify root cause (see reference error table) →
fix the DDL in Atlys/schemas/{schema_name}.sql (re-derive from the NDJSON, do not guess) →
DROP the partial __val objects (§7) → re-run from §3.
Surface to the user if still failing after 5.
Common root causes: escaped double-quote literal, a Nullable typed path in the key, a bad
timestamp value, an unwrapped INSERT row, an ORDER BY path missing from the typed hint, an
INDEX referencing a path not typed in the payload(...) hint, or a boolean path whose samples
aren't actually boolean.
7 — Clean up the throwaway objects (always)
Drop everything you created here, MVs first, then backing tables, then the base table:
DROPTABLE IF EXISTS atlys.{mv_name}__val; -- each MVDROPTABLE IF EXISTS atlys.{mv_backing}__val; -- each MV backing tableDROPTABLE IF EXISTS atlys.{spec_table}__val; -- base table
Run each as a run_query call. The __val objects must not survive this step (whether it passed
or failed) — the production create is done by atlys-cloud-test, not here.
Rules
Never claim you cannot validate because there is no shell/chdb — validation runs on the
clickhouse_write_tools MCP here. Only stop if the MCP itself is unavailable.
Always validate against the __val throwaway table, never the production {spec_table}.
Always send one statement per run_query call, in dependency order.
Always use the exact DDL text from the drafted .sql (only the __val renames differ).
Always clean up (§7) before proceeding — pass or fail.
Full algorithm (per-event-type sampling, wrapped-insert prep, throwaway renaming) and the
complete error → fix decision table: references/chdb-validation.md.
