← Data Analytics from Scratch: SQL, Spreadsheets and Metrics
Lesson
Dirty data, and the question before the data
Learner can spot the four common data-quality problems and can sequence an analysis starting from a clear question rather than from the dataset.
Four data-quality problems — and the question-first discipline
Clean question, clean data — then analysis
Real-world data is almost never perfectly clean. Before you trust any result, you must check for four common problems.
1. Missing values: some cells are blank or null. A column of 1 000 rows where 200 are empty can distort averages, totals, and counts. You need to know whether data is missing at random or because of a systematic reason — the reason changes how you handle it.
2. Duplicates: the same event or record appears more than once. A customer order logged twice will double your revenue figure for that order. Duplicates are easy to miss and easy to create (e.g., from system imports running twice).
3. Outliers: values that are far outside the normal range. A salary dataset with values [30 000, 32 000, 35 000, 38 000, 40 000, 300 000] has a mean of about $79 000, but a median of $36 500 — the single outlier drags the mean far from the typical case. Outliers may be real (a CEO salary) or errors (a typo). Either way, they must be investigated before you report a mean.
4. Inconsistent units or formats: the same quantity recorded in different units in different rows (some distances in miles, others in kilometres; some dates as MM/DD/YYYY, others as DD/MM/YYYY). Mixing them produces nonsense results silently.
Beyond data quality, there is a deeper discipline: always define your analytical question before you look at the data. The temptation is to open a spreadsheet and start exploring whatever is there. The danger is that you end up answering the question the data makes easy, not the question the business actually needs. A good analyst writes the question first — "Which product lines drove the revenue drop in Q3?" — and then asks what data is needed to answer it. Starting from data and working backward to a question produces analysis that is hard to trust and easy to misuse.
Lesson notes
Clean question, clean data — then analysis
Real-world data is almost never perfectly clean. Before you trust any result, you must check for four common problems.
1. Missing values: some cells are blank or null. A column of 1 000 rows where 200 are empty can distort averages, totals, and counts. You need to know whether data is missing at random or because of a systematic reason — the reason changes how you handle it.
2. Duplicates: the same event or record appears more than once. A customer order logged twice will double your revenue figure for that order. Duplicates are easy to miss and easy to create (e.g., from system imports running twice).
3. Outliers: values that are far outside the normal range. A salary dataset with values [30 000, 32 000, 35 000, 38 000, 40 000, 300 000] has a mean of about $79 000, but a median of $36 500 — the single outlier drags the mean far from the typical case. Outliers may be real (a CEO salary) or errors (a typo). Either way, they must be investigated before you report a mean.
4. Inconsistent units or formats: the same quantity recorded in different units in different rows (some distances in miles, others in kilometres; some dates as MM/DD/YYYY, others as DD/MM/YYYY). Mixing them produces nonsense results silently.
Beyond data quality, there is a deeper discipline: always define your analytical question before you look at the data. The temptation is to open a spreadsheet and start exploring whatever is there. The danger is that you end up answering the question the data makes easy, not the question the business actually needs. A good analyst writes the question first — "Which product lines drove the revenue drop in Q3?" — and then asks what data is needed to answer it. Starting from data and working backward to a question produces analysis that is hard to trust and easy to misuse.