Data Validation

Data Validation

Data validation is the process of checking data to determine whether it is accurate, complete, consistent,
reasonable, and suitable for the intended purpose.

After cleaning and transforming data, an analyst should never assume that the dataset is automatically correct.
The transformed dataset must be tested and validated before it is used for reporting, visualization, decision-making,
statistical analysis, or machine learning.

Learning Objectives

By the end of this lesson, you should be able to:

• Define data validation.
• Explain why data validation is important.
• Distinguish validation from data cleaning.
• Validate data types.
• Validate ranges and acceptable values.
• Validate required fields.
• Validate uniqueness.
• Validate relationships between fields.
• Identify invalid records.
• Use Excel tools for data validation.
• Use Python and pandas for validation checks.
• Design practical validation rules.
• Document validation results.
• Understand validation before publishing analytical reports.

1. What Is Data Validation?

Data validation checks whether data satisfies predefined rules or expectations.

For example, if student scores must be between 0 and 100, a value of 145 should be flagged as invalid.

Valid:
0–100

Invalid:
-5
105
145

Validation does not always mean that an invalid value should immediately be deleted.
It means that the value should be identified and investigated.

2. Why Data Validation Is Important

Bad data can produce bad analysis.
If incorrect records are allowed into a dataset, they can affect:

• Averages.
• Totals.
• Percentages.
• Charts.
• Dashboards.
• Forecasts.
• Statistical models.
• Business decisions.

Data validation provides a quality-control layer between raw data and analytical output.

3. Validation vs Data Cleaning

The two processes work together.

4. Types of Data Validation

Common validation categories include:

• Data type validation.
• Range validation.
• Format validation.
• Required-field validation.
• Uniqueness validation.
• Referential validation.
• Logical validation.
• Cross-field validation.
• Business-rule validation.
• Statistical validation.
• Completeness validation.
• Consistency validation.

5. Data Type Validation

A data type determines how a value should be interpreted.

Examples:

• Integer.
• Decimal.
• Text.
• Date.
• Time.
• Boolean.

If an amount column is supposed to contain numbers but contains text such as
“five thousand”, the data may fail numerical analysis.

6. Range Validation

Range validation checks whether numerical values fall within an acceptable range.

For example, student scores may require:

Minimum = 0
Maximum = 100

A validation rule can therefore identify:

Score = -10 → Invalid
Score = 50  → Valid
Score = 100 → Valid
Score = 150 → Invalid

7. Excel Range Validation

Excel Data Validation can restrict a cell to a specific numerical range.

For example, select the cells and use:

1. Data.
2. Data Validation.
3. Allow: Whole number or Decimal.
4. Set the minimum and maximum values.

This prevents many invalid entries from entering a worksheet in the first place.

8. Format Validation

Format validation checks whether values follow an expected structure.

Examples include:

• Email addresses.
• Phone numbers.
• Dates.
• Identification numbers.
• Student registration numbers.
• Product codes.

9. Email Validation

An email address normally contains a recognizable structure such as:

name@example.com

A validation process can flag values such as:

john@
john.example
@example.com

Format validation does not prove that the email address actually exists.
It only checks whether it follows the expected pattern.

10. Required Field Validation

Some fields should never be empty.

For example, a student registration record may require:

• Student ID.
• Student name.
• Course.
• Registration date.

If a required field is missing, the record should be flagged.

11. Completeness Validation

Completeness measures whether the required information is present.

For example, if a dataset contains 1,000 student records and 50 records have no registration date:

Missing registration dates = 50

Completeness =
950 / 1000 × 100

Completeness = 95%

This gives an analyst a measurable view of data quality.

12. Uniqueness Validation

Some fields should contain unique values.

Examples include:

• Student ID.
• Invoice number.
• Order ID.
• Transaction ID.
• Employee ID.

If the same Student ID appears twice when IDs are expected to be unique, the record should be investigated.

13. Excel Uniqueness Check

COUNTIF can be used to identify repeated values.

=COUNTIF($A$2:$A$100,A2)

If the result is greater than 1, the value appears more than once.

14. Python Uniqueness Check

duplicate_ids = df[
    df["student_id"].duplicated(keep=False)
]

print(duplicate_ids)

This identifies records whose Student ID occurs more than once.

15. Allowed Values Validation

Some fields should only contain values from a predefined list.

For example, completion status might allow:

Completed
In Progress
Not Started

A value such as “Finished” may be invalid if it is not part of the approved vocabulary.

16. Excel List Validation

Excel Data Validation can restrict a cell to a list.

For example:

Completed
In Progress
Not Started

This helps prevent inconsistent categories from being entered manually.

17. Logical Validation

Logical validation checks whether values make sense according to business rules.

