F200 TA2: Managing Data

1 Identify three stages of the data lifecycle. 3

One mark for each of the following, to a maximum of 3.

• One valid stage
• A second, different stage
• A third, different stage

Model answer. Capture, storage, and secure destruction.

2 Explain the difference between validation and verification, giving an example of each. 4

One mark for each of the following, to a maximum of 4.

• Validation defined as an automated plausibility check
• A valid example of validation
• Verification defined as a check that the data is actually correct
• A valid example of verification

Model answer. Validation is an automated check that a value is plausible: that it is the right type, within an acceptable range, in the correct format, present at all, or matches an entry in a permitted list. Rejecting a date of 31 February is validation. Verification checks that the value is actually correct rather than merely possible, which generally needs a human or a second source: entering a customer reference twice and comparing the two, or reading a figure back against the original document. The distinction matters because validation will happily accept a perfectly formatted wrong answer, and only verification catches that.

3 A charity has merged two supporter databases. The combined file has 48,000 rows, some supporters appear twice, dates are in two different formats and 1,200 rows have no email address. Describe the steps you would take to prepare this data for analysis. 6

One mark for each of the following, to a maximum of 6.

• Profile the data before changing anything
• Standardise the date formats
• Define a key for identifying duplicates
• Deduplicate, keeping the better record
• Handle the missing emails deliberately rather than deleting rows
• Document the decisions, or validate the result

Model answer. I would profile the file before changing anything: row counts per source, the distinct values in each column, the pattern of the two date formats and a count of the blanks, so there is a baseline to reconcile against afterwards. Dates would be standardised to a single ISO format, converting each source separately rather than letting one tool guess, because the two formats are ambiguous for days of twelve or less. Deduplication needs a definition before it needs code: I would agree the matching key, probably email where present and surname plus postcode where not, then review a sample of the proposed matches before merging, keeping the most recently updated record for each field and preserving both source identifiers. The 1,200 missing emails would be flagged rather than deleted, and I would check whether they share a cause, because if they all come from one source then deleting them would quietly remove that source from every result. Finally I would document every decision in a data dictionary alongside a reconciliation of the final row count against the two originals, so that the figures can be explained and the process repeated.

4 A company's monthly sales report disagrees with the figure produced by its finance team. Discuss the data quality issues that could cause this, and how the company should prevent it recurring. 8

One mark for each of the following, to a maximum of 8.

• Different definitions of the same measure
• Timing or timeliness differences
• Duplicates or missing records
• Different sources or systems
• Manual handling introducing error
• A single source of truth is proposed
• Governance measures are proposed
• A judgement about the root cause

Model answer. The most common cause is not an error at all but a difference in definition: the sales report may be counting gross order value including VAT at the point of order, while finance counts net revenue excluding VAT and only once the order has shipped and not been returned. Both numbers are correct and they measure different things. Timing is the next candidate: a month-end cut off taken at midnight in one timezone and at close of business in another will move orders between months, as will counting by order date in one system and posting date in the other. Beyond definition and timing come genuine quality problems: duplicated rows from a reimported export, missing records from a channel whose feed failed silently, and the two teams drawing from different systems, typically an operational database on one side and a warehouse or a hand-built spreadsheet on the other. Manual handling makes all of this worse, because every copy, paste and sort is an opportunity for a row to be lost without trace. Prevention is mostly governance rather than technology. The company should agree a single source of truth for sales and publish a data dictionary defining the measure precisely, including VAT treatment, returns, cancellations and the cut off rule. Each dataset needs a named owner, automated extraction rather than manual export, recorded lineage so any figure can be traced to its source, and a monthly reconciliation between the two reports that has to be explained rather than ignored. The root cause in cases like this is almost always definitional, which is why the fix is a written definition both teams have signed up to, not a more sophisticated report.