Row-Level Rejects in a 500,000-Record JSON Feed
A mapping loads a JSON array of 500,000 loyalty events per day from a REST API into an Oracle table. The API returns records with event_id, member_id, event_date, amount, and phone. Roughly 2% fail: event_date arrives as '0000-00-00' or '31/02/2025', amount exceeds NUMBER(10,2), phone exceeds 20 characters, or member_id is null. The mapping currently converts types in an Expression and the mapping task stops on the first conversion error; there is no reject table. Operations rerun the task after correcting the source, but reruns insert duplicate events because the target has no unique constraint. Downstream finance reconciliation is late, and support cannot identify which records failed.
1. Design a row-level error-handling strategy that keeps good records flowing and makes failures diagnosable.
Read the worked answer
Move validation before conversion and route bad rows instead of aborting. After JSON parsing, add an Expression that tests each field without failing: event_date is a valid date and not '0000-00-00', amount fits NUMBER(10,2), phone length is at most 20, and member_id is not null. Then use a Router with GOOD and REJECT groups.
The REJECT group writes to a reject table containing event_id, the raw JSON or key fields, error_code, error_field, mapping name, task run ID, and reject timestamp. For conversion errors that cannot be tested beforehand, configure the task's error handling to continue on row errors up to a threshold and write a reject file, but do not make that the only mechanism because reject files are harder to query and may lack business context. Fail the task only for a structural API error or when the reject rate exceeds an agreed threshold, for example 5%. This keeps about 98% of records flowing and gives support a queryable list of the 10,000 daily failures.
2. How would you make reruns idempotent without blocking the daily load?
Read the worked answer
Use a natural or composite key and an upsert. If event_id is unique in the source, add a unique constraint on event_id and load with MERGE or Update Strategy. If event_id is only unique within a day, use a composite key such as event_date plus event_id. Deduplicate the API payload before the target so a repeated event in one response does not create two rows.
An alternative is to write to a staging table with a batch_id, delete target rows for that batch_id, and insert; that is simple but makes the batch atomic and can be slower. MERGE is preferable because a rerun changes only matching rows and leaves other days untouched. Keep the reject table append-only with event_id, error_code, and run_id so that repeat failures remain visible rather than overwriting history. Rerun the whole day or only the failed partition, but verify the same counts and that the duplicate query returns zero.
3. What metrics should operations monitor after this change?
Read the worked answer
Monitor reject rate overall and by error_code, because a jump in one code points to a source change rather than random bad records. Track source record count versus accepted plus rejected; if the totals do not reconcile, parsing or task failure is hiding rows. Monitor API HTTP errors, response time, and record count versus the expected 500,000. Check target duplicate count for event_id or the composite key, and freshness against the finance cutoff.
Set alerts: reject rate above 3% warning and 5% page; zero records received during the scheduled window; duplicate count greater than zero; and source-to-target reconciliation variance above an agreed amount. Include a daily finance reconciliation by event date and amount sum. Store the reject details long enough for support to correct and replay records, and report the top failing member or source system so the upstream team can fix data quality.