Data Quality Remediation

In the previous chapter, we described how to set up data quality reporting, which allows you to easily identify bad data. This chapter moves on to correcting the data. As explained back in Chapter 1, this does not mean that the organization should aim for perfect data. The aim should be to get the data to the level where it no longer causes significant impediments to the organization achieving its goals.

This is often seen as the most challenging part of the data quality initiative. There is typically a major resource investment and a long lead time to make progress.

In spite of these challenges, this phase is also an exciting one. This is where the organization starts to see the tangible benefits that we attempted to estimate back in Chapter 3. As the bad data is replaced with correct data, the issues experienced prior to the initiative finally start to reduce in severity and impact.

Processes become more efficient, resource challenges driven by poor...

Overall remediation process

The overall process of remediation is typically cyclical in nature. It is usually not possible to work on all the issues uncovered by data quality reporting at the same time. The remediation is usually handled in tranches.

Our process for remediation has the following steps:

Figure 8.1 – End-to-end process of remediation

The following table provides a more detailed description of each step:

Prioritizing remediation activities

When you first run your data quality Rule Results Report (or your equivalent), it may be a little overwhelming. There will be failed records for every rule and sometimes the failed records may add up to many thousands. It is not uncommon in larger businesses for 250,000 or more records to fail a rule. For example, if a fast-moving consumer goods organization has a reward card scheme, it can easily have millions of customers. One of the largest of these schemes in the UK has 18 million customers. It would only take a single missing validation on an online enrollment form to generate large quantities of failed data as customers make mistakes when entering data. One organization we worked with required the date of birth of the customer, but did not validate what was entered. Around 1% of customers entered the correct day and month of birth but accidentally entered the current year instead of their birth year. The form was missing a simple validation...

Identifying the approach to remediation

Now that the priorities are understood, it is time to work on the approach to remediating the bad data. There are a number of different approaches that can be applied and the effort involved varies hugely.

Typically, each prioritized rule can be categorized into a particular approach. Most often, only one approach will apply to each issue. Sometimes there might be the possibility to apply two or more approaches to a particular issue.

For example, if supplier email addresses are missing in the ERP system to send remittance advice details, three approaches might apply:

1. The data might be in another system (for example, a contract management system) for 40% of the vendors who are missing the data. For these, the data would be migrated across to the ERP system in a batch.
2. The data might be available on previous supplier invoices for a further 40% of the vendors and could be collected and keyed in.
3. The data might have to be collected...

Moving remediation to business as usual

In cases where an automated or mass correction approach is applied, often it does not correct all of the data. There may be a difficult 20% of bad data that cannot be automatically matched and where a second approach has to be implemented. Often, difficult decisions need to be made on how far to go in correcting the data. For example, that last 20% might use a manual remediation approach such as 6 or 7. That might be so time-consuming that the cost of implementing it exceeds the benefit. In these situations, it may be most appropriate to apply the approach that gives 80% value and accept (temporarily at least!) the remaining data quality challenge. A “business as usual” remediation method could be applied for the remaining 20%.

To make this a bit clearer, here are further details on the real example in Table 8.5 where supplier bank details were missing:

• An organization’s ERP system found 65% of its suppliers were...

Understanding the effort and cost

Once the approach to each prioritized data quality issue has been identified, an approximate effort and cost estimate should be prepared, along with a timescale and plan for each issue.

• Sometimes it may be necessary to re-visit the prioritization at this point. If any of the issues will be exceptionally difficult to resolve, then it might be better to prioritize a different issue with a simpler resolution. This typically happens in the following situations:
• The approach selected is very manual and will consume more resources than are feasibly available

An approach involving a third party (that is, paying for correct data) is more expensive than initially anticipated

Momentum is important in data quality initiatives. If an issue is problematic, even where the priority is high, it can be better to move on to an issue that can be progressed efficiently.

In order to properly understand the effort and costs involved in remediating...

Governing remediation activities

Once the prioritization is complete, the approach has been identified, and the effort involved has been understood, the remediation activities begin. Just as with any other project-style activity, remediation must be governed.

Governance in this instance means the following:

• Tracking the remediation activities against the expected effort/elapsed time
• Reporting to senior leaders on the progress of the activity
• Understanding risks, issues, and “blockers” that need to be managed or mitigated
• Ensuring that when the project activity is done, ongoing work is transitioned into a business-as-usual process

