FABRIC SPARK TOOLKIT
← Hub Spark Internals Runtime 2.0 Guide OneLake & Polaris Fabric Deep Dives
⌂ Toolkit index ← Spark internals reference Deep dives · SQL surface, functions, dbt

Fabric Deep Dives

Views & UDFs in Spark · NEE do's and don'ts · Fabric SQL Database from notebooks · User Data Functions · dbt on Lakehouse and Warehouse. Companion to the Spark internals reference.
Fact-checked, not recalled. Every Spark capability claim in Section 1 was executed against a real Spark 3.5 session while writing this page. Three claims that "everyone knows" turned out to be wrong — they are called out inline as VERIFIED or FALSIFIED. Fabric-specific behaviour (SQL Database, UDF items, dbt adapters) is sourced from Microsoft and dbt Labs documentation and marked where it could not be executed here.
01 · Spark SQL Surface

Views and Functions in Spark — What Actually Exists

The four view types

Type Lifetime Behaviour and use
TEMP VIEW Session VERIFIED. Scoped to the SparkSession, invisible to other sessions, vanishes on session end. The workhorse for staging steps inside one notebook. No catalog write, so no permissions to manage.
GLOBAL TEMP VIEW Application VERIFIED. Shared across all sessions in the same Spark application — which in Fabric means across notebooks sharing a high-concurrency session. Must be read via the global_temp database: SELECT * FROM global_temp.v_name. Forgetting that prefix is the usual "table not found".
VIEW (persistent) Permanent VERIFIED. Stored in the catalog, survives sessions, visible to anyone with access to the Lakehouse. This is a logical view — the SQL re-executes on every read. No storage, no refresh, no cost until queried.
MATERIALIZED LAKE VIEW Permanent + stored Fabric-only. FALSIFIED for OSS: CREATE MATERIALIZED VIEW raises ParseException on open-source Spark 3.5 — the syntax simply does not exist there. In Fabric, CREATE MATERIALIZED LAKE VIEW produces a real Delta table with managed refresh (internals Sec 20).
Gotcha, executed and confirmed: permanent views require an alias on every computed column.
SPARK SQL NOTEBOOK %%sql OR SQL ENDPOINT 
-- FAILS: [CREATE_PERMANENT_VIEW_WITHOUT_ALIAS]
CREATE OR REPLACE VIEW v_bad AS SELECT cat, count(*) FROM orders GROUP BY cat;
-- WORKS
CREATE OR REPLACE VIEW v_good AS SELECT cat, count(*) AS n FROM orders GROUP BY cat;
-- WORKS (temp views are exempt - which is why this bites only on promotion to permanent)
CREATE OR REPLACE TEMP VIEW v_tmp AS SELECT cat, count(*) FROM orders GROUP BY cat;
The trap is the asymmetry: your temp view works all through development, then fails the moment you promote it to a permanent view.

The function types, and which to reach for

Type Runtime Verdict
Built-in functions both Always first choice. Catalyst optimises through them, whole-stage codegen fuses them, and NEE offloads them natively.
SQL UDF
CREATE FUNCTION … RETURN		 4.x only VERIFIED FALSIFIED on 3.5: raises ParseException on Spark 3.5 — it is genuinely Runtime 2.0 only, not merely "newer". Where available it is the best home for shared business logic: catalog-resident, inlined by the optimizer, zero serialization, NEE-friendly.
pandas UDF
@pandas_udf		 both Vectorized via Arrow — the right fallback when logic cannot be expressed in built-ins. Appears as ArrowEvalPython in the plan.
Python row UDF
F.udf(…)		 both VERIFIED it works — and it is still the wrong tool. Row-at-a-time serialization, opaque to Catalyst, breaks codegen, and drops the enclosing operator out of NEE. Appears as BatchEvalPython.
Registered Python UDF for SQL
spark.udf.register 		both VERIFIED. Makes a Python function callable from %%sql. Same performance caveats as a row UDF — convenience, not optimisation.
Python UDTF
@udtf		 4.x Table-valued Python functions. The udtf symbol exists in the 3.5 API surface, but treat UDTFs as a 4.x capability for production use.

