---
title: "ClickHouse workshop guide for hands-on learning"
excerpt: "Half-day ClickHouse workshop: schema design, ingestion, queries, materialized views, and TTL. Exercises for local or Tinybird."
authors: "Tinybird"
categories: "AI Resources"
createdOn: "2026-07-21 00:00:00"
publishedOn: "2026-07-21 00:00:00"
updatedOn: "2026-07-21 00:00:00"
status: "published"
---

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](https://clickhouse.com/docs). 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:

```sql
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](https://www.tinybird.co/blog/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

```sql
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](https://clickhouse.com/docs/en/engines/table-engines/integrations/kafka), 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:

```sql
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`:

```sql
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:

```sql
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](https://www.tinybird.co/blog/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:

```sql
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:

```sql
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:

```sql
ALTER TABLE events
    MODIFY TTL event_time + INTERVAL 90 DAY;
```

For [enterprise-grade real-time analytics](https://www.tinybird.co/blog/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](https://www.tinybird.co/blog/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](https://www.tinybird.co/blog/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](https://www.tinybird.co/) maps each workshop module to platform features:

| Workshop step | Tinybird equivalent |
| --- | --- |
| Create table | `.datasource` file |
| Batch insert | `tb datasource append` or [Events API](https://www.tinybird.co/docs/ingest/events-api) |
| Kafka ingestion | [Kafka connector](https://www.tinybird.co/docs/forward/ingest-data/connectors/kafka) Data Source |
| Aggregation query | SQL Pipe |
| Publish endpoint | Deploy Pipe as a sub-second API endpoint |
| Schema iteration | [Branch](https://www.tinybird.co/) with zero-copy prod data |

For [real-time dashboard step by step](https://www.tinybird.co/blog/real-time-dashboard-step-by-step), the workshop conclusion extends naturally into building a dashboard backed by Tinybird endpoints.

The [ClickHouse course](https://www.tinybird.co/blog/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](https://www.tinybird.co/customer-stories/resend). 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.

{% cta
  title="Run the workshop without setup friction"
  text="Tinybird is managed ClickHouse with local dev, Branches, and sub-second SQL APIs from Pipes. Complete the workshop in one session."
  button={href: "https://cloud.tinybird.co/signup", target: "_blank", text: "Try Tinybird free"}
/%}