Data Integration and Preparation

Introduction

Business intelligence depends on more than dashboards and analytical models. It depends on whether the underlying data is complete, consistent, timely, well-defined, and fit for purpose. Organizations typically collect data from enterprise resource planning systems, customer relationship management applications, point-of-sale systems, websites, suppliers, social platforms, sensors, documents, and external data providers. These sources differ in structure, ownership, update frequency, quality, and meaning. Data integration and preparation provide the methods for turning this fragmented data into a reliable analytical asset.

Data integration combines data from multiple sources into a coherent environment. Data preparation makes that data usable by correcting errors, standardizing representations, resolving conflicts, and creating analytical features. Together, these activities support trustworthy reporting, forecasting, customer analysis, operational monitoring, and decision-making.

The central challenge is not simply moving records from one database to another. It is preserving meaning and business rules while data moves across systems. A customer may have different identifiers in a sales system and a support system. A product may be measured in units in one source and cases in another. A transaction may be recorded as completed in one system but cancelled in another. Integration therefore requires technical design, business knowledge, data governance, and continuous quality control.

Learning Objectives

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

1. Explain the purposes and differences of ETL and ELT.
2. Design a logical data pipeline for operational, external, streaming, and unstructured data.
3. Apply data-cleansing and transformation techniques.
4. Reconcile records, measures, identifiers, and business definitions across sources.
5. Explain change-data capture and select an appropriate implementation pattern.
6. Evaluate data quality, lineage, security, governance, and ethical risks.
7. Analyze an applied data-integration scenario and recommend a practical solution.

1. The Data-Integration Problem

A source system is optimized for a particular operational purpose. A point-of-sale database records sales; a payroll system records employees; a logistics platform records shipments. Analytical systems, by contrast, require data organized around questions such as:

• Which customers are most profitable?
• Which products are likely to experience stock-outs?
• How does delivery performance affect repeat purchases?
• What is the organization’s performance by region and channel?

The data needed to answer these questions is usually distributed across systems. Integration must address four kinds of heterogeneity:

A useful distinction is between data movement and data integration. Data movement copies data. Data integration establishes relationships, definitions, transformations, controls, and usable access patterns. A technically successful load can still be an integration failure if totals are wrong or users cannot interpret the result.

2. Integration Architectures and Storage Patterns

A traditional business-intelligence architecture often contains the following layers:

1. Source layer: operational databases, applications, files, APIs, sensors, and documents.
2. Ingestion or landing layer: a controlled area where incoming data is retained in its received form.
3. Staging layer: temporary structures used for parsing, validation, and transformation.
4. Integration layer: standardized, reconciled data with shared keys and business rules.
5. Analytical storage: data warehouses, data marts, lakehouses, or analytical databases.
6. Consumption layer: reports, dashboards, notebooks, semantic models, and machine-learning applications.

A data warehouse generally stores structured, curated data organized for reliable analysis. A data lake stores large volumes of raw or lightly processed data in multiple formats. A lakehouse combines low-cost, flexible storage with warehouse-like management, transactions, and analytical performance. These labels describe architectural patterns rather than universally fixed products.

Batch and streaming integration

Batch integration processes data at scheduled intervals, such as hourly, nightly, or weekly. It is appropriate when decisions do not require immediate updates and when source systems provide periodic files. Streaming integration processes events continuously or in short intervals. It is useful for fraud detection, equipment monitoring, clickstream analysis, and real-time inventory visibility.

Streaming does not eliminate the need for batch processing. Historical backfills, reconciliation, reference-data updates, and periodic quality checks commonly remain batch activities. A mature architecture often combines both approaches.

3. ETL and ELT

ETL: Extract, Transform, Load

In ETL, data is extracted from sources, transformed in a processing environment, and then loaded into the target analytical store.

Source systems → Extract → Transform and validate → Load → Warehouse or mart

