- What is the difference between normal load and Bulk load in session properties?
Bulk loading improves the performance of a session that inserts a large amount of data to the target database. When bulk loading, the Integration Service bypasses the database log, which speeds performance. Without writing to the database log, however, the target database cannot perform a rollback. As a result, you may not be able to perform recovery.
In normal load, the database log is not bypassed and therefore the target database can recover from an incomplete session.
When you are using bulk load, if there are any indexes defined on the table, you have to drop them before loading. After the data is loaded, you can enable or recreate the indexes. If you did not drop the indexes session will fail.
Bulk load is used when you are loading a large amount of data (more than 100GB). Bulk load makes the data loading faster.
2. What is a Rank Transformation?
Rank is an Active Connected Informatica transformation used to select a set of top or bottom values of data
3. How does a Rank Transform differ from Aggregator Transform functions MAX and MIN?
Like the Aggregator transformation, the Rank transformation lets us group information. The Rank Transform allows us to select a group of top or bottom values, not just one value as in case of Aggregator MAX, MIN functions.
4. What are the restrictions of Rank Transformation?
- We can connect ports from only one transformation to the Rank transformation.
- We can select the top (or) bottom rank.
- We need to select the Number of records in each rank.
- We can designate only one Rank port in a Rank transformation.
5. Why is Sorter an Active Transformation?
When the Sorter transformation is configured to treat output rows as distinct, it assigns all ports as part of the sort key. The Integration Service discards duplicate rows compared during the sort operation. The number of Input Rows will vary as compared with the Output rows and hence it is an Active transformation.
6. How does Sorter handle Case Sensitive sorting?
The Case Sensitive property determines whether the Integration Service considers case when sorting data. When we enable the Case Sensitive property, the Integration Service sorts uppercase characters higher than lowercase characters.
7. How does Sorter handle NULL values?
We can configure the way the Sorter transformation treats null values. Enable the property Null Treated Low if we want to treat null values as lower than any other value when it performs the sort operation. Disable this option if we want the Integration Service to treat null values as higher than any other value.
8. How does a Sorter Cache works?
The Integration Service passes all incoming data into the Sorter Cache before Sorter transformation performs the sort operation.
The Integration Service uses the Sorter Cache Size property to determine the maximum amount of memory it can allocate to perform the sort operation. If it cannot allocate enough memory, the Integration Service fails the session. For best performance, configure Sorter cache size with a value less than or equal to the amount of available physical RAM on the Integration Service machine.
If the amount of incoming data is greater than the amount of Sorter cache size, the Integration Service temporarily stores data in the Sorter transformation work directory. The Integration Service requires disk space of at least twice the amount of incoming data when storing data in the working directory.
9. Have you ever used Incremental Aggregation Option which is a part of session properties? If yes, which type of changes we can capture through this setting?
Using incremental aggregation, we apply captured changes in the source data (CDC part) to aggregate calculations in a session. If the source changes incrementally and we can capture the changes, then we can configure the session to process those changes. This allows the Integration Service to update the target incrementally, rather than forcing it to delete previous loads data, process the entire source data and recalculate the same data each time you run the session.
Use incremental aggregation when the changes do not significantly change the target. If processing the incrementally changed source alters more than half the existing target, the session may not benefit from using incremental aggregation. In this case, drop the table and recreate the target with entire source data and recalculate the same aggregation formula.
INCREMENTAL AGGREGATION, may be helpful in cases when we need to load data in monthly facts in a weekly basis.
10. How do you configure a SQL transformation?
The following options are required to configure SQL transformation:
Mode: Specifies the mode in which SQL transformation runs. SQL transformation supports two modes. They are script mode and query mode.
Database type: The type of database that SQL transformation connects to.
Connection type: Pass database connection to the SQL transformation at run time or specify a connection object.
What are the different modes in which a SQL transformation runs?
SQL transformation runs in two modes. They are:
Script mode: The SQL transformation runs scripts that are externally located. You can pass a script name to the transformation with each input row. The SQL transformation outputs one row for each input row.
Query mode: The SQL transformation executes a query that you define in a query editor. You can pass parameters to the query to define dynamic queries. You can output multiple rows when the query has a SELECT statement.
Read next: Informatica interview questions and answers-2
