Spark 4.0 for SQL Developers: ANSI Mode, VARIANT, Pipe Syntax and Collations

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 HAVING to remember. It's just another WHERE after 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 ... END blocks with variables, IF, WHILE and 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

1. 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.
2. Fix casts and divisions with TRY_ functions where bad data is expected, and push data quality issues back to coverholders where it isn't.
3. 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.
4. 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.