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.

What Spark SQL is#

Spark SQL is the SQL interface of Apache Spark: a module that lets you express queries in standard SQL syntax and have them executed by the same distributed Spark engine that runs PySpark and Scala code. It is not a separate database. When you write SELECT * FROM sales in a Fabric notebook, Spark parses the statement, plans it, and runs it as a distributed job across the notebook's Spark session — exactly as it would a DataFrame operation written in Python.

In Microsoft Fabric this matters because the lakehouse stores its tables in the open Delta Lake format on OneLake. Spark SQL reads and writes those Delta tables directly, so anything you load into a lakehouse — via pipelines, dataflows, or Spark itself — is immediately queryable with plain SQL from a notebook.

Spark SQL query lifecycle in Fabric

Running SQL in a notebook: %%sql cells#

Fabric notebooks are polyglot per cell, not per notebook. A notebook has a primary language, but any individual cell can be switched to one of four Spark languages — PySpark (%%pyspark), Scala (%%spark), Spark SQL (%%sql), or SparkR (%%sparkr) — by placing the magic command at the top of the cell or using the cell language picker [S1].

A %%sql cell executes its query as Spark SQL against the notebook's Spark context. It is not routed to a different SQL engine: it shares the same running Spark session, the same cluster, and the same attached lakehouse as the PySpark and Scala cells around it [S1]. That single-session model is what makes mixing languages practical — a table or view created in one cell is visible from the next, regardless of language.

SQL cells get a solid but slightly reduced editing experience: syntax highlighting, error marking, code completion for syntax and built-in functions, smart indent, and code folding — but no code completion for user-defined functions, which PySpark, Scala, and SparkR cells do get [S1].

Notebook sessions run on Fabric's managed Spark compute. If your workspace uses the default starter pool, a session typically starts in about 5–10 seconds because Fabric keeps pre-provisioned clusters warm; custom compute configurations start on demand and take a few minutes instead [S2].

Running SQL from Python: spark.sql()#

Inside a PySpark cell, the same engine is available through the spark.sql() function:

python
df = spark.sql("SELECT region, SUM(amount) AS total FROM sales GROUP BY region")
df.show()

The result of spark.sql() is an ordinary Spark DataFrame. You can filter it, join it, chart it, or write it back to a Delta table with the DataFrame API. This is the bridge in one direction: SQL text in, DataFrame out. Use %%sql when a cell is purely a query you want to read as SQL; use spark.sql() when the query result feeds further Python logic.

Querying lakehouse Delta tables#

Tables you see in a lakehouse's Tables section are Delta tables registered in a metastore, and Spark SQL queries them by name:

sql
SELECT COUNT(*) FROM sales WHERE order_date >= '2026-01-01'

One mechanic is worth learning early: the notebook's pinned default lakehouse determines which metastore unqualified table names resolve against. If a notebook has several lakehouses attached, a Spark SQL query against a non-default lakehouse only resolves if that lakehouse is in the same workspace as the current default — otherwise you get "table not found" errors that look mysterious until you know the rule [S1]. When in doubt, check which lakehouse is pinned before debugging the SQL itself.

Temp views vs tables#

Spark SQL gives you two very different places to put a named result:

  • Temporary views exist only inside the current Spark session. CREATE OR REPLACE TEMP VIEW recent_sales AS SELECT ... (or df.createOrReplaceTempView("recent_sales") from Python) registers a name for a query or DataFrame without writing any data. When the session ends, the view is gone. Temp views are the standard way to hand a PySpark DataFrame to a %%sql cell.
  • Tables are persisted Delta data in the lakehouse. CREATE TABLE ... AS SELECT or df.write.saveAsTable("name") writes files to OneLake and registers the table so other notebooks, other sessions, and other Fabric engines can see it.

A good habit: use temp views for intermediate steps within one notebook run, and only create tables for results that need to outlive the session or be shared.

Mixing SQL with PySpark DataFrames#

The two directions of the bridge:

python
# PySpark -> SQL: expose a DataFrame to SQL cells
orders_df.createOrReplaceTempView("orders_v")
sql
-- SQL cell using the view
SELECT customer_id, COUNT(*) AS orders FROM orders_v GROUP BY customer_id
python
# SQL -> PySpark: capture a query as a DataFrame
top = spark.sql("SELECT * FROM orders_v ORDER BY amount DESC LIMIT 10")

Because everything runs in one session, there is no data copying between "the SQL side" and "the Python side" — both are views over the same distributed engine.

Two conveniences to file away for later. Reusable SQL can live in a versioned .sql file in the notebook's built-in resources and be executed with the %run magic (for example %run -b script_file.sql), instead of being copy-pasted between notebooks [S1]. And some SQL-cell behavior is tuned through session configuration rather than SQL syntax: the %%configure magic's conf block accepts settings such as livy.rsc.sql.num-rows, which controls how many rows a Spark SQL query returns to the notebook [S1].

Where to go next#

You can now run SQL three ways in a Fabric notebook — %%sql cells, spark.sql(), and %run against a .sql resource file — and you know how names resolve and where results live. The intermediate lesson goes under the hood: how Spark plans and optimizes these queries, and when Spark SQL is (and isn't) the right engine compared with the lakehouse SQL analytics endpoint and the Warehouse.

Sources#