Balancing Real-Time Ad Stats and Billing Accuracy - Eventual vs Strong Consistency

Ad tech systems must balance two conflicting goals:

(1) real-time statistics (dashboards showing "you got 1M impressions today" with <5min freshness), and

(2) accurate billing (financial records showing "you owe $50k today" with zero tolerance for error).

Eventual consistency (ElastiCache, Kinesis, fast) suits statistics; strong consistency (Aurora, slow) suits billing. Yet one campaign can affect both: a single impression must increment the stats counter AND increment the billing ledger.

The fundamental challenge is that achieving both simultaneously is expensive. A single database write that is both durable (for billing) and fast (<100ms) is hard at scale (billions of events per day). Distributed transactions (two-phase commit) add latency and availability costs. The alternative is split storage: eventual for stats, strong for billing, with reconciliation jobs. But reconciliation is complex and introduces data quality risks.

This case explores the spectrum of consistency models, from pure eventual (all data in ElastiCache, lose it on outage) to pure strong (all data in Aurora, slow), and hybrid approaches (data warehouse with late-arriving facts). The tradeoff affects not just latency, but also code complexity (conflict resolution logic), operational burden (reconciliation jobs), and risk (overbilling or underbilling).

Key Requirements

Functional Requirements

• Real-time reporting: impressions, clicks, spend visible in dashboard within 5 minutes
• Billing accuracy: revenue and advertiser spend reconcile with audit ledger to within $0.01 per day
• Campaign pause capability: can pause campaign in <1 minute (must be durable, not just cached)
• Refunds / adjustments: support billing corrections (e.g., invalid traffic refund) after campaign ends
• Cross-shard consistency: budget enforcement is correct even if advertiser spans multiple shards
• Compliance: audit logs show exact sequence of impressions, clicks, conversions for contract disputes

Non-Functional Requirements

• Read latency: dashboard load <500ms p99 (stats are eventual)
• Write latency: impression ingestion <100ms p99 (must not block publisher SDK)
• Billing SLA: advertiser charges reconciled within 24 hours; discrepancies identified within 48 hours
• Data loss tolerance: <0.01% of revenue-impacting events are lost or double-counted
• Replication lag: eventual consistency must lag <5 minutes (not hours)
• Storage cost: <$0.01 per event in durable store (billing database)

Simplified Architecture

Architecture Proposals

Option 1: Two-Tier Dual-Write (Eventual Stats + Strong Billing)

Maintain two separate systems: (1) fast eventual-consistency stores for stats (ElastiCache, Amazon Kinesis), (2) slow strong-consistency store for billing (Aurora PostgreSQL). Every impression is written to both.

The flow is:

1. Publisher sends impression: {advertiser_id, campaign_id, impression_time, price}
2. Bid server writes to two places in parallel: (a) ElastiCache: INCR advertiser_id:impressions, INCR advertiser_id:spend (eventual), (b) Aurora: INSERT INTO impression_events (advertiser_id, campaign_id, price, status, created_at). Aurora write is slower (~50ms), ElastiCache write is fast (~1ms).
3. Dashboard reads from ElastiCache: GET advertiser_id:impressions (fast, <5min fresh)
4. Billing job (hourly via AWS Lambda): SELECT SUM(price) FROM impression_events WHERE advertiser_id = X AND created_at > ..., INSERT into billing_ledger. Aurora is source of truth for billing.
5. Reconciliation job (daily via AWS Glue): compare ElastiCache stats vs. Aurora billing ledger. If discrepancy >1%, alert.

The key insight: writes go to both systems (dual-write), but reads are routed by use case (fast stats from ElastiCache, accurate billing from Aurora).

Pros

• Solves the core problem: fast reads for stats, accurate billing
• Simple to reason about: each system has one purpose
• Existing tooling: ElastiCache and Aurora are battle-tested at scale
• Flexible: can tune each system independently (ElastiCache TTL, Aurora retention)
• No distributed transactions: writes are independent; failures are local

Cons

