DEA-C02 Data Transformation Practice Question

Answer analysis

Option-by-option breakdown

For each option: why learners choose it and why it is or isn't the right answer here.

• ✓

  COALESCE(TRY_TO_NUMBER(raw:discount), 0)

  Why this is correct

  This expression uses TRY_TO_NUMBER on the VARIANT value, which attempts to convert it to a number regardless of whether it is stored as a number or a string. If the conversion fails (e.g., missing key or non-numeric string), it returns NULL, and COALESCE replaces it with 0. This directly handles both numeric and string types and missing keys, making it the correct and efficient solution.

• ✗

  COALESCE(CAST(raw:discount AS NUMBER), 0)

  Why it's wrong here

  CAST will also error on invalid conversions, similar to TO_NUMBER. It does not provide the safe error handling of TRY_TO_NUMBER. Additionally, CAST might not handle string representations of numbers as gracefully. For robust transformation of semi-structured data with varying types, TRY_TO_NUMBER is the recommended function.

• ✗

  COALESCE(TO_NUMBER(raw:discount), 0)

  Why it's wrong here

  TO_NUMBER will raise an error if the value cannot be converted, such as when the key is missing or the string is not a valid number. This would cause the query to fail for rows with missing or invalid discounts. TRY_TO_NUMBER is needed to safely handle such cases by returning NULL instead of erroring.

• ✗

  COALESCE(TRY_TO_NUMBER(raw:discount::STRING), 0)

  Why it's wrong here

  This expression attempts to cast the discount to a string first, then to a number. However, if the discount is already numeric, casting to string and then to number is redundant and may cause precision loss. Additionally, TRY_TO_NUMBER on a string that is not a valid number returns NULL, which COALESCE then replaces with 0. But it does not handle numeric values directly efficiently. It works but is not the most direct.