>GLB_
Go back

Designing Event Schemas for Product Analytics in ClickHouse

>GLB_ // tools · ClickHouse

Product analytics looks simple: user did X at time T with properties P. Then the property list grows to 300 keys, someone sends the same event three times, and the funnel query that took 200 ms now takes 40 s.

This post is the schema I’d start with today, and the reasoning behind each decision. It builds on the previous post on MergeTree internals; if ORDER BY, granules or merges are unfamiliar, read that one first.

The shape of an event

{
  "event_id": "0192f7a0-7c1e-7d2a-9a41-3b5e1f0c8d11",
  "project_id": 12,
  "event_name": "checkout_completed",
  "user_id": "u_83421",
  "anonymous_id": "a_f19c...",
  "session_id": "s_77ab...",
  "timestamp": "2026-10-08T14:03:22.381Z",
  "context": { "platform": "web", "country": "AR", "app_version": "4.2.0" },
  "properties": { "plan": "pro", "amount": "49.00", "coupon": "LAUNCH" }
}

Three groups of fields:

  1. Identity and time: always present, always filtered on.
  2. Context: small, stable set (platform, country, version). Present on every event.
  3. Properties: event-specific, open-ended, schema changes weekly.

The schema follows that split.

Option 1: everything as columns (wide table)

One column per property. Fast and well compressed, but every new property is an ALTER TABLE, and a table with hundreds of mostly empty columns is painful to operate. Works when you control every producer.

Option 2: Map(String, String)

properties Map(LowCardinality(String), String)

Flexible, no migrations. Downside: accessing properties['plan'] reads the entire map column (all keys and values) for every row scanned. Values are strings, so numeric filters need casts.

Option 3: the JSON type

The JSON data type stores each discovered path as its own subcolumn, so properties.plan reads only that path. It’s production-ready in recent releases (GA since 25.3); check your version before relying on it.

properties JSON(max_dynamic_paths = 256)

max_dynamic_paths caps how many paths get dedicated subcolumns; the rest go to a shared storage. Paths can also be typed up front:

properties JSON(amount Decimal(12, 2), plan LowCardinality(String))

Recommendation: hybrid

The schema

CREATE TABLE events
(
    -- identity
    event_id      UUID,
    project_id    UInt32,
    event_name    LowCardinality(String),
    user_id       String,
    anonymous_id  String,
    session_id    String,

    -- time
    timestamp     DateTime64(3, 'UTC') CODEC(Delta, ZSTD(1)),
    received_at   DateTime64(3, 'UTC') DEFAULT now64(3),

    -- context
    platform      LowCardinality(String),
    country       LowCardinality(FixedString(2)),
    app_version   LowCardinality(String),

    -- open-ended payload
    properties    JSON(max_dynamic_paths = 256),

    -- promoted hot property
    plan          LowCardinality(String) MATERIALIZED properties.plan::String,

    INDEX idx_user user_id TYPE bloom_filter(0.01) GRANULARITY 4
)
ENGINE = ReplacingMergeTree(received_at)
PARTITION BY toYYYYMM(timestamp)
ORDER BY (project_id, event_name, toDate(timestamp), user_id, event_id)
TTL toDateTime(timestamp) + INTERVAL 13 MONTH DELETE;

Every line is a decision. Going through them.

LowCardinality everywhere it fits

event_name, platform, country, app_version, plan: tens to thousands of distinct values. Dictionary encoding cuts storage and speeds up GROUP BY. Rule of thumb from the docs: effective below ~10k distinct values. user_id is high cardinality, so plain String.

No Nullable

Nullable adds a separate null-map file per column and blocks some optimizations. Use empty strings or sentinel values ('', 0) for “missing”. anonymous_id is '' once the user is identified, not NULL.

DateTime64(3, 'UTC') + Delta codec

Millisecond precision, timezone fixed at storage. Timestamps are monotonic-ish inside a sorted part, so Delta before ZSTD compresses them well.

timestamp (client time) and received_at (server time) are separate on purpose: client clocks lie, and you need server time for dedup versioning and late-arrival debugging.

ORDER BY: designed for the queries, not the data

Typical product analytics queries:

- events of type X, in project P, in date range D       (most dashboards)
- funnel/retention per user across event types           (needs user grouping)
- everything a given user did                            (debugging, support)

(project_id, event_name, toDate(timestamp), user_id, event_id):

“Everything a given user did” doesn’t use the sorting key well (user_id is 4th). That’s what the bloom_filter skip index is for. If per-user timelines are a primary use case, consider a projection or a second table ordered by (project_id, user_id, timestamp) fed by a materialized view.

Partitioning: monthly

Monthly partitions make TTL and DROP PARTITION cheap. Do not partition by project_id: with many tenants that’s thousands of partitions and the “too many parts” error.

