Forum Discussion
MLV Constraints
Hello,
This is anopen discussion on the topic of constraints in a materialized lake view in Fabric.
As things stand right now, these are the only constraint options available for use:
(CONSTRAINT constraint CHECK (condition) on MISMATCH DROP)
(CONSTRAINT constraint CHECK (condition) on MISMATCH FAIL)Fail just blocks the MLV from being written, as it signals a problem.
Drop removes a particular row from the MLV that's being written.
Generating an auto quality report from the MLV management view using DROP as a constraint would show you how many rows have been faulty and thus not written in the MLV.
My opening questions would be:
1. Does anyone have an idea if/when Microsoft will expand the constraint usage to somehow flag dropped rows, so you can then view exactly the information in these rows and identify the source of the issue?
2. Did any of you find a workaround to display the dropped rows, but without a lot of extra manual work (e.g. creating another table/MLV with the dropped rows and then using this as a source to build a Power BI report on top)?
Thanks!
Hi dragospopescu,
Great question, this is indeed a common limitation with MLV constraints today in Microsoft Fabric.
At the moment, there is no native mechanism to capture or inspect individual rows dropped by:
CHECK (...) ON MISMATCH DROP
You can only see aggregated statistics in the MLV management views, but not the actual rejected records.
A couple of workarounds are commonly used:
- Pre-validation pattern
Before writing to the MLV, run a Spark notebook or transformation step that applies the same constraint logic.
Valid rows go to the MLV source, while invalid rows are written to a separate Delta table (for example "rejected_rows").
This creates a simple “quarantine table” pattern that preserves full traceability. - FAIL constraint with controlled retry
Another option is to use "ON MISMATCH FAIL" and orchestrate the refresh from a notebook or pipeline.
If the refresh fails, you can run a diagnostic query on the source data to identify violating rows, log them to a table, correct the issue, and then retry the refresh.
Finally, it might be worth submitting or upvoting a request on the Fabric Ideas forum.
A built-in "soft reject with logging” mode for MLV constraints would greatly improve data observability and troubleshooting.Hope this helps!
Drop a kudo so other can find it !
- Pre-validation pattern
2 Replies
Hi dragospopescu,
Great question, this is indeed a common limitation with MLV constraints today in Microsoft Fabric.
At the moment, there is no native mechanism to capture or inspect individual rows dropped by:
CHECK (...) ON MISMATCH DROP
You can only see aggregated statistics in the MLV management views, but not the actual rejected records.
A couple of workarounds are commonly used:
- Pre-validation pattern
Before writing to the MLV, run a Spark notebook or transformation step that applies the same constraint logic.
Valid rows go to the MLV source, while invalid rows are written to a separate Delta table (for example "rejected_rows").
This creates a simple “quarantine table” pattern that preserves full traceability. - FAIL constraint with controlled retry
Another option is to use "ON MISMATCH FAIL" and orchestrate the refresh from a notebook or pipeline.
If the refresh fails, you can run a diagnostic query on the source data to identify violating rows, log them to a table, correct the issue, and then retry the refresh.
Finally, it might be worth submitting or upvoting a request on the Fabric Ideas forum.
A built-in "soft reject with logging” mode for MLV constraints would greatly improve data observability and troubleshooting.Hope this helps!
Drop a kudo so other can find it !
- Pre-validation pattern
Hi dragospopescu,
We would like to confirm if our community members answer resolves your query or if you need further help. If you still have any questions or need more support, please feel free to let us know. We are happy to help you.
Tamanchu ,Thanks for your prompt responseThank you for your patience and look forward to hearing from you.
Best Regards,
Prashanth Are
MS Fabric community support
