Nightly Fact Load Overruns a Four-Hour Window
Northwind Logistics runs a PowerCenter 10.5.6 repository with 412 mappings feeding a Snowflake warehouse. The nightly window is 22:00–02:00, but the ORDER_DETAIL fact load has overrun to 04:30 on three nights a week since a source volume increase. The session processes 180 million Oracle rows, performs a lookup against CUSTOMER_DIM (22 million rows, no cache configured), runs an aggregator with unsorted input, and calls a stored procedure that issues row-by-row statements. Session logs show 78% of elapsed time inside the aggregator and lookup, TEMP tablespace growing to 90 GB, and a single partition processing 61% of rows. The team must cut runtime to under three hours without buying more PowerCenter cores, and leadership has already licensed IDMC Cloud Data Integration for a 2025 migration of all 412 mappings.
1. Where do you start diagnosing, and what evidence would confirm each hypothesis?
Read the worked answer
Separate the two bottlenecks with measured evidence, not log impressions.
Aggregator. Confirm the session's sort key matches the group-by port order; unsorted input forces a sort of 180M rows, and 90 GB of TEMP growth is consistent with spill rather than CPU saturation. Enable sorted input, raise the work area in a test run, and compare spill volume.
Lookup. An uncached 22M-row lookup means one query per row unless an override or join is used. Check lookup statistics (rows returned per call), confirm the lookup condition uses a unique indexed key, and test with a persistent cache sized to the dimension.
Partition skew. 61% on one partition indicates a hash key with low cardinality or a dominant value such as NULL or a default region. Profile the partition key's distinct values and frequency.
Stored procedure. Row-by-row calls dominate latency. Capture a per-call timing sample and count invocations.
Then re-run a single-partition test after each change so each gain is attributable. Without that isolation you will attribute the aggregator's fix to the lookup and ship the wrong design.
2. Should the ORDER_DETAIL mapping be pushed down to Snowflake or re-tuned in place? Justify with trade-offs.
Read the worked answer
Push down the set-based portion, but not the whole mapping as-is.
The filters, the CUSTOMER_DIM join, and the aggregation are relational operations Snowflake handles well, and full pushdown eliminates moving 180M rows into the DI service. The stored procedure with row-by-row calls cannot push down and would either fail or generate unusable SQL, so split the mapping: pushdown for the join/aggregate into a staging table, then a separate mapping for the procedural post-processing.
Trade-offs to state explicitly: cost shifts from PowerCenter core hours to Snowflake credits, so use a dedicated warehouse sized for the window with auto-suspend rather than resizing the shared one; generated SQL is hard to review, so keep a documented, version-controlled SQL override; correctness must be proven with a full row-count and hash-total reconciliation against the current output before cutover.
Avoid partial pushdown that leaves the aggregator in PowerCenter; that keeps the spill and adds a hop. Measure both options on one partition first - the decision should rest on elapsed time plus cost per run, not on preference.
3. How would you sequence the migration of 412 mappings to IDMC Cloud Data Integration?
Read the worked answer
Classify before you convert. Inventory every mapping by source and target type, transformation set, and unsupported constructs (stored procedures, Java transformations, non-relational sources, sequence generators). That inventory drives the waves, not the mapping count.
Wave 1: straight-through mappings with filter/expression/lookup only - likely 50–60% of the estate. Convert with the automated conversion utility, reconcile, and cut over. This produces early wins and surfaces platform gaps.
Wave 2: mappings needing re-architecture, such as pushdown candidates and those with procedural logic. Redesign rather than lift-and-shift.
Wave 3: the genuinely complex remainder, scheduled last so lessons from waves 1–2 inform the design.
Run in parallel per subject area for at least two full cycles, reconciling with row counts and hash totals per target table, and freeze source schema changes during cutover. Retire PowerCenter mappings only after the parallel period closes; running both indefinitely doubles the maintenance cost and hides defects.