Data model for a grocery delivery marketplace: fact tables, dimensions and grain

Design the analytics data model for a grocery delivery marketplace. Customers place orders for items sold by retail stores; shoppers pick the items in the store and deliver the orders. Propose the fact and dimension tables an analytics team would build, and for every table state its grain (what exactly one row represents) before listing its columns. Then show how the model answers common questions about the business without double counting. The report says this round resembled delivery and marketplace data-modeling interviews and that stating each table's grain first mattered most. It does not list the business questions the model had to answer, so agreeing on them is part of the exercise. ```hint One row means one thing Before naming any columns, say in one sentence what a single row of each table represents. Then check whether every measure you plan to store belongs to that grain. ``` ```hint Orders are not trips Ask whether one shopper trip can carry several orders, and whether one order can be split across trips. Each answer changes where delivery facts and shopper pay belong. ``` ```hint Requested versus received In grocery delivery, the item a customer receives is not always the item they asked for. Decide where that difference is recorded. ``` ### Clarifying Questions - Which questions must the model answer first: order volume and sales by store and day, delivery speed and lateness, item availability and replacements, shopper pay and utilization, or customer retention? - Can one shopper trip carry several orders, and can one order be split across shoppers or trips? - How do the source systems record item replacements, refunds, and partial or full cancellations? - Must reports reproduce store, zone and product attributes as they were on the order date, or only as they are today? - Is daily batch freshness enough, or is there also a need for near-real-time operational views? ### What a Strong Answer Covers - The business processes identified, with the grain declared for every fact table before its columns - Separate facts at the order, item-line, trip and status-event grains, each holding only measures that are additive at its grain - Conformed dimensions with surrogate keys, and a deliberate choice about attributes that change over time - Explicit handling of replacements, refunds, cancellations, and batched or split fulfilment - Example queries that avoid fan-out double counting, and a way to allocate trip-level costs to orders - A load strategy, data-quality tests on the grain, and handling of late-arriving data ### Follow-up Questions - Shopper pay is recorded per trip, but a trip carries several orders. How do you report shopper cost per order without double counting? - A store moves to a different delivery zone mid-year. How should last year's sales by zone be reported? - An analyst joins order items to orders and sums the order-level delivery fee. What goes wrong, and how does your model prevent it? - How would you extend the model to support a near-real-time dashboard of late orders?