Dr. Estera Kot

FabCon Europe 2026 in 18 demos · Part 18 of 18 · Engineering building blocks

Materialized lake views: a medallion in SQL with drop-on-mismatch rules

Silver and gold as two CREATE statements, quality rules in the definition, and a count of every dropped row.

By Dr. Estera Kot, Microsoft MVP, CTO at Clouds on Mars, and former Microsoft Principal Product Manager for the Apache Spark runtime in Fabric. Published .

A materialized lake view in Microsoft Fabric is a Spark SQL or PySpark query whose result Fabric stores as a Delta table in a lakehouse and keeps refreshed. You write silver and gold as CREATE MATERIALIZED LAKE VIEW statements. Fabric reads the dependencies from the definitions, refreshes views in order, and checks rows against the quality rules you declare.

This note covers demo 16 of FabCon Europe 2026 in 18 demos: three bad rows enter bronze and never reach gold.

In short

  1. Silver and gold are two CREATE MATERIALIZED LAKE VIEW statements. Fabric derives the lineage and refreshes upstream views first.
  2. ON MISMATCH DROP removes failing rows and counts them. FAIL, the default, stops the refresh at the first violation, and dependent views are skipped.
  3. Incremental refresh needs change data feed on every source and supported SQL. PySpark-authored views are in preview and never refresh incrementally.
The notebook 05_mlv_medallion builds bronze, silver, and gold in lh_oud_mlv and appends three bad rows. A run named demo-run then reports Dropped rows 3 on silver.trials_clean. Waits are sped up. 84 seconds, no sound, recorded Oct 21, 2026.

Same demo in Polish and German. Step-by-step runbook: D16.

What materialized lake views are and how they work

The result of a materialized lake view, MLV for short, is an ordinary Delta table that any Fabric engine can query under the lakehouse’s permissions. Fabric takes three jobs off your notebooks. It derives the lineage and refreshes upstream views first. It picks skip, incremental, or full refresh per view. It checks each row against each constraint and counts what it drops. Learn’s quickstart needs a lakehouse with schemas enabled and Fabric Runtime 1.3.

Where the three bad rows drop in the demo medallion Bronze table preference_trials holds 10 rows: 7 copied from lh_oud_rd and 3 bad rows with a share of 140, no formula, and zero testers. Silver view trials_clean applies three CHECK constraints with ON MISMATCH DROP, writes 7 rows, and drops 3. Gold view formula_preference joins silver with bronze.formulas and writes 6 rows, one per formula. Silver refreshes first, gold second. Bronze Silver Gold 1 2 as delivered refreshed first, rules in the definition refreshed second bronze.formulas 6 formulas joined by gold bronze.preference_trials 10 rows after the append 7 rows copied from lh_oud_rd first_choice_pct = 140 formula_id = NULL n_testers = 0 10 silver.trials_clean three rules, all ON MISMATCH DROP pct_valid has_formula has_testers first_choice_pct BETWEEN 0 AND 100 formula_id IS NOT NULL n_testers > 0 7 rows added 7 gold.formula_preference AVG(first_choice_pct) per formula, joined with bronze.formulas 6 rows added 0 dropped OB-104 leads at 68 3 rows dropped each breaks one rule, counted once Run report: Dropped rows 3 Where the three bad rows drop in the demo medallion Top to bottom: bronze.formulas and bronze.preference_trials with 10 rows, 3 of them bad. Silver view trials_clean applies three DROP rules, adds 7 rows, and drops 3. Gold view formula_preference adds 6 rows, one per formula. Bronze as delivered bronze.formulas 6 formulas, joined by gold bronze.preference_trials 10 rows after the append 7 rows copied from lh_oud_rd first_choice_pct = 140 formula_id = NULL n_testers = 0 10 rows 1 Silver refreshed first silver.trials_clean three rules, all ON MISMATCH DROP pct_valid has_formula has_testers first_choice_pct BETWEEN 0 AND 100 formula_id IS NOT NULL n_testers > 0 7 rows added 3 rows dropped 3 rows dropped each breaks one rule Dropped rows 3 7 2 Gold refreshed second gold.formula_preference AVG(first_choice_pct) per formula, joined with bronze.formulas 6 rows added, 0 dropped OB-104 leads at 68
Figure 1. Ten rows reach the silver rules, seven pass, and three drop before gold.

What the demo shows

