Fabric Mlv

Fabric Materialized Lake Views (MLV)

Declarative SQL/PySpark transformations that persist as Delta tables in a schema-enabled lakehouse. Fabric handles refresh strategy, dependency order, and data quality enforcement so you don't write notebook orchestration.

When to use vs not

Use MLVs for medallion bronze→silver→gold pipelines, frequently-queried aggregates, declarative data quality, and reporting datasets that need automatic refresh. Skip them for one-off queries, sub-second streaming (use Real-Time Intelligence), or transformations that need ML inference / external API calls / non-SQL Python (use a regular Spark notebook).

Prerequisites

• Schema-enabled lakehouse — required. enableSchemas is immutable per lakehouse; you can't retrofit it.
• Fabric Runtime 1.3 — earlier runtimes can't author MLVs.
• Region — not available in South Central US (as of 2026-04).
• CDF on source tables — required for incremental refresh: ALTER TABLE bronze.x SET TBLPROPERTIES (delta.enableChangeDataFeed = true). Without it, optimal refresh degrades to skip-or-full only.

Spark SQL — CREATE

CREATE [OR REPLACE] MATERIALIZED LAKE VIEW [IF NOT EXISTS]
  [workspace.lakehouse.schema].MLV_Identifier
[(
    CONSTRAINT name1 CHECK (expr1) [ON MISMATCH DROP | FAIL],
    CONSTRAINT name2 CHECK (expr2) [ON MISMATCH DROP | FAIL]
)]
[PARTITIONED BY (col1, col2, ...)]
[COMMENT "..."]
[TBLPROPERTIES ("k1"="v1", ...)]
AS select_statement

| Clause | Notes | |---|---| | OR REPLACE | Mutually exclusive with IF NOT EXISTS | | CONSTRAINT ... CHECK | Multiple allowed. Only deterministic built-ins permitted | | ON MISMATCH DROP | Silently drops violating rows. Each row dropped at most once even if it violates multiple constraints | | ON MISMATCH FAIL | Default. Stops the refresh with an error | | PARTITIONED BY | Improves filtered-read performance | | TBLPROPERTIES | Set delta.enableChangeDataFeed=true here to enable CDF on the MLV itself for downstream MLVs |

Workspace names with spaces require backtick-quoting: ` My Workspace.lakehouse.schema.view_name `.

CREATE OR REPLACE MATERIALIZED LAKE VIEW silver.cleaned_orders
( CONSTRAINT valid_qty CHECK (quantity > 0) ON MISMATCH DROP )
PARTITIONED BY (category)
TBLPROPERTIES (delta.enableChangeDataFeed=true)
AS SELECT p.productID, p.category, o.orderDate, o.quantity, o.totalAmount
FROM bronze.products p INNER JOIN bronze.orders o ON p.productID = o.productID;

Spark SQL — manage

SHOW MATERIALIZED LAKE VIEWS IN silver;
SHOW CREATE MATERIALIZED LAKE VIEW silver.cleaned_orders;
ALTER MATERIALIZED LAKE VIEW silver.cleaned_orders RENAME TO silver.cleaned_orders_v2;
DROP MATERIALIZED LAKE VIEW silver.cleaned_orders;
REFRESH MATERIALIZED LAKE VIEW silver.cleaned_orders FULL;

You cannot ALTER the definition — only RENAME. To change SELECT, constraints, or partitioning: drop and recreate (or CREATE OR REPLACE).

PySpark (fmlv — preview)

Use when transformations need UDFs, external Python libraries, or reusable helper functions that are awkward in SQL.

import fmlv
from pyspark.sql import functions as F

@fmlv.materialized_lake_view(
    name="LH1.silver.customer_enriched",
    partition_cols=["year", "city"],
    table_properties={"delta.enableChangeDataFeed": "true"},
    replace=True
)
@fmlv.check("nonnull_sales", "sales IS NOT NULL", "drop")
def customer_enriched():
    df = spark.read.table("LH2.bronze.customer_bronze")
    return df.withColumn("sales_in_usd", F.col("sales") * 1.0)

Notebook organization rules (PySpark only)

• One @fmlv decorator per cell — multiple per cell is unsupported.
• Helper functions go in cells above the @fmlv cell.
• The defining notebook must not be deleted — scheduled refresh re-executes its cells. Deletion silently breaks every MLV defined there.
• After editing the decorator, re-run the notebook to register the change. Otherwise the next refresh executes the new code with stale registration metadata and may fail.
• No variables in @fmlv arguments — all parameters must be hardcoded literals. name=view_name will not work.
• Only %%pyspark and %%sql magics, and only at the top of a cell.
• Don't mix MLV definitions with unrelated code in the same notebook.

