Data Warehouse Concepts - Learning Module

Loading content...

0/252

Integrated

The Tower of Babel Problem

Imagine an enterprise with decades of accumulated systems: a mainframe from the 1980s storing customer records as fixed-width COBOL data, an ERP implemented in 2005 using Oracle, a CRM acquired in a merger running on Salesforce, and a modern e-commerce platform built on MongoDB.

Each system speaks its own language:

The mainframe encodes gender as M/F
The ERP uses Male/Female
The CRM offers Man/Woman/Non-Binary/Other
The e-commerce platform stores it as 1/2/3/4

Dates come as YYYYMMDD, MM/DD/YYYY, DD-Mon-YY, and Unix timestamps. Currency appears as cents in one system, dollars with two decimals in another, and local currency with variable precision in a third.

This is the data integration challenge—and it's far worse than format inconsistencies. Semantic differences lurk beneath: What counts as a 'sale'? When is a transaction 'complete'? Who qualifies as an 'active customer'?

A data warehouse addresses this chaos through integration: the systematic process of unifying heterogeneous data into a consistent, meaningful whole.

What You Will Learn

By the end of this page, you will deeply understand data integration: the dimensions of heterogeneity it addresses, the techniques for achieving consistency, the role of ETL in integration, and the governance structures that maintain integration over time. You'll learn to identify integration issues and design solutions that create genuinely unified data.

Defining Integration

Integration in the data warehouse context means that data from multiple, disparate source systems is transformed into a consistent, unified format with reconciled semantics and resolved conflicts.

"Data is gathered from the many operational systems and is put into the data warehouse. The data is coded consistently—naming, attribute measures, encoding structure, physical attributes, data formats, and so forth." — Bill Inmon

What Integration Achieves:

Syntactic Consistency — Common formats, encodings, and representations
Semantic Consistency — Unified meanings and definitions across domains
Structural Consistency — Compatible schemas and relationships
Temporal Consistency — Aligned time references and granularities

Dimensions of Integration

•Naming Conventions — Standardized terminology across the warehouse (customer_id, not cust_num, custID, and customer_number)
•Attribute Encoding — Common code values (M/F → 'Male'/'Female', or numeric codes to text)
•Measurement Units — Consistent units (all weights in kilograms, all currency in base currency)
•Data Types — Compatible types across dimensions (dates as DATE, not VARCHAR)
•Precision and Scale — Uniform numeric precision (all currency to 2 decimals)
•Null Handling — Consistent treatment of missing values (null vs. default vs. special codes)
•Business Definitions — Agreed meanings ('revenue' always includes or excludes certain components)

Integration vs. Aggregation

Integration is not simply combining data—it's reconciling data. You can aggregate disparate records, but if 'customer' means different things in each source, the aggregate is meaningless. True integration requires semantic alignment before combination.

Sources of Heterogeneity

Understanding integration requires understanding what creates heterogeneity in the first place. Differences arise from multiple dimensions:

Types of Data Heterogeneity
Type	Description	Examples
Schematic	Different structures for same concepts	Customer in CRM has 50 columns; in ERP, 20 columns. Address as single field vs. decomposed fields.
Syntactic	Different representations of same values	Date: 2024-01-15 vs 01/15/2024 vs 15-JAN-24. Boolean: true/false vs 1/0 vs Y/N.
Semantic	Different meanings for same terms	'Active Customer': CRM = any login in 90 days; Sales = purchase in 365 days.
Granularity	Different levels of detail	Web: per-click events; POS: daily summaries; Finance: monthly totals.
Temporal	Different time references	Real-time feed vs. daily batch vs. monthly snapshot. Time zones differ.
Technical	Different platforms and formats	Oracle tables, Salesforce APIs, JSON files, CSV exports, MongoDB documents.

The Root Causes:

Heterogeneity isn't accidental—it emerges from organizational realities:

Historical Evolution — Systems implemented at different times reflect different conventions
Departmental Independence — Each team optimized for their workflow without global coordination
Vendor Diversity — Package solutions impose their own data models
Mergers & Acquisitions — Acquired companies bring incompatible systems
Technology Evolution — Modern platforms use different paradigms than legacy systems

