Original practice scenario inspired by Texas Instruments Incorporated's public business context; this is not a reported interview question or a description of its internal systems. A fictional semiconductor test station sends many measurements per device. The collector must preserve sequence gaps while supporting a lower-volume pass/fail dashboard. Design the ingestion and aggregation path.
Read model answer +
Separate raw measurement durability from the dashboard projection. Each station session needs a sequence namespace so missing or repeated messages can be detected without confusing a station restart with normal continuation.
Work through it
Attach station, session, device, sequence, and event-time identifiers. Acknowledge only after durable acceptance, and deduplicate stable event keys during retries.
Build device-level summaries from versioned completion rules. Mark a result incomplete when required sequences or measurements are missing rather than inferring pass from the available subset.
Partition ingestion to preserve necessary per-session ordering and bound queues. Test restart, reordered delivery, sequence gaps, and slow downstream storage while monitoring lag.
Tradeoffs to discuss
Raw retention enables reprocessing but costs substantial storage. Aggregating near the source reduces bandwidth at the expense of later diagnostic detail.
Common pitfalls
A global sequence counter can collide after reboot if its session is missing.
Dashboard freshness does not prove the underlying measurement set is complete.
Original practice scenario inspired by Texas Instruments Incorporated's public business context; this is not a reported interview question or a description of its internal systems. A synthetic manufacturing database stores device test attempts with attempt_number, outcome, and test_program. Explain how to compute first-pass yield and final observed yield without counting retests as new devices.
Read model answer +
Use one device as the denominator and select attempts according to each metric's definition. First-pass yield describes the initial eligible attempt; final observed yield describes the latest eligible known result.
Work through it
Define the device population, observation cutoff, and test-program comparability. Validate uniqueness of the ordering key for attempts on each device.
Use ROW_NUMBER with ascending attempt order for first results and descending order for final results. Aggregate pass indicators over the same eligible device population.
Report unresolved devices and the follow-up window. Test a fail-then-pass device, repeated failed attempts, duplicate ingestion, and an attempt arriving after cutoff.
Tradeoffs to discuss
Final observed yield improves with additional follow-up, so recent and old cohorts may not be comparable. Program changes may require separate reporting strata.
Common pitfalls
Counting passing attempt rows over all attempt rows measures attempt success, not device yield.
Selecting MAX(outcome) invents an ordering unrelated to test chronology.
