Recipe: RudderStack events on ClickHouse

This recipe takes RudderStack events that RudderStack’s ClickHouse destination loads into your cluster, and makes them ready for funnels, retention and paths in Kubit. At the end, every track, page and screen call counts as an Event, and each person counts as one User whether they were signed in or not. It takes about an hour. It combines three pages and adds the RudderStack-specific parts: Connect ClickHouse, Connect RudderStack and Product analytics in your warehouse.

Before you start

RudderStack’s ClickHouse destination is enabled and has completed at least one sync
The ClickHouse database set on that destination (the Database field in RudderStack). This recipe calls it <rudderstack_db>.
An admin ClickHouse user that can create users, roles, databases and views
The password for the KUBIT user, from the Kubit team
The names of 3 events in your signup or activation flow. This recipe uses Signup Started, Account Created and Project Created.

Prompt for your coding agent

Paste this into Claude Code, Cursor or Codex. It drafts the SQL and leaves running it to you:

Read https://learn.kubit.ai/product-analytics/cookbook/rudderstack-clickhouse.md.
Write all the ClickHouse SQL in it for the RudderStack database <rudderstack_db> into kubit_rudderstack.sql.
Don't connect to ClickHouse or run anything.

Steps

Check the RudderStack tables

Run this as your admin user to confirm the destination has loaded data:

SELECT name, total_rows
FROM system.tables
WHERE database = '<rudderstack_db>'
ORDER BY total_rows DESC
LIMIT 20;

You should see tracks, identifies, and pages or screens, plus one table per track event name.

Give Kubit read access

Follow Connect ClickHouse. It creates the KUBIT user, the KUBIT_R and KUBIT_W roles and the KUBIT_DB helper database, allows Kubit’s IP ranges and adds the connection.In its Grant permissions step, grant read access on the whole RudderStack database instead of table by table:

GRANT SELECT ON <rudderstack_db>.* TO KUBIT_R;

RudderStack creates a new table the first time it sees a new event name. A database grant covers those tables too.

Create one view for all events

RudderStack writes track, page and screen calls to separate tables. This view puts them in one table with the same columns:

CREATE VIEW KUBIT_DB.rudder_events AS
SELECT id, anonymous_id, user_id, event_text AS event_name, 'track' AS event_type, timestamp, received_at
FROM <rudderstack_db>.tracks
UNION ALL
SELECT id, anonymous_id, user_id, coalesce(name, 'Page Viewed') AS event_name, 'page' AS event_type, timestamp, received_at
FROM <rudderstack_db>.pages
UNION ALL
SELECT id, anonymous_id, user_id, coalesce(name, 'Screen Viewed') AS event_name, 'screen' AS event_type, timestamp, received_at
FROM <rudderstack_db>.screens;

Remove the pages or screens part if that table doesn’t exist. A web-only source has no screens table.

Create the identity map

This view maps each anonymous ID to the most recent user ID it was identified as:

CREATE VIEW KUBIT_DB.rudder_identity AS
SELECT anonymous_id, argMax(user_id, timestamp) AS resolved_user_id
FROM <rudderstack_db>.identifies
WHERE anonymous_id IS NOT NULL AND user_id IS NOT NULL
GROUP BY anonymous_id;

Use identifies, not users. The ClickHouse destination skips the users table by default (the Skip User Table setting), and users has no anonymous_id column.

Model the events in Kubit

In the Modeler:

Add KUBIT_DB.rudder_events as a fact table, with timestamp as the event time. RudderStack corrects timestamp for client clock skew, so it’s closer to when the user acted than received_at.
Add KUBIT_DB.rudder_identity as a dimension table, joined on rudder_events.anonymous_id = rudder_identity.anonymous_id as a left join. Events from users who never signed in stay in the model.
Create the subjects below.

Subject	Definition
Event	`rudder_events.id`
User	A custom column: `coalesce(rudder_events.user_id, rudder_identity.resolved_user_id, rudder_events.anonymous_id)`

The User column picks the signed-in ID first, then the ID the device later signed in as, then the anonymous ID. Events from before and after signup then count as one person.

Add event properties

For each event you analyze, join its table on id and enable the property columns you need as fields, for example project_created.template.Enable context_* columns you filter or group by often, such as context_os_name, context_app_version and context_page_path. They exist on every RudderStack table.

Verify

Check the model with three reports. Each has a SQL query to run in ClickHouse for the same numbers. Compare a full day that ended at least 1 hour ago, so RudderStack’s sync has finished. Totals should match within 1%.

Daily active users

Build a Query report counting unique Users, all events, daily, for the last 14 days. Or ask your assistant:

“Show daily active users for the last 14 days”

SELECT toDate(e.timestamp) AS day,
       uniqExact(coalesce(e.user_id, i.resolved_user_id, e.anonymous_id)) AS dau
FROM KUBIT_DB.rudder_events AS e
LEFT JOIN KUBIT_DB.rudder_identity AS i ON e.anonymous_id = i.anonymous_id
WHERE e.timestamp >= toStartOfDay(now() - INTERVAL 14 DAY)
GROUP BY day
ORDER BY day;

