# 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.

Source: https://docs.querylab.io/bigquery/safe-functions/

Updated: 2026-10-06

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:

**CAST on a column with one bad value**

```sql
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
```

BigQuery rejects this query:

```text
Bad int64 value: n/a
```

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

**SAFE_CAST turns 'n/a' into NULL**

```sql
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
```

Result in BigQuery (3 rows):

| amount | parsed |
| --- | --- |
| 17 | 17 |
| 42 | 42 |
| n/a | NULL |

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:

**list the values that don't cast**

```sql
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
```

Result in BigQuery (1 row):

| bad_value |
| --- |
| n/a |

## Division

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

**10 / 0**

```sql
SELECT 10 / 0 AS ratio
```

BigQuery rejects this query:

```text
division by zero: 10 / 0
```

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

**SAFE_DIVIDE, IEEE_DIVIDE and NULLIF**

```sql
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
```

Result in BigQuery (1 row):

| safe_divide | normal | ieee_divide | nullif_trick |
| --- | --- | --- | --- |
| NULL | 2.5 | Infinity | NULL |

- `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:

**the largest INT64 plus one**

```sql
SELECT 9223372036854775807 + 1 AS too_big
```

BigQuery rejects this query:

```text
Integer Overflow
```

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

**SAFE_ math functions on overflow**

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

Result in BigQuery (1 row):

| overflow_add | overflow_multiply | overflow_negate |
| --- | --- | --- |
| NULL | NULL | NULL |

## 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:

**February 30, a negative length, log(-1), broken JSON**

```sql
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
```

Result in BigQuery (1 row):

| bad_date | bad_length | bad_log | bad_json |
| --- | --- | --- | --- |
| NULL | NULL | NULL | NULL |

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)**

```sql
SELECT SAFE.SQRT(1 / 0) AS still_fails
```

BigQuery rejects this query:

```text
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 function**

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

BigQuery rejects this query:

```text
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`:

**stop on a negative amount**

```sql
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
```

BigQuery rejects this query:

```text
negative amount in p2
```

As a statement of its own, `ASSERT condition AS 'message'` does the same, which is handy between the steps of a script. See [Scripting](https://docs.querylab.io/bigquery/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](https://docs.querylab.io/bigquery/how-queries-are-billed/).

See what SAFE_CAST returns for a value that will not convert. Open the playground at https://app.querylab.io?playground=true&utm_source=docs&utm_medium=concepts to run it. It is the real app on public BigQuery datasets and needs no sign-in.

## Sources

Based on the BigQuery documentation by Google, CC BY 4.0, rewritten with examples checked against BigQuery:

- [Function calls, SAFE. prefix](https://cloud.google.com/bigquery/docs/reference/standard-sql/functions-reference#safe_prefix)

- [Conversion functions, SAFE_CAST](https://cloud.google.com/bigquery/docs/reference/standard-sql/conversion_functions#safe_casting)

- [Mathematical functions](https://cloud.google.com/bigquery/docs/reference/standard-sql/mathematical_functions)