Refresh-resilient multi-file analyst case
Ostrander Cold Chain (fictional)
Standardise depot files with drifting layouts from a column map and settings file, separate valid shipments from data-quality problems, resolve resends, and build a status matrix that grows with new statuses.
- Level
- advanced
- Time
- ~90 min
- Deliverables
- 4
- Rubric
- v1
Access
Pro. This brief is public. The data pack, workspace, hints, grading and solution explanations need Pro, Lifetime Learning or a seat in an organization. See plans.
Each deliverable's solution explanation unlocks when it passes. You can also unlock it early once you have used every hint for that deliverable (this needs an account so the help is recorded, and the deliverable then counts as completed with help).
Business goal
Give the operations analyst a pipeline that survives whatever the depots send next: every valid shipment once, a log of rows that need fixing, and a depot-by-status matrix for the Monday call.
Ostrander moves chilled freight between depots. Each depot exports its week's shipments from a different system, so the files arrive with different column names, column orders and extra columns. The data team keeps a column map (raw name → standard field) and a small JSON settings file listing depots to exclude, such as the training depot. Depots sometimes resend a corrected file; the most recently modified file is the truth. Bad dates and weights must not disappear silently: they go to a log the depots fix.
Graded deliverables
1. Standardized Rows
25 pointsEvery data row from every CSV, under the five standard column names, with file name and modified time.
- Columns: SourceFile, Modified, ShipmentID, Depot, ShipDate, Status, WeightKg
- Every row of every CSV, whatever its layout
- Values unchanged (text as in the file)
Expected output schema for Standardized Rows Column Type SourceFile text Modified datetime ShipmentID text Depot text ShipDate text Status text WeightKg text - Grain:
- One row per data row in any CSV file in the folder.
- Order:
- Not graded.
- Nulls:
- No nulls are expected; values stay as text.
2. Shipments
30 pointsEach valid shipment once: excluded depots removed, resends resolved, values cleaned and typed.
Builds on: Standardized Rows
- Columns: ShipmentID, Depot, ShipDate, Status, WeightKg, SourceFile
- One row per ShipmentID, from the latest file
- No excluded depots; no invalid dates or weights
Expected output schema for Shipments Column Type ShipmentID text Depot text ShipDate date Status text WeightKg decimal SourceFile text - Grain:
- One row per ShipmentID whose latest row is valid and not from an excluded depot.
- Order:
- Not graded.
- Nulls:
- No nulls.
3. Data Quality Log
20 pointsEvery row (outside excluded depots) whose date or weight is unusable, with the problem named.
Builds on: Standardized Rows
- Columns: SourceFile, ShipmentID, Problem
- Rows from excluded depots are not logged
- Both problems named when both fail
- Sorted by SourceFile, then ShipmentID
Expected output schema for Data Quality Log Column Type SourceFile text ShipmentID text Problem text - Grain:
- One row per staging row with at least one problem.
- Order:
- Graded: SourceFile ascending, then ShipmentID ascending.
- Nulls:
- No nulls.
4. Depot Status Matrix
25 pointsValid shipments per depot by status, one column per status that occurs, plus a total.
Builds on: Standardized Rows, Shipments
- Columns: Depot, one per status in the data, Total
- 0 where a depot has no shipments with a status
- Sorted by Depot
Expected output schema for Depot Status Matrix Column Type Depot text (one column per status) whole number Total whole number - Grain:
- One row per depot with at least one valid shipment.
- Order:
- Graded: Depot ascending.
- Nulls:
- No nulls: missing combinations are 0.