Data Sources, Quality and Preparation

Introduction

Business intelligence is only as reliable as the data on which it is based. A dashboard may be visually persuasive, yet still mislead decision-makers if its source data are incomplete, duplicated, poorly defined, stale, or incorrectly transformed. Data preparation therefore is not a minor technical activity performed before “real” analysis; it is a central analytical and governance discipline.

This chapter examines how organisations identify data sources, assess data quality, profile and clean data, transform and integrate datasets, and validate reproducible ETL and ELT pipelines. The emphasis is on practical judgement: analysts must understand not only how to manipulate data, but also whether a value is meaningful, whether a transformation is defensible, and whether the resulting dataset supports a particular business decision.

Learning Objectives

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

• distinguish structured, semi-structured and unstructured data;
• evaluate internal and external data sources for fitness for purpose;
• define and measure important dimensions of data quality;
• perform data profiling and identify common defects;
• apply appropriate cleaning, transformation and integration techniques;
• distinguish ETL from ELT and design reproducible preparation workflows;
• validate data technically, statistically and from a business perspective;
• recognise ethical, security and governance risks in data preparation.

1. Data as a Business Asset

Data is a recorded representation of events, entities, measurements or observations. Information is data interpreted in context, while insight is an evidence-based understanding that can support action. For example, the value 125 is data; “125 units were sold in Store A on 14 March” is contextual information; “Store A requires replenishment before the weekend” is a possible business insight.

A data product is fit for purpose only relative to a defined use. A customer address that is adequate for regional sales analysis may be inadequate for parcel delivery. Similarly, daily revenue data may be suitable for operational monitoring but unsuitable for analysing transactions that occur within each hour.

A useful preparation process begins with a business question and a data contract. A data contract records the expected fields, definitions, formats, allowable values, refresh frequency, ownership and quality rules. It reduces ambiguity between data producers, engineers, analysts and decision-makers.

2. Classes and Sources of Data

2.1 Structured data

Structured data follow a predefined schema, commonly represented as rows and columns in relational tables. Each column has a defined name and data type, and relationships may be represented through keys.

Examples include:

• sales transactions in a relational database;
• general-ledger records;
• inventory tables;
• employee records;
• sensor readings stored in a tabular system.

Structured data are comparatively easy to query using SQL, but a rigid schema does not guarantee correctness. A table can be well organised and still contain duplicated customers, invalid dates or incorrect currency codes.

2.2 Semi-structured data

Semi-structured data contain organisational markers such as keys, tags or nested objects, but do not necessarily conform to one fixed relational schema. Common formats include JSON, XML, YAML, application logs and event messages.

For example, two JSON events may contain different optional attributes:

{"order_id":"A104", "customer_id":"C17", "total":84.50}
{"order_id":"A105", "customer_id":"C18", "total":91.00, "coupon":"SPRING"}

The second event has an additional attribute. Preparation must therefore address schema variation, nested structures, missing attributes and data types represented as text.

2.3 Unstructured data

Unstructured data do not have a consistent tabular schema. Examples include emails, contracts, photographs, audio recordings, videos, social-media posts and free-text customer comments. They often require natural-language processing, computer vision, speech recognition or manual classification before they can be used in conventional BI analysis.

Unstructured does not mean unusable. Text can be converted into sentiment categories, topics or entities; images can be classified; and documents can be indexed. However, such transformations introduce model uncertainty and potential bias, which must be documented.

2.4 Internal and external sources

Internal sources are generated within an organisation: enterprise-resource-planning systems, CRM platforms, point-of-sale systems, web analytics, spreadsheets, data warehouses and operational applications. They often provide detailed information but may reflect departmental definitions and legacy processes.

External sources include government statistics, market research, open-data portals, partner feeds, credit agencies, weather services and purchased demographic data. External data may broaden analysis but require scrutiny of licensing, methodology, update frequency, geographic coverage and comparability.

3. Data Quality

Data quality is multidimensional. A dataset is not simply “clean” or “unclean”; its quality depends on the intended use and the dimensions being assessed.

3.1 Core dimensions

• Accuracy: Does the value represent the real-world object or event correctly?
• Completeness: Are required values present? Completeness may be measured for fields, records or populations.
• Validity: Does a value conform to its permitted type, range, format or domain?
• Consistency: Do values agree across records, systems and time?
• Uniqueness: Are entities or events represented once when they should be?
• Timeliness: Is the data available and current when needed?
• Integrity: Are relationships, keys and referential constraints preserved?
• Accessibility: Can authorised users obtain and interpret the data?

