Data Integration, ETL/ELT and Data Quality

Introduction and Context

Modern organizations collect data from operational databases, enterprise applications, web services, sensors, files, mobile devices and external partners. These sources differ in structure, naming conventions, data types, update frequencies, ownership and reliability. Data integration is the discipline of combining such data so that it can be used consistently for reporting, analytics, machine learning and decision-making.

A data warehouse is not created merely by copying data from one system to another. Source data must be discovered, extracted, transformed, reconciled, validated, documented and loaded. The resulting data must also remain trustworthy as systems and business rules change. This chapter examines the engineering and governance practices required to create reliable integration pipelines.

The terms ETL and ELT describe two broad approaches. In extract-transform-load (ETL), data is transformed before it enters the target warehouse. In extract-load-transform (ELT), data is first loaded into a scalable target platform and transformed there. Neither approach is universally superior; the choice depends on data volume, latency, platform capability, security, cost and operational requirements.

Learning Objectives

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

• explain data integration, ETL and ELT architectures;
• design extraction, transformation and loading pipelines;
• profile, cleanse, standardize, deduplicate and reconcile data;
• distinguish validation, verification and data-quality monitoring;
• explain metadata, lineage, master data and change data capture;
• select suitable batch, streaming and incremental-loading strategies;
• evaluate governance, privacy, security and ethical risks; and
• apply these ideas to an organizational data-integration problem.

1. Foundations of Data Integration

Data integration creates a coherent view from multiple data sources. Sources may be structured, semi-structured or unstructured. Examples include relational tables, JSON APIs, XML documents, log files, spreadsheets, images and event streams.

Integration can occur at several levels:

• Data consolidation moves data into a common repository such as a warehouse or lakehouse.
• Data federation provides a unified access layer without necessarily copying all data.
• Application integration connects business processes and applications through APIs or messages.
• Semantic integration resolves differences in meaning, such as whether “customer” means a person, an account holder or a legal organization.

The hardest problems are often semantic rather than technical. A source field called `status` may represent an account state, delivery state or payment state. Two systems may use different units, currencies, calendars or identifiers. Integration therefore requires both technical mappings and agreement about business meaning.

A typical analytical architecture includes source systems, ingestion services, a landing or raw zone, transformation layers, a warehouse or lakehouse, semantic models and consuming tools. Metadata, orchestration, quality monitoring and security should operate across all layers rather than being added after implementation.

2. ETL and ELT Architectures

Extract

Extraction obtains data from sources while minimizing disruption to operational systems. Common methods include:

• full extraction, in which all source records are read;
• timestamp-based incremental extraction;
• change data capture (CDC), which records inserts, updates and deletes;
• database logs or replication streams;
• API extraction using pagination and continuation tokens; and
• event-based ingestion through message brokers.

A full extract is simple but can be expensive and may repeatedly process unchanged records. Incremental extraction is more efficient, but it requires reliable change indicators, deletion handling and recovery logic.

Transform

Transformation converts source data into a consistent target representation. Typical operations include type conversion, parsing, standardization, filtering, joining, aggregation, key generation, code translation and derivation of business measures.

Transformations should be deterministic where possible. A deterministic transformation gives the same output for the same input and reference data. Business rules should be versioned and documented; otherwise, analysts may be unable to explain why historical results changed.

Load

Loading writes transformed data into a target system. Loading patterns include:

• append-only loading for immutable events;
• overwrite loading for replaceable snapshots;
• upsert loading, which inserts new records and updates existing records; and
• slowly changing dimension processing, which preserves selected historical versions.

A reliable load should be restartable. If a job fails after loading half its records, rerunning it should not create duplicates or inconsistent totals. This property is commonly supported through staging tables, transaction boundaries, batch identifiers, idempotent operations and reconciliation checks.

ETL versus ELT

ETL is useful when transformation must occur before data reaches the target because of privacy constraints, limited target compute capacity or a need to reduce data volume. ELT is attractive when the warehouse or lakehouse provides scalable processing and when retaining raw data supports reprocessing, exploration and auditing.

The choice should consider:

1. Latency: streaming or near-real-time use cases require different designs from daily reporting.

2. Volume and velocity: large data volumes favor parallel and distributed processing.

3. Data sensitivity: personally identifiable information may need masking before landing.

4. Reprocessing: raw immutable data supports correction when business rules change.

5. Cost: compute, storage, network transfer and operational support all matter.

6. Governance: access controls must apply to raw, transformed and derived data.

In practice, many organizations use a hybrid architecture: data is lightly validated and secured during ingestion, retained in raw form, and transformed into curated analytical models later.

