Real-Time Analytics & Dashboards

Executive Summary

Key value propositions:

• Zero lag between data ingestion and dashboard visibility
• Instant schema changes (no materialized view rebuild delays)

Problem Being Solved

The Real-Time Analytics Paradox

Problems:

• Data staleness: Dashboard shows data from hours/days ago
• Infrastructure complexity: 3 systems to manage (app DB, ETL, warehouse)
• Operational burden: Data engineers debugging ETL failures
• Consistency issues: Data warehouse may diverge from source-of-truth
• Schema migration nightmare: Changes require ETL reconfiguration

Enterprise Pain Points

Operational Burden:

• ETL failures happen daily (data consistency issues)
• Data refresh delays cause customer complaints
• Schema changes require coordinated updates across all systems
• Query optimization requires dedicated analytics engineer
• Data governance becomes fragmented across systems

Analytical Limitations:

• Real-time analytics impossible (hourly refresh at best)
• Data consistency issues (warehouse diverges from source)
• Cannot easily join real-time + historical data
• Complex transformations lose data provenance

Implementation Examples

Example 2: Real-Time Dashboard Frontend (React)

import React, { useState, useEffect } from 'react';

export function RealtimeDashboard() {
  const [metrics, setMetrics] = useState(null);
  const [connected, setConnected] = useState(false);

  useEffect(() => {
    // Server-Sent Events for real-time updates
    const eventSource = new EventSource('/api/metrics/stream');

    eventSource.onmessage = (event) => {
      const data = JSON.parse(event.data);
      setMetrics(data);
    };

    eventSource.onerror = () => {
      setConnected(false);
      eventSource.close();
    };

    eventSource.onopen = () => {
      setConnected(true);
    };

    return () => eventSource.close();
  }, []);

  if (!connected || !metrics) {
    return <div>Connecting to dashboard...</div>;
  }

  return (
    <div className="dashboard">
      <div className="metrics-grid">
        <MetricCard
          label="Total Revenue"
          value={`$${metrics.total_revenue.toLocaleString()}`}
          trend="+12%"
        />
        <MetricCard
          label="Average Order"
          value={`$${metrics.avg_order_value.toFixed(2)}`}
          trend="+3%"
        />
        <MetricCard
          label="Total Orders"
          value={metrics.total_orders.toLocaleString()}
          trend="+8%"
        />
        <MetricCard
          label="New Customers"
          value={metrics.new_customers.toLocaleString()}
          trend="+15%"
        />
      </div>

      <div className="last-updated">
        Last updated: {new Date(metrics.timestamp * 1000).toLocaleTimeString()}
      </div>
    </div>
  );
}

function MetricCard({ label, value, trend }) {
  return (
    <div className="metric-card">
      <div className="metric-label">{label}</div>
      <div className="metric-value">{value}</div>
      <div className="metric-trend">{trend}</div>
    </div>
  );
}

Example 3: Advanced Analytics Queries (SQL)

-- 1. Top products by revenue (real-time)
SELECT
    product_id,
    product_name,
    SUM(amount) as revenue,
    COUNT(*) as order_count,
    AVG(amount) as avg_order_value
FROM orders
WHERE timestamp > datetime('now', '-24 hours')
GROUP BY product_id, product_name
ORDER BY revenue DESC
LIMIT 10;

-- 2. Customer cohort analysis with trending
SELECT
    DATE(registration_date) as cohort_date,
    COUNT(DISTINCT customer_id) as new_customers,
    SUM(CASE WHEN DATE(order_timestamp) = DATE('now')
        THEN 1 ELSE 0 END) as active_today,
    SUM(amount) as total_revenue,
    AVG(amount) as avg_order_value
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
GROUP BY cohort_date
ORDER BY cohort_date DESC;

-- 3. Real-time conversion funnel (with vector search for related products)
SELECT
    'visit' as stage,
    COUNT(DISTINCT session_id) as count,
    COUNT(DISTINCT session_id)::float /
        MAX(COUNT(DISTINCT session_id)) OVER () as conversion_rate
FROM pageviews
WHERE timestamp > datetime('now', '-1 hour')

UNION ALL

SELECT
    'add_to_cart' as stage,
    COUNT(DISTINCT session_id) as count,
    COUNT(DISTINCT session_id)::float /
        (SELECT COUNT(DISTINCT session_id) FROM pageviews
         WHERE timestamp > datetime('now', '-1 hour')) as conversion_rate
FROM cart_events
WHERE timestamp > datetime('now', '-1 hour')

UNION ALL

SELECT
    'purchase' as stage,
    COUNT(DISTINCT order_id) as count,
    COUNT(DISTINCT order_id)::float /
        (SELECT COUNT(DISTINCT session_id) FROM pageviews
         WHERE timestamp > datetime('now', '-1 hour')) as conversion_rate