ETL is valuable when transformation must occur before data enters a tightly governed warehouse, when the target has limited processing capacity, or when sensitive fields must be masked before storage. It can also reduce the volume of data loaded into the target.

ELT: Extract, Load, Transform

In ELT, data is extracted and loaded into a scalable target platform before transformation occurs there.

Source systems → Extract → Load raw data → Transform in analytical platform

ELT is well suited to cloud platforms with elastic storage and processing. It preserves raw data for reprocessing and allows different analytical teams to create controlled transformations. However, loading raw data does not remove the need for governance. Access controls, retention policies, metadata, quality checks, and cost management remain essential.

ETL and ELT are not mutually exclusive. A pipeline may mask personal data before loading, perform structural transformations during ingestion, and complete business-level transformations after loading. This hybrid design is often practical.

4. Data Extraction and Ingestion

Extraction should be designed around source behavior and business requirements. Common approaches include:

• Full extraction: every source record is copied. It is simple but may be expensive and can place pressure on operational systems.
• Incremental extraction: only new or changed records are copied.
• API extraction: records are requested through an application programming interface, subject to authentication, rate limits, pagination, and versioning.
• File ingestion: CSV, spreadsheet, JSON, XML, and fixed-width files are received and validated.
• Database replication: changes are copied from a source database to another environment.
• Event ingestion: applications publish events such as OrderCreated or PaymentReceived.

Every ingestion process should record metadata such as source name, extraction time, file or batch identifier, schema version, record count, and processing status. This metadata supports traceability and troubleshooting.

A data contract specifies what a producer promises to provide. It may define fields, types, allowed values, event meaning, delivery frequency, ownership, and compatibility rules. Data contracts reduce the risk that a source-system change silently breaks downstream analysis.

5. Data Cleansing and Quality Management

Data cleansing identifies and corrects defects without destroying legitimate variation. Important quality dimensions include:

• Accuracy: Does the value represent the real-world condition?
• Completeness: Are required values present?
• Consistency: Do related records and systems agree?
• Validity: Does the value conform to permitted formats and rules?
• Uniqueness: Are unintended duplicates absent?
• Timeliness: Is the data available and current when needed?
• Integrity: Are relationships, such as order-to-customer links, preserved?

Common cleansing operations

Standardization converts equivalent representations to a common form. Dates should use an agreed convention and time zone. Country codes should use a controlled reference list. Text may be trimmed, normalized for case, or cleaned of non-printing characters.

Validation applies rules to values and relationships. A quantity may need to be non-negative; an order date may not normally occur after its shipment date; a product code should exist in the product master.

Deduplication identifies records that refer to the same entity. Exact matching compares identical keys. Fuzzy or probabilistic matching may compare names, addresses, email addresses, and telephone numbers. Matching thresholds must be tested because aggressive matching can merge different people, while conservative matching can leave duplicates unresolved.

Missing-value treatment depends on meaning. A missing discount may mean zero, but it may also mean that the discount was not recorded. Replacing every missing value with zero creates false information. Appropriate treatments include retaining nulls, using an explicit category such as “Unknown,” obtaining the value from another source, or excluding a record under a documented rule.

Outlier review distinguishes errors from unusual but valid observations. A very large order may be a data-entry error, a wholesale transaction, or a legitimate seasonal event. Automated deletion is therefore inappropriate without contextual checks.

Measuring completeness

For a field, a simple completeness rate is:

The rate assumes that the expected-record count is known and that “missing” is defined consistently. If 9,700 of 10,000 expected customer records contain a required email address, completeness is 97%. This metric does not establish accuracy or validity; a present but incorrect email still counts as present.

6. Transformation and Data Modeling

Transformation changes data into a form suitable for analysis while preserving business meaning. Typical transformations include:

• joining records across sources;
• converting currencies and units;
• deriving calendar attributes such as month and fiscal quarter;
• mapping operational codes to standard categories;
• pivoting or unpivoting tables;
• aggregating transactions by customer, product, or period;
• splitting or combining fields;
• creating surrogate keys;
• applying slowly changing dimension logic.