3. Pipeline Design and Operations

A production pipeline normally contains the following stages:

1. Source registration: record source owner, schema, refresh frequency, sensitivity and business purpose.

2. Ingestion: extract data and record a batch or event identifier.

3. Landing: store an immutable or recoverable copy where appropriate.

4. Profiling: measure structure, completeness, uniqueness, distributions and anomalies.

5. Transformation: apply mappings, cleansing and business rules.

6. Integration: join sources using trustworthy keys and reference data.

7. Quality checks: test schema, values, relationships and reconciliation totals.

8. Loading and publication: expose only approved data products.

9. Monitoring and recovery: track failures, latency, volumes, costs and quality trends.

Orchestration tools manage dependencies, schedules, retries, alerts and parameterization. A pipeline should distinguish transient failures, such as a temporary network interruption, from permanent data errors, such as an invalid date. Blind retries can worsen a problem by duplicating writes or repeatedly processing bad data.

A useful operational record includes source name, extraction window, batch identifier, record counts, start and end times, rule versions, rejected-record counts and status. These records support auditability and troubleshooting.

4. Data Profiling and Quality Dimensions

Data profiling is the systematic examination of data before and during integration. It is both an initial discovery activity and an ongoing monitoring practice. Common profiling measures include row counts, null counts, distinct counts, minimum and maximum values, frequency distributions, pattern distributions and referential matches.

Data quality is multidimensional:

• Accuracy: values correctly represent the real-world object or event.
• Completeness: required values or records are present.
• Consistency: related systems and fields do not contradict each other.
• Timeliness: data is available and current when required.
• Validity: values conform to permitted formats, ranges and domains.
• Uniqueness: an entity is not represented by unintended duplicates.
• Integrity: relationships, such as foreign keys, remain valid.
• Fitness for purpose: data is suitable for a specified business or analytical use.

No dataset is simply “high quality” in the abstract. A postal address may be adequate for regional analysis but inadequate for parcel delivery. Quality requirements must therefore be expressed as measurable rules linked to use cases.

For a dataset with N records, a simple completeness rate for a required attribute is:

Completeness = (number of non-null values / N) × 100%

This measure assumes that null is the relevant indicator of missingness and that every record should contain the attribute. If empty strings, placeholder values or invalid codes also represent missingness, they must be included in the numerator’s definition of invalid or absent values.

A data-quality score can combine dimensions, but weighted scores should not conceal critical failures. For example, excellent completeness cannot compensate for incorrect customer identities in a high-risk process. Thresholds and non-negotiable rules should therefore accompany any aggregate score.

5. Cleansing, Standardization and Validation

Data cleansing corrects, removes, isolates or enriches problematic data. Typical operations include trimming whitespace, converting case, parsing dates, standardizing units, validating codes, handling missing values and correcting known errors.

Standardization is not the same as correction. Converting “UK,” “U.K.” and “United Kingdom” to one representation standardizes values; determining which country a vague address actually belongs to is a more difficult accuracy problem. Automated correction should be conservative and traceable.

Missing values require contextual treatment. Possible strategies include leaving them null, assigning an explicit unknown category, using a default only when semantically justified, imputing a value, or rejecting the record. Imputation can introduce bias and should not be used merely to make a table appear complete.

Validation operates at several levels:

• Schema validation: field names, types and required columns.
• Domain validation: permitted values, ranges and formats.
• Row validation: rules involving fields within one record.
• Cross-record validation: uniqueness and duplicate constraints.
• Referential validation: relationships to parent or reference tables.
• Aggregate validation: totals, counts and reconciliations against a source.
• Business validation: rules such as “a shipment cannot be delivered before it is dispatched.”

Invalid records should be quarantined with error reasons rather than silently discarded. A quarantine process supports correction, reprocessing and measurement of recurring source problems.

6. Deduplication and Entity Resolution

Deduplication identifies records that refer to the same real-world entity. Exact matching may use a stable identifier, such as a government-approved organization number. When identifiers are absent or inconsistent, probabilistic or rule-based entity resolution may compare names, addresses, telephone numbers and other attributes.

A basic process is:

1. normalize comparable fields;

2. generate candidate pairs or blocks to avoid comparing every record with every other record;

3. calculate matching evidence;

4. classify pairs as matches, non-matches or possible matches; and

5. merge or link records according to survivorship rules.

Blocking improves efficiency but can miss matches if the blocking key is poor. Matching thresholds also involve trade-offs: a low threshold increases false positives, while a high threshold increases false negatives. In sensitive applications, uncertain matches should be reviewed or retained as links rather than automatically merged.

