Prepare the source and preserve the correction evidence
Learn the reasoning. Try the task. Compare your answer.
Prepared for DataCareerHub · Curriculum version 2026-10-15.6
By the end of this lesson
- Reconstruct a verified order population from the imperfect extract.
- Record correction authority and unresolved measurements.
- Reconcile records, identifiers and analysis eligibility.
Fictional guided portfolio · Source from Module 4 · Local spreadsheet or SQLite · Open the worked case study ↗
Understand the method
Start the project by inspecting what arrived, before creating a polished report. Keep orders_raw.csv unchanged. It has 46 records representing 44 distinct order IDs. A004 is repeated identically, while Y007 appears with conflicting channels. The verified reference contains one row per order. The project should explain how that relationship was established, rather than treating a matching total as proof that preparation was correct.
Build an issue log
Use the eight investigation entries from the earlier case as a checklist, then recreate your own evidence. Record the identifier, observed value, rule, proposed action, authority and outcome. A005’s channel can be trimmed and lowercased under the dictionary rule. The impossible date at A008, negative amount at A009, unknown channel at Y003, missing value at Y005 and invalid delivery text at Y006 require their exact reference-backed corrections.
Resolve A004 by keeping one identical record under the one-order-per-ID contract. Resolve Y007 using the verified store record, not by keeping whichever row happens to appear first. Retain the conflicting alternative in the log. Different IDs with equal monetary amounts are separate orders, so deduplicating on amount would lose valid activity. Make the key and the reason for removal explicit.
Do not invent missing observations
The verified source still contains 11 blank delivery fields: seven on completed orders and four on returned or cancelled rows. These are not unfinished cleaning tasks to fill with zero. The delivery analysis is defined over completed orders, and its observed mean uses only measured durations. Independently verified order values remain eligible for sales analysis even when their delivery measurement is missing.
Use SQL diagnostics for inspection
For the SQL route, run analysis_setup.sql only in a new empty local database. It creates raw_orders preserving incoming text and duplicates, plus orders containing the supplied verified reference. The setup does not automatically transform raw_orders into orders. That distinction matters: your preparation decisions still need explanation. Queries P1 and P2 help locate repeated keys and the six field exceptions in this fixture.
P2 uses a real-calendar date check and simple tests tailored to the documented input values. It is not a general production parser for every possible numeric string. A passing diagnostic query cannot establish that an export includes every real business event. Keep the source’s limited coverage and the distinction between formatting, validity and accuracy in your project notes.
Reconcile preparation
Your record bridge should read 46 raw records, minus one exact excess copy and one conflicting excess copy, giving 44 retained unique orders. Field corrections change attributes but not that count. Filtering to completed status gives 40 orders, 20 per month. Compare prepared fields by order_id against the reference, not by row position after sorting.
In the final case study, show a compact preparation summary and keep the detailed issue log available. Explain that A020 at €1,000 is verified and remains in baseline data. It is an influential analytical observation, not another defect to remove. The preparation step is complete when decisions are auditable, not when every column happens to contain a value.
Practical exercise
Recreate preparation from the raw extract on your own local copy. Write an eight-entry issue log, resolve both repeated-key groups and apply only documented or reference-backed field corrections. Preserve genuine delivery blanks. Run P1/P2 if using SQLite, or perform equivalent key and field checks in your spreadsheet.
Deliverable: a prepared file, correction log and row bridge. Compare all retained fields against orders_verified.csv by ID. Explain the role of the supplied reference and state what would remain unresolved if that authority were unavailable.
Open the worked solution
| Stage | Records / orders |
|---|---|
| Raw source | 46 records / 44 IDs |
| Remove exact excess A004 | 45 records |
| Resolve conflicting excess Y007 | 44 records / 44 IDs |
| Apply six field corrections | 44 retained orders |
| Completed analytical population | 40 orders / 20 each month |
Corrections are A005 → web, A008 → 2026-04-08, A009 → €110, Y003 → store, Y005 → €190 and Y006 → four delivery days. The completed source log is supplied in quality_log_solution.csv. Y003 demonstrates why “mobile” cannot simply be guessed to mean web; the reference confirms store.
P1 returns A004 and Y007 with two source records each. P2 returns the six field-exception IDs. Keep 11 verified delivery blanks, seven of them on completed orders. Without authoritative values, record unresolved issues and qualify affected results rather than filling guesses. The supplied orders SQL table is a reference, not evidence that an undocumented cleaning algorithm succeeded.
One more question.
How these learning materials are prepared and maintained · Report a correction: support@datacareerhub.io
Read and practice without an account. Sign in with an existing account to save lesson completion.
Next lesson →Fictional training data. Portfolio practice is not a claim of client work or an accredited qualification. Explore practical lessons, worked examples and projects. Follow a published path, practise the method and build work you can explain.