Data Collection, Management and Quality

Introduction and context

Business analytics is only as dependable as the data on which it is based. A sophisticated dashboard, predictive model or optimisation system can produce misleading decisions when records are incomplete, duplicated, incorrectly classified or no longer current. Data collection and management are therefore not clerical activities performed before “real” analysis; they are central analytical competencies.

This chapter examines the main environments in which organisational data is stored and used: databases, spreadsheets, data warehouses, data lakes and cloud platforms. It then presents a practical data lifecycle covering acquisition, integration, cleaning, transformation, documentation and governance. Finally, it develops a framework for assessing data accuracy, completeness, consistency, validity and timeliness.

The chapter uses the term data quality to mean the degree to which data is fit for a defined purpose. Quality is consequently contextual. A postcode may be essential for delivery planning but irrelevant to a product-profitability analysis. A monthly revenue figure may be timely enough for strategic planning but too old for real-time fraud detection.

Learning objectives

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

1. Distinguish databases, spreadsheets, data warehouses, data lakes and cloud data platforms.
2. Explain the stages of data acquisition, integration, cleaning and transformation.
3. Select appropriate storage and processing arrangements for business problems.
4. Assess data accuracy, completeness, consistency, validity and timeliness using practical measures.
5. Design a controlled data-quality process and communicate its limitations.
6. Recognise ethical, legal, security and governance responsibilities in data management.

1. The data lifecycle

A useful lifecycle is:

Define → acquire → store → integrate → profile → clean → transform → analyse → monitor → retain or dispose.

The lifecycle is iterative rather than strictly linear. Data profiling may reveal that the original collection instrument was poorly designed; analysis may expose a need for additional fields; and a change in regulation may require earlier records to be deleted or reclassified.

Defining the business purpose

Before collecting data, an analyst should identify:

• the decision or process that the data will support;
• the population, time period and level of detail required;
• required data fields and acceptable values;
• permitted uses and access rights;
• retention, security and deletion requirements; and
• quality thresholds and responsibilities.

This prevents “collect everything” behaviour, which increases cost, privacy exposure and the number of opportunities for error.

Data acquisition

Data may be acquired from internal operational systems, surveys, point-of-sale devices, websites, application programming interfaces (APIs), public datasets, partners, sensors and manually entered forms. Each source has characteristic risks. Transaction systems may contain reliable amounts but poorly maintained customer addresses. Surveys may provide useful attitudes but suffer from non-response and sampling bias. External datasets may have unclear definitions or licensing restrictions.

A data acquisition specification should document the source owner, collection method, frequency, fields, units, coding conventions, time zone, consent basis and expected quality. Metadata—data about data—is essential. For example, “sales” must be defined: does it mean gross invoices, net invoices after discounts, or cash received?

2. Data storage environments

No single storage technology is best for every purpose. The correct choice depends on structure, scale, speed, governance and analytical requirements.

Relational databases

A relational database stores structured data in tables containing rows and columns. Tables are connected through keys. A primary key uniquely identifies a record; a foreign key refers to a key in another table. For example, CustomerID may be the primary key in a Customer table and a foreign key in an Order table.

Relational design commonly uses normalisation: separating entities so that each fact is stored once and relationships are represented explicitly. Normalisation reduces update anomalies. If a customer address is repeated in every order record, changing the address may leave inconsistent historical rows. However, highly normalised structures may require many joins, so analytical systems sometimes use denormalised designs such as star schemas.

A database management system (DBMS) supports querying, transactions, permissions, backup and recovery. Transactional databases typically prioritise reliable, concurrent operational updates. The principles often summarised by ACID are:

• Atomicity: a transaction succeeds completely or not at all.
• Consistency: constraints remain satisfied before and after a transaction.
• Isolation: concurrent transactions do not interfere improperly.
• Durability: committed changes survive system failure.

These properties are particularly important for orders, payments and inventory.

Spreadsheets

Spreadsheets are flexible and accessible. They are useful for small-scale analysis, prototypes, controlled lists and scenarios where users need direct manipulation. They become risky when used as uncontrolled databases. Common weaknesses include duplicated versions, hidden formulas, inconsistent formats, accidental overwriting, unclear ownership and limited audit trails.

Good spreadsheet practice includes one clear rectangular data table per sheet, unique column names, no merged cells in the data area, explicit data types, protected formula cells, validation lists, documented assumptions, version control and independent checks. A spreadsheet should not be the only authoritative record for high-volume, regulated or mission-critical transactions.

