Data Integration: ETL, ELT and Change Capture

Introduction and Context

Data integration is the coordinated movement and transformation of data from operational, external, and analytical sources into a trusted target environment. In a data warehouse, integration is not merely a technical transfer activity. It establishes how business events are represented, how definitions are harmonized, how history is preserved, and how users can rely on analytical results.

The traditional extract–transform–load (ETL) pattern performs transformation before data enters the warehouse. Modern cloud platforms also support extract–load–transform (ELT), in which relatively raw data is loaded into a scalable target and transformed using the target engine. Both patterns remain relevant. The appropriate choice depends on data volume, latency, governance, cost, source constraints, processing capabilities, and the required level of traceability.

Reliable integration must address the complete source-to-target lifecycle: source discovery, extraction, profiling, cleansing, transformation, loading, reconciliation, error management, orchestration, monitoring, and recovery. It must also identify what has changed since the previous run. Change data capture (CDC) enables incremental processing and avoids repeatedly scanning and transforming unchanged data.

Learning Objectives

After completing this chapter, the reader should be able to:

1. Distinguish ETL, ELT, batch, streaming, full-load, and incremental integration patterns.

2. Develop source-to-target mappings with explicit business and technical rules.

3. Design cleansing, standardization, validation, and transformation processes.

4. Explain CDC methods and select an appropriate incremental-loading strategy.

5. Design reconciliation, error-handling, restart, and audit mechanisms.

6. Apply orchestration principles to construct dependable data pipelines.

7. Evaluate integration architectures in relation to performance, governance, security, and cost.

1. Foundations of Data Integration

A data integration pipeline connects one or more sources to a target through a sequence of controlled activities. Sources may include relational databases, enterprise applications, files, APIs, message streams, sensors, and third-party services. Targets may include a staging database, enterprise data warehouse, lakehouse, data mart, or analytical platform.

Important terminology includes:

• Source system: The system that creates or supplies data.
• Landing zone: An area in which extracted data is stored with minimal alteration.
• Staging area: A controlled intermediate area used for validation and preparation.
• Target: The destination into which integrated data is loaded.
• Pipeline: The executable sequence of extraction, processing, loading, and control activities.
• Data contract: An agreed specification of data structure, meaning, quality expectations, ownership, and change rules.
• Lineage: Evidence of where data originated and how it was transformed.
• Watermark: A value identifying the latest successfully processed position, such as a timestamp, sequence number, or log offset.
• Idempotence: The property that rerunning the same logical input produces the same target state rather than duplicate effects.

A robust integration architecture separates concerns. Raw extraction should preserve evidence of what the source delivered. Transformation should apply documented rules. Loading should enforce target constraints and dimensional behavior. Control tables should record execution identifiers, source ranges, row counts, statuses, and errors.

2. ETL and ELT Architectures

2.1 ETL

In ETL, data is extracted from sources, transformed in an integration engine or staging environment, and then loaded into the warehouse. Typical transformations include data-type conversion, deduplication, reference-data enrichment, derivation of measures, and dimensional key assignment.

ETL is useful when:

• The target should receive only validated and governed data.
• Source data must be masked before entering a shared platform.
• The target has limited transformation capability.
• Complex transformations require specialized processing.
• Regulatory policy restricts storage of raw sensitive data.

Its limitations include additional infrastructure, data movement between processing layers, and possible bottlenecks in the transformation engine.

2.2 ELT

In ELT, data is extracted and loaded into a scalable target, often in raw or lightly structured form. Transformations are executed inside the warehouse or lakehouse using SQL, distributed processing, or managed transformation tools.

ELT is attractive when:

• The target provides elastic compute and parallel processing.
• Large volumes require scalable transformation.
• Raw data must be retained for replay, audit, or future use.
• Analysts and engineers require rapid adaptation to new data products.

ELT does not eliminate governance. Raw data still requires access control, retention rules, classification, quality monitoring, and protection of personal information. A common architecture contains raw, standardized, integrated, and presentation layers.

2.3 Choosing between ETL and ELT

The decision should be based on workload characteristics rather than fashion. Relevant criteria include transformation complexity, data sensitivity, network cost, target processing capacity, latency, replay requirements, operational skills, and platform lock-in. Hybrid designs are common: sensitive fields may be masked before landing, raw immutable data may be retained, and business transformations may be performed in the analytical platform.

