Non-deterministic ROW_NUMBER() results across Unity Catalog environments

[Khasim_1]

We have a notebook that calculates the "mode" (most frequent value) of a customer_parent_name field using ROW_NUMBER() OVER (PARTITION BY ... ORDER BY count DESC). The exact same notebook code is deployed to two Unity Catalog-governed environments (Dev and Prod catalogs), reading from two source tables that we've confirmed have identical row counts and identical top-N frequency distributions. > > Despite this, the final "mode" result for certain records differs between Dev and Prod — i.e., the same DUNS number resolves to a different customer_parent_name depending on environment. > > What we've already checked: > - Row counts match exactly between environments > - Top 10 frequency distributions for customer_parent_name are identical > - Notebook code is verified identical (diffed line by line) > - We suspect ROW_NUMBER() without a tie-breaking secondary sort column is non-deterministic when counts are tied, and cluster-level partitioning/execution order (which can differ between Unity Catalog compute environments) causes different "winners" to be selected

1. Is it expected behavior for ROW_NUMBER() (without a full tie-breaker in ORDER BY) to produce different results across different Unity Catalog catalogs/clusters, even with identical underlying data?

2. Does Unity Catalog's governance layer (lineage tracking, access policies, etc.) have any influence on query execution plans or partition ordering that could contribute to this inconsistency?

3. What is the recommended best practice within Unity Catalog for ensuring deterministic aggregation results across environments — is a secondary ORDER BY tie-breaker sufficient, or should we also be enforcing REPARTITION/SORT WITHIN PARTITIONS before the window function? 

Any insights from others who've run into non-deterministic ROW_NUMBER() behavior in multi-environment Unity Catalog setups would be greatly appreciated.

[ShamenParis]

Hi @Khasim_1 ,

• I would say this is completely expected behavior. In Spark SQL, when ROW_NUMBER() encounters a tie in the ORDER BY clause, the assignment order is entirely non-deterministic. Because Spark is a distributed engine, the "winner" of a tie is dictated by underlying file layouts, shuffle mechanics, and physical task execution timing. Since Dev and Prod operate on different compute instances and storage states, those ties will naturally resolve differently across environments.

• As per my understanding, Unity Catalog does not influence this. It operates strictly as a metadata, lineage, and governance layer. It does not interact with the Catalyst optimizer, nor does it alter physical execution plans, shuffle partitions, or how the engine processes data.

• Based on my experience, the recommended best practice is simply adding a deterministic secondary tie-breaker. Update your window function to include a secondary column that guarantees uniqueness (e.g., ROW_NUMBER() OVER (PARTITION BY duns_number ORDER BY count DESC, customer_parent_name ASC)). I would strongly advise against using REPARTITION or SORT WITHIN PARTITIONS before the window function. The WindowExec physical node re-sorts the data based on the window's internal ORDER BY clause anyway, meaning any upstream sorting is ignored if the window's own sort remains non-deterministic.

[data_pulse]

Agree with the explanation from @ShamenParis. Databricks documents this explicitly too for ROW_NUMBER(): If the ordering is not unique, the result is non-deterministic.

So if two customer_parent_name values have the same cnt, either one can receive row_number = 1, and that can differ across runs or environments.

In practice, better to use a tie-breaker that reflects the business rule rather than an arbitrary alphabetical sort.

I would also avoid using REPARTITION / SORT WITHIN PARTITIONS as a correctness fix here because:

• REPARTITION only changes how rows are distributed across Spark partitions. It does not define which tied row should come first.
• SORT WITHIN PARTITIONS sorts rows inside each Spark partition, but the window operator still has to enforce its own required partitioning and ordering.

This looks more like deterministic ordering issue rather than a UC issue. UC can influence data visibility through access controls, row filters, and masks etc, but it does not provide a deterministic tie-breaker for ROW_NUMBER() when the window ORDER BY is not unique.

[ivanvyd]

@Khasim_1 another option for calculating the most frequent name is mode(customer_parent_name, true). The second argument requests deterministic tie handling and is supported in Databricks SQL and DBR 14.1+.

SELECT
    duns_number,
    mode(customer_parent_name, true) AS customer_parent_name
FROM your_source_table
GROUP BY duns_number;

Use your actual table and key names, keeping the same filters as your existing calculation. Run this against the original rows, before the frequency aggregation. Otherwise, each name would count once rather than by its original frequency.

Two details to check before switching: mode ignores nulls, and the documentation warns that some string collations, including UTF8_LCASE, can still produce nondeterministic results with true. I would keep the explicit window ordering if tied names need a particular business rule.

[Islam_hoti · Accepted solution]

Hi,

Your diagnosis is correct, and the fix is simpler than you are expecting.

On question one, yes, this is expected and it is not a Databricks or Unity Catalog behaviour. ROW_NUMBER with an ORDER BY that does not define a total order is non deterministic by definition. When two rows tie on count, the one that gets number 1 is whichever the engine happens to encounter first within the partition, and that depends on physical factors: how many files the table is stored in, file sizes, task count, shuffle partition count, adaptive query execution decisions, Photon versus non Photon, runtime version. Two tables with identical row counts and identical frequency distributions can have completely different physical layouts, so there is no contradiction between your checks passing and the results differing. The same query on the same table can also return different winners between two runs on differently sized clusters.

On question two, no. Lineage capture and access policies do not influence physical planning or partition ordering. The one governance feature that does touch the plan is row filters and column masks, since those inject predicates, but that changes what rows are visible rather than making ordering non deterministic. The real differences between dev and prod here are physical, not governance related.

On question three, a full tie breaker in the ORDER BY is sufficient, and it is the right fix. The requirement is that the ordering be a total order within each partition, meaning no two rows can compare equal. So ORDER BY cnt DESC, customer_parent_name ASC, and if that can still tie, keep adding columns until the combination is unique. Once the ordering is total, the result is deterministic regardless of cluster size, runtime, or file layout.

REPARTITION and SORT WITHIN PARTITIONS will not help and I would not add them. They change the physical arrangement but do not make an ambiguous ordering unambiguous, so you would be substituting one accidental outcome for another, and paying for a shuffle to do it.

One suggestion beyond the fix. A tie in this calculation means your data genuinely does not have a single most frequent parent name for that DUNS number, and a tie breaker resolves it by picking alphabetically rather than by picking correctly. It may be worth flagging those records rather than silently resolving them, since the environments disagreeing is arguably the system telling you something real about the data.