Why Data Validation Is the First Step to Trustworthy Analytics and AI

Key takeaways

• The data validation process involves collecting all data and verifying its accuracy to ensure compliance, save time, and improve analytics-based decision-making.

• Data validation in Excel refers to restricting the content in cells to follow a specific formula or data type, such as a whole number or a decimal.

• Common data validation tools include Microsoft Excel and Google Sheets, as well as JavaScript.

Benefits of data validation include improved decision-making, reduced mistakes in AI models, and time and resource savings for businesses. Learn more about data validation, popular techniques, and who uses it.

Then, consider enrolling in the Google Data Analytics Professional Certificate. In as little as six months, you’ll have the opportunity to gain an immersive understanding of the practices and processes used by a junior or associate data analyst in their day-to-day job. Upon completion, add this shareable credential to your resume or LinkedIn profile.

What is data validation?

IBM defines data validation as "the process of verifying that data is clean, accurate, and ready for use” [1]. Without it, data could contain errors or misrepresentations, fall outside of bounds or other constraints, or fail to meet regulatory compliance standards. Imagine basing decisions or training artificial intelligence (AI) or machine learning models based on data that has questionable quality. Validation ensures optimal outcomes.

One data validation’s primary purposes includes ensuring that the data contains safe components to use, and another is to ensure that the data was fully collected for a project. The latter means that, for the purposes of collection, you obtain, verify, and review all data samples before sending them to the next stage of the analytical process.

The data validation process should occur throughout the entire lifecycle of the data, not just at the beginning. Data should consistently have checks for accuracy, cleanliness, and quality long after the initial validation.

Why is data validation important?

Data validation is important because it ensures data is accurate, consistent, and complete before it is used for analysis or AI model training. Validating data helps ensure correctness, consistency, and completeness before you analyze it, organize and store it, or use it for data storytelling. Discover reasons why data validation informs better decisions and prevents mistakes that artificial intelligence (AI) may make:

• Improves decision-making: Having access to high-quality, accurate data enables you to make more informed decisions, especially in a business setting. For example, say you surveyed 100 people to rate one of your products on a scale of one to 10, but the collected data had their ratings entered incorrectly, making it seem like everyone who ranked your product 10 actually ranked it a one. Without validating the data, you might deduce that the product wasn’t performing well. However, with data validation, you would discover the entry error and base your determination of the product’s success upon the correct information.

• Reduces AI mistakes: In the age of AI, it’s of the utmost importance that the data companies use is accurate. For instance, unvalidated data in an AI model can lead to misformation. Additionally, AI technology’s performance can be hindered by the data it’s fed, while high-quality data leads to better results from large language models (LLMs).

• Saves time: Imagine a sales environment where the database contains incorrect shipping and billing addresses. Without validation, those customers could have significant issues receiving their orders. Or, similarly, an e-commerce store that has an established data validation process saves time by ensuring all credit card information gets entered correctly, thereby reducing transaction errors that can stall the process.

• Ensures compliance: Another aspect of data validation is maintaining compliance for an organization. For example, if your business has compliance laws that require valid, high-quality data, failing to validate your data may constitute a compliance breach.

What is data validation in Excel?

According to Microsoft, data validation in Excel “[restricts] the type of data or the values that users enter into a cell, like a dropdown list” [2]. So, when you format a spreadsheet in Excel, you can determine which type of data belongs in each cell. Whether that be a decimal or a whole number, a date or a time, a certain amount of text, or a custom formula is up to you. Once you’ve set your restrictions, other spreadsheet users will receive an error message if the data they enter does not match the criteria you’ve predetermined.

Who uses data validation?

Data validation is primarily used by data engineers, data analysts, data scientists, and machine learning engineers to ensure data accuracy and integrity. Learn more about how data validation may apply to their responsibilities:

• Data engineers: Data engineers input data from one source to another. In this role, clean, consistent data ensures that the unloading process goes smoothly and doesn’t require reentering or revisiting. Data engineers also perform data validation as part of their routine duties.

• Data analysts: Data analysts create databases and data systems using extracted data. Part of this role may involve correcting invalid data to ensure accurate reports and analytical findings.

• Data scientists: Data scientists’ roles typically hinge on using valid, accurate data in their reports. Part of their job involves collecting data, which they may then validate and clean.

