Data Integration and Wrangling

Introduction

Data rarely arrives in a form suitable for analysis. A university may store student identity in one system, enrolment in another, assessment results in a third, and learning-platform activity in a cloud service. These sources may use different identifiers, naming conventions, time zones, data types, levels of detail, and definitions. Data integration and wrangling transform such heterogeneous inputs into a coherent analytical resource.

Data integration is the combination of data from multiple sources into a unified view or repository. Data wrangling is the broader process of inspecting, cleaning, restructuring, enriching, and validating data so that it can be analysed reliably. Integration is therefore concerned primarily with combining sources, whereas wrangling includes the preparation required before, during, and after combination.

Poorly integrated data can produce plausible but incorrect conclusions. A join at the wrong level of granularity may multiply revenue, a semantic mismatch may treat gross sales as net sales, and an unrecorded transformation may make results impossible to reproduce. At postgraduate level, integration should therefore be treated as both a technical and a governance problem.

Learning objectives

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

• distinguish ETL, ELT, federation, and data virtualization;
• identify schema conflicts, semantic differences, grain mismatches, and lineage requirements;
• apply joins, unions, aggregation, reshaping, parsing, and validation techniques;
• design practical approaches to record linkage and entity resolution;
• evaluate integration quality, risks, and ethical implications; and
• construct a traceable, reproducible data-wrangling pipeline.

1. The integration problem

A data source consists not only of values but also of metadata: field names, data types, units, permitted values, keys, time interpretation, update frequency, ownership, and business meaning. Two columns called customer_id may not identify the same population, and two fields called date may represent order date, shipment date, or date of entry.

1.1 Common heterogeneity

A reliable integration process begins with source profiling. Profiling examines row counts, null rates, distinct values, frequency distributions, duplicate rates, minimum and maximum dates, invalid formats, and relationships between fields. It should also capture source ownership, extraction time, and contractual or regulatory constraints.

1.2 Schema and semantic mapping

A schema mapping specifies how source attributes correspond to target attributes. A semantic mapping goes further by stating what the attributes mean and under what conditions they may be compared or combined.

For example, the following mapping is unsafe unless its assumptions are documented:

• amount in a point-of-sale system: gross price including tax;
• revenue in a finance system: recognized revenue excluding tax and returns.

The fields are numerically compatible but semantically different. A target measure such as net_revenue should be derived only after defining tax, discount, return, currency, and recognition rules.

1.3 Grain and functional dependence

The grain of a dataset is the entity represented by one row. Examples include one row per customer, order, order line, daily product, or customer-month. Grain must be stated explicitly before joining or aggregating.

Suppose an order table has one row per order and a payment table has several rows per order because payments may be split. Joining them produces one row per order-payment combination. If order value is summed after the join, orders with multiple payments are counted repeatedly.

A useful diagnostic is to identify the candidate key at each stage. If order_id is unique in table A but not in table B, then A-to-B is a one-to-many relationship. Aggregating B to one row per order_id before joining may be necessary, depending on the analytical question.

2. ETL, ELT, and integration architectures

2.1 ETL

Extract–transform–load (ETL) extracts data from source systems, transforms it in a staging or processing environment, and loads the result into a target warehouse or mart. ETL is useful when the target requires strict conformance, when sensitive data must be filtered before loading, or when transformation logic is stable and centrally governed.

A typical ETL sequence is:

1. Extract from operational databases, files, APIs, or message streams.
2. Land immutable raw copies in a controlled staging area.
3. Profile and validate the inputs.
4. Transform types, codes, keys, grain, and business measures.
5. Load conformed tables and publish quality metadata.

2.2 ELT

Extract–load–transform (ELT) loads relatively raw data into a scalable analytical platform and performs transformations there. ELT supports exploratory work, preserves source detail, and takes advantage of warehouse or lakehouse compute. It requires strong access controls, retention policies, documentation, and cost management because raw data may contain sensitive or unnecessary information.

ETL and ELT are not mutually exclusive. A modern platform may perform privacy filtering during extraction, raw landing during loading, and curated transformations inside the warehouse.

2.3 Other architectures

• Federation: queries remain in source systems and results are combined virtually.
• Data virtualization: a logical access layer presents multiple sources without necessarily copying all data.
• Change data capture: inserts, updates, and deletes are propagated incrementally rather than reloading complete tables.
• Streaming integration: events are integrated continuously, requiring event-time handling, late-arriving data policies, and idempotent processing.

