SAFE_CAST, SAFE_DIVIDE and the SAFE. prefix in BigQuery

One bad value stops a whole BigQuery query. SAFE_CAST, SAFE_DIVIDE, the SAFE_ math functions and the SAFE. prefix return NULL instead of failing, and how to find the rows that would have failed.

When one row out of a billion holds a value a function can’t handle, BigQuery fails the whole query. There is no partial result and no list of bad rows, only the error message. The SAFE family turns those errors into NULL: SAFE_CAST for casts, SAFE_DIVIDE and the other SAFE_ math functions for arithmetic, and the SAFE. prefix for almost every other function.

One bad value fails the query

A text column that should hold numbers, with one 'n/a' in it:

WITH raw AS (
  SELECT '42' AS amount UNION ALL
  SELECT 'n/a' UNION ALL
  SELECT '17'
)
SELECT SUM(CAST(amount AS INT64)) AS total
FROM raw

SAFE_CAST returns NULL for the value it can’t convert, and the query finishes:

WITH raw AS (
  SELECT '42' AS amount UNION ALL
  SELECT 'n/a' UNION ALL
  SELECT '17'
)
SELECT amount, SAFE_CAST(amount AS INT64) AS parsed
FROM raw
ORDER BY amount

The NULLs then disappear quietly in SUM and AVG. Before you rely on SAFE_CAST, find out what is failing. A value that isn’t NULL but casts to NULL is a bad value:

WITH raw AS (
  SELECT '42' AS amount UNION ALL
  SELECT 'n/a' UNION ALL
  SELECT '17'
)
SELECT amount AS bad_value
FROM raw
WHERE amount IS NOT NULL AND SAFE_CAST(amount AS INT64) IS NULL

Division

Dividing by zero is an error, not NULL or infinity:

SELECT 10 / 0 AS ratio

There are three ways around it, each with a different result for zero:

SELECT
  SAFE_DIVIDE(10, 0) AS safe_divide,
  SAFE_DIVIDE(10, 4) AS normal,
  IEEE_DIVIDE(10, 0) AS ieee_divide,
  10 / NULLIF(0, 0) AS nullif_trick

• SAFE_DIVIDE(x, y) returns NULL when y is 0. This is the one to use for ratios in reports.
• IEEE_DIVIDE(x, y) follows floating-point rules and returns Infinity, -Infinity or NaN.
• x / NULLIF(y, 0) works in any SQL dialect and also returns NULL.

Integer overflow

INT64 is BigQuery’s only integer type. Going past its maximum is an error:

SELECT 9223372036854775807 + 1 AS too_big

SAFE_ADD, SAFE_SUBTRACT, SAFE_MULTIPLY and SAFE_NEGATE return NULL on overflow instead:

SELECT
  SAFE_ADD(9223372036854775807, 1) AS overflow_add,
  SAFE_MULTIPLY(9223372036854775807, 2) AS overflow_multiply,
  SAFE_NEGATE(-9223372036854775808) AS overflow_negate

The SAFE. prefix

Put SAFE. in front of a function name and it returns NULL instead of an error caused by its input values. It works with string, math, date, time, JSON and most other scalar functions:

SELECT
  SAFE.PARSE_DATE('%Y-%m-%d', '2026-02-30') AS bad_date,
  SAFE.SUBSTR('bigquery', 0, -2) AS bad_length,
  SAFE.LOG(-1) AS bad_log,
  SAFE.PARSE_JSON('{oops') AS bad_json

What SAFE. doesn’t cover:

• Errors in the arguments. SAFE. protects only the function itself. If an argument fails while it is computed, the query still fails:

  SAFE.SQRT(1 / 0)BigQuery rejects this query

  1

  SELECT SAFE.SQRT(1 / 0) AS still_fails

  Error in BigQuery

  division by zero: 1 / 0

• Operators, CAST and EXTRACT. +, /, =, CAST and EXTRACT can’t take the prefix. Use SAFE_ADD, SAFE_DIVIDE and SAFE_CAST:

  SAFE.CAST is not a functionBigQuery rejects this query

  1

  SELECT SAFE.CAST('x' AS INT64) AS nope

  Error in BigQuery

  Function not found: `CAST`; Did you mean SAFE.last? at [1:8]

• Aggregate, window and user-defined functions.

• Errors unrelated to input values, such as running out of resources.

Failing on purpose

The opposite is sometimes what you want: stop a pipeline when the data is wrong, rather than load wrong numbers. ERROR(message) fails the query with your own message, which you can include in an IF or CASE:

WITH payments AS (
  SELECT 'p1' AS id, 30 AS amount UNION ALL
  SELECT 'p2', -5
)
SELECT id, IF(amount >= 0, amount, ERROR(FORMAT('negative amount in %s', id))) AS amount
FROM payments

As a statement of its own, ASSERT condition AS 'message' does the same, which is handy between the steps of a script. See Scripting.

Failed queries are free

A query that fails isn’t billed. So a cast error at the end of a long query costs time, not money. See How a query is billed.