Apache Spark / Basic: typed relational foundations / Lesson 3

Column expressions, NULL and ANSI behavior

Make missing values and invalid measurements explicit instead of hiding them inside casts or Python control flow.

About 4 min readContent reviewed 2026-10-28

On this page

Lesson goals

Use three-valued logic and null-safe equality deliberately.
Explain Spark 4 ANSI failures and tolerant conversion with rejection accounting.

A Column is a distributed expression

In PySpark, a Column describes how to compute a value for rows during execution. It is not a Python scalar available immediately on the driver. Use Spark functions and column operators to build that expression. Python if cannot decide independently for every distributed row, and Python and or or do not combine Column predicates as intended. Parenthesized expressions with the supported bitwise operators construct logical conditions. SQL expressions provide another clear route. Keep units and null behavior visible in either API; syntactically valid expressions can still turn an unknown meter reading into a misleading accepted zero.

Unknown is different from false

Ordinary equality with NULL yields unknown, and a filter retains true results only. Spark’s null-safe equality operator <=> treats two NULL values as equal and one NULL versus a known value as unequal. Use it when comparing optional values for a deliberate business reason, not as a replacement for every join key comparison. A missing site identifier should usually be rejected rather than grouped into one apparently valid site. The first query makes these logic rules visible with local expressions and returns one reasoned result row. No Spark SQL interpreter is invoked.

sql Illustrative technical example; logical results are reasoned, not engine-tested.

SELECT
 CASE WHEN NULL = NULL THEN 'true' ELSE 'not true' END AS ordinary,
 NULL <=> NULL AS both_missing,
 7 <=> NULL AS one_missing;

Expected output

ordinary | both_missing | one_missing
not true | true         | false

ordinary is not true because the comparison is unknown; the null-safe results are true and false.

Spark 4 changes the default failure boundary

Spark 4.0 enables spark.sql.ansi.enabled by default. Invalid casts and arithmetic overflow can therefore raise errors that older pipelines expected to become NULL or otherwise tolerated. Treat the change as a data-contract review rather than disabling ANSI automatically. A malformed reading may indicate a producer defect, a header read as data or a changed unit. Inspect the exact error class, expression and source value through approved redacted evidence. Store configuration assumptions with the job so a managed platform override does not silently change the interpretation of a successful result.

Tolerant conversion must leave a diagnostic trail

try_cast can return NULL for a failed supported conversion instead of raising the ordinary value-conversion error. The example distinguishes a valid decimal from a malformed energy string. The returned NULL is not itself a rejection ledger, because a genuinely absent input can also produce NULL. Preserve the raw field and a reason flag that distinguishes missing from invalid. Unsupported type conversions can still have their own analysis restrictions, so try_cast is not a universal exception suppressor. Reconcile rows classified as invalid before publishing energy totals.

sql Illustrative technical example; logical results are reasoned, not engine-tested.

SELECT raw_kwh, try_cast(raw_kwh AS DECIMAL(10,3)) AS parsed_kwh
FROM VALUES ('1.250'), ('offline') AS incoming(raw_kwh);

Expected output

raw_kwh | parsed_kwh
1.250   | 1.250
offline | NULL

The reasoned parsed values are 1.250 and NULL. The source string offline must remain available for diagnosis.

Defaulting is a domain decision

coalesce chooses the first non-NULL expression. Replacing every missing kwh with zero changes “unknown energy” into “known no production,” which can distort a solar performance report. A sensor outage may need an incomplete-day marker rather than a numeric substitute. CASE or when expressions can classify known, missing and invalid records before aggregation. Use decimal-compatible branches to avoid accidental type widening or implicit conversion. Keep the classification vocabulary stable across consumers and test boundary values, including zero, negative readings and values near declared precision limits. All expected results remain logical examples rather than engine measurements.

Common errors and fixes

CAST_INVALID_INPUT

Why it happens: An ordinary cast receives content incompatible with the requested type under the active policy.
How to fix it: Inspect and repair the source or use a deliberate tolerant parse plus rejection classification.
Prevent it next time: Test malformed and missing inputs separately across upgrades.

ARITHMETIC_OVERFLOW

Why it happens: The computed value exceeds the chosen representation under ANSI behavior.
How to fix it: Review precision, scale and the legitimate business bound.
Prevent it next time: Exercise maximum values and aggregation growth before publishing.

Python boolean evaluation rejects a Column

Why it happens: Driver control flow was applied to a distributed expression.
How to fix it: Build parenthesized Spark predicates or a reviewed SQL expression.
Prevent it next time: Review Column expressions independently from scalar Python code.

Check your understanding

1. Is coalesce(kwh,0) a harmless formatting choice for an outage report?

Show worked solution

No. It changes the represented fact from unknown production to known zero production. Preserve missingness and report completeness separately unless a documented domain rule authorizes substitution. Test the resulting aggregate denominator as well as its total.

2. What extra evidence is needed when try_cast returns NULL?