Data Integration, ETL/ELT and Data Quality

Introduction

A data warehouse is valuable only when its data is sufficiently complete, consistent, timely, understandable, and fit for analysis. Operational systems are designed for transactions, departmental applications, regulatory reporting, web interactions, sensors, and external data feeds. These sources differ in structure, naming conventions, keys, update frequencies, business rules, and reliability. Data integration is the discipline of bringing such heterogeneous data together so that it can support trustworthy analytical decisions.

This chapter examines the source-to-target pipeline: discovery and extraction, profiling, cleansing, transformation, integration, change-data capture, loading, validation, reconciliation, metadata management, and master-data management. It also considers the architectural choice between ETL and ELT and the governance, security, and ethical controls required in professional environments.

Learning Objectives

After studying this chapter, a learner should be able to:

1. Design an end-to-end ETL/ELT pipeline for heterogeneous sources.
2. Profile data and diagnose structural, semantic, and quality problems.
3. Apply cleansing, standardisation, transformation, and integration techniques.
4. Compare full extraction, incremental extraction, and change-data capture.
5. Select appropriate loading strategies and validate source-to-target results.
6. Define data-quality dimensions, measures, thresholds, and remediation processes.
7. Explain the roles of metadata, master data, lineage, and governance.
8. Evaluate practical, ethical, security, and managerial implications of data integration.

1. Data Integration as an Information System Capability

Data integration combines data from multiple sources into a coherent representation. Integration may be physical, as in a warehouse or lakehouse, or logical, as in a federated query layer. The central problem is not merely moving records. It is preserving meaning while resolving differences in structure, identity, time, granularity, and business definitions.

Common heterogeneity includes:

• Syntactic heterogeneity: CSV, JSON, XML, relational tables, APIs, messages, and documents use different formats.
• Structural heterogeneity: One system stores an address in one field while another separates street, city, and postal code.
• Semantic heterogeneity: customer_status = A may mean “active” in one system and “approved” in another.
• Key heterogeneity: A customer may be identified by an account number, email address, national identifier, or a system-specific surrogate key.
• Temporal heterogeneity: Sources may record event time, transaction time, processing time, or only the latest state.
• Granularity heterogeneity: One source records invoice lines, another records invoice totals, and a third records monthly summaries.

A robust integration design begins with business meaning. Technical mappings without agreed definitions can create a warehouse that is internally consistent but analytically wrong.

2. ETL and ELT Architectures

2.1 ETL

In extract–transform–load (ETL), data is extracted from sources, transformed in a staging or integration environment, and then loaded into the target warehouse. ETL is useful when transformation must occur before data enters a governed warehouse, when source data requires substantial cleansing, or when the target platform has limited transformation capacity.

A typical ETL sequence is:

1. Extract source data into a controlled landing or staging area.
2. Profile and validate the extract.
3. Standardise types, formats, codes, and time zones.
4. Match entities and apply business transformations.
5. Validate transformed data.
6. Load dimensions and facts using dependency order.
7. Reconcile counts, amounts, keys, and control totals.
8. Publish operational metadata and quality results.

2.2 ELT

In extract–load–transform (ELT), data is extracted and loaded into a scalable target platform before transformations are applied there. ELT is common in cloud warehouses and lakehouses because elastic compute, columnar storage, and distributed processing can transform large volumes close to the data.

ELT does not eliminate data quality or governance. It changes where processing occurs. Raw data should remain protected, documented, access-controlled, and traceable. Transformation logic should be versioned and testable.

The choice is architectural rather than ideological. Data volume, latency, regulatory requirements, skills, cost, security, and platform capabilities should determine the design.

3. Source Discovery, Extraction, and Staging

Source discovery identifies systems, owners, interfaces, data contracts, update schedules, keys, retention rules, and known defects. A source inventory should record technical and business metadata, including the source system, table or endpoint, field definitions, data owner, extraction method, frequency, expected volume, and sensitivity classification.

Extraction methods include:

• scheduled database queries;
• database replication or log reading;
• file transfer and object storage ingestion;
• REST or streaming APIs;
• message queues and event streams;
• application exports; and
• vendor-managed connectors.

A staging area should preserve the received extract, extraction timestamp, source identifier, file or batch identifier, and checksum where appropriate. Raw data should generally be immutable. Reprocessing a failed transformation should not require asking the source system to reproduce historical data.

Full extraction reads the complete source population. It is simple but can be expensive and may stress operational systems. Incremental extraction reads only new or changed records. It requires a reliable change indicator such as an update timestamp, increasing sequence number, transaction log, or source-provided watermark.

