A bronze table holds a column amount_raw typed STRING, in which most values look like 19.99 while a small share hold the text n/a. A silver load must produce a DECIMAL(10,2) column, must not fail when it meets an unparseable value, and must keep those rows in the silver table with a missing amount. The workspace runs with ANSI behaviour in effect. Which expression meets the requirement, and why?
-- silver load, one expression per candidate
SELECT order_id, <expression> AS amount FROM bronze.orders- AUse CAST(amount_raw AS DECIMAL(10,2)), because a cast to a decimal type substitutes NULL for a value it cannot parse and lets the load continue.
- BUse TRY_CAST(amount_raw AS DECIMAL(10,2)), because it returns NULL for a value it cannot parse and leaves the row present in the silver table. Correct
- CUse CAST(CAST(amount_raw AS DOUBLE) AS DECIMAL(10,2)), because routing the value through a floating point type suppresses the parse error before the decimal conversion.
- DUse CAST(amount_raw AS DECIMAL(10,2)) after filtering out rows whose amount_raw does not match a numeric pattern, because a silver row cannot carry a missing amount.
Why A is wrong: Tempting because CAST did behave this way under the legacy non-ANSI behaviour, where a failed cast returned NULL. With ANSI behaviour in effect a failed cast raises a runtime error and the load fails, which the requirement forbids.
Why B is correct: Correct. TRY_CAST performs the same conversion as CAST but yields NULL instead of raising an error when the input cannot be represented in the target type, so unparseable rows survive with a missing amount.
Why C is wrong: Tempting because a double is more permissive about scale than a decimal. The first cast still has to parse the string n/a and fails under ANSI behaviour, so the error occurs before the second cast is reached.
Why D is wrong: Tempting because filtering does avoid the error. It discards the affected rows, whereas the requirement states they must remain in the silver table with a missing amount, and silver tables can hold nullable columns.