Articles

Informatica PowerCenter Interview Questions: Dynamic Lookup Caches and Update Strategy Traces

Practice PowerCenter dynamic lookup cache questions with per-row state, NewLookupRow 0/1/2, target routing, and session options in an ordered trace.

A PowerCenter dynamic-lookup interview answer should trace each input row against the cache state left by earlier rows, then separately explain how the row is routed and what the target writer is told to do. NewLookupRow=1 means a cache insert; 2 means a cache update; 0 means no cache change under the configured comparison. None of those values is by itself proof that the target database committed the same operation. The downstream Update Strategy and session target options complete the story.

The six-row trace below is an original editorial fixture, not a transcript of a candidate interview or a claim about an employer’s process. Official Informatica facts establish dynamic-cache behavior, routing, and data-driven target operations. Editorial inference supplies the keys, values, sequencing, and recovery checks.

What problem does a dynamic lookup solve?

A static lookup cache reflects its initial contents during a session. If a new business key appears twice in the source, a static-cache design can treat both occurrences as absent unless some other control intervenes. A dynamic lookup cache can incorporate an inserted or changed row while the session runs, so later source rows are compared with that updated state. Informatica’s PowerCenter dynamic-cache example describes a customer target whose lookup cache is built from the target and updated as rows arrive.

The benefit is not magic database upsert semantics. The Lookup transformation compares a key and selected data ports in a cache. The mapping must route new and changed rows to appropriate target paths, and Update Strategy transformations mark operations for the writer. A session may have target options that change or reject those operations. Cache and target are distinct states. An answer that stops at “dynamic cache updates automatically” leaves out the failure boundary an interviewer often cares about.

Before drawing arrows, name the business key, lookup source, compared attributes, target key, duplicate-source ordering rule, and whether the cache starts from a current target snapshot. For this fixture, the business key is customer_code, and the compared value is tier. We assume one target row per code, a deterministic source order, and no null or type-conversion ambiguity. These assumptions are part of the exercise, not universal PowerCenter defaults.

Configure the row path before tracing values

For the main trace, assume incoming rows are flagged as insert before the Lookup, and the dynamic Lookup has Insert Else Update enabled. Under Informatica’s documented behavior for this mode, a new key is added to the cache, a matching key with different compared data updates the cache, and a matching identical row causes no cache change. The Lookup’s NewLookupRow output reports the result. An upstream Update Strategy can instead flag rows as update; then the relevant Update Else Insert setting changes the outcome for an absent key. Do not transfer one mode’s table to the other without naming the input row type.

Downstream, a Router or Filter branches on NewLookupRow: 1 to an insert path, 2 to an update path, and 0 to a no-change path that this exercise deliberately does not write. The insert path’s Update Strategy flags DD_INSERT; the update path flags DD_UPDATE. These are operation constants, not the same number scale as NewLookupRow. In Informatica’s transformation-language reference, DD_INSERT is numeric 0 and DD_UPDATE is numeric 1. Therefore NewLookupRow=0 means no cache change, while DD_INSERT=0 means insert this routed row. Confusing those two zeroes can send the wrong operation to the target.

The session must honor data-driven row handling, and the target options must permit the relevant inserts and updates. Informatica’s PowerCenter Workflow Basics Guide says the writer’s behavior depends on both the row flag and enabled target options. Update as Update, Update as Insert, and Update else Insert at the target are distinct choices. The latter is not the same setting as Insert Else Update on the dynamic Lookup. Say which layer you mean.

A six-row trace with repeated keys

Assume the target and initial dynamic cache both contain K2 → Standard. The ordered source stream is shown below. We use a shorthand “cache after” column for the compared tier values; a real cache may have additional ports and generated keys. Under the stated mode and comparison rule, the trace is:

Source order	Source key and tier	Cache immediately before	Cache result	`NewLookupRow`	Downstream target intent
1	`K1 → Gold`	`K1` absent	Insert `K1 → Gold`	1	`DD_INSERT` for K1.
2	`K1 → Gold`	`K1 → Gold`	No change	0	Skip this duplicate value.
3	`K2 → Premium`	`K2 → Standard`	Update `K2 → Premium`	2	`DD_UPDATE` for K2.
4	`K1 → Silver`	`K1 → Gold`	Update `K1 → Silver`	2	`DD_UPDATE` for K1.
5	`K3 → Bronze`	`K3` absent	Insert `K3 → Bronze`	1	`DD_INSERT` for K3.
6	`K2 → Premium`	`K2 → Premium`	No change	0	Skip this duplicate value.

The cache-after-row-1 state is needed to solve row 2. If you repeatedly compare each row with the initial target snapshot, you will misclassify row 2 as a second insert and row 4 as another new key. The state-after-row-3 is needed to recognize row 6 as unchanged. In an interview, draw a small mutable map and update it after each row; do not fill NewLookupRow from the entire batch at once.

Under a successful ordered target load with the stated routing and keys, the intended final target is K1 → Silver, K2 → Premium, K3 → Bronze: two inserts, two updates, two skipped rows. That is a planned target state, not proof of an executed PowerCenter run. The insert of K1 precedes its update in this exercise; a different pipeline order, target load order group, or concurrency setting may change the practical outcome. If ordering is not guaranteed, add a source version or deterministic winner and test how the mapping enforces it.