SHOW USER FUNCTIONS lists what is registered in the current session — useful for confirming a SQL UDF actually landed in the catalog rather than the session.

JDK 21 + Arrow note (relevant to Runtime 2.0). While validating these examples, pandas UDFs failed on a JDK 21 container with UnsupportedOperationException: sun.misc.Unsafe or java.nio.DirectByteBuffer… not available — Arrow needs --add-opens java.base/java.nio=ALL-UNNAMED on modern JDKs. Fabric configures this for you; the reason to know it is that the same error in a custom JVM setting or a local dev environment on Runtime 2.0 (JDK 21) is a JVM flag problem, not a code problem.
02 · Native Execution

NEE — Do's, Don'ts, and the Cost Model

Mechanics and fallback tiers are in internals Sec 25. This is the practice sheet.

DO
  • Keep sources Delta or Parquet. Native scan is the foundation; JSON/XML forfeit it entirely.
  • Use built-in functions for everything expressible. One unsupported expression drops its whole operator to the JVM.
  • Parse semi-structured data once at bronze, then never again. Re-parsing JSON downstream re-enters fallback every time.
  • Flatten before the hot path. Deeply nested struct/map manipulation is a documented fallback trigger.
  • Verify natively — look for *Transformer / *NativeFileScan in the plan, and watch the Diagnostics pane's fallback panel.
  • Decide ANSI deliberately on Runtime 2.0 — ANSI on means NEE falls back, and ANSI is the 4.x default.
  • Measure with nb_nee_fallback_analyzer rather than assuming acceleration.
DON'T
  • Don't assume "enabled" means "running natively." The most common NEE failure is a fully-fallen-back plan nobody checked.
  • Don't leave NEE on for a plan that fully falls back — you pay columnar↔row conversion for no native execution, which can be slower than NEE off.
  • Don't expect gains on I/O-bound work. Microsoft is explicit that NEE targets compute-intensive queries.
  • Don't expect it for streaming — still a JVM path.
  • Don't scatter row UDFs through silver transforms; each one is an operator dropped from native execution.
  • Don't chase a frozen operator list. Coverage moves every release — read the plan instead.
The one-line test. If df.explain() shows no *Transformer operators while spark.native.enabled=true, NEE is costing you and giving nothing. Either remove the trigger, or turn it off for that job.
03 · Fabric SQL Database

Talking to Fabric SQL Database from Notebooks

Three access paths, and they are not interchangeable. Picking the wrong one is the source of most "why is my metadata layer slow" complaints.

Path Can it call… Use for
pyodbc + Entra token tables, views, stored procedures, DML, DDL The control plane. Anything transactional, anything with output parameters, anything that must run exactly once. The only path that can execute a stored procedure.
Spark JDBC tables, views, query pushdown Bulk reads only. Returns a DataFrame for joining against lakehouse data. Cannot call stored procedures.
OneLake mirror mirrored tables (read-only) Analytical reads over SQL DB data at lakehouse speed, no connection at all. Near-real-time, not synchronous — never use it for control-plane decisions.

Calling a stored procedure (pyodbc)

PYSPARK FABRIC NOTEBOOK UTILS 
# Obtain Entra ID bearer token for Azure SQL / Fabric SQL Database
token = notebookutils.credentials.getToken("https://database.windows.net/")
jdbc_url = f"jdbc:sqlserver://{SQL_SERVER}:1433;database={SQL_DB};encrypt=true;"
# Read a VIEW: Push filter predicates into SQL engine directly
meta = (
spark.read.format("jdbc")
.option("url", jdbc_url)
.option("accessToken", token)
.option("query", "SELECT entity_id, target_table, load_type FROM dbo.vw_active_entities WHERE layer = 'silver'")
.load()
)
# Parallel read of a LARGE table with partition pushdown
big = (
spark.read.format("jdbc")
.option("url", jdbc_url)
.option("accessToken", token)
.option("dbtable", "dbo.fact_positions")
.option("partitionColumn", "position_id")
.option("lowerBound", 1)
.option("upperBound", 50_000_000)
.option("numPartitions", 16) # 16 concurrent JDBC connections
.load()
)

