Inconsistent results from a SQL Endpoint query

Hi spencer_sa ,

Thank you for reaching out to the Microsoft Fabric Community forum.

It is a known issues in some SQL Endpoint implementations like those in Databricks/Fabric or Synapse when You SELECT TOP (1) without an ORDER BY this yields non-deterministic results. You then re-reference the same table, either via JOIN or WHERE id = , causing the optimizer to potentially re-evaluate or parallelize the source table independently in multiple ways. This can result in inconsistent query plans, including multiple evaluations of the subquery, especially when dealing with large, wide Delta tables. When you reduce the number of columns, the engine may take a simpler plan and avoid these inconsistencies.

Please try the below query.

WITH selection AS (
SELECT TOP (1) id FROM TABLE ORDER BY id
)
SELECT * FROM TABLE T
INNER JOIN selection S
ON S.id = T.id;

Please try below things to fix the issue.

1. Materialize the CTE / Subquery, If possible, make the CTE a temp table or materialized view to ensure it’s evaluated once.

2. Try to break the query

SELECT TOP (1) id INTO #selection FROM TABLE;
SELECT * FROM TABLE T JOIN #selection S ON T.id = S.id;

3. Avoid SELECT * on Wide Tables, Select only necessary columns to simplify the plan.

4. Use SparkSQL over the Endpoint, As you have observed, the Spark engine respects the logical intent more consistently.

Please refer community thread.

Solved: Re: SQL enpoint sync issues - Microsoft Fabric Community

If this information is helpful, please “Accept it as a solution” and give a "kudos" to assist other community members in resolving similar issues more efficiently.
Thank you.