Data Profiling and Quality Assessment

Introduction

Data preparation begins with an empirical question: what is actually present in the dataset? A data dictionary, database schema, or project specification describes what data are intended to represent, but profiling reveals what they contain in practice. Data profiling is the systematic examination of structure, content, relationships, distributions, and metadata. Data quality assessment extends profiling by judging whether the observed data are fit for a defined analytical or operational purpose.

This distinction is important. A dataset is not inherently “high quality” or “low quality.” Quality is contextual. A customer address may be adequate for regional marketing but inadequate for parcel delivery. A transaction timestamp may be accurate enough for monthly reporting but insufficient for fraud detection requiring millisecond ordering. Consequently, quality assessment must connect measurable properties to business rules, analytical requirements, risks, and acceptance thresholds.

For data scientists, profiling is an exploratory and diagnostic activity. It supports schema interpretation, anomaly detection, transformation design, feature engineering, model-risk management, and reproducible data pipelines. For managers and data owners, it provides evidence for prioritising remediation and deciding whether a dataset can be released, integrated, or used for a high-impact decision.

Learning Objectives

After studying this chapter, you should be able to:

• explain the purpose and scope of data profiling;
• interpret schemas, metadata, distributions, and inter-column relationships;
• distinguish accuracy, completeness, consistency, validity, timeliness, and uniqueness;
• design validation rules and select suitable quality metrics;
• identify anomalies without confusing unusual observations with errors;
• construct quality scorecards and acceptance thresholds;
• evaluate data quality in an applied organisational context; and
• discuss governance, ethical, and operational implications of poor-quality data.

1. The Scope of Data Profiling

A profile is an evidence-based description of a dataset. It commonly includes:

1. Structural information: tables, files, columns, data types, keys, constraints, and relationships.
2. Metadata: definitions, ownership, source systems, units, permissible values, lineage, collection method, and update frequency.
3. Content summaries: row counts, null counts, distinct-value counts, minimum and maximum values, quantiles, frequencies, and patterns.
4. Relationship information: correlations, dependencies, referential integrity, duplicates, and cross-field logic.
5. Quality findings: rule violations, anomalies, severity, affected records, and remediation status.

Profiling may be performed at several levels:

• Table or file level: number of records, file size, extract date, and partition coverage.
• Column level: type, missingness, cardinality, distribution, format, and outliers.
• Record level: invalid or anomalous individual rows.
• Cross-column level: logical relationships such as start_date ≤ end_date.
• Cross-table level: foreign-key coverage and reconciliation with source systems.
• Temporal level: changes in volume, distributions, and quality over time.

A useful profile distinguishes observations from interpretations. For example, “7.2% of values in income are missing” is an observation. “The missing values are caused by low-income applicants avoiding disclosure” is an interpretation that requires further evidence.

2. Schema and Metadata as Quality Foundations

2.1 Schema profiling

A schema specifies the intended organisation of data. In a relational table, it may define column names, data types, nullability, primary keys, foreign keys, and permitted constraints. In a semi-structured source, schema profiling must also examine nesting, optional fields, arrays, and variation between records.

A schema profile should answer questions such as:

• Are column names stable and meaningful?
• Are numeric values stored as numeric rather than text?
• Are dates represented consistently, including time zone and precision?
• Which columns are mandatory?
• Is the declared primary key actually unique and non-null?
• Do foreign-key values match records in the referenced table?
• Have fields appeared, disappeared, or changed type since the previous release?

Schema drift is a change in structure over time. It may be intentional, such as an approved product redesign, or accidental, such as a source application exporting a date field as text. A robust pipeline detects schema drift before downstream transformations silently fail.

2.2 Metadata profiling

Metadata gives meaning to values. Important metadata includes:

Metadata itself can be incomplete or contradictory. A field labelled “age” may contain date of birth, years, or age bands. Such semantic uncertainty is a quality issue even when every cell is syntactically valid.

2.3 Data contracts

