Can ClickHouse power event tracking?
ClickHouse has documented how its own team built product analytics on ClickHouse, including event collection, materialized views, acquisition-to-conversion analysis, user paths, retention, churn, and integration with other warehouse data. That architecture demonstrates the database fit without implying that a database alone is a finished analytics product.
What architecture should sit around ClickHouse?
SensorFlow uses: official Sensors Data SDKs → Go ingestion service → Redis-assisted processing → ClickHouse event and user tables → Apache Superset. The HTTP service forms a trust boundary: it parses requests, rejects invalid payloads, records failures, and protects database credentials from clients.
| Decision | Recommended starting point | Why it matters |
|---|---|---|
| Event identity | Stable distinct_id plus explicit login transition | Funnels and retention depend on consistent users |
| Event time | Keep event time and receive time | Late events otherwise distort windows |
| Event name | Low-cardinality, governed vocabulary | Supports filtering and prevents near-duplicate metrics |
| Sort key | Align with frequent identity, event, and time filters | ClickHouse performance follows access patterns |
| Properties | Typed frequent fields; controlled evolution | Type drift silently breaks comparisons |
What does the SensorFlow table look like?
The repository initializes sensors.event with event, identity, time, platform, device, network, page, referrer, application, and geography fields. It uses a MergeTree-family engine and orders data by (distinct_id, event, time). That key favors user-event-time access; other workloads should benchmark their own filters rather than copying it blindly.
SELECT event,
count() AS event_count,
uniqExact(distinct_id) AS unique_users
FROM sensors.event
WHERE time >= now() - INTERVAL 30 DAY
GROUP BY event
ORDER BY event_count DESC;How do you validate the pipeline?
- Create a test event with a unique marker, known identity, fixed timestamp, and representative property types.
- Confirm collector acceptance and inspect errors; do not treat HTTP 2xx as final proof.
- Query the exact marker in ClickHouse and compare every critical field.
- Repeat with anonymous-to-login identity, offline delay, retry, duplicate submission, and malformed properties.
- Query the same dataset through Superset and verify timezone and filters.
For migration, compare old and new paths over the same window. Event totals are only one check: compare unique identities, required-property completeness, type distributions, event-time lag, and business metric output.
How do you build funnels and retention?
Start with reviewed SQL whose semantics are explicit: ordered versus unordered steps, conversion window, identity key, timezone, and late-event handling. ClickHouse provides functions such as windowFunnel, but a function call does not decide the business definition. Store reviewed queries as Superset datasets or version-controlled SQL and assign an owner.
SELECT distinct_id,
windowFunnel(604800)(
time,
event = 'signup',
event = 'activation',
event = 'purchase'
) AS completed_step
FROM sensors.event
GROUP BY distinct_id;What are the production limits?
- Dynamic columns are convenient but uncontrolled schema changes can create operational churn.
- Exact distinct counts can be expensive; choose exact or approximate functions deliberately.
- Raw events require retention, backup, restore, deletion, and access-control procedures.
- Dashboards need cached or pre-aggregated datasets as scale and concurrency grow.
- SensorFlow does not remove the need for ClickHouse capacity planning and monitoring.