Fixing [MLV_CONSTRAINT_NON_SCHEMA_COMPLIANT] Error in Microsoft Fabric Materialized Lake Views

If you're working with Microsoft Fabric Materialized Lake Views and run into this error:

[MLV_CONSTRAINT_NON_SCHEMA_COMPLIANT] The provided constraint: {contstrain name} is not schema-compliant

The root cause is that you've defined a CONSTRAINT on a column that isn't included in your SELECT statement. This error essentially tells you that you're trying to apply a rule to data that you haven't selected to be part of your view.

Example table:

The Problem (Missing Column)

In this code, you are creating a materialized view with a constraint on CompanyId. However, the SELECT statement only pulls SalesRepId and SalesRep, omitting CompanyId. This is what triggers the error.

CREATE MATERIALIZED LAKE VIEW silver.salesteam
(
    CONSTRAINT  salesteam_company_not_null CHECK (CompanyId IS NOT NULL) ON MISMATCH DROP
)
AS
SELECT
    SalesRepId,
    SalesRep
FROM bronze.salesteam;

The Solution (Including the Column)

To fix this, you simply need to include the CompanyId column in your SELECT statement. This ensures the column exists in the materialized view, allowing the constraint to be applied successfully.

CREATE MATERIALIZED LAKE VIEW silver.salesteam
(
    CONSTRAINT  salesteam_company_not_null CHECK (CompanyId IS NOT NULL) ON MISMATCH DROP
)
AS
SELECT
    SalesRepId,
    SalesRep,
    CompanyId
FROM bronze.salesteam;

By making this small change, you ensure your materialized lake view's schema is compliant with the constraints you've defined.