These dimensions can conflict. A rapidly available data feed may be timely but incomplete. A highly standardised dataset may be valid in format but inaccurate in substance.

3.2 Quality measures

For a field, a simple completeness rate is:

The numerator counts values that are present and acceptable for the defined field; the denominator is the number of values expected under the measurement scope. The calculation assumes that the unit of analysis and definition of “missing” are explicit. A blank string, “unknown” and a database NULL may need separate treatment.

If 9,600 of 10,000 required customer email values are present, completeness is (9,600 ÷ 10,000) × 100% = 96%. This does not establish that the emails are accurate or deliverable.

A validity rate can be expressed as:

For example, a date may be syntactically valid but semantically invalid if an order date occurs after its recorded delivery date. Quality metrics should be accompanied by scope, sampling method, rule definitions and date of measurement.

3.3 Dimensions, thresholds and remediation

A business may set a rule that at least 98% of order records must have a valid product identifier. A threshold is useful only when it has an owner and an action. If the threshold is not met, the process should specify whether records are rejected, quarantined, corrected, enriched or reported with a warning.

Quality rules should distinguish:

1. critical failures, which prevent safe use;
2. repairable defects, which can be corrected using documented logic;
3. acceptable exceptions, which are retained with an explanation.

Deleting every unusual value is not quality management. An extreme transaction may be a genuine high-value purchase rather than an error.

4. Data Profiling

Data profiling is the systematic examination of a dataset’s structure, content and relationships before transformation. It answers questions such as:

• What tables, fields and data types exist?
• How many records and distinct values are present?
• Which fields are missing, constant or highly variable?
• Are there duplicate keys or unexpected categories?
• What are the minimum, maximum, mean, median and quantiles of numeric fields?
• Are dates within the expected period?
• Do foreign keys match reference tables?

A profiling report should include metadata, frequency distributions, missingness patterns, uniqueness rates, numeric summaries, example values and rule violations. Profiling is exploratory: it reveals what the data contain, but it does not by itself establish why a defect occurred.

4.1 Missing data

Missingness may be:

• MCAR (missing completely at random): missingness is unrelated to observed and unobserved values;
• MAR (missing at random): missingness is related to observed variables;
• MNAR (missing not at random): missingness depends on the unobserved value or an unmeasured process.

These categories are analytical assumptions rather than labels that can always be proven from the dataset. The appropriate response depends on context. Options include retaining a missing category, imputing a value, obtaining the source value, using a model-based method, or excluding records from a particular analysis.

Mean imputation can reduce variation and distort relationships, so it should not be used automatically. Any imputation must preserve a flag indicating that the value was imputed and must be documented in the data lineage.

4.2 Duplicates and entity resolution

Duplicates may be exact copies, repeated events caused by system retries, or records referring to the same entity with different spellings. “Acme Ltd”, “ACME LIMITED” and “Acme Limited.” may represent one organisation, but similar names can also belong to different legal entities.

Entity resolution may use deterministic rules, such as a government identifier, or probabilistic matching based on names, addresses and contact details. Automated matching should include confidence thresholds, review procedures and an audit trail. A false merge can be more damaging than leaving two records separate because it combines the activity of different entities.

4.3 Outliers and anomalies

An outlier is an observation unusual relative to a defined distribution; it is not necessarily an error. Investigation should consider units, data-entry mistakes, legitimate exceptional events, system changes and fraud indicators. Winsorisation, removal or transformation should be justified by the analytical purpose, not applied merely because a value is inconvenient.

5. Cleaning and Transformation

Cleaning changes data to correct, standardise or manage defects. Transformation changes representation or derives variables for analysis. Both should be reproducible and traceable.

5.1 Common cleaning operations

1. Type conversion: parse dates, numeric values and Boolean fields using explicit locale and format rules.
2. Standardisation: harmonise casing, whitespace, units, country codes and category labels.
3. Deduplication: define the record grain and use appropriate keys or matching rules.
4. Missing-value handling: distinguish unknown, not applicable, withheld and not yet received.
5. Range and domain checks: reject or quarantine values outside business constraints.
6. Text cleaning: remove unwanted control characters while preserving meaningful content.
7. Reference-data alignment: map source codes to approved master values.

A transformation must preserve meaning. Converting pounds to kilograms requires a documented conversion factor and unit metadata; it is not equivalent to merely renaming a column.

5.2 Derived fields and grain

The grain of a table is what one row represents. A sales table may have one row per invoice, invoice line, shipment or daily product summary. Many analytical errors arise when tables at different grains are joined without aggregation.

