MATERIALZIED VIEW TRIGGER ON UPDATE with external table as upstream table

[tana_sakakimiya]

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

[nayan_wylde]

@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;

[Khaja_Zaffer]

Hello @nayan_wylde 

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

[tana_sakakimiya]

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

[nayan_wylde]

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. 

[nayan_wylde]

@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.

[tana_sakakimiya]

@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.

[tana_sakakimiya · Accepted solution]

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