Synthetic trials for six oud formulas.

  1. 0:00 Notebook 05_mlv_medallion. Cell 2 defines silver.trials_clean with three DROP rules: pct_valid, has_formula, and has_testers.
  2. 0:02 Run all. Cells 1 to 3 fill bronze from lh_oud_rd and create silver and gold.
  3. 0:12 Cell 5 prints “appended to bronze: 3 bad rows.”
  4. 0:16 The gold query, run before the append: one row per formula, OB-104 first at 68.
  5. 0:24 The lineage under Materialized lake views, Manage: two bronze tables, silver, and gold.
  6. 0:30 The On demand lineage refresh pane, Optimal refresh on, run name demo-run.
  7. 0:38 Recent run(s) shows demo-run In progress. It completes at 0:52.
  8. 0:54 Run details, 57 sec 800 ms, with a banner: 1 out of 2 views can run more efficiently.
  9. 1:04 Lineage marks silver done in 22 sec 33 ms, then gold in 16 sec 624 ms.
  10. 1:10 The activity table: silver.trials_clean added 7, dropped 3. gold.formula_preference added 6, dropped 0.

The numbers reconcile: 10 rows in bronze, 7 in silver, one per formula in gold. Silver added all 7 rows, not only the changes. Without change data feed on bronze, optimal refresh can only skip or rebuild, and the banner says so.

Run details pane with the banner: 1 out of 2 materialized lake views can run more efficiently with optimal refresh, Activate CDF, duration 57 sec 800 ms
Run details at 0:56.
Activity table: gold.formula_preference added 6 rows and dropped 0, silver.trials_clean added 7 rows and dropped 3
The activity table at 1:12.

ON MISMATCH DROP versus FAIL

FAIL is the default, even when you don’t write it, and stops the refresh at the first violating row. If one view mixes DROP and FAIL rules, FAIL wins. The run lists the view as Failed and marks its children Skipped.

ON MISMATCH DROP

  1. Row fails a ruleCHECK doesn’t hold.
  2. Row removedLineage counts it.
  3. Refresh continuesValid rows land.
  4. Downstream refreshesGold reads clean silver.

ON MISMATCH FAIL, the default

  1. Row fails a ruleCHECK doesn’t hold.
  2. Refresh stopsAt the first violation.
  3. View FailedError code in the run.
  4. Children skippedNo dependent refresh.
Figure 2. DROP trades the row for a running pipeline. FAIL trades the pipeline for the row.

DROP records only a count. The Data quality report shows drops per view and constraint and can alert on them. In the demo, pct_valid tests validity, has_formula completeness, and has_testers plausibility.

Questions for a data quality review:

Lineage, schedules, and incremental refresh

A lakehouse can hold several named schedules, each refreshing all views or a subset in dependency order. Learn sets a five-minute minimum interval, fails runs past 24 hours, and skips a run that starts during another. Optimal refresh, on by default, decides per view and per run:

  1. D1Optimal refresh on, all sources Delta tables?No: full refresh.
  2. D2New commits on a source since the last refresh?No: skip.
  3. D3Spark SQL definition, change data feed on every source?No: full. PySpark views stop here.
  4. D4Only supported constructs?No: full. Window functions, DISTINCT, and AVG over unpartitioned sources fail here.
  5. D5Only appends, or a refresh hint (preview)?No: full.
  6. YesAll five hold.Incremental, unless a full rebuild is cheaper.

In the demo: silver.trials_clean stops at D3, because bronze has no change data feed. gold.formula_preference stops at D3 too, and would stop at D4 on AVG.

Figure 3. How optimal refresh picks a strategy, condensed from Learn.

SUM, MIN, MAX, and COUNT without DISTINCT skip that partitioning rule unless mixed with other aggregates. Left joins qualify only while the right-side table stays unchanged.

PySpark-authored views (preview)

Decorate a DataFrame function with @fmlv.materialized_lake_view and add @fmlv.check rules, DROP or FAIL, that can call Python UDFs. These views refresh in full or skip, only through the lineage schedule, by running their notebook. Deleting it breaks the schedule.

Limits and catches

The catch: DROP gives you a number, not the rows. Nothing in the lakehouse says which three trials were rejected unless you query bronze with the inverse rule.

If Project Osmos or another agent writes your pipelines, view rules are a contract its output must pass.

Run the demo yourself

python3 fabdemo.py up from the recap repo creates the notebook 05_mlv_medallion and the schema-enabled lakehouse lh_oud_mlv. Key cells:

%%sql
-- Silver: quality rules live in the view definition, bad rows are dropped
CREATE OR REPLACE MATERIALIZED LAKE VIEW silver.trials_clean
(
  CONSTRAINT pct_valid   CHECK (first_choice_pct BETWEEN 0 AND 100) ON MISMATCH DROP,
  CONSTRAINT has_formula CHECK (formula_id IS NOT NULL)             ON MISMATCH DROP,
  CONSTRAINT has_testers CHECK (n_testers > 0)                      ON MISMATCH DROP
)
AS SELECT trial_id, formula_id, panel_segment, n_testers, first_choice_pct, trial_date
FROM bronze.preference_trials
%%sql
-- Gold: preference per formula, Fabric knows to refresh silver first
CREATE OR REPLACE MATERIALIZED LAKE VIEW gold.formula_preference
AS SELECT f.formula_id, f.formula_name, COUNT(*) AS trials, SUM(t.n_testers) AS testers,
          ROUND(AVG(t.first_choice_pct), 1) AS avg_first_choice_pct
FROM silver.trials_clean t JOIN bronze.formulas f ON t.formula_id = f.formula_id
GROUP BY f.formula_id, f.formula_name
# New delivery from the panel: three rows break the quality rules (share 140, no formula, zero testers)
one = spark.table("bronze.preference_trials").limit(1)
bad = (one.withColumn("first_choice_pct", F.lit(140.0))
       .unionByName(one.withColumn("formula_id", F.lit(None).cast("string")))
       .unionByName(one.withColumn("n_testers", F.lit(0).cast(one.schema["n_testers"].dataType))))
bad.write.mode("append").saveAsTable("bronze.preference_trials")
print("appended to bronze:", bad.count(), "bad rows")
  1. Run all in 05_mlv_medallion.
  2. Open lh_oud_mlv > Materialized lake views > Manage.
  3. Select Run, name the run, and run it.
  4. Open Recent run(s) and expand your run’s activity table.

Expected result: gold has one row per formula, and the run shows Dropped rows: 3 on silver.trials_clean.

Time and cost: about 10 minutes on the F8 capacity, per runbook D16. The recap README puts F8 at about 1.41 EUR per hour in France Central at the Azure retail price on October 21, 2026.

What to do on Monday

On Monday: Pilot one silver table as a materialized lake view, and choose DROP or FAIL rule by rule before the first scheduled run.

Key facts at a glance

Checked on Microsoft Learn on November 3, 2026.

IDFactSource
K1GA for Spark SQL views, PySpark authoring in previewLearn: overview
K2ON MISMATCH defaults to FAIL, which wins over DROPLearn: data quality
K3A row is dropped once, even with several violationsLearn: data quality report
K4Optimal refresh picks skip, incremental, or full, on by defaultLearn: optimal refresh
K5Incremental refresh needs change data feed on every sourceLearn: optimal refresh
K6Five-minute minimum schedule interval, runs fail after 24 hoursLearn: schedule a refresh
K7PySpark views refresh in full or skip, through the lineage schedule onlyLearn: PySpark reference

Frequently asked questions

What is the difference between ON MISMATCH DROP and FAIL?

DROP removes each violating row, lets the refresh continue, and reports the count in lineage. FAIL stops the refresh at the first violating row and skips dependent views. FAIL is the default and wins when a view mixes both.

Are materialized lake views generally available in Microsoft Fabric?

Yes, for views defined in Spark SQL. The Microsoft Fabric Updates Blog announced GA on March 18, 2026, and Learn’s What’s new page lists September 2026. PySpark authoring is in preview.

How do I get incremental refresh for a materialized lake view?

Enable Delta change data feed on every source table, use only supported SQL, and leave optimal refresh on. Appends refresh incrementally, while updates and deletes need a refresh hint.

Can I see which rows a constraint dropped?

No. Fabric reports counts in lineage, run details, and the Data quality report, but doesn’t keep the rows. Query the source with the inverse rule to find them.

How often can a materialized lake view refresh?

Schedules have a five-minute minimum effective interval, and runs fail after 24 hours. A refresh that starts while another is running is skipped.

About the author

Dr. Estera Kot is CTO at Clouds on Mars, a Microsoft and Databricks partner for data and AI in Europe and the Middle East. At Microsoft she was Principal Product Manager for the Apache Spark runtime in Azure Synapse Analytics and Microsoft Fabric, and she created the Native Execution Engine. She is a Microsoft MVP and holds a Ph.D. in Computer Science from the Warsaw University of Technology. She presented this series as the FabCon Europe 2026 recap in Gdynia on October 21, 2026, and online on October 23, 2026. Slides, recordings, and runbooks are in the public recap repository.

Sources

Microsoft Learn and the Microsoft Fabric blog, checked on November 3, 2026.