ased on the analysis of the provided YouTube video transcript and our previous conversation history, the
B
video is the second part of a series discussing important Informatica PowerCenter interview questions,
presented by Almas. This segment specifically focuses onTransformationsin Informatica.
The video first defines key concepts:
● A Mappingis described as a collection of sources and targets linked together by a series of
transformations. It must contain at least a source and a target.
● ATransformationis defined as a set of rules or instructions applied to data to change and move
it from source to target.
ollowing these definitions, the video presents several interview questions and provides detailed answers,
F
explaining various Informatica transformations and concepts. Here are the questions and answers
covered:
● E
xplain the difference between active and passive transformations.(This was also covered in
the previous video).
○ A nActive Transformationchanges thenumber of input rowsthat pass through it. The
number of incoming rows is different from the number of outgoing rows.
○ APassive Transformationdoesnot change the number of input rows. The number of
incoming rows is equal to the number of outgoing rows.
D
● ifferences between router and filter.
B
○ oth work onconditionsto filter data.
○ Router: Works onmultiple conditions. It hasthree groups: input, output, and adefault
groupfor rows not matching any condition. Records not matching conditions go to the
default group and arenot lost. It works likeIF...ONLY IFor CASEstatements in
SQL.
○ Filter: Works ononly one condition. Itdoes not have any groups. Records not
WHEREclause in SQL.
matching the condition arelost. It works like a
W
● hy Sorter is an active transformation?
○ W hile sorting itself doesn't reduce rows, the Sorter transformation is active because it has
adistinct option.
○ If the distinct option is selected, it retrieves only distinct rows, which changes the number
of input rows relative to the output rows.
W
● hat is the use of Source Qualifier?
○ I t's a transformation that isautomatically createdwhen you drag a source into a
mapping.
○ It represents thesource data.
○ It can perform various functions likejoining,filtering rows,sorting input, selecting
distinct records, and allowing acustom SQL query.
● What are the different ways in which we can filter the data in Informatica?
○ D
ata can be filtered using theSource Qualifier,Router,Filter, andJoiner
transformations.
W
● hat are the different types of groups in Router?
○ T he Router has aninput group, anoutput group, and adefault group. Records that do
not match any defined condition are directed to the default group.
W
● hat is Expression Transformation?
I t is apassive transformation.
○
○ It is used to performcalculationsormanipulate databy applying formulas.
○ It processesone record at a time, and therefore, it does not change the number of input
rows.
W
● hat is a Lookup transformation?
Used tolook up datafrom a particular table, view, or flat file.
○
○ Based on the data found (or not found), actions are decided for the input record.
○ It consists ofinput, output, lookup, and return ports.
Different types of Lookup transformations.
●
○ C onnected Lookup: Integrated directly into the data flow of a mapping. Can return
more than one value.
○ Unconnected Lookup: Not part of the main data flow. It is called usinglkp functions. It
can only returnone value.
A
● n unconnected lookup can have how many input parameters?
○ A
n unconnected lookup can haveany number of input parametersbut will haveonly
one output parameter.
N
● ame different types of lookup caches.
The different types areStatic,Dynamic,Persistent, andShared.
○
How do you differentiate static and dynamic caches?
●
○ D ynamic Cache: Refreshes automatically as soon as there arechanges (insert, update,
delete)in the lookup table during a mapping run. It must beexplicitly configuredas
dynamic.
○ Static Cache: Doesnot changewithin a single mapping run, even if the lookup table is
updated. It only reflects changes in subsequent runs. By default, caches are static.
W
● hat is Persistent Cache?
○ A
cache type used for values that areexpected to remain constantor change very
infrequently (like the capital of a country).
○ I t stores lookup data persistently across mapping runs toreduce lookup time. This
requires enabling thelookup cache persistent property.
H
● ow can we delete duplicate rows from a flat file?
T
○ he Source Qualifier for flat files disables options like distinct selection or custom SQL.
○ Duplicates from a flat file can be removed using aSorter transformationby selecting
thedistinct optionwithin it.
D
● ifferences between SQL override and a Lookup override.
○ S QL Override: An explicitly written SQL query in theSource Qualifier. Primarily used
tolimit the number of rowsread from the source into the mapping. Requires explicit
inclusion of anORDER BYclause if needed. Can return multiple matching records.
○ Lookup Override: An explicitly written query within theLookup transformation.
Used tolimit the number of rowscached or looked up from the lookup source to
improve performance. Uses an ORDER BYclause by default. It is designed to return
only one recordeven if multiple records match the condition.
W
● hat is the Rank transformation?
I t is anactive transformation.
○
○ It is used torank data, allowing the selection of the top or bottom records.
○ It can identify records with thelargest or smallest numeric valuesbased on a specified
port.
W
● hat is Joiner transformation?
○ A significant transformation used to performjoinssimilar to those in SQL (like inner,
outer, etc.).
○ Informatica's Joiner supports normal join, full outer join, master outer, and detail outer
joins.
W
● hat is Aggregator transformation?
I t is anactive transformation.
○
○ Used to performaggregate functions(like sum, min, max, average) on data.
GROUP BYclausein SQL, allowing data to be grouped
○ It functions similarly to the
before applying aggregate functions.
W
● hat is a Sequence Generator?
T
○ his transformationgenerates a series of numbers.
○ It is commonly used when a source lacks aunique primary keyor any primary key, to
help uniquely identify records in the target database.
W
● hat is a Union transformation and what are its restrictions?
I t is anactive transformation.
○
○ Itmerges data from multiple input sourcesinto a single output stream.
○ I t works like UNION ALLin SQL, meaning itdoes not automatically remove
duplicate rows.
○ Restrictions:
■ All input sources must have thesame number of ports, and the data type of
corresponding ports must be thesame.
■ Itdoes not remove duplicates.
■ Youcannot use Sequence Generator or Update Strategytransformations with
the Union transformation.
1. What is actually a router transformation?
A
○ router transformationis used tofilter rows in a mapping.
○ It allows you to specifymore than one condition, unlike a Filter transformation
which specifies only one condition.
○ A Router is anactive transformation.
2. W
hat are the different groups in router transformation?
T
○ here are two types of general groups:inputandoutput groups.
○ Within the output groups, there are two types:user-definedand thedefault
group.
○ Rows that meet the conditions flow into theuser-defined groups.
○ Rows thatdo not meet the conditionsare sent to thedefault group.
3. C
an you connect ports of two output groups from router to a single target?
T
○ he answer isno.
○ Youcannot connect more than one output portfrom a Router to a single
target.
○ One output group will go to only one particular target.
● What is actually Expression transformation in Informatica?
○ E xpression transformation is used tomanipulate row-wise datathrough the
mapping.
○ It means you can performcalculations on the data row-wise, on each row.
○ It is apassive transformation.
○ It can be used to perform anynon-aggregate calculations, like adding
something to a value, subtracting something, or concatenating values.
H
● ow many types of ports are there in an expression transformation?
○ T
here arethree types of portsin an expression transformation:input,output,
and avariable port.
Complex calculations are performed using thevariable port.
○
What is the execution order of ports in an expression transformation?
●
A
○ ll the ports are executedfrom top to bottom serially.
○ The execution is done in the following groups:
■ First, allinput portsare given the values.
■ Then, allvariable portsare executed (calculated based on inputs).
■ Lastly, all theoutput expressionsare executed so values can be sent to
the output port.
W
● hat is the use of variable port in an expression transformation?
○ T he use of variable ports is totemporarily store the datawhile processing is
going on.
○ Variable ports simplifycomplex calculations.
○ For example, they can be used in scenarios where you need toextract the
month part from a date.
H
● ow to generate sequence numbers using expression transformation?
○ T o generate sequence numbers using expression transformation, you can use a
variable port.
○ Create a variable port in the expression transformation andincrement it by one
for every new row.
○ Then, assign this variable to anoutput port.
○ Every time a new row comes, the value will increment by one.
ased on the provided YouTube video transcripts (,), here are the questions asked about the
B
Informatica Lookup Transformation and their answers:
1. What is actually a lookup in ETL?
A
○ lookup is acommon ETL operationused tocalculate a field's value.
○ It works by providing input parameters to the lookup transformation.
○ Based on these parameters, the lookupqueries a database or other data
sourceto return a value or a data set (list of values).
○ The lookup performs this byjoining datain the input columns with columns in
the referenced data set.
○ A lookup transformation can look up data in aflat file, relational table, view, or
synonym.
2. W
hat are the tasks of lookup transformation?
○ T oget a related valueof a field based on input parameters by referencing a data
source.
○ Toperform calculationsusing the value retrieved from the lookup table.
○ T
oupdate slowly changing dimensions (SCD); the lookup is used to check if a
record from the source already exists in the target table based on source data,
and then records are flagged as insert, update, or delete.
3. W
hat is connected and unconnected lookup transformation?
○ A connected lookupisconnected in the mapping pipelinelike other
transformations. It receives data from the source, performs the lookup, and
returns data to the pipeline. It behaves similarly to other connected
transformations.
○ Anunconnected lookupisnot connectedto other transformations in the
mapping pipeline. Other transformations that want to use it do so with the use of
lkpfunction, passing the required input parameters.
the
4. W
hat are the differences between connected and unconnected lookup?
○ D ata Flow:Connected lookup participates in data flow and receives input directly
from the pipeline. Unconnected lookup receives values using the lkpfunction.
○ Cache Type:Connected lookup can use bothdynamic and static cache.
Unconnected lookup can only usestatic cache.
○ Return Values:Connected lookup can returnmore than one value(a data set).
Unconnected lookup can returnonly one value.
○ Caching of Ports:Connected lookup caches all lookup columns. Unconnected
lookup caches only the lookup output ports in the lookup conditions and the
return port.
○ Default Values:Connected lookup supports user-defined default values to return
when lookup conditions are not satisfied. Unconnected lookup does not support
user-defined default values.
5. H
ow do you handle multiple matches in lookup transformation?
○ Y ou use the"lookup policy on multiple match"option in the lookup
transformation.
○ This option determines which row the lookup transformation returns if it finds
multiple rows that match the lookup conditions.
○ You can configure it to returnany row(first, last, or any in between rows
matching) or toreport an error.
6. W
hat are the options available to configure a lookup cache?
○ T
he available options are:persistent cache, static cache, dynamic cache,
shared cache, re-cache from lookup source, and pre-build lookup cache.
7. W
hat is a cached lookup transformation?
○ W hen a lookup is cached, the integration servicebuilds a cache memorywhen
it processes the first row of data.
○ It stores thecondition values in the index cacheandoutput values in the
data cache.
○ T
he integration service willquery the cachefor each new row that enters the
transformation, and then the lookup source if needed.
8. W
hat is uncached lookup?
F
○ or an uncached lookup, the integration servicewill not build any cache.
○ For each new row that enters the lookup transformation, the integration service
willdirectly query the lookup sourceand return a value.
9. W
hat is a dynamic cache?
T
○ he dynamic cacherepresents the data in the target.
○ The integration service builds the cache when it processes the first lookup
request.
○ It queries the cache based on the lookup condition for each row.
○ The integration serviceupdates the lookup cacheas it passes rows to the
target by either inserting a new row, updating an existing row, or making no
change.
10.What is a persistent cache?
○ If the data in the lookup source doesnot change between session runs, a
persistent cache can be used to improve performance.
○ When a session runs for the first time, the integration service creates a cache
file.
○ Instead of deleting the file after the session completes, it issaved to the disk.
○ The next time the session runs, the integration service willbuild memory from
the saved cache file.
11.What is a shared cache?
○ In a shared cache,multiple lookup transformationsin the same mapping can
be configured to share asingle lookup cache.
○ The integration service builds the cache when it processes the first lookup
transformation that shares the cache, and this cache is then used by subsequent
lookup transformations configured to share it.
● What is actually aggregator transformation?
A
○ n aggregator is anactiveandconnected transformation.
○ Itperforms aggregate calculationslike min, max, average, count, sum, and
many others.
W
● hat is actually a joiner transformation?
○ A joiner is a transformation thatjoins the data between two heterogeneous
sources.
○ It can also join the data from the same source.
○ A heterogeneous source refers to different types of sources within a single
mapping, such as an Oracle table, a flat file, and XML. Homogeneous sources, in
contrast, consist of only one type of source, like solely Oracle tables or flat files.
○ A joiner will join the sources only if there is at least one matching column
between them, and the data type of the matching column should be the same.
○ The Joiner is anactive and connected transformation.
H
● ow many joiners are required to join n number of sources?
nnumber of sources, you need
To join
○ nminus 1 joiners.
○ For instance, if you have 10 sources, you would need 10 - 1 = 9 joiners.
What are the different types of joints?
●
○ There arefour types of joints in Informatica:
■ Normal join: The integration service discards all rows from both the
master and detailed sources that do not match the join condition. Only the
rows that match the condition proceed.
■ Master outer join: This type keeps all the rows from the detailed source
and only the matching rows from the master source.
■ Detail outer join: This type keeps all the rows from the master source
and only the matching rows from the detail source. Any unmatched rows
from the detail source are discarded.
■ Full outer join: In this type, rows from both the master and detail sources
are kept; nothing is discarded.
W
● hich table should be selected as the master table in Joiner transformation?
○ In the properties of the Joiner transformation, you can select which source is the
master and which is the detail.
○ During execution, themaster source is cached into memoryfor the joining
process.
○ Therefore, to minimize memory usage, the source with theless number of
records should always be selected as the master source.
H
● ow to improve the performance of Joiner transformation?
○ J oin sorted data whenever possible. It is recommended to always sort the data
before joining.
○ For anunsorted Joinertransformation, designate the source withfewer rows
as the master source.
○ If the data issorted, then designate the source withfewer duplicate keys as
the master source.
W
● hich one is better Joiner or a lookup?
○ A
Joiner is a better choice if you want to join heterogeneous sources, such
as flat files with tables.
○ A
Lookup is a better option if you want to join homogeneous sources,
meaning if both tables are from the same type, like both being Oracle tables.
W
● hat is the difference between a joiner and a lookup transformation?
○ O ne difference is that inLookup, the default join type is a left outer join. In
Joiner, you can explicitly define the join typeas a left outer, right outer, full
outer, or normal join.
○ Another difference is thatLookup allows you to use an SQL override, which
means you can write an SQL query within the Lookup transformation. Thisis not
possible in a Joinertransformation.
ased on the provided YouTube video transcript, here are the questions asked about the
B
Informatica Union and Rank Transformations and their corresponding answers:
● What is actually a union transformation?
The Union transformation is used tomerge the data from multiple sources.
○
○ It works similar to theunion all in SQL statement.
Now Union gives Union all output but how will you get Union output to achieve the
●
union output from Union transformation?
○ O ne way is topass the output of the Union transformation to a sorter
transformation. In the properties of the sorter, youcheck the option "select
distinct". This will give the union output.
○ Another way is topass the output of Union transformation to aggregator
transformationand in aggregator transformationspecify all ports as Group by
ports. These are the two ways you can achieve Union all output as Union output.
W
● hy Union transformation is an active transformation?
○ A union transformation is active because itcombines two or more data
streams into one data stream.
○ Although the total number of rows passing into the union is the same as the total
number of rows passing out, and the sequence of rows from any given input
stream is preserved in the output, thepositions of the rows are not preserved.
○ For example, row number one from input stream one might not be row number
one in the output stream.
○ Union alsodoes not guarantee that the output is repeatable. Because of
these reasons, Union is considered an active transformation.
W
● hat is rank transformation?
○ rank transformation is used to selecttop or bottom rank of data.
A
○ It is used to select the largest or smallest numeric value in a port or a group.
○ Rank is anactive and connected transformation.
○ It can be used in cases like wanting 5 records of employees having the highest
salary or getting the top 5 salaried employees Department wise.