Reading a view or table in bulk (Spark JDBC)

PYSPARK FABRIC NOTEBOOK UTILS 
# Obtain Entra ID bearer token for Azure SQL / Fabric SQL Database
token = notebookutils.credentials.getToken("https://database.windows.net/")
jdbc_url = f"jdbc:sqlserver://{SQL_SERVER}:1433;database={SQL_DB};encrypt=true;"
# Read a VIEW: Push filter predicates into SQL engine directly
meta = (
spark.read.format("jdbc")
.option("url", jdbc_url)
.option("accessToken", token)
.option("query", "SELECT entity_id, target_table, load_type FROM dbo.vw_active_entities WHERE layer = 'silver'")
.load()
)
# Parallel read of a LARGE table with partition pushdown
big = (
spark.read.format("jdbc")
.option("url", jdbc_url)
.option("accessToken", token)
.option("dbtable", "dbo.fact_positions")
.option("partitionColumn", "position_id")
.option("lowerBound", 1)
.option("upperBound", 50_000_000)
.option("numPartitions", 16) # 16 concurrent JDBC connections
.load()
)
Five gotchas that cost real time.
  1. Spark JDBC cannot call stored procedures. It expects a query it can wrap in a subselect. Use pyodbc.
  2. A JDBC read without partitionColumn is one connection, one task. Fine for metadata, disastrous for a 50M-row table — and it looks like "Spark is slow" rather than "you asked for one thread".
  3. numPartitions is concurrent connections to an operational database. 64 partitions against a busy SQL DB is a denial-of-service you inflicted on yourself.
  4. Never write run logs row-by-row. One DataFrame write or one execute() per row against SQL DB is enormous overhead; use cur.executemany(...) or accumulate and write once (internals Sec 27).
  5. Connections are not shareable across concurrent runMultiple activities. Open per activity, close in finally.
Shape that works: pyodbc for control-plane reads and all writes · Spark JDBC only when the metadata is genuinely large or must be joined to lakehouse data · the OneLake mirror for analytical history. Fetch config once per run, cache to a dict, write the run log once at the end.
04 · User Data Functions

Fabric User Data Functions — Where They Actually Fit

A User Data Functions item is serverless Python hosted by Fabric, callable from notebooks, pipelines, Activator rules, Power BI translytical task flows, and external clients via a per-function REST endpoint. Crucially it is not a Spark UDF: it does not run inside your Spark plan, so it never appears in a query plan and never causes NEE fallback.

Spark UDF vs Fabric User Data Function — different tools entirely
Dimension Spark UDF Fabric User Data Function
Executes Inside the Spark plan, per row/batch on executors Outside Spark, serverless, once per call
Scale unit Billions of rows One call (which may carry a DataFrame)
Plan impact BatchEvalPython; forces NEE fallback None — invisible to Catalyst
Reuse Per session, or a library Item-level, cross-workspace, versioned, REST-addressable
Right for Row transformations that must be distributed Control-plane logic: validation, lookups, logging, API calls, orchestration decisions
PYSPARK FABRIC NOTEBOOK UTILS 
# --- Inside the User Data Functions item ---
import fabric.functions as fn
import logging
udf = fn.UserDataFunctions()
@udf.function() # only decorated functions are externally callable
def validate_entity(entity_name: str, row_count: int, expected_min: int = 1000) -> dict:
ok = row_count >= expected_min
logging.info(f"validate {entity_name}: {row_count} rows, ok={ok}")
return {"entity": entity_name, "ok": ok, "row_count": row_count}
def _helper(): # no decorator = private, not callable from outside
...
# --- Calling it from a notebook ---
fns = notebookutils.udf.getFunctions("etl-shared-functions") # same workspace
fns = notebookutils.udf.getFunctions("etl-shared-functions", WS_ID) # cross-workspace
result = fns.validate_entity(entity_name="silver_orders", row_count=41230) # named params
display(fns.validate_entity.functionDetails) # inspect the signature