• Dual-write complexity: if ElastiCache write succeeds but Aurora fails, or vice versa, systems diverge. Need retry logic and error handling.
• Operational burden: two systems to operate, patch, monitor, back up. Doubles on-call rotation.
• Reconciliation required: daily/hourly jobs compare stats vs. billing; if discrepancy found, manual investigation needed. Adds latency to detecting bugs.
• Data staleness: stats in ElastiCache are stale (5 min); advertiser sees outdated balance on dashboard
• Budget enforcement gaps: if budget check is done against eventual ElastiCache stats, advertiser can overspend slightly before ElastiCache is updated
• Consistency window: between writing ElastiCache and Aurora, systems are inconsistent. If system crashes in between, data is lost or duplicated.

Failure Modes & Mitigations

• Partial write: ElastiCache succeeds, Aurora fails (network partition). Risk: stat is incremented, but billing is not. Impression is lost. Mitigation: write to Aurora first (source of truth), then ElastiCache (cache). If ElastiCache write fails, that's OK (cache miss later). If Aurora fails, retry with exponential backoff; eventually succeeds.
• ElastiCache loss during outage: ElastiCache is in-memory; power failure causes data loss. Risk: stats are lost, but if Aurora is intact, can reconcile from Aurora. Mitigation: use ElastiCache persistence (AOF or RDB snapshots); or accept stats loss and regenerate from Aurora on recovery.
• Reconciliation detects divergence too late: daily reconciliation finds discrepancy after advertiser has already spent based on false stats. Risk: overbilling. Mitigation: hourly reconciliation (more cost); or use strong consistency for budget enforcement (even if dashboard is eventual).

When to Choose Choose this if you already have Aurora for other purposes and can add ElastiCache for caching. Good for platforms where stats staleness of 5 minutes is acceptable (most display ad platforms). Works well if your on-call team is experienced with both systems. Avoid if billing accuracy must be <1% (harder to achieve with eventual consistency) or if you need sub-1minute freshness for stats.

Option 2: Single-System Event-Sourced Approach (MSK + Materialized Views)

Rather than dual-writing, use event sourcing: all events (impressions, clicks, conversions) are immutable facts in Amazon MSK. MSK is the source of truth. Downstream, different materialized views consume from MSK: (1) a fast view for stats (Amazon Kinesis Data Streams + Lambda, or Kafka Streams to DynamoDB), (2) a slow durable view for billing (Aurora, or Redshift).

The flow is:

1. Impression arrives: emit event to MSK: {advertiser_id, campaign_id, price, timestamp}
2. MSK durably logs the event with replication across 3 Availability Zones
3. Multiple consumers subscribe: (a) stats consumer writes to DynamoDB every 10 seconds (real-time reporting), (b) billing consumer writes to Aurora (durable ledger), (c) budget consumer tracks running spend and enforces limits
4. Each consumer processes from its own offset; if consumer crashes, resumes from last committed offset
5. Both stats and billing read from their respective stores; both are derived from MSK, so consistent by construction

The key benefit: single event stream is the source of truth. All derived views are consistent by construction. No reconciliation needed.

Pros

• Single source of truth: MSK log is the canonical record. No divergence between stats and billing.
• Auditability: full event log is preserved; can replay to audit any calculation
• Flexibility: multiple consumers can independently derive views (stats, billing, budget enforcement, compliance, etc.) without interfering
• Fault tolerance: if Aurora consumer crashes, it resumes from saved offset; no data loss
• Scalability: MSK handles high throughput; consumers can scale independently

Cons

• Operational complexity: requires operating MSK + DynamoDB/ClickHouse + Aurora + stream processors. More moving parts than Option 1.
• Consumer lag: MSK consumers are asynchronous; billing consumer may lag 1–10 minutes behind real-time. Not suitable for real-time budget enforcement.
• At-least-once semantics: MSK provides at-least-once (not exactly-once). Consumers must be idempotent to handle duplicate events. Adds application complexity.
• Cost: MSK retention (7+ days) is expensive for billions of events per day. ClickHouse/Druid is also expensive.
• Retraining: if consumer logic changes (bug fix, new feature), old events are already processed. Must reprocess entire history to correct. Expensive.

Failure Modes & Mitigations