A data contract is an explicit agreement between a data producer and consumer. It normally specifies schema, definitions, accepted ranges, delivery schedule, quality rules, change-management procedures, and escalation responsibilities. Contracts convert informal expectations into testable conditions and are particularly valuable in distributed data platforms.

3. Distributional and Relational Profiling

3.1 Univariate summaries

For a numeric variable, useful summaries include count, missing count, minimum, maximum, mean, median, standard deviation, quantiles, and selected percentiles. The arithmetic mean of observations x₁, x₂, …, xₙ is:

Here, n is the number of non-missing observations and xᵢ is observation i. The formula assumes that the observations are numeric and that an arithmetic average is meaningful for the measurement scale. It should not be applied indiscriminately to categorical labels.

The sample standard deviation is:

The denominator n − 1 is used when s estimates population variability from a sample under conventional statistical assumptions. Profiling is not necessarily inferential, but this measure helps identify unusual spread and changes over time.

For categorical variables, frequency tables, proportions, mode, and distinct count are more informative. For a category c, its observed proportion is:

where count(c) is the number of non-missing records in category c and n is the number of non-missing records used in the denominator. The denominator must be stated because profiles may use either all rows or only populated rows.

3.2 Distributional interpretation

A distribution can reveal truncation, impossible values, unit changes, coding errors, and population shifts. For example, a height variable with values concentrated near 170 may be plausible, whereas values ranging from 1 to 2,000 may indicate mixed units or misplaced decimal points. A sudden increase in nulls after a system release may indicate an extraction failure rather than a change in customer behaviour.

Quantiles are often more robust than means in the presence of extreme values. The interquartile range is:

where Q₁ is the first quartile and Q₃ is the third quartile. A common exploratory outlier flag is a value below Q₁ − 1.5 × IQR or above Q₃ + 1.5 × IQR. This is a screening convention, not proof of error. It is less suitable for highly skewed, bounded, multimodal, or very small datasets without contextual review.

3.3 Multivariate and relational profiling

Single-column checks can miss contradictions. Examples include:

• delivery_date earlier than order_date;
• status = Cancelled while cancellation_date is null;
• a child record referencing a nonexistent customer;
• a total that does not equal the sum of component amounts;
• duplicate customer identifiers associated with incompatible names;
• a change in one field’s distribution conditional on another field.

Correlation can assist exploration but does not establish data correctness or causation. A strong correlation may be produced by duplicated fields, common time trends, or leakage from a target variable. Relational checks based on domain rules are generally stronger evidence of quality problems than correlation alone.

4. Dimensions of Data Quality

Data quality dimensions are lenses for assessment. Organisations may define them differently, so each dimension should be operationalised with explicit rules.

4.1 Accuracy

Accuracy is the degree to which a value correctly represents the real-world entity or event. Assessing accuracy usually requires a trusted reference, an authoritative source, reconciliation, or independent verification. A syntactically valid telephone number may still belong to the wrong person.

4.2 Completeness

Completeness concerns the presence of required data. It may be measured at field, record, table, or population level. A basic field completeness rate is:

The denominator must identify the relevant population. If a field is required only for a subset of records, counting all records would produce a misleading result. Completeness can also involve coverage: whether all expected entities, periods, or source files are represented.

4.3 Consistency

Consistency means that related values agree with one another, across fields, records, systems, or time. Examples include identical customer identifiers across systems, totals reconciling to transactions, and a stable coding convention. Consistency does not necessarily imply accuracy: two systems may contain the same incorrect address.

4.4 Validity

Validity is conformance to a defined type, format, domain, range, or business rule. A valid email pattern, a date in ISO format, a positive quantity, and a status from an approved list are examples. Validity checks must reflect the domain: a regular expression can test a pattern but cannot establish that an email inbox exists.

4.5 Timeliness and currency

Timeliness is whether data are available when required; currency concerns how recently values reflect the relevant reality. A dataset delivered one day late may be acceptable for quarterly analysis but unacceptable for real-time intervention. Timeliness should therefore be measured against a service-level objective, not an arbitrary universal number.

A simple delivery timeliness rate is:

4.6 Uniqueness