A common warehouse design separates facts from dimensions. A fact table stores measurable events, such as sales quantity and net revenue. Dimension tables provide descriptive context, such as customer, product, store, and date.

A crucial design decision is the grain of a fact table: the level represented by one row. For example, one row may represent one product line on one invoice, rather than one complete invoice. If grain is not declared, measures can be duplicated during joins and produce incorrect totals.

Slowly changing dimensions

A dimension changes over time. If a customer moves from Region A to Region B, analysts may need either the current region or the region that applied when each sale occurred.

• Type 1: overwrite the old value; useful when historical change is not required.
• Type 2: create a new dimension version with effective and expiry dates; preserves history.
• Type 3: retain limited previous values in additional columns; useful for restricted comparison.

Type 2 history commonly requires a surrogate key, an effective-from date, an effective-to date, and a current-record indicator. The design must prevent overlapping effective periods for the same business entity.

7. Reconciliation and Control Totals

Reconciliation compares data between stages or systems to establish that processing is complete and logically consistent. It is not merely a technical test; it is a control activity.

Useful controls include:

• source and target record counts;
• sums of monetary or quantity fields;
• counts by date, region, product, or status;
• unmatched-key counts;
• duplicate-key counts;
• rejected-record counts;
• hash totals or checksums where appropriate;
• comparisons of opening balance, movements, and closing balance.

A basic movement reconciliation is:

This identity applies when all relevant increases and decreases are defined consistently and no other adjustment category is omitted. For inventory, for example, purchases, sales, returns, transfers, shrinkage, and adjustments must be considered according to the organization’s accounting or operational policy.

Reconciliation should be performed at meaningful levels. An overall total may agree even when one region is overstated and another understated. Therefore, controls should examine both aggregate and segmented results.

8. Data Pipelines and Reliability

A data pipeline is an orchestrated sequence that extracts, validates, transforms, loads, and monitors data. A dependable pipeline has more than a successful “run” status. It should address:

• Idempotency: rerunning the same input does not create unintended duplicates.
• Ordering: events are processed in a suitable sequence, or late arrivals are handled explicitly.
• Retries: temporary failures are retried without corrupting data.
• Dead-letter handling: records that cannot be processed are isolated for investigation.
• Schema evolution: additions, removals, and type changes are governed.
• Observability: logs, metrics, alerts, lineage, and processing duration are available.
• Backfill capability: historical periods can be reprocessed under controlled conditions.
• Service-level expectations: freshness, completeness, and availability targets are defined.

Pipeline dependencies form a directed graph. A downstream report should not refresh before its required upstream data has passed quality checks. Orchestration tools schedule tasks, manage dependencies, and record execution metadata, but they do not replace sound transformation logic or governance.

A practical pipeline status model may include received, validated, processed, published, rejected, and quarantined. This makes operational handling clearer than a single success/failure flag.

9. Change-Data Capture

Change-data capture (CDC) identifies inserts, updates, and deletes in a source so that downstream systems can receive only changes rather than repeatedly extracting the entire dataset.

CDC approaches

1. Timestamp-based CDC: extract rows whose last_updated value is later than the previous successful watermark. It is simple but can miss changes when timestamps are unreliable or updates occur with identical precision.
2. Increasing-key CDC: extract records with an increasing identifier. It works for append-only data but does not naturally detect updates or deletes.
3. Trigger-based CDC: database triggers write changes to an audit table. It can capture detailed changes but adds source-system overhead and maintenance.
4. Log-based CDC: reads database transaction logs. It often provides low-impact, detailed change capture, but requires database and tooling support.
5. Snapshot comparison: compares successive extracts. It is broadly applicable but can be computationally expensive and may not reveal the exact sequence of changes.

A CDC process must define how deletes are represented. Options include physically deleting the target row, marking it inactive, recording a tombstone event, or retaining a historical version. The correct choice depends on reporting, legal retention, and audit requirements.

