ClickHouse workshop guide for hands-on learning

A ClickHouse® workshop gives developers hands-on time with schema design, ingestion, queries, and pre-aggregation before committing to production architecture. This guide structures a half-day session with five modules and exercises that map to patterns you will use in real analytics projects.

ClickHouse is a column-oriented SQL database for OLAP workloads, per ClickHouse documentation. The exercises below use standard ClickHouse SQL. You can run them on a local ClickHouse instance, ClickHouse Cloud, or Tinybird's managed environment.

Workshop prerequisites

Participants should have:

• SQL experience (any relational database)
• A local environment with ClickHouse or a Tinybird account
• Optional: sample NDJSON event files for ingestion exercises

Estimated duration: 3 to 4 hours with breaks.

Module 1: Schema design (45 minutes)

Goal: Create a MergeTree table with the right partition key, sort key, and column types.

Exercise: Define an events table for web analytics:

CREATE TABLE events
(
    event_time   DateTime64(3),
    user_id      String,
    event_name   LowCardinality(String),
    page_url     String,
    country      LowCardinality(String),
    properties   String
)
ENGINE = MergeTree()
PARTITION BY toYYYYMM(event_time)
ORDER BY (event_name, event_time, user_id);

Discussion points:

• Why PARTITION BY toYYYYMM for time-series data
• Why event_name leads the sort key when most queries filter by event type
• When to use LowCardinality(String) vs plain String

Reference: ClickHouse create table example

Checkpoint: Participants run DESCRIBE TABLE events and explain their sort key choice to the group.

Module 2: Ingestion (45 minutes)

Goal: Load data through batch insert and understand production ingestion paths.

Exercise 1: Batch insert 1,000 rows.

Exercise 2: Discuss production paths:

• HTTP JSON inserts for application events
• Kafka engine for high-throughput streams
• S3 or GCS batch loads for historical backfills

CREATE TABLE events_kafka
(
    event_time   DateTime64(3),
    user_id      String,
    event_name   String,
    page_url     String,
    country      String,
    properties   String
)
ENGINE = Kafka
SETTINGS
    kafka_broker_list = 'localhost:9092',
    kafka_topic_list = 'events',
    kafka_group_name = 'ch_workshop',
    kafka_format = 'JSONEachRow';

Per ClickHouse Kafka engine docs, the engine consumes topics and routes rows through a materialized view into a MergeTree table.

Checkpoint: Verify row count with SELECT count() FROM events.

Module 3: Queries and optimization (60 minutes)

Goal: Write aggregation queries and apply PREWHERE, sampling, and sort-key-aware filters.

Exercise 1: Hourly event counts by type:

SELECT
    toStartOfHour(event_time) AS hour,
    event_name,
    count()                   AS events,
    uniq(user_id)             AS users
FROM events
WHERE event_time >= now() - INTERVAL 7 DAY
GROUP BY hour, event_name
ORDER BY hour, event_name;

Exercise 2: Top pages with PREWHERE:

SELECT
    page_url,
    count() AS views
FROM events
PREWHERE event_name = 'page_view'
WHERE event_time >= now() - INTERVAL 1 DAY
GROUP BY page_url
ORDER BY views DESC
LIMIT 20;

Exercise 3: Exploratory sampling on a large table:

SELECT event_name, count()
FROM events
SAMPLE 0.1
WHERE event_time >= now() - INTERVAL 30 DAY
GROUP BY event_name;

For how to make SQL database faster, workshop participants should leave understanding sort key alignment and PREWHERE as the first optimization levers.

Checkpoint: Each participant presents one query and explains which columns the engine reads from disk.

Module 4: Pre-aggregation and lifecycle (60 minutes)

Goal: Build a materialized view, query the rollup, and set TTL.

Exercise 1: Create an hourly rollup:

CREATE TABLE events_hourly
(
    hour        DateTime,
    event_name  LowCardinality(String),
    events      UInt64,
    users       AggregateFunction(uniq, String)
)
ENGINE = AggregatingMergeTree()
PARTITION BY toYYYYMM(hour)
ORDER BY (event_name, hour);

CREATE MATERIALIZED VIEW events_hourly_mv
TO events_hourly
AS SELECT
    toStartOfHour(event_time) AS hour,
    event_name,
    count()                   AS events,
    uniqState(user_id)        AS users
FROM events
GROUP BY hour, event_name;

Exercise 2: Query the rollup:

SELECT
    hour,
    event_name,
    sum(events)              AS total,
    uniqMerge(users)         AS unique_users
FROM events_hourly
WHERE hour >= now() - INTERVAL 24 HOUR
GROUP BY hour, event_name;

Exercise 3: Add TTL:

ALTER TABLE events
    MODIFY TTL event_time + INTERVAL 90 DAY;

For enterprise-grade real-time analytics, materialized views and TTL are the patterns that keep dashboard queries fast as raw event volume grows.

Checkpoint: Compare query time on raw events vs events_hourly for the same dashboard chart.

Module 5: From workshop to production (30 minutes)

Discussion: What changes between the workshop environment and production:

• Ingestion moves from batch inserts to streaming pipelines
• Schema changes require migration planning
• Monitoring part count, merge queue, and compression ratios
• API layer for dashboards (Tinybird Pipes, Grafana, custom apps)

For ClickHouse backend engineers, the operational topics beyond this workshop include cluster sizing, backup strategy, and query profiling.

For real-time streaming data architectures that scale, the workshop schema becomes one node in a pipeline that includes Kafka, CDC, and HTTP endpoints.

Running the workshop on Tinybird

Tinybird maps each workshop module to platform features:

For real-time dashboard step by step, the workshop conclusion extends naturally into building a dashboard backed by Tinybird endpoints.

The ClickHouse course covers follow-up topics for participants who want structured learning beyond the workshop.

Resend processes 100TB per month on Tinybird with 62ms p90 query latency without relying on cache, per Tinybird's Resend customer story. Tinybird is SOC 2 Type II certified.

Workshop takeaway

Participants should leave able to:

1. Design a MergeTree table with partition and sort keys aligned to query patterns
2. Ingest events via batch and describe Kafka ingestion for production
3. Write aggregation queries with PREWHERE and sampling
4. Build a materialized view and query it with -Merge combinators
5. Apply TTL for data lifecycle management

Those five skills cover the majority of ClickHouse analytics projects. The workshop is the starting point. Production adds ingestion pipelines, monitoring, and API layers. On Tinybird, the hosted ingestion layer, hosted API layer, and Workspace cover those steps when you are ready to ship.