Uniqueness means that an entity or event is represented once within the intended grain. Duplicate rows may be exact duplicates, repeated events, or legitimate multiple records. The correct key must be defined before duplicates are removed. A customer may have many orders; therefore, customer_id is not unique in an order table.

A duplicate rate based on a declared key can be expressed as:

The definition of “duplicate record” must be documented, particularly when multiple rows share a key but differ in valid version, timestamp, or status.

5. Validation Rules and Anomaly Detection

5.1 Rule categories

A mature validation framework combines several rule types:

Rules should have an owner, severity, execution frequency, threshold, exception process, and evidence trail. A failed rule should produce actionable information: rule identifier, affected records, source, run time, and recommended disposition.

5.2 Anomalies are not automatically errors

An anomaly is an observation that differs from an expected pattern. It may represent an error, fraud, a rare but genuine event, a new population, or a legitimate process change. Automated detection should therefore prioritise records for investigation rather than automatically deleting them.

Useful approaches include:

• robust univariate limits using quantiles or median-based measures;
• time-series comparisons against historical volumes and seasonality;
• peer-group comparison, such as transactions relative to similar branches;
• clustering or density methods for unusual multivariate combinations;
• change-point detection for abrupt distributional shifts; and
• rule-based domain checks.

Each method has limitations. Statistical flags can encode historical bias, miss novel errors, and over-flag minority groups. A threshold based on historical data may preserve a systematic error. Human review and domain knowledge remain important.

6. Quality Metrics, Scorecards, and Acceptance Thresholds

6.1 From metrics to decisions

A quality metric quantifies one aspect of performance. A scorecard organises multiple metrics by dataset, domain, owner, severity, and time. A threshold states the condition for acceptance, warning, quarantine, or remediation.

Consider a dataset with 10,000 records. If 250 required values are missing, completeness is:

If the acceptance threshold is at least 98%, the dataset fails that criterion, even if its other dimensions pass. A scorecard should not conceal a critical failure by averaging it with less important measures.

6.2 Weighted quality scores

Where an overall indicator is useful, a weighted score may be calculated as:

where qⱼ is the normalised score for dimension j, wⱼ is its weight, m is the number of dimensions, wⱼ ≥ 0, and ∑wⱼ = 1. The score is meaningful only when component scores are placed on a comparable scale and the organisation accepts the trade-offs implied by the weights.

For example, suppose completeness is 0.975 with weight 0.30, validity is 0.99 with weight 0.25, timeliness is 1.00 with weight 0.20, consistency is 0.98 with weight 0.15, and uniqueness is 0.995 with weight 0.10. Then:

Thus the weighted score is 98.625%. However, if missing customer identifiers are classified as a critical failure, the dataset may still be rejected despite this high aggregate score. Weighted scores support prioritisation; they should not override non-negotiable controls.

6.3 Setting thresholds

Thresholds should be based on:

• analytical tolerance and model sensitivity;
• legal, regulatory, and contractual obligations;
• historical capability and process variation;
• business impact of false acceptance and false rejection;
• population coverage and sampling uncertainty; and
• the cost and feasibility of remediation.

Thresholds may be hard gates, warning bands, or statistical control limits. They should be reviewed when the business process, population, or use case changes. A threshold set only because it is convenient can institutionalise poor quality.

7. An End-to-End Profiling Workflow

A defensible workflow is iterative:

1. Define purpose and grain. Identify the decision, population, unit of observation, and acceptable risk.
2. Inventory sources. Record datasets, owners, lineage, refresh schedules, access restrictions, and known limitations.
3. Capture the declared schema. Record types, keys, constraints, units, and definitions.
4. Run an exploratory profile. Examine counts, nulls, distinct values, distributions, patterns, and sample records.
5. Compare declared and observed structure. Detect schema drift, unexpected fields, type changes, and missing partitions.
6. Specify validation rules. Combine structural, domain, relational, temporal, and reconciliation checks.
7. Investigate anomalies. Segment by time, source, geography, process stage, or user; retain evidence and distinguish error from legitimate rarity.
8. Quantify quality. Calculate dimension-specific metrics and document denominators and exclusions.
9. Apply thresholds and decide. Release, release with warnings, quarantine, or remediate.
10. Monitor continuously. Compare current results with historical baselines and communicate trends to owners.