Suppose a customer table has one row per customer and an order-line table has many rows per customer. Joining them is appropriate when the intended output remains at order-line grain. If customer-level measures are later summed without care, customer attributes may be duplicated and totals inflated. Analysts should state the grain before joining or aggregating.

5.3 Standardisation versus normalisation

Standardisation usually means making representations consistent, such as converting “UK”, “U.K.” and “United Kingdom” to one approved code. In statistical modelling, standardisation can also mean rescaling a numeric variable, often using:

where x is an observation, μ is the mean, and σ is the standard deviation. This formula is applicable when the standard deviation is non-zero and the analytical method benefits from comparable scales. It must not be confused with data-format standardisation.

6. Data Integration

Integration combines data from multiple sources into a coherent analytical asset. It requires attention to schema, keys, semantics, time and grain.

6.1 Integration challenges

• Different systems may use different customer or product identifiers.
• The same field may have different meanings, such as “revenue” before or after tax.
• Time zones and daylight-saving changes may shift event dates.
• Currency and unit conventions may differ.
• Historical records may reflect changing organisational structures.
• Source systems may update records rather than preserve event history.

A conformed dimension provides a shared definition across subject areas. For example, a governed product dimension can connect sales, inventory and returns using approved product identifiers and categories.

6.2 Joins and referential integrity

An inner join retains only matching records; a left join retains every record from the left table and matching values from the right table; a full outer join retains unmatched records from both sides. The choice should follow the business question, not convenience.

Referential-integrity checks test whether every foreign key has a valid parent key. Unmatched records should be measured and investigated. Silently dropping them can bias results, especially if missingness is concentrated in a region, customer segment or time period.

6.3 Slowly changing information

Attributes such as customer address, sales territory or product category change over time. A current-value table may be adequate for present reporting but produce historically incorrect results when applied to old transactions. Historical tracking methods preserve the relevant version and effective dates. Analysts must decide whether a report should use the current attribute or the attribute valid when the event occurred.

7. ETL, ELT and Reproducibility

ETL means extract, transform and load: data are transformed before being loaded into the target platform. ELT means extract, load and transform: raw data are loaded first, then transformed within the warehouse or lakehouse. Modern cloud platforms often favour ELT because storage and compute can scale independently, but ETL remains appropriate where data must be reduced, masked or transformed before entering a controlled environment.

Figure: ETL transforms before loading; ELT loads before transforming.

A reproducible pipeline can be rerun with the same inputs and configuration to produce the same outputs, subject to documented changes in external systems. Reproducibility requires:

• version-controlled code and configuration;
• immutable or traceable raw inputs where feasible;
• explicit dependencies and execution order;
• stable reference data or recorded reference versions;
• logging of record counts, errors and processing times;
• automated tests and failure alerts;
• documented data lineage and ownership.

A practical layered design is:

1. Raw layer: source data retained with minimal alteration.
2. Standardised layer: types, naming conventions and basic formats aligned.
3. Conformed layer: entities, keys and business definitions integrated.
4. Serving layer: curated tables or semantic models designed for BI consumption.

8. Validation and Testing

Validation asks whether prepared data are suitable and whether transformations performed as intended. It should occur at several levels.

8.1 Schema tests

Schema tests verify column presence, data types, allowed nullability and expected structure. They detect breaking changes such as a source system renaming customer_id or changing a numeric field to text.

8.2 Rule and constraint tests

Examples include:

• order quantity must be greater than zero;
• delivery date must not precede order date;
• currency must belong to the approved code list;
• a product key must exist in the product dimension;
• a supposedly unique transaction identifier must be unique.

8.3 Reconciliation tests

Reconciliation compares totals between source and target, allowing for documented exclusions and timing differences. For example, loaded sales value should reconcile to source sales value by date and currency. A successful row-count comparison alone is insufficient because values may have shifted between records.

8.4 Statistical and visual checks

Distributions, missingness rates and category frequencies should be compared across pipeline stages. Sudden changes may indicate a broken filter, duplicated join or source-system change. Visual checks such as time-series plots and histograms can reveal anomalies that row-level tests miss.

8.5 Business acceptance

Subject-matter experts must confirm that definitions and outputs make sense. A technically valid dataset can still be wrong if “active customer” is defined differently from the business policy. Acceptance criteria should be agreed before production use.

9. Applied Case: Retail Sales Integration

Background

The following is an illustrative case. A regional retailer wants a daily profitability dashboard combining point-of-sale transactions, an online store, product master data and a promotions spreadsheet.

Problem

