Warehouse fact-dimension joins
An MPP warehouse (Redshift/Snowflake/BigQuery-style) backs analytics for a retailer: a ~10B-row `fact_sales`, a 200M-row `dim_customer`, and a small `dim_store`. Analysts complain that joins between sales and customer are slow and that some queries hammer a few compute nodes while others sit idle. A handful of mega-customers (marketplace resellers) account for a huge fraction of rows. Design the physical layout — distribution/partitioning/clustering and join strategy — so the big fact↔dimension joins are fast and load is balanced.
What a strong answer looks like
Clarify scale and constraints first. Propose a clean component breakdown, then go deep on the hard parts (data model, bottlenecks, consistency, failure modes) and name the trade-offs you are making.
Clarify5:00 left
Estimate5:00 planned
Design15:00 planned
Deep dive12:00 planned
Failure8:00 planned
Which questions mattered is sealed until you submit. Telling you now would just be handing over the edge cases.
Run or narrate your approach, then ask the coach.