Designing an Analytics Pipeline
Day 1Event Ingestion at Scale
Events are immutable facts. Each event has a unique event_id, an event time, a type and context. Never update one: record the change as a new event (event.processed) pointing to the original, or replay and the audit trail break.
Evolve schemas safely. A registry enforcing BACKWARD compatibility rejects a new required field like session_id, since old events lack it. Add fields as optional with a default. Name events domain.entity.action, in the past tense.
Collect, validate, then Kafka. Lightweight collectors validate size, required fields, schema, timestamps, business rules and security, then write to Kafka asynchronously. In a batch, valid events go to Kafka and invalid ones to the dead letter topic.
Timestamps and enrichment. An event time over 5 minutes in the future is an error; over 30 days old is only a warning, since offline phones send those. Enrichment (geo, user, product, session) is best-effort: a failed lookup never blocks.
Key by user, write idempotently. Kafka orders only within a partition, so user events are keyed by user_id. acks=all makes a write durable across in-sync replicas; enable_idempotence stops a retried send from writing a duplicate.
Shed telemetry, never revenue. Above 90% queue usage the collector sheds page.viewed-type telemetry but never orders, payments or sign-ups. Per-client limits (1,000 a second) and an adaptive limiter protect downstream systems.
| Logs | Events | |
|---|---|---|
| Format | Unstructured text | Structured, schema-aware |
| Parsing | Fragile regex | Self-describing |
| Aggregation | Hard | Easy |
| Schema change | Breaks parsers | Versioned evolution |
- Events without an
event_id: no deduplication, no tracing through the pipeline. - Writing to the database before replying: a slow database makes a slow API.
- No dead letter queue, so a failed event is lost forever.
Day 2Streaming vs Batch Processing
Two ways to process. Batch processes large chunks: hours of latency, exact, cheaper, simpler. Streaming processes each event as it arrives: seconds of latency, approximate because late events may be missed, always on and more complex.
Lambda. Every event feeds a Spark batch layer (accurate, overwrites its views) and a Flink speed layer that fills the gap since the last batch; the serving layer adds them. Its big problem: two codebases that drift apart.
Kappa. One streaming codebase over a long-retention log. To reprocess, start a new version from the earliest offset, let it catch up, switch and delete the old output. 1B events at 100K/s replay in about 3 hours.
Watermarks. Watermark = max event time seen minus max out-of-orderness. With a 5-minute bound and latest event at 10:02 it's 9:57, so [9:00-10:00) fires only after an event at 10:05 or later. Wall-clock time plays no part.
Late data and state. An event after its window fired can be dropped, side-output, refire the window, or fit within allowed lateness. Keep large state in RocksDB with a TTL, and checkpoint so a crash doesn't start over.
Write once, run both. Apache Beam, Flink (batch is just bounded streaming) and Spark Structured Streaming run the same aggregation as a stream or a batch, so real-time and batch results can't drift apart.
| Lambda | Kappa | |
|---|---|---|
| Codebases | Two: Spark and Flink | One |
| Reprocessing | Batch recompute | Replay the log |
| Weak spot | Results diverge | Replay time grows with history |
| Choose when | PB history, batch ML, reconciliation | Real-time first, replay fast enough |
- Writing batch output with
mode("append"), so a retried job duplicates rows; overwrite the date partition. - Keeping state forever with no TTL, until it runs out of memory.
- Grouping by a skewed key without salting, so one task runs for hours.
Day 3Data Modeling for Analytics
OLTP vs OLAP. OLTP runs the business: many small transactions, normalized (3NF), row storage. OLAP understands it: few large queries over many rows and few columns, denormalized, column storage. "Revenue by country" becomes one scan and a GROUP BY.
Star schema. A fact table of measures (revenue, quantity) at a stated grain, here one row per line item, with foreign keys to denormalized dimensions: product, customer, date, store. order_id is a degenerate dimension kept in the fact.
SCD Type 2. On a change, close the old row (valid_to, is_current = FALSE) and insert another. Facts store the surrogate key valid at sale time, so a plain join is point-in-time. Only tracked columns create versions.
Load dimensions, then facts. Dimensions first, so today's new versions have surrogate keys. Lookups use the version valid on the batch date, not is_current, so backfills stay right. A missing product maps to unknown key -1, keeping totals whole.
Columnar storage. SUM(revenue) over a 4-column table reads 1 column: 75% less I/O, better compression, vectorized. Parquet row groups carry statistics for predicate pushdown; Delta Lake and Iceberg add ACID transactions and time travel.
Partition and pre-aggregate. Partition by filter columns like date, Z-order by a second filter such as product_id, never partition by a high-cardinality key. A daily rollup by category scans 365 × ~100 = 36,500 rows instead of 1B.
| Type 1 | Type 2 | Type 3 | |
|---|---|---|---|
| On change | Overwrite | Add a row | Add a column |
| History | None | Full | One level |
| Cost | Simplest | More storage, complex queries | Simple current vs previous |
- An order-level grain: you can never drill down to products.
- Business keys in the fact table instead of surrogate keys, so dimension history is lost.
- Partitioning by
customer_id: 10 million partitions full of small files.
Day 4Late-Arriving Data and Correctness
Event time, not processing time. A purchase made at 14:30 on the subway reaches the server at 15:45, 75 minutes late. Windowed by processing time it lands in 15:00-16:00; by event time it lands in 14:00-15:00, where it happened.
Two separate knobs. Watermark = max event time minus the out-of-orderness bound: with a 5-minute bound and max 10:03 it's 9:58, and an older event can't lower it. Allowed lateness is separate: how long a fired window still accepts updates.
Per-key watermarks idle out. With per-key watermarks the global one is the minimum, so a phone offline for two days pins it in the past and windows stop closing. Keys not seen within the idle timeout are left out.
Tune lateness from data. Measure the lateness distribution (example: p50 2 seconds, p99 5 minutes, max 72 hours) and set it per source: minutes for web, hours for mobile. Unlimited lateness grows state until it runs out of memory.
Patch or reprocess. Patch additive metrics (SUM, COUNT) when late data is small. Reprocess from raw events, overwriting the range, for non-additive ones (AVG, MEDIAN, DISTINCT), big late volumes or changed logic.
Version, label, finalize. Store every computation as a version with a reason and delta, so latest, final and as-of queries work. Stream results are provisional; a daily batch job rereads raw events for final numbers. Label dashboards Live or Final.
| Patch | Reprocess | |
|---|---|---|
| Late volume | Small | Significant |
| Aggregations | SUM, COUNT | AVG, MEDIAN, DISTINCT |
| Cost | Quick | Expensive, exact |
| Writes | Apply a delta | Overwrite the range |
- Windowing by processing time, so offline events land in the wrong hour.
- Overwriting results in place, leaving no answer to "why did yesterday change?"
- Alerting on live numbers; gate the normal threshold on
is_final, a stricter one for live.
Day 5Query Layer and Optimization
Match the engine to the access pattern. Cloud warehouses (Snowflake, BigQuery) answer in seconds, managed, for ad-hoc SQL. Real-time OLAP (ClickHouse, Druid) gives milliseconds and high concurrency for user-facing dashboards. Trino queries the lake in place; DuckDB runs in-process.
Why the dashboard is slow. "Revenue by category, region and day for 90 days" scans ~2.7B of ~11B rows (30M a day), shuffles 50GB and takes 47 seconds: no partitioning, no pre-aggregation, no caching, joins that shuffle.
Partition, then cluster. Partitioning decides which files to read, and only helps when the query filters on the partition column. Clustering (sorting, Z-ordering) decides how rows sit inside files, so category = 'A' can skip files without it.
Pre-aggregate the predictable. Pre-aggregate patterns that are predictable and frequent (over 10 a day): the executive dashboard drops from 45 seconds to 50ms. Route to the coarsest rollup that keeps the needed granularity; leave rare ad-hoc slices on demand.
Cache with the right TTL. Layers: CDN, Redis query results (60-80% hits for popular queries), database cache, block cache. TTL follows freshness: about a minute for today, hours for history. Include the tenant in the key: same SQL, different rows.
Isolate tenants' data and compute. Row-level security is cheap, but one missing filter leaks data. Per-tenant quotas (10 concurrent queries, 100GB scanned a day, 300-second timeout) stop one noisy tenant from slowing everyone else.
| Row-level security | Schema per tenant | Database per tenant | |
|---|---|---|---|
| Isolation | One missing filter leaks | Strong | Complete |
| Main cost | Noisy neighbors | Schema proliferation | Highest cost, most ops |
| Cross-tenant queries | Easy | Harder | None |
- Caching everything for 24 hours, so live panels show stale numbers.
- One
agg_everythingrollup grouped by every dimension: nearly as big as the fact table. - No query timeouts or byte limits, so runaway queries block others and costs explode.
BossReal-Time Analytics for Video Streaming Platform
Size the firehose. 10M peak viewers sending one event every 10 seconds is 1M events a second; a day adds up to ~37B, planned as 50B. Heartbeats are 70% (35B). At ~300 bytes that's 15TB a day raw, 3TB compressed.
Two serving stores. Regional collectors write to Kafka (events.playback keyed by user_id, 7-day retention). Flink feeds ClickHouse for trending and live counts; Spark feeds BigQuery for history and A/B tests; raw events land in S3.
Acknowledge after Kafka. Collectors validate and batch, but answer the client only after Kafka acks: acking earlier would lose buffered events if a collector crashed. 20 collectors per region handle ~10K events a second each.
Trending from unique viewers. Raw counts favour long content and bots, so score unique viewers (HyperLogLog) in a 1-hour window sliding every minute, normalized by a category baseline and boosted by velocity. Scores refresh every 30-60 seconds.
Three freshness tiers. Events under 5 minutes late update real-time numbers; under 24 hours they queue for the T+1 preliminary job at 6 AM; later ones wait for the T+7 final job. Phones persist events in SQLite.
Creator analytics. A star schema (fact_daily_views with five dimensions) partitioned by viewing date and clustered by content. 90% of queries hit the agg_daily_content rollup; HLL sketches merge uniques across days instead of summing them.
| Real-time | Preliminary | Final | |
|---|---|---|---|
| When | Every minute | T+1, 6 AM | T+7 |
| Late events | Under 5 minutes | Most late data | All, up to 7 days |
| Status | Provisional | Preliminary | Official, immutable |
- Acknowledging clients before Kafka acks, so a collector crash loses events.
- Counting raw events for trending, which favours long titles and bots.
- Summing daily unique viewers over 30 days, so a 5-day viewer counts 5 times.