How to Measure Data Completeness: A Step-by-Step Guide

Like a guest who forgets to bring their share to a potluck, incomplete data can leave everyone dissatisfied and scrambling to fill the gaps.

One of the most basic but crucial indicators of data quality, data completeness, is defined as not-null values, but it can often go beyond not-null values.

In this step-by-step guide, we’ll explain the essential techniques and tools for measuring and improving the completeness of your data, ensuring you have all the information you need to make informed decisions.

What constitutes complete data?

If the value of a field is null, it’s clearly incomplete, but you also need to consider null proxies, meaning values like 000-000-000, N/A, none, not-defined, Not-applicable, NA, etc. Fields populated but with null proxies should most likely count just the same as null values, thus incomplete.

The standard unit of measurement is percentage (%)

Data completeness is measured as a percentage. For example, if a Customer table of 3M customers has 2.94M populated emails, then the completeness of emails is 2.94M/3M x 100 = 98%.

1. Create a list of fields that covers your analysis or business process

Sure, you could measure every single field for data completeness, but often just a few fields are critical. Just focus on those. For example, in financial reporting, missing data in key fields like transaction amounts or dates can lead to significant inaccuracies affecting both business decisions and compliance. Talk with your various stakeholders, including data users and business process owners, to understand their requirements and the impact of each data field on their operations.

2. Assess the current state

This is where you count up the null values and the null proxies. 

A null value check in SQL can be performed by using the IS NULL operator in a WHERE clause. Here’s an example: 

SELECT 

  [column1], [column2], ..., [columnN]

FROM 

WHERE [columnX] ISNULL;

To find null proxies, you need to extend your SQL query to include conditions that check for values like -1, 9999, "N/A", "Unknown", and empty strings.

SELECT 

  [column1], [column2], ..., [columnN]

FROM 

  [your_table_name]

WHERE 

  [columnX] IS NULL OR

  [columnX] = -1 OR

  [columnX] = 9999 OR

  [columnX] = 'N/A' OR

  [columnX] = 'Unknown' OR

  [columnX] = '';

Even better, you can use data profiling tools like Talend, Informatica, or even built-in features of database management systems to automate this.

Once they’re all identified, you’ve established a baseline for data completeness, which helps measure progress.

3. Define acceptable thresholds for completeness

Now, the question is, what are the minimum levels of completeness required for your data to be considered valid and useful for analysis and decision-making? For example, in a customer relationship management (CRM) system, you might set a threshold that requires at least 98% of customer records to have complete contact information.

Review historical data and base this number on your business requirements. Different projects may have varying tolerance levels for incomplete data. For example, a healthcare dataset may require near-perfect completeness due to the critical nature of the information, whereas a marketing dataset may tolerate slightly higher levels of missing data.

4. Continuously monitor completeness

Add checking for data completeness and assessing the resulting % against your thresholds to your data quality monitoring process.

While profiling tools can help, leveraging a comprehensive data observability platform like Telmai can take your data quality efforts to the next level. Telmai ensures data completeness by continuously monitoring and validating your data against predefined thresholds and offers a wide range of capabilities to enhance your data quality strategy.

Its user-friendly UI allows you to define what correct data should look like, and its machine-learning algorithms proactively monitor the data and alert you to deviations you may not have encountered. Ready to take your data quality to the next level? Try Telmai today.