Incoming data is split into valid rows and exception rows. Valid rows continue to the load. Each exception row is kept with its full context, sent to the person who owns that kind of problem, and put back into the pipeline once it is fixed.
A row that fails a rule becomes an exception record. The record is kept, given an owner, and replayed once the row is fixed.
An exception is a row that fails a rule. A rule is a condition every row is checked against, such as a quantity of zero or more, or a plant code that exists in the plant table. Incoming data is therefore two sets: the valid rows, which passed every rule, and the exception rows, which failed one. Both sets are stored with lineage, the record of which tables each row was built from.
An exception record keeps everything the owner needs to fix it. The five items below are stored with every exception, whether the row came from SAP, from Oracle, from the warehouse system or from a file.
A rule is hard or soft. The kind decides whether a failing row is rejected or flagged.
| Kind of rule | What happens to a row that fails it | Where the row goes | Examples |
|---|---|---|---|
| Hard rule | The row is rejected from the load and becomes an exception record. | The exception table, with the full row, the rule and the run. The valid rows load as usual. | A negative quantity. A plant code that is missing from the plant table. A delivery date before the order date. |
| Soft rule | The row is flagged and continues to the load. An exception record is created for the owner. | The target, with a flag on the row, plus the exception table. | A phone number in a format other than the international standard. A description with trailing spaces. |
| Passes every rule | The row is valid. | The target and the certified view, counted in the control totals. | 840,782 of the 841K rows in the OTIF nightly run. |
Choose a hard rule when a wrong value would make the result wrong, such as a negative quantity in a delivery measure. Choose a soft rule when the row is usable and the owner should see the problem, such as a phone number in a non-standard format. Each validation operator on the canvas can be set either way. The validation operators →
Six steps take a row from the rule that failed it to the owner who fixes it and back into the pipeline.
Routing is a table. Each row maps one exception type and one severity to an owner, an acknowledge-by time and a resolve-by time. On the showcase estate the table has six rows, shown below.
Before the table, exceptions on the showcase estate went to email distribution lists and took 3.4 hours on average to reach a person who could act. With the table, each exception reaches its owner in seconds, and manual triage is 0.
The table is configuration. The business owner of each exception type maintains their row, and changes are promoted from development to staging to production through the same approval gates as any other change. SC stands for supply chain; MDM stands for master data management, the team that keeps customer and material records consistent.
| Exception type | Severity | Routed to | Acknowledge by | Resolve by |
|---|---|---|---|---|
| OTIF breach | High | SC Ops Manager | 4 h | 24 h |
| OTIF breach | Medium | SC Analyst queue | 8 h | 48 h |
| Stock-out risk | High | Procurement and Supply Chain | 1 h | 8 h |
| Country-code error | Medium | Data Steward | 24 h | 72 h |
| Duplicate customer | Medium | MDM team | 72 h | 7 d |
| Price variance | Low | Finance | 24 h | 5 d |
One exception on the showcase estate, EXC-0001, from the moment it was raised to the executive alert.
An unanswered exception moves up the reporting line at each missed clock. The acknowledge-by time and the resolve-by time come from the routing table; when either passes, the exception goes to the next person up with its full context and the time already spent.
The reminders use the channels people already watch. A quality anomaly reaches Slack #data-ops 340 ms after a rule fails; a projected miss of a deadline pages PagerDuty; a change in certification status opens a ServiceNow record; a resolved anomaly is announced in Slack.
Each level is a named person or queue, up to the executive at the top of that reporting line, so every open exception has an owner at every moment.
A rule can pass its result to the next rule. The last rule in the chain can raise the exception and prepare the fix.
PO-DRAFT-9901, in the ERP that owns purchasing. An ERP is the system that holds orders, stock, materials and finance. The buyer approves or rejects the draft. The chain takes 180 ms end to end.A rule can pass its result to another rule. The second rule then adds a check the first one lacked, and the exception that reaches the owner carries the conclusion of the whole chain.
The draft order is created in whichever ERP owns purchasing, through that system’s released interface: BAPI transactions on SAP, open interface tables and public APIs on Oracle. A BAPI is a function SAP publishes for reading or writing one business object. Every write carries a unique key, so a retry cannot create a duplicate, and the ERP’s confirmation is captured. How write-back works →
Rules run in batch on the run window, such as the OTIF rule every night, or on a single row as it lands, such as the stock-out chain above.
A rule applied to a whole batch holds the batch and pauses the pipeline until the batch is fixed.
Some problems belong to a whole batch. On the showcase estate, a certification rule required the customer phone field, KNA1.TELF1 in SAP, to be in the international E.164 format: a plus sign, the country code and the number, with no spaces. Only 38% of the rows passed. Routing the other 62% of a customer table to a steward one row at a time would help nobody, so the rule works as a quality gate: it holds the batch and the pipeline pauses at its checkpoint. How a paused pipeline resumes →
The gate shows the steward three ways to fix the batch, each with its estimated coverage after the fix. The steward picks one on the canvas, the batch is replayed, and the certification proceeds. All three are showcase examples.
A transform at ingest puts each number into the E.164 format, using the customer’s country code in KNA1.LAND1 to add the dialling prefix. Estimated coverage after the fix: 94%. This is a suggested fix with its estimated coverage.
Missing or invalid numbers are filled from the Oracle table AR_CONTACTS, which holds 71% of the missing numbers. Estimated coverage after the fix: about 87%.
TELF1 is taken out of the certified scope. The view certifies on the fields that pass, and the phone numbers stay in the source table as they are.
One fix at the source or at ingest corrects every open record that failed on the same field.
Most exceptions share a cause. When the same field is fixed at the source, or by one transform at ingest, all 1,248 open records that failed on it are corrected at the next run. The steward fixes the field once, and the replay picks up every affected row.
Corrected master data that belongs in the source goes back through write-back. On the showcase estate a country-code standardisation was written back to Oracle ERP, confirmed by the ERP and reconciled at the end of the run.
Each rule tracks its hit rate, so you can see a problem shrinking. RULE-018 on the showcase estate has 11.8M evaluations, 0.036% exceptions and a trend of −22%. The table on the right lists the batch rule runs on the showcase estate with the rows each one evaluated.
| Batch rule run | Rows evaluated | Result | Time |
|---|---|---|---|
| OTIF nightly | 841K | 218 exceptions | 4m 12s |
| Duplicate customer weekly | 482K | 96 pairs | — |
| Retention monthly | 22.4M | 441 flagged | — |
| Price variance daily | 48K | 12 exceptions | — |
A fixed row finishes the run it started, and the exception closes with a link to the loaded row.
Replay puts a fixed row where it was going. The row re-enters the pipeline at the step it left, passes the rule, loads, and the exception closes with a link to the loaded row. A full reload of the batch is therefore rare.
Replay works the same way for a single row fixed by its owner and for a whole batch fixed at a quality gate. In both cases the control totals show the valid count rising and the exception count falling, with the two still adding up to the incoming total.
A rejected row is kept as an exception record with every field of the original row, the rule it failed, the run it came from and its source system. It stays until it is fixed and replayed, and the closed exception keeps a link to the loaded row. The valid count and the exception count always add up to the incoming count.
The business owner of each exception type, through the routing table. Each row of the table maps an exception type and a severity to a person or a queue, an acknowledge-by time and a resolve-by time. The table is version-controlled and promoted from development to staging to production through approval gates, like any other change.
The clocks keep running and the escalation path takes over. When the acknowledge-by time passes, the exception moves one level up the reporting line with its full context, as EXC-0001 did at 14:18. For types where several people can act, the routing table names a queue as the owner, such as the SC Analyst queue for a Medium OTIF breach.
Yes, as a draft for a person to approve. RULE-023 on the showcase estate creates a draft purchase order in the ERP that owns purchasing, through that system’s released interface: BAPI transactions on SAP, open interface tables and public APIs on Oracle. The buyer approves the draft. Every write carries a unique key, is confirmed by the ERP and is reconciled at the end of the run.
Each rule tracks its hit rate: the number of evaluations, the share that raised an exception, and the trend. RULE-018 on the showcase estate has 11.8M evaluations, 0.036% exceptions and a trend of −22%. The exception hub shows the open count by severity, 18 High, 841 Medium and 389 Low on the showcase estate, and each batch run lists the rows it evaluated and the exceptions it raised.
One source, one rule set, one routing table: exceptions with owners, clocks and a replay path on your own data, then the four-week first load with control totals at the end.