Top Three per Region, and a Type 2 Lookup With Three Answers
An online retailer loads a 6.5 million-row daily sales table into a mart. The requirement is the top three products by revenue for each of 42 regions. The mapping joins sales to product and region, aggregates revenue by region_id and product_id, and then uses a Rank transformation with Group By set to region_id and product_id, rank port revenue, and Number of Ranks = 3. The target receives about 240 rows per region. Separately, a connected Lookup on dim_product returns three rows for many products because the dimension is Type 2 with three versions; the lookup condition uses only product_id, and the lookup policy is 'Return first matching row'. The Aggregator has 'Sorted Input' enabled, but the upstream Joiner is not sorted, and the session log shows a 12 GB sorter spill. The session runs in four partitions. Volume is growing 20% per quarter.
1. Explain why Rank returns about 240 rows per region and give the corrected transformation settings, including sort placement.
Read the worked answer
Rank groups rows by the Group By ports and assigns ranks within each group. With Group By set to both region_id and product_id, every product is its own group, so every product gets rank 1 and passes the top-three filter. The output is essentially all products, about 240 per region. The Rank should group by region_id only, rank on revenue descending, and set Number of Ranks to 3. The input must have one row per region and product, which the Aggregator already produces.
The Aggregator's Group By should be region_id, product_id, and its Sorted Input option requires the input to arrive sorted by those ports in that order. The upstream Joiner does not guarantee order, so either add a Sorter on region_id, product_id before the Aggregator or disable Sorted Input and let the Aggregator build its own groups. If the source is relational and the join can be pushed down, add ORDER BY region_id, product_id to the Source Qualifier SQL and verify the plan; but do not assume a database order survives a Joiner transformation. Rank itself does not require sorted input.
If the session is partitioned, Rank must be partitioned by region_id or run in one partition so a region's top three is not computed separately in multiple partitions.
2. Fix the Type 2 product lookup. State the lookup condition, policy, and cache choice, and explain the test you would run.
Read the worked answer
The lookup condition product_id = in_product_id matches three historical versions, so 'Return first matching row' returns an arbitrary version, not the current one. Add the current-row predicate to the lookup condition or to a Lookup SQL Override, for example is_current = 'Y' or eff_end_date = TO_DATE('9999-12-31','YYYY-MM-DD'). The lookup should return the surrogate key and the attributes of the current version.
Use 'Report Error' as the lookup policy during testing so duplicate current rows fail loudly rather than silently picking one. Once the dimension is verified unique on product_id for current rows, 'Return first matching row' is acceptable for production, but it is a safety net, not the design. If the dimension has duplicate current rows, fix the dimension load first.
Because the dimension changes only weekly and is read-only in this mapping, use a persistent lookup cache with a scheduled rebuild, not a rebuild on every session run. A persistent cache reduces the repeated cache build to a weekly cost. Do not use a dynamic cache here unless this mapping also updates the dimension; dynamic cache adds overhead and changes the cache at runtime. Validate by comparing lookup returns for a sample of 100 products against the dimension's current rows.
3. The four partitions did not improve runtime. What would you check and what would you change?
Read the worked answer
First check the session log's per-partition row counts. If one partition processes most of the rows, the partition key is skewed. Hashing on region_id is a poor choice when one region dominates; hash on a high-cardinality key such as order_id or claim_id, or use round-robin if the transformation does not need co-location. Second, check the partition points around the Aggregator, Rank, and Lookup. Repartitioning before an Aggregator can force extra sorting; if Rank is partitioned by a key other than region_id, each partition computes its own top three and the result is wrong, not just slow.
Third, the 12 GB sorter spill and buffer-pool waits point to the sort, not the target. Eliminate the Sorter by pushing ORDER BY and the aggregation into the source SQL when possible, or disable Sorted Input and let the Aggregator group the data. Increase DTM buffer only after reducing the sort volume; a larger buffer delays the spill but does not fix it. Test one change at a time with the same input and compare per-partition throughput.