The Event Wait That Fired on a Temporary File
At 01:30 each day the scheduler starts workflow wf_vendor_load. The first task is an Event Wait on vendor_orders_*.csv with a 90-minute timeout. At 02:10 the vendor writes vendor_orders_20240301.csv.tmp, and at 02:26 renames it to vendor_orders_20240301.csv after the full 2.4 million rows are written. The Event Wait glob also matches the .tmp file, so it fires at 02:11. The next session reads the partial file and loads 1.1 million rows. The session has 'Stop on errors' = 0 and 'Treat source rows as' = Insert. It completes with status Succeeded. A downstream finance report is short by 1.3 million rows. The workflow has a Decision task after the session that checks whether the session succeeded and sends a success email. There is no control file. The target table is partitioned by load_date; the session commit interval is 10,000 and target load type is Normal.
1. Redesign the workflow to prevent loading a partially written file and to fail visibly. Specify task types and link conditions.
Read the worked answer
The Event Wait is watching a pattern that matches the temporary file, so it signals readiness too early. Change the readiness contract: the vendor should write a separate control file such as vendor_orders_20240301.csv.done only after the rename, or the Event Wait should watch the exact final filename with a stable-size check. If the vendor cannot change, add a Timer and a Command task that verifies the file size is unchanged across two checks before allowing the session to start.
Structure the workflow as: Start to Event Wait on the control file with a 90-minute timeout, then a Decision on file_ready, then the Session, then post-session validation, then a Decision on counts_match, then a success Email. Any failure path goes to an Email and a Control task that aborts the workflow. Set the Event Wait timeout to route to the failure path, not to continue. The Decision task must use an explicit link condition such as $file_ready = 'Y' on the success link and $file_ready = 'N' on the failure link.
Do not rely on session status alone. Add a post-session Command task or a validation mapping that compares the source record count or control-file count to the target count for the batch.
2. The 1.1 million rows are already in the target. How do you recover without duplicating data, and which session properties matter?
Read the worked answer
Because the session reported success, Resume from last checkpoint will not help; it only applies to a failed session with a recovery strategy. Restarting the session would insert another 1.1 million rows because the load is Insert and the commit interval of 10,000 has already committed data. Recover by deleting the target rows for this batch or load_date and rerunning the complete file. Use a batch ID or the business date, not SYSDATE, so the delete is precise. If the target is partitioned by load_date, delete only that partition.
For the rerun, make the session idempotent: a pre-session SQL delete for the batch, or an Update Strategy with a lookup on the natural key if the target must support updates. Set Treat source rows as deliberately; Insert is fine only when the batch is clean. Keep the commit interval, but understand that it defines the recovery granularity. If you later switch to failed-session recovery, choose Resume from last checkpoint only for normal loads that can tolerate reprocessing from the last commit; bulk loads and truncate options usually force a full reload.
After the rerun, reconcile source, target, and reject counts before releasing the finance report.
3. What monitoring change would ensure a Succeeded session cannot silently produce a short report?
Read the worked answer
Add a data-completeness gate after the session. The gate can be a Command task that runs a line count on the final file and compares it to a control-file count, or a validation mapping that counts source rows, target rows, and distinct business keys for the batch. Store the expected count in a workflow variable or parameter file. A Decision task then routes to success only when the counts match within the agreed tolerance; otherwise it sends an alert and aborts.
Also capture session statistics. PowerCenter exposes row counts at the session level; persist them to an audit table from a post-session mapping or command. A session can succeed with zero or partial rows if the source ends early, so status alone is not a completeness check. Add a check that the file's last line is a trailer record if the vendor provides one.
Finally, change the success email to include the batch ID, source count, target count, reject count, and duration. An operator seeing 1.1 million rows instead of 2.4 million would have caught the issue immediately.