Data warehouses

A data warehouse is a governed repository designed primarily for reporting and analysis. It typically combines data from multiple operational systems, preserves historical records and presents consistent business definitions.

A common warehouse design is a star schema. A central fact table stores measurable events such as sales, while dimension tables describe products, customers, stores and dates. The grain of a fact table states what one row represents—for example, “one product line on one invoice.” Defining grain prevents double counting.

Warehouse loading often follows ETL or ELT. In ETL, data is extracted, transformed and then loaded. In ELT, raw data is loaded first and transformed within the target platform. Warehouses are appropriate for repeatable management reporting, governed metrics and historical trend analysis, but they require investment in modelling, pipelines and governance.

Data lakes

A data lake stores large volumes of raw or lightly processed data, including structured tables, semi-structured JSON and unstructured text, images or audio. Its flexibility supports exploration, machine learning and future uses that were not known when the data was collected.

Flexibility creates risks. Without catalogues, ownership, schemas and quality controls, a lake can become a “data swamp”: data exists but cannot be found, interpreted or trusted. A mature lake therefore uses zones such as raw, curated and consumption layers, together with metadata, lineage, access controls and retention rules.

Cloud data platforms

Cloud platforms provide storage and computing as services over a network. They may offer managed relational databases, warehouses, object storage, streaming services and analytical tools. Benefits include elastic capacity, rapid deployment, managed backups and access to specialised services. Risks include variable costs, vendor dependence, misconfigured permissions, data residency concerns, outages and dependence on network connectivity.

Cloud adoption does not remove governance responsibilities. The organisation remains accountable for lawful processing, appropriate access, encryption, supplier oversight, backup strategy and recovery objectives. A shared-responsibility model usually means that the provider secures the underlying service while the customer must configure identities, data, applications and permissions correctly.

Comparing storage environments

3. Data integration

Data integration combines data from different sources so that it can be analysed consistently. The principal difficulties are not merely technical. Different systems may use different identifiers, definitions, time zones, currencies and levels of detail.

Schema and semantic integration

Schema integration aligns structures. One system may call a field Cust_ID, another CustomerNumber; one may store a date as DD/MM/YYYY, another as YYYY-MM-DD. Semantic integration aligns meaning. “Active customer” could mean a customer with an account, a customer who purchased in the last 12 months, or a customer who has not been deleted.

A data dictionary should define field names, meanings, types, units, allowed values, source systems, owners and sensitivity classifications. Master data management establishes authoritative versions of shared entities such as customers, products, suppliers and locations.

Identifiers and entity resolution

The safest integration uses a stable common identifier. When none exists, entity resolution attempts to determine whether records refer to the same entity using exact or probabilistic matching. Matching “A. Rahman, 12 King St” with “Amir Rahman, 12 King Street” may be reasonable, but automated matching can create false merges. High-impact decisions require thresholds, review queues and an audit record.

ETL and ELT pipelines

A robust pipeline normally includes extraction, staging, validation, transformation, loading and monitoring. It should be repeatable and preferably idempotent: running the same load again should not create unintended duplicates. Incremental loading can use a change timestamp, transaction log or watermarks. Pipeline controls should record row counts, rejected records, processing time, source version and error messages.

4. Data cleaning and transformation

Cleaning is the detection and treatment of defects; transformation changes data into a suitable analytical form. Neither should be performed invisibly. Every material decision should be documented because cleaning can alter results.

Common data defects

Typical defects include:

• missing values and unknown values;
• duplicate records;
• invalid dates, codes or ranges;
• inconsistent spelling and capitalisation;
• mixed units or currencies;
• incorrect data types;
• impossible combinations, such as a delivery date before an order date;
• outliers caused by errors or genuine exceptional events; and
• selection, response or measurement bias.

A missing value is not automatically zero. A zero may mean no activity, whereas a blank may mean not recorded, not applicable or deliberately withheld. These meanings must be distinguished where possible.

A practical cleaning sequence

1. Preserve the original source and create a controlled working copy.
2. Profile fields for type, range, frequency, missingness, uniqueness and distributions.
3. Standardise formats, such as dates, case, units and currency.
4. Validate values against domain rules and reference tables.
5. Identify duplicates using keys and carefully chosen matching rules.
6. Investigate anomalies with source owners; do not automatically delete them.
7. Impute, flag or exclude missing values according to the analytical purpose.
8. Recalculate derived fields from trusted components where appropriate.
9. Run post-cleaning checks and record all changes in a data-quality log.

