The Flat File That Rejected 41,000 Rows Before the Mapping Saw Them
An insurance company loads a daily vendor extract into Oracle stg_claim. The file is pipe-delimited, 1.2 million rows, 14 columns, and arrives at 04:15. The source definition declares claim_amount as decimal(12,2) and service_date as date. In production the session rejects 41,000 rows to badfile_claim. The vendor sends credits as (1,234.56) and some amounts as $1,234.56; dates arrive as MM/DD/YYYY while the source definition expects YYYY-MM-DD. The mapping uses a Source Qualifier with the filter claim_amount > 0 and an Expression that calls TO_DATE(service_date,'YYYY-MM-DD'). Downstream, an Aggregator sums claim_amount by claim_type, and a Lookup checks stg_claim for duplicate claim IDs. The target has a load_date column defaulting to SYSDATE. The business wants rejects visible in a table, not only in a reject file, and wants the daily load to fail if more than 0.5% of rows reject. The file volume is stable at about 1.2 million rows per day.
1. Why are the 41,000 rows rejected before the Expression transformation can correct them? Redesign the source definition and the conversion logic.
Read the worked answer
For a flat file source, the PowerCenter reader performs data type conversion using the source definition before rows enter the mapping. If claim_amount is declared decimal(12,2), the reader cannot parse (1,234.56) or $1,234.56; it writes the row to the reject file and the Expression never sees it. The same applies to service_date declared as date when the file contains MM/DD/YYYY; the reader rejects on the format mismatch.
Change the source definition columns to string for claim_amount and service_date so every row reaches the mapping. In an Expression, first normalize: remove $ and commas, detect a leading ( and a trailing ) to apply a negative sign, then call TO_DECIMAL(...,12,2). Convert the date with TO_DATE(service_date,'MM/DD/YYYY'). Add an is_valid flag using IS_NUMBER or IS_DATE after normalization, or use ERROR() only if you deliberately want a row reject. Keep the original string values in the reject path for audit.
Do not put the numeric filter in the Source Qualifier against the raw string: claim_amount > 0 would compare text, and (1,234.56) would sort and compare unpredictably. Filter after conversion.
2. How would you route rejects to a table and enforce the 0.5% threshold in the workflow?
Read the worked answer
Use a Router after the conversion Expression with two groups: VALID when the normalized amount and date parse and the claim ID is non-null, and REJECT otherwise. Connect VALID to the Aggregator and the Lookup path. Connect REJECT to a relational reject table with columns for the raw row, claim ID, file name, batch ID, error reason, and load timestamp. This makes rejects queryable instead of leaving them only in badfile_claim.
For the threshold, do not rely on the session Stop on errors property; it counts errors, not a percentage, and it would stop mid-load. Load the rejects first, then run a small control mapping or Command task that computes reject_count / total_count. A Decision task after the load checks the result: if it exceeds 0.005, send an alert and route to a Control task that aborts the workflow or marks the batch failed. The valid rows can still be loaded for investigation, but the batch must not be reported as complete.
Store batch_id on both the staging and reject rows so the threshold query and any cleanup are scoped to one file.
3. What should happen to `load_date`, and how would you make the load restartable?
Read the worked answer
load_date should not be left to the target default alone. If the target default is SYSDATE, a rerun on a later day creates a different load_date for the same business file, which breaks partition pruning and reconciliation. Set load_date explicitly in the mapping from a workflow parameter such as the file's business date or the batch date, and keep it consistent across staging, reject, and audit rows.
Make the load restartable by scoping every write to batch_id. Before the session, run a pre-session delete for that batch, or use an Update Strategy with a lookup on the natural key if updates are allowed. For a daily full file, delete-then-insert for the batch is simpler and idempotent. On failure, rerun the whole batch rather than resuming a partially committed load; with a 10,000-row commit interval, a partial load is already committed. Ensure the reject table is also batch-scoped. Finally, validate the target count against the source valid count before the workflow sends success.