Semantic Heterogeneity is the Hardest

Technical differences are solvable with code. Semantic differences require organizational decisions. When Finance says 'revenue' includes pending orders and Sales says it doesn't, no ETL logic resolves this—only business governance can. This is why integration projects are as much about process as technology.

Integration Techniques

Achieving integration requires a systematic approach applied during the ETL (Extract-Transform-Load) process. Here are the core techniques:

Standardization Techniques

•Code Mapping / Value Mapping — Transform source-specific codes to standard values. E.g., map CRM's 'M'/'F' and ERP's 'Male'/'Female' to warehouse standard 'M'/'F'.
•Format Conversion — Convert dates, numbers, and strings to standard formats. All dates become YYYY-MM-DD; all phones become E.164 format.
•Unit Conversion — Normalize measurements. Convert pounds to kilograms, local currency to base currency using specified rates.
•Precision Normalization — Align numeric scales. If sources have 2, 4, and 6 decimal places, standardize to the required precision.
•Null/Default Standardization — Apply consistent treatment for missing values. Replace empty strings, placeholder values (-1, 'N/A'), and nulls with standard representation.
•Case Normalization — Standardize text casing (uppercase, lowercase, title case) for consistent matching and grouping.
•Name/Address Standardization — Apply parsing and formatting rules to names and addresses for consistent representation.

Integration Mapping Example
SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
-- ==============================================
-- EXAMPLE: Gender Code Standardization
-- ==============================================
 
-- Source mappings table
CREATE TABLE etl_code_mapping (
    domain          VARCHAR(50),    -- 'GENDER', 'ORDER_STATUS', etc.
    source_system   VARCHAR(50),
    source_value    VARCHAR(100),
    standard_value  VARCHAR(100)
);
 
INSERT INTO etl_code_mapping VALUES
-- Gender mappings from various sources
('GENDER', 'CRM_LEGACY', 'M', 'Male'),
('GENDER', 'CRM_LEGACY', 'F', 'Female'),
('GENDER', 'ERP_SAP', 'Male', 'Male'),
('GENDER', 'ERP_SAP', 'Female', 'Female'),
('GENDER', 'ECOM_MONGO', '1', 'Male'),
('GENDER', 'ECOM_MONGO', '2', 'Female'),
('GENDER', 'ECOM_MONGO', '3', 'Non-Binary'),
('GENDER', 'ECOM_MONGO', '4', 'Unknown');
 
-- Transformation query
SELECT 
    src.customer_id,
    COALESCE(
        map.standard_value, 
        'Unknown'  -- Default for unmapped values
    ) AS gender_standardized
FROM source_customer src
LEFT JOIN etl_code_mapping map 
    ON map.domain = 'GENDER'
    AND map.source_system = src.source_name
    AND map.source_value = src.gender_code;

Entity Resolution and Matching

Perhaps the most challenging aspect of integration is entity resolution (also called record linkage or data matching)—determining when records from different sources represent the same real-world entity.

The Problem:

CRM has: 'Jon Smith, jsmith@email.com, 555-1234'
ERP has: 'Jonathan Smith, jonathan.smith@company.com, (555) 123-4000'
Loyalty has: 'J. Smith, 12 Oak Street, Anytown USA'

Are these the same person? The answer is critical—merge incorrectly and you conflate different people; fail to merge and you fragment your customer view.

Entity Resolution Approaches

•Deterministic Matching — Exact match on one or more keys. If SSN matches exactly, it's the same person. Simple but limited—requires reliable common identifiers.
•Probabilistic Matching — Score similarity across multiple attributes; merge if score exceeds threshold. Accounts for typos, variations, missing data. More flexible but requires tuning.
•Rule-Based Matching — Expert-defined rules: 'If email matches exactly OR (first name matches AND phone matches within 1 digit), merge'. Transparent but labor-intensive.
•Machine Learning Matching — Train models on labeled match/non-match pairs. Scale and accuracy benefits, but requires training data and explainability challenges.
•Hybrid Approaches — Combine methods: deterministic for high-confidence matches, probabilistic for remainder, human review for uncertain cases.

Converting Mermaid diagram...

Golden Records