3. Source-to-Target Mapping

A source-to-target mapping is the principal design artifact linking source attributes to target attributes and specifying how values are interpreted. A professional mapping should include:

| Mapping element | Typical content |

|---|---|

| Source object and column | Table, file field, API property, or event attribute |

| Target object and column | Warehouse table and attribute |

| Data type and length | Source and target representation |

| Transformation rule | Conversion, derivation, lookup, or aggregation |

| Null and default rule | Treatment of missing or unknown values |

| Key rule | Natural key, surrogate key, or composite key behavior |

| Quality rule | Accepted range, format, uniqueness, or referential condition |

| Load behavior | Insert, update, overwrite, expire, reject, or quarantine |

| Security classification | Confidentiality and masking requirements |

| Lineage and owner | Responsible business and technical parties |

For example, an order source may contain `customer_code`, `order_date_text`, `currency_code`, and `net_amount`. The target may require a surrogate customer key, a date key, a standardized currency code, and a decimal measure. The mapping must state whether invalid dates are rejected, whether an unknown customer receives an “unknown” dimension key, and whether monetary values are converted to a reporting currency.

Mappings should distinguish technical transformations from semantic transformations. Converting a character date into a database date is technical. Defining “net sales” as gross sales minus discounts and returns is semantic and requires business-owner approval.

4. Extraction and Data Profiling

Extraction may be full, incremental, or event-driven. A full extraction reads the complete source population. It is simple but may be expensive and may create unnecessary load. Incremental extraction reads new or changed records. Event-driven extraction receives change events or messages as they occur.

Before designing transformations, data should be profiled. Profiling examines completeness, uniqueness, frequency distributions, valid formats, minimum and maximum values, referential relationships, and unexpected patterns. Profiling should be performed on representative samples and, where feasible, on complete populations.

Extraction design must account for:

• Transaction consistency and isolation.
• Source-system performance and extraction windows.
• Time zones and clock synchronization.
• Late-arriving records and corrections.
• API pagination, rate limits, and retries.
• File naming, checksums, encoding, and schema drift.
• Security of credentials and network transmission.

A point-in-time-consistent extract is preferable when related tables must represent the same business state. If that is not possible, the pipeline should record extraction times and acknowledge possible temporal inconsistency.

5. Cleansing and Transformation

Data cleansing improves fitness for use but should not silently conceal source defects. Typical operations include:

• Standardizing case, whitespace, punctuation, codes, and units.
• Converting data types and date formats.
• Resolving duplicate entities.
• Validating ranges and domain values.
• Handling missing, unknown, and not-applicable values.
• Applying reference-data and master-data rules.
• Matching records across systems.
• Deriving measures and dimensions.

5.1 Missing values

A null may mean unknown, not collected, not applicable, or technically unavailable. These meanings should not be collapsed without justification. Warehouses often use explicit dimension members such as “Unknown” or “Not applicable,” while measures may remain null when zero would misrepresent the business event.

5.2 Deduplication and entity resolution

Duplicate detection may use deterministic rules, such as an exact customer identifier, or probabilistic matching based on names, addresses, telephone numbers, and other attributes. Probabilistic matching requires thresholds, review procedures, and monitoring of false matches. Incorrectly merging two people can be more harmful than retaining a duplicate.

5.3 Reference and master data

Codes from different systems should be mapped to governed reference values. For example, country codes, product categories, currencies, and organizational units may require crosswalk tables with effective dates. Reference-data changes should be versioned where historical reporting depends on the value that was valid at the time.

5.4 Transformation order

Transformation order affects results. A defensible sequence is often: preserve raw data, validate structure, standardize formats, apply reference mappings, resolve identities, derive attributes, enforce business rules, and then load target structures. The sequence must be documented because operations such as rounding, filtering, aggregation, and currency conversion may not commute.

6. Loading Data Warehouse Structures

Loading strategies depend on the target model.

6.1 Fact loading

A fact row normally represents a business event at a declared grain, such as “one order line” or “one account balance per day.” The grain must be stated before mapping measures. Surrogate foreign keys are commonly resolved by looking up dimension keys using natural business identifiers and effective dates.

