Sync Tables: Unity Catalog to Lakebase - Materialized Views triggered mode

[Pat]

Hi,

I am facing some issues syncing Materialized Views into Lakebase with triggered mode, and I actually wonder if this is actually possible.

create materialized view some_catalog.some_schema.some_table
tblproperties(
  'delta.enableChangeDataFeed' = 'true',
  'pipelines.channel'='PREVIEW')
as
select * from some_catalog_src.some_schema_src.some_table_src limit 100;

I've already added params that are needed, you can see below

when trying to create synced table, I get following error:

Incremental sync for table some_catalog_src.some_schema_src.some_table_src requires Auto CDF to identify changes, which is only available in the PREVIEW channel. Set pipeline_channel = PREVIEW to proceed.

This materialized view is created manually, but I have also tested on MV created via SDP (in PREVIEW channel).

this works with tables:

create table some_catalog.some_schema.some_table_tbl
tblproperties(
  'delta.enableChangeDataFeed' = 'true')
as
select * from some_catalog_src.some_schema_src.some_table_tbl_src limit 100;

 The source for triggered sync needs to have CDF enabled:

and I believe I have this enabled, but just noticed it's in Private Preview, so will need to double check:

https://www.reddit.com/r/databricks/comments/1rl264r/materialized_view_change_data_feed_cdf_private/

while I wrote this, I will leave it and double check if I have enabled CDF on MVs

[ShamenParis]

Hi @Pat 

Your research on this is spot on! I was actually really curious about this problem and tested it in my own environment to find the exact solution, and I replicated your exact issue.

MV creation:

Sync to Postgres (Snapshot):

Here is exactly why this is happening and why you are seeing that error:

The issue comes down to the difference between Triggered and Snapshot sync modes for Lakebase Synced Tables:

• Snapshot Mode (Works): This performs a full refresh. It just reads the entire Materialized View as it exists right now and copies it over. It does not need to track incremental changes, which is why it succeeds perfectly.

• Triggered Mode (Fails): This performs an incremental sync. To do this, the background sync pipeline must use Change Data Feed (CDF) to figure out what changed in your MV.
  
  Hope this helps you get your Lakebase sync running!

[emma_s]

Hi,

For the CDC on Materialised views, as the feature is in private preview at the moment you would need to speak to your account team to get it enabled. You also need to be using DBR 18.1 or above on classic compute for it to work.

The workaround would be to have the materialized view copied into a standard delta table and use this for the sync. But the snapshot approach may be the most efficient option.

Thanks,

Emma