For example:

Age = 5
Employment Status = Full-time employee

This combination may require investigation depending on the context.

Another example:

Payment Status = Paid
Amount Paid = ₦0

This may be logically inconsistent and should be reviewed.

18. Cross-Field Validation

Cross-field validation checks relationships between two or more columns.

For example:

Total = Quantity × Unit Price

If the stored total does not equal the calculated total, the record may contain an error.

19. Example of Cross-Field Validation

The second record requires investigation.

20. Referential Validation

Referential validation checks whether values correctly reference related records.

For example, suppose a transaction table contains:

customer_id

Every customer ID should normally correspond to an existing customer in the customer table.

If transaction ID 500 references customer ID 9999 but customer 9999 does not exist,
the relationship is invalid.

21. Business Rule Validation

Business rules describe how data should behave according to the organization.

Examples:

• A student cannot graduate before completing required courses.
• A product quantity cannot be negative.
• A transaction cannot have a negative sale amount unless refunds are explicitly represented that way.
• An employee termination date should not normally be earlier than the employment start date.
• A completed course should have a completion date.

22. Date Validation

Dates should be checked for:

• Valid date format.
• Valid calendar dates.
• Reasonable date ranges.
• Correct chronological order.
• Missing values.

For example:

Start Date: 2026-09-01
End Date:   2026-08-01

This may violate the expected chronological relationship.

23. Date Range Validation

A useful rule is:

End Date >= Start Date

In pandas:

invalid_dates = df[
    df["end_date"] < df["start_date"]
]

24. Statistical Validation

Statistical validation checks whether numerical values appear reasonable compared with the rest of the dataset.

Examples include checking:

• Minimum.
• Maximum.
• Mean.
• Median.
• Standard deviation.
• Quartiles.
• Outliers.

A sudden maximum that is dramatically larger than normal values may indicate an error or a legitimate unusual event.

25. Distribution Validation

Charts can help identify unexpected data patterns.

Useful charts include:

• Histogram.
• Box plot.
• Scatter plot.
• Line chart.
• Bar chart.

Visualization is therefore part of practical data validation.

26. Excel Validation Functions

Excel provides many functions useful for validation.

=ISNUMBER(A2)

=ISTEXT(A2)

=ISBLANK(A2)

=COUNTIF(A:A,A2)

=COUNTBLANK(A:A)

=AND(A2>=0,A2<=100)

=IF(AND(A2>=0,A2<=100),"Valid","Invalid")

27. Example: Student Score Validation in Excel

If the score is stored in A2:

=IF(
    AND(A2>=0,A2<=100),
    "Valid",
    "Invalid"
)

This creates a validation status.

28. Example: Required Field Validation

If B2 contains a student name:

=IF(
    TRIM(B2)="",
    "Missing",
    "Present"
)

This identifies blank or whitespace-only values.

29. Example: Duplicate Validation

=IF(
    COUNTIF($A$2:$A$100,A2)>1,
    "Duplicate",
    "Unique"
)

30. Conditional Formatting

Conditional formatting can make invalid records easy to identify visually.

For example, values outside the permitted score range can be highlighted.

This is useful when manually reviewing a dataset before analysis.

31. Python Validation with pandas

Python can automate validation checks.

import pandas as pd

df = pd.read_csv("students.csv")

print(df.info())

print(df.isna().sum())

print(df.describe(include="all"))

These commands provide an initial overview of the structure and quality of the dataset.

32. Validate Numerical Ranges in pandas

invalid_scores = df[
    (df["score"] < 0) |
    (df["score"] > 100)
]

print(invalid_scores)

This identifies scores outside the expected range.

33. Validate Allowed Categories in pandas

allowed_statuses = {
    "Completed",
    "In Progress",
    "Not Started"
}

invalid_status = df[
    ~df["status"].isin(allowed_statuses)
]

print(invalid_status)

34. Validate Missing Required Fields

required_columns = [
    "student_id",
    "student_name",
    "course"
]

missing_required = df[
    df[required_columns].isna().any(axis=1)
]

print(missing_required)

35. Validate Duplicate IDs

duplicate_ids = df[
    df["student_id"].duplicated(keep=False)
]

print(duplicate_ids)

36. Validate Data Types in pandas

print(df.dtypes)

You should verify that:

• Dates are dates.
• Amounts are numerical.
• IDs have the intended representation.
• Categorical values are consistent.

37. Validate Dates in pandas

df["registration_date"] = pd.to_datetime(
    df["registration_date"],
    errors="coerce"
)

invalid_dates = df[
    df["registration_date"].isna()
]

If a date cannot be interpreted, it becomes missing and can then be investigated.

38. Validation Reports

