Forum Discussion
MLV Constraints
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,
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 !