Survivorship rules specify which value is retained when records disagree. Possible rules include trusted-source priority, most recent verified value, greatest completeness or human review. Every merge should preserve source identifiers and a history of the decision. Deleting the losing record without lineage can make later correction impossible.

7. Reconciliation and Control Totals

Reconciliation checks whether the integrated result agrees with authoritative source information. It may compare record counts, financial totals, quantities, hash totals, key ranges or status distributions.

For example, if a daily sales extract contains transaction amounts, a control check can compare the source total with the sum loaded into the warehouse, after accounting for documented exclusions, currency conversion and rounding. A difference is not automatically an integration defect; it may indicate late-arriving transactions, refunds, filtering or a source correction. The important requirement is that differences are explained and recorded.

Reconciliation should occur at suitable grains. A total organization-wide amount may hide a missing regional partition. Therefore checks may be performed by date, product, location, source system or batch. Tolerances must reflect units and business risk; a tolerance suitable for floating-point measurement may be unacceptable for financial accounting.

8. Master Data, Reference Data and Slowly Changing Dimensions

Master data represents core business entities such as customers, products, suppliers and employees. Reference data consists of controlled classifications and codes, such as country codes, currencies and product categories. Poor management of either creates inconsistency across analytical and operational systems.

Master data management (MDM) typically includes identity resolution, stewardship, ownership, approved definitions, hierarchies and distribution of trusted identifiers. MDM does not necessarily mean that one application owns every attribute. It means that responsibilities and authoritative sources are explicit.

In dimensional warehousing, a slowly changing dimension (SCD) preserves changes in descriptive attributes. A Type 1 approach overwrites the old value and does not preserve history. A Type 2 approach creates a new version with effective dates or a current-record indicator. Type 2 is appropriate when historical reports must reflect the attribute that was valid at the time of an event. The design must define handling for late-arriving changes, corrections and overlapping validity periods.

9. Change Data Capture and Incremental Processing

CDC identifies source changes so that pipelines process only new or modified data. Techniques include:

• timestamps such as `last_updated`;
• trigger-based change tables;
• database transaction-log reading;
• snapshot comparison; and
• source-generated events.

Timestamp extraction is simple but vulnerable to clock differences, updates that do not modify the timestamp, and deletes that leave no row to extract. A robust design uses a watermark with overlap, such as rereading a small time interval, and then applies idempotent upserts. The overlap prevents missed records but requires duplicate-safe processing.

CDC must define delete semantics. A physical delete may be represented as a tombstone, a deletion flag or an operation in a change stream. If deletes are ignored, the warehouse may falsely report entities that no longer exist.

Exactly-once processing is difficult across independent systems. Many practical pipelines aim for at-least-once delivery combined with idempotent application, unique event identifiers and deduplication. The business consequence of duplicates should guide the design.

10. Metadata, Lineage and Governance

Metadata describes data. Technical metadata includes schemas, types, partitions and pipeline jobs. Business metadata includes definitions, owners, classifications and approved usage. Operational metadata includes load times, failure status and quality results.

Data lineage records the origin and transformations of data. It may answer questions such as: Which source fields contributed to this metric? Which reports depend on a changed column? When was a value transformed, masked or aggregated? Lineage supports impact analysis, regulatory response, debugging and trust.

Governance establishes decision rights and controls. Important roles include data owners, stewards, custodians, platform engineers, privacy officers and consumers. Policies should address access, retention, classification, quality thresholds, issue escalation and change approval.

Privacy principles include purpose limitation, data minimization, access control, encryption, retention limits and lawful processing. Sensitive attributes should not be copied into analytical environments without a documented purpose. Masking or tokenization may reduce exposure, but derived attributes and combinations of seemingly harmless fields can still enable re-identification.

11. Worked Example: Customer Integration

Assume an organization receives 10,000 customer records from a CRM system. Profiling finds 9,600 non-null email values, 200 blank strings, and 200 null values. If blank strings and nulls both mean missing, completeness is:

Completeness = (10,000 − 400) / 10,000 × 100% = 96%

The CRM also uses `United Kingdom`, `UK` and `GB` for one country. A reference-data mapping standardizes these representations to the approved code `GB`, while preserving the original value for audit. Phone numbers are normalized to an agreed international format where the country is known; ambiguous numbers are quarantined rather than guessed.

Duplicate detection identifies 320 candidate pairs. After review and matching rules, 250 pairs are confirmed duplicates, 50 are rejected as different people and 20 remain uncertain. The 250 confirmed pairs are merged using a master customer identifier. The source identifiers of both records are retained, and the chosen email value is documented as coming from the most recently verified source.