A watermark records the highest safely processed source position. If W_t is the last committed watermark at run t, an incremental process should select records beyond that position, while allowing for late-arriving or corrected records. A practical design often uses an overlap window rather than relying on exact timestamps, because clock precision, transaction ordering, and late commits can otherwise cause missed rows.

4. Profiling and Data Understanding

Data profiling is the systematic examination of data content, structure, relationships, and patterns before transformation. It converts assumptions into evidence.

Important profiling activities include:

• Schema profiling: data types, lengths, nullable fields, and unexpected columns.
• Column profiling: minimum, maximum, distinct count, frequency distribution, and missingness.
• Pattern profiling: dates, telephone numbers, postal codes, identifiers, and free-text patterns.
• Key profiling: uniqueness, duplicate rates, and candidate keys.
• Relationship profiling: orphan foreign keys, referential integrity, and cardinality.
• Temporal profiling: freshness, event gaps, future dates, and irregular arrival patterns.
• Value profiling: invalid codes, outliers, negative quantities, and impossible combinations.

For a column with N records and m missing values, the missingness rate is:

The formula assumes that every record has been included in the profiling population and that “missing” has a clearly defined meaning. Blank strings, nulls, sentinel values such as 9999, and “not applicable” may need separate categories.

For a rule evaluated over N records, with c records passing, a simple validity rate is:

This measure is meaningful only when the rule, population, and evaluation time are specified. A validity rate of 99% may be unacceptable for a critical financial field and excellent for a noisy optional marketing attribute.

Profiling should be repeated after transformation. A pipeline can remove invalid records without correcting the underlying source problem; therefore, source-quality trends and target-quality outcomes should be reported separately.

5. Cleansing and Standardisation

Cleansing changes data to satisfy agreed quality rules while preserving traceability. Common operations include:

• trimming whitespace and normalising character encoding;
• standardising case and punctuation;
• converting dates to a canonical representation;
• resolving time zones and daylight-saving rules;
• converting units, currencies, and decimal precision;
• mapping source codes to governed reference values;
• validating ranges and formats;
• handling missing, unknown, and not-applicable values;
• correcting or quarantining invalid records; and
• deduplicating entities or transactions.

Cleansing must distinguish correction from suppression. Deleting a suspicious transaction may improve a dashboard while destroying evidence. Prefer quarantine, exception reporting, and governed remediation when the record may be important.

A transformation specification should state source field, target field, rule, lookup table, default behaviour, error action, owner, and effective date. For example, a country-code mapping should specify whether ISO alpha-2, alpha-3, or numeric codes are expected and how historical codes are handled.

Entity Resolution and Deduplication

Entity resolution determines whether records refer to the same real-world entity. Deterministic matching uses exact or carefully normalised identifiers. Probabilistic matching uses several attributes and produces a similarity score. A conceptual matching score may combine evidence from name, address, telephone, and email, but weights and thresholds must be validated against labelled examples.

False matches merge different people or organisations; missed matches leave duplicates. Both errors can materially distort customers, revenue, risk, and regulatory reporting. High-impact matches should support human review, confidence bands, survivorship rules, and an audit trail.

6. Transformation and Integration Design

Transformation converts source representations into a target model. It can involve projection, filtering, joins, aggregation, derivation, code translation, unit conversion, and history management.

A dimensional warehouse commonly contains:

• Fact tables, which record measurable events at a declared grain; and
• Dimension tables, which describe entities such as customer, product, location, and date.

The grain must be stated before measures are designed. For example, “one row per product per order line” is materially different from “one row per order.” Joining facts at incompatible grains can double-count measures.

Surrogate keys separate warehouse identity from unstable source identifiers. A dimension lookup should resolve a source business key to the appropriate warehouse version. Unknown or not-yet-identified members should normally use controlled placeholder keys rather than null foreign keys, while retaining the unresolved source identifier for later correction.

Slowly Changing Dimensions

A Type 1 change overwrites an attribute and does not retain its previous value. It is suitable when history is irrelevant or correction of an error is required. A Type 2 change creates a new dimension version, usually with an effective-start timestamp, effective-end timestamp, and current-row indicator. It is suitable when historical analysis must reflect the attribute value that applied at the time.

Type 2 processing must prevent overlapping effective periods for the same business key. Late-arriving changes require a stated policy: revise prior history, insert a backdated version, or record the change prospectively. The policy should reflect business and regulatory requirements.

7. Change-Data Capture

Change-data capture (CDC) identifies inserts, updates, and deletes after an initial load. Major approaches include:

• Timestamp-based CDC: selects records whose update timestamp exceeds a watermark. It is simple but vulnerable to missing timestamps, clock issues, and updates that do not modify the tracked field.
• Trigger-based CDC: writes changes to an audit table through database triggers. It can be precise but adds source-system overhead.
• Log-based CDC: reads database transaction logs. It generally has low application impact but requires platform-specific tooling and careful handling of schema changes.
• Snapshot comparison: compares successive extracts. It is widely applicable but potentially expensive and may not reveal the order of changes.
• Event-based CDC: consumes business events. It supports near-real-time integration but requires reliable event contracts and replay design.

CDC pipelines must define delete semantics, ordering, duplicate handling, replay, retention, and failure recovery. Idempotency is essential: reprocessing the same change should not create an unintended second effect. This can be achieved using stable event identifiers, merge operations, and processed-event records.

8. Loading Strategies and Pipeline Control

Loading may be batch, micro-batch, or streaming. Batch loading is operationally simple and appropriate for daily or periodic reporting. Streaming reduces latency but increases complexity in ordering, late data, state management, and recovery.

Common loading patterns include append, overwrite, upsert, merge, and partition replacement. A fact table may be append-oriented, while a dimension may require merge and history logic. Load dependencies should be explicit: reference data and dimensions are generally resolved before fact foreign keys are finalised.

A production pipeline should have:

• run identifiers and batch timestamps;
• control totals and source watermarks;
• checkpointing and restart capability;
• retry policies with bounded backoff;
• dead-letter or quarantine handling;
• schema-drift detection;
• operational logs and alerts; and
• separation between technical failure and data-quality failure.

A technically successful job can still be a business failure if it loads zero sales, duplicates revenue, or imports an incomplete source extract.

9. Data-Quality Dimensions and Measurement

Data quality is fitness for intended use, not an absolute property. Common dimensions include:

A composite quality score can be useful, but it must not conceal critical failures. If dimension scores are q₁, q₂, …, q_k and weights are w₁, w₂, …, w_k, with non-negative weights summing to 1, a weighted score is:

Here each q_i must use a comparable scale, such as 0 to 1, and w_i expresses business importance. The score is an indicator, not proof of fitness. A high overall score can mask a zero score on a legally critical attribute; therefore, hard thresholds and critical-rule gates should accompany aggregation.

Quality rules should identify the subject, population, condition, threshold, severity, owner, and action. For example: “For active accounts, account identifier must be non-null and unique; failure above 0.1% blocks publication.”

10. Validation, Reconciliation, and Testing

Validation tests whether data conforms to technical and business expectations. Typical tests include schema checks, data-type checks, domain checks, null checks, uniqueness checks, referential-integrity checks, range checks, and reconciliation checks.

Reconciliation compares source and target populations and measures. If source record count is S and target count is T, the count difference is:

For monetary or additive measures, the amount difference is:

These comparisons require aligned filters, currency, time period, grain, and treatment of rejected records. A non-zero difference is not automatically an error: aggregation, deduplication, late-arriving data, and intentional exclusions may explain it. The explanation must be documented.

Testing should occur at several levels:

1. Unit testing: individual mappings and transformations.
2. Data-driven testing: representative, boundary, null, duplicate, and malformed cases.
3. Integration testing: interaction among sources, pipelines, and targets.
4. Regression testing: confirmation that changes do not break established behaviour.
5. Performance testing: throughput, latency, concurrency, and recovery.
6. User-acceptance testing: confirmation that business outcomes and definitions are correct.

11. Metadata, Lineage, and Master Data

Metadata describes data. Technical metadata includes schemas, types, partitions, and jobs. Business metadata includes definitions, owners, classifications, and approved terms. Operational metadata includes run times, row counts, failures, freshness, and quality results.

Data lineage records the path from source through transformations to reports or analytical products. It supports impact analysis, audit, incident investigation, regulatory response, and trust. Lineage should capture both technical movement and semantic transformations, such as currency conversion or exclusion rules.

Master data management (MDM) governs shared entities such as customer, supplier, product, employee, and location. An MDM programme typically requires:

• an agreed canonical model;
• identity resolution and survivorship rules;
• stewardship roles;
• reference-data governance;
• golden-record publication or synchronization;
• versioning and history; and
• dispute and exception workflows.

MDM is not simply a deduplication tool. It is an organisational capability that assigns authority, accountability, and lifecycle control to core business entities.

12. Applied Case: Illustrative Retail Integration Programme

Background

