These two things kill data quality and trust in data for AI and BI

Thought:

Stop shipping incomplete data to the business

Working across many clients, I see this pattern repeatedly. It is pretty simple, but causes a lot of noise in the business and wasted time in data engineering.

Let’s look at a typical payment table in a typical model:

Typical model

So, what is the problem?

Well, we have NULLs in the data, null being the technical representation of an empty cell that has no information.

This leads to the two main problems that kill data quality.

Two issues:

First problem:

For the business that tries to analyse how payments are handled, it can’t segment all the data, as shown here, by the “Payment Type”.

They will get an answer where they can’t account for some of the records without having any information on why this is missing or how to interpret this.

The same is true for the Payment Amount. The business does not know if it should be 0 or if it is an error.

Second problem:

When the business tries to profile the customers, some of the customers will not link to any customer model. If they use SQL and an (inner) join, the payments for customers with no keys will be omitted, leading to wrong splits and totals. In systems that have better handling of missing keys, the business will still have a bucket of unhandled customers. Again, without any information about why this is or how to interpret this.

Both of these problems lead to the business not being able to directly trust the data. Furthermore, the business needs to engage with data engineering to work with the data.

How to fix this:

Details

How to handle the nulls:

For information fields like “Payment Type”, as a minimum, a “Missing value” should be used. The better option is to include information for the business like “Not provided by payment handler”.

For the Payment Amount, it will should be a data engineering task to determine if 0 is a correct business value. The better option is to ensure that values are always provided from the source system.

For the keys, if no customer key is found, the customer should be parked at a placeholder entry, like

-1 = “Unknown Customer”.

This can be expanded to include more levels of handling, like

-2 = “Protected Customer”,

-3 = “Missing Customer System Integration”.

If we look at a corrected result, the consumer of the data can now:

Know directly that the missing customer is due to the customer being protected (talk with the customer team)
The “Payment Type” is missing because the payment handler did not provide it (talk with the payment handler)
All amounts are numbers and can be assumed to be correct.

How to handle the nulls data models

Questions about what this means for your organization? Get in touch.

– Michael Faursby-Ahlers, CTO, Stretch Qonnect | Qloud Cover

TL;DR:

Problem:

1. Null in business-facing data kills trust in the data.
2. Lack of trust in data increases the workload on the data engineering team.

What to do:

1. Ensure that no models, tables, views, metrics, dimensions, etc. for consumption contain any nulls.
2. Ensure valid keys and placeholder keys.
3. Communicate as much relevant information about missing data to the business in the data.