An unknown dimension member is often assigned a reserved key, such as a negative integer, when a fact arrives before its dimension record. This supports referential integrity, but the unresolved relationship must be monitored and corrected where possible.

6.2 Dimension loading

Common dimension behaviors include:

• Type 1: Replace the previous attribute value; history is not retained.
• Type 2: Insert a new version and preserve the old version with effective and expiry timestamps plus a current-row indicator.
• Type 3: Retain limited prior values in additional columns.

Type 2 processing requires a stable natural key, change-detection logic, effective dating, and protection against overlapping versions. For a record with effective time `t_start` and expiry time `t_end`, a common validity condition is `t_start <= event_time < t_end`. The exact boundary convention must be applied consistently.

6.3 Upsert and merge behavior

An upsert inserts a previously unseen business key and updates or versions an existing key. A reliable merge must define duplicate source keys, deletion handling, null semantics, concurrency behavior, and retry behavior. Database-specific `MERGE` statements should not be assumed safe in every platform; uniqueness constraints and deterministic source preparation remain necessary.

7. Change Data Capture and Incremental Processing

CDC identifies inserts, updates, and deletes after an initial load. Its purpose is to reduce source scans, lower latency, and preserve change history.

7.1 Timestamp-based capture

A source `last_modified` timestamp can be used as a watermark. A pipeline extracts records newer than the previous successful watermark, often with an overlap interval to protect against precision differences or delayed commits. The overlap requires idempotent loading and deduplication.

This method is simple but depends on reliable source timestamps. It may miss updates when timestamps are not changed consistently, and it may not identify deletes unless deletion timestamps or tombstones exist.

7.2 Increasing-key capture

An increasing sequence or identity column can identify appended records. It is efficient for insert-only sources but usually cannot detect updates or deletes unless additional mechanisms exist.

7.3 Log-based CDC

Database transaction logs, redo logs, or write-ahead logs can provide ordered changes with operation type, keys, values, and transaction information. Log-based CDC generally has lower source-query overhead and better delete visibility, but it requires connector support, log retention, schema-change handling, and careful offset management.

7.4 Trigger- and snapshot-based capture

Triggers write changes to audit tables but add source-system overhead and operational complexity. Snapshot comparison detects differences between two source states and can identify changes without source instrumentation, but it may be computationally expensive and can have ambiguous delete semantics.

7.5 CDC correctness

A CDC design should define:

1. The initial synchronization method.

2. The change source and ordering guarantee.

3. The checkpoint or offset.

4. Transaction and commit semantics.

5. Duplicate and out-of-order event handling.

6. Delete and tombstone handling.

7. Schema evolution behavior.

8. Replay and recovery procedures.

At-least-once delivery is common. It may deliver an event more than once, so the target should use event identifiers, source sequence numbers, or deterministic keys to achieve idempotent effects. Exactly-once claims must be examined carefully because end-to-end exactly-once behavior depends on the source, transport, processing engine, target, and checkpoint protocol.

8. Reconciliation, Quality, and Error Handling

Reconciliation provides evidence that the pipeline processed what it was expected to process. Useful controls include source and target row counts, inserted and updated counts, rejected counts, control totals, checksums, minimum and maximum timestamps, and aggregate measures such as total sales.

A basic count difference can be expressed as:

`Difference = SourceCount - TargetAcceptedCount - RejectedCount`

This identity is meaningful only when the source and target populations have the same scope and when duplicates, deletes, and filtering rules are explicitly represented. It is not proof of correctness by itself.

Quality rules may be categorized as:

• Structural: Required columns, data types, file layout, and schema compatibility.
• Completeness: Required values and expected record coverage.
• Validity: Domain values, formats, and ranges.
• Uniqueness: No prohibited duplicate keys.
• Consistency: Agreement between related fields and systems.
• Timeliness: Arrival and processing within an agreed service level.

Error handling should distinguish transient, permanent, and data-quality errors. Transient network failures may be retried with exponential backoff and a maximum retry limit. Permanent configuration failures require operator intervention. Bad records should be quarantined with the original payload, rule violated, pipeline run identifier, and diagnostic context. Silent row loss is unacceptable.

A dead-letter area is useful for records that cannot be processed automatically. Quarantined data requires ownership, retention, access control, remediation, and replay procedures. Error handling should avoid exposing sensitive values in logs.

9. Orchestration and Operational Reliability

