AI-generated content. This lesson was produced from model knowledge plus the cited public sources; its claims have not yet passed Fabric Codex human verification. Verify limits and feature support against current Microsoft documentation before relying on them.

From SQL text to distributed execution: Catalyst#

Every Spark SQL statement — whether it arrives via a %%sql cell, spark.sql(), or the DataFrame API — passes through the Catalyst optimizer, Spark's extensible query planner. The pipeline, at architect altitude:

  1. Parse — the SQL text becomes an unresolved logical plan: a tree of relational operators whose table and column references are not yet bound to anything real.
  2. Analyze — Catalyst resolves names against the catalog (in Fabric, the metastore behind your lakehouse), checks types, and produces a resolved logical plan. This is the stage where a wrong default lakehouse surfaces as "table not found": the notebook's pinned default lakehouse determines which metastore unqualified table names resolve against, and a lakehouse in a different workspace from the default won't resolve [S1].
  3. Optimize — rule-based rewrites transform the logical plan: predicate pushdown (filters move as close to the scan as possible), column pruning (unused columns are dropped from reads), constant folding, join reordering informed by statistics.
  4. Plan physically — Catalyst generates candidate physical plans (e.g., broadcast hash join vs. sort-merge join) and selects one using cost estimates. The chosen plan compiles down to the distributed tasks executors actually run.

You can inspect all of this with EXPLAIN in SQL or df.explain() in Python — a habit worth building before reaching for tuning knobs.

Adaptive Query Execution#

Static plans are only as good as the statistics available at planning time, which for fresh or skewed data can be badly wrong. Adaptive Query Execution (AQE) re-optimizes at runtime: at each shuffle boundary Spark observes the actual sizes of the data produced and can revise the rest of the plan — coalescing an excessive number of small shuffle partitions into fewer, right-sized ones; switching a sort-merge join to a broadcast join when one side turns out to be small; and splitting skewed partitions so one giant key doesn't stall a stage. In Fabric these optimizer behaviors are part of the platform's Spark, and they continue to apply even when the Native Execution Engine offloads some operators to native code [S2].

Delta, partitioning, and predicate pushdown#

Lakehouse tables are Delta tables, and the interaction between your predicates and the physical layout is where most read performance lives:

  • File skipping. Delta maintains per-file column statistics (min/max) in its transaction log. A pushed-down predicate like WHERE order_date >= '2026-06-01' lets Spark skip entire files whose statistics rule them out — before reading a byte of Parquet.
  • Partition pruning. If the table is partitioned by a column that appears in the filter, whole directories are eliminated. Partition on low-cardinality, frequently filtered columns; over-partitioning produces a small-files problem that hurts far more than it helps.
  • Column pruning. Parquet is columnar, so SELECT only what you need — pushdown of column selection means unread columns cost nothing.

The practical discipline: write sargable predicates (compare bare columns to literals, not function(column) to literals), keep partition counts modest, and check EXPLAIN output for PartitionFilters and PushedFilters to confirm the pruning you expect is actually happening.

Spark SQL vs. SQL analytics endpoint vs. Warehouse T-SQL#

Fabric gives you three SQL surfaces over related data, and choosing correctly is an architecture decision, not a syntax preference:

Choosing Spark SQL vs T-SQL vs KQL in Fabric
  • Spark SQL (notebooks/jobs) — the engineering surface. Full read and write on lakehouse Delta tables, in-session mixing with PySpark/Scala, UDFs, ML libraries, and the full Spark dialect. Choose it for transformation pipelines, backfills, and any logic that lives alongside code. Cost model: a Spark session must be running; you pay for active session time [S3].
  • SQL analytics endpoint (lakehouse) — a read-only T-SQL surface automatically provided over the same lakehouse Delta tables. Choose it for BI tools, ad-hoc analyst queries, and serving semantic models — no Spark session, no cluster warm-up, T-SQL semantics. It is a query layer, not a transformation engine: writes still happen through Spark (or pipelines).
  • Warehouse (T-SQL) — the full read/write T-SQL experience: multi-table transactions, T-SQL DML/DDL, and the workload patterns of a classic relational warehouse, with data still stored in open Delta format on OneLake. Choose it when your team's skills, tooling, and requirements (e.g., transactional multi-table writes in T-SQL) are SQL-first rather than Spark-first.

A useful rule of thumb: Spark SQL when the logic belongs in code, the analytics endpoint when the question comes from a BI tool, the Warehouse when the whole workload is T-SQL-native. All three read Delta on OneLake, so choosing one is not a data-copy decision.

Session and pool considerations#

Because Spark SQL runs inside a Spark session, session mechanics are part of query behavior:

  • Startup latency. Starter pools keep pre-provisioned Medium-node clusters running, so sessions typically start in 5–10 seconds; choosing any custom node size or configuration switches to on-demand provisioning at roughly 2–5 minutes [S3]. Workspaces behind Tenant Private Links or Managed VNets cannot use starter pools at all and always pay the on-demand startup cost [S3].
  • Session lifetime. Sessions expire after 20 minutes of inactivity by default (configurable), and an unused pool deallocates shortly after expiry [S3]. Temp views and cached data die with the session — design notebooks so they can rebuild state.
  • Elasticity. With dynamic allocation, Fabric reserves executors at submission based on the pool's minimum node count, then grows and shrinks executor count with actual task demand, so you rarely hand-tune executor counts per job [S3]. Billing covers only active session time, not warm-up or deallocation [S3].
  • Per-session SQL tuning. The %%configure magic tunes SQL-cell behavior through session configuration — for example livy.rsc.sql.num-rows caps how many rows a Spark SQL query returns to the notebook [S1].
  • Pipelines are narrower. When a notebook runs as a pipeline activity, only five magics are supported — %%pyspark, %%spark, %%csharp, %%sql, and %%configure — so notebooks destined for orchestration should stay within that surface [S1].

Takeaway#

Spark SQL in Fabric is standard Catalyst-planned, AQE-corrected Spark execution over Delta tables — so classic Spark tuning intuition transfers directly. What is Fabric-specific is the surrounding topology: metastore resolution via the pinned lakehouse, pool startup and session lifecycle economics, and the deliberate split of SQL surfaces across Spark SQL, the read-only analytics endpoint, and the Warehouse. The expert lesson descends one more level, into the Native Execution Engine and shuffle internals.

Sources#