Data Profiling and Quality Assessment

Introduction

Data wrangling begins with an important question: Can this dataset be trusted for its intended use? A dataset may contain thousands or millions of records and still be unsuitable for analysis because values are missing, incorrectly formatted, duplicated, outdated, or inconsistent. Data profiling is the systematic examination of data structures, values, distributions, relationships, and quality characteristics. Data quality assessment uses the evidence produced by profiling to determine whether data is fit for a particular purpose.

In professional data science, quality is not an absolute property. A dataset may be adequate for exploratory analysis but inadequate for regulatory reporting. For example, an approximate customer location may be acceptable for regional marketing but unacceptable for emergency dispatch. Quality must therefore be evaluated in relation to business requirements, analytical objectives, risks, and users.

This chapter introduces practical methods for profiling datasets and assessing six central dimensions of quality: completeness, validity, accuracy, consistency, uniqueness, and timeliness. It also explains descriptive statistics, constraints, quality reports, data-quality rules, and responsible remediation.

Learning objectives

By the end of this chapter, you should be able to:

1. Explain the purpose and stages of data profiling.
2. Distinguish completeness, validity, accuracy, consistency, uniqueness, and timeliness.
3. Use descriptive statistics and frequency analysis to identify data problems.
4. Design constraints and quality rules for structured datasets.
5. Calculate and interpret common quality indicators.
6. Produce a practical data-quality report.
7. Recommend appropriate remediation while preserving data lineage and accountability.

1. Data profiling as an investigative process

Data profiling is the process of collecting information about a dataset’s structure and content. A profile normally includes:

• table and field names;
• data types and formats;
• number of records;
• missing-value counts;
• distinct-value counts;
• minimum, maximum, mean, median, and spread for numerical fields;
• frequency distributions for categorical fields;
• patterns in text and identifiers;
• potential keys and relationships;
• violations of defined business rules.

Profiling is often performed at three levels.

Structural profiling

Structural profiling examines metadata: column names, data types, field lengths, nullability, primary-key candidates, and relationships between tables. It answers questions such as:

• Is customer_id stored as text or as a number?
• Is transaction_date represented consistently as a date?
• Which columns are mandatory?
• Can a field serve as a unique identifier?

Content profiling

Content profiling examines the actual values in fields. It identifies unusual values, invalid codes, outliers, empty strings, inconsistent capitalization, and unexpected patterns. A field declared as numeric may contain values such as unknown, £1,200, or N/A, which can cause conversion and analysis errors.

Relationship profiling

Relationship profiling examines connections between fields and tables. It can detect orphan records, unmatched foreign keys, impossible combinations, and violations of functional dependencies. For example, if every product code should identify one product name, a product code associated with several names requires investigation.

A useful profiling workflow is:

1. define the intended use and quality requirements;
2. preserve and inspect the raw source;
3. profile structure and content;
4. define quality rules and thresholds;
5. measure violations;
6. investigate causes;
7. correct, quarantine, or document problematic records;
8. repeat profiling after transformation;
9. publish a quality report with evidence and limitations.

Profiling should occur both before and after data cleaning. A cleaned dataset without a second profile cannot demonstrate that the transformation achieved its purpose.

2. Descriptive statistics for profiling

Descriptive statistics summarise the observed data. They do not prove that values are correct, but they reveal patterns requiring investigation.

For a numerical variable with observations x₁, x₂, ..., x_n, the arithmetic mean is:

Here, n is the number of observed values and xᵢ is the value of observation i. The mean is appropriate for quantitative data when extreme values do not dominate interpretation. It has the same unit as the variable.

The median is the middle ordered observation, or the average of the two middle observations when n is even. It is often more robust than the mean when data is skewed or contains extreme values.

The range is:

The sample standard deviation is:

The denominator n − 1 is used when estimating population variability from a sample. If all records constitute the complete population of interest, a population standard deviation may instead use n. The standard deviation has the same unit as the measured variable.

The interquartile range is:

where Q₁ and Q₃ are the first and third quartiles. A common exploratory rule flags values below Q₁ − 1.5 × IQR or above Q₃ + 1.5 × IQR as potential outliers. This is a screening rule, not proof of an error. A genuine high-value transaction and a misplaced decimal may both be flagged.

For categorical fields, useful summaries include frequency, proportion, number of distinct values, and the most common value. A frequency table may reveal variants such as Female, female, F, and Fem. that should be assessed for standardisation.

3. The six dimensions of data quality

3.1 Completeness

Completeness concerns whether required data is present. Missingness may appear as a null value, blank string, placeholder such as -, or an unrecorded row.

For a field, the completeness rate is:

If 950 of 1,000 customer records contain a telephone number, completeness is 950/1000 × 100% = 95%. The denominator must be defined carefully: “expected values” may mean all records, only active customers, or only records for which the field is applicable.

Missingness can be classified as:

• structural: the field does not apply, such as a delivery date for a digital purchase;
• unknown: the value should exist but is not known;
• not recorded: the process failed to capture it;
• suppressed: the value is intentionally withheld for privacy or policy reasons.

Replacing every missing value with zero is dangerous because zero is a meaningful measurement, not a general missing-value code.

3.2 Validity

Validity asks whether a value conforms to an accepted type, format, range, domain, or rule. Examples include:

• an age must be an integer from 0 to 120;
• a percentage must lie between 0 and 100;
• a date must use an accepted calendar format;
• a country code must belong to an approved reference list;
• a quantity must be non-negative.

A validity rate can be expressed as:

A value can be valid but inaccurate. For example, 31 February 2025 is invalid as a date, while 28 February 2025 may be a valid but inaccurate recorded date.

3.3 Accuracy

Accuracy is the degree to which a recorded value represents the real-world value. It is usually assessed by comparison with a trusted source, supporting document, measurement process, or confirmed observation.

Accuracy is more difficult to measure than validity. A phone number can have a valid structure and still belong to the wrong person. A sensor reading can be numeric and within its permitted range but incorrectly calibrated.

Accuracy assessment may involve:

• comparison with an authoritative master dataset;
• sampling and manual verification against source documents;
• reconciliation with totals or independent systems;
• checking sensor calibration and measurement procedures;
• confirming records with data owners.

Accuracy claims should identify the reference source and date of verification.

3.4 Consistency

Consistency means that data agrees across fields, records, systems, or time. Examples include:

• a line-item total agrees with quantity multiplied by unit price;
• a customer’s status agrees across two systems;
• a transaction date is not earlier than the customer-registration date;
• the same unit of measurement is used throughout a column;
• a foreign key refers to an existing parent record.

Consistency rules often use multiple fields. A dataset may pass individual field validation while failing cross-field validation.

3.5 Uniqueness

Uniqueness concerns whether records or identifying fields occur only as intended. Duplicate records may result from repeated uploads, integration errors, retries, or legitimate repeated events.

The duplicate rate for a proposed key can be measured as:

A duplicate must be defined operationally. Two purchases by the same customer on the same day are not necessarily duplicates. A duplicate may instead mean identical values across transaction ID, timestamp, amount, and product code.

3.6 Timeliness

Timeliness concerns whether data is current and available when needed. It has at least two aspects:

• currency: how recently the value was updated;
• availability: whether it arrives within the required reporting or operational deadline.

A useful operational measure is data latency:

Both times must use the same time zone and time unit. If a fraud-monitoring system requires data within five minutes but records arrive after twenty minutes, the data may be accurate yet not timely enough for the purpose.

4. Constraints and quality rules

Constraints convert expectations into testable conditions. They should be specific, measurable, documented, and linked to an owner.

A rule should also state how exceptions are handled. A failed rule may cause rejection, quarantine, warning, or acceptance with an audit flag. For example, a missing optional marketing preference may generate a warning, whereas a missing transaction identifier may prevent loading.

Quality rules must avoid hidden assumptions. A rule that an email field must be non-null may unfairly penalise customers who cannot or should not provide email. Requirements should therefore be reviewed with domain experts and privacy stakeholders.

5. Measuring and interpreting quality