When organizations start to remediate data quality issues for the first time, it has to be managed quite formally. This is simply because the organization has no established processes, best practices, and institutional knowledge in this area. Some organizations that I have worked with have decided to simply assign...

Tracking benefits

Remediation activities are very time-consuming and challenging. It is very common for data quality initiatives to be so focused on this activity that they do not manage stakeholders properly at this stage. The initiative has promised benefits in the business case stage (even if just qualitative benefits). The benefits may have been used to persuade leaders to take resources away from other work to be dedicated to remediation.

It is therefore often very important to start to show that the promised benefits have actually been delivered by the remediation. Where this is done well, you will see the following:

• Leaders encouraging you to continue on to the next process area or data domain
• Previously reluctant stakeholders asking for their area to be added to the roadmap
• Increased investment in related data activities – such as analytics – because the level of confidence in data increases
• Additional areas appointing data stewards/data...

Summary

Remediation has always been one of the most challenging parts of a data quality initiative. It can be incredibly difficult to get sufficient resources to make meaningful changes to data quality scores in a reasonably short period of time. This chapter has outlined how to ensure that the resources allocated are working on what the organization believes are the key priorities and that the approach taken is the most effective possible.

This chapter has outlined how to show stakeholders the progress being made and how to tie progress to the benefits that were agreed upon in the business case chapter.

With successful remediation, you will be asked to continue your data quality initiative into previously unsupportive business areas and deliver even more benefits.

To sustain business benefits, it is critical to make permanent changes to the way the organization manages its data and to embed further data quality improvement into the fabric of its business processes. The next...

The rest of the chapter is locked

Unlock this book and the full library FREE for 7 days

Get unlimited access to 7000+ expert-authored eBooks and videos courses covering every tech area you can think of

Renews at $15.99/month. Cancel anytime

Start free trial

Previous Chapter

Chapter 12 of 16

Next Chapter

You have been reading a chapter from

Practical Data Quality

Published in: Sep 2023Publisher: PacktISBN-13: 9781804610787

© 2023 Packt Publishing Limited All Rights Reserved

Register for a free Packt account to unlock a world of extra content!

A free Packt account unlocks extra newsletters, articles, discounted offers, and much more. Start advancing your knowledge today.

Sign up now

undefined

Unlock this book and the full library FREE for 7 days

Get unlimited access to 7000+ expert-authored eBooks and videos courses covering every tech area you can think of

Start free trial

Renews at $15.99/month. Cancel anytime

Author (1)

Robert Hawker

Robert Hawker started his career as a chartered accountant before making the leap into data in 2007. He led data teams within two global implementations of SAP, looking after master data management, data ownership and stewardship, metadata management, and, of course, data quality over a 14-year period. He moved into analytics in 2017 and now specializes in Microsoft Power BI training, implementation, administration, and governance work. He lives in the UK and shares his experiences through conference and blogs.
Read more about Robert Hawker

See other products by Robert Hawker

Personalised recommendations for you

Based on your interests and search pattern

Et al.

Ever wonder why speech recognition systems don't understand the Scottish accent, or what would happen if an astronaut only ate mac 'n' cheese, or other spurious reflections you'd have at a bar? We did, then collated those deliberations into absurd research articles with fake figures and methodologies inspired by even more fictionally absurd studies.

BookAug 2023230 pages5

Generative AI with LangChain

This book is a comprehensive introduction to LLMs and LangChain, demystifying the basic mechanics of LangChain, its functionalities, and the myriad of applications it can be integrated into.

BookDec 2023360 pages4

Generative AI with LangChain

This book is a comprehensive introduction to LLMs and LangChain, demystifying the basic mechanics of LangChain, its functionalities, and the myriad of applications it can be integrated into.

BookDec 2023360 pages5

Generative AI with LangChain

This book is a comprehensive introduction to LLMs and LangChain, demystifying the basic mechanics of LangChain, its functionalities, and the myriad of applications it can be integrated into.

BookDec 2023360 pages1

Generative AI with LangChain

This book is a comprehensive introduction to LLMs and LangChain, demystifying the basic mechanics of LangChain, its functionalities, and the myriad of applications it can be integrated into.

