AWS Real-Time Dashboards: Druid, ClickHouse, or Lambda?

Advertisers and publishers demand live dashboards showing metrics updated within minutes: impressions, clicks, conversions, spend, CPM. Real-time reporting requires an OLAP (Online Analytical Processing) engine that ingests high-volume event streams, aggregates them on the fly, and serves slice-and-dice queries to dashboards. The challenge is balancing freshness (sub-minute latency), cardinality (thousands of dimensions per event), and query flexibility (arbitrary drill-down) while keeping costs manageable.

Three approaches dominate on AWS: Druid (columnar OLAP purpose-built for high-cardinality, roll-up aggregation, deployed on EC2 ASG or EKS), ClickHouse (raw-event columnar store with Kafka native support and powerful SQL, deployed on EC2 ASG), and Lambda architecture (Kinesis micro-batch into pre-aggregated cubes with Pinot or Aurora/Redshift). Druid excels at metrics slicing (impressions by date, country, campaign, audience, device); ClickHouse at raw-event queries; Lambda at hybrid (real-time summary + archived detail). This case examines the three, their operational trade-offs, and guidance on schema design, Kinesis ingestion, and GDPR compliance.

Key Requirements

Functional Requirements

• Metric freshness: Dashboard metrics updated within 1–5 minutes of event occurrence.
• Arbitrary drill-down: slice metrics by any combination of dimensions (date, country, campaign, audience, creative, device, OS, browser).
• Sub-second query latency: p99 query response <2 seconds even at 100M events/day scale.
• Cardinality support: handle 1000+ distinct values per dimension (e.g., 50,000 unique campaigns + 200 countries + 100 audiences = high cardinality).
• Retention policy: keep 7-day detailed data, 90-day daily rollups, 2-year monthly rollups.
• Segmentation: support complex cohort filtering (e.g., "impressions from users in USA who saw video ads from campaign X").
• GDPR compliance: delete all events for a user upon deletion request without impacting other users' data.

Non-Functional Requirements

• Throughput: ingest 500K+ events/sec sustained during peak hours.
• Tail latency resilience: infrastructure failures must degrade gracefully; query timeout >5 seconds acceptable, but p95 <1 second target.
• Multi-tenancy: isolate metrics per advertiser/publisher; no data leakage across tenants.
• Cost efficiency: keep per-event cost <$0.001 (100M events/month ≈ $100k budget).
• Schema evolution: add new dimensions without downtime or full re-index.
• Audit: who accessed which reports; track metric changes.

Simplified Architecture

Architecture Proposals

Option 1: Druid (Time-Series OLAP with Roll-Up, EC2 ASG)

Druid is a columnar OLAP datastore designed for metrics aggregation. Events flow into Kinesis Data Streams (or Apache Kafka on Amazon MSK); Druid real-time ingestion tasks (deployed on EC2 ASG or EKS) consume from Kinesis and aggregate events into immutable segments (e.g., 1-minute segments). Historical segments are stored in S3 deep storage. Query engine (Druid broker) slices aggregated metrics by dimensions without touching raw events. Druid's roll-up feature pre-aggregates (COUNT, SUM, AVG) at ingestion time, drastically reducing storage.

Pros:

• Roll-up efficiency: pre-aggregate COUNT, SUM at ingestion; 100M raw events collapse to 10M rolled-up rows (90% compression).
• Fast queries: querying aggregates (already summed) much faster than raw-event scan.
• High cardinality: designed for 1000+ dimensions; query planner optimizes to avoid full table scan.
• Mature ecosystem: widely used in ad-tech (Airbnb, Twitter); native integrations with Kafka, S3.
• AWS Kinesis native: Kinesis Data Streams integration via custom firehose consumer; automatic scaling.

Cons:

• Complex semantics: roll-up configuration upfront; cannot change aggregations retroactively without re-ingestion.
• Approximate for percentiles: COUNT aggregation exact; MEDIAN/p95 not native (requires approximation or HyperLogLog).
• Cardinality limits: if dimension has 10M unique values (e.g., user IDs), roll-up cardinality explodes; storage not practical.
• Schema change friction: adding new dimension requires new segment version; re-ingestion for backfill.
• Operational complexity: managing segment indexing, handoff from real-time to historical tier, balancing across EC2 ASG nodes.

Failure Modes & Mitigations:

• Roll-up key missing: query asks for metric not included in roll-up config. Mitigate: pre-define all likely metrics; publish roll-up schema to team; CI/CD validation.
• Cardinality explosion: dimension has 50M unique values; roll-up becomes huge. Mitigate: exclude high-cardinality dimensions from roll-up; store raw or use approximate count distinct (HyperLogLog).
• Kinesis lag: Druid real-time task falls behind; freshness degrades. Mitigate: multiple ingestion tasks (EC2 ASG scale-out), Kinesis partitioning per campaign to parallelize.
• Schema evolution: new dimension added; backfill required. Mitigate: design schema with 20 custom dimensions reserved for future use; store as JSON.
• S3 slowness: historical segment queries to S3 timeout. Mitigate: cache frequently-accessed segments in local SSD via EC2 instance store; S3 Select for pushdown filtering.

