Best Of

I'll just add some context for anybody still watching this issue. This comment is specific to Magic ETL; Beast Mode (Domo Adrenaline) works differently:

Issues like this happen when you mix floats and decimals, or sometimes integers and decimals (since dividing an integer by an integer produces a float). When a decimal is divided by zero, you get a "division by zero" error. A float, in contrast, becomes one of three special values depending on the numerator: Infinity, -Infinity, or NaN for numerators 1, -1, and 0 respectively.

Almost any function or operator applied to a decimal and a float will begin by trying to convert the float to a decimal (e.g. DECIMAL(8) / 8.0 becomes DECIMAL(8) / DECIMAL(8.0)). If the float is any of those aforementioned special values this conversion fails with the error you saw.

There are a couple ways to avoid this problem. The most common is to ensure that any denominator that might be zero becomes null instead, so any division by zero results in null instead of an error or a NaN/Inf/-Inf. For example, instead of 100 / maybe_zero you'd write 100 / NULLIF(maybe_zero, 0). This solution works for both floats and decimals as long as you apply it consistently.

However, if you already have NaN/Inf/-Inf in a float column, you need a way to get rid of it or at least treat it differently in your formula. To do that, you can use the FINITE(…) function. FINITE(num) is true when num is a real number, null if it is null, and false if it is any non-finite special number like NaN or Infinity. If you want to use a float column num without its NaNs or Infinities, you can wrap it in an expression like this: CASE WHEN FINITE(num) THEN num ELSE NULL END