Data Integration & the ETL Process

Three ways to integrate data

Before data reaches a warehouse it must be integrated from many sources. There are three general approaches, and the exam contrasts them:

- Consolidation (ETL / data warehousing). Physically copy the data into a central store (the warehouse). *Pros:* users isolated from source-system load; history retained; fast access to a purpose-built store; efficient batch transforms. *Cons:* high network/storage/maintenance cost; performance can degrade as the warehouse grows. This is the data-warehouse approach and the focus of this module. - Federation (Enterprise Information Integration, EII). Build a virtual, real-time unified view *without moving the data* — queries are resolved against the sources on demand. *Pros:* always-current data; simple for the calling app; ideal when copies aren't permitted. *Cons:* heavy per-query workload (all integration happens at query time); often read-only. - Propagation (Enterprise Application Integration / replication). Duplicate data in near-real time as updates occur at the source. *Pros:* near-real-time availability; transparent to applications. *Cons:* real overhead to keep the duplicates synchronized.

Most large environments mix all three for different feeds. Consolidation when you need history and complex repeated queries; federation when data must be current or copies are forbidden; propagation when near-real-time matters.

ETL: turning operational data into reconciled data

Within the consolidation approach, ETL (Extract–Transform–Load) is the reconciliation pipeline that converts messy operational data into clean reconciled data. It has five steps:

1. Map & manage metadata — document which source elements map to which warehouse
                           columns, with the transformation and cleansing rules.
2. Extract (Capture)     — pull the needed subset from the sources. STATIC extract =
                           a full snapshot (initial load); INCREMENTAL extract =
                           only changes since last capture (ongoing, often from logs).
3. Scrub (Cleanse)       — find and fix errors/inconsistencies (data scrubbing).
4. Transform             — convert to warehouse format: select, join, normalize,
                           aggregate, apply field-level functions, generate keys.
5. Load & Index          — write the result to the warehouse and build/maintain
                           indexes. REFRESH mode bulk-rewrites; UPDATE mode writes
                           only changes (used with periodic data to keep history).

Worked example: the matched extract/load modes

The extract and load modes pair up, and the exam likes the pairing:

INITIAL load:   static extract  (full snapshot)   +  refresh mode (bulk rewrite)
ONGOING upkeep: incremental extract (only changes) +  update mode  (write changes,
                                                       never delete → periodic data)

Changed data capture is what makes incremental extract efficient — it identifies what changed since the last run (via flags, last-update timestamps, or transaction-log after-images) so you never re-scan the whole source.

Why scrubbing dominates the effort

The single most quoted fact: 60–80% of a business-intelligence project's work is ETL, and the lion's share of *that* is the scrub/cleanse step. Source data is genuinely filthy — a name spelled a hundred ways, impossible dates, a phone-number field repurposed to hold interest rates, the same customer keyed differently in three systems. The discipline is: scrubbing identifies errors; the source systems should fix them. ETL detects, corrects what it safely can, rejects what it can't, and sends messages back upstream so the bad data stops being created — rather than silently papering over it in the warehouse.