The sources use different product codes, currencies and date formats. Online transactions may be resent after network failures, and the promotions file contains manually entered discount percentages. Initial dashboard revenue is 7% higher than the finance report.

Analysis

Profiling reveals that the online feed contains repeated transaction identifiers. A join between transaction lines and promotions also multiplies rows because the promotions file contains overlapping date ranges for the same product. In addition, online revenue is recorded in euros while the finance report is in the group reporting currency.

Approach and solution

The team defines the grain as one row per transaction line. It retains raw files, assigns ingestion timestamps and creates a source-specific transaction key. Duplicate online events are removed only when the transaction identifier, line number, amount and event timestamp meet the documented retry rule.

Product codes are mapped through a governed cross-reference table. Currency conversion uses the approved daily exchange-rate table, with the rate date and source retained. Promotion records are validated so that each product-date combination resolves to one approved promotion; overlapping records are sent to an exception queue rather than arbitrarily selected.

Automated tests check uniqueness, referential integrity, date logic, currency coverage and reconciliation by source and day. The final dashboard distinguishes gross sales, discounts, returns and net sales rather than presenting one ambiguous “revenue” measure.

Outcome

The apparent 7% overstatement is traced to duplicated online events and promotion joins. After correction, remaining differences are explained by finance cut-off timing. The pipeline produces a daily quality report and prevents publication when critical reconciliation rules fail.

Learning points

The case demonstrates that integration errors are often semantic and relational rather than merely formatting problems. Defining grain, preserving lineage, controlling reference data and involving finance stakeholders are as important as writing transformation code.

10. Governance, Ethics and Security

Preparation processes can expose personal, confidential or commercially sensitive information. Organisations should apply data minimisation, purpose limitation, access control, retention schedules and appropriate encryption. Personally identifiable information should be masked or pseudonymised where full identity is not required. Pseudonymisation reduces direct identification risk but does not make data anonymous if re-identification remains possible.

Data quality decisions can create fairness risks. Removing records with incomplete demographic fields may disproportionately exclude a group. Imputation can conceal unequal service access. Entity matching may produce more errors for names or addresses represented less frequently in the training data. Quality monitoring should therefore examine error rates across relevant groups where lawful and ethically justified.

Governance should assign owners for data domains, definitions, quality rules and remediation. Audit logs should record who changed a rule, when it changed and which outputs were affected. Regulatory obligations vary by jurisdiction and sector, so organisations must align preparation practices with applicable privacy, records-management, financial-reporting and industry requirements.

11. Contemporary Developments and Best Practice

Cloud warehouses, lakehouses and streaming platforms increasingly support large-scale preparation, but technology does not remove the need for conceptual discipline. Data observability tools monitor freshness, volume, schema changes and distribution drift. Data catalogues document definitions and lineage. Machine-learning assistance can suggest mappings, classify text or detect anomalies, but generated recommendations require human review, testing and provenance.

Recommended practice is to:

1. begin with the decision, population, grain and definitions;
2. profile before cleaning;
3. preserve raw data and never overwrite evidence without traceability;
4. make transformations explicit, modular and version controlled;
5. distinguish correction from imputation and assumption;
6. validate both records and business totals;
7. quarantine uncertain records rather than silently discarding them;
8. monitor quality continuously after deployment;
9. document ownership, lineage, exceptions and limitations;
10. review privacy, fairness and access implications throughout the lifecycle.

Chapter Summary

Data sources may be structured, semi-structured or unstructured, and may originate internally or externally. Their usefulness depends on fitness for purpose, not simply on format or technical accessibility. Data quality includes accuracy, completeness, validity, consistency, uniqueness, timeliness, integrity and accessibility.

Profiling reveals structure, missingness, duplicates, anomalies and relationships before transformation. Cleaning and transformation require explicit rules, careful treatment of missing values, attention to table grain and preservation of meaning. Integration depends on reliable keys, conformed definitions, temporal awareness and referential-integrity controls.

ETL transforms before loading, whereas ELT loads before transforming; both can be effective when supported by version control, lineage, logging and testing. Validation should combine schema checks, business rules, reconciliations, statistical review and subject-matter acceptance. Ethical and governance controls are essential because preparation can affect privacy, fairness and the credibility of organisational decisions.

Key Terms

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

Completeness: Extent to which required data are present.

Data contract: Agreement specifying expected data structure, meanings, quality rules and responsibilities.

Data grain: The real-world object or event represented by one row.

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

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

Data quality: Fitness of data for a defined use across relevant quality dimensions.

ELT: Extract, load and transform.

