azuredatabricksdelta-live-tablesdatabricks-unity-catalog

Materialize View creation not supported from DLT Pipeline

I have a very basic scenario where I am using my custom catalog and want materialize view to get created from DLT table at the end of pipeline. The SQL used as below for the same.

enter image description here

where "loom_data_transform" is a Streaming table. But pipeline fails at the last stage where materialize view is tried to create from "loom_data_transform" with following error message.

enter image description here

org.apache.spark.sql.AnalysisException: '__materialization_mat_loom_data_report_1' was read as a stream (i.e. using `readStream` or `STREAM(...)`), but '__materialization_mat_loom_data_report_1' is not a streaming table. Either add the STREAMING keyword to the CREATE clause or read the input as a table rather than a stream.

Could someone please help me on this issue.

Solution

  • Finally, I able to resolve it but the fix is really surprising. When Materialize view need to be created from stream table as base, the syntax should be

    live.loom_data_transform

    in place of

    STREAM(live.loom_data_transform)