ANSI mode errors on Databricks Runtime 17+: CAST_INVALID_INPUT, DIVIDE_BY_ZERO and overflow fixes cheat sheet

A notebook or job that worked on Databricks Runtime 15.4 or 16.4 fails after moving to 17.x with errors like [CAST_INVALID_INPUT], [DIVIDE_BY_ZERO] or [ARITHMETIC_OVERFLOW]. The code didn't change. The default did. ANSI SQL mode is enabled by default in Apache Spark 4.0 and Databricks Runtime 17.0 and above. Operations that used to return NULL quietly now raise an error.

Databricks SQL warehouses already had ANSI mode on by default, so the same query can behave one way on a warehouse and another way on an older cluster.

#Check which mode you're in

-- Notebook or job on a cluster / serverless
SET spark.sql.ansi.enabled;

-- SQL warehouse
SET ANSI_MODE;

In Python:

spark.conf.get("spark.sql.ansi.enabled")

#Error to fix lookup

The exact error class names and message text vary by runtime version. The class in square brackets at the start of the message is the part to search for. The message often adds a hint, such as "Use try_cast to tolerate malformed input", which tells you the intended fix.

#Find the bad rows before you change anything

Don't just wrap everything in try_ functions. First find out what the bad values are, because they may be a data quality problem upstream.

#Strings that won't cast

SELECT order_amount_raw, count(*) AS rows
FROM bronze.sales.orders_raw
WHERE order_amount_raw IS NOT NULL
  AND try_cast(order_amount_raw AS DECIMAL(18,2)) IS NULL
GROUP BY order_amount_raw
ORDER BY rows DESC
LIMIT 50;

Typical results include '', 'N/A', '1,234.50' and values with trailing spaces or currency symbols.

#Zero divisors

SELECT count(*) AS zero_rows
FROM silver.sales.orders
WHERE quantity = 0;

#Timestamps that won't parse

SELECT event_time_raw, count(*)
FROM bronze.web.events_raw
WHERE event_time_raw IS NOT NULL
  AND try_to_timestamp(event_time_raw, 'yyyy-MM-dd HH:mm:ss') IS NULL
GROUP BY event_time_raw
LIMIT 50;

#Fix patterns in SQL

#Casting

-- Before (returned NULL silently without ANSI, fails with ANSI)
SELECT CAST(order_amount_raw AS DECIMAL(18,2)) AS order_amount FROM bronze.sales.orders_raw;

-- After: explicit about what to tolerate
SELECT try_cast(regexp_replace(trim(order_amount_raw), ',', '') AS DECIMAL(18,2)) AS order_amount
FROM bronze.sales.orders_raw;

#Implicit casts in filters and joins

ANSI mode also catches comparisons between a number and a string that isn't a valid number:

-- Fails if any customer_code is not numeric
SELECT * FROM silver.crm.customers WHERE customer_id = customer_code;

-- Make the types match on purpose
SELECT * FROM silver.crm.customers WHERE customer_id = try_cast(customer_code AS BIGINT);

#Division and arithmetic

SELECT
  try_divide(revenue, quantity)              AS unit_price,      -- NULL when quantity = 0
  revenue / NULLIF(quantity, 0)              AS unit_price_alt,
  try_multiply(CAST(units AS BIGINT), 1000)  AS milli_units,
  try_add(counter_a, counter_b)              AS total_counter
FROM silver.sales.orders;

#Arrays and maps

SELECT
  try_element_at(split(path, '/'), 3)        AS third_segment,
  try_element_at(attributes, 'campaign')     AS campaign
FROM bronze.web.events_raw;

#Fix patterns in PySpark

Most try_ functions are available in pyspark.sql.functions. Where your version lacks one, use F.expr:

from pyspark.sql import functions as F

df = (spark.table("bronze.sales.orders_raw")
      .withColumn("order_amount", F.expr("try_cast(order_amount_raw AS DECIMAL(18,2))"))
      .withColumn("unit_price", F.try_divide("revenue", "quantity"))
      .withColumn("event_ts", F.try_to_timestamp(F.col("event_time_raw"),
                                                 F.lit("yyyy-MM-dd HH:mm:ss"))))

Python UDFs aren't affected by ANSI mode in the same way. The errors come from Spark SQL expressions, including those generated by DataFrame methods like .cast("int").

#Quarantine instead of silently nulling

If a bad value means bad data, keep the row and flag it, rather than turning it into NULL and moving on:

CREATE OR REPLACE TABLE silver.sales.orders_rejected AS
SELECT *, current_timestamp() AS rejected_at
FROM bronze.sales.orders_raw
WHERE order_amount_raw IS NOT NULL
  AND try_cast(order_amount_raw AS DECIMAL(18,2)) IS NULL;

Load the good rows into silver and alert on the count in orders_rejected.

#Turning ANSI mode off (last resort)

You can restore the old behaviour per session or per cluster:

SET spark.sql.ansi.enabled = false;   -- clusters, serverless
SET ANSI_MODE = false;                -- SQL warehouse session

On a classic cluster, you can also set spark.sql.ansi.enabled false in Compute → (cluster) → Advanced options → Spark config, or in the job cluster definition.

Warning

Disabling ANSI mode brings back silent NULLs, wrapped-around integer overflow and truncated casts. Totals can be wrong without any error. Use it only as a short-term unblock for a Sev1, scoped to the failing job, with a ticket to fix the expressions.

Caution

Don't set spark.sql.ansi.enabled false in a shared cluster policy or as a workspace-wide default. Teams that already depend on ANSI errors to catch bad data would lose that protection without being told.

#Prevent it next time

• Before a runtime upgrade, run each production job once on a 17.x job cluster against a copy of yesterday's data, then look for the error classes above.
• Search your repos for CAST(, .cast(, /, element_at and to_timestamp in bronze-to-silver code. That's where malformed input lands.
• Use try_cast and friends only at the ingestion boundary, together with a rejected-rows table. In silver and gold, let ANSI errors fail loudly.
• Keep clusters, serverless and SQL warehouses on the same ANSI setting, so a query behaves the same way everywhere it runs.
• Add a data quality check, such as a Lakeflow expectation or a simple count query, on the number of rows that fail to cast, and alert on change.