Data quality: profiling, monitoring and testing

Why this matters

Every lesson in this course has touched data quality: a duplicate customer, a date read the wrong way, a report showing stale numbers. As a governance discipline, data quality turns those individual fixes into a system: agreed definitions of what "good" means, measurements of how good the data is, checks that run automatically, and a way of fixing problems at their source instead of patching them in every report.

The exam's objective is to implement quality assurance through profiling, monitoring and testing. This lesson covers the dimensions of data quality, profiling as a baseline, automated tests, monitoring over time, and managing quality issues through to their fix.

The lesson

The dimensions: accuracy, completeness, consistency, timeliness, validity, uniqueness

"Good data" is too vague to measure, so data quality is described along several dimensions. Six are widely used.

• Accuracy: values correctly describe the real world. A customer's address is where they actually live.
• Completeness: required values are present. The practice raw customer file is missing 20 of its 314 emails.
• Consistency: the same fact agrees across records and systems. A customer's region is the same in the customer table and the orders table.
• Timeliness: data is available and current enough for its use. A daily report has yesterday's data by the morning.
• Validity: values conform to their rules, formats and allowed values. A region is one of four; a date is a real date; a quantity is positive.
• Uniqueness: each real-world thing is recorded once. The practice raw file's 14 duplicate customers fail it.

The dimensions matter because they can be measured, turning quality into numbers such as "93.6% of customer records have an email" for the practice raw file, and because different uses need different dimensions. A marketing mailing needs complete, valid emails; a financial report needs accuracy and timeliness above all.

Profiling as a baseline for quality

Profiling, covered in domain 2 as the first look at a new data set, is also the foundation of ongoing quality management. A profile measures a data set on each dimension: missing values per column, distinct values and their frequencies, ranges, format conformance, key uniqueness.

Profiled once, it describes the data's current state. Recorded and repeated, it becomes a baseline: the normal level of each measure against which later profiles are compared. A column that is normally 2% empty and suddenly becomes 30% empty signals a problem upstream, even if 30% is not obviously wrong on its own.

Profiling also informs the rules. The four real regions, the valid range of quantities, the expected number of orders per day: each observation from a profile can become a quality rule that is then checked automatically.

Automated data quality tests in a pipeline

Quality checks are most effective when they run automatically, every time data moves, rather than when someone remembers.

A data quality test asserts something that must be true of the data and fails when it is not. Typical tests include:

• not null: a required column has no missing values;
• unique: a key column has no duplicates;
• accepted values: a column contains only allowed values;
• range: numeric values fall within limits;
• referential integrity: every foreign key matches a row in the related table;
• row count: a load contains a plausible number of rows compared with previous loads;
• freshness: the latest data is recent enough;
• reconciliation: a total matches a control total or a source system.

Tests run at the stages where problems enter: on ingestion, before bad data spreads; after transformation, to catch logic errors; and before publication, as a final gate. A failing test can stop a pipeline so bad data does not reach reports, or warn while letting data through, depending on how severe the failure is. Tests are written as code, kept in version control, and extended every time a new kind of problem is found, which makes them a growing record of what has gone wrong before.

Monitoring quality over time and alerting on drift

Tests catch rule violations. Monitoring watches for gradual change, including changes no rule anticipated.

Quality monitoring tracks measures over time: completeness rates, row counts, distinct counts, averages and distributions of key fields. Drift is a gradual or sudden shift away from the baseline: the share of orders from one channel slowly falling to zero because a tracking field stopped being populated, or the average order value jumping because prices were loaded in a different currency.

Monitoring is usually shown on a data quality dashboard, with scorecards for important data sets and trends for each measure, and alerts when a measure moves outside its expected range. As with any alerting, thresholds should reflect normal variation so that alerts mean something.

For machine learning, data drift, a change in the input data compared with the data a model was trained on, is a particular concern, because a model can keep producing confident predictions after its inputs have changed enough to make them wrong.

Quality issues: logging, ownership and fixing at the source

When a quality problem is found, it needs managing like any other issue.

• Log it: record the problem, where it was found, its scope and its impact, in an issue log or ticketing system, so it is visible and not rediscovered repeatedly.
• Assign an owner: usually the data steward or owner for the affected data, who is accountable for its resolution.
• Find the root cause: ask why the bad data exists, not just where it is. The duplicate customers in the practice raw file might come from a sign-up form that does not check for existing accounts.
• Fix at the source: correcting the form stops new duplicates; cleaning them in every report only hides them, and each report may clean them differently.
• Correct existing data where needed, and record what was changed.
• Prevent recurrence: add a test or monitor so the problem is caught if it returns.

This cycle, measure, find, fix at source, prevent, is the difference between a data quality programme and an endless series of workarounds, and it is the natural end point of everything this course has covered.