ETL: Extract, transform and load.

Entity resolution: Process of determining which records refer to the same entity.

Foreign key: Field that refers to a key in another table.

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

Integrity: Preservation of valid relationships and constraints among data.

Referential integrity: Condition in which foreign-key references correspond to valid parent keys.

Schema: Formal description of fields, types, relationships and constraints.

Semi-structured data: Data organised by tags, keys or nesting without a fully fixed tabular schema.

Structured data: Data organised according to a predefined schema, commonly rows and columns.

Unstructured data: Data without a consistent predefined tabular structure.

Validation: Testing whether data and transformations satisfy technical and business requirements.

Review Questions

Short Answer Questions

1. Distinguish structured, semi-structured and unstructured data, giving one example of each.
2. Why is data quality relative to fitness for purpose?
3. What is the difference between ETL and ELT?
4. Why must the grain of a table be identified before joining datasets?
5. Name four dimensions of data quality.

Long Answer Questions

1. Explain the stages of a reproducible data-preparation pipeline, from source ingestion to BI serving.
2. Discuss the causes and treatment of missing data, including the risks of automatic mean imputation.
3. Compare deterministic and probabilistic entity resolution and explain when each may be appropriate.
4. Explain how schema, rule, reconciliation and business-acceptance tests complement one another.
5. Evaluate the governance and ethical risks associated with using personal and unstructured data in BI.

Analytical / Application Questions

1. A sales dashboard is 12% higher than the finance system. Design a profiling, reconciliation and integration investigation that could identify the cause without deleting source data.
2. A customer table contains names, addresses and email addresses but no stable customer identifier. Propose an entity-resolution process, including confidence thresholds, review controls and measures of error.
3. An organisation loads raw clickstream data into a cloud warehouse and transforms it there. Recommend an ELT quality-control design covering schema drift, duplicate events, late-arriving data, privacy and monitoring.

Multiple Choice Questions with Answers

Question 1

Which characteristic most directly distinguishes semi-structured data from structured data?

A. It contains no information about relationships.

B. It uses organisational markers such as keys or tags without requiring one fixed tabular schema.

C. It can only be stored in spreadsheets.

D. It is always generated by sensors.

Correct Answer: B. It uses organisational markers such as keys or tags without requiring one fixed tabular schema.

Question 2

A dataset contains 9,600 non-missing required values out of 10,000 expected values. What is its completeness rate?

A. 0.96%

B. 4%

C. 96%

D. 104%

Correct Answer: C. 96%

Question 3

Which action best preserves reproducibility in a data pipeline?

A. Overwriting the raw source after cleaning

B. Changing transformation rules manually in production

C. Keeping version-controlled code and documenting inputs and configurations

D. Deleting records that fail silently

Correct Answer: C. Keeping version-controlled code and documenting inputs and configurations

Question 4

What does table grain describe?

A. The physical storage size of a table

B. The real-world object or event represented by one row

C. The number of columns in a table

D. The colour scheme of a dashboard

Correct Answer: B. The real-world object or event represented by one row

Question 5

Which join retains every record from the left table and matching records from the right table?

A. Inner join

B. Left join

C. Cross join

D. Full join only

Correct Answer: B. Left join

Question 6

Which statement about an outlier is correct?

A. Every outlier is a data-entry error.

B. Outliers must always be deleted.

C. An outlier is unusual relative to a defined distribution but may be legitimate.

D. Outliers occur only in categorical data.

Correct Answer: C. An outlier is unusual relative to a defined distribution but may be legitimate.

Question 7

In ELT, when does transformation generally occur?

A. Before extraction

B. Between extraction and loading only

C. After data are loaded into the target platform

D. Only after dashboard publication

Correct Answer: C. After data are loaded into the target platform

Question 8

Which test checks whether every foreign key has a corresponding parent key?

A. Referential-integrity test

B. Visualisation test

C. Mean-imputation test

D. Compression test

Correct Answer: A. Referential-integrity test

Question 9

Why can joining tables at different grains inflate totals?

A. The join changes all dates to text.

B. One entity may be repeated across multiple matching rows.

C. The database automatically removes all duplicates.

D. Foreign keys cannot be used in joins.

Correct Answer: B. One entity may be repeated across multiple matching rows.

Question 10

Which practice best addresses privacy risk when identity is not needed for analysis?

A. Publish all identifying fields

B. Increase the number of copied datasets

C. Mask or pseudonymise personal identifiers and restrict access

D. Replace all missing values with names

Correct Answer: C. Mask or pseudonymise personal identifiers and restrict access