Spark CLI

Materialized Lake View Patterns — Skill Resource

Public-facing authoring patterns for Microsoft Fabric Materialized Lake Views (MLVs). Use this resource when the task is about writing, executing, reviewing, or restructuring MLV definitions, not when the task is about Spark job triage or broad cross-workload orchestration.

Recommended patterns

Must

1. Use deterministic SQL in MLV definitions — keep transformations stable across refreshes.

2. Prefer Delta sources with Change Data Feed (CDF) enabled for source tables that feed MLVs.

3. Use Materialized Lake Views for durable layer outputs, not for transient notebook-only logic.

4. Apply data quality checks close to the source-aligned layer using CONSTRAINT ... CHECK ... ON MISMATCH DROP where appropriate.

5. Separate Bronze, Silver, and Gold responsibilities clearly:

   • Bronze: raw landing / source-aligned tables

   • Silver: cleaned and conformed datasets

   • Gold: business-facing aggregates

6. Keep MLVs business-stable — preserve query semantics unless the user explicitly asks for a redesign.

7. Use documented syntax only — avoid undocumented or implementation-specific features by default.

Prefer

1. Source-aligned Silver MLVs first, denormalized Silver MLVs second — then aggregate in Gold.

2. COUNT and SUM for Gold metrics when they satisfy the business requirement.

3. Downstream notebooks or BI logic for ranking, moving windows, and presentation formatting.

4. Cross-lakehouse 4-part naming when reading from another workspace/lakehouse.

5. Partitioned outputs when downstream reads are heavily filtered by date or a small set of dimensions.

6. Thin Gold MLVs that serve reusable business outputs instead of embedding every downstream convenience calculation.

MLV topology: count, consolidation, and redundancy

How many MLVs per lakehouse?

Consolidating redundant MLVs:

• If multiple MLVs query the same source with similar transforms → consolidate into one broader MLV

• If downstream consumers need different subsets → one MLV with broader scope + views/filters on top

• If refresh cadences differ → separate lakehouses (one schedule per lineage constraint)

When to split across lakehouses:

• Different refresh cadences needed (hourly vs daily)

• Different team ownership (RBAC boundaries)