Transformation examples

Transformations may include converting text to dates, converting kilograms to tonnes, aggregating transactions by month, joining dimensions, creating categorical bands, encoding variables, or calculating a margin. A margin calculation must define its basis. If revenue is 12,000 currency units and cost of goods sold is 7,200, gross margin is:

Gross margin percentage is:

The percentage assumes revenue is non-zero and that revenue and cost are measured for the same products, period and accounting basis. It should not be calculated by averaging store-level percentages unless the intended weighting is understood.

Missing-data treatment

Options include correcting the source, retaining a missing category, deleting records, imputing a value, or using a model-based method. The decision depends on why values are missing and how the variable is used. Mean imputation can reduce variation and distort relationships; carrying forward a previous value may be inappropriate when conditions change. Analysts should report the proportion treated and test whether conclusions change under reasonable alternatives.

5. Assessing data quality

Quality assessment should use explicit rules, measurable indicators and a named owner. The five dimensions in this chapter are related but distinct.

Accuracy

Accuracy is closeness to the real-world value or an accepted reference. A recorded customer address is accurate if it matches the customer’s verified address at the relevant time. Accuracy is assessed through source reconciliation, sampling, independent verification and exception investigation. A value can be complete and valid yet inaccurate: a date may have the correct format but represent the wrong event.

Completeness

Completeness concerns whether required data is present. A basic field completeness rate is:

For a field with 9,500 non-missing values among 10,000 required records, completeness is 95%. This metric assumes that “missing” is correctly identified and that every record in the denominator genuinely requires the field. Completeness can also be assessed at record, field, table and dataset levels.

Consistency

Consistency means that related representations and rules agree. Examples include the same product having one active category, totals agreeing with line items, and a customer not simultaneously marked both active and closed. A rule-consistency rate can be expressed as:

Rules must be defined before testing, and exceptions may be legitimate. A historical product category may differ from the current category without being an error.

Validity

Validity means conformity to an allowed format, range, type or business rule. A percentage outside 0–100 is invalid if the field represents a percentage. A date such as 31 February is invalid; a validly formatted date may still be inaccurate. Validation can be syntactic, such as checking a date pattern, or semantic, such as checking that dispatch occurs on or after order date.

Timeliness

Timeliness concerns whether data is available and current enough for its purpose. A simple latency measure is:

If a sale occurs at 14:05 and becomes available for analysis at 14:17, latency is 12 minutes. The calculation assumes both timestamps use the same time zone and clock standard. Timeliness is not identical to frequency: hourly data may still be stale if processing is delayed.

Quality scorecards

A scorecard may report each dimension separately rather than hiding weaknesses in one average. If an organisation combines dimensions into an index, it must state the weights and explain the decision. For example:

Here, sᵢ is the score for dimension i, wᵢ is its weight, weights are non-negative and sum to 1, and all scores use a comparable scale. A weighted index is a management device, not a substitute for examining individual failures. High accuracy cannot compensate for a legally unusable dataset.

6. Governance, ethics and security

Data governance assigns decision rights and accountability. Important roles include a data owner, who is accountable for business use and quality; a data steward, who manages definitions and operational controls; a custodian, who manages technical storage and access; and an analyst, who uses data within approved purposes.

Governance practices include catalogues, lineage, quality rules, retention schedules, access reviews, incident procedures, change control and documented definitions. Lineage shows where a field originated, how it was transformed and where it was used. It supports auditability and troubleshooting.

Ethical and legal responsibilities include purpose limitation, data minimisation, transparency, appropriate consent or other lawful basis, protection of sensitive information, fairness and respect for individual rights. Personal data should be collected only when justified, protected through least-privilege access and encryption, and retained no longer than necessary. De-identification reduces risk but is not automatically irreversible; combinations of fields can enable re-identification.

Analytics teams should consider whether poor-quality data disproportionately affects a group. Missing addresses may be more common in informal housing; language-dependent forms may reduce response from some communities. Data-quality remediation should therefore examine social as well as technical consequences.