The architecture should be selected according to latency, volume, source autonomy, privacy, reproducibility, cost, and analytical use case rather than fashion.

3. Core relational operations

3.1 Joins

A join combines rows according to a predicate, commonly equality of key fields.

• Inner join: retains only matched rows.
• Left outer join: retains every row from the left table and matched rows from the right.
• Right outer join: the reverse of a left join.
• Full outer join: retains matched and unmatched rows from both tables.
• Semi-join: retains left rows for which a match exists but does not append right attributes.
• Anti-join: retains left rows for which no match exists.

For a left join, unmatched right-side attributes are normally null. Null does not mean zero, false, or “not applicable”; its interpretation must be documented.

Join quality should be assessed by match rate, unmatched records, unexpected multiplicity, and key uniqueness. If a key is expected to be unique, test that expectation explicitly rather than assuming it.

3.2 Unions and append operations

A union appends rows from compatible relations. A union by position is risky because columns may be rearranged or have different meanings. Union by named columns is safer, provided that data types and semantics agree. Missing attributes should be represented explicitly and documented.

A union should not be used to combine customer records from two systems merely because both contain names and addresses. That problem is entity resolution, not row appending.

3.3 Aggregation

Aggregation changes grain. For a measure x grouped into groups G, the group sum is:

Here, S_g is the sum for group g and x_i is the value of record i. The formula assumes that records are at the intended grain, values use compatible units and currencies, and duplicate records have already been addressed.

Other common aggregates include count, distinct count, minimum, maximum, average, and percentile. A simple average of averages is generally invalid when group sizes differ. If subgroup means μ₁, μ₂, … have counts n₁, n₂, …, the combined mean is:

This weighted formula assumes that the subgroup observations represent the same measurement and that their counts are known and non-overlapping.

3.4 Reshaping

Wide data stores repeated measures in columns, such as sales_Jan, sales_Feb, and sales_Mar. Long data stores the measure in one column and the period in another. Long form is usually preferable for grouping, visualization, and statistical modelling.

• Melt or unpivot: wide to long.
• Pivot: long to wide, often using an aggregation when multiple values map to one cell.
• Explode: converts array or repeated values into separate rows.

Reshaping can alter grain and should therefore be followed by row-count and uniqueness checks.

4. Parsing and standardization

Parsing converts unstructured or weakly structured values into typed attributes. Common tasks include:

• splitting a full name into components while preserving the original;
• extracting fields from JSON, XML, or log strings;
• parsing dates with explicit locale and time-zone rules;
• converting currency text to decimal numeric values;
• normalizing phone numbers to an agreed country format;
• standardizing units such as kilograms and pounds; and
• separating product codes into meaningful components.

Parsing rules must avoid silent coercion. For example, the string 03/04/2025 is ambiguous: it may mean 3 April or 4 March. A robust pipeline records the source locale or rejects ambiguous values for review.

Currency conversion requires a rate date and rate type. If an amount is converted from currency c to a reporting currency r, a simplified conversion is:

where A_c is the original amount, R_{c→r} is the applicable exchange rate, and A_r is the converted amount. The calculation is valid only when the rate quotation convention, date, and treatment of fees are defined.

Standardization should preserve both raw and standardized values when auditability matters. A normalized postal code may support matching, but the original address is needed to investigate errors.

5. Record linkage and entity resolution

Entity resolution determines which records refer to the same real-world entity. Record linkage is often used for comparing records across files; entity resolution also includes creating and maintaining a canonical entity representation.

5.1 Deterministic linkage

Deterministic rules use exact or logically composed identifiers, such as a verified customer number or a combination of source system and account number. They are explainable and efficient but fail when identifiers are missing, changed, mistyped, or duplicated.

A hierarchy may apply the strongest available rule first:

1. verified government or institutional identifier;
2. source customer identifier with trusted source lineage;
3. normalized email plus date of birth, where legally permissible;
4. address and name combination; and
5. manual review for unresolved cases.

Rules should never use sensitive attributes casually. The purpose, legal basis, retention, and access controls must be established.

5.2 Probabilistic linkage

