The JSON Files That Became a Data Mart

A medium Data Modeling interview practice problem on DataDriven. Design the schema on the canvas: tables, keys and relationships, then get feedback on it.

Domain
Data Modeling
Difficulty
medium
Seniority
L5
Asked in
Amaris

Problem

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.

Three semi-structured inputs. One queryable warehouse.

Worked solution and explanation

Why this problem exists in real interviews

This probes whether you can translate nested document structure into relational grain. The signal is whether you recognize that a JSON array inside a parent object is a child entity at a finer grain, and that naively mirroring the document produces repeating groups that destroy join performance.

Break down the requirements

Step 1: Unfold line_items into fact_order_lines

The line_items array is one row per item per order. Embedding line fields as columns on fact_orders would create a repeating group and break any per-product analysis.

Step 2: Unfold status_history into fact_shipment_events

Status history is a sequence of status transitions per shipment. Model it as fact_shipment_events at one-row-per-transition grain, each event carrying shipment_sk so transitions from different shipments of the same order never blur together. The shipment header itself, including its address object, lands in dim_shipment, which keeps order_sk so partial fulfillment stays first-class.

Step 3: Normalize customer and product as conformed dimensions

dim_customer and dim_product get surrogate keys; source IDs stay as natural keys. This lets the mart evolve without coupling to the source document shape.

Step 4: Model categories as a closure-style dimension

Nested category hierarchies flatten into dim_category with path and depth. A bridge table is a defensible alternative, but a denormalized path is cheaper for BI.

Step 5: Separate order header from order lines

fact_orders stores order-total and status, fact_order_lines stores per-product measures. The split prevents fan-out on queries that only need header counts.

The solution

Below is one conceptually sound approach. The grain of each child fact is derived directly from the corresponding JSON array.

`fact_order_lines` grain = one row per line item; `dim_shipment` holds one row per shipment with its address; `fact_shipment_events` grain = one row per status transition
  • dim_customer: customer_sk BIGINT PK, customer_nk TEXT, email TEXT, country TEXT, created_at TIMESTAMP
  • dim_product: product_sk BIGINT PK, product_nk TEXT, name TEXT, sku TEXT, list_price DECIMAL
  • dim_category: category_sk BIGINT PK, product_sk BIGINT FK, path TEXT, leaf_name TEXT, depth INT
  • fact_orders: order_sk BIGINT PK, order_nk TEXT, customer_sk BIGINT FK, order_ts TIMESTAMP, order_total DECIMAL, status TEXT
  • fact_order_lines: order_line_sk BIGINT PK, order_sk BIGINT FK, product_sk BIGINT FK, quantity INT, unit_price DECIMAL, line_total DECIMAL
  • dim_shipment: shipment_sk BIGINT PK, shipment_nk TEXT, order_sk BIGINT FK, street TEXT, city TEXT, country TEXT
  • fact_shipment_events: shipment_event_sk BIGINT PK, shipment_sk BIGINT FK, event_ts TIMESTAMP, status TEXT, carrier TEXT
  • dim_customer references fact_orders
  • fact_orders references fact_order_lines
  • dim_product references fact_order_lines
  • dim_product references dim_category
  • fact_orders references dim_shipment
  • dim_shipment references fact_shipment_events

The analysis pattern

Top products by category path in the last 30 days
SELECT
    p.name AS product,
    c.path AS category_path,
    SUM(ol.quantity) AS units_sold,
    SUM(ol.line_total) AS gross_sales
FROM fact_order_lines ol
JOIN fact_orders o ON o.order_sk = ol.order_sk
JOIN dim_product p ON p.product_sk = ol.product_sk
JOIN dim_category c ON c.product_sk = p.product_sk
WHERE o.order_ts >= NOW() - INTERVAL '30 days'
  AND o.status = 'fulfilled'
GROUP BY p.name, c.path

Trade-offs and alternatives

Fully normalized star
Child arrays unfold into their own facts. Writes are heavier (multiple inserts per document). Reads are cheap and each dimension evolves independently. Best when downstream BI drives the workload.
Document-preserving OBT
One wide fact stores JSON payloads as semi-structured columns. Ingestion is a single insert. Reads pay the cost of JSON parsing every query. Best when query patterns are exploratory and storage is cheap.

Common follow-up questions