A fictional national retailer operates point-of-sale stores, an e-commerce platform, a loyalty application, and a supplier system. Executives report that online and store revenue disagree, customer counts are inflated, and product profitability is unstable.

Problem Analysis

Profiling reveals that store sales arrive daily while e-commerce sales arrive hourly. Product identifiers differ across systems, refunds are represented as negative lines online but as separate transactions in stores, and customer email addresses contain inconsistent case and whitespace. The warehouse also joins order-level shipping costs to line-level sales, duplicating costs when an order has several lines.

Approach

The programme establishes a canonical product master and maps source product codes to warehouse surrogate keys. It defines the fact grain as one row per order line, with refunds represented by signed quantities and amounts. Order-level costs are allocated under an approved business rule or stored in a separate order-grain fact. Log-based CDC is introduced for e-commerce orders, while store extracts use a watermark and a two-hour overlap window.

A staging layer preserves raw files and source metadata. Profiling rules identify invalid product codes, missing order identifiers, duplicate events, and unexpected currency values. Type 2 history is applied to product category assignments because management needs historical profitability by the category applicable at the time of sale. Data-quality gates block publication when revenue reconciliation exceeds the agreed threshold.

Outcome

The retailer obtains one governed revenue definition, traceable product mappings, and separate measures at compatible grains. Customer counts become more reliable after identity resolution, while unresolved matches are quarantined for stewardship review. The project does not eliminate all source defects; instead, it makes their frequency, ownership, and business impact visible.

Learning Points

The principal lesson is that integration is a socio-technical process. Correct SQL cannot resolve an undefined revenue policy, conflicting ownership, or an ambiguous product identity. Grain, definitions, lineage, and quality thresholds must be agreed before large-scale automation.

13. Governance, Security, Ethics, and Contemporary Practice

Integrated platforms increase the risk associated with personal, financial, health, and commercially sensitive data. Controls should include least-privilege access, encryption in transit and at rest, masking or tokenisation, retention limits, segregation of duties, audit logging, and secure development practices. Sensitive raw zones should not become an uncontrolled data repository.

Privacy principles require purpose limitation, data minimisation, lawful processing, appropriate retention, and mechanisms for correction or deletion where applicable. Identity resolution can create serious harms when records are incorrectly merged. Automated quality rules may also encode discriminatory assumptions, particularly when “normal” values are learned from historically biased data.

Contemporary platforms increasingly use schema registries, data contracts, data observability, lakehouse tables, streaming CDC, and data products. AI can assist with anomaly detection, mapping suggestions, entity matching, and natural-language metadata search. However, AI-generated mappings must be reviewed because plausible suggestions can silently alter meaning, expose sensitive data, or reproduce bias. Human accountability, reproducible rules, evaluation datasets, and approval workflows remain necessary.

14. Best-Practice Design Principles

1. Begin with business definitions and declared grain.
2. Preserve raw inputs and record provenance.
3. Make transformations versioned, testable, and explainable.
4. Prefer explicit mappings to undocumented implicit conversions.
5. Treat quality as measurable and owned, not as a final clean-up step.
6. Design for idempotency, replay, late data, and schema evolution.
7. Separate rejected records from silently discarded records.
8. Reconcile both counts and business measures.
9. Use metadata and lineage as operational assets.
10. Apply stronger controls to critical data elements and sensitive data.
11. Monitor source-quality trends as well as target outcomes.
12. Establish stewardship processes for exceptions and master data.

Chapter Summary

Data integration creates a reliable analytical foundation by connecting heterogeneous sources while preserving meaning, history, and traceability. ETL transforms before loading; ELT loads before transforming, and both require governance. Effective pipelines include source discovery, immutable staging, profiling, cleansing, semantic transformation, entity resolution, CDC, controlled loading, validation, reconciliation, and operational monitoring.

Data quality is multidimensional and use-dependent. Completeness, accuracy, validity, consistency, uniqueness, timeliness, integrity, and traceability should be measured against explicit rules and business thresholds. Metadata and lineage explain what data means and where it came from, while MDM governs shared entities and reference values. Professional practice also requires privacy, security, ethical review, human stewardship, and resilience against late, duplicated, malformed, or changed data.

Key Terms

Data integration: Combining data from multiple sources into a coherent analytical representation.

ETL: Extracting, transforming, and loading data into a target platform.

ELT: Extracting and loading data before transforming it in the target platform.

Staging area: A controlled intermediate location for extracted data.

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

Data cleansing: Correcting, standardising, quarantining, or otherwise managing defective data.

Entity resolution: Determining whether records refer to the same real-world entity.