BookDec 2023360 pages5

Mastering Tableau 2023

This book is a comprehensive resource to mastering your Tableau skills and becoming a BI expert. As you progress, you will learn how to build advanced dashboards and improve your storytelling to derive key business insight, as well as make you well-versed with advanced functionalities of Tableau in the business intelligence domain.

BookAug 2023684 pages

Building AI Applications with ChatGPT APIs

This guide covers all ChatGPT API features for effortless creation of robust AI powered apps. With its help, you’ll be able to leverage ChatGPT’s cutting-edge NLP models to take your app development skills to the next level. You’ll also work on ten exciting projects that will give you the practical know-how that you can apply to your existing applications.

BookSep 2023258 pages5

Building AI Applications with ChatGPT APIs

This guide covers all ChatGPT API features for effortless creation of robust AI powered apps. With its help, you’ll be able to leverage ChatGPT’s cutting-edge NLP models to take your app development skills to the next level. You’ll also work on ten exciting projects that will give you the practical know-how that you can apply to your existing applications.

BookSep 2023258 pages2

Data Engineering with AWS

Embark on a journey to master data engineering pipelines on AWS! Our book offers a hands-on experience of AWS services for ingesting, transforming, and consuming data. Whether you're an absolute beginner or someone with basic data engineering experience, this guide is an indispensable resource.

BookOct 2023636 pages5

Modern Data Architecture on AWS

Every organization wants an agile, performant, and cost-effective data platform that meets all their current and future business needs. Purpose-built AWS analytics services and their features play a big part in building such a modern data platform. This book brings to you all the design and architectural patterns that’ll help you achieve this goal.

BookAug 2023420 pages5

Practical Guide to Applied Conformal Prediction in Python

Discover the power of Conformal Prediction with the "Practical Guide to Applied Conformal Prediction in Python." Master the latest techniques to quantify uncertainty in machine learning and computer vision models, and seamlessly apply them to your industry applications.

BookDec 2023240 pages

TinyML Cookbook

With over 70 project-based recipes, the TinyML Cookbook is a practical guide that will help you to get the most out of your microcontrollers. It provides a comprehensive understanding of the theoretical foundations while giving you hands-on experience training ML models for deployment on Arduino Nano 33 BLE Sense, Raspberry Pi Pico, and SparkFun RedBoard Artemis Nano microcontrollers.

BookNov 2023664 pages

GO TO TOP

Machine LearningKubernetesReactPythonCybersecurityUnity Game DevelopmentPower BIAzureWeb Development

• Machine Learning
• Kubernetes
• React
• Python
• Cybersecurity
• Unity Game Development
• Power BI
• Azure
• Web Development

Legal

Terms and ConditionsPrivacy PolicyCookie PolicyShipping PolicyReturn Policy

Support

HelpContact Us

Business

Partnerships

Sponsored eBooks

Custom eBooks

CareersBecome an author

Packt+ Membership

SubscriptionDataProSecPro

Packt Subscription Packt NewsLetter

Legal

Terms and Conditions

Privacy Policy

Cookie Policy

Shipping Policy

Return Policy

Support

Help

Contact Us

Business

Packt Subscription

Packt NewsLetter

---

Company Address: Packt Publishing Ltd, Grosvenor House, 11 St Paul's Square, Birmingham, B3 1RB© 2024 Packt Publishing Limited All Rights Reserved

Step Name	Description
Prioritize	Identification of the most important data quality failures so that these can be targeted early.
Identify the approach	There are a number of different ways to remediate data. Here are some examples: Manual record-by-record corrections Collection/upload of data from a third party Automatic...

Prioritizing remediation activities

When you first run your data quality Rule Results Report (or your equivalent), it may be a little overwhelming. There will be failed records for every rule and sometimes the failed records may add up to many thousands. It is not uncommon in larger businesses for 250,000 or more records to fail a rule. For example, if a fast-moving consumer goods organization has a reward card scheme, it can easily have millions of customers. One of the largest of these schemes in the UK has 18 million customers. It would only take a single missing validation on an online enrollment form to generate large quantities of failed data as customers make mistakes when entering data. One organization we worked with required the date of birth of the customer, but did not validate what was entered. Around 1% of customers entered the correct day and month of birth but accidentally entered the current year instead of their birth year. The form was missing a simple validation...