CDC also requires attention to transaction boundaries, duplicate delivery, late events, clock differences, schema changes, and recovery after failure. A robust consumer should be able to process a repeated event safely. This is commonly achieved with a stable event identifier, a source sequence number, or a target-side uniqueness rule.

10. Integrating External, Streaming, and Unstructured Data

External data

External data may include economic indicators, demographic statistics, weather, market data, supplier files, or public datasets. Before integration, assess licensing, update frequency, geographic coverage, definitions, methodology, and reliability. An external dataset should not be treated as authoritative merely because it is convenient.

Streaming data

Streaming systems represent data as events, often containing an event identifier, event type, event time, source, entity identifier, and payload. Event time is when the business activity occurred; processing time is when the platform handled the event. The distinction matters when networks delay messages or devices operate offline.

Streaming pipelines must address duplicate events, out-of-order arrival, late data, replay, and retention. Windowed calculations should state whether the window is based on event time or processing time and how late events are handled.

Unstructured data

Documents, emails, images, audio, and video are not naturally represented as rows and columns. Integration may involve document classification, optical character recognition, natural-language processing, metadata extraction, or embedding generation. Extracted fields should retain links to the original document, extraction method, confidence score, and review status.

AI-assisted extraction can improve scale, but it may produce plausible errors, omit context, or reproduce bias. Sensitive documents require access control, secure processing, retention rules, and human review for high-impact decisions.

11. Applied Case: Integrating Omnichannel Retail Data

The following is an illustrative case.

Background

A national retailer sells through physical stores, an e-commerce site, and a mobile application. Customer, order, inventory, shipment, and promotion data are maintained by separate systems. Executives receive weekly sales reports, but online and store totals differ and inventory dashboards are often two days behind.

Problem

The retailer wants a daily profitability dashboard and near-real-time stock alerts. Initial investigation finds that store systems identify products by local codes, the e-commerce platform uses global product identifiers, returns are recorded separately from sales, and customer email addresses contain duplicates and inconsistent capitalization.

Analysis

The integration team establishes the following rules:

• one fact row represents one product line on one order;
• net sales exclude tax and are reduced by approved returns;
• the product master is the authoritative source for global product identifiers;
• store codes are mapped to global products through a governed cross-reference table;
• customer matching uses exact loyalty identifiers where available and reviewed probabilistic matching otherwise;
• inventory events use event time and retain a unique event identifier;
• daily reconciliation compares source totals with integrated totals by channel and date.

Approach and solution

A hybrid architecture is selected. Operational databases publish changes through log-based CDC into a landing area. Web and mobile order events are ingested continuously. External promotion files are loaded nightly. Documents such as supplier invoices are processed using optical character recognition, with low-confidence fields routed for review.

Raw data is retained for audit and reprocessing. Personally identifiable information is encrypted and access is restricted. Standardized product, customer, store, and date dimensions are created. A Type 2 product-location dimension preserves historical selling-region assignments. Streaming inventory events update a near-real-time operational view, while a nightly batch process performs complete reconciliation and correction.

Quality controls reject invalid currencies, unknown product mappings, negative quantities without an approved return status, and duplicate event identifiers. Rejected records are quarantined rather than silently discarded.

Outcome

The retailer obtains a common definition of net sales and can compare channels without double-counting returns. Stock alerts become more timely, while the nightly reconciliation identifies late or missing events. Analysts can trace a dashboard measure to source batches, transformation rules, and quality results.

Learning points

The case demonstrates that integration is a business-design problem as well as an engineering problem. Declaring grain, ownership, definitions, keys, exception handling, and reconciliation rules is as important as selecting ingestion technology. It also shows why real-time data and batch controls can complement rather than replace one another.

12. Governance, Ethics, and Regulation

Data preparation can create risks even when the original data was collected lawfully. Matching records may reveal sensitive relationships. Enriching customer profiles may create unfair inferences. Deleting values may remove evidence needed for audit. Organizations should therefore apply:

