Information Integration: From Messy Sources to Trustworthy Data

Combining two datasets is rarely a matter of aligning column names. The difficult work is deciding whether two fields mean the same thing, whether two records describe the same entity, which source wins when values disagree, and how a consumer can trace the result.

Four layers of integration

Layer	Question	Example failure
Syntax	Can the bytes be parsed?	A quoted CSV field contains a newline
Schema	Which fields correspond?	venue means a name in one source and an ID in another
Entity	Which records refer to the same thing?	MIT and Massachusetts Institute of Technology split
Semantics	Which value is valid in this context?	Two systems use different definitions of “active”

Passing the first layer does not prove the data is integrated. A pipeline can parse perfectly while silently merging different concepts.

In plain terms: formatting asks whether two labels can fit in the same box. Semantics asks whether they should be in the same box at all.

Warehouse or federation

A warehouse copies and transforms source data into a controlled target. It supports stable performance, history, and governance, but introduces ingestion delay and duplicated state.

A federated query leaves data at each source and combines it at query time. It can be fresher and avoids a full copy, but performance and correctness depend on source availability, latency, and query capability.

Need	Prefer materialization	Prefer federation
Repeatable analytics and history	✓
Strictly current source value		✓
Predictable query latency	✓
Data cannot be copied		✓
Source has limited filtering or joins	✓

Many systems use both: materialize stable analytical facts, then federate a narrow live lookup.

Schema mapping needs explicit rules

For every target field, record:

• source field and source system;
• transformation and units;
• null and invalid-value policy;
• authoritative source or precedence rule;
• effective time and refresh cadence;
• owner and downstream consumers.

This turns a collection of scripts into a data contract. When a source changes price from dollars to cents, the failure becomes detectable rather than mysterious.

Entity resolution is a decision process

Exact string equality is too strict, while fuzzy matching alone is too eager. A safer flow is:

1. Normalize: case, punctuation, whitespace, Unicode, known abbreviations.
2. Block: generate plausible candidate pairs using a cheap key.
3. Compare: score multiple attributes such as name, email, address, or date.
4. Decide: match, non-match, or manual review.
5. Merge: apply field-level survivorship rules.
6. Preserve lineage: retain source IDs, original values, rule version, and confidence.

For a research database, author name alone is weak evidence. Email, affiliation, coauthors, and publication history can help, but every signal has failure cases. A merged identity should be reversible.

Provenance is part of the data

A trustworthy derived record should answer:

• Which source records produced it?
• Which transformation version ran?
• When was the source observed?
• Which rule chose the surviving value?
• Can the output be rebuilt exactly?

Without provenance, a wrong value becomes a debate. With provenance, it becomes a traceable defect.

Test the pipeline at boundaries

Row count alone is a weak test. Add checks for uniqueness, referential integrity, null distribution, accepted ranges, freshness, and rejected records. Include fixtures with embedded delimiters, quoted newlines, duplicate identities, late arrivals, deleted source records, and unit changes.

-- Orphan check after loading a paper-author relationship.
SELECT pa.paper_id
FROM paper_author AS pa
LEFT JOIN paper AS p ON p.id = pa.paper_id
WHERE p.id IS NULL;

The result should be empty, but the rejected-input table should not be. Quietly dropping malformed records makes a clean dashboard by hiding the problem.

Review card

• Integration spans syntax, schema, identity, and semantics.
• Warehousing copies data; federation integrates at query time.
• Field mappings need units, null policy, precedence, and ownership.
• Entity resolution needs candidate generation, evidence, thresholds, and reversible merges.
• Provenance makes derived facts explainable and rebuildable.
• Test rejected and edge-case data, not only successful row counts.

-- 載入 paper-author relationship 後檢查 orphan。
SELECT pa.paper_id
FROM paper_author AS pa
LEFT JOIN paper AS p ON p.id = pa.paper_id
WHERE p.id IS NULL;