Once records are matched, a golden record is created—the single, authoritative version of the entity. This requires survivorship rules: which source provides the 'best' value for each attribute? Email from CRM (primary system), address from most recent order, name from official billing records. These rules codify business logic into the integration process.

Conformed Dimensions

Conformed dimensions are a critical concept from Ralph Kimball's dimensional modeling methodology. A conformed dimension is a dimension that means the same thing across all fact tables and data marts that reference it.

Why Conformance Matters:

Without conformed dimensions, integration breaks down at the analytical level. If 'dim_customer' in the Sales mart has different keys or attributes than 'dim_customer' in the Marketing mart, they cannot be joined. Reports become incompatible. 'Customer' stops being a unified concept.

Non-Conformed (Broken)

•Sales mart: dim_customer with customer_sk
•Marketing mart: customer_dim with cust_id
•Service mart: customers with c_key
•Different keys, attributes, grain
•Cannot compare sales vs. marketing data
•Each team has 'their' customer definition

Conformed (Integrated)

•Single dim_customer shared across marts
•Same customer_key in all fact tables
•Identical attributes and definitions
•Guaranteed join compatibility
•Cross-mart analysis is natural
•Organization has ONE customer definition

Building Conformed Dimensions:

Identify Common Dimensions — Customer, Product, Date, Employee appear across multiple business processes
Define Once, Share Everywhere — A central team owns each conformed dimension definition
Standardize Keys — All references use the same surrogate keys
Synchronize Updates — When dim_customer changes, all dependent marts see the change
Subset, Don't Diverge — Marts may use subset views of a conformed dimension, but never divergent definitions

The Date Dimension as Universal Example

The date dimension is the canonical example of a conformed dimension. Every fact table has date keys. If sales data and marketing data use different date definitions (fiscal vs. calendar, different week boundaries), cross-analysis fails. A single, conformed dim_date ensures 'Q4 2024' means the same thing everywhere.

The ETL Role in Integration

ETL (Extract-Transform-Load) processes are the primary mechanism for achieving integration. The 'Transform' phase is where integration happens—where heterogeneous source data becomes unified warehouse data.

Integration Tasks in ETL Transform Phase

•Data Cleansing — Correct errors, remove duplicates within sources, fix formatting issues before integration
•Value Standardization — Apply code mappings, format conversions, and unit transformations
•Entity Resolution — Execute matching logic to identify and link records across sources
•Golden Record Construction — Apply survivorship rules to create authoritative records from matched sources
•Relationship Population — Build cross-references and establish foreign key relationships
•Derived Attribute Calculation — Compute integrated metrics that span sources (total lifetime value from all channels)
•Temporal Alignment — Normalize time zones, align effective dates, establish consistent temporal semantics
•Audit Trail Creation — Track source attribution, transformation lineage, and data quality metrics

ETL Integration Pattern Example
SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
-- ==============================================
-- INTEGRATION TRANSFORMATION EXAMPLE
-- Unified Customer from Multiple Sources
-- ==============================================
 
INSERT INTO dim_customer (
    customer_key, customer_id, full_name, email, phone,
    address_standard, city, state, postal_code,
    first_purchase_date, customer_segment, loyalty_tier,
    source_crm, source_erp, source_ecom,
    effective_date, is_current
)
WITH matched_customers AS (
    -- Entity resolution already performed
    SELECT * FROM stg_customer_matches
),
survivorship AS (
    SELECT 
        m.unified_customer_id,
        -- Name: prefer CRM (primary system)
        COALESCE(crm.full_name, erp.full_name, ecom.full_name) AS full_name,
        -- Email: prefer most recently updated
        CASE 
            WHEN crm.email_updated > COALESCE(erp.email_updated, '1900-01-01')
                AND crm.email_updated > COALESCE(ecom.email_updated, '1900-01-01')
            THEN crm.email
            WHEN erp.email_updated > COALESCE(ecom.email_updated, '1900-01-01')
            THEN erp.email
            ELSE ecom.email
        END AS email,
        -- Address: prefer ERP (billing system)
        COALESCE(erp.address, crm.address, ecom.address) AS address_standard,
        -- First purchase: earliest across all sources
        LEAST(
            COALESCE(crm.first_order_date, '2099-12-31'),
            COALESCE(erp.first_order_date, '2099-12-31'),
            COALESCE(ecom.first_order_date, '2099-12-31')
        ) AS first_purchase_date,
        -- Track which sources contributed
        crm.customer_id AS source_crm,
        erp.customer_id AS source_erp,
        ecom.customer_id AS source_ecom
    FROM matched_customers m
    LEFT JOIN stg_crm_customer crm ON m.crm_key = crm.customer_id
    LEFT JOIN stg_erp_customer erp ON m.erp_key = erp.customer_id  
    LEFT JOIN stg_ecom_customer ecom ON m.ecom_key = ecom.customer_id
)
SELECT 
    NEXT VALUE FOR seq_customer_key,
    unified_customer_id,
    full_name,
    email,
    -- Additional standardization
    standardize_phone(phone) AS phone,
    address_standard,
    city,
    state,
    standardize_postal_code(postal_code, country) AS postal_code,
    first_purchase_date,
    calculate_segment(lifetime_value, recency) AS customer_segment,
    loyalty.tier AS loyalty_tier,
    source_crm, source_erp, source_ecom,
    CURRENT_DATE AS effective_date,
    TRUE AS is_current
