Query about validating source data format

Hi

I was wondering if there was clean and quick method of checking the format of an inbound source file before any heavy lifting is done in a transformation. I know I can check the value of a field (eg. Is Date) and then abort as necessary. But in our case we had setup a transformation against a CSV file that had a header row. Then all of sudden the source system dropped the header row and the first column. This was obviously a breaking change and probably done in error.

The Transformation is fairly basic with a 1:1 mapping and would have failed when it went to write the data to the target database table. But as it happens we also had a validator in play that filtered out rows we didn't need and because all the columns were shifted to the left by 1. The validator ended up discarding all of the rows but ending without error.

Our package with the new "Fail with Zero Writes" caught the error.

But I was wondering if the transformation could have seen the issue straight away and avoided reading the data in the first place.

For example could there be some METADATA check that confirms the actual number of columns against the expected number?

Because we skip the header row we don't have a chance to read it. But even if we did we would have to do a number of transformation steps and even perhaps add in an extra validator to check the row number then check the value in a certain field to make sure it is a column name we expected.

I am sure there are plenty of ways to do this but was just after a quick and succinct method that we could possibly use across the board and implement in most of our transformations.

For the moment my workaround is to still skip the header and by chance there is a date field, so I check it is a date and abort if not. But the fact there was something I could type check was luck.

cheers

There is no quick fix for this a the moment or easy solution unfortunately

Thanks Peter. Thought I'd ask. cheers