An engineer notices that a Lakehouse table of ticket-scan events, which suffers from the small-file problem after months of frequent streaming writes, is slow not only in Spark notebooks but also when analysts query it through the SQL analytics endpoint using plain T-SQL. Why does the same file-layout issue affect both engines?
Select an answer to reveal the explanation.
Short Explanation
A messy stockroom slows down every clerk who has to dig through it, not just the one who made the mess. The SQL analytics endpoint and Spark aren't separate stockrooms — they're two different people pulling from the exact same shelf of Delta Parquet files sitting in OneLake. So compacting those files with OPTIMIZE helps both engines at once, because the fix happens at the shared storage layer, not inside either engine.
Full Explanation
A Fabric Lakehouse table's data lives as Delta-formatted Parquet files in OneLake, and both the Spark engine and the SQL analytics endpoint read from that same underlying storage — there's no separate, independently-maintained copy for each engine — so a small-file problem is a storage-layer issue that raises scan cost for whichever engine touches the table, which is exactly why analysts see slowness through plain T-SQL even though the fragmentation was caused by Spark-side streaming writes. This means running OPTIMIZE compaction fixes the shared root cause and benefits both query paths from a single maintenance operation. The idea that the SQL endpoint reads a separate, unaffected copy is incorrect — it's precisely the shared-storage architecture that makes the slowdown show up in both places. Row-level security is an independent access-control feature; whether or not it's configured has no bearing on whether small files slow down a scan. And T-SQL queries against a Lakehouse table are executed by the SQL analytics endpoint's own query engine, not by silently launching a Spark job behind the scenes — the two engines are separate compute paths over the same stored files, not one wrapping the other. A caveat: because both engines share storage, a maintenance schedule for OPTIMIZE (and VACUUM) should be planned with all consuming engines in mind, not tuned only for whichever workload happened to notice the slowdown first. A concrete check: compare query duration for the same filter through both the SQL analytics endpoint and a Spark notebook, before and after running OPTIMIZE on the table.