A load reconciliation compares source records, accepted records and rejected records. The pipeline must explain the relationship among these populations rather than simply report that the target contains 9,750 rows. Quality results, mapping versions, quarantine reasons and merge decisions are stored as operational metadata.

12. Illustrative Case: Regional Retail Data Warehouse

Background

A fictional retailer operates stores, an online shop and a loyalty application. Store sales are delivered nightly, online transactions arrive through an API, and loyalty data is exported weekly. Executives report that revenue, customer counts and product performance differ across dashboards.

Problem

The systems use different product codes, time zones and customer identifiers. Online refunds are represented as negative transactions, while some stores send separate refund files. Customer addresses contain inconsistent formats, and product categories have changed over time.

Analysis

A profiling exercise discovers duplicate online events caused by API retries, missing store identifiers in 3 percent of sales rows, and a product-code mapping that has not been updated for a recent acquisition. Reconciliation shows that a dashboard excludes late-arriving refunds.

Approach

The retailer establishes a raw landing zone with batch identifiers and immutable API payloads. CDC is used for product and customer master data. A conformed product dimension maps regional codes to enterprise products, while a Type 2 dimension preserves historical categories. Transaction events receive unique source-event keys, and idempotent loading prevents API retries from creating duplicate sales.

Data-quality rules check required store identifiers, valid product keys, nonnegative quantities, permissible dates and reconciliation of sales and refunds. Records failing structural checks are quarantined. Missing store identifiers are sent to a steward queue rather than assigned arbitrarily. Dashboards are built on a governed semantic model with documented revenue definitions.

Outcome

The organization obtains consistent revenue definitions, visible exception queues and traceable product history. Some discrepancies remain because source systems have different business calendars, but they are documented rather than hidden. Executives can distinguish genuine commercial differences from integration defects.

Learning Points

The case demonstrates that integration requires semantic agreement, not only technical movement. Idempotency, CDC, master data, lineage, reconciliation and stewardship work together. A quality dashboard is useful only when its metrics are connected to decisions and remediation processes.

13. Challenges, Limitations and Contemporary Developments

Common challenges include schema drift, undocumented source changes, late-arriving data, fragile APIs, inconsistent identifiers, high cloud-compute costs and insufficient ownership. Fully automated cleansing is also limited: context-dependent accuracy often requires domain experts.

Data contracts are increasingly used to make expectations explicit between producers and consumers. A contract may specify schema, semantics, freshness, quality rules and change-notification procedures. Observability platforms extend monitoring beyond job success to freshness, volume anomalies, distribution changes and lineage impact.

Lakehouse architectures combine low-cost object storage with warehouse-style governance and querying. Streaming integration supports operational analytics, fraud detection and real-time recommendations, but it introduces event ordering, replay, state management and exactly-once concerns. Artificial intelligence can assist schema matching, anomaly detection and entity resolution, but generated mappings must be validated, explainable and governed. Automated models may reproduce source bias or make inappropriate matches; human review remains important for high-impact decisions.

14. Best Practices

Effective integration programs generally:

1. define business meaning and ownership before building mappings;

2. profile sources before designing transformations;

3. retain raw data when lawful and useful for audit or reprocessing;

4. make pipelines incremental, restartable and idempotent;

5. treat invalid records as visible exceptions;

6. preserve source keys, batch identifiers and transformation history;

7. use reference and master data under explicit stewardship;

8. test schemas, rules, relationships, totals and freshness;

9. monitor quality trends rather than relying on one-time certification;

10. protect sensitive data through minimization and least privilege; and

11. document assumptions, tolerances and known limitations.

Chapter Summary

Data integration combines heterogeneous sources into a consistent, usable information environment. ETL transforms before loading, whereas ELT loads first and transforms in the target; hybrid designs are common. Reliable pipelines require controlled extraction, well-defined transformations, restartable loading, orchestration and operational metadata.

Data profiling identifies structural and semantic problems. Quality must be assessed through dimensions such as accuracy, completeness, consistency, timeliness, validity, uniqueness and integrity. Cleansing, standardization, validation, deduplication and reconciliation should be traceable and linked to business requirements. Master data, reference data, CDC, slowly changing dimensions, metadata and lineage provide the foundations for stable historical and analytical views. Governance, privacy and ethical controls are essential, especially when personal data and automated matching are involved. Professional integration is therefore both an engineering discipline and an organizational data-management practice.