Suppose a customer table contains 2,000 records. The customer_id field has 2,000 non-null values, but 8 values are duplicated. The email field has 1,840 non-null values, of which 1,790 pass a syntax check. The completeness of email is 1840/2000 × 100% = 92%. Its syntactic validity among all records is 1790/2000 × 100% = 89.5%, while validity among non-missing values is approximately 1790/1840 × 100% ≈ 97.3%. These are different indicators and should not be confused.

A quality score may be calculated when dimensions have agreed weights:

where qⱼ is the score for quality dimension j, wⱼ is its weight, and the weights satisfy ∑ⱼ wⱼ = 1. This weighted score is meaningful only when the component scores are defined on a comparable scale and the weights reflect actual risk. A single average can conceal a critical failure; therefore, reports should show individual dimensions and high-severity violations as well as any overall score.

6. Quality reports and dashboards

A professional quality report should be reproducible and understandable to technical and non-technical users. It commonly contains:

1. dataset name, owner, source, extraction time, and version;
2. purpose and scope of assessment;
3. record and field counts;
4. data types and missingness summary;
5. distributions and unusual values;
6. rule definitions and pass/fail results;
7. quality indicators by field and dimension;
8. severity, impact, and affected records;
9. suspected causes and recommended actions;
10. limitations, assumptions, and next review date.

A compact field-level report might include:

Dashboards are useful for monitoring trends, but a visual indicator does not replace rule definitions or evidence. A red quality indicator should link to failed records, rule versions, and remediation status.

7. Worked example: profiling a small sales dataset

Consider five records:

Assume that Order ID should be unique, quantity must be a positive integer, date is required, and status must be Open, Closed, or Cancelled.

There are five records, so the completeness of Order date is 4/5 × 100% = 80%. The order ID A102 occurs twice, producing one duplicated record under the stated uniqueness rule and a duplicate-record rate of 1/5 × 100% = 20%. Quantity has one invalid negative value, so its validity rate is 4/5 × 100% = 80%. All observed statuses belong to the approved domain, giving a status validity rate of 100%.

The report should not simply delete one A102 record. The analyst should determine whether the duplicate represents a repeated load or two legitimate sales sharing an identifier. Similarly, the negative quantity might represent a return; if returns are permitted, the rule is inappropriate and should distinguish sales from returns. This example demonstrates that profiling identifies issues, while domain interpretation determines the correct response.

8. Applied case: illustrative online retailer

Background

An illustrative online retailer integrates orders from a web shop, mobile application, and call centre. Management observes that daily revenue reports differ from the finance system and that customer-service staff cannot locate some orders.

Problem

An initial profile shows 1.2 million order rows. The order identifier is missing in 0.4% of rows, 1.8% of identifiers are duplicated, and 3.5% of product codes do not match the product master. Dates use three formats, and some timestamps are stored in local time while others are stored in UTC. The order-status field contains twelve labels although only five are approved.

Analysis

The team separates symptoms from causes. Duplicate identifiers are traced to retry logic in the mobile application. Product-code failures arise partly from retired products and partly from truncation during a database export. Status variation is caused by uncontrolled codes in the call-centre system. Timestamp differences produce apparent daily revenue discrepancies at midnight boundaries.

The team defines rules for required identifiers, approved status values, referential integrity, timestamp standardisation, and duplicate detection. It also assigns severity: missing order IDs and financial reconciliation failures are critical; unusual but explainable product codes are warnings.

Approach and solution

The retailer preserves raw files, creates a staging layer, standardises timestamps to UTC while retaining the original time zone, maps historical status codes to a controlled vocabulary, and quarantines rows with missing identifiers. Duplicate events are investigated using source system, event time, order ID, amount, and item details rather than deleting records automatically. Product-code mappings are versioned so that historical reports remain reproducible.

Automated profiling runs after every load. The quality report is delivered to data engineering, finance, and operations. Owners are assigned to each failed rule, and thresholds are reviewed monthly.

Outcome

After remediation, missing identifiers fall below the agreed threshold, product-master matching improves, and revenue reconciliation is documented by time zone and transaction status. Some records remain unresolved, but they are visible, quarantined, and excluded from specified reports rather than silently altered.