A professional data workflow should produce a validation report.

A validation report can contain:

• Total number of records.
• Total number of columns.
• Missing values.
• Duplicate records.
• Invalid categories.
• Invalid ranges.
• Invalid dates.
• Referential errors.
• Business-rule violations.
• Overall validation status.

39. Example Validation Summary

40. Pass, Fail, and Review

Not every validation result should simply be labelled valid or invalid.
A useful system may use three states:

• Pass: the record satisfies the rule.
• Fail: the record clearly violates the rule.
• Review: the record requires human investigation.

This is particularly useful when unusual values may be legitimate.

41. Validation Does Not Mean Automatic Deletion

One of the most important rules in data analysis is:

Do not automatically delete every record that fails a validation check.

A failed validation may represent:

• A data-entry error.
• A legitimate exception.
• A business event.
• A system problem.
• An incomplete record.
• An incorrect validation rule.

The analyst should investigate before deciding what action to take.

42. Validation Workflow

A professional validation workflow can be:

1. Understand the dataset.
2. Define quality requirements.
3. Define validation rules.
4. Run automated checks.
5. Identify failed records.
6. Investigate failures.
7. Correct or classify problems.
8. Run the checks again.
9. Document the results.
10. Approve the dataset for analysis.

43. Validation Rules Should Be Specific

A good validation rule should clearly describe what is expected.

Weak rule:

Check scores.

Better rule:

Student scores must be numeric
and must fall between 0 and 100.

Specific rules are easier to automate and audit.

44. Validation at Data Entry

The best time to prevent bad data is often before it enters the system.

Examples include:

• Dropdown lists.
• Required fields.
• Input masks.
• Minimum and maximum limits.
• Date restrictions.
• Unique identifiers.
• Format checks.

This is called preventive validation.

45. Validation After Data Collection

Validation should also happen after data has been collected or imported.

Imported files can contain errors even if the original collection system had validation rules.

This is why data analysts should validate datasets before analysis.

46. Preventive vs Detective Validation

47. Data Validation in a Dwallnet Student Dataset

Imagine Dwallnet has a student dataset with:

Student ID
Student Name
Course
Score
Registration Date
Payment Status
Amount Paid

Possible validation rules include:

• Student ID must be unique.
• Student Name cannot be blank.
• Course must belong to the approved course list.
• Score must be between 0 and 100.
• Registration Date must be a valid date.
• Payment Status must use approved values.
• Amount Paid must not be negative.
• Paid status should correspond to an appropriate payment amount.

48. Example: Payment Validation

Suppose the approved course fee is ₦150,000.
A simplified validation could identify:

Amount Paid < 0
→ Invalid

Amount Paid > 150,000
→ Review

Amount Paid = 150,000
→ Fully Paid

Actual business rules may be more complex when discounts, installments, refunds, or scholarships exist.

49. Validation and Outliers

An outlier is not automatically an invalid value.

For example, a sales transaction of ₦20,000,000 may be unusual but legitimate.

Validation asks whether the value violates a known rule.
Outlier analysis asks whether the value is unusually different from the rest of the data.

50. Validation and Data Consistency

Validation can also check whether related values use consistent representations.

For example:

Lagos
LAGOS
lagos

If the approved representation is "Lagos", other forms should be flagged or standardized.

51. Validation and Data Transformation

After transformation, validation should confirm that the transformation produced the expected result.

For example, if:

sales_value = quantity × unit_price

the analyst should verify that the calculated values are correct.

52. Automated Validation

For repeated analytical workflows, validation checks should be automated whenever possible.

Automation provides:

• Consistency.
• Speed.
• Repeatability.
• Reduced manual error.
• Better documentation.

53. Example Automated Validation Function

def validate_scores(df):
    invalid = df[
        (df["score"] < 0) |
        (df["score"] > 100)
    ]

    return invalid

The function can be reused whenever a similar dataset is processed.

54. Example Validation Function for Required Fields

def validate_required_fields(df, columns):
    return df[
        df[columns].isna().any(axis=1)
    ]

This makes validation reusable across multiple projects.

55. Validation Before Dashboard Publishing

Before publishing a dashboard, validate the underlying dataset.

Ask:

• Are totals correct?
• Are dates current and valid?
• Are categories standardized?
• Are duplicates handled?
• Are missing values understood?
• Are calculations correct?
• Are filters working correctly?
• Are unusual values explained?

A beautiful dashboard built from invalid data is still an unreliable dashboard.

56. Validation and Data Governance

In professional organizations, validation is part of data governance.

Data governance defines responsibilities, standards, rules, and processes for managing data.

Validation helps organizations maintain trustworthy data over time.

57. Validation Checklist

