How do you test and validate your data cleaning assumptions and rules?

The easiest way to verify data within Excel is to: - perform test calculations --> do errors occur due to wrong data format - produce simple one time statistics to perform simple cross checks. Use functions like COUNTIF, SUMIF etc. to get a feeling, if sample size is in scope and also detect other anomalies.

Testing and validating your data-cleaning assumptions and rules in Excel can be achieved as follows: Set Clear Criteria: Define cleaning rules in a separate worksheet or document. Sample Testing: Apply rules to a small data subset using functions like IF, AND, OR, and COUNTIF. Cross-Validation: Use VLOOKUP or INDEX-MATCH to compare cleaned data with a known dataset and apply conditional formatting to highlight discrepancies. Automate Checks: Enforce rules with Data Validation and use macros for repetitive tasks. Peer Review: Share the workbook with colleagues for review, using "Track Changes" to monitor modifications. Iterative Refinement: Continuously refine rules based on feedback and detected errors, maintaining a log of changes.

Data Validation: Assess whether the data conforms to predefined rules (e.g., range checks, format checks). It ensures data quality. Data Verification: Compare cleaned data against external sources or ground truth. Verification confirms the accuracy of data cleaning.

You always want to take the conservative approach when working with data. On the Sprocket app I always have to keep in mind which data is customer generated / externally sourced vs internally put together. Some data is useful to a point but its important to understand how it was collected and with what purpose in mind. The wrong data utilized in the wrong way will ultimately generate a garbage output. I find that it always helps to put yourself in the shoes of the humans on either end of the data journey and consider if it makes sense to you how it will be used. Sometimes the solution is to be very careful and change as little as possible, especially if you have trouble understanding exactly how it all works in order not to break things ??

Data validation can be performed by engineers like us, where we can check the competency of the dataset and each feature in it. Check for missing and incomplete values, and check if there are any erroneous values. However, the data verification job can be performed by domain specialists, especially when we consider critical and sensitive data like from the healthcare sector.

Pandas library in python provides two functionalities, namely, info() and describe() to get a quick overlook of the dataset. Missing values and different datatypes in the dataset can be found using the info() function, and the statistical profile of the data can be viewed using the describe() function.

Manual Inspection: Review a sample of cleaned data manually to identify discrepancies. Statistical Checks: Use summary statistics, histograms, or box plots to identify outliers or unexpected patterns. Domain-Specific Checks: Apply domain knowledge to validate data (e.g., checking age values for reasonableness).

External Datasets: Compare cleaned data with trusted external datasets. Unit Tests: Write automated tests to validate specific cleaning rules. Data Profiling Tools: Use tools that generate data profiles and highlight potential issues

The worst thing that can happen to data is any form of silent failure where your data is being corrupted without you knowing. Depending on how this occurs no amount of data cleaning may be able to restore or revert what you had. Therefore verifying how your data is being generated and stored in the first place is is paramount + critical too is who you select to hire to build the logic for generating the original data! ??

When setting up rules or expectations for how data should be handled, especially when it's business critical data, it is often useful to co-locate the instructions prominantly with the data in a human readable format ( or at the very least a descriptive link to the instructions ) Many corporations practice a fragmented chaotic storage of cross-organizational information. It may be impossible for a human to know there are instructions or rules to follow if they find the data itself first. So go low tech and be practical about it by placing what they need to know in the same place or atleast the summary there of with a link ??