Articles
Oracle ODI Interview Questions: Knowledge Modules, Flow Control, and Failed Loads
Prepare Oracle ODI answers with Knowledge Module roles, flow versus static control, I$/E$ row tracing, and safe failed-load recovery.
“An Oracle Data Integrator mapping stops after staging rows have arrived. The target has only some expected records, and an error table contains rejects. Do you restart the session?” A strong ODI answer does not begin with a button. It identifies the generated task that failed, the Knowledge Modules (KMs) selected for source movement, target integration, and data checks, and the commit or replay boundary. A restart can be appropriate, but only after you know which rows were already accepted and what the failed step would do again.
This guide traces an original four-row mapping through a staging table, a flow-control check, and a hypothetical failed integration task. Evidence boundary: Oracle’s ODI documentation defines KM responsibilities, C$/I$/E$ tables, flow versus static control, and session inspection/restart behavior. The four-row fixture and its explicit validation policy are editorial examples. No candidate report establishes a universal company-specific ODI interview process or one-size-fits-all restart rule.
Map the work to the correct Knowledge Module
ODI uses declarative mappings plus KMs that generate executable tasks. A KM is not a standalone job: it requires mapping, datastore, model, and topology metadata. If the source is on another data server, a Loading KM (LKM) can move source data to the staging area, commonly through a C$ temporary table. An Integration KM (IKM) handles integration from source or staging execution unit into the target. A Check KM (CKM) tests defined constraints; it can act on incoming flow before the target write or on existing target data, depending on control mode.
These roles are not synonyms for three sequential SQL statements that every mapping must run. Oracle offers different KMs and integration strategies. A co-located source may not need the same LKM movement. An append IKM without flow control can issue a direct insert; a control-append strategy may create I$, invoke a CKM, then write accepted rows. Options such as FLOW_CONTROL, STATIC_CONTROL, COMMIT, and error handling change generated tasks. Read the actual physical design and selected KM options before describing the execution path.
| Component | Typical responsibility | Evidence to inspect in a failure |
|---|---|---|
| LKM | Move data from a source server into staging, often C$ | Source connection, extract predicate, transferred count. |
| IKM | Build flow and apply inserts/updates to target | Strategy, generated task SQL, merge key, commits. |
| CKM | Check mapped constraints and record rejects, often E$ | Constraint metadata, control mode, rejected rows. |
The staging area is where transformations and intermediate sets execute, not necessarily the database where the source resides. An interviewer may change the source technology, target database, or staging location. That can change which LKM is appropriate and where the generated SQL runs. Do not memorize “LKM always creates C$ on the target” as a universal architecture claim; Oracle’s guide describes common behavior, and the selected topology is decisive.
Work through four rows and three distinct outcomes
Assume an incoming batch for OrderLine(order_id, product_id, qty), an existing target row for order_id=10, and a valid Product reference set {P1, P2}. The mapping intends one current row per order_id. The incoming rows are deliberately awkward:
| Source row | order_id | product_id | qty | Issue under this exercise’s rules |
|---|---|---|---|---|
| A | 10 | P1 | 2 | Duplicate business key with C. |
| B | 11 | P9 | 1 | Product reference missing. |
| C | 10 | P1 | 3 | Duplicate business key with A. |
| D | 12 | P2 | 4 | Valid new row. |
Specify the policy before predicting row counts: for this editorial fixture, the flow check rejects both incoming rows sharing a duplicate order_id, rejects an unknown product, and allows D. Under that stated policy, I$ initially holds A–D; E$ records A, B, and C with reasons; the accepted flow contains D. If integration and commits then succeed, the target gains D and the original target row 10 remains unchanged. The four source rows reconcile as three rejects plus one accepted row. This is a test oracle for the fixture, not a promise that every Oracle CKM or IKM rejects both duplicates. A different KM may choose one row, fail the batch, or rely on the database constraint; verify its generated code and options.
If the real requirement is “latest update wins,” A and C need a deterministic tie-break such as source sequence, event time plus unique event ID, or a business version. Rejecting both is only one defensible policy. If you pick C without a stable order, a retry can pick A instead and silently change the target. This is why an interview answer should name the merge identity and ordering rule before discussing idempotence.
Oracle describes I$ as a flow table used by an IKM strategy and E$ as an error table created by a CKM. The CKM may remove invalid records from the checked set. E$ rows have origin and error metadata, so they are not merely a copy of a failed target insert. Check the exact selected CKM and its configured constraints: a reference that exists physically but is absent from ODI model metadata might not be checked in flow control, while a physical database foreign key can still reject the target write.
Flow control is not static control
Flow control checks incoming mapped data before it is written to the target. In the common control-append path, the CKM checks I$ against target constraints, records rejected rows in E$, and leaves valid rows for target integration. This can prevent bad incoming rows from contaminating the target, subject to the checks actually defined and the selected strategy.
Static control checks data already in a datastore or checks the target after loading in a mapping, depending on configuration. It is a quality audit of existing state, not a guarantee that every invalid incoming row is quarantined before the write. If the physical database enforces a constraint, an invalid insert may fail and roll back before static control gets a chance to log it. If it does not enforce the constraint, invalid data may reach the target and be detected later. The exact order is visible in the generated session tasks, not implied by the words “static control.”
For the fixture, ask two separate questions: “What would a selected CKM identify in I$ before integration?” and “What could a static check identify in the target afterward?” The first depends on the fixture’s validation policy and the flow option. The second depends on what actually reached the target and which constraints are checked. A candidate who says E$ always contains exactly three rows without naming those conditions is overstating certainty.
Diagnose a failed session before choosing a restart
Open Operator Navigator and locate the session, step, and task in error. Oracle’s monitoring documentation recommends inspecting the code and connection for the failed task. Record the source and target connections, KM name and options, generated SQL, error message, and row statistics. An authentication failure during LKM extract calls for a different recovery than a unique-key violation during IKM target merge. Also check whether the batch is part of a Load Plan, whose exception/restart policy can differ from a standalone session.
Suppose D passed flow control but the integration task failed after a target write. Do not infer from the UI’s red icon that nothing was committed. Oracle says failed-session connections and transactions are rolled back when they are uncommitted; already committed work is a separate matter. Inspect the KM’s commit points and target state. If the target row 12 is present, replaying a plain append could duplicate it or fail on its key. If the target row 12 is absent, a restart may be safe under the same source snapshot—but source rows may have changed during the outage. Capture a batch ID or extract boundary so you know what is being replayed.
Oracle documents restarting a failed session from the failed task by default, and its operator guidance also describes when a restart point cannot be identified and a fresh scenario execution is preferable. That is an operational capability, not a blanket guarantee of idempotence. A restart may skip earlier successful tasks while their staging data persists, or it may encounter cleaned-up temporary tables. Check the actual task state and session log. Editing a generated command in one failed session can aid debugging; Oracle warns it does not fix the source mapping, procedure, package, or scenario. Repair the underlying object and regenerate its scenario where needed.
A recovery ledger for the four-row fixture
A small ledger prevents “rerun it” from becoming an untestable plan:
| Boundary | Expected under stated policy | Check before replay |
|---|---|---|
| Source snapshot | Four rows A–D, batch B17 | Is the extract reproducible? |
| Staged flow | Four rows in I$ before checks | Was staging preserved or rebuilt? |
| Rejected set | A, B, C in E$ | Reasons and origin for each row. |
| Accepted set | D only | Does it have a stable business key? |
| Target after success | Existing 10 plus new 12 | Is 12 present after failure? |
The table is an exercise artifact, not a claim about an actual ODI run. If a retry is planned, make the target operation idempotent for batch B17: for example, reconcile by order_id and a stable source version, or delete/replace one controlled batch partition under a transaction. The exact method depends on whether rows are updates, inserts, corrections, or append-only events. Do not casually delete the entire target because the batch failed. Reconcile counts and keys after the rerun, not just the session status.
If a CKM rejects A, B, and C intentionally, the session can still complete while business completeness is unacceptable. Agree on an error threshold or quarantine policy before treating “green” as success. Conversely, an expected small reject set need not force a full pipeline rollback. Separate technical success, data-quality acceptance, and downstream publication. A practical answer explains who reviews E$, how rejects are corrected, and whether a corrected row is replayed as the same batch or a new version.
Fast answers to likely ODI follow-ups
What does an LKM do? It helps move heterogeneous source data to a staging area or between servers; its generated tasks depend on source and topology. It does not by itself decide target merge semantics.
What does an IKM do? It integrates flow into a target according to a selected strategy such as append or incremental update. Inspect its physical design and commit options for the actual run.
What does a CKM do? It checks modeled constraints, commonly writing rejected records to E$; flow and static control apply it at different points.
Does a row in E$ prove a database insert was attempted? No. Under flow control, a CKM can quarantine it before target integration.
Can I restart at the failed task? ODI can support that, but first verify the persisted staging state, commit boundary, changed source data, and target replay safety. The button is not an idempotency proof.
What if the target constraint fails but the CKM reports no reject? Check whether the ODI model contains that constraint, whether the CKM and flow option ran, whether the constraint is deferred or different in the database, and which generated task failed.
How do you prove recovery? Compare source batch identity, staged count, reject reasons, accepted keys, target before/after, and downstream consumer state. Record the exact task and scenario version used.