Artificial intelligence can assist with anomaly detection, entity matching and automated classification, but it does not eliminate the need for human judgement. Models may reproduce source bias, create false matches or obscure why a correction was made. Automated actions should have confidence thresholds, human review for consequential cases, monitoring and rollback procedures.

7. Applied case: improving retail inventory data

The following is an illustrative case based on a fictional retailer.

Background

Northstar Retail operates 40 stores and an online channel. Management reports frequent stockouts despite apparently adequate inventory. Store systems, the warehouse system and the online catalogue use different product identifiers.

Problem

An initial review finds that 8% of online products have no matching warehouse identifier, 6% of store records use obsolete product codes, and some quantities are recorded in cases while others are recorded in individual units. A daily dashboard combines these sources without documenting the conversions.

Analysis

The team defines the grain of the inventory fact table as “one product at one location on one observation date.” It creates a product master with a stable product identifier, unit-of-measure conversions and effective dates for product-code changes. Profiling shows that missing mappings are concentrated among recently introduced products. Reconciliation also reveals that the online system updates every hour, whereas store feeds arrive overnight; apparent differences may therefore reflect timeliness rather than physical stock.

Quality rules are established:

• every active product-location record must have a valid master identifier;
• quantity must be non-negative unless an adjustment code is present;
• unit conversion must use the product master;
• inventory timestamps must include time zone;
• duplicate product-location-date records are rejected; and
• source row counts and rejected rows are reported after each load.

Approach and solution

The retailer builds a staged pipeline. Raw files are preserved; mappings and conversions occur in a curated layer; the warehouse stores conformed product and location dimensions. Rejected records are sent to a review queue rather than silently discarded. The dashboard labels data freshness and separates “unavailable” from zero stock.

Outcome

After four weeks, unmapped active products fall from 8% to 0.4%, and unit inconsistencies are reduced substantially. The stockout analysis now distinguishes genuine shortages from delayed feeds and catalogue errors. Management also discovers that several apparent stockouts were caused by products being recorded in cases in one system and units in another.

Learning points

The case demonstrates that data quality is multidimensional. Increasing completeness without resolving units would not produce accurate inventory. A shared identifier, explicit grain, metadata, lineage, exception handling and freshness indicators are more valuable than merely adding another dashboard.

8. Best practices and limitations

Effective practice begins with business definitions and ownership, not with a software purchase. Use controlled source systems for critical transactions, preserve raw data, make pipelines repeatable, validate at the point of entry and monitor quality continuously. Prefer correction at source to repeated downstream repair. Separate raw, curated and analytical layers, and retain an audit trail for material transformations.

Quality thresholds should reflect risk. A 99% valid postcode rate may be acceptable for a broad market analysis but unacceptable for a delivery-routing system if the remaining 1% causes costly failures. Sampling and automated rules complement one another: automated checks scale, while human investigation explains causes and legitimate exceptions.

No cleaning process can recover information that was never collected, and no storage platform can resolve ambiguous definitions automatically. Historical comparisons may remain difficult when business rules change. Data-quality measures can also create perverse incentives if staff are rewarded for suppressing exceptions rather than correcting them. Consequently, quality reporting should include context, unresolved issues and confidence, not only a single score.

Chapter Summary

Data management is a lifecycle that connects business purpose, acquisition, storage, integration, cleaning, transformation, analysis and monitoring. Relational databases support structured transactions; spreadsheets support flexible small-scale work; warehouses support governed historical reporting; lakes support diverse raw data; and cloud platforms provide elastic managed services but require careful configuration and governance.

Integration requires attention to schemas, meaning, identifiers, grain, units and time. Cleaning involves profiling, standardisation, validation, duplicate handling and transparent treatment of missing values. Data quality should be assessed through accuracy, completeness, consistency, validity and timeliness, using defined rules and measures. Governance establishes ownership, lineage, security, retention and accountability, while ethical practice addresses privacy, fairness and appropriate use. Reliable analytics depends less on impressive tools than on clear definitions, controlled processes and continuous investigation of exceptions.

Key Terms

Data accuracy: The extent to which data represents the correct real-world value.

Data completeness: The extent to which required data is present.

Data consistency: The extent to which related data agrees across records and systems.

Data dictionary: A documented description of fields, meanings, formats, units and ownership.

Data governance: The framework of authority, accountability, policies and controls for data.

Data lake: A repository for large volumes of raw or lightly processed data in varied formats.

