Original practice scenario: Design a synthetic beverage traceability system connecting production batches, packaging lots and distributor shipments, including later data corrections.
Read model answer +
Model traceability as versioned relationships between independently identified lots. The system must distinguish physical lineage from commercial shipment records and retain the evidence behind corrections.
Work through it
Define identifiers and quantities for production, packaging and shipment entities; represent splits and merges with explicit edges and units.
Validate conservation rules where applicable, quarantine conflicting scans and append corrections with author, time and reason rather than deleting history.
Support bounded upstream and downstream searches from a lot, with tests for returns, partial shipments, duplicate events and missing links.
Tradeoffs to discuss
An append-only history supports investigation but requires clear views of current accepted lineage and disputed evidence.
Common pitfalls
A scanned label alone does not establish physical authenticity.
Do not assume every source reports events immediately or in causal order.
Original practice scenario: A synthetic beverage dataset has shipment lines, promotion records and distributor territories. Explain a safe monthly sales query when each shipment has several promotions.
Read model answer +
Declare the result grain as distributor and reporting month, and aggregate each independent many-side relation before combining it. Otherwise promotions can multiply shipment revenue.
Work through it
Deduplicate shipment lines by their stable business key and accepted revision, then compute quantity and revenue in a documented currency and unit.
Summarize promotion exposure separately at a compatible grain; resolve territory history using the shipment date and non-overlapping effective intervals.
Join the summaries and reconcile totals to the shipment-only baseline, retaining distributors with no promotion and explicitly handling missing territory.
Tradeoffs to discuss
Pre-aggregation limits row multiplication but can remove detail needed for attribution; maintain separate detailed analysis when required.
Common pitfalls
Do not repair fanout with an unexplained DISTINCT.
Use documented return and cancellation rules instead of silently treating every shipment as completed sales.
