SYSTEM DESIGN #09 · INTERVIEW GUIDE
Global Reporting System
A global marketing team running campaigns in 50 countries needs one source of truth — but they need it in their local currency, their local timezone, and in under 3 seconds. This Global Reporting System solves the fan-out problem of multi-currency, multi-timezone OLAP at scale: it normalises all spend and revenue to USD using ECB exchange rates at query time, routes fast queries to ClickHouse and historical queries to BigQuery, and uses a two-tier Redis cache (5-minute TTL for today, 24-hour TTL for historical) to absorb dashboard refresh storms. You will learn how to design a reporting backend that serves 10,000 concurrent users across 50 markets, handles currency restatement without data loss, and keeps p95 query latency under 3 seconds on 2 years of raw event data.
ClickHouseBigQueryRedisECB FX RatesGraphQL
💡
The Gist — What Problem Are We Solving?
One place to see how your ads performed across every country in under 3 seconds
Your campaigns run in 50+ countries, 30 currencies, multiple timezones, with different metric definitions in each team. Getting a single consistent accurate report across all of that is surprisingly hard. This system normalises currencies to USD at query time, ensures ROAS means exactly the same thing everywhere, and returns results in under 3 seconds no matter how large the query.
💬Think of it as a universal translator for marketing data — every country, currency, and channel rolled into one consistent fast view.
These are the capabilities the system must deliver — what users and operators can actually do with it.
📊
Report Builder
📊Drag-and-drop dimensions (country, channel, campaign) and metrics (ROAS, CPA, CVR)
💱
Currency Normalisation
💱Normalise all spend and revenue to USD at query time using daily ECB exchange rates
📐
Semantic Layer
📐Canonical metric definitions via dbt — ROAS means the same thing in every report
⚡
Query Caching
⚡Cache results with TTL based on date range; invalidate on pipeline completion
📤
Export
📤PDF and CSV export; scheduled email delivery of reports
⚡
Non-Functional Requirements
These define how well the system must perform — the quality attributes that separate a toy from a production system.
⚡ Query Latency
⚡<3s p95 for standard reports over 90-day range
👥 Concurrency
👥10K concurrent users without degradation
⏱️ Data Freshness
⏱️Max 60 minutes lag from event to queryable report
📅 History
📅5 years of data; historical queries via BigQuery
🌍 Coverage
🌍50+ regions; single unified schema
📊
Key Metrics — The Numbers That Define This System
The headline numbers to know cold — and be ready to explain how each one is achieved.
🏗️
System Architecture Diagram
Full data flow from source to serving. Each layer scales independently.
Ingestion
→
→
ClickHouse
last 90 days) + BigQuery
→
↓
↓
🗺️
End-to-End User Journey
Trace a single request end-to-end — the story interviewers want you to tell fluently.
1
Marketer opens report
— Selects: last 30 days, dimensions = country + channel, metrics = ROAS + conversions + spend
2
Cache check
— Cache key = SHA256(dimensions + filters + date_range + workspace_id). Hit → return in <50ms
3
Cache miss — query routing
— Date range <90 days → ClickHouse. Date range >90 days → BigQuery. Mixed → federated query.
4
Currency normalisation
— JOIN with fx_rates table: spend_usd = spend_local × rate_on_date. ECB rates loaded daily at 06:00 UTC.
🔭
High-Level Design — Component Breakdown
Core components — each with a single, well-defined responsibility. The key architectural insight: each layer scales independently, and failure in one component is isolated from the rest.
1 — Report UI
Handles responsibilities for the Report UI layer. Designed for independent horizontal scaling — additional instances added without architectural changes. Communicates asynchronously with adjacent components to maximise throughput and fault isolation.
2 — Query Router
Handles responsibilities for the Query Router layer. Designed for independent horizontal scaling — additional instances added without architectural changes. Communicates asynchronously with adjacent components to maximise throughput and fault isolation.
3 — Redis Cache
In-memory data structure server handling hot-path lookups in <1ms. Used for: canonical ID cache, rate limiting (token buckets), session state, feature store, and leaderboards. Cluster mode with 6 shards for horizontal scale.
4 — FX Normalise
Handles responsibilities for the FX Normalise layer. Designed for independent horizontal scaling — additional instances added without architectural changes. Communicates asynchronously with adjacent components to maximise throughput and fault isolation.
5 — ClickHouse
Column-oriented OLAP database. MergeTree engine with per-day partitioning and (entity_id, date) sort key. Pre-aggregated Materialised Views reduce query time for common dashboard patterns from seconds to milliseconds.
6 — BigQuery
Handles responsibilities for the BigQuery layer. Designed for independent horizontal scaling — additional instances added without architectural changes. Communicates asynchronously with adjacent components to maximise throughput and fault isolation.
🔬
Low-Level Design — Deep Dives
Deep dives worth explaining in detail in any senior engineering interview. For each: know the data structure, the algorithm, the why, and the trade-off you made.
1 — Query Router
ClickHouse vs BigQuery
Routing rules evaluated in priority order: (1) Date range ≤90d AND standard report template → ClickHouse. (2) Date range >90d OR ad-hoc → BigQuery. (3) Cross-market join >3 regions → BigQuery. (4) ClickHouse p95 >5s in last 5min (overloaded) → BigQuery spill. Rules stored in config service — no deployment for routing changes. All queries logged with: engine, latency, bytes_scanned, cache_hit for cost attribution.
def route_query(query):
if query.date_range_days <= 90 and is_standard(query):
if clickhouse_healthy():
return 'clickhouse'
return 'bigquery'
2 — FX Normalisation
ECB Rates · Query-time
FX normalisation at query time (not storage time). Raw events store original currency + amount. ECB rates loaded daily at 17:00 CET into Redis hash {currency: rate_to_usd}. Query-time formula: normalised = amount × rate[original_currency] / rate[reporting_currency]. Locked historical rates stored in Postgres for financial reconciliation (different from live rates). Currency list: 47 currencies across all 50 markets.
def normalise(amount, from_ccy, to_ccy, date=None):
if date: # historical locked rate
rates = postgres.get_fx_rates(date)
else:
rates = redis.hgetall(‘fx_rates’)
return amount * float(rates[from_ccy]) / float(rates[to_ccy])
3 — Redis Cache Layer
2-tier TTL · Invalidation
Two-tier cache: (1) Today’s data: TTL=5 minutes — simple expiry. (2) Historical data: TTL=24 hours — invalidated by Kafka consumer watching ClickHouse write stream. On partition update for date D: publish invalidation message → API gateway DEL all keys with date=D → async cache warm-up for top-100 most-queried reports. Cache key: SHA-256(org_id + query_hash). Hit rate: ~82% for repeated dashboard refreshes.
def get_report(query):
key = cache_key(query)
cached = redis.get(key)
if cached:
return json.loads(cached)
result = execute_query(query)
ttl = 300 if query.includes_today else 86400
redis.setex(key, ttl, json.dumps(result))
return result
4 — Report Builder API
GraphQL · Streaming
GraphQL API exposes flexible report builder: dimensions (country, channel, campaign, creative, device), metrics (impressions, clicks, conversions, spend, revenue, ROAS), date range, filters. Response streaming for large reports (>10K rows) via HTTP chunked transfer. Report templates pre-defined for common patterns (campaign performance, channel mix, geographic breakdown) — hit Redis cache directly without query planning.
type Query {
performance(
dimensions: [Dimension!]!
metrics: [Metric!]!
dateRange: DateRangeInput!
filters: [FilterInput]
): PerformanceReport!
}
⚖️
Trade-offs & Decision Log
Every senior interview comes down to these decisions. Know the exact trade-off, the reasoning, and the specific numbers that justify each choice.
⚖️ ClickHouse vs BigQuery for Reporting
✓
ClickHouse (self-managed) ✅ For <90d queries
- Sub-3s p95 on 90-day windows — 10× faster than BQ for hot data
- Column-oriented MergeTree with per-cohort pre-aggregates
- Cost-effective at 5TB/day ingest vs BQ on-demand pricing
- Operational burden of managing shards, replicas, ZooKeeper
→
BigQuery for historical queries
- Serverless — no operational overhead
- Best for ad-hoc queries on 2+ years of history
- Slower for repeated dashboard queries (slot contention)
- Higher per-query cost for frequent reports
💡Decision: ClickHouse for 0-90 day dashboard queries; BigQuery for >90d ad-hoc analysis; results materialised to ClickHouse nightly
⚖️ Single Global DB vs Regional Deployments
✓
Regional ClickHouse Clusters ✅ Chosen
- <50ms query latency for all regions vs 200ms cross-region
- Data residency compliance (EU data stays in EU)
- Independent scaling per region
- Higher operational complexity — 3 clusters to manage
→
Single Global Cluster
- Single pane of glass — simpler operations
- Cross-region queries are 200-300ms
- Data residency violations for GDPR markets
- Single point of failure for global reporting
💡Decision: Regional clusters (US, EU, APAC) with a global query router; cross-region joins at the application layer
🎯Interview Questions — Answered
The exact questions interviewers ask — with production-grade answers
Q1
How does the query router decide between ClickHouse and BigQuery?
Routing rules (evaluated in order): (1) Date range ≤90 days → ClickHouse. (2) Date range >90 days → BigQuery. (3) Query involves cross-market join across 3+ regions → BigQuery (better distributed join support). (4) Ad-hoc query (not on pre-defined report template) → BigQuery. (5) If ClickHouse p95 latency >5s in last 5 minutes (overloaded) → spill to BigQuery. Rules stored in a config service — no code deployment needed to adjust routing. All queries logged with engine used, latency, bytes scanned, and result cache hit for cost attribution.
Q2
How does the FX normalisation work without distorting historical data?
FX normalisation uses a ‘normalise at query time, not at storage time’ approach. Raw events store original currency and amount. ECB reference rates are loaded daily and stored in a Redis hash {currency_code: rate_to_USD}. At query time, the reporting API applies: normalised_value = original_value × fx_rate[original_currency] / fx_rate[reporting_currency]. This means historical data automatically reflects current FX rates in reports — important for year-over-year comparison. For financial reconciliation (which needs locked rates), a separate ‘close of day’ rate snapshot is stored per date in Postgres.
Q3
How is the Redis cache invalidated when new data arrives?
Two-tier invalidation strategy: (1) Today’s data (TTL=5min): simply expires and is refetched. Stale data never older than 5 minutes. (2) Historical data (TTL=24h): invalidated by a Kafka consumer that watches the ClickHouse write stream. When a ClickHouse partition for date D is updated (late events, corrections), a cache invalidation message is published. The API gateway subscribes and calls Redis DEL for all cache keys with date=D. Cache warm-up: on invalidation, the most-frequently-queried reports are pre-fetched asynchronously and loaded into cache before user requests arrive.
System Design Series · Every Tuesday & Thursday
Level up your system design interviews
Each post covers Gist, Functional & Non-Functional Requirements, Key Metrics, System Diagram, User Journey, HLD, LLD, and Trade-offs & FAQs.
Subscribe to never miss a post →
Previous Articles
Categories: System Design
Tags: bigquery, clickhouse, global scale, interview prep, olap, reporting, system design
Leave a Reply