Data Preparation: Cleaning, Repair and Reconciliation
Sietske, the HR Business Partner at Raket Parts, needs a sick-hours figure, but the raw HR extract contains historical versions and unknown values. Learn to check what a row means, whether identifiers repeat, and whether the table can support the question. Work with the data of Raket Parts, a fictional maker of rocket parts.
You will be able to
- profile a source and decide whether it is fit, fit with limits or not fit with evidence
- classify an unusual record and preserve genuine business events
- route unresolved cases by consequence
- specify and review clear data quality rules
Is this table fit for the job?
Profile a source and decide whether it is fit, fit with limits or not fit with evidence.
Error or exception?
Classify an unusual record, preserve genuine business events, and route unresolved cases by consequence.
Rules a machine can check
Specify and review clear data quality rules, including what to do when a check cannot run.
Clean by rule, not by eye
Write cleaning rules with a change log and a no-overwrite condition, check that a rerun changes nothing, and send what the rules cannot decide to its owner.
Do the sources agree?
Match supplier invoices to orders and receipts line by line, recompute their amounts and classify every difference instead of trusting a total that agrees.
Data Preparation / Module 1
Is this table fit for the job?
Sietske, the HR Business Partner at Raket Parts, needs a sick-hours figure, but the raw HR extract contains historical versions and unknown values. Learn to check what a row means, whether identifiers repeat, and whether the table can support the question.
Sections
Data Preparation / Module 1 / Free practice
Try it on your own question
Run it in the practice editor
The practice editors have the Raket Parts tables loaded. Type a query or a formula, run it and change it as often as you like. Nothing is marked.
Data Preparation / Module 2
Error or exception?
Teun has a list of odd records and wants them removed. Learn which ones are true duplicates, which document real reversals or partial events, and when to hold a record for the owner.
This module builds on module 1. If you are new to this, start with module 1.
Sections
Data Preparation / Module 2 / Free practice
Try it on your own question
Run it in the practice editor
The practice editors have the Raket Parts tables loaded. Type a query or a formula, run it and change it as often as you like. Nothing is marked.
Data Preparation / Module 3
Rules a machine can check
Jelmer receives requests such as “hours should be right”. Turn wishes into checks with a field, condition, limit, consequence and owner. A rule must also detect the data it could not inspect.
This module builds on module 2. If you are new to this, start with module 2.
Sections
Data Preparation / Module 3 / Free practice
Try it on your own question
Run it in the practice editor
The practice editors have the Raket Parts tables loaded. Type a query or a formula, run it and change it as often as you like. Nothing is marked.
Data Preparation / Module 4
Clean by rule, not by eye
Jelmer has a cleaning standard the data owners agreed. Before anything runs automatically, every change needs a written rule, a log line, the same result on a second run, and confirmed values left alone. Whatever the rules cannot decide goes to its owner.
This module builds on module 3. If you are new to this, start with module 3.
Sections
Data Preparation / Module 4 / Free practice
Try it on your own question
Run it in the practice editor
The practice editors have the Raket Parts tables loaded. Type a query or a formula, run it and change it as often as you like. Nothing is marked.
Data Preparation / Module 5
Do the sources agree?
Teun asks whether the Q3 2025 supplier invoices are supported. The ledger agrees with the invoices to the cent. Jelmer checks what that agreement proves, then recomputes from orders and receipts and classifies every difference.
This module builds on module 4. If you are new to this, start with module 4.
Sections
Data Preparation / Module 5 / Free practice
Try it on your own question
Run it in the practice editor
The practice editors have the Raket Parts tables loaded. Type a query or a formula, run it and change it as often as you like. Nothing is marked.
Data Preparation / Module 1 / 1.1
1.1 What one row stands for
Section 1.1 complete
Next: 1.2 Is the identifier unique?
Data Preparation / Module 1 / 1.2
1.2 Is the identifier unique?
Section 1.2 complete
Next: 1.3 Blanks, zeros and states
Data Preparation / Module 1 / 1.4
1.4 Fit for which use?
Section 1.4 complete
Next: Module 1 check
Continue to the module check Recap this section Back to module 1
Data Preparation / Module 1 / Check
Module 1 check
Module 1 complete
Next: Module 2, Error or exception?
Continue to module 2 Try it on your own question Recap module 1 Back to module 1
Data Preparation / Module 2 / 2.1
2.1 Odd is not wrong
Section 2.1 complete
Next: 2.2 Duplicates and retries
Data Preparation / Module 2 / 2.2
2.2 Duplicates and retries
Section 2.2 complete
Next: 2.3 Partial and unknown
Data Preparation / Module 2 / 2.4
2.4 Route and rank
Section 2.4 complete
Next: Module 2 check
Continue to the module check Recap this section Back to module 2
Data Preparation / Module 2 / Check
Module 2 check
Module 2 complete
Next: Module 3, Rules a machine can check
Continue to module 3 Try it on your own question Recap module 2 Back to module 2
Data Preparation / Module 3 / 3.1
3.1 From wish to rule
Section 3.1 complete
Next: 3.2 Rules that miss blanks
Data Preparation / Module 3 / 3.4
3.4 Run the rules
Section 3.4 complete
Next: Module 3 check
Continue to the module check Recap this section Back to module 3
Data Preparation / Module 3 / Check
Module 3 check
Module 3 complete
Next: Module 4, Clean by rule, not by eye
Continue to module 4 Try it on your own question Recap module 3 Back to module 3
Data Preparation / Module 4 / 4.1
4.1 The obvious fix
Section 4.1 complete
Next: 4.2 Write the standard as rules
Data Preparation / Module 4 / 4.3
4.3 Run it twice
Section 4.3 complete
Next: 4.4 Confirmed values, the queue and fixes at source
Data Preparation / Module 4 / 4.4
4.4 Confirmed values, the queue and fixes at source
Section 4.4 complete
Next: Module 4 check
Continue to the module check Recap this section Back to module 4
Data Preparation / Module 4 / Check
Module 4 check
Module 4 complete
Next: Module 5, Do the sources agree?
Continue to module 5 Try it on your own question Recap module 4 Back to module 4
Data Preparation / Module 5 / 5.1
5.1 Agreeing is not proving
Section 5.1 complete
Next: 5.2 Recompute, and count the rows first
Data Preparation / Module 5 / 5.2
5.2 Recompute, and count the rows first
Section 5.2 complete
Next: 5.3 Match line by line and classify
Data Preparation / Module 5 / 5.3
5.3 Match line by line and classify
Section 5.3 complete
Next: 5.4 No residual left unexplained
Data Preparation / Module 5 / 5.4
5.4 No residual left unexplained
Section 5.4 complete
Next: Module 5 check
Continue to the module check Recap this section Back to module 5
Data Preparation / Module 5 / Check
Module 5 check
Module 5 complete
Next: Module 6, coming later
Back to the Path Try it on your own question Recap module 5 Back to module 5
Data Preparation / Module 1 / Recap
Recap card
1.1 What one row stands for
What one row stands for
- A saved version is not automatically a new business event.
- Count different absence references when the question is about absences.
Common mistake
The trap. Adding all history rows counts revised records twice.
Data Preparation / Module 1 / Recap
Recap card
1.2 Is the identifier unique?
Is the identifier unique?
- Compare the total row count with different identifier values.
- A source replay can move records without creating new events.
Common mistake
The trap. Treating every repeated key as an invalid business record.
Data Preparation / Module 1 / Recap
Recap card
1.3 Blanks, zeros and states
Blanks, zeros and states
- A blank is not a value of zero.
- Read a reason or state before deciding what missing means.
Common mistake
The trap. Turning UNKNOWN absence hours into zero understates what may be missing.
Data Preparation / Module 1 / Recap
Recap card
1.4 Fit for which use?
Fit for which use?
- A fitness verdict always names the intended use.
- Report available measures together with their limitations.
Common mistake
The trap. Calling a partly observed total complete.
Data Preparation / Module 1 / Recap
Recap card
Module 1: Is this table fit for the job?
What one row stands for
- A saved version is not automatically a new business event.
- Count different absence references when the question is about absences.
Common mistake
The trap. Adding all history rows counts revised records twice.
Is the identifier unique?
- Compare the total row count with different identifier values.
- A source replay can move records without creating new events.
Common mistake
The trap. Treating every repeated key as an invalid business record.
Blanks, zeros and states
- A blank is not a value of zero.
- Read a reason or state before deciding what missing means.
Common mistake
The trap. Turning UNKNOWN absence hours into zero understates what may be missing.
Fit for which use?
- A fitness verdict always names the intended use.
- Report available measures together with their limitations.
Common mistake
The trap. Calling a partly observed total complete.
Data Preparation / Module 2 / Recap
Recap card
2.1 Odd is not wrong
Odd is not wrong
- An odd-looking record can be a genuine event.
- Keep the evidence needed to reverse or explain a transaction.
Common mistake
The trap. Deleting a genuine credit note because it is unusual.
Data Preparation / Module 2 / Recap
Recap card
2.2 Duplicates and retries
Duplicates and retries
- A source can resend a record without creating a new event.
- A rejected duplicate stays as an audit record but should not be treated as payable.
Common mistake
The trap. Counting a replay or rejected duplicate as a new transaction.
Data Preparation / Module 2 / Recap
Recap card
2.3 Partial and unknown
Partial and unknown
- An incomplete process can be valid.
- Unknown status deserves owner review, not an invented outcome.
Common mistake
The trap. Assuming an unreceived return is a defective duplicate.
Data Preparation / Module 2 / Recap
Recap card
2.4 Route and rank
Route and rank
- Each unresolved class needs a reason, an owner and release evidence.
- For equal kinds of risk, compare money at stake before the count of flags.
Common mistake
The trap. Ranking cases only by how many rows are involved.
Data Preparation / Module 2 / Recap
Recap card
Module 2: Error or exception?
Odd is not wrong
- An odd-looking record can be a genuine event.
- Keep the evidence needed to reverse or explain a transaction.
Common mistake
The trap. Deleting a genuine credit note because it is unusual.
Duplicates and retries
- A source can resend a record without creating a new event.
- A rejected duplicate stays as an audit record but should not be treated as payable.
Common mistake
The trap. Counting a replay or rejected duplicate as a new transaction.
Partial and unknown
- An incomplete process can be valid.
- Unknown status deserves owner review, not an invented outcome.
Common mistake
The trap. Assuming an unreceived return is a defective duplicate.
Route and rank
- Each unresolved class needs a reason, an owner and release evidence.
- For equal kinds of risk, compare money at stake before the count of flags.
Common mistake
The trap. Ranking cases only by how many rows are involved.
Data Preparation / Module 3 / Recap
Recap card
3.1 From wish to rule
From wish to rule
- State the field, condition, limit, consequence and owner.
- Use testable language instead of a broad hope.
Common mistake
The trap. Calling an aspiration an automated quality check.
Data Preparation / Module 3 / Recap
Recap card
3.2 Rules that miss blanks
Rules that miss blanks
- Count what the check covered and skipped.
- A skipped blank cannot become a pass without evidence.
Common mistake
The trap. Declaring success because the check never inspected missing values.
Data Preparation / Module 3 / Recap
Recap card
3.3 Block or warn
Block or warn
- Block misleading completeness claims; warn where limited use remains safe.
- Name the person accountable for reviewing each failure.
Common mistake
The trap. Treating all warnings as safe substitutes for stops.
Data Preparation / Module 3 / Recap
Recap card
3.4 Run the rules
Run the rules
- A run report records failures, coverage, response and owner.
- An absent column causes a rule failure, never an empty success.
Common mistake
The trap. Treating an untested or missing field as a clean pass.
Data Preparation / Module 3 / Recap
Recap card
Module 3: Rules a machine can check
From wish to rule
- State the field, condition, limit, consequence and owner.
- Use testable language instead of a broad hope.
Common mistake
The trap. Calling an aspiration an automated quality check.
Rules that miss blanks
- Count what the check covered and skipped.
- A skipped blank cannot become a pass without evidence.
Common mistake
The trap. Declaring success because the check never inspected missing values.
Block or warn
- Block misleading completeness claims; warn where limited use remains safe.
- Name the person accountable for reviewing each failure.
Common mistake
The trap. Treating all warnings as safe substitutes for stops.
Run the rules
- A run report records failures, coverage, response and owner.
- An absent column causes a rule failure, never an empty success.
Common mistake
The trap. Treating an untested or missing field as a clean pass.
Data Preparation / Module 4 / Recap
Recap card
4.1 The obvious fix
The obvious fix
- Confirmed values: no rule writes over them, however right it looks
- Fix at source: a name typed into a report is back missing next month
Common mistake
The obvious fix. “Take the supplier's date” would overwrite X-CNF-0006 and X-CNF-0012 and settle 14 disputes.
Data Preparation / Module 4 / Recap
Recap card
4.2 Write the standard as rules
Write the standard as rules
- Rule parts: ID, input, output, leave-alone condition, queue
- Change log: rule, record, old value, new value
Common mistake
Skipping the zero. CTRY-01 changed 0 of 436 customer rows, and the run is still recorded.
Data Preparation / Module 4 / Recap
Recap card
4.3 Run it twice
Run it twice
- Safe to rerun: run 2 on run 1's output changes nothing
- Merge: deciding two records are one customer is the owner's call
Common mistake
Tidying into a merge. Removing digits turns 32 German university labels into one name.
Data Preparation / Module 4 / Recap
Recap card
4.4 Confirmed values, the queue and fixes at source
Confirmed values, the queue and fixes at source
- Queue gross: 14 DISPUTED dates plus 55 INCONCLUSIVE causes, each with an owner
- Source fixes: Teun adds the three missing labels in the master
Common mistake
Calling it final. The run changed no delivered-by date; 14 are still DISPUTED.
Data Preparation / Module 4 / Recap
Recap card
Module 4: Clean by rule, not by eye
The obvious fix
- Confirmed values: no rule writes over them, however right it looks
- Fix at source: a name typed into a report is back missing next month
Common mistake
The obvious fix. “Take the supplier's date” would overwrite X-CNF-0006 and X-CNF-0012 and settle 14 disputes.
Write the standard as rules
- Rule parts: ID, input, output, leave-alone condition, queue
- Change log: rule, record, old value, new value
Common mistake
Skipping the zero. CTRY-01 changed 0 of 436 customer rows, and the run is still recorded.
Run it twice
- Safe to rerun: run 2 on run 1's output changes nothing
- Merge: deciding two records are one customer is the owner's call
Common mistake
Tidying into a merge. Removing digits turns 32 German university labels into one name.
Confirmed values, the queue and fixes at source
- Queue gross: 14 DISPUTED dates plus 55 INCONCLUSIVE causes, each with an owner
- Source fixes: Teun adds the three missing labels in the master
Common mistake
Calling it final. The run changed no delivered-by date; 14 are still DISPUTED.
Data Preparation / Module 5 / Recap
Recap card
5.1 Agreeing is not proving
Agreeing is not proving
- A copy agrees: each ledger journal is posted from its invoice
- Independent sources: the order gives the price, the receipt the quantity
Common mistake
Trusting the match. €8,420,751.45 on both sides shows the ledger copied 166 invoices, 35 of them with no order.
Data Preparation / Module 5 / Recap
Recap card
5.2 Recompute, and count the rows first
Recompute, and count the rows first
- Recompute: receipt quantity times the order's unit price
- Rows first: 877 receipts for 793 orders; 84 arrived twice
Common mistake
Doubling the order. X-PO-00003's €9,618.84 on both receipt rows becomes €19,237.68.
Data Preparation / Module 5 / Recap
Recap card
5.3 Match line by line and classify
Match line by line and classify
- Lines, not headers: X-SIN-00145 splits into a matched €12,003.36 and an unsupported €1,534.09
- Classes: no order support, no receipt by design, timing, currency
Common mistake
Naming a cause. The data does not say what €1,534.09 is; it is queried.
Data Preparation / Module 5 / Recap
Recap card
5.4 No residual left unexplained
No residual left unexplained
- Gross: -€0.01 and +€0.01 are two items, never €0.00
- As at a date: October receipts stay out of a 30 September view
Common mistake
Plugging the cent. A €0.01 “other” line hides a difference instead of explaining it.
Data Preparation / Module 5 / Recap
Recap card
Module 5: Do the sources agree?
Agreeing is not proving
- A copy agrees: each ledger journal is posted from its invoice
- Independent sources: the order gives the price, the receipt the quantity
Common mistake
Trusting the match. €8,420,751.45 on both sides shows the ledger copied 166 invoices, 35 of them with no order.
Recompute, and count the rows first
- Recompute: receipt quantity times the order's unit price
- Rows first: 877 receipts for 793 orders; 84 arrived twice
Common mistake
Doubling the order. X-PO-00003's €9,618.84 on both receipt rows becomes €19,237.68.
Match line by line and classify
- Lines, not headers: X-SIN-00145 splits into a matched €12,003.36 and an unsupported €1,534.09
- Classes: no order support, no receipt by design, timing, currency
Common mistake
Naming a cause. The data does not say what €1,534.09 is; it is queried.
No residual left unexplained
- Gross: -€0.01 and +€0.01 are two items, never €0.00
- As at a date: October receipts stay out of a 30 September view
Common mistake
Plugging the cent. A €0.01 “other” line hides a difference instead of explaining it.