Data-Cleaning Verification Checklist

Author: Michael WIlson

This checklist will help ensure that data cleaning tasks have been properly completed and that the dataset is accurate, consistent, and ready for analysis.

1. Backup Data Before Cleaning

• Ensure a copy of the original dataset is backed up.
• Save the cleaned dataset as a separate file to maintain data integrity.

2. Check for Missing Data

• Identify missing values using tools like SQL’s IS NULL or Excel’s Conditional Formatting.
• Decide on a strategy: Remove, impute (mean, median, mode), or leave missing values based on the context.

3. Handle Duplicates

• Find and remove duplicates: Use DISTINCT or GROUP BY in SQL or the Remove Duplicates tool in Excel.
• Verify that removing duplicates doesn’t result in loss of meaningful data.

4. Validate Data Types

• Check data types for each column (numeric, text, date, etc.) and ensure they align with the expected format.
• Use functions like CAST() in SQL or data validation rules in Excel to enforce correct types.

5. Standardize Formats

• Ensure uniform formats for text fields (e.g., case consistency), date fields (e.g., YYYY-MM-DD), and numerical data (e.g., currency symbols, decimal points).

6. Remove Outliers (When Necessary)

• Identify outliers using statistical methods or visualization tools (e.g., box plots).
• Decide whether to remove or keep outliers based on the analysis context.

7. Handle Incorrect Data

• Detect invalid entries (e.g., phone numbers, email formats) using regular expressions or validation tools.
• Correct or remove invalid data.

8. Ensure Consistency Across Columns

• Cross-check related columns for logical consistency (e.g., start date should be earlier than end date).

9. Address Categorical Variables

• Ensure consistent categories in categorical columns (e.g., “Male” vs. “M”).
• Merge or standardize categories where needed.

10. Normalize and Scale Data (If Necessary)

• Normalize data for machine learning algorithms, particularly when using numeric fields (e.g., min-max scaling or z-score normalization).

11. Document Cleaning Process

• Document all steps taken during the cleaning process, including assumptions made, how missing data was handled, and how errors were corrected.
• Save the cleaning script (e.g., SQL queries or Python scripts) for reproducibility.

12. Perform Final Data Validation

• Run summary statistics (e.g., COUNT, SUM, AVG) to check the cleaned data for anomalies.
• Visually inspect the dataset using visualization tools (e.g., histograms, bar charts, scatter plots).

13. Test Data for Usability

• Check that the cleaned data aligns with your analysis goals.
• Test it by running a small analysis or generating preliminary reports to ensure readiness.

By following this checklist, you'll help ensure that your dataset is accurate, clean, and ready for analysis.

With a career spanning multiple industries, I'm currently immersed in a new and exciting chapter: pursuing my Google Data Analytics Certification. This course has fueled my passion for technology and data-driven decision-making, complementing my extensive experience as a Mortgage Loan Officer, where I specialized in helping clients achieve their homeownership dreams. For over a decade, I've also owned and operated a successful dog boarding and daycare business, providing exceptional care and fostering long-term relationships with clients and their pets.

This combination of analytical skills, client-focused service, and entrepreneurial experience equips me to tackle the world of data analytics with determination and a deep understanding of customer needs. I'm eager to apply these insights and newly acquired skills to solve complex business challenges in the ever-evolving data landscape.