Probabilistic linkage compares fields using agreement evidence. Exact agreement on a rare surname may be more informative than agreement on a common surname. Similarity can be measured using edit distance, token similarity, phonetic encoding, or domain-specific rules.

A classical framework compares the probability of observing agreement patterns under a match and a non-match. For comparison pattern γ, a log-likelihood score can be expressed as:

M denotes that the pair is a true match, U denotes a non-match, and P represents the probability of the observed comparison pattern. Positive evidence favours a match; negative evidence favours a non-match. In practice, probabilities may be estimated from labelled pairs, clerical review, or carefully constructed reference data.

A production system should define match, possible-match, and non-match thresholds; monitor false positives and false negatives; and retain the evidence supporting each decision. Blocking, such as comparing only records sharing postal region or email domain, reduces computational cost but can miss true matches if the blocking key is wrong.

5.3 Canonical records and survivorship

After identifying multiple records as one entity, survivorship rules determine which values populate the canonical record. Possible rules include most recently verified, source with highest authority, non-null preference, or field-level trust ranking. These rules must be transparent because selecting one address or demographic value can affect eligibility, service, and reporting.

6. Data lineage, quality, and testing

Data lineage records where data came from, how it changed, when it was processed, and who or what performed the transformation. A useful lineage record includes source identifier, extraction timestamp, transformation version, input and output schema, responsible owner, quality results, and destination datasets.

Quality should be evaluated relative to intended use. Common dimensions include:

• Completeness: required values are present.
• Validity: values conform to permitted formats and domains.
• Accuracy: values represent the real-world phenomenon.
• Consistency: related sources do not contradict one another without explanation.
• Uniqueness: entities are not unintentionally duplicated.
• Timeliness: data is available and current enough for the decision.
• Integrity: keys and relationships are preserved.

Useful controls include schema tests, null thresholds, accepted-value tests, uniqueness tests, referential-integrity checks, reconciliation totals, distribution monitoring, and anomaly detection. A failed test should produce an actionable response: reject, quarantine, warn, or accept with a documented exception.

Reconciliation is especially valuable. If source order value for a period is 12.4 million monetary units and the curated table contains 12.1 million, the 0.3 million difference must be explained by returns, exclusions, timing, currency conversion, or defects. Agreement of totals alone does not prove correctness, but unexplained disagreement is a clear warning.

7. Worked example: integrating orders and payments

Assume an order table contains:

The payment table contains:

A direct join creates two rows for O1. Summing order_total after that join gives 100 + 100 + 150 = 350, although the true order total is 100 + 150 + 80 = 330. The inflation occurs because the join changed the grain from one row per order to one row per order-payment combination.

The safer method is to aggregate payments first:

Then left-join this one-row-per-order result to orders. O3 receives a null payment total, which may be interpreted as unpaid only after the business rule confirms that missing payment rows mean no recorded payment. A derived balance can then be calculated as:

where B_o is order balance, T_o is order total, and P_o is aggregated payment. For O1, B = 100 − 100 = 0; for O2, B = 150 − 150 = 0. O3 has no payment record and should be handled explicitly rather than silently treated as a numeric zero.

8. Applied case: a university retention data mart

The following is an illustrative case.

Background

A university wants to identify students who may need academic support. The proposed data mart combines the student information system, learning-management activity, library use, and finance records.

Problem

The systems use different identifiers. The student system stores one row per enrolment, the learning platform stores one row per activity event, and finance stores one row per charge or payment. Names and email addresses also change over time. A naïve join produces duplicated enrolments and misleading activity totals.

Analysis

The team first documented grain:

• student table: one row per student;
• enrolment table: one row per student-course-term;
• activity table: one row per event;
• finance table: one row per student-transaction.

They created a crosswalk from historical platform identifiers to the institutional student identifier. Activity was aggregated to student-course-week before joining to enrolment. Finance was aggregated to student-term, with access restricted to authorized staff because financial difficulty is sensitive information.

Approach and solution

The pipeline used raw immutable landing data, validated schemas, explicit UTC conversion for event timestamps, and a published data dictionary. Records without a confident identity match were placed in a review or unresolved table rather than forced into a student. The analytical mart retained source keys and transformation versions for lineage.

The team also separated descriptive indicators from decisions. Low platform activity was treated as a signal for human review, not as an automated diagnosis. Students were informed about support services and given a channel to correct inaccurate records.