Orchestration coordinates dependencies, schedules, parameters, retries, parallelism, alerts, and recovery. A pipeline graph may contain tasks for source extraction, schema validation, staging, dimension processing, fact loading, reconciliation, and publication.

Good orchestration practices include:

• Use explicit dependencies rather than relying on task timing.
• Separate extraction, transformation, and publication states.
• Assign a unique run identifier to each execution.
• Persist watermarks only after successful processing.
• Make tasks restartable and idempotent.
• Use atomic publication or swap techniques where consumers must not see partial results.
• Monitor duration, throughput, freshness, failure rate, and backlog.
• Apply service-level objectives for data availability and quality.
• Version code, mappings, schemas, and configuration.

A common recovery pattern is to retain immutable raw inputs, record control metadata, stage transformed results, validate them, and publish only after reconciliation succeeds. If a later task fails, the pipeline can resume from the last durable checkpoint rather than re-reading the source blindly.

10. Worked Example: Incremental Sales Integration

Assume an order-line source contains `order_id`, `line_id`, `customer_code`, `product_code`, `last_modified_ts`, `operation`, `quantity`, `unit_price`, and `discount_amount`. The target fact table has a surrogate `customer_key`, `product_key`, `date_key`, and `net_amount`.

The declared grain is one row per order line. The transformation rule is:

`net_amount = (quantity × unit_price) - discount_amount`

The formula assumes quantity, unit price, and discount are expressed in compatible units and currency, and that discount is not greater than the gross amount unless the business rule explicitly permits credits.

The pipeline proceeds as follows:

1. Read source changes whose modification sequence is greater than the stored successful watermark, with a controlled overlap if timestamp capture is used.

2. Store the source payload and extraction metadata in the raw layer.

3. Validate required keys, numeric fields, currency, and operation codes.

4. Deduplicate by the source event identifier or by the latest deterministic version of `(order_id, line_id)`.

5. Resolve customer and product natural keys to dimension surrogate keys using the transaction’s business date.

6. Compute `net_amount` and reject or quarantine records failing financial validation.

7. Apply inserts, updates, and deletes according to the source operation.

8. Reconcile counts and monetary control totals.

9. Advance the watermark only after successful publication and control checks.

If three valid rows have net amounts of 90, 200, and 50, the target control total is 340 monetary units. This total alone cannot prove row-level correctness; it must be combined with keys, counts, and exception analysis.

11. Illustrative Case Study: Omniretail Customer and Sales Warehouse

Background

Omniretail operates stores, an online channel, and a customer-service platform. Store sales arrive nightly, online orders are available through a relational database, and service interactions arrive through an API. The organization wants daily profitability and customer-service reporting.

Problem

The channels use different customer identifiers, product codes, currencies, and time zones. Reports previously contained duplicate customers, missing returns, and inconsistent daily totals. A full nightly reload exceeded the available processing window.

Analysis

Profiling showed that the online database had a reliable transaction log, store files included a daily sequence number but no explicit delete records, and the service API supplied update timestamps with occasional delays. Product and currency reference data were centrally governed, but customer matching required review for uncertain matches.

Approach and solution

Omniretail adopted a hybrid architecture. Raw files, API responses, and CDC events were retained in a controlled landing zone. Log-based CDC was used for online orders. Store files were incrementally processed using sequence numbers and periodic full snapshots to detect exceptional corrections. API extraction used a timestamp watermark with a two-hour overlap and idempotent deduplication.

A conformed customer dimension used deterministic identifiers where available and a governed matching service for uncertain records. Type 2 history was applied to customer segment and preferred channel, while spelling corrections were treated as Type 1 changes. Product and currency crosswalks were effective-dated. Returns were represented as negative fact amounts rather than silently deleting original sales events.

The orchestration workflow loaded dimensions before facts, quarantined invalid records, and published daily marts only after count, amount, and freshness checks passed. Every run recorded source ranges, event counts, rejected records, and reconciliation outcomes.

Outcome

The design reduced unnecessary source scanning, made late-arriving corrections visible, and gave analysts consistent customer and product dimensions. More importantly, it made exceptions traceable. The organization still had to maintain matching review queues, monitor source schema changes, and periodically perform backfills.

Learning points