Profiling should be reproducible. The profile code, rule definitions, reference data, execution timestamp, software version, and source snapshot should be retained where practical. This supports auditability and prevents analysts from making decisions from an unrecorded one-time inspection.

8. Applied Case: Illustrative Retail Orders Dataset

Background

The following is a fictional illustrative case. A retailer receives a daily order extract for forecasting and operational reporting. The declared grain is one row per order line. Required fields include order_id, customer_id, order_date, quantity, unit_price, currency, and status.

Problem

The forecasting team reports unstable daily sales totals. Operations also observes that some cancelled orders appear in fulfilment reports. The data team must decide whether the extract can be loaded into the analytical warehouse.

Analysis

The profile contains 2,000,000 rows. The team finds:

• 1,200 rows have null order_id values;
• 3,000 rows repeat an order_id and line identifier combination;
• 4,000 rows have quantity ≤ 0;
• 6,000 rows contain currencies outside the approved reference list;
• 8,000 rows have ship_date earlier than order_date;
• 15,000 rows have status = Cancelled but a non-null fulfilment date; and
• the extract contains 1,950,000 rows although the source control total reports 2,000,000 expected rows.

The last finding is a coverage and completeness problem, not simply a row-level null problem. Investigation by source partition shows that one regional system failed during extraction. The currency issue is concentrated in the same region, suggesting a source or mapping failure rather than random corruption.

Approach and solution

The team defines hard gates for non-null order-line keys, source control-total reconciliation, and approved currencies. Negative or zero quantities are not automatically deleted because returns may be represented by negative quantities; instead, the model distinguishes valid return codes from invalid negative sales quantities. Date sequencing is flagged for investigation because time-zone conversion may explain a subset.

The team quarantines the incomplete regional partition, repairs the currency mapping using the approved reference table, and requests a corrected extract. It also creates a rule that cancelled orders cannot enter the fulfilment fact table unless an approved exception code exists. Duplicate keys are investigated before deduplication, because repeated lines may represent legitimate versioned updates.

Outcome

The corrected extract reconciles to the source control total. The remaining date exceptions are documented as time-zone conversions, while genuine sequencing errors are returned to the source team. Forecasting becomes more stable because the incomplete region is no longer silently treated as zero activity. The scorecard reports separate metrics for completeness, validity, consistency, and reconciliation rather than hiding critical failures inside one average score.

Learning points

The case demonstrates that data quality is multi-dimensional. A dataset can have acceptable field-level completeness while missing an entire regional partition. It also shows why business meaning must guide validation: a negative quantity can be an error or a valid return. Finally, quarantine, lineage, exception handling, and source accountability are as important as profiling algorithms.

9. Governance, Ethics, and Regulatory Considerations

Poor-quality data can produce discriminatory, unsafe, or unlawful outcomes. Missingness may be concentrated among vulnerable groups; inaccurate identity matching may deny services; and historical errors may be amplified by automated models. Quality assessment should therefore examine subgroup differences, not only overall averages.

Data minimisation and purpose limitation remain relevant. Profiling should not expose sensitive attributes unnecessarily. Access to raw values should be restricted, and profiles should use masking or aggregation where possible. Personal data may be subject to obligations concerning accuracy, correction, retention, security, provenance, and auditability, depending on jurisdiction and sector.

Anomaly detection also raises governance questions. A high-risk flag should support review rather than become an unexplained automatic decision. Organisations should document the reason for rules, the evidence used, the authority responsible for overrides, and the process for correcting records. Quality ownership should be shared among data producers, custodians, stewards, analysts, and accountable business owners.

10. Technology and Contemporary Practice