• Machine learning engineers: As machine learning engineers work to train AI models, they must ensure the models receive clean, valid data. Part of this role may involve checking data to ensure its consistency and accuracy.

What are the most common data validation techniques?

The six most common techniques are data type, format, code, consistency, range, and uniqueness checks. Explore each of the six common types of checks and how you use them to validate data:

Data type check

Data types refer to values entered in a cell, such as a number or a letter. If a cell only accepts letters, you want to ensure they do not contain numbers.

Format check

Formats typically apply to dates, but may also include email addresses, physical addresses, or phone numbers. If you format phone numbers using a +1 prior to the 10-digit phone number, you’d want to check that each phone number follows this same format.

Code check

Common code checks include zip codes, country codes, and ISBN (book) codes. In these instances, similar to a format check, you want to ensure they follow the same format consistently. If all area codes have five digits, you’d want to look out for those that contain more or fewer.

Consistency check

Consistency checks, or logical checks, refer to data flowing in a sensical way. For example, you’d enter a birth date earlier in a sheet than a death date. Or, you’d input a first payment due date before the final payment due date.

Range check

If a range in a cell should fall between 0 and 100, any data outside of that range, such as 130, would become invalid. Anything outside of the range needs correcting.

Uniqueness check

Uniqueness checks ensure that each cell contains unique, unrepeated data. For example, if you entered a birth date twice for one customer, this data would not qualify as unique and would require correcting.

What are some helpful data validation tools?

Data validation tools include spreadsheet software, such as Excel or Google Sheets, programming languages, such as JavaScript, and data integration processes. Learn more about each type of tool and when you might use it to validate data:

• Excel: Microsoft Excel offers a drop-down data validation menu that allows you to set rules for what users may type in specific cells. You can set limitations on number types, formats, text length, and formulas.

• Google Sheets: Similar to Excel, Google Sheets provides users with tools to set restrictions on cells to validate data. In Google Sheets, users have the option to manually specify the values to enter in spreadsheet cells, or they can predefine allowed cells.

• JavaScript: This programming language allows you to validate data in the browser, making validation quicker than with other languages that may require an external server.

• Data integration: IBM notes that the extract, transform, load (ETL) process can perform data validation [1]. ETL, as a process, ensures that data moves from one point to another without losing quality or consistency, and remains clean as it enters a new database.

What is the difference between data validation, data verification, and data cleaning?

Data validation checks data on entry, data cleaning corrects it afterward, and data verification can occur at any point to confirm accuracy, but most often as part of the data cleaning process.

In the order of operations, data validation typically occurs first when you collect and input data. Data cleaning occurs later, as you make corrections and adjustments to the collected data. Data verification plays a similar role to data validation; however, unlike data validation, it can occur at any time during the data lifecycle. More specifically, if data moves from one company to another, data verification would occur as a “check” of accuracy and integrity.

Read more: What Is Data Cleaning?

Best practices for effective data validation

When validating data, it’s important to perform processes that work for you and the specific data that you collect. To prevent potential challenges or obstacles in the data validation process, consider best practices for performing data validation:

• Perform data verification in addition to validation: As you initially enter data, you may ensure its validity, but if you merge it from one source to another, you need to verify correctness and accuracy. Initially, correct data may change or become invalid during a merge process, so you want to continue verifying and validating data throughout the data's lifecycle.

• Take your time: As you collect and input data, one way to prevent mistakes is to check your work as you go. Though you may take more time to input the data and check it upfront, you may save time later by not having to redo your work.

• Look out for duplicates: Duplicate values or cells may affect your data analysis or how you or a machine learning algorithm interprets it. Check for duplicates by running a uniqueness check or using a merge function in your spreadsheet or data entry platform.

Explore free digital resources

Subscribe to Career Chat on LinkedIn to get timely updates on popular skills, tools, and certifications. Then, discover free, digital resources designed to optimize your professional growth:

• Watch on YouTube: What Is Data Wrangling? The Key to Clean Data

• Find a course: Which Data Analysis Course Should You Take? Find Out in 1 Minute

• Learn from experts: Meet the Data Analyst Using His Creativity to Tell Visual Stories

With Coursera Plus, you can learn and earn credentials at your own pace from over 350 leading companies and universities. With a monthly or annual subscription, you’ll gain access to over 10,000 programs. Just check the course page to confirm your selection is included.