Scalable Data Architecture and Modeling Questions

Design data architectures and data models that support high performance analytics and dashboards at scale. Topics include schema design patterns such as star and snowflake schemas, normalization versus denormalization trade offs, indexing and partitioning strategies, materialized views and aggregation layers, appropriate use of real time versus scheduled batch updates, storage and file format considerations, query pattern driven modeling, handling large volumes and high concurrency, refresh and latency trade offs, monitoring and performance tuning, cost versus performance trade offs, and data governance and lineage to ensure correctness and maintainability. Interview candidates should be able to reason about architecture decisions in the context of query performance, update cadence, concurrency, and operational constraints.

HardSystem Design

26 practiced

Architect a scalable analytics platform to ingest 10 billion events per day, support sub-minute dashboards for some KPIs, ad-hoc analysis, two years retention, and budget constraints. Describe ingestion, storage/lakehouse choices, modeling approach, aggregation/serving layers, and monitoring/operational practices.

Sample Answer

Requirements & constraints:- 10B events/day (~115k events/sec avg, peaks higher), sub-minute dashboards for key KPIs, ad-hoc analysis, 2-year retention, budget conscious.

High-level design:1) Ingestion (streaming + batching)- Edge producers -> Kafka (or managed streaming like Confluent / AWS MSK) with topic partitioning by customer/region to scale.- Lightweight validation/metadata enrichment via serverless consumers (e.g., AWS Lambda/Kinesis Data Analytics or Flink) to drop malformed events and add routing keys.- Use compacted topics for lookup/identity updates.

2) Storage / Lakehouse- Raw immutable events landed to object store (S3/GCS) in partitioned, compressed Parquet/ORC (time + shard).- Use a lakehouse layer (Delta Lake / Iceberg / Hudi) for ACID, efficient deletes/updates, and time travel. Partition by date + shard; sort within file by high-cardinality keys used in filters.- Maintain a separate hot store (columnar OLAP) for recent window (last 7 days) for sub-minute needs: ClickHouse / Snowflake stream table / BigQuery streaming buffer.

3) Modeling approach- Build canonical event model and a conformed dimensional model (star schemas) with fact tables (events, sessions) and dimension tables (user, product, campaign).- Implement ELT: scheduled Spark/Dataproc or serverless SQL (Snowflake/BigQuery) jobs that read lakehouse Parquet and populate transformed tables.- Maintain lineage via metadata (OpenLineage/Marquez) and document metrics.

4) Aggregation & Serving- Real-time KPIs: stream-aggregations with Flink/Beam writing to materialized real-time aggregates in Redis / Druid / ClickHouse for sub-minute dashboards.- Nearline aggregates: hourly/daily rollups materialized into BI-ready tables in the lakehouse for ad-hoc queries.- BI tools connect to the serving layer (Druid/ClickHouse or BI-friendly SQL store). Cache dashboards and use query result caching.

5) Retention & Cost optimization- Raw events: keep hot last 7–30 days in parquet with fast access; move older partitions to infrequent access tiers; compact small files periodically.- Downsample or store sampled versions for long-term analytics; keep full fidelity for 90 days, summary rollups for 2 years.- Choose managed serverless analytic engines when possible to reduce ops cost; use spot instances for heavy batch ETL.

6) Monitoring & Ops- End-to-end observability: ingest metrics (lag, throughput, consumer lag), data quality (schema drift, null rates), and SLA monitors. Use Prometheus + Grafana and alerting.- Data quality tests in CI (Great Expectations) and production checks; automated anomaly detection on key metrics.- Pipeline retries, DLQs for poisoned messages, and runbooks for on-call.- Cost monitoring: tag storage & compute, budget alerts, and monthly rightsizing.

Trade-offs:- Real-time stores add cost (ClickHouse/Druid) but necessary for sub-minute SLAs; can limit to a small set of KPIs to control budget.- Using managed cloud services reduces operational burden but may increase recurring costs—balance with reserved/spot capacity.

This design supports scalable ingestion, BI-friendly models, sub-minute dashboards via a real-time serving layer, efficient long-term retention, and operational practices to ensure reliability and cost control.

MediumTechnical

29 practiced

Explain query-pattern-driven modeling. Given dashboards that frequently aggregate revenue by date, country, and product category, but rarely by user_id, how would you design your schema to optimize for these queries? Include whether to denormalize and which columns to cluster or index.

Sample Answer

Query-pattern-driven modeling means designing your schema to match how reports actually query the data—optimize for the most common aggregations, filters, and join patterns rather than for perfectly normalized OLTP structures.

Situation & recommendation:- People frequently aggregate revenue by date, country, and product category; rarely by user_id. Design a reporting (analytics) schema that makes these queries fast and cheap.