Learning points

The case illustrates that data quality is a socio-technical issue. Technical validation detects patterns, but process ownership, reference data, time-zone policy, lineage, and business definitions determine whether a correction is defensible.

9. Remediation, governance, and ethics

Common remediation actions include standardising formats, mapping codes, correcting known transcription errors, deduplicating records, imputing missing values, obtaining a fresh source, and quarantining records for review. Every change should preserve the raw value, transformed value, rule applied, timestamp, responsible process, and reason for change.

Imputation requires particular care. Mean substitution can reduce apparent variation and distort relationships. A missing value should not be imputed merely to improve a completeness percentage. The method should reflect the data-generating process, be documented, and be evaluated for bias.

Data quality also has ethical and legal implications. Incorrect identity, health, credit, or employment data can harm individuals. Quality processes should support:

• data minimisation and purpose limitation;
• access controls and encryption;
• protection of sensitive and personal data;
• audit trails and retention policies;
• correction mechanisms for affected individuals;
• fairness testing across relevant groups;
• human review for high-impact decisions.

AI-assisted profiling can detect anomalies, infer patterns, and prioritise records for review. However, AI recommendations are not proof of error. Models may reproduce historical bias, expose personal information, or falsely flag minority patterns as anomalous. Human oversight, explainable rules, representative testing, and documented approval remain necessary.

10. Challenges and best practices

Important challenges include changing schemas, inconsistent source definitions, hidden missing values, measurement error, data drift, duplicate events, and conflicting business rules. A quality rule that is correct for one period may become inappropriate after a process change.

Best practices include:

1. define fitness-for-purpose before measuring quality;
2. profile raw, transformed, and published data;
3. use both field-level and cross-field rules;
4. distinguish invalid, inaccurate, missing, and unusual values;
5. use reference data and authoritative sources explicitly;
6. retain lineage and never overwrite raw evidence without justification;
7. assign owners and remediation deadlines;
8. report rates together with counts and severity;
9. monitor quality over time to detect drift;
10. validate assumptions with domain experts;
11. protect personal and sensitive information;
12. treat automated flags as investigation priorities, not automatic truth.

Chapter Summary

Data profiling is a structured investigation of dataset metadata, values, distributions, and relationships. Descriptive statistics reveal missingness, unusual distributions, outliers, and coding problems, while constraints convert business expectations into testable quality rules. Completeness measures whether required values are present; validity measures conformity to permitted types, formats, domains, and ranges; accuracy concerns correspondence with reality; consistency concerns agreement across fields, records, systems, and time; uniqueness concerns unintended duplication; and timeliness concerns currency and availability.

Quality assessment is meaningful only in relation to purpose, risk, and agreed thresholds. Effective reports show definitions, indicators, failed records, severity, causes, owners, and limitations. Remediation should preserve lineage and avoid unjustified deletion or imputation. Governance, privacy, fairness, and human accountability are essential when quality findings influence people or important decisions.

Key Terms

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

Data quality: Fitness of data for a defined purpose.

Completeness: Presence of required data values.

Validity: Conformity to defined types, formats, domains, or rules.

Accuracy: Agreement between recorded and real-world values.

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

Uniqueness: Absence of unintended duplicate records or identifiers.

Timeliness: Suitability of data’s currency and availability for use.

Constraint: A condition that acceptable data must satisfy.

Reference data: Controlled values used to standardise or validate records.

Null: A database representation of an absent or unknown value.

Outlier: An observation unusually distant from the remainder of a distribution.

Data lineage: Record of data origins, transformations, and destinations.

Quarantine: Separating records that require investigation from accepted data.

Duplicate rate: Proportion of records identified as duplicates under a specified rule.

Latency: Time between a source event and data availability or assessment.

Data drift: Change in data distributions or relationships over time.

Referential integrity: Requirement that a reference value corresponds to an existing related record.

Fitness for purpose: Suitability of data for a specified task or decision.

Review Questions

Short Answer Questions

