How to Get Record-Level Visibility into Which Rows Failed and Why

You get record-level visibility into which rows failed in a pipeline by capturing rejected records at each transformation and load step, tagging them with a failure reason and source row identifier, and routing them to a dedicated error table where they can be inspected and reprocessed. This guide is for data engineers and analysts who need more than a row count when a pipeline run reports errors. By the end, you will have a system that tells you exactly which rows failed, at which step, and why, so you can fix the root cause rather than rerun the full pipeline blindly.

Most ETL tools surface job-level failure counts by default. That tells you something went wrong, but not what to do about it. Row-level pipeline errors require a deliberate logging strategy built into the pipeline itself.

The Problem with Job-Level Error Reporting

A pipeline run reports "147 rows failed." The job log shows a type mismatch error. Without record-level error logging, the investigation starts over from scratch: pull the source data, rerun a sample, guess which rows triggered the error. For pipelines processing tens of thousands of records, that process can take hours.

The underlying issue is that most pipeline runs capture errors at the job level, not the row level. They tell you a step failed; they do not tell you which input record caused it or what the specific value was. Debugging failed rows in a pipeline without row-level context means working backward from a stack trace, which is slow and often inconclusive when the source data changes between runs.

What You Will Need Before Starting

• A pipeline with at least one step that currently produces errors or rejected records
• A destination table or storage location for error records (a separate schema in your warehouse works well)
• Write access to add transformation steps and output branches to the existing pipeline
• An understanding of which fields in your source data are most likely to cause failures (nulls in required fields, type mismatches, out-of-range values)

How to Get Record-Level Visibility into Failed Rows: Step-by-Step

Step 1: Identify Where in the Pipeline Rows Are Being Lost

Before building any error capture logic, you need to know at which steps records are currently disappearing. Most pipelines drop rows silently at transformation filters, join steps, or load operations without any indication that records were excluded.

What to do:

• Run the pipeline and compare record counts at each step: source record count, post-transformation count, and destination load count; a drop between any two steps indicates rows are being lost at that point
• Check join steps specifically; inner joins silently drop records from either side that do not find a match, and this is one of the most common sources of unexplained row loss
• Review filter steps for conditions that may be excluding valid records alongside invalid ones (for example, a filter that drops null values in a field that is legitimately null for some record types)
• Note every step where the record count decreases and rank them by volume of loss

Output of this step: A map of every pipeline step where records are currently being dropped, with approximate row counts per step.

Step 2: Add an Error Branch to Each Failing Step

With the drop points identified, add a parallel output branch at each one. The main branch continues to process valid records as before; the error branch captures rejected records and routes them to a holding area for inspection.

What to do:

• At each step where records are lost, add a secondary output that captures records that fail the step's condition rather than discarding them; in most ETL tools this is called a rejected records output, an error branch, or a conditional router
• For transformation steps that apply data type conversions, route any record where the conversion fails to the error branch rather than letting it abort the step or get silently dropped
• For filter steps, add an inverse filter on the error branch: records that fail the main filter condition go to the error branch, so you can see exactly what was excluded and why
• For load steps, capture rows that fail insertion due to constraint violations (duplicate keys, null violations, type mismatches at the database level) and route them to the error branch before the step retries or aborts

Output of this step: An error branch attached to each drop-point step, capturing rejected records before they are discarded.

Where Integrate.io helps: Integrate.io surfaces a rejected records output on transformation and load steps by default. You can connect that output directly to a destination without writing any custom error-handling logic, which means the error branch is a configuration choice rather than a development task.

Step 3: Tag Each Rejected Record with a Failure Reason and Source Identifier

A rejected record by itself is only half the information you need. Without a failure reason and a way to trace it back to the source system, the error table becomes another pile of unstructured data. Tag every rejected record at the point of capture.

What to do:

• Add a field to each rejected record that names the step where it failed, for example "step_name: address_type_conversion" or "step_name: warehouse_load"; this tells you immediately which part of the pipeline rejected the row
• Add a failure reason field with a short, specific description of why the record was rejected: "null value in required field," "duplicate primary key," "date format mismatch," or "value exceeds column length"
• Preserve the source record's natural identifier in the error record (the customer ID, order number, or transaction ID from the source system) so the row can be looked up in the source system and corrected
• Add a pipeline run timestamp so you can filter error records by run and track whether a recurring failure is getting better or worse over time

Output of this step: Rejected records tagged with step name, failure reason, source identifier, and run timestamp, ready to be written to an error destination.