Surrogate key: A warehouse-generated identifier independent of a source-system key.

Grain: The precise level of detail represented by one fact row.

Change-data capture: Detecting inserts, updates, and deletes after an initial extraction.

Watermark: A recorded source position used to control incremental extraction.

Idempotency: The property that replaying the same input does not create unintended additional effects.

Data quality: Fitness of data for a defined intended use.

Data lineage: A record of data origin, movement, transformation, and use.

Metadata: Data describing technical, business, and operational characteristics of data.

Master data management: Governance of shared core entities and their identities.

Slowly changing dimension: A dimension design that manages changes in descriptive attributes over time.

Reconciliation: Comparison of source and target counts, values, or relationships.

Data contract: An explicit agreement about schema, meaning, quality, and delivery expectations.

Data stewardship: Organisational responsibility for definitions, quality, and remediation.

Review Questions

Short Answer Questions

1. Distinguish ETL from ELT and identify one circumstance favouring each.
2. Why should the grain of a fact table be declared before measures are loaded?
3. What is a watermark in incremental extraction?
4. Name four dimensions of data quality and give one example measure for each.
5. Explain the difference between technical metadata and business metadata.

Long Answer Questions

1. Explain the stages of a production-grade source-to-target pipeline, including profiling, transformation, loading, and reconciliation.
2. Compare timestamp-based, trigger-based, log-based, snapshot, and event-based change-data capture.
3. Discuss how Type 1 and Type 2 slowly changing dimensions support different analytical requirements.
4. Explain why master-data management is an organisational capability rather than merely a deduplication algorithm.
5. Evaluate the security, privacy, and ethical risks introduced by integrating customer data across systems.

Analytical / Application Questions

1. A source contains 1,000,000 records, of which 18,000 have null customer identifiers. Calculate the missingness rate and recommend two governance actions.
2. A retailer joins an order-level shipping-cost table to an order-line fact table and observes inflated costs. Diagnose the grain problem and propose a corrected model.
3. Design a quality gate for a daily financial-load pipeline. Specify at least four rules, thresholds, failure actions, and the metadata that should be recorded.

Multiple Choice Questions with Answers

Question 1

Which statement best describes ELT?

A. Data is transformed in the source system before extraction

B. Data is extracted and loaded before transformation in the target platform

C. Only real-time data can be processed

D. Data is loaded without any validation

Correct Answer: B. Data is extracted and loaded before transformation in the target platform

Question 2

What is the main purpose of a staging area?

A. To replace all source systems

B. To provide a controlled intermediate location for extracted data

C. To store only aggregated reports

D. To remove the need for metadata

Correct Answer: B. To provide a controlled intermediate location for extracted data

Question 3

A profile contains 50,000 records and 1,250 missing values. What is the missingness rate?

A. 0.25%

B. 1.25%

C. 2.5%

D. 4.0%

Correct Answer: C. 2.5%

Question 4

Which CDC approach commonly reads database transaction logs and usually avoids application triggers?

A. Log-based CDC

B. Snapshot comparison

C. Manual spreadsheet extraction

D. Static full extraction

Correct Answer: A. Log-based CDC

Question 5

What does declaring fact-table grain accomplish?

A. It specifies the level of detail represented by each fact row

B. It guarantees that all source records are accurate

C. It eliminates the need for surrogate keys

D. It converts ETL into ELT

Correct Answer: A. It specifies the level of detail represented by each fact row

Question 6

Which slowly changing dimension technique preserves historical versions by inserting new rows?

A. Type 0

B. Type 1

C. Type 2

D. Overwrite-only loading

Correct Answer: C. Type 2

Question 7

Which quality dimension asks whether a value conforms to an allowed format or domain?

A. Timeliness

B. Validity

C. Uniqueness

D. Lineage

Correct Answer: B. Validity

Question 8

Why is idempotency important in a CDC pipeline?

A. It ensures replaying the same input does not create unintended duplicate effects

B. It guarantees that every source value is correct

C. It removes the need for error handling

D. It forces all data to be loaded in real time

Correct Answer: A. It ensures replaying the same input does not create unintended duplicate effects

Question 9

Which activity primarily compares source and target totals and record counts?

A. Entity resolution

B. Reconciliation

C. Tokenisation

D. Schema evolution

Correct Answer: B. Reconciliation

Question 10

What is a central purpose of master-data management?

A. To govern shared entities such as customers and products

B. To compress every warehouse table

C. To replace all data-quality rules with AI

D. To prevent historical data from being retained

Correct Answer: A. To govern shared entities such as customers and products