Design a data warehouse and ETL pipeline that ingests data from multiple operational databases, transforms it, and serves it for business analytics and reporting.
Scale requirements: - 20 source databases (Postgres, MySQL, MongoDB) with 500+ tables total - 50 TB of historical data in the warehouse - 200 GB of new/changed data per day - 500 analysts running queries concurrently - Dashboard queries must complete in under 10 seconds
Areas to cover: 1. Data extraction from heterogeneous sources (CDC vs batch) 2. Transformation layer (cleaning, denormalization, aggregation) 3. Data modeling (star schema, slowly changing dimensions) 4. Storage engine and query performance 5. Orchestration, monitoring, and data quality
Constraints: - Data freshness: analytics data must be no more than 1 hour stale - Handle schema evolution in source databases gracefully - Data quality validation at every stage - Support both ad-hoc SQL queries and scheduled reports - Maintain audit trail of all transformations
How to approach it
- Hint 1
Start with extraction: how do you get data out of 20 different databases efficiently? Compare batch extraction (full/incremental dumps) with CDC (Change Data Capture via Debezium). What are the tradeoffs for freshness, source database load, and complexity?
- Hint 2
Consider the transformation layer: how do you model the data warehouse? Think about star/snowflake schema, slowly changing dimensions (SCD Type 1 vs 2), and the ELT pattern where raw data lands first and transforms happen in the warehouse.
- Hint 3
Think about the query layer: 500 concurrent analysts with 10-second query SLAs. How does a columnar storage engine (Snowflake, BigQuery, Redshift) achieve this? What about pre-computed aggregates, materialized views, and query result caching?
Ready to answer it out loud?
Record your answer in 1:30 and Preptile scores it 1–10 with specifics — what landed, what you skipped, and what to say next time.
Practising needs an invite code. Join the waitlist and we’ll send you one.