Sample ClickHouse Analytics Audit

Context

Acme Events is a SaaS product moving customer-facing analytics from Postgres into ClickHouse. Product managers need dashboards under two seconds for tenant-scoped event metrics. The current setup works in development, but production dashboards are slow and some daily totals disagree with Postgres exports.

• Source of truth: Postgres tables for accounts, users, and events.
• ClickHouse ingestion: CDC stream into a raw events table.
• Primary access pattern: tenant-filtered dashboards over the last 7-90 days.
• Known symptoms: duplicate rows after backfills, slow joins, and late-arriving event corrections.

Audit Scope

This sample focuses on the first pass of a one-week review: identify the highest-risk design choices, explain why they matter, and return a sequence of fixes the team can test without rewriting the whole system.

• Inputs: schema DDL, one representative slow query, ingestion notes, and dashboard expectations.
• Output: prioritized findings, concrete SQL/design recommendations, and follow-up questions for implementation.
• Excluded here: client-specific infrastructure, credentials, private datasets, and production access.

Representative Schema

CREATE TABLE events_raw
(
    tenant_id UUID,
    event_id UUID,
    user_id UUID,
    event_name LowCardinality(String),
    event_time DateTime64(3),
    ingested_at DateTime64(3),
    properties String,
    version UInt64,
    deleted UInt8
)
ENGINE = ReplacingMergeTree(version, deleted)
PARTITION BY toYYYYMM(event_time)
ORDER BY (event_name, event_time, tenant_id, user_id, event_id);

The table uses a ReplacingMergeTree, but the ordering key does not match the most important filters. Tenant-scoped dashboards need to skip by tenant_id and time first; this design makes ClickHouse read far more data before filtering.

Representative Slow Query

SELECT
    toDate(e.event_time) AS day,
    u.plan,
    count() AS events
FROM events_raw e
LEFT JOIN users_current u
    ON e.user_id = u.user_id
WHERE e.tenant_id = {tenant_id:UUID}
  AND e.event_time >= now() - INTERVAL 30 DAY
  AND e.event_name IN ('checkout_started', 'checkout_completed')
GROUP BY day, u.plan
ORDER BY day;

The query is a reasonable product question, but it combines a poor primary sort key, a runtime dimension join, and raw-event aggregation for a dashboard that users refresh repeatedly.

Findings

Summary: the largest risks are not raw ClickHouse capacity. They are table layout, deduplication semantics, and repeated dashboard queries that force too much work at request time.

P0Ordering key does not match dashboard access patterns

Most important queries filter by tenant, time range, and event name. The current ordering key starts with event_name, so tenant-scoped reads are inefficient. Move tenant and time earlier in the key for this workload.

ORDER BY (tenant_id, event_time, event_name, user_id, event_id)

P0Correctness checks are missing around CDC replacement

ReplacingMergeTree eventually collapses duplicates, but queries can observe duplicates unless they account for versioning or run after merges. Add explicit reconciliation checks against source counts and decide which queries require FINAL, pre-aggregation, or an already-deduplicated current table.

P1Dashboard query should not join raw events to user state on every refresh

The query joins high-volume raw events to a changing user dimension. For the dashboard path, either denormalize the required user attributes at ingestion time or build a rollup that records the plan dimension needed by the dashboard.

P1Rollups need late-event and correction behavior defined

Materialized views can make the dashboard fast, but they also make correctness mistakes durable. Define how late events, user-plan changes, deletes, and backfills should update the aggregate table before relying on it.

Prioritized Fix Plan

1. Create a new events table ordered by (tenant_id, event_time, event_name, user_id, event_id) and backfill one month for comparison.
2. Add daily reconciliation checks: source events, ClickHouse raw rows, deduplicated events, and dashboard aggregate totals.
3. Build a dashboard-specific aggregate for tenant/day/event/plan and test late-event behavior with synthetic replays.
4. Move repeated dashboard queries to the aggregate path; keep raw-event queries for drilldown and debugging.
5. Add runbook notes for backfills, merge lag, duplicate detection, and freshness alerts.

Example Deliverable

A real audit would include the team's actual constraints, representative EXPLAIN output, expected tradeoffs, and concrete migration steps. The goal is not a strategy deck; it is a fix list the engineering team can apply.