Associate Data Practitioner free question 2 / 10 · Domain 1: Data Preparation and Ingestion
A STRING column named amount in a BigQuery table holds a mix of values such as "1200" and "unknown". You want rows that can be read as numbers converted to NUMERIC, and rows whose conversion fails turned into NULL. Which expression should you use?
- SAFE_CAST(amount AS NUMERIC)
- PARSE_NUMERIC(amount)
- CAST(amount AS FLOAT64)
- CAST(amount AS NUMERIC) as a plain cast
Show the answer and explanation
Answer: A
Why this is correct
SAFE_CAST attempts the same conversion as a regular CAST, but it replaces a runtime error raised during the conversion with NULL. A STRING can be converted to NUMERIC, so "1200" becomes a number and "unknown" becomes NULL instead of stopping the query. Keep in mind that SAFE_CAST does not hide errors found while the query is being analyzed, for example when the two types cannot be converted to each other at all.
Why the other options are wrong
"PARSE_NUMERIC(amount)" … PARSE_NUMERIC reads a string as a NUMERIC value, but it has no behavior that turns a malformed numeric string into NULL. The row containing "unknown" would still raise an error.
"CAST(amount AS FLOAT64)" … This is a regular cast to FLOAT64. A string that is not a number, such as "unknown", causes a runtime error, and the target type is also not the NUMERIC you asked for.
"CAST(amount AS NUMERIC) as a plain cast" … A regular CAST cannot convert "unknown" to NUMERIC, so the query fails at runtime on that row instead of returning NULL.
Key point
SAFE_CAST covers runtime errors caused by a value that cannot be converted. It is a separate matter from a query that is invalid from the start because of an impossible type conversion.