High-volume order events with nested line items and late corrections
An online ticket marketplace sends order events to Data Cloud through the Ingestion API at about 2.3M events per day, peaking at 9K per minute during on-sales. Each JSON payload contains order header fields (order_id, buyer_email, order_ts, total_amount, currency) plus a nested array of up to 40 line items (line_id, event_id, quantity, price, seat_section). Up to 3% of line items arrive 24 to 48 hours late because of partner reconciliation, and the same order_id can appear again with corrected totals. The team currently maps the entire payload into one DLO with the line items stored as a JSON string. Segmentation needs last order date and number of orders in the last 12 months at the buyer level, plus event-category affinity. Queries on the JSON string are slow and line-item reporting is impossible.
1. Recommend how to ingest and model this payload. Specify streams, keys, DLO and DMO choices, and explain why the current JSON-string approach fails.
Read the worked answer
Split the payload at ingestion into two flat streams: an order-header stream keyed by order_id, and an order-line stream keyed by line_id with order_id as a foreign key. The Ingestion API payload should be flat; nested arrays should not be buried in one field. Keep a raw DLO for audit if needed, then map to modeled DMOs: a sales-order-like DMO for the header and a sales-order-product-like DMO for line items, with a relationship from line to header.
The JSON-string approach fails for three reasons. First, field mapping and relationships cannot be applied to fields inside a string, so buyer-level metrics cannot use order attributes efficiently. Second, the DMO cannot relate line items to orders, so event-category affinity is impossible. Third, every query has to parse the string, which is slow and prevents normal filters and aggregations. Flattening at ingestion is the design choice that makes both modeling and segmentation possible.
2. How should late and corrected records be handled so buyer-level metrics and line-level affinity stay accurate?
Read the worked answer
Use stable primary keys and upsert semantics rather than append-only assumptions. For the header, order_id is the key; for the line, line_id is the key. When a corrected order arrives, the same key updates the record instead of creating a duplicate. If the DLO layer is append-only, add a deterministic transformation or deduplication step before the DMO so only the latest version of each key is modeled.
Separate event time from ingestion time. order_ts should be the business event timestamp used by metrics, not the time Data Cloud received the record. Late-arriving line items should still attach to the existing order because order_id is the relationship key. After late loads, calculated insights and segments that depend on those orders must refresh on a cadence that follows ingestion. Monitor late-record volume and reconcile header totals against line totals so corrected orders do not silently diverge.
3. How would you calculate last order date and orders in the last 12 months without double-counting buyers who have many line items?
Read the worked answer
Calculate both metrics from the order-header DMO, not from a joined header-plus-line result set. Count distinct order_id and take the maximum order_ts per unified buyer. Join the header to the individual through the buyer identifier, typically email and party identification, so the metric lands on the unified profile.
Line items are used for affinity only. For event-category affinity, count distinct event_id or line_id per category from the line DMO after linking to the buyer through order_id. If you join header to line first and then count orders, each order fans out into many rows and the order count inflates. Keep the two calculations separate: header for order frequency and recency, line for product and category behavior.