ODI Interview Question and Answers 04 September 2025

Q: Can you tell me the architecture of ODI?

A: ODI follows a modular architecture with components:
- ODI Studio (design-time client for mappings, load plans, scenarios).
- Repositories (Master & Work) to store metadata.
- ODI Agent (Standalone/Java EE) to execute jobs.
- Topology for defining physical/logical data servers.
- Scenarios & Load Plans for execution in different contexts.

Q: Suppose you are migrating from 11g to 12c. What challenges do you face and how to handle that?

A: Migration challenges include:
- Repository upgrade (Master/Work migration scripts).
- Topology & security changes.
- ODI 12c introduces declarative mappings instead of interfaces.
Approach: Backup repos, run ODI upgrade assistant, validate mappings, test load plans, resolve deprecated KM usage.

Q: Suppose one load plan got failed in production. How to find out the issue and fix that?

A: Steps:
- Check ODI Operator logs for error details.
- Verify agent logs.
- Check source/target connectivity.
- Rerun failed step from restartability point.
- Fix root cause (data issue, connection, KM error) before re-execution.

Q: How to integrate ODI with Oracle Cloud or any third-party API?

A: Approaches:
- Use ODI Technology: RESTful Web Services / SOAP.
- Leverage ODI Procedure with Groovy/Java for API calls.
- Use Staging tables for JSON/XML parsing.
- For Oracle Cloud, configure topology with Oracle DBaaS/Autonomous DB endpoints.

Q: How to track the DML operation in SQL?

A: Methods:
- Triggers to log INSERT/UPDATE/DELETE in audit table.
- Use ORA_ROWSCN pseudocolumn.
- Use Flashback Data Archive for history.
- LogMiner for redo log mining.

Q: What is the substitute of API in ODI?

A: In ODI, Knowledge Modules (KMs) act as reusable templates like APIs.
Also, ODI SDK (Java/Groovy) provides programmatic API for automation.

Q: What is load balancing?

A: Load balancing distributes workloads across multiple servers/agents to avoid bottlenecks and optimize performance.

Q: What is data quality control?

A: Data Quality Control ensures data correctness, completeness, consistency.
- Implemented via ODI CKM (Check Knowledge Module).
- Example: Constraint checks, duplicate checks, referential integrity.

Q: How to load valid record in one table and invalid record in another table?

A: Use CKM for constraint validation.
- Mapping flow → valid rows to Target_Valid, invalid rows (errors) to Target_Error.
- Can also implement filter conditions in mapping.

Q: Suppose we have 3 sources (API, JSON, CSV) and want to load into a single DB table. What is the approach?

A: Steps:
- Extract API → staging table.
- Load JSON using ODI JSON driver → staging.
- Load CSV via File technology → staging.
- Union all staging data → final target table.

Q: What are the views?

A: Views are logical representations of queries stored in DB.
- Types: Simple, Complex, Materialized.
- Used for abstraction, security, performance (materialized).

Q: What is contextual agent in ODI?

A: A logical agent linked with context.
- ODI resolves to physical agent based on execution context (Dev/Test/Prod).
- Useful for portability of scenarios.

Q: What are the different types of facts?

A: Types:
- Additive facts (summable across all dimensions).
- Semi-additive (summable across some dimensions).
- Non-additive (not summable, e.g., ratios).
- Derived/Calculated facts.

Q: What are the different types of dimensions?

A: Types:
- Conformed, Junk, Degenerate, Role-playing, Slowly Changing Dimension (SCD), Static, Time.

Q: What are the different ways of prompting?

A: Ways include:
- ODI Variables (prompt at runtime).
- User input parameters in Load Plans.
- BI/Reporting tool prompts.

Q: What is SCD?

A: Slowly Changing Dimension — handles changes in dimension attributes.
- Type 1: Overwrite.
- Type 2: History tracking with versioning.
- Type 3: Limited history with previous/current fields.

Q: How to load multiple files into a DB table?

A: Methods:
- ODI File technology with wildcards.
- ODI procedure using UTL_FILE/OS command to iterate files.
- Use staging and append to target.

Q: How to handle incremental data load?

A: Methods:
- ODI CDC (Change Data Capture).
- Using LAST_UPDATE_DATE column.
- Using ODI variables to capture max date/key.

Q: What about lookups and joins?

A: In ODI mappings:
- Lookups are defined as lookup components for reference data.
- Joins can be inner/outer, done via mapping join components.
- Pushdown optimization executes joins at source DB.

Q: How to load data from XML to relational DB table?

A: Use ODI XML technology:
- Define XML model via XSD.
- Reverse engineer.
- Map XML elements to target relational table.
- Load via XML driver/KM.

Q: What are different types of Oracle Data Warehouses and their architecture?

A: Types:
- Enterprise Data Warehouse (EDW).
- Data Mart (department-level).
- Operational Data Store (ODS).
- Architecture: Source → Staging → DW (fact/dim) → Data Marts → BI Layer.

Q: What are partition and its types?

A: Partitioning splits large tables for performance.
Types:
- Range, List, Hash, Composite (Range-Hash, Range-List).
- Interval (auto range).
- Reference (child follows parent partition).
- System, Virtual column.

Q: Can you tell me the cursor?

A: A cursor is a pointer to the result set of a query in PL/SQL.
- Types: Implicit, Explicit, Cursor FOR loop, Parameterized.
- Attributes: %FOUND, %NOTFOUND, %ROWCOUNT, %ISOPEN.
- Used to fetch/process rows one by one in PL/SQL.

Click here for detailed answers

More Question and Answers