topic Re: MATERIALZIED VIEW TRIGGER ON UPDATE with external table as upstream table in Data Engineering

MATERIALZIED VIEW TRIGGER ON UPDATE with external table as upstream table

tana_sakakimiya — Fri, 12 Sep 2025 12:27:56 GMT

goal: implement event driven architecture without trigger on file arrival

I would like to know whether materialzied view can update itself when its source table which is external table updated.

given that the source external table referencing data in delta format on S3 with this delta.
enableChangeDataFeed= true
delta.enableRowTracking = true
delta.enableDeletionVectors = true

and i configure the materialized view to trigger on update

Re: MATERIALZIED VIEW TRIGGER ON UPDATE with external table as upstream table

nayan_wylde — Fri, 12 Sep 2025 14:00:38 GMT

@tana_sakakimiya Yes you can alter the view to refresh when the source table refreshes.
This feature is in beta now and you might have to reach out to a Databricks Support to enable the feature in your workspace.

This is the syntax for it.

%sql ALTER MATERIALIZED VIEW uc.sales.gold.mv_orders_agg TRIGGER ON UPDATE AT MOST EVERY INTERVAL 1 MINUTE;

Re: MATERIALZIED VIEW TRIGGER ON UPDATE with external table as upstream table

Khaja_Zaffer — Fri, 12 Sep 2025 14:03:03 GMT

Hello @nayan_wylde

Are you sure we can have 1 minute interveral in sql queries? #justasking

Re: MATERIALZIED VIEW TRIGGER ON UPDATE with external table as upstream table

nayan_wylde — Fri, 12 Sep 2025 14:12:52 GMT

@Khaja_Zaffer That is what is mentioned in the Databricks Docs. I have asked our SA to enable this feature will test and share more details.

Re: MATERIALZIED VIEW TRIGGER ON UPDATE with external table as upstream table

tana_sakakimiya — Fri, 12 Sep 2025 14:20:33 GMT

Thank you. I was reluctant because I am not sure whether the on update works with external table.

Re: MATERIALZIED VIEW TRIGGER ON UPDATE with external table as upstream table

tana_sakakimiya — Fri, 12 Sep 2025 14:25:23 GMT

@nayan_wylde

thank you again.

let's see the other contributors responses from databricks engineers before closing this discussion.
Maybe we can receive infomative response about what to be cautious when the source tables are external tables.

Re: MATERIALZIED VIEW TRIGGER ON UPDATE with external table as upstream table

nayan_wylde — Fri, 12 Sep 2025 14:26:14 GMT

yes there is a limitation with external tables currently to 10,000 rows per change set. But if you do file arrival trigger on source it might remove that limitation but it seems you have disabled the file arrival trigger.

Re: MATERIALZIED VIEW TRIGGER ON UPDATE with external table as upstream table

tana_sakakimiya — Sun, 14 Sep 2025 03:07:08 GMT

it seems that my idea is a bad idea because it seems that materialzied view doesn't support incremental udpate for external location

Incremental refresh for materialized views - Azure Databricks | Microsoft Learn