Basics
What is the Aggregator transformation in Informatica?
The Aggregator transformation performs aggregate calculations, such as averages and sums, on groups of rows (Informatica Aggregator Transformation Overview). Rows enter a mapping one at a time, and the Aggregator collapses them into results per group, such as a total per region. It is the mapping-level counterpart of an aggregate query in a database.
Why it is asked: it is the opening check that you know what the transformation is for.
Is the Aggregator transformation active or passive?
The Aggregator is an active transformation, according to the same guide (Informatica Aggregator Transformation Overview). The label fits the behaviour you can see in the worked example below: six rows go in and three group rows come out, so the row count changes. A common mistake is to call it passive, like the Expression transformation.
Why it is asked: the active or passive split is a standard classification question.
What is the difference between the Aggregator and Expression transformations?
The Aggregator performs calculations on groups, while the Expression transformation performs them on a row-by-row basis (Informatica Aggregator Transformation Overview). Use Expression to derive a value from one row, such as a price times a quantity. Use the Aggregator when the answer needs several rows together, such as the total of those values per customer.
Why it is asked: it tests whether you can choose the right transformation for a requirement.
How is the Aggregator different from SQL GROUP BY?
Both calculate aggregates over groups, but the Aggregator's transformation language lets you use conditional clauses in aggregate expressions to filter rows, which Informatica describes as more flexible than the Structured Query Language (SQL) (Informatica Aggregator Transformation Overview). The Aggregator also has its own cache and sorting behaviour, covered below. It is not simply GROUP BY renamed.
Why it is asked: interviewers want to hear that you know both tools, not that you treat them as identical.
How does the Aggregator turn six rows into three?
The Aggregator collects rows by group and calculates one result per group. In this illustrative example of six order rows across three regions, the SUM of amount is 180 for East, 120 for West and 250 for North, and the COUNT of rows is two for each.1
| Region | Rows in | SUM of amount | COUNT of rows | Average |
|---|---|---|---|---|
| East | 2 | 180 | 2 | 90 |
| West | 2 | 120 | 2 | 60 |
| North | 2 | 250 | 2 | 125 |
Why it is asked: a small hand calculation shows you understand grouping, not only the name.
What does a conditional aggregate do?
A conditional aggregate applies a condition inside the aggregate expression so that only matching rows count toward the result; this is the conditional-clause feature the guide describes (Informatica Aggregator Transformation Overview). In the illustrative data, one East order of 60 is refunded, so a total over paid rows only is 120 for East instead of 180.1 The exact expression syntax is in the guide.
Why it is asked: it checks that you can get two different totals from one pass over the data.
Groups and functions
What is a Group By port?
A Group By port is an Aggregator port you mark as a group-by port; the marked ports define the groups, and the transformation returns one row per distinct combination of their values. In the worked example, region is the group-by port, so three regions give three output rows. Marking several ports groups by the combination, such as region and year.
Why it is asked: grouping is the core configuration, and an interviewer expects you to say how it is set.
What happens if no Group By port is selected?
With no group-by port, the Aggregator treats all input rows as one group and returns a single row for the whole input. In the worked example that would be one row holding the grand total of 550 instead of three regional rows.1 This is the way to get a whole-table total, such as a count of all rows.
Why it is asked: it tests whether you understand that grouping is optional but changes the output shape.
What value does a non-aggregated, non-group-by port return?
A port that is neither a group-by port nor wrapped in an aggregate function typically returns the last row received for that group, so its value depends on row order. That makes it unreliable for anything but a value that is the same on every row of the group. Make the intent explicit with an aggregate function such as LAST, FIRST or MAX.
Why it is asked: it is a classic trick question about silent, order-dependent results.
Which aggregate functions can you use, and can you nest them?
The Aggregator supports AVG, COUNT, FIRST, LAST, MAX, MEDIAN, MIN, PERCENTILE, STDDEV, SUM and VARIANCE. You can nest one aggregate function inside another, such as taking the MAX of a SUM, but only one level deep. Nested aggregates have a consequence for Sorted Input, covered under tuning.
Why it is asked: it checks that you know what the transformation can compute without extra logic.
Knowing it is half the job. Saying it is the other half.
Interview Clozo is an AI interview copilot for real interview rounds: it hears the question and puts a strong answer on your screen, and you say it in your own words. See the features or how it works.
Start Free Trial →Caches and properties
Why does the Aggregator need a cache?
The aggregate cache is the memory in which the Integration Service stores data until it completes the aggregate calculations (Informatica Aggregator Caches page, PowerCenter 10.4.0). The service uses cache memory to process Aggregator transformations with unsorted input. Until the last row of a group arrives, the total is not final, so the data has to be held.
Why it is asked: cache behaviour is where performance questions begin.
What do the index cache and the data cache each store?
The Aggregator uses two caches. The index cache stores the group values as configured in the group-by ports, and the data cache stores the calculations and row data based on those ports (Informatica Aggregator Caches page, PowerCenter 10.4.0). Many distinct groups therefore grow the index cache, and many or wide calculated values grow the data cache.
Why it is asked: it separates people who know how cache sizing works from those who only know that a cache exists.
What are the default cache sizes for the Aggregator?
In the PowerCenter 10.5 Transformation Guide, the default data cache size is 2,000,000 bytes and the default index cache size is 1,000,000 bytes (Informatica Aggregator properties page). You can enter a number, use a value from a parameter file, or choose the Auto setting so the service decides. Defaults can differ between versions, so confirm yours.
Why it is asked: it separates people who have configured the transformation from those who have only read about it.
What is the cache directory for?
The cache directory is the local directory where the Integration Service creates the index and data cache files, and by default it uses the directory set for the process variable $PMCacheDir in the Workflow Manager (Informatica Aggregator properties page). It must exist and have enough disk space. With incremental aggregation on, the service backs up the files each run, so space for two sets is needed.
Why it is asked: disk-space failures are a real operational issue.
What is the Transformation Scope property?
Transformation Scope tells the Integration Service how to apply the transformation logic to incoming data. Transaction applies it to all rows in a transaction, for when a row depends only on rows in the same transaction. All Input applies it to all incoming data and drops incoming transaction boundaries, for when a row depends on all rows in the source (Informatica Aggregator properties page).
Why it is asked: it tests whether you read the property list rather than only the headline feature.
What does the Tracing Level property do?
The Tracing Level sets the amount of detail displayed in the session log for this transformation (Informatica Aggregator properties page). The levels are Normal, Terse, Verbose Initialization and Verbose Data. A verbose level helps when you debug unexpected group totals; a lower level keeps the log small once the mapping is trusted.
Why it is asked: it is a quick check of day-to-day debugging habits.
Tuning
What does the Sorted Input option do?
Sorted Input is an Aggregator option that tells the service the incoming data is already sorted by group. When you use it, the service assumes all data is sorted by group, which reduces the data cached during the mapping and improves performance. This is the wording of the Data Integration Service tuning guide (Informatica Performance Tuning Guide, Data Integration Service version, 10.5).
Why it is asked: it is the best-known Aggregator tuning lever.
Does Sorted Input sort the data for you?
No. The option only declares that the data is presorted by group, and the properties page says to select it only if the mapping passes sorted data to the Aggregator (Informatica Aggregator properties page). The tuning guide points to a Sorter transformation as a way to supply sorted data. Because the service trusts the declaration, data that is not correctly presorted can give unexpected results, according to the same properties page, so sort upstream.
Why it is asked: it catches the common belief that the option performs the sort.
When must you not use Sorted Input?
Do not select Sorted Input when an aggregate expression nests aggregate functions, or when the session uses incremental aggregation. Check these two conditions before ticking the option to speed up a mapping.
Why it is asked: it tests whether you know the limits of a tuning option, not only its benefit.
Where should a Filter transformation go relative to the Aggregator?
Place the Filter before the Aggregator. The tuning guide says a Filter placed ahead of the Aggregator reduces unnecessary aggregation (Informatica Performance Tuning Guide, Data Integration Service version, 10.5). Rows removed early are never grouped, calculated or held in the cache, so the Aggregator does less work.
Text description of the diagram
- A Filter placed before the Aggregator reduces unnecessary aggregation.
- With Sorted Input the service assumes all data is sorted by group.
Why it is asked: it shows you think about the whole mapping, not one transformation.
What is incremental aggregation?
Incremental aggregation is a session option you can enable after you create a session that includes an Aggregator transformation; it uses historical cache data to aggregate new source data incrementally (Informatica Aggregator Transformation Overview). The idea is to avoid reprocessing all old rows each run. The cost, from the properties page, is that the service keeps backup cache files.
Why it is asked: it tests whether you can handle growing data sets.
What are the tuning topics for the Aggregator in PowerCenter?
The PowerCenter 10.5 Performance Tuning Guide lists three topics under optimizing Aggregator transformations: grouping by simple columns, using sorted input, and using incremental aggregation (Informatica Optimizing Aggregator Transformations). For grouping, the advice is to prefer simple group-by columns, such as numeric ones, over strings, dates or complex expressions.
Why it is asked: an interviewer may ask for your tuning checklist.
Follow-up questions
What are the common Aggregator transformation interview questions?
The twenty-two above cover the recurring ones: what the transformation does, active versus passive, Group By ports, aggregate functions, Aggregator versus Expression, caches, Sorted Input, filter placement and incremental aggregation. Be ready to explain a trade-off, such as less cache memory with sorted input against the cost of sorting upstream, not only the definition.
Is the same advice true in every Informatica product?
Not necessarily. The pages used here come from different documentation sets: the Cloud Data Integration for PowerCenter guide, PowerCenter 10.4.0 and 10.5, and a Data Integration Service tuning guide. Names and defaults can differ, so say which product and version you are describing.
How do you prepare for a data-integration interview round?
Practise explaining one transformation end to end with a small table of rows, as in the worked example. For general problem-solving practice, work through the coding interview guide. If your stack also includes application code, other question lists are Java interview questions, JavaScript interview questions and Spring Boot interview questions. Broader roles are covered in full stack developer questions and solution architect questions.
- The Aggregator is an active transformation that calculates aggregates per group, unlike Expression, which works row by row.
- Group By ports define the groups; with none, all rows form one group.
- With unsorted input it holds data in the aggregate cache until the calculations finish.
- Sorted Input assumes the data is already sorted by group; it does not sort for you.
- Filter before the Aggregator so fewer rows are aggregated.
- Incremental aggregation reuses historical cache data to aggregate only new source data.
Sources
- Aggregator Transformation Overview (Transformation Guide, Cloud Data Integration for PowerCenter) — Informatica documentation, 26 Oct 2026
- Aggregator Caches (Advanced Workflow Guide, PowerCenter 10.4.0) — Informatica documentation, 26 Oct 2026
- Aggregator Transformation Optimization (Performance Tuning Guide, 10.5) — Informatica documentation, 26 Oct 2026
- Optimizing Aggregator Transformations (Performance Tuning Guide, PowerCenter 10.5) — Informatica documentation, 26 Oct 2026
- Configuring Aggregator Transformation Properties (Transformation Guide, PowerCenter 10.5) — Informatica documentation, 26 Oct 2026
