Spark SQL reference for materialized lake views

This article provides the Spark SQL syntax for creating, listing, renaming, and deleting materialized lake views in Fabric.

Create a materialized lake view

You can define a materialized lake view from any table or from another materialized lake view within a lakehouse.

Syntax

CREATE [OR REPLACE] MATERIALIZED LAKE VIEW [IF NOT EXISTS] [workspace.lakehouse.schema].MLV_Identifier 
[( 
    CONSTRAINT constraint_name1 CHECK (condition expression1) [ON MISMATCH DROP | FAIL],  
    CONSTRAINT constraint_name2 CHECK (condition expression2) [ON MISMATCH DROP | FAIL] 
)]
[(
    REFRESH_HINT <hint_name> UNIQUE (<column1> [, <column2>, ...])
)]
[PARTITIONED BY (col1, col2, ... )] 
[COMMENT "description or comment"] 
[TBLPROPERTIES ("key1"="val1", "key2"="val2", ... )] 
AS select_statement 

Arguments

Examples

The following example creates a materialized lake view with a data quality constraint, a comment, and partitioning. The OR REPLACE clause overwrites any existing view with the same name.

CREATE OR REPLACE MATERIALIZED LAKE VIEW silver.cleaned_order_data
(
    CONSTRAINT valid_quantity CHECK (quantity > 0) ON MISMATCH DROP
)
PARTITIONED BY (category)
COMMENT "Cleaned order data joined from products and orders"
AS SELECT 
    p.productID,
    p.productName,
    p.category,
    o.orderDate,
    o.quantity,
    o.totalAmount
FROM bronze.products p INNER JOIN bronze.orders o
ON p.productID = o.productID

The following example creates a simpler materialized lake view. The IF NOT EXISTS clause prevents an error if the view already exists, making it safe for deployment scripts.

CREATE MATERIALIZED LAKE VIEW IF NOT EXISTS silver.products_with_sales AS
SELECT 
    p.productID,
    p.productName,
    p.category,
    CASE  
        WHEN COUNT(o.orderID) OVER (PARTITION BY p.productID) > 0 THEN TRUE  
        ELSE FALSE  
        END AS has_sales 
FROM bronze.products p LEFT JOIN bronze.orders o 
ON p.productID = o.productID

Ingest files with USING OneLake_Files

In addition to defining a materialized lake view from tables, you can define one that ingests CSV or Parquet files directly from OneLake. A file-ingesting view uses USING OneLake_Files with a physical OneLake folder, a OneLake folder shortcut, or a single-file path. You can optionally use a FROM-less AS SELECT clause to filter, shape, and aggregate the source as it's ingested.

The following table shows which syntax applies to each authoring style:

Syntax

CREATE [OR REPLACE] MATERIALIZED LAKE VIEW [IF NOT EXISTS] [workspace.lakehouse.schema].MLV_Identifier
[(
    CONSTRAINT constraint_name CHECK (condition) ON MISMATCH DROP | FAIL
)]
USING OneLake_Files
OPTIONS (
    'format' = 'csv' | 'parquet',
    'path'   = 'abfss://<workspace>@<host>/<lakehouse>/Files/<folder-or-file>',
    ['header' = 'true' | 'false',]
    ['delimiter' = '<char>',]
    [...]
)
[PARTITIONED BY (column_name [, ...])]
[COMMENT "description"]
[TBLPROPERTIES (
    'schema_mode'  = 'DYNAMIC' | 'FIXED',
    'refresh_mode' = 'APPEND_ONLY' | 'FULL' | 'MIRROR'
)]
[AS SELECT select_expression [...] [WHERE condition]
    [GROUP BY expression [, ...]] [HAVING condition]]

OPTIONS reference

TBLPROPERTIES reference

Example

The following example ingests a folder of CSV files with a header row into a bronze view. Each row also gets a __filepath__ column that records the source file it was read from.

CREATE MATERIALIZED LAKE VIEW bronze.raw_orders
USING OneLake_Files
OPTIONS (
    'format' = 'csv',
    'path'   = 'abfss://SalesWorkspace@onelake.dfs.fabric.microsoft.com/SalesLake.Lakehouse/Files/orders/',
    'header' = 'true'
)
TBLPROPERTIES (
    'schema_mode'  = 'DYNAMIC',
    'refresh_mode' = 'APPEND_ONLY'
);

You can then build downstream silver and gold materialized lake views that select from bronze.raw_orders; Fabric records the dependency and refreshes them in order. To trace files through the pipeline, see Manage Fabric materialized lake views lineage.

Filter, transform, and validate files during ingestion

Use a FROM-less AS SELECT to select columns, filter rows, cast values, add computed columns, aggregate data, or apply window functions. File-ingestion constraints run against the projected output, so a constraint can reference a computed alias.

The following example captures malformed rows, applies a row-level data quality rule, filters the input, and partitions the materialized output:

CREATE MATERIALIZED LAKE VIEW silver.valid_orders
(
    CONSTRAINT positive_amount
        CHECK (order_amount > 0) ON MISMATCH DROP
)
USING OneLake_Files
OPTIONS (
    'format' = 'csv',
    'path' = 'abfss://SalesWorkspace@onelake.dfs.fabric.microsoft.com/SalesLake.Lakehouse/Files/orders/',
    'header' = 'true',
    'multiLine' = 'true',
    'dateFormat' = 'MM/dd/yyyy',
    'timestampFormat' = 'MM/dd/yyyy HH:mm:ss',
    'encoding' = 'ISO-8859-1',
    'comment' = '#',
    'columnNameOfCorruptRecord' = '_corrupt_record'
)
PARTITIONED BY (region)
COMMENT "Valid orders ingested from CSV"
TBLPROPERTIES (
    'schema_mode' = 'DYNAMIC',
    'refresh_mode' = 'FULL'
)
AS SELECT
    order_id,
    region,
    CAST(amount AS DECIMAL(18,2)) AS order_amount,
    CASE WHEN amount >= 1000 THEN 'high' ELSE 'standard' END AS order_band,
    _corrupt_record,
    __filepath__,
    current_timestamp() AS ingested_at;

Query captured rows for inspection or remediation:

SELECT
    _corrupt_record,
    __filepath__,
    ingested_at
FROM silver.valid_orders
WHERE _corrupt_record IS NOT NULL;

You can also create aggregated or windowed materialized results directly from files:

CREATE MATERIALIZED LAKE VIEW gold.sales_by_region
USING OneLake_Files
OPTIONS (
    'format' = 'parquet',
    'path' = 'abfss://SalesWorkspace@onelake.dfs.fabric.microsoft.com/SalesLake.Lakehouse/Files/sales/'
)
TBLPROPERTIES ('schema_mode' = 'DYNAMIC', 'refresh_mode' = 'FULL')
AS SELECT
    region,
    COUNT(*) AS order_count,
    SUM(amount) AS total_amount,
    AVG(amount) AS average_amount
GROUP BY region
HAVING SUM(amount) > 0;

Supported expressions include standard Spark SQL arithmetic, aggregate, statistical, window, and ranking functions.

Ingest a single file

Set path to a CSV or Parquet file instead of a folder. A single-file source uses FULL refresh semantics so replacing the file replaces the materialized result.

CREATE MATERIALIZED LAKE VIEW bronze.monthly_snapshot
USING OneLake_Files
OPTIONS (
    'format' = 'parquet',
    'path' = 'abfss://SalesWorkspace@onelake.dfs.fabric.microsoft.com/SalesLake.Lakehouse/Files/snapshots/2026-08.parquet'
)
TBLPROPERTIES (
    'schema_mode' = 'FIXED',
    'refresh_mode' = 'FULL'
);

Get a list of materialized lake views

To get the list of all materialized lake views in a schema, use the following syntax:

SHOW MATERIALIZED LAKE VIEWS <IN/FROM> Schema_Name;

For example, to list all materialized lake views in the silver schema:

SHOW MATERIALIZED LAKE VIEWS IN silver;

Retrieve the statement that created a materialized lake view

To get the CREATE statement for a materialized lake view, use the following syntax:

SHOW CREATE MATERIALIZED LAKE VIEW MLV_Identifier;

For example, to retrieve the definition of products_with_sales:

SHOW CREATE MATERIALIZED LAKE VIEW products_with_sales;

Update a materialized lake view

To modify the definition of a materialized lake view (such as the SELECT query, constraints, or partitioning), use the CREATE OR REPLACE command. Alternatively, you can delete the existing view and recreate it.

Rename a materialized lake view

To rename an existing materialized lake view, use the ALTER MATERIALIZED LAKE VIEW command. The syntax is:

ALTER MATERIALIZED LAKE VIEW MLV_Identifier RENAME TO MLV_Identifier_New;

For example, to rename products_with_sales:

ALTER MATERIALIZED LAKE VIEW products_with_sales RENAME TO products_with_sales_v2;

Delete a materialized lake view

You can delete a materialized lake view by using the Delete option in the lakehouse object explorer or by running a DROP command. The syntax is:

DROP MATERIALIZED LAKE VIEW MLV_Identifier;

For example, to delete products_with_sales:

DROP MATERIALIZED LAKE VIEW products_with_sales;

Current limitations

The following limitations apply to the Spark SQL statements for materialized lake views:

• Schema names — All-uppercase schema names (for example, MYSCHEMA) aren't supported. Use mixed case or lowercase.
• No data manipulation language (DML) statements — You can't run INSERT, UPDATE, or DELETE statements against a materialized lake view. Data is populated only by the SELECT query in the definition.
• No time-travel queries — The SELECT query in a materialized lake view definition can't use Delta Lake time travel syntax (for example, VERSION AS OF or TIMESTAMP AS OF).
• No user-defined functions — User-defined functions (UDFs) aren't supported in the SELECT query that defines a materialized lake view.
• No temporary views as sources — The SELECT query can reference tables and other materialized lake views, but not temporary views.
• Session-level Spark properties — Spark configuration properties set at the session level (for example, spark.conf.set(...)) aren't applied during a scheduled refresh. Set properties at the lakehouse or workspace level instead.
• File ingestion — File-ingesting views support only CSV and Parquet. A FIXED-schema view fails if the source folder holds files with different schemas at creation time. Source paths can't contain spaces. OneLake folder shortcuts are supported as sources, but managed refresh doesn't recursively discover files added under nested shortcut folders.

Related content

• What are materialized lake views in Microsoft Fabric?
• Data quality in materialized lake views
• Optimal refresh for materialized lake views