A top-products dashboard is a problem about a continuous update stream, windowed aggregates, and ranking under freshness pressure, not just SQL over a table.
The case helps show how event ingestion, windowed aggregation, top-k maintenance, and the serving layer split between streaming and query workloads.
For interviews and engineering discussions, it is useful because it trains reasoning about freshness, exactness, and cost at the boundary between analytics and operational data.
Pipeline Thinking
Ingestion, partitioning, deduplication, and stage latency drive system behavior.
Serving Layer
Index and cache-locality decisions directly shape user-facing query latency.
Consistency Window
Explicitly define where eventual consistency is acceptable and where it is not.
Cost vs Freshness
Balance update frequency with compute/storage cost and operational complexity.
Acing SDI
Practice task from chapter 17
Top Products Dashboard: realtime/batch serving layers, pre-aggregations, and explainable metrics.
Top Products Dashboard is a case about building an analytics serving layer. Interviewers focus on how you separate OLTP and OLAP paths, preserve metric consistency, and communicate freshness clearly to business users.
Functional requirements
- Top products ranking by revenue, orders, conversion.
- Filters by time window, region, category, and channel.
- Drill-down to product/campaign details and report export.
- Role-based access (exec/product/ops).
Non-functional requirements
- p95 dashboard response < 300ms.
- Data freshness SLA: 1-5 minutes for operational analytics.
- Metric consistency across dashboards and reports.
- Controlled cost of ad-hoc queries and backfills.
High-Level Architecture
Theory
ETL/ELT architecture
Ingestion, transformation, and serving layers for analytics platforms.
High-Level Architecture
events -> aggregations -> serving -> reconciliationThis topology separates ingest pipeline, query serving path, and reconciliation/backfill control loop.
The architecture separates ingest/aggregation, query-serving path, and reconcile/backfill loop to balance latency, freshness, and KPI consistency.
Write/Read Paths
Write/Read Paths
How metrics are written into serving layer and how dashboard reads aggregates under load.
Write path: events go through ingestion and stream aggregation, then are committed into serving tables with freshness/version metadata.
Events Ingest
Layer 1orders/clicks stream
Product events enter via ingestion API and are validated against contracts.
Stream Bus
Layer 2partitioned log
Events are published to stream bus and distributed to aggregation jobs.
Aggregation Jobs
Layer 3windowed compute
Window/materialized aggregates are computed for key dimension combinations.
Serving Store
Layer 4OLAP views
Top-N and KPI rows are stored in serving tables indexed for dashboard filters.
Freshness Metadata
Layer 5watermark/version
Watermark, formula version, and source metadata are persisted for explainability.
Write path checkpoints
- •Schema contracts are validated at ingress to avoid polluted aggregates.
- •Aggregation jobs should be idempotent to support safe retries.
- •Watermark and formula version must be stored with KPIs for explainability.
Serving data model
- Dimensions:
time_bucket, region, category, channel. - Metrics:
revenue, orders, conversion_rate, margin. - Feature flags for formula migration without downtime.
- Freshness/source metadata for every metric.
Data quality controls
- Data contracts and schema checks at ingestion.
- Metric reconciliation between BI and finance reports.
- Outlier detection and anomaly alerts on core KPIs.
- Backfill pipeline for historical correction.
Common mistakes
- Running BI directly on production OLTP without a dedicated serving layer.
- No freshness/version context in dashboard UI.
- Ignoring cost of ad-hoc analytics and recomputation.
- Mixing analytics SLAs with transactional product SLAs.
Related chapters
- A/B Testing platform - Adjacent analytics case where event quality directly impacts experiment validity.
- ClickHouse overview - Columnar OLAP engine for low-latency dashboard queries and pre-aggregations.
- ETL/ELT architecture - Ingestion and transformation patterns for reliable analytics serving layers.
- Streaming Systems - Streaming fundamentals for freshness, watermarks, and late-event handling.
- Kafka - Event backbone patterns for ingestion, partitioning, and consumer lag control.
- FinOps and analytics cost - Cost governance for ad-hoc workloads, backfill jobs, and long-term retention.