Diagnose invalid casts and missing measurements

Understand the likely causes

• The source can contain a changed delimiter, header row, unit or timestamp convention. Spark 4.0 enables ANSI behavior by default, so malformed conversions can fail where an earlier configuration tolerated them. A tolerant parse can also hide invalidity if raw values and rejection categories are discarded.

Identify where interpretation first diverged

Compare the approved source schema and version with the actual producer contract. Check whether a numeric identifier was inferred as a number, whether CSV options changed field positions and whether timestamps carry an offset or a documented local zone. Do not use the final formatted report to infer the input type. DataFrame construction can be lazy, so an error appearing at count or write may originate in an earlier read or cast. Capture a minimal synthetic record that reproduces the interpretation without copying customer data or connecting to the configured source.

Preserve the raw value while testing conversion

The SQL fixture makes one valid decimal and one failed conversion visible. try_cast is used deliberately to inspect values; it is not permission to publish NULL as zero. Keep the raw field and distinguish truly absent input from invalid non-null content. Unsupported type-pair conversions can still have analysis restrictions, so inspect the actual error rather than assuming every exception becomes NULL. The query is non-runnable here and its expected values are logical results, not captured Spark output.

SELECT raw_kwh, try_cast(raw_kwh AS DECIMAL(10,3)) AS parsed_kwh
FROM VALUES ('2.500'),('sensor-offline') AS r(raw_kwh);

raw_kwh       | parsed_kwh
2.500         | 2.500
sensor-offline| NULL

Reconcile categories before recalculating totals

A source row should have an explicit outcome such as accepted, missing or invalid under the chosen policy. The second fixture demonstrates a classification stage after tolerant parsing. Retain the original batch and reading identity alongside that result, then compare source counts with accepted, rejected and intentionally excluded rows. SUM ignores NULL, so a plausible lower total can conceal a failed sensor population. Define whether incomplete days remain publishable and how corrected readings later revise them. Disabling ANSI globally merely to finish the job can change unrelated expressions and is not a contextual source repair.

SELECT raw_kwh, parsed_kwh, CASE
 WHEN raw_kwh IS NULL THEN 'missing'
 WHEN parsed_kwh IS NULL THEN 'invalid'
 ELSE 'accepted' END AS quality
FROM parsed_readings;

Repair the contract and retest the selected release

Review the exact source and destination Spark migration notes, observed ANSI and time-zone settings, and provider conversion behavior. Prefer a narrow producer or schema correction when the source violated its contract. If tolerant ingestion is intended, publish rejection accounting and repair ownership rather than silently accepting losses. Test zero, NULL, malformed text, numeric bounds and timestamp transitions in a separately authorized lab. This guide executes no Spark parser, file reader or configuration change; local validation checks syntax and exact synthetic expectations only.

Keep it from recurring

• Version schemas and units, keep raw-value rejection evidence, and include malformed and boundary fixtures in release checks. Record selected runtime defaults alongside observed overrides, with a documented owner for repaired-source replay.