dlt pipeline will not create live tables

[mysteryuser000]

I have created a dlt pipeline based four sql notebooks, each containing between 1 and 3 queries.  Each query begins with "create or refresh live table ..." yet each one outputs a materialized view.  I have tried deleting the materialized views and running the pipeline from scratch but it creates views again.

I can't find any examples of other people having this issue. What am I doing wrong? Below is one of the queries.

create or refresh live table table_name

comment "union of table1 and table2 data"

   as

  select * from live.table1 union select * from live.table2;

[Kaniz_Fatma]

Hi @mysteryuser000, 

1. In Databricks SQL, materialized views are Unity Catalog managed tables that allow you to precompute results based on the latest data in source tables. Unlike other implementations, the results returned reflect the state of data when the materialized v...
2. When you create a materialized view in a Databricks SQL warehouse, a Delta Live Tables pipeline is created to process refreshes to the materialized view. You can monitor the status of refresh operations in the Delta Live Tables UI.
3. Now, let’s address your specific query:

CREATE OR REFRESH LIVE TABLE table_name
COMMENT "union of table1 and table2 data"
AS
SELECT * FROM live.table1 UNION SELECT * FROM live.table2;

• Ensure that live.table1 and live.table2 exist and contain the expected data.
• Confirm that there are no other dependencies (e.g., triggers, views) causing the materialized view to be recreated.
• Remember that materialized views in Databricks SQL reflect the state of data when last refreshed. If the underlying data changes, the materialized view won’t automatically update until explicitly refreshed.
• To manually refresh the materialized view, use the following command:

REFRESH MATERIALIZED VIEW catalog.schema.view_name;

• Check the status of the refresh operation in the Delta Live Tables UI or use the DESCRIBE EXTENDED c...
• You can schedule automatic refreshes for materialized views to keep them up-to-date. Consider setting up a refresh schedule if your data changes frequently.
• Let me know how it goes!