The interview setup
Design a data warehouse for mid-size e-commerce (2M orders/day). Use a star schema with facts (orders, clicks, inventory) and dimensions (customers, products, dates). Handle SCD Type 2 for customer and product.
This interview tests dimensional modeling judgment: grain first, then keys, then history. Tool choice is secondary.
Background concepts from first principles
Star schema
A large fact table of additive measurements sits in the center, surrounded by dimension tables that describe who, what, where, when. Queries join facts to dims for slices such as revenue by category by day.
Grain
Grain is the meaning of one fact row in one sentence. "One row per order line per order" is a grain. If you mix order headers and lines in one table, dashboards double-count.
Surrogate keys
Warehouse keys (surrogate integers/hashes) stay stable even when source natural keys change or recycle. Dimensions own them. Facts store them.
SCD Type 2
When a tracked attribute changes, insert a new dimension row with validity interval. Facts keep the surrogate key that was active at event time so history remains correct.
Clicks versus orders
Clicks arrive at much higher volume and different grain. Dumping raw clicks beside order facts without rollup is a common modeling mistake.
The expanded problem
- Define grain for each fact
- Design core dimensions with surrogate keys
- Implement SCD2 for customer and product
- Support BI queries such as revenue by category by day
- Relate clicks to orders carefully
dim_date dim_customer (SCD2) \ dim_product (SCD2) +--> fact_order_item (grain: one line per order line) dim_store /
What good looks like
- Say grain before columns
- Separate facts for different grains
- SCD2 example with valid_from/valid_to
- Caution on raw click grain
- Partition facts by date
Clarifying questions
- Order fact grain: header versus line item?
- Do clicks need user-level detail forever?
- Which attributes need SCD2 versus Type 1?
- Timezone for business dates?
Out of scope
- Picking Redshift vs BigQuery as religion
- Building the entire semantic layer UI
- Real-time inventory microservices deep dive
Clarifying questions strategy
Ask questions that change grain, retention, SLA, or cost. If told to decide, state assumptions explicitly and continue.
What good looks like verbally
Narrate why stages exist. Name atomic publish. Name how reruns stay safe. Mention what is out of scope.
Scope control
Batch interviews reward candidates who protect the critical morning path and park nice-to-haves. Say what can wait until after the SLA table lands.
Clarifying questions strategy
Ask questions that change grain, retention, SLA, or cost. If told to decide, state assumptions explicitly and continue.
What good looks like verbally
Narrate why stages exist. Name atomic publish. Name how reruns stay safe. Mention what is out of scope.
Scope control
Batch interviews reward candidates who protect the critical morning path and park nice-to-haves. Say what can wait until after the SLA table lands.
Clarifying questions strategy
Ask questions that change grain, retention, SLA, or cost. If told to decide, state assumptions explicitly and continue.
What good looks like verbally
Narrate why stages exist. Name atomic publish. Name how reruns stay safe. Mention what is out of scope.
Scope control
Batch interviews reward candidates who protect the critical morning path and park nice-to-haves. Say what can wait until after the SLA table lands.
Clarifying questions strategy
Ask questions that change grain, retention, SLA, or cost. If told to decide, state assumptions explicitly and continue.
What good looks like verbally
Narrate why stages exist. Name atomic publish. Name how reruns stay safe. Mention what is out of scope.
Scope control
Batch interviews reward candidates who protect the critical morning path and park nice-to-haves. Say what can wait until after the SLA table lands.
Clarifying questions strategy
Ask questions that change grain, retention, SLA, or cost. If told to decide, state assumptions explicitly and continue.