Data Foundations, Quality and Preparation

Introduction

Data-driven decision making depends less on the volume of data than on whether the data represent the phenomenon of interest accurately, consistently and ethically. A sales dashboard may contain millions of transactions, yet still support poor decisions if prices are recorded in mixed currencies, customer identifiers are duplicated, or missing values occur disproportionately in one region. Data preparation is therefore not a clerical prelude to analysis; it is an analytical activity that shapes what can be concluded.

This chapter examines the foundations of business data, including data types, sources and measurement scales. It then develops a quality framework based on validity, reliability, completeness, consistency, timeliness, uniqueness and bias. Finally, it presents a practical workflow for collection, cleaning, integration, transformation and exploratory preparation.

Learning Objectives

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

1. Distinguish structured, semi-structured and unstructured data and identify common business data sources.
2. Classify variables using nominal, ordinal, interval and ratio measurement scales.
3. Assess data validity, reliability, completeness, consistency, uniqueness, timeliness and bias.
4. Design responsible data-collection procedures.
5. Clean, integrate and transform data for analysis.
6. Use exploratory checks to identify errors, unusual observations and patterns of missingness.
7. Explain the ethical, governance and managerial implications of data preparation.

1. What Is Data?

Data are recorded observations, measurements, events or representations of entities and their attributes. An entity may be a customer, product, employee, store or transaction. An attribute describes an entity, such as customer segment, product price or transaction date. A dataset is a structured collection of observations, usually arranged as rows and columns.

A useful distinction is between a record, a variable and a value. A record is one observational unit—for example, one order. A variable is a characteristic measured for each unit—for example, order value. A value is the recorded result, such as 245.50. Confusing the observational unit is a frequent source of analytical error. A customer-level dataset and a transaction-level dataset answer different questions and should not be treated as interchangeable.

Data may also be described as:

• Primary data: collected directly for the current investigation, such as survey responses or an experiment.
• Secondary data: collected previously for another purpose, such as government statistics, archived transactions or industry reports.
• Metadata: data about data, including definitions, units, ownership, collection dates, permissible values and transformation history.

Metadata is essential for interpretation. A field called revenue is ambiguous unless the organization specifies whether it includes tax, discounts, refunds and shipping charges, and whether the currency is pounds, dollars or euros.

2. Data Types and Sources

2.1 Structural forms

Structured data have a predefined schema, such as a relational table with fields for customer ID, date, quantity and price. Structured data are relatively easy to query, validate and aggregate.

Semi-structured data do not conform to a rigid tabular schema but contain organizational markers. JSON, XML, application logs and many API responses are examples. Different records may contain different fields, so a schema must often be inferred or imposed during preparation.

Unstructured data lack a consistent tabular structure. Text, images, audio, video, emails and scanned documents are examples. They can be converted into analytical features, but this introduces additional choices and possible measurement error.

2.2 Common business sources

Business data may originate from enterprise resource planning systems, customer relationship management platforms, point-of-sale systems, websites, mobile applications, sensors, surveys, social media, public registers and third-party providers. Each source has a characteristic purpose, population, collection process and error profile.

Operational systems often provide detailed records but may reflect processes designed for transactions rather than analysis. Survey data can capture attitudes that transactional systems cannot observe, but responses may be affected by question wording, non-response and social desirability. Web data may be timely but incomplete because users block cookies, change devices or interact anonymously.

A source assessment should ask:

1. Who generated the data and for what purpose?
2. What population and time period do the data cover?
3. What does each record represent?
4. How were variables measured and coded?
5. What exclusions, revisions or incentives affected collection?
6. Are access, licensing and privacy conditions satisfied?

2.3 Quantitative and qualitative data

Quantitative data represent counts or measurements and can support arithmetic operations under appropriate measurement assumptions. Examples include units sold, delivery time and account balance. Qualitative data represent categories, descriptions or meanings, such as complaint type, occupation or interview text. Categorical data may be encoded numerically for storage, but numerical labels do not automatically make the categories quantitative.

A binary variable has two categories, such as subscribed/not subscribed. A discrete variable takes separate countable values, such as number of support calls. A continuous variable can, in principle, take any value in an interval, such as weight or elapsed time. In practice, continuous values are recorded with finite precision.

3. Measurement Scales

The measurement scale determines which comparisons and calculations are meaningful.

Nominal labels identify groups but do not imply that one category is greater than another. Ordinal categories provide order, but the difference between “satisfied” and “very satisfied” cannot automatically be assumed equal to the difference between “neutral” and “satisfied.” Interval scales support meaningful differences; 20 °C is 10 degrees warmer than 10 °C, but it is not twice as warm in the ratio-scale sense. Ratio scales possess a meaningful zero, so £200 is twice £100 when the same currency and definition are used.

