Original practice scenario inspired by Thermo Fisher Scientific Inc.'s public business context; this is not a reported interview question or a description of its internal systems. A fictional laboratory platform imports instrument files and produces derived results. Analysts can rerun processing with a new method version. Design provenance so a displayed result can be reproduced without confusing it with later reruns.
Read model answer +
Identify a result by its input artifact and processing specification, not just by sample name. Reruns should create distinct derived versions linked to their immutable inputs and method configuration.
Work through it
Store source-file checksums, instrument identity, acquisition time, and import validation. Keep original files separate from normalized records.
Record method version, parameters, software environment, and execution status for each derivation. Promote a result to the current view through an explicit selection action.
Expose provenance and supersession relationships in the interface. Test duplicate imports, partially uploaded files, method upgrades, and two simultaneous reruns of the same sample.
Tradeoffs to discuss
Capturing environments and artifacts increases storage but makes discrepancies diagnosable. Reproducibility also depends on preserving external dependencies used by the method.
Common pitfalls
Overwriting the old value loses scientific context.
A human-readable filename alone is not a reliable identifier or integrity check.
Original practice scenario inspired by Thermo Fisher Scientific Inc.'s public business context; this is not a reported interview question or a description of its internal systems. A synthetic lab database has samples, required_stages by assay type, and stage_runs with retry attempts and statuses. Explain a query that finds each sample's missing successful stages without counting failed retries as completion.
Read model answer +
Construct the required sample-stage pairs first, then test whether a qualifying successful run exists for each pair. Retried attempts should not multiply requirements or erase missing stages.
Work through it
Join each eligible sample to stages required for its assay type and the applicable requirement version. Define whether canceled samples belong in the report.
Use NOT EXISTS against successful stage runs keyed by sample and stage. Apply the documented cutoff or method-version rule inside that existence test.
Return sample_id and missing_stage in a deterministic order. Test duplicate successes, failed-then-successful retries, a sample with no runs, and an assay with no requirements.
Tradeoffs to discuss
A completeness report may need historical requirement versions. Reinterpreting old samples under a new stage list changes the business question.
Common pitfalls
COUNT(stage_runs) measures attempts, not completed stages.
An inner join to runs removes the very samples whose missing work must be reported.
