Why Product Analytics Dashboards Fail at Scale and How to Fix Them

timestamp: string; // ISO 8601 UTC properties: Record<string, unknown>; _metadata: { source: 'web' | 'mobile' | 'api'; sdk_version: string; }; };

export const EVENT_SCHEMA_VERSION = '1.0.0';

### Step 2: Ingest via Stream Broker
Use Kafka or Redpanda for durable, partitioned ingestion. Produce events from client SDKs with idempotency keys to prevent duplicates during retries.

```typescript
// ingest/producer.ts
import { Kafka, logLevel } from 'kafkajs';

const kafka = new Kafka({
  brokers: [process.env.KAFKA_BROKER!],
  logLevel: logLevel.WARN,
});

const producer = kafka.producer();

export async function trackEvent(event: ProductEvent) {
  await producer.connect();
  await producer.send({
    topic: 'product-events',
    messages: [
      {
        key: event.session_id,
        value: JSON.stringify(event),
        headers: { 'schema-version': EVENT_SCHEMA_VERSION },
      },
    ],
  });
}

Step 3: Pre-Aggregate with Materialized Views

Raw event tables should never serve dashboard queries. Use a columnar database (ClickHouse, PostgreSQL with TimescaleDB, or DuckDB) and create materialized views that compute metrics at ingestion time.

-- ClickHouse materialized view for daily active users & session counts
CREATE TABLE product_events (
  event_id String,
  user_id Nullable(String),
  session_id String,
  event_name String,
  timestamp DateTime64(3, 'UTC'),
  properties String,
  source LowCardinality(String)
) ENGINE = MergeTree()
ORDER BY (source, event_name, timestamp);

CREATE TABLE dashboard_daily_metrics (
  date Date,
  source LowCardinality(String),
  event_name LowCardinality(String),
  unique_users UInt64,
  total_sessions UInt64,
  event_count UInt64
) ENGINE = SummingMergeTree()
ORDER BY (date, source, event_name);

CREATE MATERIALIZED VIEW dashboard_daily_mv
TO dashboard_daily_metrics
AS SELECT
  toDate(timestamp) AS date,
  source,
  event_name,
  uniqExact(user_id) AS unique_users,
  uniqExact(session_id) AS total_sessions,
  count() AS event_count
FROM product_events
GROUP BY date, source, event_name;

Step 4: Serve via Aggregation API

The frontend should never query the database directly. Build a TypeScript API that reads from materialized views, applies time-range filters, and returns paginated, cached responses.

// api/dashboard.ts
import { Hono } from 'hono';
import { ClickHouseClient } from '@clickhouse/client';
import { cache } from './cache';

const app = new Hono();
const db = new ClickHouseClient({ host: process.env.CLICKHOUSE_URL });

app.get('/api/metrics/daily', async (c) => {
  const source = c.req.query('source') ?? 'web';
  const startDate = c.req.query('start') ?? '2024-01-01';
  const endDate = c.req.query('end') ?? new Date().toISOString().split('T')[0];
  const cacheKey = `metrics:${source}:${startDate}:${endDate}`;

  const cached = await cache.get(cacheKey);
  if (cached) return c.json(cached);

  const result = await db.query({
    query: `
      SELECT date, event_name, sum(unique_users) AS users, sum(event_count) AS events
      FROM dashboard_daily_metrics
      WHERE source = {source:String}
        AND date BETWEEN {start:Date} AND {end:Date}
      GROUP BY date, event_name
      ORDER BY date ASC
    `,
    format: 'JSONEachRow',
    query_params: { source, start: startDate, end: endDate },
  });

  const rows = await result.json();
  await cache.set(cacheKey, rows, { ttl: 300 }); // 5-min TTL
  return c.json(rows);
});

export default app;

Step 5: Frontend Integration Pattern

Use a React hook that handles loading states, error boundaries, and automatic refetching on filter changes. Avoid polling; trigger fetches on user interaction.

// hooks/useDashboardMetrics.ts
import { useQuery } from '@tanstack/react-query';
import { fetchMetrics } from '../api/client';

export function useDashboardMetrics(filters: { source: string; range: [string, string] }) {
  return useQuery({
    queryKey: ['dashboardMetrics', filters],
    queryFn: () => fetchMetrics(filters),
    staleTime: 1000 * 60 * 5, // 5 minutes
    refetchOnWindowFocus: false,
    retry: 1,
  });
}

Architecture Rationale

• Stream ingestion + materialized views decouples write throughput from read latency. Aggregation happens once at ingestion, not per dashboard refresh.
• Columnar storage optimizes for analytical queries. SummingMergeTree and ORDER BY clauses enable fast range scans and automatic rollups.
• API cache layer absorbs traffic spikes and reduces database load. TTL-based invalidation balances freshness with performance.
• Schema contracts prevent silent data corruption. Versioned headers enable backward-compatible schema evolution without breaking existing views.

Pitfall Guide

1. Querying Raw Event Tables for Dashboard Metrics

Raw tables contain billions of rows with high cardinality. Running COUNT(DISTINCT user_id) on every dashboard load forces full table scans. Pre-aggregate into materialized views or summary tables. Query raw data only for ad-hoc forensic analysis, never for interactive dashboards.

2. Ignoring Timezone and Daylight Saving Boundaries

Timestamps stored in local time break time-range filters during DST transitions. Store all events in UTC. Normalize user-facing timestamps at the presentation layer using Intl.DateTimeFormat or a library like date-fns-tz. Never aggregate across ambiguous clock boundaries.

3. Schema Drift Without Versioning

Adding properties to events without updating the schema contract causes type mismatches, null pointer exceptions, and silent metric drops. Enforce strict typing at ingestion. Use a schema registry or TypeScript interfaces with runtime validation (e.g., zod). Migrate views incrementally, not in big-bang deployments.

4. Coupling Dashboard UI Directly to Production Databases

Direct DB access from frontend applications bypasses authentication, rate limiting, and query optimization. It also exposes internal table structures. Always route through a backend API that enforces access controls, query whitelisting, and response pagination.

5. Late-Arriving Data Causing Metric Inconsistency

Network retries, mobile offline queues, and batch uploads cause events to arrive out of order. If materialized views process data sequentially, late events create gaps. Use event time processing with watermarks (Flink/ksqlDB) or configure your columnar DB to handle out-of-order inserts with INSERT QUORUM and late-arrival re-aggregation jobs.

6. Over-Fetching in Frontend Dashboards

Loading 30 days of hourly granularity for a high-level KPI card wastes bandwidth and memory. Implement server-side pagination, granularity selectors, and data downsampling. Return daily aggregates for monthly views, hourly for daily views, and raw events only for drill-down panels.

7. Missing Data Quality Validation Layer

Corrupted JSON, missing required fields, or duplicate event IDs poison analytics. Add a validation step in your ingestion pipeline that rejects malformed payloads, deduplicates by event_id, and routes invalid events to a dead-letter queue. Monitor validation failure rates as a SLO.

Production Best Practices

• Enforce idempotent ingestion using event_id as a deduplication key
• Set materialized view refresh intervals based on dashboard freshness requirements (2s for real-time, 5m for operational)
• Use connection pooling and query timeouts to prevent runaway analytical queries
• Implement metric definition documentation alongside code; treat metrics as product features
• Run weekly data reconciliation scripts comparing raw event counts vs. aggregated totals

Production Bundle

Action Checklist

• Define event schema contract with versioning and runtime validation
• Provision stream broker (Kafka/Redpanda) with partition strategy by session or tenant
• Create columnar database with materialized views for top 10 dashboard metrics
• Build aggregation API with query whitelisting, pagination, and TTL caching
• Implement frontend data hooks with staleTime, error boundaries, and granularity controls
• Add late-arrival handling and out-of-order ingestion support
• Deploy data quality monitors for schema drift, duplication, and freshness SLAs
• Document metric definitions, calculation logic, and data lineage

Decision Matrix

Scenario	Recommended Approach	Why	Cost Impact
<10M events/month, internal team dashboard	PostgreSQL + TimescaleDB + Materialized Views	Simpler ops, ACID compliance, sufficient for moderate scale	Low ($150-$300/mo)
10M-100M events/month, real-time product metrics	ClickHouse + Stream Ingestion + API Cache	Columnar compression, fast aggregations, predictable scaling	Medium ($400-$900/mo)
>100M events/month, multi-tenant SaaS analytics	Kafka + Flink + ClickHouse + Edge Cache	Horizontal scalability, exactly-once semantics, isolation per tenant	High ($1.2k-$3k/mo)
Legacy batch-only pipeline, no streaming	dbt + BigQuery/Snowflake + Scheduled Views	Leverages existing warehouse, minimal refactoring, batch-friendly	Medium ($600-$1.5k/mo)

Configuration Template

# docker-compose.analytics.yml
version: '3.8'
services:
  kafka:
    image: confluentinc/cp-kafka:7.5.0
    ports: ["9092:9092"]
    environment:
      KAFKA_NODE_ID: 1
      KAFKA_LISTENERS: PLAINTEXT://0.0.0.0:9092
      KAFKA_ADVERTISED_LISTENERS: PLAINTEXT://localhost:9092
      KAFKA_OFFSETS_TOPIC_REPLICATION_FACTOR: 1

  clickhouse:
    image: clickhouse/clickhouse-server:24.3
    ports: ["8123:8123", "9000:9000"]
    volumes:
      - ./clickhouse/init.sql:/docker-entrypoint-initdb.d/init.sql
      - clickhouse_data:/var/lib/clickhouse

  api:
    build: ./api
    ports: ["3000:3000"]
    environment:
      CLICKHOUSE_URL: http://clickhouse:8123
      KAFKA_BROKER: kafka:9092
      CACHE_TTL: 300

volumes:
  clickhouse_data:

-- clickhouse/init.sql
CREATE DATABASE IF NOT EXISTS analytics;
USE analytics;

CREATE TABLE IF NOT EXISTS product_events (
  event_id String,
  user_id Nullable(String),
  session_id String,
  event_name LowCardinality(String),
  timestamp DateTime64(3, 'UTC'),
  properties String,
  source LowCardinality(String)
) ENGINE = MergeTree()
ORDER BY (source, event_name, timestamp);

CREATE TABLE IF NOT EXISTS dashboard_daily_metrics (
  date Date,
  source LowCardinality(String),
  event_name LowCardinality(String),
  unique_users UInt64,
  total_sessions UInt64,
  event_count UInt64
) ENGINE = SummingMergeTree()
ORDER BY (date, source, event_name);

CREATE MATERIALIZED VIEW IF NOT EXISTS dashboard_daily_mv
TO dashboard_daily_metrics
AS SELECT
  toDate(timestamp) AS date,
  source,
  event_name,
  uniqExact(user_id) AS unique_users,
  uniqExact(session_id) AS total_sessions,
  count() AS event_count
FROM product_events
GROUP BY date, source, event_name;

Quick Start Guide

1. Clone the repository and run docker compose -f docker-compose.analytics.yml up -d to start Kafka, ClickHouse, and the API server.
2. Seed sample events using the provided TypeScript SDK: npm run seed -- --count 50000 --source web.
3. Verify materialized view population: clickhouse-client --query "SELECT count() FROM dashboard_daily_metrics".
4. Access the dashboard API at http://localhost:3000/api/metrics/daily?source=web&start=2024-01-01&end=2024-12-31 and integrate with your frontend using the provided React Query hook.