# Every Line Remembered > Customers move, products relaunch, and some stores have no address. Reshape the tables so nothing gets forgotten. Canonical URL: Domain: Data Modeling · Difficulty: medium · Seniority: L5 ## Problem You are handed the transactional database behind a retail chain: orders, customers, products, stores (some of them digital storefronts with no physical location), and employees, where a single order spans several line items, each for a different product. Redesign it as a dimensional warehouse that keeps every line item as its own record, so product-mix, per-line sales, and customer-behavior reporting stays a simple roll-up. Watch two quirks the source carries: product identifiers get reused when a retired item relaunches, and a sale must keep showing the customer's address as it stood on the order date even after the customer later moves. ## Worked solution and explanation ### What this problem really is Underneath the retail costume this is a grain-and-history problem. Anyone can list five dimension tables and a fact; what separates candidates is declaring the fact at the order line rather than the order, and deciding which dimensions have to carry history. Model the fact at order grain and every product-mix question needs a fan-out back to line detail. Model dim_customer as Type 1 and the first time a customer moves you silently rewrite every past geo report. The reused product identifiers are the tell that forces surrogate keys, and the digital storefronts are the tell that the store dimension cannot assume a physical address. > **Trick to Solving** > > Before drawing any tables, a strong candidate asks: "what is the smallest additive unit the business reports on, and which dimensions are shared across facts?" The answer is the grain. Everything after that is conformed dimensions and surrogate key discipline. > > 1. Declare `fact_sales` grain = one row per order line > 2. Pick conformed dimensions (customer, product, store, employee, date) > 3. Decide SCD strategy per dimension > 4. Introduce surrogate keys on every dimension --- ### Break down the requirements #### Step 1: Declare the fact grain as the order line One row per order line lets analysts compute units, gross sales, and product mix by simple aggregation. Order-grain would force fan-out joins back to line detail for any product question. #### Step 2: Introduce surrogate keys on every dimension Natural keys stay as `*_nk` columns but are not the PK. This lets a product_id renumber in the source (a retired item relaunched under the same id) without cascading to fact rows. #### Step 3: Apply SCD Type 2 on dim_customer address Customer city and state move. Tracking the as-of address via `effective_from`, `effective_to`, and `is_current` lets a past sale attribute to the address it shipped to. #### Step 4: Conform dim_date across any time-based lookup One calendar dimension is shared by every fact and by date-typed columns on dimensions via role-playing keys. #### Step 5: Keep dim_store flexible enough for digital Digital storefronts have no physical address. A nullable address is one defensible choice; a `store_type` flag lets reporting partition physical vs digital cleanly, all within one dim_store. --- ### The solution Below is one defensible model. The grain anchors the rest of the design, and Type 2 on `dim_customer` is the one SCD lever worth defending out loud. > **Why this design holds up** > > The line-grain fact makes every product and category question additive. Surrogate keys decouple the warehouse from OLTP churn. Type 2 on `dim_customer` preserves historical attribution when a customer moves, which matters for any geo report. > **What strong candidates do** > > They name the grain before drawing a single box. They call out which dimensions are Type 1 vs Type 2 and justify each. They acknowledge digital storefronts as a dimension nuance, not a schema exception. > **Red flags to avoid** > > Using OLTP primary keys as warehouse PKs couples reporting to source churn. Keeping order-grain only forces fan-out joins on product mix questions. Modeling customer address as Type 1 silently rewrites historical geo reports when a customer moves. --- ### The analysis pattern **Weekly gross sales by region and category** ```sql SELECT d.fiscal_week, s.region, p.category, SUM(f.quantity) AS units, SUM(f.extended_price) AS gross_sales FROM fact_sales f JOIN dim_date d ON d.date_key = f.date_key JOIN dim_store s ON s.store_sk = f.store_sk JOIN dim_product p ON p.product_sk = f.product_sk GROUP BY d.fiscal_week, s.region, p.category ``` --- ### Trade-offs and alternatives **Kimball star with line-grain fact** Predictable joins, conformed dimensions, good BI tool support. Writes are heavier because dimensions require SCD processing. Storage grows with dimension history. **One Big Table on columnar storage** Single wide fact with dimension attributes flattened onto every row. No joins at query time. Schema evolution is trickier and reprocessing history on a dimension change rewrites millions of rows. --- ## Common follow-up questions - How would you extend the model to track promotion attribution per order line? _(Tests adding a `dim_promotion` and whether it lives as a factless bridge.)_ - Which SCD type would you use on `dim_employee` role and why? _(Tests role tracking trade-offs; promotions usually need Type 2 for commission history.)_ - A customer address corrects a typo. Does that trigger a new `dim_customer` row? _(Tests the difference between a real change and a data quality fix.)_ - How would you handle returns without breaking the additive grain? _(Tests whether returns become negative-quantity rows or a separate `fact_returns`.)_ - How would you partition `fact_sales` to keep yearly comparisons bounded? _(Tests partitioning by `date_key` and its impact on fiscal reporting.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/every_line_remembered) - [Data Modeling Interview Questions](https://datadriven.io/data-modeling-interview-questions) - [Data Engineering Interview Prep Guide](https://datadriven.io/data-engineer-interview-prep) - [Daily Challenge](https://datadriven.io/daily) --- Source: DataDriven (https://datadriven.io). DataDriven is the data engineering interview community. Live code execution in SQL, Python, and Spark sandboxes. Every feature is open to every member.