Build a Funnel with 3 steps, Signup Started, Account Created and Project Created, with a 1-day conversion window over the last 30 days. Or ask:

“Build a funnel from Signup Started to Account Created to Project Created in the last 30 days, with a 1 day conversion window”

Step 1 should be close to:

SELECT uniqExact(coalesce(e.user_id, i.resolved_user_id, e.anonymous_id)) AS users
FROM KUBIT_DB.rudder_events AS e
LEFT JOIN KUBIT_DB.rudder_identity AS i ON e.anonymous_id = i.anonymous_id
WHERE e.event_name = 'Signup Started'
  AND e.timestamp >= toStartOfDay(now() - INTERVAL 30 DAY);

If step 2 is close to 0 while step 1 is large, the User subject is splitting people at signup. Users start anonymous and get a user_id at Account Created, so the steps only connect through the identity map.

Weekly retention

Build a Retention report: starting event Account Created, returning on any event, weekly, 8 weeks. Or ask:

“Weekly retention for 8 weeks for users who did Account Created, returning on any event”

If it fails

Errors while creating the views

Symptom	Cause	Fix
`Unknown expression identifier 'name'`	The `pages` or `screens` table has no `name` column because no call set a name	Replace `coalesce(name, 'Page Viewed')` with `'Page Viewed'`
`There is no supertype for types`	A column has different types in two tables	Cast it in each `SELECT`, for example `toString(user_id)`
`Table <rudderstack_db>.screens does not exist`	The source sends no `screen` calls	Remove the `screens` part of the view

Counts are higher than expected

Duplicate rows are the most common reason. RudderStack creates ClickHouse tables with the ReplacingMergeTree engine, ordered by (received_at, id). When RudderStack retries a load, the same event can be stored twice until ClickHouse merges the table parts in the background, which can take minutes or hours.

SELECT count() AS row_count, uniqExact(id) AS unique_events
FROM <rudderstack_db>.tracks
WHERE timestamp >= now() - INTERVAL 1 DAY;

If row_count is larger than unique_events, the table has unmerged duplicates. Event counts in Kubit can include them. Unique user counts don’t change.

Counts are lower than expected

Check	Query or action	What it means
The event’s table exists	`SELECT name FROM system.tables WHERE database = '<rudderstack_db>' AND name = '<event_table>'`	No row: RudderStack hasn’t synced that event yet
Kubit can read it	`SHOW GRANTS FOR KUBIT_R`	No `SELECT ON <rudderstack_db>.*`: run the grant in step 2
The latest sync finished	`SELECT max(received_at) FROM <rudderstack_db>.tracks`	Older than the destination’s Sync Frequency: check its sync status in RudderStack
Events reach `tracks`	The destination’s Skip Tracks Table setting	On: events are only in per-event tables, so the view misses them

RudderStack lowercases event names and converts them to snake case for table names. Search for account_created, not Account Created. The original name is in event_text.

Daily numbers are shifted

ClickHouse stores DateTime values as UTC instants and shows them in the server’s time zone unless the column or query sets one. Check it with SELECT timezone(), and compare one day in UTC with toDate(timestamp, 'UTC') = yesterday().

Events land in the future

RudderStack calculates timestamp as received_at - (sent_at - original_timestamp) to correct for client clock skew. A device with a badly wrong clock can still produce future dates:

SELECT count() AS future_events
FROM <rudderstack_db>.tracks
WHERE timestamp > now() + INTERVAL 1 HOUR;

If the count is high, use received_at as the event time in the model.

Booleans and nested properties

RudderStack stores booleans as UInt8 in ClickHouse, so filter on = 1, not = true. Nested properties are flattened with the parent key as prefix: { plan: { tier: "pro" } } becomes plan_tier. If JSON Columns is set on the destination, the listed paths go into one native JSON column instead, which needs ClickHouse 25.3 or later.

Next steps

Reports overview: every report type and when to use it.
Dashboards: put the three check reports on one dashboard.

Start here

Agent Observability

Product Analytics

Bring your own warehouse

Recipe: RudderStack events on ClickHouse

Before you start

Prompt for your coding agent

Steps

Check the RudderStack tables

Give Kubit read access

Create one view for all events

Create the identity map

Model the events in Kubit

Add event properties

Verify

Daily active users

Weekly retention

If it fails

Errors while creating the views

Counts are higher than expected

Counts are lower than expected

Daily numbers are shifted

Events land in the future

Booleans and nested properties

Next steps

​Before you start

​Prompt for your coding agent

​Steps

Check the RudderStack tables

Give Kubit read access

Create one view for all events

Create the identity map

Model the events in Kubit

Add event properties

​Verify

​Daily active users

​Signup funnel

​Weekly retention

​If it fails

​Errors while creating the views

​Counts are higher than expected

​Counts are lower than expected

​Daily numbers are shifted

​Events land in the future

​Booleans and nested properties

​Next steps

Before you start

Prompt for your coding agent

Steps

Verify

Daily active users

Signup funnel

Weekly retention

If it fails

Errors while creating the views

Counts are higher than expected

Counts are lower than expected

Daily numbers are shifted

Events land in the future

Booleans and nested properties

Next steps