• purpose limitation and data minimization;
• role-based access and least privilege;
• encryption in transit and at rest;
• retention and secure deletion policies;
• consent and lawful-use requirements where applicable;
• data lineage and audit trails;
• segregation of duties for data changes;
• documented definitions and ownership;
• bias and impact assessment for automated matching or classification.

Regulatory obligations vary by jurisdiction and industry. Privacy laws may grant rights relating to access, correction, deletion, or automated decision-making. Financial, healthcare, and employment data may have additional requirements. Compliance should be treated as an ongoing design constraint, not a final checklist.

13. Best Practices and Limitations

Effective integration programs begin with business questions and data-product requirements rather than technology selection. They define data owners, critical data elements, quality thresholds, source priorities, and acceptable latency. They retain sufficient raw evidence to investigate errors, but do not retain unlimited personal data without a purpose.

Teams should implement automated tests for schema, nullability, valid ranges, uniqueness, referential integrity, freshness, and reconciliation. Tests should be version-controlled and reviewed when business rules change. Monitoring should distinguish source failure, pipeline failure, data-quality failure, and expected business variation.

No integration design is perfect. Full real-time processing increases complexity and cost. Probabilistic entity matching can produce false matches. External data can change definitions without notice. Raw data lakes can become disorganized. AI extraction can be inaccurate and difficult to explain. Reconciliation may identify disagreement without revealing which source is correct. These limitations require human judgment, clear ownership, and iterative improvement.

Contemporary Developments

Modern platforms increasingly support metadata-driven pipelines, data observability, open table formats, event-driven architectures, and data catalogs. Data mesh ideas emphasize domain ownership and data products, while centralized governance establishes shared standards and controls. Generative AI can assist with schema documentation, mapping suggestions, SQL generation, and anomaly investigation, but generated outputs must be tested and governed like any other production artifact.

The durable principles remain stable: define meaning, preserve lineage, test quality, control access, make processing recoverable, and reconcile important results.

Chapter Summary

Data integration combines heterogeneous operational, external, streaming, and unstructured data into a coherent analytical environment. ETL transforms before loading, whereas ELT loads first and transforms in the target platform; hybrid patterns are common. Data cleansing addresses completeness, accuracy, validity, consistency, uniqueness, timeliness, and integrity. Transformations must preserve business meaning and declare the grain of analytical facts. Reconciliation uses counts, totals, segmented controls, and balance identities to detect missing or duplicated data. Reliable pipelines require idempotency, retries, quarantine, schema management, observability, and backfill capability. Change-data capture reduces repeated extraction by identifying inserts, updates, and deletes, but must handle watermarks, transaction order, duplicates, and deletion semantics. Governance, privacy, security, lineage, and human review are essential, especially when AI is used to process sensitive or unstructured data.

Key Terms

Data integration: Combining data from multiple sources into a coherent and usable environment.

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

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

Data pipeline: An orchestrated sequence that moves, transforms, validates, and publishes data.

Data cleansing: Detecting and correcting data defects while preserving valid information.

Data quality: The fitness of data for a specified use, including accuracy, completeness, and timeliness.

Data contract: An explicit agreement about a dataset’s fields, meaning, format, delivery, and ownership.

Reconciliation: Comparing stages or systems to verify completeness and consistency.

Data grain: The level of detail represented by one row in a fact table.

Fact table: A table containing measurable business events or observations.

Dimension table: A table containing descriptive context for facts.

Slowly changing dimension: A method for managing changes in descriptive attributes over time.

Change-data capture: Identifying source inserts, updates, and deletes for downstream processing.

Watermark: A recorded position or time used to identify the next incremental extraction boundary.

Idempotency: The property that repeating an operation does not create unintended additional effects.

Event time: The time at which a business event occurred.

Processing time: The time at which a system processed an event.