The scale is a property of the measurement, not merely of the storage format. A five-point rating stored as 1–5 remains ordinal unless a defensible measurement model justifies treating it as approximately interval. Treating categorical codes as numerical quantities can produce misleading averages and correlations.

4. Data Quality: Dimensions and Assessment

Data quality is fitness for a specified use. There is no universally perfect dataset: a delay of several hours may be acceptable for strategic planning but unacceptable for real-time fraud detection. Quality assessment must therefore state the intended decision, population, time horizon and tolerance for error.

4.1 Validity

Validity concerns whether a value represents what it is intended to represent. A date of 31 February is syntactically invalid; a customer age of 250 is substantively implausible; a survey question that measures purchasing power when it claims to measure satisfaction has construct-validity problems.

Validation rules may be:

• Type rules: a date field must parse as a date and quantity as numeric.
• Range rules: quantity must be non-negative and within a plausible operational limit.
• Domain rules: country codes must belong to an approved reference list.
• Relational rules: delivery date should not precede order date.
• Referential rules: every order’s customer ID should exist in the customer master table.

4.2 Reliability

Reliability concerns consistency of measurement. A scale is more reliable when repeated measurement under similar conditions produces similar results. Reliability can be threatened by changing question wording, inconsistent staff procedures, faulty sensors or unstable system integrations. Reliability does not guarantee validity: a thermometer that is consistently two degrees too high is reliable but inaccurate.

For a binary classification, agreement between two coders can be summarized using observed agreement, but raw agreement may be inflated when one category is very common. Cohen’s kappa is sometimes used for two categorical raters:

Here, pₒ is the observed proportion of agreement and pₑ is the agreement expected by chance from the raters’ marginal proportions. Kappa assumes categorical ratings from two raters and is sensitive to prevalence and marginal-distribution effects; it should not be interpreted mechanically as a universal reliability standard.

For a multi-item scale, internal consistency is often summarized by Cronbach’s alpha:

where k is the number of items, sᵢ² is the variance of item i, and sₜ² is the variance of the total score. Alpha assumes a suitable scale structure and is not proof of unidimensionality or validity. It should be complemented by content and construct evaluation.

4.3 Completeness, consistency, uniqueness and timeliness

Completeness concerns whether required observations or fields are present. A completeness rate for a field can be expressed as:

The denominator must be defined carefully. If records excluded from the system are not counted, apparent completeness may be overstated.

Consistency means that related values and coding conventions agree across records or systems. For example, “United Kingdom,” “UK” and “GB” may represent one country but create grouping problems unless harmonized.

Uniqueness means that a record is not duplicated in a way that distorts analysis. Duplicate detection may use an exact key, such as transaction ID, or probabilistic matching across name, address and date. Timeliness concerns whether data are available and current enough for the decision. A highly accurate monthly dataset may be unsuitable for minute-by-minute operational control.

4.4 Bias and representativeness

Bias is systematic distortion that causes the observed data or resulting estimate to differ from the target phenomenon. Common forms include:

• Coverage bias: some members of the target population cannot enter the sampling frame.
• Non-response bias: people who do not respond differ meaningfully from respondents.
• Selection bias: inclusion depends on characteristics related to the outcome.
• Measurement bias: the instrument or process systematically over- or under-records values.
• Survivorship bias: analysis includes only entities that remain observable.
• Historical or allocation bias: past decisions embedded unequal treatment in the data.

Random variation and systematic bias are different. Increasing sample size can reduce random sampling error, but it does not automatically remove systematic bias. A useful audit compares the sample with known population benchmarks and examines missingness and outcomes across relevant groups.

5. A Data Preparation Workflow

Data preparation is iterative rather than strictly linear. A practical workflow is:

Figure: Data preparation is an iterative process; exploration may reveal the need to revisit cleaning or collection.

5.1 Define and document

Begin with the decision and analytical unit, not with the available spreadsheet. Establish a data dictionary containing variable name, definition, type, scale, unit, allowable values, source, owner and sensitivity classification. Record extraction dates, time zones, currency, version and known limitations. This documentation supports reproducibility and prevents “semantic drift,” in which a field’s meaning changes without its name changing.

5.2 Collect responsibly

Collection procedures should minimize unnecessary personal data and obtain appropriate consent or another lawful basis where required. Surveys should use neutral wording, clear response options and pilot testing. Administrative and digital data should be collected with access controls, retention limits and appropriate security. Sampling should be aligned with the target population; convenience samples may be useful for exploratory work but should not be presented as representative without justification.

