Apologies if this is answered elsewhere, I'm not sure how to phrase the question in EasyMorph terms.
We are doing data transform and then inserting / updating a migration database. Due to data quality in the source, we have records that quite often won't be insertable into the target DB due to null conditions or bad FKs.
I want to engineer a process so that any record that CAN go into the export db does, and then every record that fails follows a different workflow so we can write it to a troubleshooting table and then go back and fix the source data.
Is this possible? Or is there another approach that would solve this OTHER than trying to catalog all of the possible issues in advance and setting up filters to filter those records out?
Of course, as soon as I post, we figured it out - but there was a missing link we didn't understand so I'll put this here:
In the end what we realized was that a) we couldn't use bulk export and b) on the regular export action we needed to set the batch size to "1". I assume because the batch is done in transaction and so if ONE record in the batch fails, the whole batch gets that same error.
So once we did that and set our export to put the error in a new column, we were able to achieve our goal. Now we can just pull out that error and hopefully get it written as a string to our errors table.
Usually, it's a good idea to try detecting bad rows before exporting, because exporting rows one by one can be too slow. For instance, you can use the "Select matching DB rows" action to pre-match foreign keys to primary keys in EasyMorph and see if any of the FKs fail to match even before you do the export.
Thanks, that's helpful. Yeah, in a perfect world we'd just catch all these along the way, and we might still add in filters to find the bad ones before we try to import them, BUT the data is rough so we might just have to see how well it goes.
This is a one time process, so we can afford for it to be a little slow, but we'll see what happens IRL.