The case demonstrates that no single CDC method is universally superior. Reliable integration combines source-specific capture, explicit semantic rules, idempotent processing, effective-dated reference data, and operational controls. A faster pipeline is not successful if it produces untraceable or semantically inconsistent data.

12. Governance, Security, and Ethical Considerations

Integration pipelines frequently process personal, financial, health, or employment data. Data minimization should guide extraction: a field should not be copied merely because it exists. Sensitive fields require classification, encryption in transit and at rest, least-privilege access, secrets management, masking, and controlled retention.

Lineage and audit logs support accountability and regulatory inquiries. However, logs can themselves contain personal information and must be protected. Data subject rights, retention limits, legal holds, cross-border transfer rules, and purpose limitation should be reflected in pipeline design. Deletion requests are particularly difficult when raw zones, derived tables, backups, and replicas exist; an explicit deletion propagation strategy is required.

Automated entity matching and quality rules can encode bias or produce unjustified exclusions. High-impact decisions should not rely on opaque data corrections without human oversight. AI-assisted mapping, anomaly detection, and schema matching can improve productivity, but generated rules must be reviewed, tested, versioned, and traceable. AI tools must not receive confidential source data without approved safeguards.

13. Challenges and Contemporary Developments

Major challenges include schema drift, source outages, late and out-of-order events, clock errors, high-cardinality data, changing business definitions, platform cost, and operational ownership. Data contracts and compatibility testing reduce unexpected changes. Contract testing verifies that producers and consumers agree on schema, semantics, and quality expectations before deployment.

Contemporary integration increasingly uses lakehouse architectures, open table formats, streaming platforms, data observability tools, and declarative transformation frameworks. Streaming and micro-batch processing reduce latency but increase demands for event-time handling, state management, replay, and operational monitoring. Data mesh approaches emphasize domain ownership and data products, but they do not remove the need for common identifiers, interoperability, governance, and enterprise-level quality controls.

The central design principle remains stable: preserve evidence, make transformations explicit, control state transitions, measure outcomes, and enable recovery.

Best Practices Checklist

1. Declare the grain of every fact and the meaning of every important attribute.

2. Maintain version-controlled mappings, schemas, and transformation code.

3. Preserve raw inputs when permitted by governance and privacy requirements.

4. Use stable watermarks and advance them only after successful completion.

5. Design for idempotence, replay, and partial failure.

6. Treat deletes, late arrivals, and corrections as first-class cases.

7. Reconcile counts, control totals, freshness, and exceptions.

8. Quarantine bad data rather than silently discarding it.

9. Protect credentials, sensitive fields, logs, and operational metadata.

10. Test normal, boundary, duplicate, out-of-order, schema-change, and recovery scenarios.

Chapter Summary

Data integration converts heterogeneous operational and external data into a reliable analytical resource. ETL transforms data before warehouse loading, whereas ELT uses the target platform for transformation; hybrid architectures combine their strengths. Source-to-target mappings document technical conversions, semantic definitions, quality rules, keys, security, and load behavior.

Extraction may be full, incremental, or event-driven. Cleansing must distinguish unknown, missing, and inapplicable values, while entity resolution and reference-data management require explicit governance. Fact and dimension loading depend on declared grain, surrogate-key resolution, and carefully selected historical behavior such as Type 1 or Type 2 dimensions.

CDC can use timestamps, increasing keys, logs, triggers, or snapshots. Correct incremental processing requires checkpointing, ordering decisions, delete handling, schema evolution, idempotence, and replay. Reconciliation, quarantine, retry policies, lineage, monitoring, and orchestration provide operational reliability. Security, privacy, retention, ethical matching, and regulatory obligations must be designed into the pipeline. The professional objective is not simply to move data quickly, but to make every important result explainable, recoverable, and fit for decision-making.

Key Terms

Data integration: Coordinated movement and transformation of data across systems.

ETL: Extract, transform, and load processing pattern.

ELT: Extract, load, and transform processing pattern.

Landing zone: Area retaining extracted data before substantial processing.

Staging area: Intermediate processing area for validation and preparation.

Source-to-target mapping: Specification connecting source attributes to target attributes and rules.

Data contract: Agreement defining structure, meaning, quality, ownership, and change expectations.

Data profiling: Examination of data structure, values, quality, and patterns.

Watermark: Position recording the latest successfully processed source state.