Outcome

The university obtained a reproducible weekly dataset with fewer duplicate counts and traceable measures. Analysts could distinguish “no activity recorded” from “student did not participate,” a crucial distinction for fair interpretation.

Learning points

1. Grain must be documented before integration.
2. Aggregation should occur at the level required by the analytical question.
3. Unresolved identity matches should remain visible.
4. Sensitive data requires purpose limitation and access control.
5. A predictive or risk-oriented output must not be treated as an unquestionable decision.

9. Governance, ethics, and contemporary practice

Integration increases analytical power and therefore increases risk. Combining datasets may reveal sensitive characteristics that were not apparent in any single source. Governance should address lawful purpose, consent where applicable, data minimization, retention, access control, encryption, audit logging, and rights of correction.

Entity resolution can create false matches that merge two people, or false non-matches that fragment one person into several records. In high-impact contexts, these errors may affect admission, credit, employment, healthcare, or public services. Thresholds, review procedures, appeal mechanisms, and impact monitoring are therefore essential.

AI-assisted parsing and matching can accelerate wrangling, but generated transformations may contain undocumented assumptions or reproduce bias. Human review, test datasets, version control, reproducible code, and explicit model documentation remain necessary. Synthetic data can support development, but it does not automatically preserve the privacy or statistical properties of the original data.

Contemporary platforms increasingly combine lake storage, warehouse querying, catalogues, data contracts, orchestration, and observability. Data contracts define expected schemas, semantics, freshness, and quality responsibilities between producers and consumers. These tools improve reliability only when organizational ownership and remediation processes are clear.

Best-practice workflow

1. Define the analytical question and target grain.
2. Inventory sources, owners, constraints, and update patterns.
3. Profile structure, values, keys, duplicates, and missingness.
4. Write a semantic and schema mapping.
5. Preserve raw inputs and record extraction metadata.
6. Standardize types, units, codes, dates, and identifiers.
7. Resolve entities using explainable rules and review thresholds.
8. Aggregate before joining when necessary to control grain.
9. Apply transformations through version-controlled pipelines.
10. Test quality, reconcile totals, and quarantine defects.
11. Publish lineage, definitions, limitations, and access rules.
12. Monitor drift, late data, match quality, and downstream use.

Chapter Summary

Data integration and wrangling convert heterogeneous, operationally oriented data into an analyzable and governed resource. The central difficulties are not merely technical syntax; they include schema conflict, semantic ambiguity, incompatible granularity, uncertain identity, temporal inconsistency, and incomplete lineage. ETL transforms before loading, whereas ELT loads before transforming; both may be combined in modern architectures.

Joins, unions, aggregation, reshaping, and parsing are powerful operations that can silently alter grain or meaning. Record linkage requires transparent deterministic or probabilistic methods, while entity resolution additionally requires canonical records and survivorship rules. Quality controls should test completeness, validity, accuracy, consistency, uniqueness, timeliness, and integrity. Lineage makes transformations auditable and reproducible.

Professional practice also requires privacy protection, purpose limitation, access control, review of uncertain matches, and caution when integrated data informs consequential decisions. A strong pipeline is therefore technically correct, semantically explicit, testable, traceable, and ethically governed.

Key Terms

Data integration: Combining data from multiple sources into a coherent view or repository.

Data wrangling: Inspecting, cleaning, transforming, restructuring, and validating data for analysis.

ETL: Extract–transform–load architecture.

ELT: Extract–load–transform architecture.

Schema: The structural description of fields, types, relationships, and constraints.

Semantic mapping: Documentation of how data meanings correspond across sources.

Grain: The entity or event represented by one row.

Join: Relational operation that combines rows according to a matching condition.

Union: Operation that appends compatible rows from relations.

Aggregation: Reduction of multiple records into summaries at a specified grain.

Reshaping: Changing data between wide, long, nested, or repeated structures.

Parsing: Converting structured or semi-structured text into typed attributes.

Record linkage: Comparing records to determine whether they refer to the same entity.

Entity resolution: Identifying, consolidating, and maintaining representations of real-world entities.

Blocking: Restricting candidate record comparisons to improve linkage efficiency.

Survivorship rule: Rule selecting values for a canonical entity record.

