Create Materialized Views via DLT Pipelines

Question

Create Materialized Views via DLT Pipelines

Ashwini Gaikwad 130

I am attempting to create materialized views via DLT pipelines in the "abc" catalog and "gold" schema based on different catalog and schema ("fmv"), which contains 20 tables.

Workspace binding , NSG connections and access part is already achieved between both the catalogs.

However, the DLT pipelines fail during the initialization stage with the following error:

"You attempted to update an empty pipeline. This error usually means no tables were discovered in your specified source code. Please verify your source code contains table definitions."

Is it possible to create materialized views in one catalog and schema based on tables residing under different catalog and schema? If so, what are the steps to achieve this?

YutongTie-MSFT 53,966 Reputation points Moderator

2024-11-23T01:00:47.3033333+00:00

@Ashwini Gaikwad Thanks for reaching out to us, we are checking on this issue internally and will let you know soon.

Regards,

Yutong
AnnuKumari-MSFT 34,556 Reputation points Microsoft Employee Moderator

2024-11-25T09:09:25.39+00:00

Hi Ashwini Gaikwad ,

Just following up to see if the below answer helped. Please do consider clicking Accept Answer as accepted answers help community as well. Also, please click on Yes for the survey 'Was the answer helpful'
Ashwini Gaikwad 130 Reputation points

2024-11-25T11:01:10.53+00:00

Please check the description of code and error below
Ashwini Gaikwad 130 Reputation points

2024-11-25T11:05:45.7566667+00:00

@AnnuKumari-MSFT Thank you for your response. But there is no issue with permissions, SPN has read permissions on source side and full write permissions on destination side.

Below is my code

But still I am getting an error :

Can you try to build the same scenario in your environment and can you please let me know if it works for you?

Regards,

Ashwini

2 answers

Your answer

YutongTie-MSFT 53,966 Reputation points Moderator

2024-11-23T01:00:47.3033333+00:00

@Ashwini Gaikwad Thanks for reaching out to us, we are checking on this issue internally and will let you know soon.

Regards,

Yutong
AnnuKumari-MSFT 34,556 Reputation points Microsoft Employee Moderator

2024-11-25T09:09:25.39+00:00

Hi Ashwini Gaikwad ,

Just following up to see if the below answer helped. Please do consider clicking Accept Answer as accepted answers help community as well. Also, please click on Yes for the survey 'Was the answer helpful'
Ashwini Gaikwad 130 Reputation points

2024-11-25T11:01:10.53+00:00

Please check the description of code and error below
Ashwini Gaikwad 130 Reputation points

2024-11-25T11:05:45.7566667+00:00

@AnnuKumari-MSFT Thank you for your response. But there is no issue with permissions, SPN has read permissions on source side and full write permissions on destination side.

Below is my code

But still I am getting an error :

Can you try to build the same scenario in your environment and can you please let me know if it works for you?

Regards,

Ashwini

Answer 1

AnnuKumari-MSFT 34,556 Microsoft Employee Moderator

Hi Ashwini Gaikwad ,

Thankyou for using Microsoft Q&A platform and thanks for posting your query here.

Based on the provided information, it seems that you are trying to create materialized views via DLT pipelines . However, the DLT pipelines fail during the initialization stage with the error message "You attempted to update an empty pipeline. This error usually means no tables were discovered in your specified source code. Please verify your source code contains table definitions."

It is important to ensure that the necessary permissions and access are granted to the user or service principal creating the materialized views. Specifically, Kindly ensure that the service principal running the DLT pipeline has:

Read access to the source catalog/schema where the base tables reside.
Write access to the target catalog/schema where the materialized views will be created.

Additionally, kindly make sure to use fully qualified table name.

Hope it helps. Kindly let us know how it goes. Thankyou

Ashwini Gaikwad 130 Reputation points

2024-11-27T11:47:22.4566667+00:00

@AnnuKumari-MSFT do you have any update on the above comment?
AnnuKumari-MSFT 34,556 Reputation points Microsoft Employee Moderator

2024-11-27T17:51:59.4733333+00:00

Hi Ashwini Gaikwad ,

I have reached out to the internal team for getting assistance on your followup query. I will reach back to you once I get some response from the team. Thankyou.

Answer 2

AnnuKumari-MSFT 34,556 Microsoft Employee Moderator

Hi Ashwini Gaikwad ,

I got the response from the internal team.

"Seems like in your code there's no Delta Live Table syntax. DLT pipeline is looking for that syntax to run the pipeline. If your goal is to schedule updating the materialized view, you can take a look here on how to do it: Schedule materialized view refreshes

Once you schedule it, then you'll be able to see the job/pipeline created in the workflows tab.

Note: Currently, all DLT tables must reside in same Catalog.Schema. However, in a future release, you will be able to put any Table/MV into different catalog/schemas."Hope it helps. Kindly accept the answer by clicking on Accept answer button. Thankyou

ShaikMaheer-MSFT 38,546 Reputation points Microsoft Employee Moderator

2024-12-02T07:19:12.29+00:00

Hi Ashwini Gaikwad , Just checking if above answer helps. If yes, please consider marking it as Accepted Answer. Please let us know if any further queries. Thank you.

Share via

Create Materialized Views via DLT Pipelines

2 answers

Your answer