Where they earn their place in a metadata-driven framework

Use Why a UDF item beats the alternatives
Centralised run logging One log_run() callable identically from a notebook and a pipeline. Without it, pipelines cannot write your run log without a notebook activity purely to hold the code.
Validation gates "Is this entity allowed to run now?" as one function consulted by every orchestrator. Business rules change in one place.
Config resolution Wrap the SQL Database lookup so notebooks never carry connection logic or secrets handling at all.
Rate-limited API calls Async support suits I/O-bound work, and the serverless model means no Spark session idles while you wait (internals Sec 32).
Cross-workspace standards Centralise in a platform workspace; every domain workspace calls the same tested function.
Where NOT to use them. Never per row. A UDF item is a service call, not a vectorized operator — calling it inside a Spark withColumn over millions of rows is a per-row network round trip. Community guidance is consistent: keep them for light tasks. Pandas DataFrame/Series input and output (Arrow-backed) lets you pass a batch in one call, which is the correct pattern when data must go through a UDF item at all — but distributed row transformation belongs in Spark built-ins.
05 · dbt on Fabric

dbt on Lakehouse and Warehouse — Deep Dive

Microsoft ships two separate adapters, and the split matters more than most teams expect: they target different engines, different SQL dialects, and different materializations.

Adapter Target Mechanics
dbt-fabricspark Lakehouse (Spark SQL) Connects to Fabric Spark through the Livy API, session-jobs mode only (so the Spark session is reused across models). Auto-detects schema-enabled lakehouses via the Fabric REST API and switches between two-part and three/four-part naming accordingly.
dbt-fabric Warehouse (T-SQL) Connects over ODBC — requires Microsoft ODBC Driver 18 (17 also works). T-SQL dialect, warehouse semantics.

dbt Jobs in Fabric entered public preview in December 2025 as a native orchestration item, and Microsoft has stated dbt Fusion support is expected later in Q2 2026. A community adapter (dbt-fabric-samdebruyn) supports both engines in one package and newer dbt-core versions — worth knowing about, but evaluate support model before production use.

The feature that changes the MLV conversation

dbt-fabricspark supports materialized='materialized_lake_view' — dbt can create and schedule Materialized Lake Views directly, including cron and daily schedule configuration. This resolves the usual tension: you keep dbt's lineage, testing and CI/CD while the refresh itself is managed by Fabric.
SPARK SQL NOTEBOOK %%sql OR SQL ENDPOINT 
{{ config(
materialized='materialized_lake_view',
mlv_schedule={
      "enabled": true,
"configuration": {
        "startDateTime": "2026-04-10T00:00:00",
"endDateTime": "2026-12-31T23:59:59",
"localTimeZoneId": "Central Standard Time",
"type": "Daily",
"times": ["06:00", "18:00"]
      }
    }
) }}

SELECT order_date, SUM(amount) AS revenue
FROM {{ ref('silver_orders') }}
WHERE status = 'complete'
GROUP BY order_date
Documented limit: only one active schedule per lakehouse lineage — the adapter updates an existing schedule rather than adding a second. Plan one schedule per lineage, not per model.
The MLV trap carries straight through dbt. A dbt model materialized as an MLV inherits every incremental-refresh constraint from internals Sec 20: window functions force full refresh, sources must have CDF enabled, and any update or delete on a source forces full refresh regardless. dbt makes the MLV easier to author and schedule; it does not make it incremental. If your source has update/delete traffic, an incremental dbt model doing a MERGE is the better materialization — you get incremental behaviour dbt controls, instead of an MLV that quietly rebuilds every cycle.

Gotchas, by adapter

