How to capture exception results in seperate table if the business rules fails during data ingestion

SUSHIL
Level 3
How to capture exception results in seperate table if the business rules fails during data ingestion

Hi all, 

Can you help me regarding below requirment. 

 

I am having use case where I want to capture my exception in seperate table  based on business validation rule if fails 

Consider there is source table from where we are going to prepare data for target table. 

During the source to target ingestion, for example in source there is column named customer id. 

I need to configure my business rules

1) should be mandatory

2) length should be 10

3) data type should be numeric

If any rules failed or all the above fails during  data preparing to target, the exception should capture in seprate table and data need to be prepared for the exception  captures

 

 

0 Kudos
1 Reply
Manuel
Dataiker Alumni

Hi, 

I suggest you approach your challenge as data preparation logic:

  • In a Prepare recipe, create new columns to indicate the failure of each condition separately;
  • Create a summary column that marks the row as containing at least one error;
  • Use a filter recipe to separate the errored records into their own dataset;
  • Prepare the errored dataset into the format you desire.

To identify those specific errors, you can use the following processors of the prepare recipe:

  1. Use "Formula" with isBlank or isNull (whichever works in your data) to validate an id exists
  2. Use "Extract with Regular Expression" to validate the 10 characters, ticking the create Found column
  3. Use "Extract with Regular Expression" to validate all numerical, ticking the create Found column.

FYI, you can actually use just one single "Extract with Regular Expression" processor to validate all conditions in one go (10 digits), but that would not allow distinct error messages.

If you are not familiar with Regular Expressions, check out the Smart Builder functionality in this video, https://videos.dataiku.com/watch/5RLFrHK4dEPFwuV7BoV8kZ?

I hope this helps.