Oracle Data Integrator (ODI): Architecture & Knowledge Modules

Explain ODI's E-LT architecture and its Knowledge Module concept, and how it differs from traditional ETL tool architectures.

Oracle Data Integrator (ODI) is Oracle's flagship data integration tool feeding most Oracle-stack data warehouses, and its architectural philosophy — E-LT rather than ETL — is one of the most commonly tested conceptual questions for anyone claiming ODI experience.

Traditional ETL is like hiring a moving crew to carry every box to a rented workshop to reassemble your furniture before finally delivering it to your new house. ODI's E-LT approach is like delivering the boxes straight to your new house and having your house's own well-equipped garage (the target database) do the assembly work on-site, since it already has better tools for the job than a rented workshop would.

Key Concepts

Traditional ETL tools run a dedicated transformation engine *between* source and target — data is extracted, piped through the ETL server's own transformation engine, and then loaded. ODI instead follows an E-LT (Extract, Load, Transform) pattern: it extracts data and loads it into the target (or a staging area) largely as-is, then generates and pushes native SQL down to the target (or staging) database to perform the actual transformation, letting the database's own powerful, already-tuned query engine do the heavy lifting rather than routing data through a separate, potentially weaker transformation server. This is a deliberate performance and infrastructure-cost decision, especially relevant when the target is already a powerful, purpose-built platform like Oracle Database or Exadata.

E-LT (Extract, Load, Transform)generates and pushes native SQL down to the target (or staging) database

The mechanism that makes this database-agnostic and reusable is the Knowledge Module (KM) — a template-like unit of code generation logic that ODI uses to generate the actual SQL/scripts for a given technology and task. There are several KM types: Reverse-Engineering KMs (importing metadata from a source), Loading KMs (LKM — extracting/staging data from a source into the target technology, e.g., LKM SQL to Oracle), Integration KMs (IKM — the core transform-and-load logic, e.g., IKM Oracle Incremental Update implementing a merge/upsert pattern), Check Knowledge Modules (CKM) — validating data against defined constraints, and Journalizing KMs (JKM) — implementing CDC by leveraging the source database's own change-tracking mechanisms (like Oracle GoldenGate or database triggers).

Knowledge Module (KM)Reverse-Engineering KMsLoading KMsIntegration KMsCheck Knowledge Modules (CKM)

A strong interview answer notes that because KMs are essentially customizable templates, ODI developers rarely write raw procedural code for common patterns — instead they select (or lightly customize) the appropriate KM for the source/target technology pair and the desired loading strategy, which is what gives ODI its reputation for high reusability across very different source and target technology combinations within the same project.