FROM orders
WHERE timestamp > datetime('now', '-1 hour');

-- 4. Correlation analysis (products bought together)
WITH product_pairs AS (
    SELECT
        o1.product_id as product_1,
        o2.product_id as product_2,
        COUNT(*) as co_purchase_count
    FROM orders o1
    JOIN orders o2 ON o1.order_id = o2.order_id
        AND o1.product_id < o2.product_id
    WHERE o1.timestamp > datetime('now', '-30 days')
    GROUP BY product_1, product_2
)
SELECT
    p1.product_name as product_1_name,
    p2.product_name as product_2_name,
    co_purchase_count,
    co_purchase_count::float /
        (SELECT SUM(co_purchase_count) FROM product_pairs) as correlation
FROM product_pairs
JOIN products p1 ON p1.product_id = product_1
JOIN products p2 ON p2.product_id = product_2
ORDER BY co_purchase_count DESC
LIMIT 20;

-- 5. Anomaly detection (unusual patterns)
SELECT
    DATE(timestamp) as date,
    SUM(amount) as daily_revenue,
    AVG(SUM(amount)) OVER (ORDER BY DATE(timestamp)
        ROWS BETWEEN 7 PRECEDING AND CURRENT ROW) as avg_7_day,
    STDDEV(SUM(amount)) OVER (ORDER BY DATE(timestamp)
        ROWS BETWEEN 7 PRECEDING AND CURRENT ROW) as stddev_7_day,
    CASE
        WHEN ABS(SUM(amount) - AVG(SUM(amount)) OVER
                (ORDER BY DATE(timestamp) ROWS BETWEEN 7 PRECEDING AND CURRENT ROW)) >
             2 * STDDEV(SUM(amount)) OVER
                (ORDER BY DATE(timestamp) ROWS BETWEEN 7 PRECEDING AND CURRENT ROW)
        THEN 'ANOMALY'
        ELSE 'NORMAL'
    END as status
FROM orders
WHERE timestamp > datetime('now', '-90 days')
GROUP BY DATE(timestamp)
ORDER BY date DESC;

Example 5: Docker Compose - Analytics Stack

# Dockerfile - Analytics application
FROM rust:latest as builder
WORKDIR /app
COPY Cargo.* ./
COPY src ./src
RUN cargo build --release

FROM debian:bookworm-slim
RUN apt-get update && apt-get install -y curl
COPY --from=builder /app/target/release/analytics-app /usr/local/bin/

RUN mkdir -p /data && chmod 700 /data
RUN useradd -m -u 1000 app
USER app:app

EXPOSE 8080 3000
HEALTHCHECK --interval=30s --timeout=3s \
    CMD curl -f http://localhost:8080/health || exit 1

ENTRYPOINT ["analytics-app"]

# docker-compose.yml - Complete analytics stack
version: '3.8'

services:
  # HeliosDB-Lite analytics backend
  analytics-api:
    build: .
    environment:
      RUST_LOG: info
      DATABASE_PATH: /data/analytics.db
      BIND_ADDRESS: 0.0.0.0:8080
    volumes:
      - analytics-data:/data
    ports:
      - "8080:8080"
    healthcheck:
      test: ["CMD", "curl", "-f", "http://localhost:8080/health"]
      interval: 10s
      timeout: 3s
      retries: 3

  # Frontend dashboard
  dashboard:
    image: node:18-alpine
    working_dir: /app
    volumes:
      - ./dashboard:/app
      - /app/node_modules
    ports:
      - "3000:3000"
    environment:
      REACT_APP_API_URL: http://localhost:8080
    command: npm start
    depends_on:
      - analytics-api

  # Event ingestion service
  event-collector:
    image: node:18-alpine
    working_dir: /app
    volumes:
      - ./collector:/app
      - /app/node_modules
    environment:
      ANALYTICS_API_URL: http://analytics-api:8080
      KAFKA_BROKERS: kafka:9092
    depends_on:
      - analytics-api
      - kafka
    command: npm start

  # Kafka for high-volume event streaming (optional)
  kafka:
    image: confluentinc/cp-kafka:latest
    environment:
      KAFKA_BROKER_ID: 1
      KAFKA_ZOOKEEPER_CONNECT: zookeeper:2181
    depends_on:
      - zookeeper

  zookeeper:
    image: confluentinc/cp-zookeeper:latest
    environment:
      ZOOKEEPER_CLIENT_PORT: 2181

volumes:
  analytics-data:

Conclusion

• Real-time data freshness
• Affordable economics
• Operational simplicity