5.3 Profile before changing

Profiling describes the data as received. Inspect row and column counts, data types, missing-value patterns, unique-value counts, frequency distributions, minimum and maximum values, date ranges and duplicate keys. For numeric variable x, the arithmetic mean is:

where n is the number of observed values. The mean is sensitive to extreme values, so the median and quantiles should also be examined. For a sample standard deviation:

The denominator n − 1 is used when estimating population variability from a sample under the usual independent-sampling framework. These summaries describe observed data; they do not by themselves establish causality or representativeness.

5.4 Clean values

Cleaning should correct errors without silently changing legitimate variation. Typical actions include:

• standardizing capitalization, whitespace, spelling and date formats;
• converting units and currencies using documented rules;
• resolving invalid codes against reference tables;
• correcting obvious transcription errors only when supported by evidence;
• flagging implausible values for review rather than deleting them automatically;
• separating missing, not applicable, refused and unknown responses;
• identifying duplicates using business keys and timestamps.

Missing data require diagnosis. If missingness is unrelated to observed or unobserved values, it is often called missing completely at random. If it depends on observed variables, it is missing at random conditional on those variables. If it depends on the unobserved value itself, it is missing not at random. These assumptions cannot be proven solely from the observed dataset. Options include complete-case analysis, explicit missing categories, model-based imputation or sensitivity analysis. Imputation should not create false certainty, and the original missingness indicator should often be retained.

Outliers are observations unusually distant from the rest, not automatically errors. A high order value may be a genuine wholesale purchase. Investigate source records, business context and influence on the intended analysis. Robust summaries, transformations or segmented analysis may be preferable to arbitrary deletion.

5.5 Integrate data

Integration combines sources while preserving meaning. Joins require compatible keys and well-defined relationship cardinality. A one-to-many join can legitimately produce multiple rows for one customer, but an accidental many-to-many join can multiply transactions and inflate revenue.

Before joining, check key uniqueness, coverage and data types. Maintain a crosswalk when identifiers differ across systems. Resolve conflicting definitions explicitly—for example, whether “active customer” means a purchase in 30 days or a login in 90 days. Keep source columns and transformation logs so that results can be traced back.

5.6 Transform for analysis

Transformation may include aggregation, reshaping, encoding categorical variables, scaling, binning and creating derived measures. A ratio such as conversion rate must have a defensible numerator and denominator and must avoid aggregating individual rates without considering their denominators. For example, the overall conversion rate is total conversions divided by total eligible visits, not necessarily the simple average of daily rates.

A common standardization is the z-score:

where xᵢ is an observation, x̄ is the reference mean and s is the reference standard deviation. It is dimensionless and useful when variables are measured on different scales, but it is sensitive to outliers and should be calculated using an appropriate training or reference set when used in predictive modeling. Transformations must not leak future information into a model-training dataset.

5.7 Explore and validate

Exploratory data analysis uses tables, plots and summaries to identify distributions, relationships, unusual records and potential errors. Useful checks include histograms for shape, box plots for extreme values, scatter plots for associations, time plots for discontinuities and cross-tabulations for category inconsistencies. Exploration generates hypotheses; it does not replace confirmatory analysis.

Validation should include automated tests and human review. Examples are: required columns exist; IDs are unique where expected; totals reconcile to trusted reports; dates fall within the study period; categorical values belong to approved domains; and row counts after joins match the expected cardinality. A quality report should state the number of records received, rejected, corrected, imputed and retained.

6. Worked Example: Preparing Retail Order Data

Suppose an analyst receives 100,000 order rows from an online retailer. Each row should represent one order line and contain order ID, customer ID, product ID, order date, quantity, unit price, discount and delivery date.

Initial profiling finds 1,200 missing customer IDs, 400 negative quantities, 700 duplicated order-line IDs and dates in three formats. The analyst should not simply delete all problematic rows. First, the data dictionary and source-system owner are consulted. Negative quantities are found to represent returned items, while 35 rows are genuine entry errors. The business definition therefore distinguishes sales and returns rather than treating every negative value as invalid.

The preparation proceeds as follows:

1. Parse all dates using documented day–month conventions and retain the original strings for audit.
2. Validate that unit price and discount use the same currency and tax convention.
3. Separate return lines from sales lines using the transaction-type code; investigate the 35 records with an impossible combination of return code and positive quantity.
4. Resolve duplicate IDs by checking whether they are repeated extracts or legitimate split shipments.
5. Link customer IDs to the customer master table and report unmatched records rather than assigning arbitrary customers.
6. Calculate line net value using the documented rule: quantity × unit price − discount. Confirm the treatment of returns and tax with finance.
7. Compare daily totals with the trusted order-management report and investigate material differences.
8. Produce an analysis-ready table and a rejected-or-review table, each with a reason code.