Where Integrate.io helps: Integrate.io's transformation steps let you add calculated fields to the error branch output, so tagging rejected records with step name and failure reason is a field-addition operation rather than a custom script.

Step 4: Route All Error Records to a Dedicated Error Table

With tagged error records flowing out of each error branch, route them all to a single destination. A centralized error table makes it possible to query across all failure types, run counts, and step names in one place.

What to do:

• Create a dedicated error table in your warehouse with columns for: source table name, pipeline run ID, step name, failure reason, source record identifier, the full rejected row as a JSON or text field, and the run timestamp
• Route all error branches from all steps in the pipeline to this single table; use an append load mode so each run adds new rows rather than overwriting previous errors
• If the pipeline handles multiple source objects (multiple tables or files), add a source object name field so errors from different sources can be filtered separately
• Index the error table on run timestamp and step name so queries against large error volumes stay fast

Output of this step: A single error table in the warehouse receiving tagged rejected records from all pipeline steps, queryable by run, step, and failure reason.

Step 5: Build a Failure Summary Query

An error table is only useful if someone looks at it. A standard failure summary query run after each pipeline execution turns the error table from a passive log into an active diagnostic tool.

What to do:

• Write a query that groups the error table by step name and failure reason, shows the count of rejected records per combination, and filters to the most recent pipeline run; this gives a one-screen summary of every failure type and its volume
• Add a second query that lists the source record identifiers for the top failure type, limited to the first 50 rows; this is the starting point for root cause investigation
• Schedule the summary query to run automatically after each pipeline execution and post results to a Slack channel or email report so the team sees failures without having to remember to check
• Track the error counts per run over time; a failure reason that appears in every run at the same volume is a systemic data quality issue in the source system, not a pipeline bug

Output of this step: A failure summary query and alert that surfaces record-level error counts by step and reason after every pipeline run.

Step 6: Build a Reprocessing Path for Corrected Records

Visibility into failed rows is only half the value. The other half is being able to fix the underlying records and push them through the pipeline without rerunning the full job. A reprocessing path makes error resolution fast and auditable.

What to do:

• Design the error table so it includes enough of the original record to allow correction: either the full source row as a stored field, or the source identifier plus a link back to the source system
• For errors caused by data quality issues in the source (null values, wrong formats, out-of-range entries), build a correction workflow: export the error records, fix the values, and load the corrected rows back into the source or directly into a reprocessing staging table
• Create a separate pipeline or a conditional branch in the main pipeline that reads from the reprocessing staging table and attempts to load those records again, logging the outcome back to the error table with a "reprocessed" status flag
• For errors caused by pipeline configuration (wrong type mapping, incorrect filter condition), fix the pipeline first, then reprocess; do not push records through a broken step

Output of this step: A documented reprocessing path that takes corrected records from the error table through to the destination, with outcomes logged back to the error table.

Common Mistakes to Avoid

• Logging errors only at the job level: A job-level error count tells you something failed; it does not tell you which records or why. Always capture errors at the step level with row identifiers attached.
• Using the same table for errors and valid records: Mixing rejected records into the destination table makes it impossible to distinguish bad data from good data downstream. Route errors to a dedicated error table from the start.
• Capturing the error message without the source identifier: An error message like "null value in required field" is useless without knowing which record triggered it. Always preserve the source record's natural key in the error output.
• Overwriting the error table on each run: Using a truncate-and-reload mode on the error table means you lose the history of which records failed in previous runs. Use append mode and filter by run timestamp to see current errors.
• Never acting on the error table: An error table that nobody queries becomes background noise. Tie it to a scheduled alert and assign ownership for reviewing it after each run. Integrate.io's pipeline monitoring can trigger notifications when rejected record counts exceed a threshold, so errors surface automatically rather than waiting for someone to investigate.

Conclusion

Getting record-level visibility into which rows failed and why requires four things working together: error branches that capture rejected records at each step, tags that identify the step name, failure reason, and source identifier, a centralized error table that accumulates results across runs, and a summary query that surfaces the data after each execution. Teams that build this system stop debugging pipeline failures from job-level stack traces and start fixing specific records with known causes.

Integrate.io's rejected record outputs and calculated field steps make it possible to build this pattern without custom code, so the time investment goes into designing the error schema rather than writing logging infrastructure. Once record-level error logging is in place, reprocessing corrected rows and tracking data quality trends over time become natural extensions of the same system.