1. What is data profiling, and how does it differ from data cleaning?
2. Distinguish validity from accuracy using an example.
3. Why should missing values not automatically be replaced with zero?
4. What is the purpose of a referential-integrity rule?
5. Name four items that should appear in a data-quality report.

Long Answer Questions

1. Explain the six dimensions of data quality and describe one practical measure for each.
2. Discuss how descriptive statistics can reveal quality problems without proving that a value is incorrect.
3. Explain how constraints should be designed, documented, and assigned different responses according to severity.
4. Describe the role of data lineage in responsible data remediation.
5. Discuss the ethical and governance risks of using automated or AI-assisted data-quality assessment.

Analytical / Application Questions

1. A dataset contains 10,000 records. A required field has 9,300 non-missing values, of which 9,114 pass a validity rule. Calculate completeness and validity among non-missing values, and explain why both measures should be reported.
2. A sales table contains duplicate invoice numbers, negative quantities, and dates in three formats. Design a profiling and remediation plan that distinguishes investigation, correction, and quarantine.
3. A real-time fraud system receives accurate transactions 30 minutes after they occur, although the operational requirement is five minutes. Assess the timeliness problem and recommend monitoring indicators and process improvements.

Multiple Choice Questions with Answers

Question 1

Which activity is an example of structural profiling?

A. Calculating the median order value

B. Checking whether a column is nullable and identifying its data type

C. Comparing a customer address with a utility bill

D. Investigating a suspected duplicate transaction

Correct Answer: B. Checking whether a column is nullable and identifying its data type

Question 2

A field contains 950 non-missing values in 1,000 records. What is its completeness rate?

A. 5%

B. 50%

C. 95%

D. 105%

Correct Answer: C. 95%

Question 3

Which statement best distinguishes validity from accuracy?

A. Validity concerns recency, while accuracy concerns uniqueness

B. Validity concerns conformity to rules, while accuracy concerns correspondence with reality

C. Validity concerns duplicates, while accuracy concerns missingness

D. Validity and accuracy always mean exactly the same thing

Correct Answer: B. Validity concerns conformity to rules, while accuracy concerns correspondence with reality

Question 4

Which is a cross-field consistency rule?

A. Customer ID must be non-null

B. Status must be one of three approved labels

C. Quantity must be an integer

D. Delivery date must not precede order date

Correct Answer: D. Delivery date must not precede order date

Question 5

What does a duplicate rate measure?

A. The proportion of records missing a value

B. The proportion of records failing a format rule

C. The proportion of records identified as duplicates under a specified rule

D. The proportion of records older than a defined date

Correct Answer: C. The proportion of records identified as duplicates under a specified rule

Question 6

Which action best preserves auditability during remediation?

A. Overwriting the original value permanently

B. Deleting all unusual records without review

C. Retaining raw and transformed values with the rule and timestamp

D. Replacing every missing value with the mean

Correct Answer: C. Retaining raw and transformed values with the rule and timestamp

Question 7

A dataset has 1,790 valid email values among 1,840 non-missing email values. Approximately what percentage of non-missing values are valid?

A. 79.3%

B. 89.5%

C. 92.0%

D. 97.3%

Correct Answer: D. 97.3%

Question 8

Which situation primarily indicates a timeliness problem?

A. A transaction arrives 30 minutes after an event when the requirement is five minutes

B. A country code is not in the approved list

C. Two records share an identifier

D. A required field is blank

Correct Answer: A. A transaction arrives 30 minutes after an event when the requirement is five minutes

Question 9

Why should an outlier not automatically be deleted?

A. Outliers are always caused by missing values

B. An outlier may be a genuine observation or a data error requiring investigation

C. Outliers cannot be detected using descriptive statistics

D. Deleting outliers always improves accuracy

Correct Answer: B. An outlier may be a genuine observation or a data error requiring investigation

Question 10

What is the main purpose of a quality threshold?

A. To define the acceptable level of performance for a quality rule

B. To convert all categorical fields into numbers

C. To guarantee that every value is accurate

D. To remove the need for domain experts

Correct Answer: A. To define the acceptable level of performance for a quality rule