Gotcha Detail
Schema-enabled vs not (Spark) Four-part naming works only against schema-enabled lakehouses; setting workspace_name on a non-schema-enabled target raises a parse-time error. Set it once in profiles.yml as a target default rather than per model.
Incremental models use staging tables The Spark adapter uses persisted staging tables instead of temp views to work around Spark's REQUIRES_SINGLE_PART_NAMESPACE limitation. Expect extra physical tables, and include them in your maintenance scope (internals Sec 30).
Livy session lifetime Session-jobs mode reuses one Spark session across models — good for CU, but a long dbt run holds that session. Livy sessions also time out on inactivity, which surfaces as a mid-run failure on very long DAGs.
ODBC driver on the runner (Warehouse) dbt-fabric needs ODBC Driver 18 installed on whatever runs dbt, including CI agents and containers. On Debian/Ubuntu you also need the ODBC header files before pip install.
Collation (Warehouse) Fabric Warehouse is tested on Latin1_General_100_BIN2_UTF8 — a case-sensitive, binary collation. Models that rely on case-insensitive joins or comparisons behave differently than on a typical SQL Server.
Authentication Azure CLI (az login) is fine for self-hosted development; use an Entra service principal for CI/CD and scheduled runs.
Threads Fabric Lakehouse profiles commonly use threads: 1. Raising it multiplies concurrent Livy work — test against capacity before increasing (internals Sec 18).

Choosing where gold lives — Lakehouse or Warehouse

Signal Lakehouse (dbt-fabricspark) Warehouse (dbt-fabric)
Team dialect Spark SQL; Python models available T-SQL; familiar to SQL Server teams
Consumption Direct Lake, Spark, notebooks T-SQL clients, multi-table transactions, tools expecting a relational warehouse
Transactions Per-table Delta commits Multi-table transactional guarantees
MLV materialization Supported Not applicable
Write path Spark, so V-Order and clustering are yours to control Warehouse manages its own storage and maintenance
A composition that works in practice. Bronze and silver in the Lakehouse via Spark notebooks (metadata-driven, CDF-incremental where updates exist — internals Sec 38). Gold modelled in dbt: materialized_lake_view for genuinely append-only SQL-expressible aggregates, incremental with MERGE for everything else. Serve Direct Lake from the Lakehouse gold; push into Warehouse only where you need T-SQL semantics or multi-table transactions. Orchestrate with dbt Jobs or a pipeline calling dbt, and keep one schedule per lakehouse lineage.

The trap to avoid: using dbt as a reason to model gold twice — once as MLVs and again as warehouse tables. Pick the consumption surface first, then the materialization.
Sources: Microsoft Learn (User Data Functions, notebookutils.udf, SQL Database connectivity, dbt on Fabric), dbt Labs documentation (fabricspark and fabric adapters), the microsoft/dbt-fabricspark repository and PyPI package notes, and the Microsoft Fabric blog. Spark capability claims in Section 1 were executed against Spark 3.5 while writing. Preview statuses move — verify anything marked preview before building on it.
Deep Dive 6 · AI & Knowledge

Fabric Data Agents & Governed NL-to-Query Architecture

Fabric Data Agents provide conversational data access over Lakehouses, Warehouses, and KQL Databases under strict security isolation. Unlike raw LLM text-to-SQL prompts, Fabric Data Agents enforce a four-layer governance hierarchy and restrict unbounded outputs.

Architecture: Fabric Data Agent Life of a Request
1. User NL Prompt Natural Language Question Passed with user Entra Security Context 2. Grounded Routing Source Routing + Schema NL2Ontology or Guarded T-SQL Prevents Unsafe Joins 3. Delegated Execution Target Engine Query SQL Analytics / Lakehouse Hard 25-Row Result Cap 4. Response Governed Answer
Deep Dive 7 · Enterprise Knowledge

Fabric IQ: The Ontology Layer Over OneLake Data

Fabric IQ introduces a shared semantic ontology layer mapped directly to existing Power BI semantic models and OneLake Delta tables. This replaces ad-hoc entity definitions across departments with an authoritative instance graph.