The Hidden Danger of Non-Deterministic SQL

Why Your Queries May Return Different Results Every Time — And Why It Matters

The Hidden Danger of Non-Deterministic SQL

Why Your Queries May Return Different Results Every Time — And Why It Matters

Recently, a data team spent several days investigating what appeared to be a serious defect.

Their validation tests kept failing. The same query produced different results in different runs.

After careful analysis, they uncovered the real root cause:

SELECT  
    customer_id,  
    region,  
    ROW_NUMBER() OVER (PARTITION BY region) AS rn  
FROM customers

The migration was correct.

The SQL was broken.

Without an ORDER BY, ROW_NUMBER() assigns numbers arbitrarily. Different engines. Different execution plans. Different runs. Different answers.

The query worked — until it didn’t.

And this is more common than most teams realize.

What Makes SQL Non-Deterministic?

A query is non-deterministic when it can produce different results from the same input data.

Let’s look at the most common patterns.

1️⃣ Window Functions Without `ORDER BY`

-- Non-deterministic  
SELECT  
    ROW_NUMBER() OVER (PARTITION BY department) AS rn,  
    employee_name  
FROM employees

Which row gets rn = 1?

The database decides.

The same applies to:

FIRST_VALUE
LAST_VALUE
LAG
LEAD
RANK
DENSE_RANK

Without an ORDER BY, the engine is free to choose any physical order.

Different query plan = different result.

2️⃣ Window Functions With Non-Unique `ORDER BY`

Even with an ORDER BY, you may still be unsafe:

SELECT  
    ROW_NUMBER() OVER (  
        PARTITION BY department  
        ORDER BY hire_date  
    ) AS rn,  
    employee_name  
FROM employees

What if two employees share the same hire_date?

Who becomes rn = 1?

Again: arbitrary choice.

Determinism requires uniqueness within each partition.

3️⃣ `LIMIT` Without `ORDER BY`

SELECT * FROM orders LIMIT 10

Which 10 rows?

Whatever the engine finds first.

Correct version:

SELECT * FROM orders  
ORDER BY order_id  
LIMIT 10

4️⃣ Volatile Functions

SELECT  
    customer_id,  
    RANDOM() AS random_value,  
    NOW() AS execution_time,  
    UUID() AS unique_id  
FROM customers

These are intentionally non-deterministic.

But if used in transformations or tests, they break reproducibility.

Why This Actually Matters

Non-determinism is not just a theoretical purity issue.

It breaks real systems.

1️⃣ Migration & Regression Testing

When migrating platforms:

Oracle Results  →  Compare  ←  Snowflake Results

If results differ:

Is it a migration bug?
Or non-deterministic SQL noise?

You cannot tell.

Teams waste days chasing phantom defects while real issues hide in the noise.

2️⃣ Reproducible Analytics

Imagine your quarterly report:

Monday: $10.2M
Tuesday (same data): $10.4M
Wednesday: $10.1M

If ranking or deduplication logic is non-deterministic, your numbers drift randomly.

Explaining that to a CFO is not fun.

3️⃣ Incremental Pipelines & Deduplication

Very common pattern:

WITH deduplicated AS (  
    SELECT *,  
        ROW_NUMBER() OVER (  
            PARTITION BY customer_id  
            ORDER BY updated_at  
        ) AS rn  
    FROM raw_customers  
)  
SELECT * FROM deduplicated WHERE rn = 1

If updated_at is not unique, today’s winner might be tomorrow’s loser.

Silent data changes.

Downstream corruption.

No errors thrown.

The Fix: Deterministic Tie-Breakers

The solution is simple:

Make your**ORDER BY**unique within each partition.

❌ Before (Non-Deterministic)

ROW_NUMBER() OVER (  
    PARTITION BY department  
    ORDER BY hire_date  
)

✅ After (Deterministic)

ROW_NUMBER() OVER (  
    PARTITION BY department  
    ORDER BY hire_date, employee_id  
)

employee_id acts as a tie-breaker.

Now:

Same input → same output → every time.

Common Tie-Breaker Columns

Beyond Fixing: Monitoring Determinism

Even after adding a tie-breaker, you may want to know:

When would ties have existed?
Are data patterns violating assumptions?
Is dedup logic under stress?

You can add DQ monitoring columns.

SELECT  
    customer_id,  
    region,  
    created_at,ROW_NUMBER() OVER (  
        PARTITION BY region  
        ORDER BY created_at, customer_id  
    ) AS rn,  
    COUNT(*) OVER (  
        PARTITION BY region, created_at  
    ) AS _dq_tie_count,  
    CASE  
        WHEN COUNT(*) OVER (PARTITION BY region, created_at) > 1  
        THEN 'TIE_EXISTS'  
        ELSE 'NO_TIE'  
    END AS _dq_determinism_status  
FROM customers

Now you can:

Investigate tie scenarios
Monitor them over time
Detect data drift
Prevent silent instability

This is where MDDE thinking starts to matter.

MDDE Perspective: Determinism Is Architecture

In Metadata-Driven Data Engineering (MDDE), SQL is not just code.

It is executable architecture.

Determinism must therefore be:

Detectable (static analysis)
Enforced (rules)
Observable (DQ signals)

In MDDE Lite , we include a determinism checker:

from mdde_lite.determinism import check_determinism  
issues = check_determinism(sql)

It detects:

This shifts determinism from “developer discipline” to architectural guarantee.

Best Practices Summary

1️⃣ Always Include `ORDER BY` in Window Functions

ROW_NUMBER() OVER (PARTITION BY x ORDER BY y, z)

2️⃣ Ensure `ORDER BY` Is Unique

Add a primary or surrogate key as final element.

3️⃣ Never Use `LIMIT` Without `ORDER BY`

4️⃣ Parameterize Time-Dependent Filters

WHERE created_at > @report_date - INTERVAL '7 days'

Not:

WHERE created_at > NOW() - INTERVAL '7 days'

5️⃣ Monitor Tie Scenarios in Critical Logic

If it affects financial reporting, deduplication, or incremental loads — monitor it.

Final Thought

SQL should behave like a mathematical function:

Same input → Same output

If it doesn’t, it’s not just a query problem.

It’s an architectural flaw.

And architectural flaws scale.

The Hidden Danger of Non-Deterministic SQL