• Check column names.
• Check data types.
• Check required fields.
• Check missing values.
• Check duplicate identifiers.
• Check allowed categories.
• Check numerical ranges.
• Check date ranges.
• Check cross-field relationships.
• Check referential relationships.
• Check outliers.
• Check business rules.
• Check totals and calculations.
• Review failed records.
• Document validation results.

58. Practical Exercise

Create a student dataset containing at least 30 records with:

• Student ID.
• Student Name.
• Course.
• Score.
• Registration Date.
• Payment Status.
• Amount Paid.

Intentionally introduce several problems:

• A duplicate Student ID.
• A blank student name.
• A score greater than 100.
• A negative score.
• An invalid course name.
• An invalid date.
• A negative payment amount.
• An invalid payment status.

Then:

1. Create validation rules for each field.
2. Use Excel to identify invalid records.
3. Create a validation status column.
4. Repeat the exercise using pandas.
5. Count the number of failed records.
6. Investigate each failure.
7. Correct legitimate errors.
8. Run validation again.
9. Prepare a short validation report.

59. Practical Python Validation Example

import pandas as pd

df = pd.read_csv("students.csv")

allowed_courses = {
    "Data Analysis",
    "Web Development",
    "Cyber Security",
    "Web Design"
}

allowed_statuses = {
    "Paid",
    "Part Payment",
    "Pending"
}

invalid_scores = df[
    (df["score"] < 0) |
    (df["score"] > 100)
]

invalid_courses = df[
    ~df["course"].isin(allowed_courses)
]

invalid_statuses = df[
    ~df["payment_status"].isin(allowed_statuses)
]

missing_required = df[
    df[
        ["student_id", "student_name", "course"]
    ].isna().any(axis=1)
]

duplicate_ids = df[
    df["student_id"].duplicated(keep=False)
]

negative_payments = df[
    df["amount_paid"] < 0
]

print("Invalid scores:", len(invalid_scores))
print("Invalid courses:", len(invalid_courses))
print("Invalid statuses:", len(invalid_statuses))
print("Missing required fields:", len(missing_required))
print("Duplicate IDs:", len(duplicate_ids))
print("Negative payments:", len(negative_payments))

60. Knowledge Check

1. What is data validation?
2. Why is validation important before analysis?
3. What is the difference between cleaning and validation?
4. What is range validation?
5. What is uniqueness validation?
6. What is a required field?
7. What is format validation?
8. What is referential validation?
9. What is a business-rule validation?
10. Why should invalid records not automatically be deleted?
11. What is preventive validation?
12. What is detective validation?
13. How can Excel Data Validation help analysts?
14. How can pandas be used for validation?
15. Why should validation be repeated after correcting errors?
16. Why is validation important before publishing a dashboard?

61. Key Terms

• Data Validation: checking data against defined quality rules.
• Range Validation: checking whether values fall within permitted limits.
• Uniqueness: checking whether values that should be unique are actually unique.
• Format Validation: checking whether values follow an expected structure.
• Completeness: checking whether required information is present.
• Referential Validation: checking relationships between related datasets.
• Business Rule: a condition that data must satisfy according to organizational requirements.
• Preventive Validation: stopping invalid data before it enters a system.
• Detective Validation: identifying invalid data after it has entered a system.
• Validation Report: a summary of data-quality checks and their results.

62. Key Takeaways

• Data validation checks whether data meets defined quality requirements.
• Validation should happen after cleaning and transformation.
• Data types, ranges, formats, required fields, uniqueness, and relationships can all be validated.
• Business rules are important for meaningful validation.
• Excel provides Data Validation and many formulas for practical checks.
• Python and pandas can automate validation processes.
• An unusual value is not automatically an error.
• Failed validation records should be investigated rather than blindly deleted.
• Validation should be repeated after corrections.
• Validation results should be documented.
• A dashboard or report is only as trustworthy as the data behind it.

63. Lesson Summary

Data validation is one of the most important quality-control stages in data analysis.
It checks whether a dataset is accurate, complete, consistent, reasonable, and suitable for its intended purpose.

Validation can check numerical ranges, data types, formats, missing values, duplicate identifiers,
allowed categories, dates, relationships between fields, business rules, and statistical patterns.

Excel can be used for interactive validation through Data Validation, formulas, filters, and conditional formatting.
Python and pandas can automate more complex and repeatable validation processes.

A professional analyst should investigate failed validation checks, correct genuine errors,
document decisions, and rerun validation before approving a dataset for analysis.

Next Lesson

The next lesson is Preparing Analysis-Ready Datasets, where we will bring together the cleaning,
transformation, and validation techniques learned throughout this module to produce a final dataset that is ready
for analysis, visualization, dashboards, and reporting.