When we designed ChistaDATA University, the first decision was what it would not be: a catalogue of unrelated ClickHouse courses that people take in whatever order the calendar allows. Every training programme I have sat through as a student, and most I have seen sold to enterprise teams, works that way, and the result is engineers who can recite the MergeTree engine family but have never watched a Keeper quorum fall over.
So we built a competency ladder instead, and we built ClickHouse certification on top of it as a defended capstone on a live multi-node cluster rather than a multiple-choice exam. This post explains how the ladder is put together, what each rung actually asks a learner to do, and how the certification, lab environment, corporate and academic programmes fit around it.
A note on what this is and is not. The full programme detail, module lists and contact routes live on the ChistaDATA University page; this is the design rationale behind the programmes and the ClickHouse certification, written by the people who teach it, with the lab code we actually hand to learners so you can judge the level for yourself.
ClickHouse education as a competency ladder, not a catalogue
Three principles hold the whole thing together. First, placement is by assessment, not by self-selection: every learner takes a free 45-minute technical assessment and enters at the level it indicates, which is how we stop a senior SRE from sitting through OLAP-versus-OLTP and stop an analyst from being thrown into Keeper internals. Second, the curriculum is versioned against ClickHouse releases.
Labs run on the current stable line and one previous LTS, so a cohort in September 2026 works with 26.8 LTS syntax, the text index rather than the deprecated bloom-filter indexes, async_insert on by default, and the current Keeper feature flags, and it can rehearse an upgrade between the two lines. Third, every performance claim a learner makes is evidenced from system.query_log, system.parts or system.merges on a cluster large enough that a bad schema decision hurts. That last one is the same rule we apply to ourselves in consulting, and there is no reason a training programme should be held to a lower standard.
The ladder has three core levels, CHT-100 Beginner, CHT-200 Intermediate and CHT-300 Advanced, and two specialist programmes that sit on top of it, CHT-400 ClickHouse Troubleshooting and CHT-500 Advanced Analytics in ClickHouse. CHT-900, the executive track for CTOs and CXOs, runs in parallel so that the people sponsoring an adoption understand what their engineers are being asked to do. Across the five technical programmes there are 62 modules and more than 240 lab hours, all hands-on, taught by engineers who carry a 24×7 pager for petabyte-scale ClickHouse estates.
The architecture thread that runs through every programme
One thing we decided early was that ClickHouse architecture would not be a module. It is the spine. CHT-100 introduces the columnar storage model and the MergeTree part lifecycle because you cannot read a query plan without it. CHT-200 goes down into sorting keys, granules, skip indexes and the write path because that is where most production latency is decided.
CHT-300 opens the engine: LSM-style part management, the vectorised operator pipeline, allocator behaviour, the replication protocol through Keeper. CHT-400 uses all of it diagnostically, and CHT-500 leans on it to explain why a single GROUP BY with combinators replaces a Spark job. A learner who completes the core ladder has seen the same architecture four times at increasing depth, which is how understanding actually sticks.
CHT-100: Beginner ClickHouse Training, the first step to ClickHouse certification
Six weeks, forty learning hours, for analysts, backend engineers and data engineers new to OLAP. Prerequisites are basic SQL and Linux shell literacy. The ten modules run from OLAP-versus-OLTP foundations through columnar storage mechanics, installation, the data types that matter, MergeTree essentials, ingestion fundamentals, the ClickHouse SQL dialect, reading a query plan, visualisation and access, and operational hygiene. Learners deploy a single-node instance, model a fact table, load a few hundred million rows and build a dashboard over it.
The lab that changes how beginners think is the one where they prove a schema decision with the query log rather than a stopwatch. Here is the shape of it: the same query against two versions of a table, one with a sensible sorting key and one without, and the evidence pulled from system.query_log.
-- CHT-100 Lab 5: prove the sorting key, do not assert it
CREATE TABLE lab.trips_bad
(
pickup_datetime DateTime,
vendor_id UInt8,
passenger_count UInt8,
trip_distance Float32,
fare_amount Float32
)
ENGINE = MergeTree
ORDER BY tuple(); -- no sorting key at all
CREATE TABLE lab.trips_good
(
pickup_datetime DateTime,
vendor_id UInt8,
passenger_count UInt8,
trip_distance Float32,
fare_amount Float32
)
ENGINE = MergeTree
ORDER BY (vendor_id, pickup_datetime);
-- Run the same query against both, tagged so the evidence is easy to find
SELECT vendor_id, count(), avg(fare_amount)
FROM lab.trips_good
WHERE vendor_id = 2 AND pickup_datetime >= '2024-06-01' AND pickup_datetime < '2024-07-01'
GROUP BY vendor_id
SETTINGS log_comment = 'cht100-lab5-good';
-- The deliverable is this result, not an opinion
SELECT
log_comment,
query_duration_ms,
read_rows,
formatReadableSize(read_bytes) AS read,
ProfileEvents['SelectedMarks'] AS marks_selected,
ProfileEvents['SelectedRanges'] AS ranges
FROM system.query_log
WHERE type = 'QueryFinish'
AND log_comment LIKE 'cht100-lab5-%'
ORDER BY event_time DESC
LIMIT 2;Completing CHT-100 with the proctored written exam qualifies a learner for the first ClickHouse certification tier, Certified ClickHouse Professional. Deliverables are a documented single-node deployment, a normalised-to-denormalised modelling exercise, ten labs with performance evidence, and a capstone dashboard over a dataset of at least 100 million rows.
CHT-200: Intermediate ClickHouse Training
Eight weeks, fifty-five hours, for data, platform and analytics engineers who have either completed CHT-100 or spent six months hands-on. This is the write-path programme: how data arrives, how it is deduplicated, how incremental aggregates are maintained, and how a schema decision made in year one shows up as query latency in year three. Modules cover the MergeTree engine family in depth, sorting and partition key design, data skipping indexes, materialised views and incremental aggregation, streaming ingestion, dictionaries and enrichment, JOIN strategies, compression and codecs, mutations and the data lifecycle, and observability foundations.
The centrepiece deliverable is a production-shaped streaming pipeline from Kafka to a replicated fact table with idempotent replay, plus a rollup layer whose effect on dashboard cost is measured rather than claimed. The pipeline skeleton learners build looks like this, and the point of the exercise is every setting that is explicit rather than inherited.
-- CHT-200 Lab 5: Kafka to replicated fact table with an incremental rollup
CREATE TABLE lab.events_queue
(
payload String
)
ENGINE = Kafka
SETTINGS
kafka_broker_list = '${KAFKA_BROKERS}',
kafka_topic_list = 'events',
kafka_group_name = 'cht200-lab5',
kafka_format = 'JSONAsString',
kafka_num_consumers = 2,
kafka_handle_error_mode = 'stream';
CREATE TABLE lab.events_local
(
event_date Date,
event_time DateTime64(3, 'UTC'),
tenant_id UInt32,
user_id UInt64,
event_name LowCardinality(String),
properties JSON
)
ENGINE = ReplicatedMergeTree('/clickhouse/tables/{shard}/lab/events_local', '{replica}')
PARTITION BY toYYYYMM(event_date)
ORDER BY (tenant_id, event_time, user_id)
TTL event_date + INTERVAL 13 MONTH
SETTINGS index_granularity = 8192;
CREATE MATERIALIZED VIEW lab.mv_events_ingest TO lab.events_local AS
SELECT
toDate(parseDateTime64BestEffort(JSONExtractString(payload, 'ts'))) AS event_date,
parseDateTime64BestEffort(JSONExtractString(payload, 'ts')) AS event_time,
JSONExtractUInt(payload, 'tenant_id') AS tenant_id,
JSONExtractUInt(payload, 'user_id') AS user_id,
JSONExtractString(payload, 'event_name') AS event_name,
JSONExtractRaw(payload, 'properties')::JSON AS properties
FROM lab.events_queue;
-- The rollup the dashboard actually reads; measured against the base table in the lab report
CREATE TABLE lab.events_hourly_local
(
tenant_id UInt32,
hour DateTime,
event_name LowCardinality(String),
events AggregateFunction(count),
users AggregateFunction(uniq, UInt64)
)
ENGINE = ReplicatedAggregatingMergeTree('/clickhouse/tables/{shard}/lab/events_hourly_local', '{replica}')
PARTITION BY toYYYYMM(hour)
ORDER BY (tenant_id, hour, event_name);
CREATE MATERIALIZED VIEW lab.mv_events_hourly TO lab.events_hourly_local AS
SELECT
tenant_id,
toStartOfHour(event_time) AS hour,
event_name,
countState() AS events,
uniqState(user_id) AS users
FROM lab.events_local
GROUP BY tenant_id, hour, event_name;The schema design document that accompanies this is reviewed by a principal engineer and must include the alternatives that were rejected and why. This is the pipeline build that is later graded for the Engineer tier of ClickHouse certification. Learners also ship a Grafana observability pack with alerting thresholds for part counts, merge backlog and insert latency, which is the moment most of them realise that observability is a schema decision too.
CHT-300: Advanced ClickHouse Training
Ten weeks, seventy hours, for SREs, DBREs, principal engineers and data architects with CHT-200 and a year of production exposure behind them. This is the internals and distributed-systems programme, and the one that separates the Architect tier of ClickHouse certification from the rest. Learners provision a three-shard, two-replica cluster with a ClickHouse Keeper ensemble, then spend a good part of the course deliberately degrading it and recovering it. Modules cover storage engine internals, the query execution engine, sharding and distribution, replication and coordination, performance engineering, write-path scaling, object storage and separated compute, security architecture, Kubernetes and infrastructure as code, and backup, DR and capacity planning.
The failure drills are the reason this programme exists: Keeper quorum loss with observation of read-only degradation and restoration; replica divergence detection and recovery via CHECK TABLE; merge starvation and its remediation sequence; a rolling upgrade across minor version boundaries; and cross-region failover with RPO measured rather than assumed. The verification queries below are what a learner runs during the quorum-loss drill, before and after stopping two of the three Keeper nodes, and the assessment is a defence of what they saw and what they did about it.
-- CHT-300 Drill 1: Keeper quorum loss. Run before, during, and after.
-- 1. Coordination state as the server sees it (system.zookeeper_info since 26.1)
SELECT * FROM system.zookeeper_info FORMAT Vertical;
-- 2. Which replicated tables have gone read-only, and how deep the queue is
SELECT
database,
table,
is_readonly,
is_session_expired,
queue_size,
inserts_in_queue,
merges_in_queue,
absolute_delay
FROM system.replicas
WHERE is_readonly OR queue_size > 0
ORDER BY absolute_delay DESC;
-- 3. Inserts during the outage must fail loudly, not silently; prove it
INSERT INTO lab.events_local
SELECT today(), now64(3), 1, 42, 'drill', '{}'::JSON;
-- Expected: DB::Exception: Table is in readonly mode (TABLE_IS_READ_ONLY)
-- 4. After quorum is restored: the queue drains and delay returns to zero
SELECT
table,
queue_size,
absolute_delay,
last_queue_update
FROM system.replicas
WHERE database = 'lab'
ORDER BY queue_size DESC;CHT-300 together with CHT-400 and a defended multi-region design is the route to the top ClickHouse certification tier, Certified ClickHouse Architect. The deliverables are the provisioned cluster, demonstrated recovery from every drill, and a documented capacity-planning and upgrade strategy.
CHT-400: ClickHouse Troubleshooting, the on-call rung of ClickHouse certification
Six weeks, forty-five hours, for on-call engineers, SREs, and support and platform teams. CHT-400 is the one programme that grew directly out of our break-fix practice, and it is regularly delivered standalone as a five-day workshop for teams already operating ClickHouse. The format is break-fix simulation: learners receive pre-broken clusters that show only symptoms, and have to reach root cause with system tables, logs and profiling. The methodology is deliberately release-independent: scope the issue to one query, table, node or cluster; prove the root cause with a bounded test and, where relevant, a flame graph; then fix, verify, and add a guardrail so it does not recur.
The incident catalogue is the one every ClickHouse on-call engineer eventually meets: too many parts (DB::Exception code 252), memory limit exceeded, replication lag and stuck queues, query latency regression after a release, disk exhaustion, duplicate or missing rows, Kubernetes crash loops and distributed query timeouts. The first-hour diagnostic for the most common of them, too many parts, is a good illustration of what the programme teaches: the fix is almost never the setting the error message names.
-- CHT-400 Incident 1: "Too many parts (300)". Find the cause, not the setting.
-- Who is inserting, how often, and how small?
SELECT
table,
count() AS new_parts_last_hour,
round(avg(rows)) AS avg_rows_per_part,
formatReadableSize(avg(size_in_bytes)) AS avg_part_size,
uniq(query_id) AS distinct_inserts
FROM system.part_log
WHERE event_type = 'NewPart'
AND event_time >= now() - INTERVAL 1 HOUR
GROUP BY table
ORDER BY new_parts_last_hour DESC
LIMIT 10;
-- Are merges keeping up, or starved?
SELECT
table,
count() AS active_merges,
round(max(progress), 2) AS max_progress,
formatReadableSize(sum(total_size_bytes_compressed)) AS merging_bytes,
max(elapsed) AS longest_merge_s
FROM system.merges
GROUP BY table;
-- Which client is producing the small inserts? (usually the answer)
SELECT
user,
client_name,
count() AS inserts,
round(avg(written_rows)) AS avg_rows,
any(Settings['async_insert']) AS async_insert
FROM system.query_log
WHERE type = 'QueryFinish'
AND query_kind = 'Insert'
AND event_time >= now() - INTERVAL 1 HOUR
GROUP BY user, client_name
ORDER BY inserts DESC;Deliverables are a tiered incident runbook with severity classification and escalation paths, a reusable diagnostic query library saved as views, root-cause write-ups for six simulated incidents, and a preventive alerting specification with thresholds the learner can justify. Enterprise customers use completion of CHT-400 as their internal on-call readiness sign-off, which is the most practical thing a ClickHouse certification can be used for.
CHT-500: Advanced Analytics in ClickHouse
Eight weeks, sixty hours, for analytics engineers, data scientists, product analysts and BI leads with CHT-200 and strong analytical SQL. CHT-500 treats ClickHouse as an analytical compute engine and asks a specific question of every workload: can the multi-stage Spark or pandas pipeline that computes this be replaced by one declarative query? Modules cover dimensional modelling for columnar engines, aggregate combinator mastery, session and path analytics, funnel, cohort and retention, statistical analysis in SQL, time-series and anomaly detection, customer-facing analytics, and the semantic layer and lakehouse interop.
The function families are taught as families, because that is how they compose: the -If, -Array, -State, -Merge, -Resample, -ForEach, -Distinct, -OrNull and -Map combinators; the array and higher-order functions; window functions; windowFunnel, sequenceMatch, sequenceCount, retention and uniqUpTo; the approximate algorithms from uniqHLL12 to quantileTDigest and topK; WITH FILL, INTERPOLATE, ASOF JOIN and the seriesDecomposeSTL family for time series; the geospatial and text primitives; and the vector distance functions and UDF surface.
A funnel that would be three Spark stages elsewhere is one query here, and the lab requires the p95 latency of that query to be recorded against a stated budget.
-- CHT-500 Lab 4: a three-step funnel within 24 hours, per tenant, with a latency budget
SELECT
tenant_id,
level,
count() AS users,
round(100 * count() / max(count()) OVER (PARTITION BY tenant_id), 1) AS pct_of_entry
FROM
(
SELECT
tenant_id,
user_id,
windowFunnel(86400)(
event_time,
event_name = 'signup',
event_name = 'create_project',
event_name = 'first_query'
) AS level
FROM lab.events_local
WHERE event_date >= today() - 30
GROUP BY tenant_id, user_id
)
WHERE level > 0
GROUP BY tenant_id, level
ORDER BY tenant_id, level
SETTINGS log_comment = 'cht500-lab4-funnel';
-- Weekly retention for the same cohort logic, one query, no external pipeline
SELECT
tenant_id,
sum(r[1]) AS week0,
sum(r[2]) AS week1,
sum(r[3]) AS week2,
sum(r[4]) AS week3
FROM
(
SELECT
tenant_id,
user_id,
retention(
event_date >= today() - 28 AND event_date < today() - 21,
event_date >= today() - 21 AND event_date < today() - 14,
event_date >= today() - 14 AND event_date < today() - 7,
event_date >= today() - 7
) AS r
FROM lab.events_local
WHERE event_date >= today() - 28
GROUP BY tenant_id, user_id
)
GROUP BY tenant_id;Deliverables are a production analytical model supporting funnel, cohort and retention with documented p95 latency, a portfolio rewriting five external-pipeline computations into single ClickHouse queries, an A/B testing harness with defensible significance calculations, and a multi-tenant customer-facing analytics API design with isolation, quotas and caching. CHT-500 is one of the two specialist programmes that, with CHT-200 and a graded pipeline build, earns the middle ClickHouse certification tier, Certified ClickHouse Engineer.
CHT-900: the executive track for CTOs and CXOs
Ten hours, delivered alongside the engineering programmes. Technical adoption fails when the sponsor does not understand what is being adopted, so CHT-900 is a condensed briefing series on the questions a CTO is actually asked: where ClickHouse fits against Snowflake, BigQuery, Redshift, Druid and Pinot; how to model total cost of ownership honestly; how to manage migration risk; team capability planning and hiring economics; governance and compliance under GDPR, SOC 2 and HIPAA, including audit logging; and how to measure value through latency-to-revenue linkage, cost per query and adoption metrics. It is vendor-neutral by design, including on the question of whether to run ClickHouse at all.
The competency matrix and the ClickHouse certification ladder
Nine competencies are tracked across the five programmes, each at one of four depths: Intro, Applied, Core or Mastery. Installing, configuring and securing a node is Core in CHT-100 and Mastery in CHT-300. Sorting key and partition design is Core in CHT-200 and Mastery in CHT-300. Sharding, replication and Keeper are Core in CHT-300 and Mastery in CHT-400, because you only truly understand replication once you have repaired it. Incident diagnosis is Core in CHT-400; advanced analytical SQL is Core in CHT-500. The matrix is what lets a corporate customer see, per engineer, where the team stands before and after a cohort, and it is the basis of the management report we issue at the end of every corporate engagement.
The ClickHouse certification ladder has three tiers, and every one of them ends in a capstone defended against a live multi-node cluster in front of an examiner. Certified ClickHouse Professional requires CHT-100 plus a proctored written examination. Certified ClickHouse Engineer requires CHT-200, one specialist programme (CHT-400 or CHT-500) and a graded pipeline build. Certified ClickHouse Architect requires CHT-300, CHT-400 and a defended multi-region design.
We are candid that the credential is issued by ChistaDATA rather than by a standards body; its weight comes from the fact that a defended capstone on a real cluster is far harder to fake than a multiple-choice score, which is why enterprise customers have adopted it as an on-call readiness standard and why it carries practical meaning in technical hiring.
Delivery models, the lab environment and ClickHouse certification preparation
Four delivery models cover the ways teams actually learn and prepare for ClickHouse certification. Instructor-led virtual cohorts run two live sessions a week, recorded, with office hours and a shared cluster per cohort, and suit distributed teams and individual practitioners. On-site workshops run three to five consecutive eight-hour days on the customer’s own schemas and data volumes, for teams starting a migration or hardening a deployment. Self-paced education offers video modules, downloadable datasets, auto-graded labs and asynchronous review for continuous onboarding. Embedded mentorship places a principal engineer in a team’s sprint ceremonies and code reviews for a fixed number of hours a week, for production teams that need coaching more than a classroom.
The lab environment is the same for all of them, and it is not a laptop container. Each cohort gets a three-shard, two-replica cluster with a three-node ClickHouse Keeper ensemble, provisioned on demand, loaded with multi-billion-row public datasets so that a poor sorting key measurably hurts and a good one measurably helps. The companion stack is Kafka or Redpanda, Grafana, Prometheus, MinIO object storage and Kubernetes with the ClickHouse operator.
CHT-400 adds pre-broken sandboxes that reset between attempts. Labs are available on the current stable line and one previous LTS so that upgrade rehearsal is a lab, not a slide, and every learner keeps access for 30 days after the programme for revision and ClickHouse certification preparation. Customers who must train inside their own VPC can host the same labs from our infrastructure-as-code templates.
Corporate ClickHouse training
Corporate engagements customise four things: module selection per role, customer schemas replacing the sample schemas under NDA, the customer’s own incident history as the CHT-400 case set, and the customer’s latency targets as lab pass criteria. That last point matters more than it sounds: when the pass criterion for a lab is the p95 your product actually has to hit, the labs double as a design review of your platform.
Contracting is built for procurement: cohort licensing with volume tiers, multi-year enterprise agreements, purchase-order-friendly paperwork, pre- and post-assessment scores per competency delivered as a management report, a documented on-call readiness sign-off standard built on the ClickHouse certification tiers, an optional transition into managed services, and annual delta workshops covering each new release line.
University and academic partnership programmes
The academic side reuses the same material at a different cadence. A fourteen-week semester module packages lecture slides, lab manuals, graded assignments and an examination bank from CHT-100 and CHT-200 for an undergraduate database systems or data engineering elective. A graduate seminar takes the internals reading list, source-code walkthroughs and distributed-systems assignments from CHT-300 into a master’s advanced database or systems course.
Capstone and thesis sponsorship gives final-year students anonymised workloads, engineer mentorship, benchmark harnesses and publication support. Faculty enablement is train-the-trainer education for teaching staff with lab infrastructure guidance. And a student certification pathway offers discounted examination vouchers so that graduates leave with the ClickHouse certification alongside their degree. Departments can request syllabus mapping documents that align modules to existing learning outcomes and accreditation requirements.
Engagement process and contracting
The process has six stages and the first two cost nothing. A discovery call establishes workload, team size, current pain and target outcomes. The skills assessment places each engineer on the ladder. Syllabus and SOW fixes module selection, schedule, pass criteria and pricing. Delivery is live sessions, labs on a dedicated cluster and weekly reviews. Certification is the proctored exam plus the capstone defence on the live cluster. Aftercare is 30 days of lab access, an annual refresh, and optional 24×7 support. Public instructor-led cohorts start monthly; customised corporate training typically starts two to three weeks after syllabus sign-off, which is the time it takes us to rebuild the labs around your schemas.
ClickHouse training and ClickHouse certification FAQ
Which programme should my team start with? Take the free 45-minute skills assessment and let it decide. As a rule of thumb, teams with no ClickHouse exposure start at CHT-100; teams already ingesting data but fighting query latency or part counts belong in CHT-200; teams running multi-node production clusters start at CHT-300 or go straight to CHT-400.
Can CHT-400 be taken on its own? Yes. It is regularly delivered as a standalone five-day workshop for on-call teams already operating ClickHouse. The only prerequisites are comfort with ClickHouse SQL and Linux administration; the diagnostic methodology is taught from first principles.
Is training delivered on our own data and schemas? For corporate engagements, yes, under NDA. Sample datasets are replaced with your schemas, query patterns and latency targets, so the labs double as a design review of your actual platform.
What does CHT-500 replace? It is aimed at teams computing funnels, cohorts, retention or attribution in Spark, pandas or warehouse jobs. It teaches the ClickHouse function families that express those workloads as single queries, along with the cost model for deciding when that is and is not appropriate.
Do you provide the lab infrastructure? Yes. Each cohort gets a dedicated multi-shard, multi-replica cluster with Kafka, Grafana, Prometheus, object storage and Kubernetes. Teams that prefer to train inside their own VPC can host the labs from our infrastructure-as-code templates.
Is the ClickHouse certification recognised outside ChistaDATA? The credential is issued by ChistaDATA and is used by enterprise customers as their internal on-call readiness standard. Because it requires a defended capstone on a live cluster rather than a multiple-choice exam alone, it carries practical weight in technical hiring, and we would rather say that plainly than overstate it.
How quickly can a cohort start? Public cohorts begin monthly. Customised corporate training typically starts two to three weeks after syllabus sign-off.
Where I land on ClickHouse certification
A certificate is worth exactly what it took to earn. We built ChistaDATA University so that the ClickHouse certification at the top of it can only be earned by someone who has provisioned a replicated cluster, broken it, repaired it, and defended the design to an engineer who runs the same thing in production.
If that is the standard your team needs to meet, the programme page has the module detail and the assessment booking, and the assessment itself costs nothing. And the usual caveat applies even to training: the lab settings and queries in this post are teaching material on lab data; test them on your own workload before they go anywhere near production, and keep your backup and DR posture in good order while you do.
Sources: ChistaDATA University programme page, ClickHouse changelog, ClickHouse parametric aggregate functions (windowFunnel, retention), system.replicas, system.part_log.
