Found a fault. Now what?
You have found six bad rows. The easy answer is to delete them all. But one of those rows is a real payment with a typing slip.
Every fix is a decision. A good decision has a reason and a test that proves it worked.
Key words
| Word | What it means |
|---|---|
| Remedial action | a step taken to put a fault right |
| Cleanse | to correct or remove bad data so what is left can be trusted |
| Regression | a new fault caused by a fix, or an old fault that comes back |
| Retest | to run the tests again after a fix |
| Convention | an agreed way of writing something, such as a date as YYYY-MM-DD |
| SQL | a language for asking questions of data kept in tables |
| Query | a question you ask of data, written in SQL |
The cells students fill in are left blank in this preview.
By the end of this lesson
Note
I can choose a fix for each fault, justify it, make it safely, follow the data conventions and test again.
Starter
Have a go
Starter
3 questionsYour answers are marked here and are not kept. A college account keeps every student’s marks.
This is practice
Important
The real task comes from the exam board. This lesson uses an invented organisation and its own made-up data. Your teacher tells you how the real tasks are set and what you may bring.
Worked example: four ways to fix
| Action | When to use it | Example from the deposits |
|---|---|---|
| Remove | The row is a repeat or has no real meaning | Row 4 repeats row 2 |
| Correct | You know the true value | Branch SOUTH becomes South |
| Fill in | You can work out the missing value from a rule | No rule here, so do not guess |
| Flag and ask | Only the owner knows the answer | Row 5 has no amount: ask the branch |
The cells students fill in are left blank in this preview.
Say why for every fix
Write the reason beside the action. "Removed row 4 because it repeats row 2 in every column and would add £40 to the total twice."
Never guess a missing amount to make a total look complete. A guess that looks real is worse than a gap that is marked.
Run it: fix on a copy
CREATE TABLE deposit_raw AS SELECT * FROM deposit;
DELETE FROM deposit WHERE deposit_id = 4;
UPDATE deposit SET branch = 'South' WHERE branch = 'SOUTH';
UPDATE deposit SET branch = 'North' WHERE branch = 'north';
SELECT branch, COUNT(*) AS rows_in FROM deposit GROUP BY branch;Always keep the original
Tip
The first line copies the raw table. If a fix is wrong you can go back. Nobody can prove a fix was right if the original is gone.