• Independent failure isolation (one lineage failure shouldn't block another)

• Cross-lakehouse lineage (Extended lineage) can still chain them in dependency order

Rule of thumb: If a single lineage refresh takes > 30 minutes, consider splitting. Each split allows independent scheduling and parallel execution.

Avoid

1. Window functions inside MLVs — move them downstream.

2. Non-deterministic functions inside MLVs — stamp values during ingestion instead.

3. RIGHT JOIN, FULL OUTER JOIN, CROSS JOIN in MLVs intended for incremental refresh.

4. ORDER BY and LIMIT in MLV definitions.

5. Standalone SELECT DISTINCT as a default modeling pattern.

6. Embedding moving time windows like date_sub(current_date(), 90) directly in the MLV.

7. Using MLVs as a substitute for orchestration — non-MLV ingestion, validation, and cross-system workflows still belong in pipelines or notebooks. (Refresh ordering between MLVs themselves is the Lakehouse's job — see Refresh and orchestration guidance below.)

When to use Materialized Lake Views

Choose an MLV when the user needs one or more of the following:

• a durable curated table in a Lakehouse

• repeatable cleansing or conformance logic

• pre-joined analytical detail tables

• reusable aggregate outputs for BI or downstream notebooks

• a Bronze → Silver → Gold layer implemented directly in Fabric Lakehouse

Do not default to MLVs when the task is primarily:

• ad-hoc notebook exploration

• one-off data movement

• streaming/event processing

• Spark job debugging or performance triage

Executable PySpark authoring

Use the documented fmlv decorators when an interactive Livy session does not accept MLV DDL through spark.sql. Keep the source and output names aligned with the Spark catalog shown by SHOW SCHEMAS / SHOW TABLES.

import fmlv
from pyspark.sql.functions import col

@fmlv.materialized_lake_view(name="lakehouse.nyc_trips_clean", replace=True)
@fmlv.check(
    name="fare_positive",
    condition="fare_amount IS NOT NULL AND fare_amount > 0",
    action="DROP",
)
def nyc_trips_clean():
    return (
        spark.table("lakehouse.nyctlc")
        .select(
            col("vendor_id").alias("VendorID"),
            col("tpep_pickup_datetime").alias("lpep_pickup_datetime"),
            col("fare_amount"),
            col("total_amount"),
        )
        .filter(
            col("fare_amount").isNotNull() & (col("fare_amount") > 0)
        )
    )

Put any immediate-materialization predicate inside the decorated function before its DataFrame is returned. Filtering the decorated symbol afterward only creates a temporary clean DataFrame; it does not change the persisted MLV. Read persisted state back with spark.table("<qualified-mlv-name>").

Use SQL DDL for code-generation or notebook authoring requests where the target runtime supports the MLV SQL parser. Do not repeatedly retry rejected SQL in a live session; switch to the documented PySpark API.

Live execution safety

Keep every Livy operation bounded so a slow session or materialization cannot hold the assistant indefinitely:

• Make session creation, statement submission, and each status read a short, one-shot request.

• Poll session and statement state with an explicit elapsed-time deadline and a fixed sleep interval. Stop waiting for session readiness on dead, error, killed, success, or shutting_down; treat available, error, and cancelled statement states as terminal.

• Reserve time to read persisted state and report the result. If the deadline expires, stop polling and report the last observed state instead of starting another watcher or retry loop.

• Never leave a background PowerShell job, detached process, or unbounded foreground polling command running while waiting for Livy.

Layering patterns

Pattern 1: Source-aligned Silver MLV

Use one MLV per important Bronze source when you need:

• type cleanup

• validation

• null/range checks

• basic derived columns

• a stable foundation for downstream joins

-- Enable CDF on the SOURCE so this MLV can incrementally refresh.
-- TBLPROPERTIES on the MLV itself only helps DOWNSTREAM MLVs that read from it.
ALTER TABLE bronze.orders SET TBLPROPERTIES (delta.enableChangeDataFeed = true);

CREATE OR REPLACE MATERIALIZED LAKE VIEW silver.orders_clean
(
    CONSTRAINT valid_order_id CHECK (order_id IS NOT NULL) ON MISMATCH DROP,
    CONSTRAINT positive_amount CHECK (amount > 0) ON MISMATCH DROP
)
PARTITIONED BY (order_date)
TBLPROPERTIES (delta.enableChangeDataFeed = true)  -- for DOWNSTREAM IR consumers
AS
SELECT
    order_id,
    customer_id,
    product_id,
    order_date,
    CAST(amount AS DECIMAL(12,2)) AS amount
FROM bronze.orders;

Pattern 2: Denormalized Silver MLV

Use a joined Silver MLV when Gold should aggregate over a clean, stable analytical grain.

-- All three sources must have CDF enabled for this MLV to incrementally refresh.
-- (silver.orders_clean already has CDF from Pattern 1; add it to the other two.)
ALTER TABLE silver.customers_clean SET TBLPROPERTIES (delta.enableChangeDataFeed = true);
ALTER TABLE silver.products_clean  SET TBLPROPERTIES (delta.enableChangeDataFeed = true);

CREATE OR REPLACE MATERIALIZED LAKE VIEW silver.order_details
PARTITIONED BY (order_date)
TBLPROPERTIES (delta.enableChangeDataFeed = true)  -- for DOWNSTREAM IR consumers
AS
SELECT
    o.order_id,
    o.order_date,
    o.amount,
    c.customer_name,
    c.region,
    p.category
FROM silver.orders_clean o
INNER JOIN silver.customers_clean c ON o.customer_id = c.customer_id
INNER JOIN silver.products_clean p ON o.product_id = p.product_id;

Pattern 3: Gold aggregate MLV

Use Gold MLVs for business-facing metrics and reusable summary tables.

CREATE OR REPLACE MATERIALIZED LAKE VIEW gold.daily_revenue
AS
SELECT
    order_date,
    region,
    COUNT(*) AS order_count,
    SUM(amount) AS total_revenue
FROM silver.order_details
GROUP BY order_date, region;

Data quality patterns

Use constraints for deterministic row-level checks.

CREATE OR REPLACE MATERIALIZED LAKE VIEW silver.customers_clean
(
    CONSTRAINT valid_customer_id CHECK (customer_id IS NOT NULL) ON MISMATCH DROP,
    CONSTRAINT valid_email CHECK (email LIKE '%@%') ON MISMATCH DROP
)
TBLPROPERTIES (delta.enableChangeDataFeed = true)
AS
SELECT customer_id, customer_name, email, region
FROM bronze.customers;

Prefer simple expressions. Keep the logic auditable and easy to explain.

Cross-lakehouse and schema organization

Cross-lakehouse reads

Use documented 4-part naming when needed:

SELECT *
FROM WorkspaceName.LakehouseName.bronze.orders;

If a workspace, lakehouse, or schema name contains spaces, wrap that part in backticks: `My Workspace`.LakehouseName.bronze.orders.

Schema organization

For medallion-style design, organize tables and MLVs into schemas such as:

CREATE SCHEMA IF NOT EXISTS bronze;
CREATE SCHEMA IF NOT EXISTS silver;
CREATE SCHEMA IF NOT EXISTS gold;

Keep naming predictable:

• bronze.orders

• silver.orders_clean

• silver.order_details

• gold.daily_revenue

SQL management commands

List MLVs in a schema

SHOW MATERIALIZED LAKE VIEWS IN silver;

Retrieve the original definition

SHOW CREATE MATERIALIZED LAKE VIEW silver.orders_clean;

Update an MLV definition

You cannot alter an existing MLV definition in place. Use CREATE OR REPLACE to overwrite the current definition:

CREATE OR REPLACE MATERIALIZED LAKE VIEW silver.orders_clean AS
SELECT order_id, customer_id, product_id, order_date,
       CAST(amount AS DECIMAL(12,2)) AS amount
FROM bronze.orders;

To rename an MLV without changing its definition:

ALTER MATERIALIZED LAKE VIEW silver.orders_clean RENAME TO silver.orders_clean_v2;

Current limitations

These limitations apply to Spark SQL MLV definitions:

1. Schema and MLV naming — all-uppercase schema names (e.g., MYSCHEMA) are not supported; use mixed case or lowercase. MLV object names are case-insensitive and normalized to lowercase (MyTestView becomes mytestview).

2. No DML statements — INSERT, UPDATE, DELETE cannot target an MLV. Data comes only from the SELECT query.

3. No time-travel queries — VERSION AS OF and TIMESTAMP AS OF are not supported in the MLV definition.

4. No user-defined functions — UDFs are not supported in MLV definitions.

5. OR REPLACE and IF NOT EXISTS cannot be combined in the same statement.

6. Temp views cannot be sources — an MLV must select from persisted tables or other MLVs. Session-scoped temp views (createOrReplaceTempView) are not valid sources.

7. Session Spark configs don't apply to scheduled refresh — spark.conf.set(...) values set interactively are not carried into scheduled refresh runs. Set properties at the lakehouse or workspace level instead.

See the Spark SQL Reference for the complete and current list of limitations.

PySpark MLVs (Preview)

PySpark-authored MLVs (defined with import fmlv and the @fmlv.materialized_lake_view decorator on a function that returns a DataFrame) are supported but have trade-offs:

• No incremental refresh — PySpark MLVs always use full refresh.

• Lineage-schedule refresh only — cannot refresh on-demand via notebook as with Spark SQL-based views.

• replace=True replaces the definition, not the current rows — existing data changes only on the next lineage refresh.

• Immediate readback needs an explicit transformation — when the user requires the newly registered result to be clean before a lineage refresh, apply the same predicate inside the decorated function before returning its DataFrame, then read the persisted target with spark.table(...). Keep @fmlv.check(..., action="DROP") as the durable refresh-time rule. Do not filter only a temporary post-registration DataFrame or report that the check removed rows unless persisted readback proves it.

• Renaming — the MLV object itself can be renamed via ALTER MATERIALIZED LAKE VIEW ... RENAME TO ... (see the SQL syntax above; applies to both SQL- and PySpark-authored MLVs). If you instead change the @fmlv.materialized_lake_view decorator name in code, you have to drop and recreate.

• Use PySpark when you need complex transformation logic, reusable functions, external Python libraries, or custom UDFs.

Running spark.sql("CREATE MATERIALIZED LAKE VIEW ...") from a notebook cell is a Spark SQL MLV (it keeps optimal/incremental refresh), not a PySpark-authored MLV.

When incremental refresh matters, prefer Spark SQL notebooks over PySpark.

See the PySpark Reference for notebook organization best practices and current limitations.

Refresh and orchestration guidance

MLVs define durable data products. Refresh orchestration belongs to the Lakehouse, not to notebook or pipeline schedulers.

[!TIP] Manage MLV refresh from your Lakehouse, not notebooks

Once your MLVs are created, rely on the Lakehouse's built-in capabilities instead of orchestrating refresh from notebooks:

• Lineage — Fabric derives dependency order from MLV definitions automatically. Open the Materialized lake views tab → Manage to view the graph and follow a run in progress.

• Scheduled refresh — Create one or more schedules to refresh all MLVs or a selected subset. Each schedule runs views in dependency order and retries transient failures.

Use notebooks to author and iterate on MLV definitions; use Lakehouse lineage and schedules to handle ordering, refresh, and retries.

Scheduled refresh

Use the Lakehouse Materialized lake views tab → Manage → Schedules to configure:

• Repeat cadence: minute, hourly, daily, weekly, or monthly

• Optimal refresh toggle (default On): Fabric automatically picks incremental or full refresh per view

• Extended lineage: refresh chains across multiple lakehouses in dependency order from a single schedule. When MLVs in Lakehouse B depend on tables in Lakehouse A, a single schedule on Lakehouse B refreshes Lakehouse A's MLVs first, then B's — no need for separate schedules per lakehouse. See Manage lineage for cross-lakehouse dependency visualization.

Key behaviors (per current Fabric documentation — confirm against the refresh reference as platform limits may change):

• A refresh run fails if it exceeds 24 hours

• If a new refresh starts while another is in progress, Fabric skips the later one

For programmatic schedule management (create/update/delete schedules, trigger on-demand refresh via code), use the MLV Public REST API. For conversational schedule management via Copilot, see the the mlv mode skill.

Manual refresh order

Prefer the lakehouse Materialized lake views → Manage schedule/lineage view for routine refresh. The SQL below is the documented way to force a one-time full refresh of an individual MLV (for troubleshooting or after a correction); FULL is the only documented REFRESH MATERIALIZED LAKE VIEW form.

REFRESH MATERIALIZED LAKE VIEW silver.orders_clean FULL;
REFRESH MATERIALIZED LAKE VIEW silver.customers_clean FULL;
REFRESH MATERIALIZED LAKE VIEW silver.order_details FULL;
REFRESH MATERIALIZED LAKE VIEW gold.daily_revenue FULL;

Recommended sequence:

1. source-aligned Silver MLVs

2. denormalized Silver MLVs

3. Gold MLVs

4. maintenance steps on a slower cadence

Modeling tradeoffs

Exact distinct counts

If the user requests exact distinct counts, explain that:

• the requirement is valid

• the design may be less refresh-friendly

• one option is to pre-deduplicate earlier in the flow

• another option is to accept that this MLV may not be the most incremental-refresh-friendly shape

Rankings and moving windows

If the user requests ranking, lag/lead, or moving windows:

• keep the base curated dataset in an MLV

• move the ranking/window logic to a notebook or consuming layer

Presentation logic

If the user requests rounding, formatting, or report-only columns:

• store raw business measures in the MLV

• apply presentation formatting downstream

Routing guidance for the agent

Use this resource when the user asks about:

• materialized lake views

• MLV authoring

• designing Silver/Gold tables with MLVs

• MLV constraints

• CREATE MATERIALIZED LAKE VIEW

• refresh ordering for MLV-based layers

• medallion design implemented directly with MLVs

Escalate to e2e-medallion-architecture or FabricDataEngineer when the request becomes:

• multi-workspace architecture

• end-to-end Bronze → Silver → Gold orchestration

• pipeline design across multiple workloads

• Power BI + Spark + pipeline coordinated rollout

Official documentation references