Exception Handling in a High-Volume Data Migration
A Blue Prism process is migrating 10,000 customer records from a legacy system to a new CRM. The process reads from an Excel file, validates data, and enters into the CRM via a web interface. During a pilot run of 100 records, 15 failed due to various reasons: 5 had missing mandatory fields, 3 had invalid date formats, 4 caused CRM timeouts, and 3 had duplicate records in CRM. The process currently marks all failures as 'Exception' and stops. The business wants to maximize successful migrations and minimize manual intervention. They require a detailed report of failures with reasons for manual follow-up. The process must also be able to resume from the last successful record without reprocessing.
1. Categorize the failures and propose an exception handling strategy for each category.
Read the worked answer
Categorize failures: (1) Missing mandatory fields: These are business exceptions; the record cannot be processed without data. Strategy: Mark as 'Business Exception' with reason 'Missing mandatory field', log to a separate queue for manual correction, and continue. (2) Invalid date formats: These are also business exceptions; attempt to auto-correct if possible (e.g., convert to standard format), else mark as business exception. (3) CRM timeouts: These are system exceptions; implement retry with exponential backoff (e.g., retry 3 times with increasing delays). If still failing, mark as system exception and log for IT. (4) Duplicate records: Check if duplicate exists before inserting; if so, mark as 'Duplicate' and skip. Use a separate queue for exceptions with categories. For all, ensure the process continues to next record.
2. How would you design the process to resume from the last successful record and generate the failure report?
Read the worked answer
Use a work queue to manage records. Load all records into the queue with a unique key (e.g., customer ID). The process marks each item as complete or exception. To resume, the process can simply continue from the queue; Blue Prism queues automatically track status. For reporting, at the end of the process, query the queue for items with status 'Exception' and generate a CSV report with columns: Customer ID, Failure Reason, Category, Timestamp. Alternatively, use a separate 'Exception' queue for failed items, and generate the report from that queue. Ensure the process does not reprocess completed items by filtering on status. This allows seamless resumption and detailed reporting.