• Consumer lag grows: billing consumer is slower than event arrival rate. Lag grows from 1 min → 1 hour. Risk: billing is stale; advertiser's balance is not up-to-date. Mitigation: over-provision consumer (add threads/instances in ECS); monitor lag via CloudWatch; alert if >5 min.
• Duplicate events in MSK: producer retried; event appears twice. Risk: impression is counted twice. Mitigation: ensure consumers are idempotent (dedup by event_id); use MSK exactly-once semantics if available (see C2-01).
• MSK message loss: broker failure before replication. Risk: event is lost; billing is undercounted. Mitigation: use 3-zone replication; enable log compaction; ensure consumers have committed offset before moving on.

When to Choose Choose this if you are already running MSK for other pipelines (logs, events, analytics) and can extend it to handle billing. Good if you need strong auditability and compliance (financial services, insurance). Ideal if you want to eliminate reconciliation jobs and have single source of truth. Avoid if you lack expertise operating MSK or if consumer lag is unacceptable.

Option 3: Split Storage by Use Case (ClickHouse for Analytics, Aurora for Billing, Stream Sync)

Separate storage systems optimized for their use case: (1) ClickHouse or Amazon Redshift for real-time analytics (fast aggregation, sub-second OLAP queries), (2) Aurora for transactional billing (ACID, audit trail), (3) a lightweight stream processor (Kafka Streams or Lambda) to keep them in sync.

The flow is:

1. Impression event published to MSK
2. Stream processor (Kafka Streams or Kinesis Data Firehose) consumes events
3. Parallel writes: (a) batch writes to ClickHouse every 10 seconds (real-time analytics), (b) transactional writes to Aurora (billing ledger)
4. Dashboard queries ClickHouse: "SELECT SUM(price) FROM impressions WHERE advertiser_id = X AND date = today" returns in <1 second (fast OLAP)
5. Billing jobs query Aurora: "SELECT SUM(price) FROM billing_ledger WHERE advertiser_id = X" returns exact ledger
6. Sync mechanism: MSK offset is committed after both writes succeed; if one fails, offset is not committed, event is replayed

This approach optimizes each store for its workload.

Pros

• Optimized per use case: ClickHouse excels at real-time aggregation (columnar, fast OLAP); Aurora excels at transactions (ACID, row-based)
• Fast analytics: ClickHouse can aggregate billions of rows in <1 second; much faster than Aurora for analytical queries
• Strong billing: Aurora provides ACID transactions; billing is durable and consistent
• Eventual consistency with bounds: MSK ensures both systems see same events; lag is bounded by stream processor throughput
• Scalability: ClickHouse and Aurora can be scaled independently

Cons