The result is more defensible than a dataset produced by automatic deletion. It preserves meaningful returns, exposes uncertainty and allows a manager to understand how reported revenue was constructed.

7. Illustrative Case: Regional Customer Churn

Background. A subscription company wants to identify customers at risk of cancellation. Its data warehouse contains subscription records, support contacts, payment events and marketing interactions.

Problem. The initial churn model reports substantially higher risk for customers in one region. Managers propose reducing marketing investment there.

Analysis. A data audit shows that the region migrated to a new support platform. Support contacts were recorded with a different customer identifier, and many interactions could not be linked to subscriptions. The region also had a higher proportion of customers without recorded marketing consent, so marketing events were absent by design. The apparent regional pattern is partly a measurement and coverage problem.

Approach. Analysts create a verified identifier crosswalk, document the platform change, distinguish “no interaction recorded” from “no interaction occurred,” and include data-availability indicators. They compare results using linked records only and then conduct sensitivity analyses under alternative assumptions about unlinked contacts. The model is evaluated separately by region, tenure and consent status.

Outcome. The regional difference becomes smaller and less certain. The company delays reallocating investment, improves identity resolution and establishes a migration-quality checklist for future system changes.

Learning points. Missingness can be operationally patterned; a predictive model inherits weaknesses in its inputs; and data preparation can change a managerial conclusion. Quality assessment is therefore part of decision analysis, not merely technical housekeeping.

8. Governance, Ethics and Contemporary Practice

Data preparation involves responsibility toward data subjects and decision makers. Organizations should apply purpose limitation, data minimization, access control, retention limits and secure transfer. Personal data should be handled according to applicable law and organizational policy; sensitive attributes require heightened safeguards. De-identification reduces risk but is not an absolute guarantee, especially when datasets can be linked.

Fairness requires examining whether collection, missingness, labels or historical decisions disadvantage groups. Removing a protected attribute does not necessarily remove discrimination because proxy variables may remain. Analysts should document legitimate purposes, assumptions, data provenance and known limitations, and provide mechanisms for correction where feasible.

AI and automated data-preparation tools can accelerate schema matching, anomaly detection, text classification and code generation. They can also introduce fabricated mappings, privacy leakage, hidden transformations and automation bias. Human review, restricted environments, version control, test cases and reproducible logs remain necessary. A good principle is that every automated transformation should be inspectable, testable and reversible.

9. Best Practices and Limitations

Effective practice includes:

• begin with the decision and unit of analysis;
• create a data dictionary and quality plan before extensive modeling;
• profile raw data before modifying it;
• preserve raw data as read-only and create versioned working data;
• distinguish correction, exclusion, imputation and recoding;
• validate joins and reconcile totals;
• investigate outliers and missingness in context;
• record assumptions, code, timestamps and responsible owners;
• assess quality across relevant groups, not only overall;
• communicate uncertainty and limitations to decision makers.

No cleaning process can recover information that was never collected. Standardization can also remove meaningful variation, and imputation can make a dataset appear more complete than it really is. Quality is always relative to purpose, and a technically polished dataset may still be biased or ethically inappropriate.

Chapter Summary

Data are observations whose meaning depends on their entity, variable definition, source, time and measurement process. Structured, semi-structured and unstructured data present different preparation challenges, while primary and secondary sources differ in purpose and provenance. Nominal, ordinal, interval and ratio scales determine which comparisons and calculations are defensible.

Data quality includes validity, reliability, completeness, consistency, uniqueness, timeliness and representativeness. Bias can arise from coverage, non-response, selection, measurement, survivorship and historical allocation. A responsible workflow defines the decision and observational unit, documents metadata, collects data lawfully, profiles raw data, cleans and integrates transparently, transforms appropriately, and validates results through automated and human checks. Missing values and outliers require contextual investigation rather than automatic deletion. Governance, privacy, fairness and reproducibility are integral to professional data preparation.

Key Terms

Data: Recorded observations, measurements or events.

Metadata: Information describing data, including definitions, units, provenance and ownership.

Observational unit: The entity represented by one record.

Structured data: Data organized according to a predefined schema.

Semi-structured data: Data with organizational markers but a flexible schema.

Unstructured data: Data without a consistent tabular structure.

Nominal scale: Categories without a meaningful order.

Ordinal scale: Ordered categories without known equal intervals.

