Apache Spark 4.0 came out last year, and it's now arriving in managed platforms' runtimes. If you're about to move your workloads from a Spark 3.x runtime, there are a few SQL changes you need to know about. One of them can break existing jobs, and bordereaux pipelines are particularly exposed to it. The rest are good additions, especially for people who think in SQL first.
Here are the four SQL changes I think matter most, with delegated authority examples you can try in a Spark 4 session.
1. ANSI mode is now on by default
This is the one that can break things, so it comes first.
In Spark 3.x, spark.sql.ansi.enabled was false by default, and Spark was forgiving in ways that hid bad data. Having mapped dozens of different bordereaux formats over the years, I've seen every variation of the values that turn up in a premium column:
1: -- Spark 3.x (non-ANSI) 2: SELECT CAST('1,250.00' AS DECIMAL(18,2)); -- NULL (thousands separator) 3: SELECT CAST('N/A' AS DECIMAL(18,2)); -- NULL 4: SELECT CAST('31/02/2025' AS DATE); -- NULL 5: SELECT 250.00 / 0; -- NULL (commission % with zero premium)
In Spark 4.0 with ANSI mode on, all of these raise errors. As a former SQL Server developer I think that's correct. SQL Server has always failed on these, and quietly turning a premium of "1,250.00" into NULL meant written premium was understated with nobody noticing. But a pipeline that "worked" on 3.x can start failing on 4.0 the first time a coverholder sends a bad value.
How to handle it:
1: -- Clean known formats, then use TRY_ functions where bad values are expected 2: SELECT certificate_ref, 3: TRY_CAST(REPLACE(gross_premium_text, ',', '') AS DECIMAL(18,2)) AS gross_premium, 4: CAST(TRY_TO_TIMESTAMP(inception_date_text, 'dd/MM/yyyy') AS DATE) AS inception_date 5: FROM bronze.premium_bdx_raw; 6: 7: SELECT umr, section_no, 8: TRY_DIVIDE(commission_amount, gross_premium) * 100 AS commission_pct 9: FROM silver.premium_transaction; 10: 11: -- Find the offending rows, by coverholder, before migrating 12: SELECT coverholder_id, gross_premium_text, COUNT(*) AS rows_affected 13: FROM bronze.premium_bdx_raw 14: WHERE gross_premium_text IS NOT NULL 15: AND TRY_CAST(REPLACE(gross_premium_text, ',', '') AS DECIMAL(18,2)) IS NULL 16: GROUP BY coverholder_id, gross_premium_text 17: ORDER BY rows_affected DESC;
That last query is worth turning into a standard data quality report that goes back to coverholders. You can set spark.sql.ansi.enabled = false to get the old behaviour back, and that's a reasonable short-term fix to unblock a migration. I'd treat it as temporary, though. The errors are pointing at real data problems.
2. The VARIANT type for semi-structured data
Before 4.0, JSON in Spark meant either keeping it as a STRING and parsing it with get_json_object/from_json on every query, or defining a fixed STRUCT schema that broke when the source added a field. Snowflake users have had VARIANT for years. Now Spark has it too.
It's a good fit for bordereaux submitted through APIs or portals, where each coverholder's payload carries the standard fields plus their own extras:
1: CREATE TABLE bronze.bdx_submission ( 2: submission_id BIGINT, 3: coverholder_id STRING, 4: received_at TIMESTAMP, 5: payload VARIANT 6: ) USING DELTA; 7: 8: INSERT INTO bronze.bdx_submission 9: SELECT submission_id, coverholder_id, received_at, PARSE_JSON(raw_json) 10: FROM landing.bdx_submission_raw; 11: 12: -- Extract with a path and a target type 13: SELECT submission_id, 14: VARIANT_GET(payload, '$.contract.umr', 'STRING') AS umr, 15: VARIANT_GET(payload, '$.contract.section', 'STRING') AS section_no, 16: VARIANT_GET(payload, '$.risk.certificate_ref', 'STRING') AS certificate_ref, 17: VARIANT_GET(payload, '$.risk.sum_insured', 'DECIMAL(18,2)') AS sum_insured, 18: VARIANT_GET(payload, '$.premium.gross', 'DECIMAL(18,2)') AS gross_premium, 19: TRY_VARIANT_GET(payload, '$.risk.flood_zone', 'STRING') AS flood_zone -- only some coverholders send it 20: FROM bronze.bdx_submission; 21: 22: -- See what a payload looks like 23: SELECT SCHEMA_OF_VARIANT(payload) FROM bronze.bdx_submission LIMIT 1;
VARIANT is stored in a binary format, so it's much faster to query than re-parsing JSON strings, and it handles schema changes without DDL. Note that table formats have to support it too. Delta Lake added VARIANT support alongside Spark 4.0, so check the Delta version on your platform before relying on it in shared tables.
3. SQL pipe syntax
This one divides opinion, but I've grown to like it. Pipe syntax lets you write a query in the order it runs, using the |> operator:
1: FROM silver.premium_transaction 2: |> WHERE year_of_account = 2025 3: |> AGGREGATE SUM(gross_premium_gbp) AS gwp_gbp, COUNT(*) AS transactions 4: GROUP BY coverholder_id 5: |> WHERE gwp_gbp > 100000 6: |> ORDER BY gwp_gbp DESC 7: |> LIMIT 10;
Compare that with the usual SELECT ... FROM ... WHERE ... GROUP BY ... HAVING ... ORDER BY, where the order you write the clauses isn't the order they're evaluated. With pipes:
- There's no
HAVINGto remember. It's just anotherWHEREafter the aggregation. - You can add steps without wrapping everything in another subquery or CTE.
- It reads like a DataFrame chain, which helps when the same team writes both SQL and PySpark.
A longer example: GPI utilisation by contract section, with a join and derived columns:
1: FROM silver.premium_transaction AS p 2: |> JOIN silver.contract_section AS cs USING (umr, section_no) 3: |> WHERE cs.year_of_account = 2025 4: |> AGGREGATE SUM(p.gross_premium_gbp) AS gwp_gbp, MAX(cs.gpi_limit_gbp) AS gpi_limit_gbp 5: GROUP BY umr, section_no, cs.coverholder_id 6: |> EXTEND ROUND(gwp_gbp / gpi_limit_gbp * 100, 1) AS gpi_utilisation_pct 7: |> WHERE gpi_utilisation_pct >= 80 8: |> ORDER BY gpi_utilisation_pct DESC;
It's fully optional and mixes freely with regular SQL. I wouldn't rewrite existing code, but for new exploratory queries it's become my habit.
4. String collations
Another feature SQL Server developers have always had and Spark lacked: collations. Coverholder and broker names arrive in every combination of upper and lower case, depending on who keyed them. You can now compare and group strings case-insensitively without wrapping everything in LOWER():
1: SELECT 'Acme Underwriting Ltd' = 'ACME UNDERWRITING LTD' COLLATE UTF8_LCASE; -- true 2: 3: CREATE TABLE silver.broker ( 4: broker_code STRING, 5: broker_name STRING COLLATE UTF8_LCASE, 6: broker_group STRING COLLATE UNICODE_CI 7: ) USING DELTA; 8: 9: SELECT broker_name, COUNT(*) 10: FROM silver.broker 11: GROUP BY broker_name; -- 'Acme Re Brokers' and 'ACME RE BROKERS' group together
Like VARIANT, collations in stored tables depend on table format support, so test on your platform first. Expressions using COLLATE in a query work regardless. And collations only fix case and accents. "Acme Underwriting Ltd" vs "Acme Underwriting Limited" still needs proper reference data matching on the coverholder PIN or broker code.
Other things worth a look
- SQL scripting:
BEGIN ... ENDblocks with variables,IF,WHILEand loops. It's familiar territory for anyone who wrote T-SQL stored procedures. It started as a preview in 4.0, so check its status on your runtime. - Python data source API: write custom readers and writers in pure Python, which is useful for reading from bordereaux platform APIs or niche file formats.
- Spark Connect improvements: a thin client architecture that more platforms now build on.
A migration checklist
- Run your existing jobs on a Spark 4 runtime in a test environment with ANSI on, against several months of real bordereaux. Collect every failure.
- Fix casts and divisions with
TRY_functions where bad data is expected, and push data quality issues back to coverholders where it isn't. - Check third-party libraries and connectors for Spark 4 / Scala 2.13 builds. Spark 4.0 dropped Scala 2.12 and Java 8/11, and requires Java 17 or later.
- Only then start using VARIANT, collations and pipe syntax in new code.
Spark 4.0 brings Spark SQL closer to what SQL developers expect from a database: strict about bad data, flexible with semi-structured data, and more pleasant to write. For anyone processing bordereaux, the strictness alone is worth the upgrade.