Schema design:- Use a star schema: a wide fact_revenue table that contains revenue, date_key, country_code, product_category_id, product_name (denormalize small, stable dimension attributes used in GROUP BY), and user_id (keep for occasional drilldowns).- Create dimension tables for product and user for lookups when needed, but do not force joins for the common dashboards.

Denormalization:- Denormalize product_category and country into the fact (store category name/code). This avoids joins and improves aggregation speed. Keep user_id as a foreign key but don’t denormalize heavy user attributes.

Physical optimization:- Partition the fact table by date (day/month) to enable partition pruning for time-filtered dashboards.- Cluster/sort by country and product_category (the two most common GROUP BYs) so scans for a single country/category hit contiguous data blocks — this dramatically reduces IO for those queries.- Avoid clustering/indexing on user_id (rarely used); having it as a non-clustered column saves maintenance cost.- In columnar warehouses (BigQuery/Snowflake): use clustering keys on (country, product_category) and rely on partitioning by date. In row/OLAP stores (Redshift): choose sort keys on (date, country, product_category) and distribution key on product_category or date depending on join patterns.- Precompute/maintain aggregate tables or materialized views (daily revenue by date×country×category) for dashboards that need millisecond responses.

Trade-offs and governance:- Denormalization increases storage and requires ETL to keep values consistent — enforce controlled ETL and provenance. Aggregates speed up reads but require refresh strategy (near-real-time vs batch).- Monitor query patterns: if user_id queries increase, adjust clustering or create a separate user-centric aggregate.

This approach minimizes scan cost for the dominant queries (date, country, category), keeps occasional user-level analytics available, and balances performance with maintainability.

MediumTechnical

47 practiced

Compare materialized views, pre-aggregated tables, and on-the-fly aggregation for dashboards that have conflicting requirements around freshness and compute cost. Include maintenance strategies and how to handle metric corrections.

Sample Answer

Direct comparison — when dashboards need both freshness and low compute cost, choose between materialized views (MVs), pre-aggregated tables, and on‑the‑fly (OTF) aggregation by trading latency, cost, and complexity.

Definitions and trade-offs:- Materialized views: database-managed cached query results (can be incremental). Pros: low query latency, DB handles refresh; good when base data changes moderately. Cons: storage and refresh cost; freshness depends on refresh frequency.- Pre-aggregated tables: manually built aggregate tables (daily/hourly granularity). Pros: predictable cost, fine control over partitioning and retention; great for heavy, repeatable queries. Cons: freshness limited to build cadence; operational overhead to maintain pipelines.- On‑the‑fly aggregation: compute aggregates at query time. Pros: always freshest, simple pipeline. Cons: high compute/latency for large datasets; can be cost-prohibitive at scale.

Maintenance strategies:- For MVs: use incremental/partitioned refreshes, schedule near-real-time micro-batches for hot partitions, monitor freshness metrics and staleness SLA, and evict/recompute on schema changes.- For pre-aggregates: implement idempotent ETL jobs, partition by time and business key, use backfill jobs and change-data-capture (CDC) to update recent partitions, and automated data quality checks.- For OTF: add query caching at BI layer, limit full-table scans with predicate pushdown, and enforce query timeouts & row limits to control cost.

Handling metric corrections:- Implement a correction pipeline: record metric lineage and change events (audit table) so you can backfill affected aggregates.- For MVs/pre-aggregates: trigger targeted recomputes for impacted partitions and update MV refresh logs; maintain versioned snapshots so dashboards can show “as-reported” vs “corrected” values.- For OTF: recomputation automatically reflects corrections, but ensure historical queries use corrected source or tagging to show original vs corrected values.

Practical pattern (BI-friendly):- Use hybrid approach: pre-aggregate common heavy metrics (daily/hourly) + MVs for near-real-time hot views + OTF for ad-hoc drilldowns. Route executive dashboards to pre-aggregates/MVs for fast SLAs; allow analysts to run OTF for exploratory work. Automate monitoring, alert on staleness and cost spikes, and document metric definitions and correction procedures so stakeholders trust dashboard numbers.

EasyTechnical

30 practiced

Explain indexing versus partitioning in the context of a cloud data warehouse. What problems does each solve, how do they affect query performance, and what are common pitfalls when choosing partition or index keys for analytics tables?

EasyTechnical

48 practiced

You support daily time-series event data (10M rows/day) and power daily dashboards that mostly query the last 30 days. Describe a partitioning scheme for the fact tables and explain how it optimizes typical dashboard queries, maintenance operations (vacuum/compaction), and historical backfills.

Unlock Full Question Bank

Get access to hundreds of Scalable Data Architecture and Modeling interview questions and detailed answers.

Join thousands of developers preparing for their dream job.