Is there a reliable way to make Magic ETL intentionally fail?

MattBeal

I have an ETL that uses, among other inputs, a Webform that isn't always maintained perfectly. One recent problem involved two rows for the same identifier, with different text in the description column.

What I'd like to do is make the ETL fail if something like this happens. I tried doing a Group By on the identifier column, count the values, then filtered for counts greater than 1, and then tried to force an error by dividing by 0, but this results in "infinity". Similarly, SQRT() of a negative number produces "NaN", but no error.

I know there's potentially other approaches to this problem, such as creating an ETL that does validation across multiple datasets, then set up an alert based on certain outputs, but in the meantime, alerts won't prevent the ETL from producing incorrect results. I'd like the ETL to stop until the problem is fixed.

GrantSmith

You can use the ERROR function to cause the dataflow to error out with a specific message in a formula tile.

CASE WHEN `Number` < 0 THEN ERROR('Number is negative') END

The one caveat to this is that you have to save the ETL immediately after you enter this formula otherwise if it attempts to validate your ETL it won't allow you to save the ETL so if you need to make future changes you may need to remove the ERROR function, make the changes you need then add the ERROR function back and immediately save the ETL.

GrantSmith

You can use the ERROR function to cause the dataflow to error out with a specific message in a formula tile.

CASE WHEN `Number` < 0 THEN ERROR('Number is negative') END

The one caveat to this is that you have to save the ETL immediately after you enter this formula otherwise if it attempts to validate your ETL it won't allow you to save the ETL so if you need to make future changes you may need to remove the ERROR function, make the changes you need then add the ERROR function back and immediately save the ETL.

MattBeal

That's excellent, thanks! I guess I didn't use the right search terms, but I was trying to search for etl, error, etc. and didn't find anything.

Even better, my next question was going to be how I can fail with a meaningful message.

GrantSmith

It's not a very well documented feature, I just happened to stumble across it when attempting to come up with a solution for you. If you could accept the answer so others will find it I'd appreciate it.