SAFE mode: NULL instead of a crash

The instrument bay

Today the commandant opens a new section of the archive for you: the colonies' instrument logs. The life-support sensors broadcast their readings straight into the ether, and the receivers stored everything exactly as it came — as strings. Wherever the link broke, garbage was left in the string: an "n/a" marker, fragments, emptiness.

In Postgres you would have cleaned that up in advance — ETL, regular expressions, a staging table. Here there is nobody left to clean: the archive is dead, and nobody will rewrite the data now. The query has to survive dirty data on its own.

QUERY: I searched this dialect for the word SAFE. I found a whole family. It seems the archive knew what kind of data it would have to live with.

CAST takes everything down, SAFE_CAST does not

The ordinary CAST is the same here as in Postgres — and just as lethal: one uncastable value kills the entire query:

SELECT CAST('н/д' AS INT64) AS reading;
-- Could not cast literal "н/д" to type INT64

On a table of a billion rows that means: the query ran, burned bytes — and died somewhere around the 998 millionth row. The meter, by the way, charged for all of it.

The dialect's counterpart is SAFE_CAST: the same syntax, but an uncastable value becomes NULL and the query makes it to the end. Look at four readings from a single sensor:

SAFE_DIVIDE: a zero in the denominator

The second classic of dirty data is division by zero. The ordinary / crashes here too:

SELECT 1 / 0;  -- division by zero

SAFE_DIVIDE(a, b) returns NULL when b is zero or NULL. In Postgres you wrote a / NULLIF(b, 0) — the idiom works here as well, but the dialect gives you a ready-made function.

Let us compute the terminals' conversion: how many of a colonist's product views ended in a purchase. Some colonists have no views at all — and there is your zero in the denominator:

IF and IFNULL: conditionals the dialect's way

The CASE you know from Postgres works unchanged, but for the two most frequent cases the dialect keeps short forms:

• IF(cond, a, b) — instead of CASE WHEN cond THEN a ELSE b END;
• IFNULL(x, default) — instead of a two-argument COALESCE(x, default).

-- Postgres:
CASE WHEN total >= 1000 THEN 'крупный' ELSE 'обычный' END
-- BigQuery:
IF(total >= 1000, 'крупный', 'обычный')

Let us label the supply station's orders:

IFNULL gets along with the SAFE family: the chain "compute the dangerous thing → substitute a fallback" is the dialect's signature move. And it plugs straight into the star from the first lesson:

SELECT * EXCEPT(raw_payload) REPLACE(IFNULL(revenue, 0) AS revenue)
FROM report

— "every column but the junk one, and plug the holes in revenue with a zero". Exactly this move is waiting for you in the practice tasks below. Let us try the chain on conversion:

When SAFE hides a bug

Re-read the output: colonist 6 has a conversion of 0. Now look at their row in the previous cell: 0 views and 1 purchase. That is not "does not convert" — that is a purchase past the storefront: another route to the purchase, or a hole in the logging. SAFE_DIVIDE answered honestly with NULL ("there is nothing to count"), while our IFNULL(..., 0) lied with confidence: "zero".

With aggregates it is trickier still: AVG ignores NULL but counts 0. The average conversion "over colonists with NULL" and "over colonists with zeros" are two different numbers, and both look plausible.

The rule: SAFE_ is for garbage you have decided to ignore deliberately.* For impossible states the dialect has the opposite — ERROR():

SELECT IF(views = 0 AND purchases > 0,
          ERROR('purchase without a view: check the logging'),
          SAFE_DIVIDE(purchases, views)) AS conversion
FROM per_user

ERROR() deliberately crashes the query with your message: better to fall loudly than to count nonsense in silence. There is no live cell for ERROR() in this chapter, and that is on purpose: its successful outcome is a red refusal, and in the trainer that is indistinguishable from a broken example. The archive accepts the syntax: while the condition is false, the branch with ERROR() simply never fires — and the one that fires takes the whole query down.

The SAFE. prefix for every other function

The SAFE family is wider than a couple of functions: almost any scalar function can be called with the SAFE. prefix — and instead of an error it returns NULL. The functions in the example are simply the first ones to hand: PARSE_DATE merely tries to read a date of the form YYYY-MM-DD, and there is no need to unpick its mask right now. Without the prefix both lines below crash the query:

SELECT SUBSTR('архив', 0, -2);       -- SUBSTR: length must be positive number
SELECT PARSE_DATE('%F', 'не дата');  -- error parsing [не дата] with format [%F]

The prefix works only with scalar functions: SAFE.SUM(...) is rejected by the archive right at parse time (SAFE is not supported for function sum), and syntactically the prefix cannot be attached to operators such as / — which is why SAFE_DIVIDE and SAFE_CAST are shipped as separate functions.

Important: SAFE. mutes a COMPUTATION error, not a type error. SAFE.SUBSTR(order_id, 2, 2) from the previous lesson will still fall over with No matching signature: wrong argument types are caught before the run, and there is nothing to mute there. Compare three calls — two dangerous ones under the prefix and one that is bound to work: