To ensure the legitimacy of your business intelligence reports, you have to feed valid data to your analysis processes. This is why data validation has become an important concern, especially now that data is being captured and integrated from multiple, disparate data sources.

What is data validation?

Data validation checks whether your data values are appropriate for their context. Data values prove their appropriateness by passing a certain set of validation rules such as having the right data type or existing in an acceptable data format, range, etc.  

Examples of valid data include email addresses that contain an @ symbol, dates that exist in a given format (such as MM/DD/YYYY), residential addresses that contain valid postal codes, and so on.

It is important to understand here that data validation means checking whether the data values are valid for their context, but it does not include checking whether the data value in itself is the right or the correct value. This means that you can validate the email addresses of incoming leads by checking if there is an @ symbol present in the field, but you cannot verify whether the email address provided belongs to the person who filled out the form. However, data validation and accuracy are all parts of the bigger data cleansing process.

When to perform ?

It is best to target data validation at the point of data entry. But this may not be the ideal case as companies use a number of different data application systems for data capture and entry, and it may be difficult to have the same validation rules configured at each end.  

In addition to data entry, it is necessary to validate data prior to any ETL processes to ensure valid data is being fed to these processes and thus reducing “garbage-in, garbage-out” scenarios.

How to validate data?

You can validate incoming data at the point of data entry or validate existing data records by using data cleansing tools and applying validation rules. Depending on the business need your data fulfills, you can perform a number of methods to validate your data records. Some of them include:

1. Data presence: This validation method ensures that the required data is present in your data records and there are no empty or null data values.
2. Data type: This ensures that your data record values are present in the data type that is acceptable for that attribute. For example, First and Last Name values should not contain digits, Age should not contain letters, and so on.
3. Data format/pattern: This ensures that your data values follow the required validation pattern. For example, dates are present in the MM/DD/YYYY format, email addresses follow the [name]@[domain-name] pattern, and so on.
4. Data range: This validation method is mostly for validating date and number values by putting a lower and upper limit on the allowed values. For example, you can accept your customer age to be between 18-45 in your CRM, and so any number of entry that is less or more than this range, it will be considered invalid.
5. Data length: This ensures that the number of characters in a data field does not exceed a certain limit.
6. Data lookup: This means that your data must belong to a list of acceptable values, and so the data validity can be checked by looking up this value against the possible ones. For example, if a data field requires a day input, then there can only be 7 possible values for this such as Monday, Tuesday, and so on.

Automating the process

Many organizations appoint their in-house developers to manually code and run scripts and check the validity of their data records. Although this approach may allow you to design a data validation system that is specific to your data profiling and validation needs, automated software programs are on the rise because along with the flexibility of customization, they offer a complete system for data quality management lifecycle. For example, these software programs can allow you to import data from multiple sources, profile them to see the state of your data, design standardized validation rules, and apply them uniformly to all records. This one-go, automated workflow saves you a lot of time, cost, and effort. This is why it is becoming a popular choice among enterprise organizations.