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.

ODI's distinguishing idea is where the transformation runs. It is E-LT rather than ETL, and that is the standard question.

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

    ETL    source -> the ETL server's own engine -> target
           the tool does the transforming, on its own hardware

    E-LT   source -> target (or a staging schema) -> transform there
           the target database does the transforming

Why that matters. A warehouse database is already built for set-based work at volume — parallelism, partitioning, optimised joins. An ETL engine is another machine that has to be sized, and data crosses the network to reach it.

ODI generates SQL and runs it where the data is. There is no transformation engine to scale.

Knowledge Modules are how it generates that SQL. A KM is a template for one kind of operation, per technology:

    LKM   Loading         move data between servers
    IKM   Integration     insert or merge into the target
    CKM   Check           enforce constraints, route rejects
    RKM   Reverse         read metadata from a source
    JKM   Journalize      set up change data capture

So a mapping is declarative. You state the source, the target and the transformations; the KM decides the SQL for those technologies.

Change the IKM from a simple insert to an incremental update and the same mapping now merges rather than appends — without rewriting anything.

Which is the real productivity argument: the technical pattern is separated from the business logic. A hundred mappings share one KM, and changing how loading works changes all of them.

The topology is configured separately — physical servers, then logical schemas, with a context mapping between them.

    logical schema "SALES_DW"
      in context "Development" -> dev database
      in context "Production"  -> prod database

So one mapping runs against any environment by switching the context, with no change to the mapping. That is how promotion works, and it is why environment details never appear in the logic.

Where E-LT is a poor fit. When the target cannot do the transformation — a file target, or a source and target on databases with no shared staging. ODI handles these, using a staging area, and the elegance of the model is reduced.

What the interviewer is probing.1. "What makes ODI different from a traditional ETL tool?" Probing: the architecture. Stalls: "Nothing." Moves up: it is ELT — it generates SQL that runs on the target database rather than moving data through its own engine.

2. "What is a knowledge module?" Probing: the central concept. Stalls: "A template." *Moves up:* a reusable code template for a step — loading, integrating, checking, reverse-engineering — which is what makes the generated SQL database-specific.

3. "What are the repositories?" Probing: the structure. Stalls: "One repository." *Moves up:* master for topology and security, work for projects and execution logs — usually with separate work repositories per environment.

4. "What is a CKM used for?" Probing: the quality angle. Stalls: "Nothing." Moves up: enforcing declared constraints during load and routing rejected rows to an error table, which is how data quality is made visible.