Data Mapping with Nested Arrays and Aggregation
A scenario retrieves a list of orders from an e-commerce platform. Each order contains an array of line items, and each line item has a product ID, quantity, and price. The goal is to create a single CSV file with one row per line item, including order ID, product name (looked up from a separate product list), quantity, and total price (quantity * price). The product list is retrieved once per scenario run and contains about 10,000 products. The current scenario uses an iterator on orders, then an iterator on line items, and a 'Get record' module to fetch each product individually. The scenario is slow and sometimes times out after 40 minutes.
1. What is the main performance bottleneck, and how would you redesign the scenario to improve speed?
Read the worked answer
The bottleneck is the 'Get record' module inside nested iterators, which makes an API call for each line item. With many orders and line items, this results in thousands of API calls, causing slowness and potential rate limiting. Redesign:
- Retrieve the entire product list once at the beginning (using a 'Search records' or 'List' module) and store it in a variable or data structure (e.g., an array of objects).
- Use a 'Set variable' module to hold the product list, or better, use a 'Data store' if the list is large, but 10,000 products might fit in memory.
- Then, iterate over orders and line items, and for each line item, use a 'Map' or 'Get' function to find the product name from the stored list by matching product ID. This avoids individual API calls.
- Alternatively, use the 'Array aggregator' to collect all line items, then use a 'Iterator' and 'Get variable' or 'Map' to enrich, then aggregate into CSV.
- Use the 'Create CSV' module with the aggregated data. This reduces API calls to just a few (fetch orders, fetch products) and processes in memory.
2. How would you handle the case where a product ID is not found in the product list?
Read the worked answer
If a product ID is missing, the mapping function should return a default value (e.g., 'Unknown') or skip the line item. Using the 'map' function with a fallback: {{map(productList; 'name'; 'id'; lineItem.productID) || 'Unknown'}}. Alternatively, use an 'ifempty' function. Also, consider logging missing IDs for review. To avoid errors, ensure the mapping does not fail; use 'if' or 'ifempty' to handle nulls. For CSV output, it's better to include a placeholder than to break the scenario.