Modern data platforms commonly implement profiling in automated pipelines. Data observability systems monitor freshness, volume, schema, distribution, and lineage. Metadata catalogues connect technical columns with business definitions and ownership. Great care is needed, however: automation can detect that a distribution changed but may not explain whether the change is a business event or an extraction defect.

Machine learning can assist anomaly prioritisation, entity resolution, and drift detection. Yet model-based quality checks introduce their own risks: opaque thresholds, feedback loops, biased historical baselines, and instability under population change. A strong architecture combines automated checks with versioned rules, reference data, human review, and explicit escalation.

Data quality is increasingly treated as a product capability. Teams publish data products with contracts, service-level objectives, documentation, tests, and incident management. This approach shifts quality from a final cleansing exercise to a continuous engineering and governance responsibility.

Challenges and Limitations

First, accuracy is difficult to measure without a trusted reference. Second, missingness may be informative rather than random, so imputation can change the meaning of a dataset. Third, duplicate removal can destroy legitimate history when the grain is misunderstood. Fourth, thresholds can create false confidence when the sample is small or the population changes. Fifth, quality dimensions can conflict: aggressive standardisation may improve consistency while reducing fidelity to source values. Finally, quality assessment has a cost. Organisations should prioritise controls according to decision impact, regulatory exposure, and operational risk rather than attempting to validate every possible property equally.

Best-Practice Checklist

• Define the intended use, population, grain, and risk before profiling.
• Combine schema, metadata, distributional, relational, and temporal checks.
• Document metric denominators, exclusions, units, and assumptions.
• Use authoritative reference data for accuracy and validity where available.
• Treat anomalies as investigation candidates, not automatic errors.
• Establish hard gates for critical failures and warning bands for manageable variation.
• Version rules, profiles, reference data, and source snapshots.
• Segment quality results by source, time, geography, and relevant population groups.
• Assign owners for remediation and communicate quality trends.
• Monitor continuously and revise thresholds when processes or uses change.

Chapter Summary

Data profiling establishes an empirical understanding of dataset structure, metadata, distributions, relationships, and temporal behaviour. Quality assessment interprets that evidence through dimensions including accuracy, completeness, consistency, validity, timeliness, and uniqueness. Effective assessment requires explicit grains, schemas, definitions, validation rules, reference data, and documented denominators.

Distributional summaries reveal missingness, skew, unexpected ranges, and population shifts, while cross-field and cross-table rules expose contradictions that univariate statistics cannot detect. Anomalies should be investigated in context because unusual values may be valid. Metrics and scorecards support communication, but weighted aggregate scores must not conceal failures in critical controls. Acceptance thresholds should reflect use-case risk, regulation, historical performance, and operational consequences.

A professional quality process is reproducible, monitored, governed, and connected to remediation. It treats data quality as a continuous organisational capability rather than a one-time cleansing task. Ethical practice additionally requires attention to subgroup effects, privacy, explainability, correction rights, and the consequences of automated decisions.

Key Terms

Accuracy: Degree to which data correctly represent the real-world value or event.

Anomaly: Observation that differs from an expected pattern and requires investigation.

Completeness: Presence of required values, records, entities, or time periods.

Consistency: Agreement among related values across fields, records, systems, or time.

Data contract: Agreement specifying data structure, meaning, quality, delivery, and responsibilities.

Data grain: The unit represented by one record, such as one order line.

Data observability: Continuous monitoring of data behaviour, freshness, structure, and lineage.

Data profiling: Systematic examination of data structure, content, relationships, and metadata.

Data quality dimension: A defined perspective used to judge fitness for use.

Duplicate rate: Proportion of records repeated under a specified key definition.

Freshness: How recently data were produced or updated relative to requirements.

IQR: Interquartile range, calculated as Q₃ − Q₁.

Lineage: Record of data origins, transformations, and destinations.

Metadata: Information describing data definitions, units, ownership, source, and usage.

Schema drift: Unplanned or unhandled change in data structure over time.

Scorecard: Organised presentation of quality metrics, thresholds, status, and ownership.

Timeliness: Availability of data within the required time window.

Uniqueness: Property that intended entities or events are represented once at the defined grain.