Key Terms

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

ETL: Extract, transform and load processing.

ELT: Extract, load and transform processing.

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

Data cleansing: Detecting and correcting, removing or isolating problematic data.

Data standardization: Representing equivalent values in a common format.

Deduplication: Identifying and handling records that represent the same entity.

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

Reconciliation: Comparing integrated results with authoritative source information.

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

Reference data: Controlled codes and classifications used for interpretation.

Change data capture: Identifying inserts, updates and deletes in source systems.

Idempotency: The property that repeating an operation produces the same intended result.

Slowly changing dimension: A dimensional design for managing historical attribute changes.

Metadata: Information describing data, its meaning, structure and operation.

Data lineage: A record of data origins, movements and transformations.

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

Quarantine: Isolation of records that fail processing or quality rules.

Data stewardship: Organizational responsibility for data meaning, quality and use.

Review Questions

Short Answer Questions

1. Distinguish ETL from ELT.

2. Why is data profiling performed before transformation?

3. What is the purpose of a quarantine area?

4. Define change data capture.

5. Name four dimensions of data quality.

Long Answer Questions

1. Explain the stages of a production-grade data-integration pipeline.

2. Compare full extraction, timestamp-based extraction and log-based CDC.

3. Discuss the role of master data and reference data in analytical integration.

4. Explain how deduplication and entity resolution should be designed and governed.

5. Discuss how metadata, lineage and governance improve trust in a data warehouse.

Analytical / Application Questions

1. A pipeline loads an API repeatedly after network failures and produces duplicate transactions. Design an idempotent loading strategy and identify the metadata it should record.

2. Two source systems report different customer counts. Propose a profiling and reconciliation investigation, including possible explanations and quality checks.

3. Design an integration approach for a retailer requiring daily warehouse reporting and near-real-time fraud detection. Justify choices concerning ETL or ELT, CDC, storage, validation and governance.

Multiple Choice Questions with Answers

Question 1

Which statement best distinguishes ETL from ELT?

A. ETL transforms data before loading, whereas ELT transforms it after loading.

B. ETL is used only for streaming data, whereas ELT is used only for batch data.

C. ETL cannot perform validation, whereas ELT can perform validation.

D. ETL stores no metadata, whereas ELT always stores metadata.

Correct Answer: A. ETL transforms data before loading, whereas ELT transforms it after loading.

Question 2

Which extraction method is most directly based on database transaction records?

A. Full table scan

B. Transaction-log change data capture

C. Manual spreadsheet export

D. Random sampling

Correct Answer: B. Transaction-log change data capture

Question 3

A dataset has 10,000 records, of which 9,600 contain a valid required value. What is the completeness rate?

A. 94%

B. 95%

C. 96%

D. 97%

Correct Answer: C. 96%

Question 4

Why should invalid records usually be quarantined rather than silently discarded?

A. To increase the apparent row count

B. To support correction, investigation and reprocessing

C. To eliminate the need for validation

D. To guarantee that all source data is accurate

Correct Answer: B. To support correction, investigation and reprocessing

Question 5

Which technique is most appropriate for preserving historical versions of a dimension attribute?

A. Type 1 slowly changing dimension

B. Type 2 slowly changing dimension

C. Full table deletion

D. Random deduplication

Correct Answer: B. Type 2 slowly changing dimension

Question 6

What is a major risk of using an overly low entity-matching threshold?

A. More false-positive matches

B. All missing values become complete

C. Source timestamps become synchronized

D. Referential integrity is automatically guaranteed

Correct Answer: A. More false-positive matches

Question 7

Which property makes a pipeline safe to rerun without creating unintended duplicate effects?

A. Ambiguity

B. Idempotency

C. Opacity

D. Volatility

Correct Answer: B. Idempotency

Question 8

What does data lineage primarily describe?

A. The physical size of a database server

B. The origins and transformations of data

C. The salary structure of data engineers

D. The number of users viewing a dashboard

Correct Answer: B. The origins and transformations of data

Question 9

Which practice best addresses deletes in a CDC pipeline?

A. Ignore all missing source rows

B. Represent deletes through tombstones, flags or delete operations

C. Convert every delete into an insert

D. Reload only rejected records

Correct Answer: B. Represent deletes through tombstones, flags or delete operations

Question 10

Why can an aggregate data-quality score be misleading?

A. It always prevents data profiling

B. Strong performance on one dimension can conceal a critical failure on another

C. It cannot be calculated for numerical data

D. It automatically removes duplicate entities

Correct Answer: B. Strong performance on one dimension can conceal a critical failure on another