- Where did u use the Unix shell scripts in informatica projects?
- how to u generate surrogate keys for tables with more than 2 billion records (surrogate key is a primary key field).
- how do u propagate date coloumn to a flat file, if u need format to be DD-MON-YYYY?
- if a look up returning multiple matching rows, then how Unconnected lookup & Connected lookup will perform? (Not using lookup by last value)
- If you use sorted I/P option in Aggregator but gave it Unsorted I/P then what will happen?
- If i have 100 rows given as I/P to aggregator & want 100 rows as O/P then how can u achieve that?(none of the coloumns are primary key)
- If i have 100 rows given as I/P to aggregator & want just 100th row as O/P then how can u achieve that?
- What kind of stored procedures are there in informatica
- If i use pre-session stored procedure, then i need to activate another field, What would that be?
- What are all the conditions (=, Not Between) you have in Lookup & in Joiner?
- What is the use of Reusable sequence generator (not in the resuability sense), any other?
- If i had a flat file, Can i over ride SQL in Source Qualifier or Lookup?
- If i have a flat file target, When i click it in the Workflow manager, What all properties would i get?
- I have to use Order by, I have a table with coloumns A,B,C...I need order by B,C ..Do u need to add some more thing to "select * from TABLE NAME order by B,C" ???
- What is the use of Return Port & Output port in Lookup Transformation?
- If i have used 2 update strategys in my mapping..1 for insert & other for delete, then i changed target option in session properties from datadriven to delete...then how is my mapping going to perform ? (all deletes or insert & delete)
- To concatinate 2 or more Flat files, Workflow scheduling, File watcher script.
- Dont use sequence generator, But use a Expression variable increment & look up transformation to get the last value used from target.
- Using To_date function before loading to flat file
- Fails the session with error messg 'Expecting keys to be ascending'.
- Aggregator is an active transformation. So you can't expect it to give exact number of output rows for all input rows you have given to it.
- If you dont select any group by port in ports tab of aggregator transformation then informatica is only going to give last row as output for all the numerous records given to it as input.
- Lookup (=, !=, >=, <=, >, <), Joiner (= only )
- You can never over ride a SQL Query when you are playing with flat files.
- File Writer, Merge Partionned file, Merge File name, Merge File Dir, O/P file name, O/P file dir, Reject file name, Reject file dir.
- By default all the ports in lookup transformation are Look up & O/P ports. Return ports are only used in Unconnected lookup & You need have atleast 1 Output port in Connected lookup. (cant explain in detail here...)
- Workflow succeds but you get an error mesg in logs saying target did not permit to insert & all the records marked for inserts are loaded into badfile.
How would u rate urself on oracle, UNIX, DWH Concepts, Informatica
Why you need surrogate key instead of OLTP primary key, Tell a scenario where it’s mandatory to use surrogate key
Ans: If prod key is numeric before & OLTP People decided to go for alphanumeric, we may have to change all data types. in warehouse where that key is involved. So best to keep it away from business defined values, To track SCD
Scenario for using dynamic lookup
Ans: Loading data from flat file to a table, But the data in file is violating primary key constraint of table due to
How would you tune performance for a given scenario, scenario explained by interviewer!
Scenario where you developed some procedures
Scenario where you developed some UNIX scripts
Scenario where you encountered toughest time in job
In your complete job profile, what are the documentations you have done?
What’s the default & Max size of data cache?
20 MB, 2 GB
What is materialized view, how it’s going to improve performance.
Ans: It creates physical table unlike normal view
When you use bitmap indexes
Ans: when cardinality is low, distinct rows <> Newfile.txt
what is implicit cursor & explicit cursor in oracle?
Ans: Implicit cursor is a defined for every query executed. Its attributes can be retrieved after executing query using sql as prefix Ex: sql%rowcount. Explicit cursor is the cursors what we define manually.
what is the cursor attributes
what are procedures you follow for good performance while doing TDD
What are your roles & responsibilities in previous project
What are all the phases in the DWH project from bidding to delivery
How do you communicate any mistakes to team members, so that they won’t be repeated?
how do you ensure all the business rules defined are implemented in development (Traceability matrix)
how did you follow code reviews, what are all the things u check
at what levels in ur project you perform testing
How did u implement defect tracking in ur project?
At what time in the project, you perform the performance tuning
Did you face any situation where you coded mapping wrong & in later stages found it, if yes how did u rectify
Do you have any check list in doing TDD doc, or is it just your experience that helps
tell me the process you followed in developing a mapping, the documents you got & instructions you got
did you have any team members directly reporting to you
how is the work assigned to you in your project & who takes care of assigning
did you involve in status updates of your project to client, if not who does it
how would you stop session row if a value for particular column is matched to a given value
Ans: use abort function of informatica in expression transformation
how about error function in informatica
Ans: it logs the error message you defined in the session log if particular condition is satisfied
levels of logs you can maintain in informatica sessions
Ans: Terse,Normal,And Verbose
how to run sequential sessions only if previous session loads at least one row in target
Ans: Defined a link condition between sessions with TgtSuccessrows>0
difference between max & greatest functions in oracle
Ans: Greatest gives greatest of 2 values, max is the max value of all available values
how you get number of rows returned by lookup when a condition matches
Ans: Define a new column & use SQL override with count(*)
how can you configure informatica workflow to run only on first working day of the month
Ans: Define a calendar & go with a session or script
when you go for sql overrides
Ans: When you want to share some processing load with Database, When it’s more effective
what are types of triggers, difference between row level & statement level trigger
Ans: after/before statement level/row level insert/delete/update
how would you question, if you are in interview panel
What is the advantage of packages in PL/SQL
Ans: Modular approach, Security, Integrity
If you have some err in a procedure of package, how you would know which procedure is it
Can you join 2 tables using SQL override in Lookup transformation
If a session fails, What are the steps you follow in resolving it
Ans: check session logs with various tracing levels
What’s your most difficult situation in development
How you filter records in TOAD
Ans: Use filter icon & Add rule on columns
What is persistent cache in lookup transformation.
Ans: Its remains even after session run, useful in incremental aggregation
how did you implement incremental extraction
Ans: Mostly using set variable or some way of truncating stage tables or use parameter files
tell me about 'set variable' function in informatica
Ans: Set a value to variable depending on the last row processed
if you change properties in session, which one takes preference, is it mapping level or session level
Ans: Session Level
syntax for defining a parameter in parameter file
Ans: Folder - workflow - session – mapplet
How did you move your code from development to UAT or Production?
Did you take back up of your repository at any time? If yes how is it stored & where
Did you install informatica anywhere?
Explain the architecture of informatica
How would you identify why a record is rejected (D,O,N,T Indicators)
Post your answers as comments if am wrong or if you have a better answer.....Thx