Validity: Conformance to permitted types, formats, values, ranges, or rules.

Review Questions

Short Answer Questions

1. What is the difference between data profiling and data quality assessment?
2. Why must the grain of a dataset be defined before testing uniqueness?
3. Distinguish validity from accuracy using an example.
4. What information should a data contract contain?
5. Why should an anomaly not automatically be treated as an error?

Long Answer Questions

1. Explain how schema, metadata, and distributional profiling work together to support data preparation.
2. Compare accuracy, completeness, consistency, validity, timeliness, and uniqueness, including one suitable metric or rule for each.
3. Describe an end-to-end workflow for profiling and assessing a dataset intended for predictive modelling.
4. Explain the benefits and limitations of weighted quality scores and hard acceptance gates.
5. Discuss the ethical, privacy, and governance implications of automated data-quality monitoring.

Analytical / Application Questions

1. A dataset contains 50,000 records, of which 1,500 required customer identifiers are missing. Calculate completeness and determine whether the dataset passes a 97% minimum threshold. State any assumptions.
2. An order table contains negative quantities, repeated order identifiers, and shipment dates earlier than order dates. Design a validation plan that distinguishes legitimate returns, duplicate updates, and genuine date errors.
3. A source system delivers complete daily files but omits one region for an entire month. Explain which quality dimensions are affected, how the issue should appear on a scorecard, and whether an aggregate quality score should permit release.

Multiple Choice Questions with Answers

Question 1

Which activity is most directly associated with data profiling?

A. Examining distributions, missingness, schemas, and relationships

B. Replacing every unusual value with the mean

C. Training a predictive model without inspecting inputs

D. Encrypting all data without recording metadata

Correct Answer: A. Examining distributions, missingness, schemas, and relationships

Question 2

A field contains values from an approved list but some values refer to the wrong customer. Which quality dimension is primarily violated?

A. Timeliness

B. Accuracy

C. Uniqueness

D. Completeness

Correct Answer: B. Accuracy

Question 3

A table has 10,000 records and 250 missing required values. What is its completeness rate?

A. 2.5%

B. 95.0%

C. 97.5%

D. 99.75%

Correct Answer: C. 97.5%

Question 4

Which rule is a cross-field validation rule?

A. The country code must be three characters

B. The customer identifier must not be null

C. The status must be in an approved list

D. The delivery date must be on or after the order date

Correct Answer: D. The delivery date must be on or after the order date

Question 5

Why is a weighted aggregate quality score insufficient as the only release decision?

A. Weights cannot ever be assigned to quality dimensions

B. A high average can conceal a critical failure

C. Aggregate scores always measure accuracy directly

D. Quality metrics cannot be calculated numerically

Correct Answer: B. A high average can conceal a critical failure

Question 6

What does schema drift describe?

A. A change in the distribution of one numeric variable only

B. A change in the structure or declared form of data over time

C. The removal of duplicate business events

D. A delay in delivering a scheduled file

Correct Answer: B. A change in the structure or declared form of data over time

Question 7

Which statement about an IQR-based outlier flag is correct?

A. It proves that every flagged record is erroneous

B. It is a screening convention requiring contextual interpretation

C. It can be applied only to categorical variables

D. It guarantees detection of all data-quality problems

Correct Answer: B. It is a screening convention requiring contextual interpretation

Question 8

A foreign-key check finds orders whose customer identifiers do not exist in the customer table. Which issue is demonstrated?

A. Referential integrity failure

B. Timeliness failure only

C. Unit inconsistency only

D. Duplicate suppression

Correct Answer: A. Referential integrity failure

Question 9

A delivery process has 120 expected files and 114 arrive before the deadline. What is the timeliness rate?

A. 5%

B. 95%

C. 94%

D. 114%

Correct Answer: B. 95%

Question 10

Which is the most appropriate treatment for an unusual but potentially legitimate transaction?

A. Delete it immediately

B. Replace it with the median

C. Flag it for contextual investigation

D. Ignore it because it is rare

Correct Answer: C. Flag it for contextual investigation