FROM survivorship s
LEFT JOIN stg_loyalty loyalty ON s.unified_customer_id = loyalty.customer_id;

Governance for Sustained Integration

Integration isn't a one-time project—it's an ongoing discipline that requires governance structures to maintain consistency as systems evolve.

Integration Governance Components
Component	Purpose	Implementation
Business Glossary	Authoritative definitions for business terms	Documented definitions of 'customer', 'sale', 'revenue' with source-of-truth designation
Data Stewardship	Assigned ownership for data domains	Named stewards for Customer, Product, Finance data with decision authority
Code/Value Standards	Master lists of valid values	Centralized reference tables for countries, currencies, product categories
Integration Rules	Documented transformation logic	Version-controlled specifications for how source values map to warehouse values
Data Quality Metrics	Ongoing measurement of integration quality	Match rates, standardization coverage, conformance scores tracked over time
Change Management	Process for evolving standards	Governed procedure when source systems change or new sources onboard

The Drift Problem

Without governance, integration degrades over time. New source systems onboard without proper mapping. Existing mappings become stale as source systems evolve. Business definitions drift as new stakeholders reinterpret them. Governance structures exist to prevent this entropy—they're not bureaucracy, they're preservation of integration investment.

Modern Integration Platforms:

While integration principles are timeless, modern tools assist:

Master Data Management (MDM) platforms automate entity resolution and golden record management
Data Catalogs provide searchable business glossaries and lineage tracking
Data Quality tools measure and monitor integration metrics
Metadata Management systems document and version integration rules
Data Contracts formalize expectations between data producers and consumers

Summary: Integration

Integration is the engine that transforms chaotic, contradictory data into a coherent analytical foundation. Let's consolidate the key insights:

Key Takeaways

•Integration unifies heterogeneous data — It resolves syntactic, semantic, schematic, and temporal differences across sources.
•Heterogeneity has organizational roots — History, departmental independence, vendor diversity, and M&A create the fragmentation integration must address.
•Standardization techniques are the building blocks — Code mapping, format conversion, unit normalization, and naming conventions create consistency.
•Entity resolution is the hard problem — Determining when records represent the same entity requires sophisticated matching approaches.
•Conformed dimensions extend integration to analytics — Shared dimensions ensure that integrated data can actually be analyzed together.
•ETL is the execution mechanism — Transform logic implements integration decisions made through governance.
•Governance sustains integration over time — Without ongoing stewardship, integration degrades as systems evolve.

What's Next:

With subject-orientation and integration established, we now turn to Time-Variance—the characteristic that preserves historical depth. Unlike operational systems that maintain current state, data warehouses capture data across time, enabling trend analysis, point-in-time reporting, and understanding of how subjects evolve.

Page Complete

You now understand Integration—the process of unifying heterogeneous data into consistent warehouse content. From standardization techniques to entity resolution to conformed dimensions, you have the conceptual toolkit for achieving true data integration. Next, we explore Time-Variance: how warehouses preserve history.