When to Choose:

• Metrics pre-aggregated (SUM impressions by date/country/campaign) is primary use case.
• Cardinality moderate (1,000–100,000 combinations of dimensions).
• Team familiar with Druid operations (Apache ecosystem maturity, EKS experience).
• Can tolerate roll-up schema upfront.

Option 2: ClickHouse (Raw-Event Columnar Store, EC2 ASG)

ClickHouse stores raw events as-is in a columnar format, making it ideal for flexible analytics. Kafka table engine (or Kinesis-via-Lambda) allows direct ingestion; MergeTree table engine automatic sorting and compression. Queries execute against raw data; no pre-aggregation required. Cost per event higher than Druid, but schema changes trivial (add column, resume ingestion). Deployed on EC2 ASG with i4i NVMe instances for fast local storage; replicated across AZs via MergeTree replication.

Pros:

• Raw flexibility: store raw events; query any column combination without pre-aggregation.
• Schema evolution: add column to table, resume ingestion; no re-indexing.
• Native Kinesis/Kafka: Kafka table engine pulls directly; minimal latency (~5-30 seconds for MergeTree background merge).
• Compression: columnar + LZ4/ZSTD often 10x better than row-based storage.
• Powerful SQL: full SQL support; JOIN, subqueries, window functions, UDFs.
• Materialized views: pre-aggregated views updated incrementally; hybrid speed + flexibility.

Cons:

• Higher storage: raw events larger than rolled-up metrics; 100M events ≈ 500 GB vs Druid 50 GB.
• Query cost: scan more data; queries slower than Druid aggregates (500 ms–2 sec vs 100 ms).
• Late binding: aggregations computed at query time; scales with query volume and data size.
• Eventual consistency: Kafka/Kinesis table buffers; visible data lags ~5 seconds behind Kinesis offset.
• Operational overhead: auto-merging background tasks; tuning table settings (merge parameters, memory) needed. ZooKeeper coordination complexity.

Failure Modes & Mitigations:

• Kinesis lag: ClickHouse Kafka/Kinesis table falls behind offset. Mitigate: multiple consumers on EC2 ASG, dedicated Kinesis shards per table, increase consumer batch size.
• Query timeout: scanning 100M rows for single-day query takes >2 seconds. Mitigate: date-based partitioning (scan only relevant date); pre-materialized views for common aggregates; Prewhere optimization.
• Schema evolution mismatch: new event format breaks old queries. Mitigate: add new columns as nullable; handle null in application query.
• Storage growth: 500 GB raw events fill disk. Mitigate: TTL policies (delete 90+ day data); archive to cold S3 via Tiered storage.

When to Choose:

• Schema evolving rapidly; cannot pre-define aggregations.
• Need to answer ad-hoc questions (pivot arbitrary dimensions).
• Team comfortable managing large datasets; has ClickHouse expertise.
• Can tolerate higher cost per event for flexibility.

Option 3: Lambda + Cube Pre-Aggregation (Kinesis Micro-Batch to Aurora/Redshift)

Kinesis Data Streams routes events to two paths: real-time micro-batch Lambda (every 1–2 minutes) pre-aggregates into a "metrics cube" (Aurora PostgreSQL or Redshift materialized view), and raw events archived to S3 (Parquet via Kinesis Firehose). Query layer chooses: fresh metric from cube (p50 <100ms latency), or detailed drill-down from S3 (higher latency, but fully flexible). Cube updated every micro-batch cycle; raw archive updated hourly or daily via Glue jobs.

Pros:

• Hybrid best-of-both: real-time dashboard from cube (fast), detailed analysis from S3 (flexible).
• Cost-effective: cube stores aggregates (cheap Aurora storage); raw S3 Parquet cold tier (very cheap, $0.005/GB/mo).
• Flexible schema: raw S3 data stores all events; add custom dimensions without concern.
• Decouples latency: dashboard refresh independent from drill-down data availability.
• Easy GDPR compliance: delete user from cube (re-aggregate next cycle) + delete from S3 (Parquet rewrite via Glue).
• Serverless (Lambda): no EC2/Kubernetes management; auto-scales with Kinesis shard count.

Cons:

• Operational complexity: managing two data paths (cube + S3), two query engines (fast + flexible).
• Eventual consistency: cube updates every 1–2 minutes; during cycle, stale data.
• Cardinality constraints: cube still has dimension limits; if too many dimensions, cube explodes.
• Data duplication: raw events in Kinesis, cube, S3; increases storage 2–3x.
• Query routing logic: application must decide: use cube or S3? adds latency to decision; client-side logic or API gateway routing.

Failure Modes & Mitigations:

• Cube aggregate lag: micro-batch Lambda fails; metrics stale for 2+ minutes. Mitigate: retry logic via Lambda DLQ; fall back to previous cycle's data with "as-of" indicator.
• S3 query too slow: drill-down query on 100M+ parquet rows takes 10+ seconds. Mitigate: partition S3 by date/campaign; Athena projection-based partitioning; pre-cache common drill-downs in ElastiCache.
• Cube + S3 divergence: metrics in cube don't match detail in S3 (e.g., due to replay/retransmit). Mitigate: daily reconciliation Lambda; alert on mismatches >0.1%; automated recompute on divergence.

When to Choose:

• Need fast dashboard + flexible drill-down simultaneously.
• Budget allows dual storage (cube + S3).
• Can manage two query engines (Aurora SQL + Athena/Presto).
• GDPR compliance simple (separate user records from metrics).
• Prefer serverless (Lambda) over managing EC2/EKS clusters.

Comparison Table

Recommendation

For advertiser dashboards (performance metrics): Use Lambda + Cube architecture (Option 3). Cube (Aurora or Redshift) serves hot metrics (impressions, clicks, spend by date/campaign/country) updated every minute; S3 cold tier stores raw events for detailed user-level drill-down via Athena. Advertisers get <100ms dashboard refresh and can still debug campaign performance ad-hoc. Serverless Lambda scales automatically; no EC2 management.

For publisher ad-ops dashboards: Use Druid (Option 1) if reporting is stable (standard metrics, fixed dimensions). Roll-up ensures sub-100ms query latency and low storage cost. Deploy on EKS for simplified operations; Kinesis auto-scales shards. Invest in upfront schema design; changes are painful but rare.

For product analytics / data science: Use ClickHouse (Option 2) if iterative analysis common. Raw-event storage (500 GB for 100M daily events) acceptable cost for schema flexibility. Team can afford slower queries (p50 500 ms) for one-off exploration. Materialized views can accelerate common aggregations.

Migration path (scaling):

• Start: Lambda + Aurora cube (simple, proven, serverless).
• Scale: Add Druid for performance-critical dashboards; keep Lambda for flexibility.
• Mature: Multi-engine (Druid for metrics, ClickHouse for exploration, Lambda for hybrid).

Capacity & Performance Notes

Druid Cluster (EC2 ASG):

• Ingestion: 500K events/sec into Kinesis → 10 real-time tasks (50K/sec each, m5.2xlarge instances).
• Storage: 100M events/day with 5:1 roll-up compression = 2 GB/day indexed segments.
• Retention: 7 days hot (14 GB) + 90 days rollup (4.5 GB) + archive to S3 cold.
• Query latency baseline: p50 50–100 ms (aggregates), p99 200–500 ms (high cardinality).
• Infrastructure: 3 data nodes (EC2 m5.4xlarge, 16 vCPU, 64 GB RAM, 500 GB EBS gp3 each), 2 query brokers (ECS Fargate, 4 vCPU), 1 coordinator (ECS Fargate).
• Cost: ~$20k/month (EC2 ASG + Kinesis + S3).

ClickHouse Cluster (EC2 ASG):

• Ingestion: 500K events/sec from Kinesis (via Lambda consumer or Kafka connector).
• Storage: 100M events/day ≈ 5 GB/day (0.05 bytes/event after compression; i4i NVMe SSDs).
• Retention: 7 days hot (35 GB) + cold S3 archive via TTL.
• Query latency: p50 300–500 ms (table scan), p99 1–2 sec (multi-column filter).
• Infrastructure: 3 shards × 2 replicas (EC2 i4i.2xlarge, 8 vCPU, 64 GB RAM, 1 TB NVMe), ZooKeeper cluster (3 nodes, m5.large).
• Cost: ~$25k/month (EC2 ASG + Kinesis + ZooKeeper).

Lambda + Cube (Hybrid, Serverless):

• Cube (Aurora): 100K rows aggregated every 1 min = 144M rows/day. Aurora burstable db.t4g.2xlarge can handle (16 vCPU, 32 GB RAM).
• S3 raw events: 100M events/day = 5 GB Parquet (LZ4 compression) = 150 GB/month, ~$2.25/month storage.
• Lambda: 1–2 Lambda functions per shard; concurrent execution auto-scales; avg 5 sec per 60-sec batch.
• Query latency: cube p50 50 ms (Aurora), S3 p50 2–5 sec (Athena startup + scan).
• Infrastructure: 1 Aurora burstable instance (db.t4g.2xlarge), Kinesis Data Streams (1–10 shards), Lambda (on-demand), Athena (on-demand), S3 (auto-scaled).
• Cost: ~$15k/month (cheapest; Aurora ~$3k + Kinesis ~$2k + Lambda ~$1k + Athena ~$2k + S3 <$0.5k).

GDPR Compliance Example:

• User requests deletion.
• Druid: rebuild affected day's segments without user's events (1–4 hour job via batch task); segments re-published to S3.
• ClickHouse: run mutation to delete user; compaction in background; ~30 minute latency.
• Lambda + Cube: delete from Aurora (instant) + delete/rewrite Parquet in S3 (Glue job, acceptable next day).