F200 TA2: Managing Data
The data lifecycle and the analytics pipeline, how data is gathered, stored and transformed, and what data quality actually means.
The data lifecycle and the analytics pipeline
The data lifecycle
Data has a life, and each stage has its own risks and its own legal duties.
| Stage | What happens | The risk here |
|---|---|---|
| Create or capture | Data is generated or collected | Collecting more than you need, or capturing it wrong |
| Store | It is written somewhere durable | Insecure storage, no backup, wrong location |
| Use and process | It is queried, analysed, shared | Using it for a purpose it was not collected for |
| Share or publish | It goes to another system or person | Disclosure, re-identification, loss in transit |
| Archive | It is moved to cheaper, colder storage | Forgetting it exists while remaining responsible for it |
| Destroy | It is securely deleted | Keeping it past its retention period |
The analytics pipeline
| Step | What it produces |
|---|---|
| 1. Define the question | A decision somebody will actually make differently |
| 2. Collect | Raw data from systems, surveys, sensors, APIs, open sources |
| 3. Clean and prepare | Data fit to analyse: typed, complete, consistent, deduplicated |
| 4. Transform | Joined, aggregated, derived fields, the right grain |
| 5. Analyse | Patterns, comparisons, correlations, models |
| 6. Visualise and communicate | A chart and a sentence somebody can act on |
| 7. Act and monitor | A decision, and a measurement of whether it worked |
A pipeline built without a question produces a dashboard nobody opens. The first step is naming the decision that will be made differently depending on what the data says. If no decision changes, there is nothing to analyse.
Step 1 of 7 1. Define the question — Without this, the output is a dashboard nobody opens.
Gathering and storing data
Methods of gathering
| Method | Produces | Watch out for |
|---|---|---|
| Operational systems | Transactions, orders, logs, bookings | Captures what the business does, not what customers wanted |
| Surveys and forms | Direct answers at scale | Self report error, leading questions, low response rates |
| Interviews and focus groups | Reasons behind the numbers | Small samples, interviewer effects |
| Observation and user testing | What people actually do | Costly, small, people behave differently when watched |
| Sensors and IoT | High volume, high velocity readings | Drift, failure, gaps, enormous volume |
| Web and app analytics | Behaviour, funnels, devices | Consent requirements, blocking, bot traffic |
| APIs | Live data from another system | Rate limits, changes without notice, terms of use |
| Open and purchased data | Scale and context you cannot generate | Licence terms, age, different definitions |
Where it is stored
| Store | What it is for |
|---|---|
| Relational database (OLTP) | Running the business: many small reads and writes, strict integrity |
| Data warehouse (OLAP) | Analysing the business: large reads over structured, modelled data |
| Data lake | Keeping everything, structured or not, in its raw form for later |
| Data mart | A subject-specific slice of a warehouse for one team |
| NoSQL store | Documents, key-values, graphs or wide columns, where a fixed schema does not fit |
| Cloud object storage | Cheap, durable storage of files at any scale |
| Spreadsheet | A model, a prototype or a small one-off. Not a database |
Schema on write and schema on read
A warehouse enforces the structure when data is written, so everything in it is clean and consistent and anything that does not fit is rejected. A lake accepts data as it arrives and applies structure when it is read, so nothing is lost but nothing is guaranteed either. The modern pattern is both: a lake for raw retention, a warehouse for the modelled data people actually query.
No concurrency control, no referential integrity, no audit trail, a row limit, and one person's accidental sort destroys the whole thing. Spreadsheets are superb modelling tools and dangerous systems of record.
- At the bottom, the sources: every system that generates data.
- The lake holds it raw, in whatever shape it arrived.
- Transformation cleans, types, joins and deduplicates it.
- The warehouse holds modelled data, ready to be queried.
- And only then can a dashboard be trusted by the people reading it.
Transformation, data quality and assurance
ETL and ELT
- ETL — Extract, Transform, Load. Clean and reshape the data on the way in, so only modelled data lands. Traditional warehouse practice.
- ELT — Extract, Load, Transform. Land the raw data first, transform it inside the warehouse. Cheaper compute makes this the common modern choice, and it keeps the raw data for re-processing.
What cleaning actually involves
| Problem | What to do about it |
|---|---|
| Missing values | Decide per field: drop the row, impute a value, or keep it as explicitly unknown. Record which |
| Duplicates | Define what makes two records the same, then deduplicate on that key |
| Inconsistent formats | Standardise dates to ISO, phone numbers, capitalisation, units |
| Inconsistent categories | "UK", "U.K.", "United Kingdom", "GB" are one category spelled four ways |
| Outliers | Investigate before removing. An outlier is sometimes the most important record |
| Wrong types | Declare types on import rather than letting the tool guess |
| Structural errors | Trailing spaces, mojibake, merged cells, header rows inside the data |
The dimensions of data quality
| Dimension | The question it answers |
|---|---|
| Accuracy | Does it match reality? |
| Completeness | Is anything missing that should be there? |
| Consistency | Do two systems agree? |
| Timeliness | Is it current enough for the decision? |
| Validity | Does it conform to the rules and the format? |
| Uniqueness | Is each real thing represented exactly once? |
| Relevance | Does it bear on the question being asked? |
Validation and verification
- Validation is automated and checks that data is reasonable: a range check, a type check, a format check, a presence check, a lookup against a list, a check digit.
- Verification checks that data is correct: double entry, a read-back, a visual check against the source document.
Validation will happily accept a perfectly formatted wrong answer. Verification is the only thing that catches it, and it needs a human or a second source.
Data assurance and governance
- A data dictionary: every field, its type, its format, its permitted values, its meaning
- Ownership: a named person accountable for each dataset
- An audit trail: who changed what, when
- Lineage: where each figure came from and what was done to it
- Documented cleaning: the decisions taken, so a result can be reproduced
"Removing the 14 rows with a missing postcode would have biased the result, because those orders were all from one warehouse whose system does not capture postcodes, so they were kept and flagged instead." That is data quality reasoning, and it is what the longer questions reward.
Key terms you must be able to define
- Data lifecycle
- The stages data passes through from creation to secure destruction.
- Data pipeline
- The sequence of steps that takes raw data through to a decision.
- ETL
- Extract, Transform, Load: data is cleaned and reshaped before it is loaded.
- ELT
- Extract, Load, Transform: raw data is loaded first and transformed in place.
- Data warehouse
- A store of structured, modelled data optimised for analysis.
- Data lake
- A store holding raw data of any structure for later processing.
- OLTP
- Online transaction processing: a system optimised for many small reads and writes.
- OLAP
- Online analytical processing: a system optimised for large analytical queries.
- Data cleaning
- Correcting or removing inaccurate, incomplete, duplicated or malformed data.
- Validation
- An automated check that data is of the right type, format or range.
- Verification
- A check that data is actually correct, usually against a second source.
- Data dictionary
- Documentation defining every field, its type, format, permitted values and meaning.
- Data lineage
- A record of where data came from and every transformation applied to it.
- Outlier
- A value far from the rest of the distribution.
Flashcards
Say the definition out loud before you flip the card. Retrieving it from memory is what builds recall, reading it again is not.
Reach the top grade on this topic
Top answers treat cleaning as a set of decisions with consequences, not a checklist. Say what you did about the missing values, why that choice rather than the alternative, and what bias the alternative would have introduced. That is the difference between describing a process and understanding it.
Mistakes that cost marks here
- Confusing validation with verification. Validation checks plausibility; verification checks truth.
- Deleting outliers automatically. Investigate first, because the outlier is sometimes the finding.
- Dropping every row with a missing value without checking whether the missing rows share a cause.
- Confusing a data warehouse with a data lake, or either with a database used to run the business.
- Treating a spreadsheet as a system of record.
- Starting with the data rather than with the question the data is supposed to answer.
Analysts consistently report spending the majority of their time finding, cleaning and reshaping data rather than analysing it. The modelling is the part that gets taught; the cleaning is the part that fills the week.
Knowledge check
Exam-style questions
Five written questions in the style of the real paper. Write a full answer, then let the marker check it against the mark scheme.
- LOne valid stage
- LA second, different stage
- LA third, different stage
Capture, storage, and secure destruction.
- LValidation defined as an automated plausibility check
- LA valid example of validation
- LVerification defined as a check that the data is actually correct
- LA valid example of verification
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.
- LProfile the data before changing anything
- LStandardise the date formats
- LDefine a key for identifying duplicates
- LDeduplicate, keeping the better record
- LHandle the missing emails deliberately rather than deleting rows
- LDocument the decisions, or validate the result
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.
- LDifferent definitions of the same measure
- LTiming or timeliness differences
- LDuplicates or missing records
- LDifferent sources or systems
- LManual handling introducing error
- LA single source of truth is proposed
- LGovernance measures are proposed
- LA judgement about the root cause
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.