Informatica PowerCenter Developer Specialist common mistakes
Informatica PowerCenter Developer Specialist · Understand the traps and how to avoid them.
[Repository Objects] Keeps a mapping checked out for a whole sprint and tests only the local copy, then is surprised that a colleague's session still runs the previous logic and that the colleague cannot modify the mapping.
In a versioned repository, Save writes a private version and places a write lock on the object. Other users, the Workflow Manager and any downstream mapping only see the last version that was checked in, so all their work is built on stale metadata.
What to do instead
Check in at each functional milestone with a meaningful comment, use Repository Manager's version history and dependency views to see what others reference, and never leave objects checked out overnight. Use Undo Checkout to drop work you do not want.
[Source and Target Definitions] Designs the load to rely on the primary key or a declared foreign key relationship to reject duplicate or orphan rows, then finds duplicates in the table and no errors in the session log.
Key columns in PowerCenter source and target definitions are metadata. They drive SQL generation (for example the WHERE clause of a generated UPDATE) and join hints, but the Integration Service does not enforce them at load time.
What to do instead
Enforce uniqueness explicitly with an Update Strategy plus Lookup, or dedupe the source with Sorter and Aggregator, and create real constraints in the database DDL. Confirm which statement actually ran by reading the generated SQL in the session log.
[Mapping Design and Core Transformations] In an Expression transformation, writes one output port's expression as a reference to another output port (OUT_TOTAL referencing OUT_QTY) and expects the second value to be computed first.
Ports are evaluated top to bottom in the order they are listed. Variable ports can be referenced by ports below them, but output ports cannot be referenced by other ports of the same transformation, so the mapping fails validation or the expression quietly resolves to something else.
What to do instead
Chain the calculations through variable ports (v_ prefix) in dependency order and end with a single output port that references the last variable. Reorder ports rather than duplicating expressions across ports.
[Advanced Transformations] Uses an ordinary cached Lookup to decide INSERT versus UPDATE while the same mapping also updates the target, so a duplicate key arriving later in the same source batch is inserted instead of updated.
A static lookup cache is built once at session start from the lookup table and is not refreshed by rows the session itself writes. Changes made earlier in the run are invisible to later lookups, so the lookup returns 'not found'.
What to do instead
Use a dynamic lookup cache with Insert Else Update or Update Else Insert and route on NewLookupRow, or dedupe the source before the lookup. Reserve uncached lookups for very small reference tables.
[Mapplets and Reusable Logic] Builds a mapplet that ends in a Target definition so the audit or error write is 'reusable', then finds the target cannot be added or the mapplet will not validate.
A mapplet may contain source definitions, transformations and mapplet input and output ports only. A target definition is not permitted because the mapplet is a transformation reused inside mappings, not a session-level object.
What to do instead
Keep the write in the mapping and let the mapplet deliver ports to a downstream target. If reuse is the real goal, put the target in a separate mapping or reuse the port-level mapplet across mappings that each own their own targets.
[Workflows, Sessions, Tasks and Scheduling] Sets the session's Treat source rows as option to Update to fix a re-run, but the rows are still inserted or duplicates appear.
An Update Strategy transformation present in the mapping takes precedence over the session-level option. If it exists and its expression defaults to DD_INSERT, the session setting is ignored for those rows.
What to do instead
Inspect the mapping for Update Strategy transformations and their DD_INSERT, DD_UPDATE and DD_DELETE expressions. Use the session option only when no Update Strategy exists, and verify the statement issued from the generated SQL in the session log.
[Workflows, Sessions, Tasks and Scheduling] Wires two sessions straight off the Start task with unconditional links and expects the fact load to wait for the dimension load to finish.
Unconditional links carry no dependency on the predecessor's status. Both sessions are dispatched as soon as the parent task completes and run concurrently, so the fact load can read half-loaded dimensions.
What to do instead
Chain Start to session1 to session2 and put the condition on the second link, for example $session1.Status = SUCCEEDED, and enable Fail parent if this task fails wherever a downstream task must not run.
[Performance Tuning] Ticks Sorted Input on an Aggregator, or on a Joiner's sorted ports, because it is supposed to be faster, without ensuring incoming rows are genuinely ordered on the group-by or join keys.
Sorted input is a promise, not a check. The Integration Service trusts the flag and groups consecutive rows, so unsorted data yields wrong aggregates or a sorted-port error, and the log usually blames the wrong transformation.
What to do instead
Guarantee the order upstream: set Number of Sorted Ports on the Source Qualifier only when the database ORDER BY matches, otherwise add a Sorter, or clear the sorted-input option. Compare results against an unsorted run on a small dataset.
[Performance Tuning] Places a Filter transformation immediately after a relational Source Qualifier to 'reduce rows read from the database', then finds the source still returns every row and the session is slow.
Once rows leave the Source Qualifier they are already in the DTM. Filter, Expression and most other transformations process rows in the Integration Service; only SQL in the Source Qualifier, including the SQL override, is executed by the database.
What to do instead
Push the predicate into the Source Qualifier SQL Query override or its Source Filter property, or enable source-level pushdown optimization. Read the generated SQL in the session log to confirm the WHERE clause reached the database.
[Error Handling and Recovery] Enables Truncate target table on a session that must be restartable, then finds after a failure that recovery cannot use the earlier commit point and the load effectively starts over.
Recovery resumes from the last commit point, which presumes the rows already written are still in the target. A truncate at session start invalidates that state, so the session is re-run from the beginning and the target is emptied again, doubling extract work and losing partial progress.
What to do instead
Move the truncate to a separate pre-session command task or a delete step in its own mapping, keep a commit interval that gives recovery a recent restart point, and choose Recover rather than Restart after a failure. Test the failure path deliberately before production.