A safe numeric cast silently lowers a total

Illustrative learning scenario; not a claimed client engagement. A fictional price staging feed contains valid decimal text, a genuine zero and a malformed value. TRY_TO_DECIMAL keeps the query running, but SUM ignores the resulting NULL, so a dashboard appears successful with an incomplete total.

Symptoms

Count rejected conversions instead of allowing SUM to conceal invalid input.

Diagnosis

1. Count input rows and inspect raw values alongside parsed values before aggregation.
2. Distinguish a source NULL from a non-null value that failed conversion. Decide separately whether missing values are allowed.
3. Report accepted totals with rejection counts; a partial sum must not be presented as a complete reconciliation.

Runnable minimal example

Snowflake SQL; read-only CTE fixture. Not executed in a Snowflake account; expected output is documentation-reviewed. Uses fixed precision and scale, not a floating-point target.

WITH raw AS (
  SELECT column1::INTEGER id, column2::VARCHAR amount_text
  FROM VALUES (1, '12.50'), (2, 'oops'), (3, '0.00')
), parsed AS (
  SELECT id, amount_text,
         TRY_TO_DECIMAL(amount_text, 12, 2) amount
  FROM raw
)
SELECT COUNT(*) input_rows,
       COUNT(amount) accepted_rows,
       COUNT(CASE WHEN amount_text IS NOT NULL AND amount IS NULL
                  THEN 1 END) rejected_rows,
       SUM(amount) accepted_total
FROM parsed;

Expected output — not executed

INPUT_ROWS | ACCEPTED_ROWS | REJECTED_ROWS | ACCEPTED_TOTAL
3          | 2             | 1             | 12.50

Fix and boundaries

Use the parsed relation as a validation boundary. Send invalid rows to a controlled rejection workflow and withhold publication or label the total as partial under an explicit quality policy. This lab only diagnoses rows; it does not persist a quarantine or change production data. A strict TO_DECIMAL is appropriate if the entire batch must fail fast.

Why it works

TRY_TO_DECIMAL returns NULL on a failed conversion. COUNT(amount) and SUM(amount) ignore that NULL, but the conditional count exposes it. The valid zero remains a numeric row and contributes to accepted_rows. Explicit precision and scale avoid the default integer scale, but values with excessive fractional digits may still be rounded; conversion success alone does not prove the original scale was valid.

Prevention

Monitor accepted, missing and rejected counts and enforce thresholds before publishing. Validate the source grammar and scale separately if rounding is forbidden. Test overflow and all-rejected batches, where SUM returns NULL; do not silently turn an unknown total into zero.

Practice exercise

Add a row with SQL NULL and one with 1.234. Add a missing_rows metric and decide whether excessive scale should be rejected before casting. Explain why rejected_rows alone cannot measure both problems.

Documentation

• Snowflake TRY_TO_DECIMAL
• Snowflake SUM