Start by clarifying requirements and scale, then propose a layered architecture: ingest raw data into a data lake, transform into a star schema with pre-aggregated fact tables at weekly, monthly, and yearly grains, and serve via a fast query layer. Emphasize partitioning, columnar storage, and incremental processing to handle 100M rows/day and 1B customers efficiently.
Pro tip: Discuss how you would handle late-arriving data and backfills without disrupting dashboards, and mention cost optimization by tiering storage and using spot instances for batch jobs.
Ask about query patterns, latency SLAs, data freshness, and geographic/demographic dimensions. Confirm the scale: 100M rows/day * 5 years = ~182.5B rows, and 1B customers.
Propose a star schema with order item fact and customer dimension, and pre-aggregated fact tables at weekly, monthly, and yearly grains. Use columnar storage (e.g., Parquet) partitioned by date and possibly geography.
Use a batch pipeline (e.g., Spark) to process daily data into aggregates, with incremental updates. Consider streaming for real-time needs but batch is sufficient for weekly/monthly/yearly.
Leverage pre-aggregations, partitioning, and indexing. Use a query engine like Presto/Athena or a data warehouse (Redshift) with materialized views. Cache frequent queries.
Discuss trade-offs: pre-aggregation vs. flexibility, cost vs. performance, and how to handle schema evolution and late data. Ensure the system scales horizontally.
AI-generated suggestions, not part of the candidate's original notes. May be inaccurate — verify before relying on them.