Data lineage: Record of source, transformations, timing, ownership, and destination.

Data contract: Agreement specifying expected schema, semantics, quality, and responsibility.

Referential integrity: Correctness of relationships between keys in related datasets.

Data grain: The analytical level at which measures and records are represented.

Review Questions

Short Answer Questions

1. What is the difference between ETL and ELT?
2. Why must the grain of a dataset be documented before joining tables?
3. Distinguish a union from a join.
4. What is the purpose of a survivorship rule in entity resolution?
5. Name four dimensions of data quality.

Long Answer Questions

1. Explain how schema conflicts and semantic differences can produce incorrect analytical results. Use examples.
2. Compare deterministic and probabilistic record linkage, including their strengths and limitations.
3. Discuss how ETL, ELT, federation, and streaming integration differ in architecture and use case.
4. Explain why aggregation before joining can prevent measure inflation. Include a worked example.
5. Describe the role of data lineage, testing, and reconciliation in a production wrangling pipeline.

Analytical / Application Questions

1. An order table has one row per order and a shipment table has several rows per order. Design a pipeline to calculate total order value and shipment count without duplicating revenue. Identify the required tests.
2. Two institutional systems contain names, emails, addresses, and birth dates but no common identifier. Propose a linkage strategy, thresholds, review process, and privacy safeguards.
3. A dashboard reports a 20% increase in activity after integrating a new platform. Develop a diagnostic plan to determine whether the change reflects real behaviour, a grain mismatch, duplicate events, timezone effects, or a change in data coverage.

Multiple Choice Questions with Answers

Question 1

Which operation appends rows from compatible datasets while preserving their column structure?

A. Union

B. Inner join

C. Pivot

D. Entity resolution

Correct Answer: A. Union

Question 2

A payment table contains multiple rows per order. What is the safest first step before joining it to a one-row-per-order table when calculating order balances?

A. Duplicate the order table

B. Aggregate payments to one row per order

C. Drop all payment rows except the first

D. Convert payment amounts to text

Correct Answer: B. Aggregate payments to one row per order

Question 3

Which issue occurs when a field called revenue means gross sales in one source and recognized net revenue in another?

A. Syntactic conflict

B. Semantic conflict

C. Referential integrity

D. Blocking

Correct Answer: B. Semantic conflict

Question 4

In a left join, what normally happens to a left-side row with no matching right-side row?

A. It is always deleted

B. It is retained and right-side attributes are null

C. It is duplicated for every right-side row

D. It is converted into a union row

Correct Answer: B. It is retained and right-side attributes are null

Question 5

What does data lineage primarily document?

A. Only the final dashboard colour scheme

B. The origin, transformations, timing, and destination of data

C. Only the number of missing values

D. The encryption algorithm used by a source

Correct Answer: B. The origin, transformations, timing, and destination of data

Question 6

Which approach is most appropriate for an ambiguous date such as 03/04/2025?

A. Assume the month is always 03

B. Randomly choose a locale

C. Use documented locale metadata or quarantine the value

D. Convert it directly to a Boolean

Correct Answer: C. Use documented locale metadata or quarantine the value

Question 7

What is the principal purpose of blocking in record linkage?

A. To remove all unmatched records permanently

B. To reduce the number of candidate pairs compared

C. To guarantee that every pair is a match

D. To aggregate financial transactions

Correct Answer: B. To reduce the number of candidate pairs compared

Question 8

If subgroup means have different subgroup sizes, how should a combined mean generally be calculated?

A. Use the unweighted mean of subgroup means

B. Use the largest subgroup mean only

C. Use a count-weighted mean

D. Use the minimum subgroup mean

Correct Answer: C. Use a count-weighted mean

Question 9

Which practice best supports reproducibility in a wrangling pipeline?

A. Overwriting raw source files

B. Using undocumented manual corrections

C. Version-controlling transformations and recording metadata

D. Removing all source identifiers

Correct Answer: C. Version-controlling transformations and recording metadata

Question 10

Why can a direct join between one-row-per-order and one-row-per-payment tables inflate total order value?

A. The join changes currency automatically

B. Orders with multiple payments appear on multiple joined rows

C. The join removes all order identifiers

D. Aggregation always converts sums into averages

Correct Answer: B. Orders with multiple payments appear on multiple joined rows