How do you approach the challenge of integrating data from heterogeneous sources into a cohesive data model?

Instruction: Explain your methodology for data integration from various sources.

Context: This question evaluates the candidate's ability to handle data integration challenges, ensuring data from different sources can be effectively combined into a unified model.

Updated

Official answer

Define the target entities, record grain and business questions before mapping fields. Inventory source ownership, identifiers, units, time meanings, update/delete behavior, access restrictions and delivery guarantees. Similar column names do not establish equivalent meaning.

Use source-qualified identifiers and an explicit crosswalk for entity resolution. For example, CRM customer 42 and billing customer 42 must not be joined merely because their numeric IDs match. Record the evidence and unresolved cases when mapping them to a shared customer identity.

Choose an authoritative source or conflict rule per attribute, preserve provenance, and separate effective time from arrival time. Normalize formats without inventing absent facts. Validate join cardinality: an order-to-line join may multiply order-level revenue, so retain the intended grain or aggregate at the right stage.

Load reproducible source batches or change streams with correction, deletion and replay handling. Quarantine incompatible records with reasons and reconcile keys, counts and amounts before publication. Version contracts and monitor drift, freshness, unresolved matches and downstream coverage. A rise in user engagement does not by itself demonstrate that integration was correct; use direct data-quality and consumer checks.

Related Questions