System designCase study 15 of 15
System design · Case study 15 of 15
Design a Reporting and Analytics Platform
Design the reporting layer for a company where executives, finance and operations all need dashboards, and different teams currently report different numbers for the same metric.
On this page
Functional requirements
- Define each business metric once and reuse it everywhere
- Daily executive dashboards and hourly operational dashboards
- Drill down from totals to underlying records
- Scheduled exports for finance
Non-functional requirements
- Dashboards load in under 3 seconds
- Every published number traceable to source tables
- Clear freshness indicators on every dashboard
- Row-level security for regional managers
Scale assumptions
- About 300 dashboard users
- Around 40 certified metrics
- Largest fact table roughly 2 billion rows
Technologies
Data warehouse or lakehouse SQL, Transformation framework (for example dbt), Semantic or metrics layer, BI tool
Curated star-schema marts in the warehouse feed a semantic layer that defines certified metrics; dashboards query the semantic layer, with aggregates or extracts for the heaviest views.
Approach
Reporting problems are usually trust problems: inconsistent definitions, stale data and slow dashboards. Design for one definition per metric and visible freshness.
Architecture
- Marts: star schemas with clear grain for each business process.
- Semantic layer: certified metrics and dimensions defined once, in version control.
- Aggregates: pre-computed rollups for the heaviest dashboards.
- BI tool queries the semantic layer, with row-level security applied.
- Exports: scheduled extracts for finance from the same certified metrics.
Metric definitions
Each metric has an owner, a precise definition (filters, grain, time zone, treatment of refunds), tests and documentation. Changes are reviewed like code.
Freshness and lineage
Show the last successful refresh on every dashboard. Lineage from dashboard to metric to mart to source makes discrepancies explainable.
Performance
Model at the right grain, pre-aggregate heavy views, use partition pruning, and cache where the BI tool supports it. Measure dashboard load times.
Data quality
Reconcile key totals (revenue, orders) with source systems daily; failed reconciliation blocks certified dashboards from refreshing and alerts owners.
Security
Row-level security for regional views, masked personal data, and audited access to finance exports.
Cost
Aggregates and caching reduce repeated scans; schedule refreshes to match how often numbers are actually used.