PySpark trade-offs vs SQL

| Capability | Spark SQL | PySpark (fmlv) | |---|---|---| | Optimal (incremental) refresh | ✅ | ❌ — always full refresh or skip | | On-demand refresh from notebook | ✅ (REFRESH ... FULL) | ❌ — lineage-schedule only | | Rename via SQL | ✅ (ALTER ... RENAME) | ❌ — drop+recreate, or rename in lakehouse explorer |

Optimal refresh

Optimal refresh is on by default. Per-run, Fabric picks one of three strategies based on Delta commits on source tables:

| Strategy | When | |---|---| | Skip | No new Delta commits on any source table | | Incremental | New commits + query uses only the supported-construct subset + all sources have CDF enabled + append-only | | Full | Source has updates/deletes, unsupported constructs, non-Delta source, or PySpark-defined MLV |

Toggle: lakehouse → Materialized lake views → Manage → Optimal refresh. Off = every scheduled run does a full rebuild.

What blocks incremental refresh

| Construct | Behavior | |---|---| | SELECT aggregates (SUM, COUNT, AVG, MIN, MAX, STDDEV) | Full refresh | | GROUP BY, DISTINCT, window functions | Full refresh | | Non-deterministic funcs (rand(), uuid(), current_timestamp()) | Full refresh | | INNER JOIN, LEFT OUTER, LEFT SEMI, UNION ALL | Incremental — but LEFT joins fall back to full if the right-side table changes | | Subqueries / EXISTS | Full refresh if any referenced table changes | | WITH (CTE) | Incremental if every clause inside is supported | | Source is non-Delta table | Always full refresh |

Unsupported constructs don't block creation — they just downgrade to full refresh. Audit MLVs whose runs always show as Full when you expected Incremental.

Lineage and scheduling

When an MLV references another MLV or table, Fabric builds a dependency DAG (the lineage view). A single schedule runs the whole DAG in the right order — you don't write orchestration. Currently one active schedule per lineage per lakehouse.

UI path: lakehouse → Materialized lake views → Manage → Schedule → New schedule.

Run history retention: last 25 runs OR last 7 days, whichever comes first.

| Run state | Meaning | |---|---| | In progress | Currently running | | Success | All views in DAG refreshed | | Failed | At least one view failed; downstream children auto-marked Skipped | | Skipped | Same view already refreshing in another active run | | Canceled | Manually canceled from Monitor hub |

Note: Monitor hub may show a Skipped MLV run as Canceled — they're the same thing in the lineage view.

REST API (job scheduler)

{jobType} is RefreshMaterializedLakeViews for every MLV endpoint.

# On-demand refresh of the entire lineage
POST /v1/workspaces/{ws}/lakehouses/{lh}/jobs/RefreshMaterializedLakeViews/instances
→ 202 Accepted, Location: .../jobs/instances/{jobInstanceId}

# Schedule CRUD
POST   /v1/workspaces/{ws}/lakehouses/{lh}/jobs/RefreshMaterializedLakeViews/schedules
GET    /v1/workspaces/{ws}/lakehouses/{lh}/jobs/RefreshMaterializedLakeViews/schedules
GET    /v1/workspaces/{ws}/lakehouses/{lh}/jobs/RefreshMaterializedLakeViews/schedules/{id}
PATCH  /v1/workspaces/{ws}/lakehouses/{lh}/jobs/RefreshMaterializedLakeViews/schedules/{id}
DELETE /v1/workspaces/{ws}/lakehouses/{lh}/jobs/RefreshMaterializedLakeViews/schedules/{id}

# Job instance status / cancel
GET  /v1/workspaces/{ws}/lakehouses/{lh}/jobs/RefreshMaterializedLakeViews/instances
GET  /v1/workspaces/{ws}/lakehouses/{lh}/jobs/instances/{jobInstanceId}
POST /v1/workspaces/{ws}/lakehouses/{lh}/jobs/instances/{jobInstanceId}/cancel

Polling pattern: take Location from the 202, poll the Get Item Job Instance endpoint until status ≠ InProgress. Job-scheduler limits cap schedules-per-lakehouse and visible historical instances.