Change data capture: Technique for identifying inserts, updates, and deletes.

Idempotence: Property that repeated processing produces the same logical target state.

Surrogate key: Warehouse-generated identifier used independently of a source business key.

Type 1 dimension: Dimension behavior that overwrites changed attributes.

Type 2 dimension: Dimension behavior that preserves historical versions.

Reconciliation: Comparison of source, processing, and target results using control evidence.

Quarantine: Controlled storage for records that fail processing or quality rules.

Orchestration: Coordination of pipeline tasks, dependencies, schedules, and recovery.

Schema drift: Unplanned or evolving changes in data structure.

Lineage: Record of data origin and transformations.

Data contract testing: Verification that producer and consumer expectations remain compatible.

Review Questions

Short Answer Questions

1. What is the principal difference between ETL and ELT?

2. Why must the grain of a fact table be declared before transformation design?

3. Name two limitations of timestamp-based CDC.

4. What is the purpose of a quarantine area?

5. Define idempotence in the context of data pipelines.

Long Answer Questions

1. Explain the components that should be included in a professional source-to-target mapping.

2. Compare Type 1 and Type 2 dimension loading, including appropriate use cases and control requirements.

3. Discuss how reconciliation, auditing, and error handling contribute to pipeline reliability.

4. Compare log-based, timestamp-based, trigger-based, and snapshot-based CDC.

5. Explain how privacy, security, lineage, and retention requirements influence integration architecture.

Analytical / Application Questions

1. A source provides a `last_modified_ts` field but does not record deletes. Design an incremental strategy that minimizes missed updates and explain how deletions could be addressed.

2. A daily sales load has matching source and target row counts, but total sales differ. Identify possible causes and propose an investigation and reconciliation plan.

3. Design a hybrid ETL/ELT architecture for a regulated organization that must mask personal data before analytical processing while retaining replay capability. Justify the placement of each control.

Multiple Choice Questions with Answers

Question 1

Which characteristic most directly distinguishes ELT from ETL?

A. ELT never validates data

B. ELT performs major transformations in the target platform

C. ELT can process only streaming data

D. ELT does not require extraction

Correct Answer: B. ELT performs major transformations in the target platform

Question 2

What should be declared before mapping measures into a fact table?

A. The dashboard color scheme

B. The database vendor

C. The fact table grain

D. The number of report users

Correct Answer: C. The fact table grain

Question 3

Which CDC method generally reads database transaction records rather than repeatedly querying business tables?

A. Log-based CDC

B. Snapshot comparison

C. Manual file upload

D. Static full extraction

Correct Answer: A. Log-based CDC

Question 4

Why is an overlap interval often used with timestamp-based incremental extraction?

A. To increase the number of rejected records

B. To protect against delayed commits and timestamp precision issues

C. To eliminate the need for deduplication

D. To convert updates into deletes

Correct Answer: B. To protect against delayed commits and timestamp precision issues

Question 5

What is the defining behavior of a Type 2 dimension?

A. It discards all historical values

B. It retains historical versions of changed records

C. It stores only aggregate measures

D. It prevents surrogate keys

Correct Answer: B. It retains historical versions of changed records

Question 6

Which property ensures that retrying a pipeline does not create duplicate logical effects?

A. Idempotence

B. Pagination

C. Encryption

D. Normalization

Correct Answer: A. Idempotence

Question 7

Which control best supports investigation of a rejected source record?

A. Deleting the record immediately

B. Storing the payload, rule violation, and run identifier

C. Ignoring the error if row counts match

D. Replacing all values with zero

Correct Answer: B. Storing the payload, rule violation, and run identifier

Question 8

If quantity is 4, unit price is 75, and discount is 20, what is the net amount under the chapter’s formula?

A. 280

B. 300

C. 320

D. 355

Correct Answer: A. 280

Question 9

When should a CDC watermark normally be advanced?

A. When extraction begins

B. After successful processing and publication controls

C. Whenever a single record is read

D. Before source authentication

Correct Answer: B. After successful processing and publication controls

Question 10

Which statement about matching source and target row counts is correct?

A. It proves every value is correct

B. It is useless in every situation

C. It is useful evidence but must be combined with other controls

D. It automatically detects missing deletes

Correct Answer: C. It is useful evidence but must be combined with other controls