Data latency: The elapsed time between an event occurring and its availability for use.

Data lineage: The record of data origin, transformations and destinations.

Data warehouse: A governed analytical repository integrating historical organisational data.

Database: An organised collection of data managed for storage, retrieval and update.

ETL: Extract, transform and load; a process for preparing data for a target system.

ELT: Extract, load and transform; a process that transforms data after loading it.

Entity resolution: The process of deciding whether records refer to the same entity.

Foreign key: A field that refers to a key in another table.

Grain: The precise meaning represented by one row in a fact table.

Master data: Shared core entities such as customers, products and suppliers.

Metadata: Information describing data, including its meaning, origin and format.

Primary key: A field or combination of fields that uniquely identifies a record.

Validity: Conformity to specified formats, ranges, types or business rules.

Review Questions

Short Answer Questions

1. What is the principal difference between a data warehouse and a data lake?
2. Why is defining the grain of a fact table important?
3. Distinguish accuracy from validity.
4. State two risks of using spreadsheets as uncontrolled databases.
5. What is data lineage, and why is it useful?

Long Answer Questions

1. Explain the data lifecycle from business definition through retention or disposal, including the role of iteration.
2. Compare relational databases, spreadsheets, warehouses, lakes and cloud platforms for business analytics.
3. Describe a controlled process for cleaning and transforming a customer dataset.
4. Explain how governance, privacy and security responsibilities influence data management.
5. Discuss why a single overall data-quality score may mislead managers.

Analytical / Application Questions

1. A dataset contains 18,000 required values, of which 17,280 are non-missing. Calculate the completeness rate and explain one limitation of this measure.
2. A retailer joins sales records to a product master and finds duplicate product identifiers, mixed units and delayed store feeds. Design an investigation and remediation plan using at least three quality dimensions.
3. An organisation wants to use automated entity matching to combine customer records. Recommend controls that reduce false matches, privacy risks and untraceable decisions.

Multiple Choice Questions with Answers

Question 1

Which storage environment is primarily designed for governed historical reporting and analysis?

A. Spreadsheet

B. Data warehouse

C. Operational point-of-sale terminal

D. Uncontrolled text file

Correct Answer: B. Data warehouse

Question 2

What does the grain of a fact table specify?

A. The encryption algorithm used by the table

B. The maximum number of rows permitted

C. What one row represents

D. The age of the oldest record

Correct Answer: C. What one row represents

Question 3

A recorded value follows the required date format but refers to the wrong transaction. Which quality dimension is primarily deficient?

A. Accuracy

B. Completeness

C. Timeliness

D. Consistency

Correct Answer: A. Accuracy

Question 4

A dataset has 17,280 non-missing required values out of 18,000. What is its completeness rate?

A. 90%

B. 94%

C. 96%

D. 98%

Correct Answer: C. 96%

Question 5

Which practice best reduces accidental spreadsheet errors?

A. Using multiple unlabelled copies

B. Merging cells throughout the data table

C. Protecting formula cells and documenting assumptions

D. Replacing all missing values with zero

Correct Answer: C. Protecting formula cells and documenting assumptions

Question 6

What is the main purpose of a primary key?

A. To identify a record uniquely

B. To store a currency conversion rate

C. To determine when a report is refreshed

D. To encrypt a database

Correct Answer: A. To identify a record uniquely

Question 7

A sale occurs at 14:05 and becomes available for analysis at 14:17. Assuming a common time zone, what is the data latency?

A. 2 minutes

B. 10 minutes

C. 12 minutes

D. 22 minutes

Correct Answer: C. 12 minutes

Question 8

Which situation is an example of semantic integration?

A. Changing a date from text to date type

B. Aligning different meanings of “active customer”

C. Compressing a data file

D. Backing up a database

Correct Answer: B. Aligning different meanings of “active customer”

Question 9

Why should rejected pipeline records usually be sent to a review queue rather than silently deleted?

A. To increase the number of duplicates

B. To preserve traceability and support investigation

C. To avoid defining validation rules

D. To guarantee that all records are accurate

Correct Answer: B. To preserve traceability and support investigation

Question 10

Which statement about cloud data platforms is most accurate?

A. They remove the need for access controls

B. They guarantee perfect data quality

C. They provide scalable services but still require customer governance

D. They can store only structured tables

Correct Answer: C. They provide scalable services but still require customer governance