Dedup with ReplacingMergeTree

SDKs retry. Queues redeliver. You will get duplicates.

ReplacingMergeTree(received_at) keeps, for rows with the same sorting key, the one with the highest received_at. But dedup only happens during merges, which run eventually. At query time you can still see duplicates. Options:

-- exact, slower: dedupes at read time
SELECT count() FROM events FINAL
WHERE project_id = 12 AND event_name = 'checkout_completed';

-- usually fine for analytics: count distinct ids
SELECT uniqExact(event_id) FROM events
WHERE project_id = 12 AND event_name = 'checkout_completed';

FINAL performance has improved significantly in recent versions and is reasonable when the query is already narrowed by the sorting key. Measure it on your data.

Generate event_id client-side (UUIDv7 is a good fit: time-ordered) so retries carry the same id.

plan is computed from properties at insert time and stored as a real LowCardinality column. Queries use plan directly, no JSON path lookup. It’s not returned by SELECT *. Adding one later:

ALTER TABLE events
    ADD COLUMN coupon LowCardinality(String)
    MATERIALIZED properties.coupon::String;

ALTER TABLE events MATERIALIZE COLUMN coupon;  -- backfill existing parts

TTL

TTL ... + INTERVAL 13 MONTH DELETE drops raw data after 13 months (one year + one month for YoY comparisons). With ttl_only_drop_parts = 1 and monthly partitions, whole parts get dropped instead of rewritten.

Pre-aggregation with materialized views

Raw events answer everything; pre-aggregated tables answer the dashboard in milliseconds. Daily active users per project:

CREATE TABLE dau
(
    project_id UInt32,
    day        Date,
    platform   LowCardinality(String),
    users      AggregateFunction(uniq, String)
)
ENGINE = AggregatingMergeTree
ORDER BY (project_id, day, platform);

CREATE MATERIALIZED VIEW mv_dau TO dau AS
SELECT
    project_id,
    toDate(timestamp) AS day,
    platform,
    uniqState(user_id) AS users
FROM events
WHERE user_id != ''
GROUP BY project_id, day, platform;

Query with the -Merge combinator:

SELECT day, uniqMerge(users) AS dau
FROM dau
WHERE project_id = 12 AND day >= today() - 30
GROUP BY day
ORDER BY day;

Notes:

Funnels and retention

ClickHouse has built-in parametric aggregates for both.

Funnel: signup → activation → purchase within 7 days:

SELECT
    level,
    count() AS users
FROM
(
    SELECT
        user_id,
        windowFunnel(604800)(
            toDateTime(timestamp),
            event_name = 'signed_up',
            event_name = 'project_created',
            event_name = 'checkout_completed'
        ) AS level
    FROM events
    WHERE project_id = 12
      AND toDate(timestamp) BETWEEN '2026-09-01' AND '2026-09-30'
      AND event_name IN ('signed_up', 'project_created', 'checkout_completed')
    GROUP BY user_id
)
GROUP BY level
ORDER BY level;

The window is in the unit of the timestamp argument, hence the toDateTime() cast to keep it in seconds. The event_name IN (...) filter matters: it’s the second key column, so it prunes granules before the funnel runs.

Retention: users who signed up on day 0 and came back on day 1 and day 7:

SELECT
    sum(r[1]) AS signed_up,
    sum(r[2]) AS day_1,
    sum(r[3]) AS day_7
FROM
(
    SELECT
        user_id,
        retention(
            toDate(timestamp) = '2026-09-01' AND event_name = 'signed_up',
            toDate(timestamp) = '2026-09-02',
            toDate(timestamp) = '2026-09-08'
        ) AS r
    FROM events
    WHERE project_id = 12
      AND toDate(timestamp) IN ('2026-09-01', '2026-09-02', '2026-09-08')
    GROUP BY user_id
);

Ingestion

curl 'http://localhost:8123/?query=INSERT%20INTO%20events%20FORMAT%20JSONEachRow' \
  --data-binary @events.ndjson

Anti-patterns

PatternProblemInstead
String for every columnLarger storage, slower GROUP BYLowCardinality, numeric types, UUID
Nullable by defaultExtra files, fewer optimizationsSentinels ('', 0)
ORDER BY (timestamp)No pruning by project or eventTenant and event type first
PARTITION BY project_idThousands of partsMonthly partition
One insert per eventToo many partsBatch or async_insert
Dashboards on raw eventsRe-scanning history every refreshAggregatingMergeTree + MV
Trusting ReplacingMergeTree for exact countsDedup is eventualFINAL or uniqExact(event_id)

Checklist

References

Share this post:
Share this post on X Share this post via email

Related Posts

Previous Post
Managing OAuth2 Token Rotation in Postman Without Overwriting Environments
Next Post
Why ClickHouse Is Fast: Understanding MergeTree Internals