Designing Analytics and Reporting Systems
1. Designing Analytics Pipeline Architecture
Client/Server SDK ─▶ Collector ─▶ Stream Bus ─▶ Stream + Batch Processing
│
▼
Lake (raw) → DW (modeled) → BI / API
| Stage | Detail |
|---|---|
| Ingest | Segment, RudderStack, Snowplow, Kafka |
| Transform | dbt, Spark, Flink |
| Serve | Looker, Tableau, custom dashboards |
2. Designing Event Tracking System
| Practice | Detail |
|---|---|
| Tracking plan | Versioned schema for events |
| Event naming | object_action (e.g., order_placed) |
| Identity | anonymous_id + user_id (alias on login) |
| Validation | Schema reject at ingest |
| Privacy | Honor consent / DNT |
3. Designing Data Aggregation Pipeline
| Tier | Detail |
|---|---|
| Raw events | Append-only |
| Sessionization | Group by session |
| Daily rollups | Pre-aggregated metrics |
| User-level marts | Per-entity summaries |
4. Designing Real-Time Analytics
| Engine | Strength |
|---|---|
| ClickHouse | Fast columnar OLAP |
| Druid | Time-series, sub-second |
| Pinot | LinkedIn-style real-time |
| Materialize / RisingWave | Streaming SQL views |
| Rockset | Search + analytics index |
5. Designing Batch Analytics
| Stack | Detail |
|---|---|
| Lake | S3/GCS + Iceberg/Delta/Hudi |
| Compute | Spark, Trino, BigQuery, Snowflake |
| Modeling | dbt |
| Scheduling | Airflow / Dagster |
6. Designing Metrics Collection
| Type | Detail |
|---|---|
| Business metrics | DAU, conversion, revenue |
| Product metrics | Feature usage |
| Tech metrics | Latency, errors |
| Metric layer | Cube, MetricFlow, dbt Semantic |
7. Designing Data Retention for Analytics
| Tier | Detail |
|---|---|
| Hot (DW) | Recent 90d |
| Warm (lake) | 1–2 years |
| Cold (archive) | Compliance retention |
| Aggregates | Keep longer; raw can expire |
8. Designing Custom Reporting
| Approach | Detail |
|---|---|
| SQL access (read replica) | For analysts |
| Templated reports | Parameterized |
| Embedded analytics | Looker / Sisense iframes |
| Self-serve UI | Drag-drop builder |
| Async export | Large reports → email/link |
9. Designing Analytics API
| Aspect | Detail |
|---|---|
| Pre-aggregated | Serve from cubes / rollups |
| GraphQL / REST | Flexible query |
| Caching | By time bucket |
| Per-tenant isolation | Row-level filter mandatory |
10. Designing Data Export Functionality
| Format | Detail |
|---|---|
| CSV / TSV | Common |
| Parquet | Large + analytical |
| JSON / NDJSON | API-friendly |
| Async + signed URL | Large jobs |
| Audit logged | Who exported what |
11. Designing Business Intelligence Integration
| Tool | Detail |
|---|---|
| Looker / Tableau / PowerBI / Mode / Hex | Dashboards |
| Reverse ETL | Hightouch, Census push to ops tools |
| Semantic layer | One source of truth for metrics |
| Row-level perms | Per BI viewer |
12. Designing A/B Testing Analytics
| Element | Detail |
|---|---|
| Assignment | Hash(user_id, experiment) → variant |
| Exposure event | Logged per variant |
| Metric | Pre-defined success metric |
| Statistical method | Frequentist (t-test) or Bayesian |
| Tools | Statsig, GrowthBook, LaunchDarkly, Optimizely |
| Guardrails | Stop on regression in core metrics |