Three Lines, Nine Claims
Two Q2 totals and a fulfillment workbook disagree across tables with different grains and clocks. Decide what Finance can certify today.
- ROLE
- Commercial Operations Analyst
- TIMEBOX
- 75–105 minutes
- COMPLEXITY
- Intermediate / 3 of 5
- ROTATION
- Brief 2 of 16
The decision has already reached your desk.
The Q2 close is paused. HarborHome source order numbers repeat after the acquisition cutover, two analysts produced different booked-value totals, and a fulfillment workbook contains durations that appear to run backward.
The source pack contains canonical order headers, purchase lines and one-to-many order and shipment events. Each table has its own record and warehouse clocks. A join that looks reasonable can multiply money or use evidence that arrived after the close cutoff.
Finance does not need a complete transition program this morning. It needs a controlled Q2 order count, captured booked value and a ruling on whether the supplied fulfillment headline is supportable.
Certify, qualify, or withhold the Q2 volume, booked-value and fulfillment headline, with a grain-safe control table.
Freeze all evidence at 08 Jul 2024 08:30 ET. Preserve source-number collisions and impossible clock sequences as exceptions; do not repair or deduplicate them silently.
Run the brief as a controlled assignment.
The workbench mounts only the listed source neighborhood and supplies neutral starter worksheets. It does not grade the conclusion or reveal the mechanisms planted in the larger assignment.
- Open the dedicated brief workspace and confirm the brief ID in the queue.
- Establish table grain, cutoff and control totals before joining or modeling.
- Make at least two distinct evidence moves and test a credible rival explanation.
- Leave the requested polished artifact, then export the workspace or portfolio package.
The source files are shared; the draft workspace is not. Changing to another full assignment drops brief mode deliberately.
Real tables. A deliberately bounded neighborhood.
These Parquet files already belong to Meridian’s public 96-table estate. Use the mounted schema.table names in SQL or Python; download links are provided for learners working in a local DuckDB environment.
GRAIN / One canonical Q2 order header.
CAUTION / Source order numbers can repeat across systems; canonical order_id and warehouse availability control the population.
GRAIN / One captured purchase line.
CAUTION / Reduce lines to order grain before combining with another one-to-many event table.
GRAIN / One order lifecycle event.
CAUTION / Occurrence, source-recorded and warehouse-available clocks differ; multiple events belong to one order.
ANALYSIS CUTOFF / 08 Jul 2024 / 08:30 ET
Questions to pressure-test—not steps to copy.
These prompts define the analytical territory without prescribing an order, technique or conclusion.
- 01
What should one row represent at each stage of the calculation?
- 02
Which identifier is canonical across the cutover, and where can a direct join fan out?
- 03
Which event clock answers operational elapsed time, and which records were actually knowable at the cutoff?
Leave work another analyst can review.
Artifact presence can be recorded; analytical quality remains a human judgment. A complete brief has evidence, reasoning and a decision—not merely executed code.
- 01Grain map
State the grain and key of each file and show where a direct three-way join can fan out.
- 02Close control
Produce Q2 order count and captured booked value by source system with pre/post-join row and value reconciliations.
- 03Clock exception view
Quantify unavailable-at-cutoff and impossible-sequence records before calculating any fulfillment duration.
- 04Controller handoff
Deliver a polished certification note naming what is certified, qualified or withheld and why.
Review the reasoning after a real attempt.
The debrief does not contain an official answer. It identifies defensible analytical moves, common failure modes and questions a reviewer may use to challenge the handoff.
SPOILER-GATED REVIEW / REVEAL AFTER YOUR FIRST HANDOFF
DEBRIEF REVEALED / THIS MAY CHANGE HOW YOU APPROACH THE BRIEF
The challenge is constructing compatible facts before joining and proving that money, orders and elapsed times survive both grain and cutoff controls.
Defensible approaches
- Use canonical order_id for the business fact while retaining source_system plus source_order_id as a collision diagnostic.
- Reduce lifecycle and shipment events to one declared milestone per order before joining them to headers or lines.
- Reconcile header and line values separately, then show row counts, distinct orders and value totals before and after each join.
Common traps
- Joining all three files and trusting GROUP BY to repair multiplication.
- Deduplicating repeated source_order_id without including the source system.
- Using the latest or cleanest event rather than the event that was knowable at the frozen cutoff.
Reviewer questions
- Can every reported order and dollar trace to exactly one controlled fact?
- Would a late-arriving event alter a supposedly frozen result?
- Did you distinguish a valid close measure from a useful but not-yet-certifiable operational metric?
CHALLENGE A COLLEAGUE
Pass the Brief
The link shares this spoiler-free briefing. It never includes your work, identity, or browser progress.SUGGESTED NOTEA routine multi-table close could multiply money and move clocks. I audited the grain in Three Lines, Nine Claims from The Analyst.
