_This article was originally published on_ [_hashnode.dev_](https://wtfzambo.hashnode.dev/the-importance-of-not-using-nulls-for-merge-keys) _on Jul 2, 2025._

---

Last week I was working on a Spark pipeline that was running slowly, and I discovered that a specific task with significant skew was the cause.

Googling for the problem didn’t return any meaningful result, so I had to figure it out myself.

Here’s what I found and how I fixed it.

## **Understanding SCD2 with Delta Lake**

The pipeline was an [SCD2](https://en.wikipedia.org/wiki/Slowly_changing_dimension) incremental build, something that every data engineer has to deal with sooner or later.

Frameworks like [Delta](https://delta.io/) allow the use of this strategy inside a data lake, thanks to a simple API that grants update and merge capabilities over otherwise immutable data.

Merging a sequence of data changes to a base table is relatively straightforward:

1.  Identify the type of change (`INSERT`, `UPDATE`, `DELETE`) for each record in the sequence
2.  Prepare it to be merged
3.  Run the merge operation

The second step is crucial because `UPDATE`s **are treated specially**. An `UPDATE` record must be used twice during the merge:

1.  To mark the old record in the base table as expired (setting `is_current = false` and an end date).
2.  To insert the updated values as a new record.

Delta’s `merge` operation begins with setting a **merge condition**, usually based on a primary key (e.g. `base.primary_key = updates.primary_key`). Then, it has two main actions:

*   `whenMatchedUpdate`: for records that meet the merge condition
*   `whenNotMatchedInsert`: for records that _don’t_ meet the merge condition

We need to make sure that records of type `UPDATE` fall under both of them. In other words, the record must both match and not match the merge condition at once.

To obtain this result, for these records we create duplicates that can never satisfy the `whenMatched` condition. Usually, this is done by setting `merge_key = null`, and then we append these records to the rest of the data we’re merging. This is how it’s recommended in [Delta’s official documentation](https://docs.delta.io/latest/delta-update.html#slowly-changing-data-scd-type-2-operation-into-delta-tables).

## **Null merge keys create skewed partitions**

By setting `merge_key = null`, we’re artificially creating skew: since a Delta merge is effectively a `JOIN`, Spark will shuffle data based on the join/merge condition (unless [broadcasted](https://spark.apache.org/docs/latest/api/java/org/apache/spark/broadcast/Broadcast.html)), and will send all records with `merge_key = null` to a single partition, using only a single core to perform an operation that could otherwise be parallelized.

![Execution skew caused by setting merge_key = null in a Delta merge operation. ](https://janea-minio.apps-us2.janeasystems.com/public/fd64b289_7e55_4dc9_a8a8_c8d424c7707d_63fc6f4559.png)

The above screenshot is taken from the build I was working on: the long green bar represents the task where all the records with the `null` merge key ended up being, with the length representing its duration.

It took way longer to execute, and it processed an amount of records an order of magnitude larger than any other task in that job.

![the number of records processed after setting merge_key = null in a Delta ](https://janea-minio.apps-us2.janeasystems.com/public/33a3c51a_9d14_433a_87ce_97a817f3cf6f_411881319d.png)

## **Setting the merge key to a valid value**

Fortunately, the fix is simple: we need to set the `merge_key` for those records to something that isn’t `null`, can never match any of the primary keys, and is evenly distributed. If the primary key is a string, we can set the `merge_key` like this:

```plaintext
df_inserts = df.withColumn(
    "merge_key", F.concat(F.lit("__INSERT__"), F.col("id"))
)
```

This keeps the original distribution of data based on the primary key, while also ensuring that the `merge_key` can never match the original pk.

However, if the primary key is a number, we can’t use the method above. That’s because during merge, the engine casts the `merge_key` to an `integer`, which means it will go back to being `null`, since that’s the result of casting an alphanumeric string to a number.

In this case, we need to set our `merge_key` to be a number, always making sure to never match the original pk. This can be done by setting it in the negatives (assuming the pk is always a positive integer). Here’s the example code:

```plaintext
df_inserts = df.withColumn(
    "merge_key", -100_000_000 - F.col("id")
)
```

Like the above case, this keeps the same distribution of data, and also satisfies all the other requirements.

---

Applying the fix to the pipeline removed the skew entirely, as the data could now be properly distributed across all executors. The total run time was cut in half.

In the end, this is not different than salting join keys in a normal Spark pipeline. The source of confusion comes from using Delta API, which abstracts Spark joins and makes it a little less obvious to identify where the problem hides.

Avoiding nulls in merge keys can dramatically improve Spark pipeline performance. Learn how setting null merge keys causes partition skew.

How Avoiding Nulls Can Reduce Costs in Spark Pipelines

This article was originally published on [hashnode.dev](https://wtfzambo.hashnode.dev/the-only-real-benchmark-is-the-people) on Aug 12, 2025.

**August 7, 2025**  
GPT-5 is released to the public. OpenAI _“smartest, fastest, most useful model yet.”_

**August 11, 2025**

The first comments start rolling in.

> _I am actually pretty disappointed. Altman's claims were about a revolutionary technology and this model is not. It is a cheaper version of GPT4 with some improvements in specific tasks. IMHO I think that the conversational side of the model is worse than GPT4. \[..\] It is far from the intelligence level that OpenAI marketed in the last few months.”_
> 
> _“I have spent the weekend with GPT 5 and I'm done._  
> _This is weird - because it's meant to be so much better.”_
> 
> _“With GPT 5 \[..\] prompts which used to get you best possible outcomes with previous established models may not give you same response.”_

I picked the first comments that came up by querying “gpt 5” on LinkedIn two minutes before writing this, but feel free to [try for yourself](https://www.linkedin.com/search/results/all/?keywords=gpt%205). I bet $10 that you’ll get similar results.

Yet, benchmarks tell a completely different story (and Edward Tufte would have a stroke if he saw this chart).

![444e1ccc-3d0f-46ab-b8e8-7c23d6648768.webp](https://janea-minio.apps-us2.janeasystems.com/public/444e1ccc_3d0f_46ab_b8e8_7c23d6648768_ec93b4e860.webp)

---

So who’s right, the people or the benchmarks? Well, yes.

The thing is, benchmarks are not an objective measure of usefulness. Rather, they measure how well the LLM performs **the specific benchmark task(s)**.

But people are not using ChatGPT to solve benchmarks; instead, they use it to talk about an unbelievably large range of stuff, going from coding to therapy to… \*_checks notes_\* [getting married to the AI](https://www.reddit.com/r/MyBoyfriendIsAI/comments/1mmxlpz/i_married_my_ai_on_friday_heres_the_whole_story/) itself…?

Anyway.

I can already hear the “_experts”_ screaming at the top of their lungs: _“akSHuALLy, there are many different benchmarks that LLMs get tested on! There’s no better way to measure them!”_

Ok Einstein, now hear me out: assuming you’re not a cheeto-fingered basement dweller, do you shower and dress in your best clothes when you go on a first date? Yes?

So what if… **what if** these benchmarks are tailored in a way that \*_gasp\*_ makes the investors and customers go like the gossiping girls meme, when a new model is released?

How queer that **for-profit** organizations dress up their products in the best possible clothes to generate as much cash flow as possible. Peculiar indeed.

---

But if not benchmarks, then what?

This is one of the cases where **people's** consensus is a reliable indicator of performance.

In other words, **the best LLM for a given area is the one that users stick to after the release hype of a new model fades.**

The proompters, the vibecoders, the LinkedIn lunatics, the software engineers… they all collectively gravitate towards the LLM that gives them the best bang for their buck. Because in the end, it’s the user experience that matters, not some cooked-up benchmark.

The good news is that this consensus is very easily tracked and measured: many AI aggregators have [leaderboards](https://openrouter.ai/rankings) where you can see the distribution of AI usage over time and even narrow it to specific areas.

![24c82256-6185-4239-9b5a-f000ea5d4f01.webp](https://janea-minio.apps-us2.janeasystems.com/public/24c82256_6185_4239_9b5a_f000ea5d4f01_3a2a4770b9.webp)

It’s also much easier to read: _“GPT-4.1 has a 3% market share in the Marketing area”_ is a much better indicator of performance than _“it scores 52% on Aider Polyglot”_.

And if you don’t trust me, [trust him](https://youtu.be/DWkHN0VaFJA?si=nw8vHam_CIVP-fKq&t=2081).

A senior engineer breaks down GPT-5 release, cutting through hype to reveal how it performs, where it fails, and what are the real LLM benchmarks.

Engineers' Take on GPT-5: The Real LLM Benchmark

Engineers' Take on GPT-5: The Only Real LLM Benchmark

small_Untitled design.png

thumbnail_Untitled design.png

Untitled design.png

Federico

Choosing the right filesystem and architecture for historical tracking and ML applications.

Architecture Considerations

Helping the team allocate resources for the solution’s deployment and ongoing maintenance.

Workforce Limitations

A scalable data lake capable of processing multiple streams of historical data in parallel.

Robust Data Pipelines

A structured data lake facilitating long-term storage and efficient data retrieval.

Flexible Data Storage

A system designed for machine learning integration, enabling predictive modeling.

Built-in Support for Machine Learning

Horizontal scaling ensures future data growth while maintaining cost efficiency, fault tolerance, and optimal performance.

Scalability

Strict access controls, encrypted credential storage, and automated CI/CD pipelines guarantee data integrity and protection.

Enhanced Security

How to future-proof historical data for AI: scalable ETL pipelines, Delta Lake SCD2, Azure Synapse performance, and Key Vault security for a fintech platform [case study]

AI-Ready Data Lake For A Fintech Innovator

Borrower behavior is unpredictable, and the system must handle a wide variety of exceptions.

Edge cases

Many critical steps live in the day-to-day judgment of collectors.

SME expertise

LLM-based systems can vary in output while policies and process rules need reliable outcomes.

Non-determinism

Purpose-built agents supporting 15 to 50k cases a day, depending on the context.

Role-Specific Assistants

Shared orchestration and composable components for adding new assistants across channels without rebuilding the system.

Extensible Architecture

Improved completion of real-world workflows with tight feedback loops from SMEs and edge-case driven design.

Workflow Completion

A modular AI agent factory for finance that scales role-specific assistants across delinquency workflows, supporting between 15 to 50k cases a day.

AI Agent Factory for Finance

Senior Data Engineer

large_Janea Systems OG Image No Text.png

medium_Janea Systems OG Image No Text.png

small_Janea Systems OG Image No Text.png

thumbnail_Janea Systems OG Image No Text.png

Janea Systems OG Image No Text.png

Janea Systems is a software engineering consultancy, serving the enterprises that drive global innovation for over two decades. We help our clients build and optimize technologies used by millions of developers and billions of consumers. 

Projects

NDA

NDA

Related Blogs

Let's talk about your project

Let's talk about your project