• Complexity: three systems to operate (MSK, ClickHouse, Aurora). Requires expertise in each.
• Cost: ClickHouse or Redshift is expensive (cluster required; not cheap like ElastiCache). Doubles analytics infrastructure cost.
• Sync logic: stream processor must handle failures (e.g., Aurora write failed, but ClickHouse succeeded). Need retry logic and eventual consistency handling.
• ClickHouse learning curve: SQL syntax differs from Aurora; aggregations are different; requires retraining for analytics team.
• Data quality: if ClickHouse and Aurora diverge (e.g., ClickHouse has data Aurora doesn't), reconciliation is complex.

Failure Modes & Mitigations

• ClickHouse write fails, Aurora succeeds: event is in Aurora billing ledger, but not in ClickHouse dashboard. Risk: dashboard shows lower stats than actual. Mitigation: stream processor retries ClickHouse write with exponential backoff; eventual consistency ensures reconciliation within hours via CloudWatch alarms.
• Aurora write fails, ClickHouse succeeds: event is in ClickHouse, but not in billing ledger. Risk: dashboard shows inflated stats; billing is undercounted. Mitigation: treat as fatal; stream processor fails, offsets are not committed; event is replayed. Retry until both writes succeed.
• ClickHouse cluster unavailable: stats pipeline stops. MSK events are not consumed (offset not committed). Risk: MSK lag grows. Mitigation: circuit breaker; if ClickHouse is down >5 min, skip ClickHouse writes (still write to Aurora), commit offset. ClickHouse can catch up later via batch Glue job.

When to Choose Choose this if you already have ClickHouse or Redshift for analytics and want to add billing on top. Ideal for large platforms (1B+ impressions/day) where ClickHouse's analytical performance is critical. Good if your team has ClickHouse and Aurora expertise. Avoid if operational complexity is a concern or if you lack resources for three systems.

Comparison Table

Recommendation

For most ad tech platforms, start with Option 1 (Dual-Write) and upgrade to Option 2 (Event-Sourced) as scale grows.

Use Option 1 if:

• You have <1B impressions/day
• Your team is comfortable with Aurora and ElastiCache
• Reconciliation overhead is acceptable (1–2 person-days per week)
• Stats staleness of 5 minutes is acceptable

Upgrade to Option 2 (Event-Sourced) when:

• You reach 1B+ impressions/day and reconciliation becomes a bottleneck
• You have multiple consumers (billing, stats, compliance, budget enforcement) that must be consistent
• Your team can operate MSK and has stream processing experience
• Auditability is critical (financial services, regulated industries)

Use Option 3 (Split Storage) if:

• You are at 10B+ impressions/day and analytics performance is critical
• Your team already runs ClickHouse or Redshift and can maintain it alongside Aurora
• Sub-second dashboard response time is a business requirement
• Cost is not a constraint

Implementation guide for Option 1 (recommended):

1. Write path: (a) write to Aurora impression_events table first (transactional, durable via RDS), (b) on success, INCR ElastiCache counters (advertiser_id:impressions, advertiser_id:spend). If ElastiCache write fails, log error but continue (cache miss later).
2. Read path (Dashboard): read from ElastiCache with 5-minute staleness accepted. If ElastiCache is unavailable, query Aurora directly (slower, but correct).
3. Reconciliation job (hourly via Lambda/Glue): SELECT COUNT(*), SUM(price) FROM impression_events WHERE created_at > ... GROUP BY advertiser_id. Compare with ElastiCache values. If discrepancy >1%, alert via SNS/CloudWatch.
4. Budget enforcement: check against ElastiCache spend (eventual), but also enforce hard limit in Aurora (strong). Advertiser can overspend slightly on ElastiCache, but Aurora blocks further impressions once hard limit is hit.
5. Retention: keep Aurora impression_events for 30 days (for dispute resolution); delete older rows nightly via Lambda. ElastiCache TTL=24h (expires automatically).

Capacity & Performance Notes

Sizing for 10B impressions/day (Option 1):

• Aurora write rate: 10B impressions / 86400s ≈ 115k QPS. With batching (1000 impressions per batch), batch rate = 115 QPS. Using RDS benchmarks, an r6g.4xlarge Aurora instance can handle ~500 batches/sec. Capacity is high. Use r6g.4xlarge with 1TB SSD; replication to 2 standby replicas.
• ElastiCache write rate: 115k INCR commands per second. ElastiCache Cluster Mode with 16 shards handles ~100k QPS per shard easily. Use cache.r6g.xlarge with 32 GB RAM per shard; retention ~70 GB (24h TTL, 1KB per advertiser_id key).
• Reconciliation job: scan 10B rows in Aurora, aggregate by advertiser_id, compare with ElastiCache. Using AWS Glue parallel query (10 workers), takes ~10 minutes. Run hourly.
• Latency: Aurora write 10–20ms, ElastiCache write <1ms. Total: ~20ms p99.

Option 2 sizing (Event-Sourced):

• MSK throughput: 115k events/s, each 500 bytes = 57.5 MB/s. MSK cluster with 3 brokers, each with 2TB EBS, 7-day retention = 3.5 TB per broker, acceptable.
• Consumer lag: billing consumer processes 115k events/s. On m5.2xlarge instance (8 core) running Kafka Streams, handles ~50k events/s comfortably. Need 3 consumer instances for redundancy/availability in ECS.
• End-to-end latency: event → MSK ~50ms, billing consumer ~100ms (batching), Aurora write ~20ms = 170ms total.

Option 3 sizing (Split Storage):

• ClickHouse cluster: 10B impressions/day, columnar storage ~1 byte per metric (price). 10B × 1 byte = 10 GB per day = 100 GB per 10 days. ClickHouse cluster with 4 nodes (c5.4xlarge, 256 GB SSD each), handles this comfortably. Dashboard query latency <1 second.
• Aurora: same as Option 1.
• Stream processor: Kafka Streams app on 3 ECS Fargate tasks (2vCPU, 4GB RAM each), handles 115k events/s easily.