Change one setting and the answer changes

Disable Insert Else Update while keeping input rows flagged as insert. Informatica documents that a new key can still be inserted into the dynamic cache, but an existing key under the insert-only behavior is not updated by the incoming row. In the fixture, row 3 would no longer advance cached K2 from Standard to Premium, and row 4 would not advance cached K1 from Gold to Silver. If the downstream Router depends on NewLookupRow, those rows can take the no-change branch. The apparently harmless checkbox determines whether a repeated business key with new attributes becomes an update candidate.

Alternatively, flag an incoming row as update and examine Update Else Insert. Informatica’s mode table says that with the option cleared, an absent key on an update row is left unchanged in the cache (NewLookupRow=0); with it selected, the absent key is inserted (NewLookupRow=1). Existing different data can update (2) in both cases. This is why “dynamic lookup” alone is not enough information to predict a row’s result. Ask for input row type and the relevant cache mode.

Compared values can also change the answer. If a non-key port is excluded from comparison, changing only that port may not produce the expected cache-update signal. If null handling uses Ignore Null, an apparent difference may be suppressed; Informatica’s table documents a no-change result for particular all-null cases. A correct production mapping defines which fields are material and how nulls mean “unknown,” “clear this value,” or “leave it alone.” The fixture deliberately avoids those cases so the 0/1/2 trace is unambiguous.

Another trap is equating a persistent cache with a dynamic cache. “Persistent” concerns retaining cache files between sessions; “dynamic” concerns modifying cache contents during processing. A cache can have configuration choices along both dimensions, with rebuild and synchronization consequences. When the interviewer says “the cache was stale,” ask which cache type, which source snapshot, and whether the session was configured to rebuild or synchronize. Do not assume the target and a carried-over cache began from the same state.

Cache state is not a commit receipt

Suppose the mapping updates K2 in its dynamic cache, routes the row to an update path, and the target write fails on a constraint or is rolled back. NewLookupRow=2 tells you what the Lookup did to its cache, not that K2 in the database is now Premium. If a later K2 Premium row arrives while the cache says Premium, it may be classified as unchanged and skipped even though the target still has Standard. Informatica’s documentation discusses cache/target synchronization because this separation matters.

The safe operational response is to identify the actual failed boundary, target row state, committed transaction range, cache build/rebuild policy, and replay key. Do not claim a particular rollback behavior for every session configuration. If the target and cache disagree, a controlled rebuild from the authoritative target or a replay that explicitly reconciles keys may be needed. Before restarting, record whether K1, K2, and K3 were actually written, not merely how many cache events occurred.

Count at three levels: source input (six rows), dynamic-cache events (two inserts, two updates, two no-changes under this exercise), and target committed operations (unknown until verified, intended four). A session log’s transformation statistics can help, but key-level comparison with the target is stronger evidence. If the session reports four rows processed by Update Strategy but only three target rows changed, inspect rejected rows, disabled target options, update keys, and transaction status. The cache trace is a diagnostic clue, not the final reconciliation.

How to explain the mapping in an interview

Start with a one-sentence contract: “The target has one current tier per customer code; source order is authoritative, and only changes are written.” Draw the initial cache and the six source rows. State input flags and Insert Else Update. Work row 1, then immediately mutate the cache; repeat in order. After that, route 1/2/0 to insert/update/skip, name DD_INSERT and DD_UPDATE, and check Data Driven plus target insert/update options. End by distinguishing planned target state from observed committed state.

If asked for a more robust design, add a stable source sequence or event version. Without one, two different updates to K1 could arrive in a nondeterministic order, yielding a nondeterministic final tier. Decide whether the target accepts only increasing versions or whether a full batch is deduplicated before the lookup. This is an editorial design recommendation, not a feature of the dynamic cache itself. The cache can remember the previous processed row; it does not determine which business event should win.

A minimal test fixture should include: one absent key; the same absent key repeated unchanged; an existing key updated; the newly inserted key updated; an existing key repeated unchanged; and a deliberately failed target write. The first five establish the 0/1/2 mapping. The failure establishes whether monitoring and recovery understand cache/target separation. Use row-level expected outcomes, not only a final row count.

Fast answers to PowerCenter follow-ups

What does NewLookupRow=0 mean? No dynamic-cache insert or update for that row under the configured comparison and input mode. It is not DD_INSERT, despite the shared numeric zero.

Can a dynamic cache replace Update Strategy? No. The cache decides a lookup/cache result; downstream mapping and session settings determine target actions.

Why does source order matter? Each row can change the cache that the next row sees. Repeated business keys require a deterministic winner rule.

What happens if an update-flagged key is absent? It depends on Update Else Insert. With the option cleared in Informatica’s documented table, the cache can remain unchanged; with it selected, it can insert.

How do you verify success? Compare target keys and values after commit against the intended final state, inspect rejects and session options, and check cache initialization/rebuild state before replay.