Identifying the approach to remediation

Now that the priorities are understood, it is time to work on the approach to remediating the bad data. There are a number of different approaches that can be applied and the effort involved varies hugely.

Typically, each prioritized rule can be categorized into a particular approach. Most often, only one approach will apply to each issue. Sometimes there might be the possibility to apply two or more approaches to a particular issue.

For example, if supplier email addresses are missing in the ERP system to send remittance advice details, three approaches might apply:

1. The data might be in another system (for example, a contract management system) for 40% of the vendors who are missing the data. For these, the data would be migrated across to the ERP system in a batch.
2. The data might be available on previous supplier invoices for a further 40% of the vendors and could be collected and keyed in.
3. The data might have to be collected...

Moving remediation to business as usual

In cases where an automated or mass correction approach is applied, often it does not correct all of the data. There may be a difficult 20% of bad data that cannot be automatically matched and where a second approach has to be implemented. Often, difficult decisions need to be made on how far to go in correcting the data. For example, that last 20% might use a manual remediation approach such as 6 or 7. That might be so time-consuming that the cost of implementing it exceeds the benefit. In these situations, it may be most appropriate to apply the approach that gives 80% value and accept (temporarily at least!) the remaining data quality challenge. A “business as usual” remediation method could be applied for the remaining 20%.

To make this a bit clearer, here are further details on the real example in Table 8.5 where supplier bank details were missing:

• An organization’s ERP system found 65% of its suppliers were...

Understanding the effort and cost

Once the approach to each prioritized data quality issue has been identified, an approximate effort and cost estimate should be prepared, along with a timescale and plan for each issue.

• Sometimes it may be necessary to re-visit the prioritization at this point. If any of the issues will be exceptionally difficult to resolve, then it might be better to prioritize a different issue with a simpler resolution. This typically happens in the following situations:
• The approach selected is very manual and will consume more resources than are feasibly available

An approach involving a third party (that is, paying for correct data) is more expensive than initially anticipated

Momentum is important in data quality initiatives. If an issue is problematic, even where the priority is high, it can be better to move on to an issue that can be progressed efficiently.

In order to properly understand the effort and costs involved in remediating...

Governing remediation activities

Once the prioritization is complete, the approach has been identified, and the effort involved has been understood, the remediation activities begin. Just as with any other project-style activity, remediation must be governed.

Governance in this instance means the following:

• Tracking the remediation activities against the expected effort/elapsed time
• Reporting to senior leaders on the progress of the activity
• Understanding risks, issues, and “blockers” that need to be managed or mitigated
• Ensuring that when the project activity is done, ongoing work is transitioned into a business-as-usual process

When organizations start to remediate data quality issues for the first time, it has to be managed quite formally. This is simply because the organization has no established processes, best practices, and institutional knowledge in this area. Some organizations that I have worked with have decided to simply assign...

Tracking benefits

Remediation activities are very time-consuming and challenging. It is very common for data quality initiatives to be so focused on this activity that they do not manage stakeholders properly at this stage. The initiative has promised benefits in the business case stage (even if just qualitative benefits). The benefits may have been used to persuade leaders to take resources away from other work to be dedicated to remediation.

It is therefore often very important to start to show that the promised benefits have actually been delivered by the remediation. Where this is done well, you will see the following:

• Leaders encouraging you to continue on to the next process area or data domain
• Previously reluctant stakeholders asking for their area to be added to the roadmap
• Increased investment in related data activities – such as analytics – because the level of confidence in data increases
• Additional areas appointing data stewards/data...

Summary

Remediation has always been one of the most challenging parts of a data quality initiative. It can be incredibly difficult to get sufficient resources to make meaningful changes to data quality scores in a reasonably short period of time. This chapter has outlined how to ensure that the resources allocated are working on what the organization believes are the key priorities and that the approach taken is the most effective possible.

This chapter has outlined how to show stakeholders the progress being made and how to tie progress to the benefits that were agreed upon in the business case chapter.

With successful remediation, you will be asked to continue your data quality initiative into previously unsupportive business areas and deliver even more benefits.

To sustain business benefits, it is critical to make permanent changes to the way the organization manages its data and to embed further data quality improvement into the fabric of its business processes. The next...