Data quality report

Auto-generated Power BI report tracking CHECK violations and DROP counts. Lakehouse → Manage materialized lake views → Data quality report → Generate report.

• Two pages: Overview (last 7 days, top MLVs/constraints) and MLV Detail (filterable by SchemaName / MLVName / RelativeDate).
• Built on DirectQuery — capped at 1M rows per query on non-premium capacity.
• Workspace/lakehouse names with special characters or spaces can fail report generation.
• Recipients need at least Read or ReadData on the SQL analytics endpoint.
• Violations ≥ drops (one row only ever dropped once even if it violates multiple constraints).

Limitations and gotchas

| Issue | Cause | Fix | |---|---|---| | MLV name unexpectedly lowercased | Names are case-insensitive, normalized to lowercase | Reference as lowercase everywhere; don't rely on MyView resolving distinct from myview | | ALTER to change SELECT fails | Only RENAME is supported via ALTER | Drop + recreate, or CREATE OR REPLACE | | INSERT/UPDATE/DELETE rejected | MLV is populated only by its SELECT | Modify the source table or rewrite the SELECT | | Time-travel in definition rejected | VERSION AS OF / TIMESTAMP AS OF not allowed | Materialize the historical snapshot to a regular table first | | UDF / temp view in definition rejected | Not supported in CREATE MATERIALIZED LAKE VIEW | Rewrite without UDFs, or switch to PySpark fmlv | | Schema name MYSCHEMA rejected | All-uppercase schema names not supported | Use mixed-case or lowercase schema names | | spark.conf.set(...) doesn't apply on refresh | Session-level Spark properties are dropped on scheduled refresh | Set lakehouse- or workspace-level properties instead | | Optimal refresh always picks Full | Unsupported construct (aggregates / window / non-deterministic / non-Delta source) or no CDF on source | Check the supported-construct table; enable CDF; restructure SELECT | | Incremental refresh skips changes from a LEFT join's right side | Right-side change triggers full refresh by design | Expected; or rewrite as INNER if right side is fully populated | | PySpark MLV refresh fails after notebook edit | Decorator changed but notebook wasn't re-run | Re-execute every cell once after editing; refresh re-uses the latest cell contents | | PySpark MLV stops refreshing | Defining notebook deleted | The notebook is load-bearing for PySpark MLVs — don't delete it | | @fmlv.materialized_lake_view(name=view_name) errors | Variables not allowed in decorator args | Hardcode every parameter as a literal | | MLV name with . rejected | Periods reserved for workspace.lakehouse.schema.name qualification | Use _ or another separator | | Two MLVs in one PySpark cell — only one registers | One decorator per cell limit | Split into separate cells | | Run shows as Canceled in Monitor hub but Skipped in lineage | Monitor hub maps Skipped → Canceled | Trust the lineage view's status | | Data quality report fails to generate | Workspace/lakehouse name has spaces or special characters | Rename, or generate the report against a clean-named lakehouse | | Data quality report missing rows | DirectQuery 1M-row cap on non-premium | Use premium capacity, or recreate the report after pruning history | | Cross-lakehouse MLV chain doesn't work | Cross-lakehouse lineage and execution not supported | Keep MLV chains within a single lakehouse |

Reference

• Microsoft Learn: What are materialized lake views in Microsoft Fabric?
• Microsoft Learn: Spark SQL reference for materialized lake views
• Microsoft Learn: Optimal refresh for materialized lake views
• Comprehensive MS Learn link bundle (concept / SQL ref / PySpark fmlv / optimal refresh / lineage / scheduling REST / monitoring / data quality report / lakehouse schemas + Runtime 1.3 prereqs): [references/REFERENCE.md](references/REFERENCE.md)

See also

• fabric-spark — PySpark in Fabric notebooks (the broader Spark surface; MLVs are one consumer)
• fabric-eventhouse — KQL materialized views (different engine, similar concept)
• fabric-error-handling — notebook/pipeline error patterns; useful when wrapping MLV refresh in a larger flow
• fabric-monitoring — Monitor hub and Workspace monitoring
• pbip-project-structure — .Lakehouse/ folder placement when MLV-bearing lakehouses live in a PBIP repo

Source & license

This open-source skill is cataloged on AgentStack and links to its original source — we do not rehost the code.

• Author: wardawgmalvicious
• Source: wardawgmalvicious/claude-config
• License: MIT

Install and usage instructions live in the source repository linked above.