The JSON Files That Became a Data Mart
A medium Data Modeling mock interview question on DataDriven, with an AI interviewer who asks follow-up questions and a hire or no-hire decision at the end.
- Domain
- Data Modeling
- Difficulty
- medium
- Seniority
- L5
Interview Prompt
A retail client delivers three daily JSON feeds from its e-commerce platform, every record keyed by a string source ID like 'ORD-20260401-0042', and wants them turned into a relational data mart whose joins run on the warehouse's own integer keys with the source IDs kept alongside. Orders arrive as `{order_id, customer:{id, name, email}, line_items:[{product_id, qty, price}], status}`; products as `{product_id, name, categories:[{id, name, parent_id}]}`, where `parent_id` chains categories up to three levels deep; and shipments as `{shipment_id, order_id, address:{street, city, country}, status_history:[{status, timestamp}]}`, where one order can span several shipments. Design the dimensional schema that captures every order, line item, product category, shipment, and shipment status transition.
Summary
Three semi-structured inputs. One queryable warehouse.
How This Interview Works
- Read the vague prompt (just like a real interview)
- Ask clarifying questions to the AI interviewer
- Draw your data model on the schema canvas: tables, keys and relationships
- Answer the interviewer's follow-up questions, then get a hire or no-hire decision