Interval scale: Equal intervals without a meaningful zero.

Ratio scale: Equal intervals with a meaningful zero.

Validity: The extent to which data represent the intended concept or rule.

Reliability: Consistency of measurement under similar conditions.

Completeness: Presence of required observations or values.

Consistency: Agreement among related values, definitions or systems.

Uniqueness: Absence of unintended duplicate records.

Bias: Systematic distortion in data or resulting conclusions.

Data profiling: Examination of structure, distributions, missingness and anomalies.

Data lineage: Traceable history of data sources and transformations.

Imputation: Replacement or estimation of missing values using a stated method.

Data leakage: Use of information in analysis or modeling that would not be available at the intended decision time.

Review Questions

Short Answer Questions

1. Distinguish primary data from secondary data and give one business example of each.
2. Why is a five-point satisfaction rating generally considered ordinal?
3. Explain the difference between validity and reliability.
4. Name three validation rules that can be applied to business data.
5. Why should an analyst profile data before cleaning it?

Long Answer Questions

1. Explain the four measurement scales and discuss how scale choice affects descriptive statistics.
2. Describe a complete workflow for preparing data from multiple operational systems.
3. Discuss the main sources of bias in business datasets and methods for detecting them.
4. Explain how missing values and outliers should be investigated and treated.
5. Evaluate the ethical and governance responsibilities involved in preparing customer data for analytics.

Analytical / Application Questions

1. A company joins a customer table to a transaction table and observes that total revenue has doubled. Diagnose possible causes and design checks to determine whether a many-to-many join caused row multiplication.
2. A survey has a 20% response rate, and respondents are disproportionately long-term customers. Explain the likely bias and propose a more defensible analysis strategy.
3. A retailer’s data contain dates in multiple formats, negative quantities, duplicate transaction IDs and missing customer identifiers. Design a documented preparation plan that preserves legitimate returns and reports uncertainty rather than silently deleting records.

Multiple Choice Questions with Answers

Question 1

Which measurement scale supports meaningful ratios because it has a true zero?

A. Nominal

B. Ordinal

C. Interval

D. Ratio

Correct Answer: D. Ratio

Question 2

A customer record contains a country code not included in the approved country reference list. Which quality issue is most directly indicated?

A. Invalidity

B. Timeliness

C. Reliability

D. Sampling error

Correct Answer: A. Invalidity

Question 3

What is the primary danger of an unvalidated many-to-many join?

A. It removes all missing values

B. It multiplies records and can inflate aggregates

C. It converts ordinal data to ratio data

D. It guarantees causal conclusions

Correct Answer: B. It multiplies records and can inflate aggregates

Question 4

Which statement best distinguishes reliability from validity?

A. Reliability concerns consistency; validity concerns whether the intended concept is measured

B. Reliability concerns privacy; validity concerns storage costs

C. Reliability applies only to numerical data; validity applies only to text

D. They are identical concepts

Correct Answer: A. Reliability concerns consistency; validity concerns whether the intended concept is measured

Question 5

A survey question asks respondents to rate service quality but actually asks only about price. This is primarily a problem of:

A. Construct validity

B. Uniqueness

C. Timeliness

D. Referential integrity

Correct Answer: A. Construct validity

Question 6

If missingness occurs more often because a new regional system cannot export a field, the missingness is best described as:

A. Random measurement noise only

B. Operationally patterned missingness

C. Proof that the field is unnecessary

D. A ratio-scale transformation

Correct Answer: B. Operationally patterned missingness

Question 7

Which action is most appropriate for a very large but genuine wholesale order detected as an outlier?

A. Delete it automatically

B. Replace it with the mean

C. Investigate it in business context

D. Convert it to a missing value without documentation

Correct Answer: C. Investigate it in business context

Question 8

What does a z-score transformation produce under its usual definition?

A. A value expressed relative to a mean in standard-deviation units

B. A guaranteed normally distributed variable

C. A categorical code

D. A measure of database uniqueness

Correct Answer: A. A value expressed relative to a mean in standard-deviation units

Question 9

Which practice best supports reproducibility?

A. Overwriting the raw extract after cleaning

B. Keeping transformation code, versions and quality logs

C. Removing all rejected records permanently

D. Allowing undocumented manual edits

Correct Answer: B. Keeping transformation code, versions and quality logs

Question 10

Why can removing a protected attribute fail to eliminate unfairness in an analytical model?

A. All models ignore variables

B. Proxy variables and historical patterns may still encode group membership

C. Protected attributes are always irrelevant

D. Missing values automatically become unbiased

Correct Answer: B. Proxy variables and historical patterns may still encode group membership