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
orders/clicks stream
Product events enter via ingestion API and are validated against contracts.
Stream Bus
partitioned log
Events are published to stream bus and distributed to aggregation jobs.
Aggregation Jobs
windowed compute
Window/materialized aggregates are computed for key dimension combinations.
Serving Store
OLAP views
Top-N and KPI rows are stored in serving tables indexed for dashboard filters.
Freshness Metadata
watermark/version
Watermark, formula version, and source metadata are persisted for explainability.
Events Ingest
orders/clicks stream
Product events enter via ingestion API and are validated against contracts.
Stream Bus
partitioned log
Events are published to stream bus and distributed to aggregation jobs.
Aggregation Jobs
windowed compute
Window/materialized aggregates are computed for key dimension combinations.
Serving Store
OLAP views
Top-N and KPI rows are stored in serving tables indexed for dashboard filters.
Freshness Metadata
watermark/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.