Data lineage: Documentation of data origins, transformations, movements, and destinations.

Quarantine: Isolating records that fail validation for investigation and controlled resolution.

Data lakehouse: An architecture combining flexible data-lake storage with managed analytical capabilities.

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?
3. Name four dimensions of data quality.
4. What problem does change-data capture address?
5. Distinguish event time from processing time.

Long Answer Questions

1. Compare ETL, ELT, and hybrid integration designs, including their advantages and risks.
2. Explain how a data pipeline can be made reliable and recoverable.
3. Describe cleansing, transformation, and reconciliation procedures for a customer-sales dataset.
4. Explain Type 1 and Type 2 slowly changing dimensions and identify an appropriate use for each.
5. Discuss governance, privacy, and ethical risks in integrating external and unstructured data.

Analytical / Application Questions

1. A company’s source system reports 50,000 orders, while the warehouse contains 49,600. Design a reconciliation investigation that identifies whether the difference is caused by extraction, rejection, duplication, late arrival, or business-rule filtering.
2. Design a CDC approach for a high-volume order database that includes inserts, updates, and deletes. Justify the method and explain how you would handle duplicate delivery and source outages.
3. An organization wants real-time inventory alerts but receives supplier files nightly and store transactions intermittently. Propose an architecture that combines streaming and batch integration, and specify the quality controls required before publishing alerts.

Multiple Choice Questions with Answers

Question 1

Which statement best distinguishes ETL from ELT?

A. ETL transforms data before loading, while ELT transforms data after loading.

B. ETL can process only structured data, while ELT can process only unstructured data.

C. ETL always provides real-time processing, while ELT always uses batch processing.

D. ETL requires no data-quality controls, while ELT requires them.

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

Question 2

If 9,700 of 10,000 expected records contain a required value, what is the completeness rate?

A. 9.7%

B. 30%

C. 97%

D. 103%

Correct Answer: C. 97%

Question 3

Which fact-table design principle prevents measures from being unintentionally duplicated?

A. Using the largest possible number of columns

B. Declaring the grain of the fact table

C. Removing all dimension tables

D. Replacing all null values with zero

Correct Answer: B. Declaring the grain of the fact table

Question 4

Which CDC method commonly reads database transaction logs?

A. Snapshot comparison

B. Timestamp-based extraction

C. Log-based CDC

D. Increasing-key extraction

Correct Answer: C. Log-based CDC

Question 5

What is the primary purpose of a quarantine area in a data pipeline?

A. To permanently delete all invalid records

B. To isolate records that fail validation for investigation

C. To increase the speed of every transformation

D. To replace the source system

Correct Answer: B. To isolate records that fail validation for investigation

Question 6

A Type 2 slowly changing dimension is primarily used to:

A. Overwrite all historical attribute values

B. Preserve changes in dimension attributes over time

C. Remove all surrogate keys

D. Convert streaming data into images

Correct Answer: B. Preserve changes in dimension attributes over time

Question 7

Which example best represents event time?

A. The time a processing server receives an event

B. The time an analyst opens a dashboard

C. The time a purchase actually occurred

D. The time a database backup begins

Correct Answer: C. The time a purchase actually occurred

Question 8

Which property means that rerunning the same input does not create unintended duplicates?

A. Idempotency

B. Latency

C. Completeness

D. Partitioning

Correct Answer: A. Idempotency

Question 9

Why is a nightly reconciliation still useful in a streaming architecture?

A. Streaming systems cannot contain any data

B. It can identify missing, late, or inconsistent events

C. It eliminates the need for event identifiers

D. It guarantees that all source data is accurate

Correct Answer: B. It can identify missing, late, or inconsistent events

Question 10

Which action is most appropriate when a missing discount value may mean either zero or ‘not recorded’?

A. Always replace it with zero

B. Always delete the transaction

C. Define the meaning and apply a documented rule

D. Copy a random value from another transaction

Correct Answer: C. Define the meaning and apply a documented rule