BigQuery table using DataStream gives CDC error

[makoto1]

Hello,

The following error occurs when creating a Materialized View on a table using DataStream and executing a SELECT on the Matelialized View.

```
Materialized views <project id>:<dataset>.<table name> has a base table <project id>:<dataset>.<table name> with unapplied Change Data Capture(CDC) upsert data within a configured period.
```

The source data comes from an AWS RDS PosgreSQL.
The region is asianortheast-1.

It was normal until around the middle of March, but from April this error occurs.

I have tried below.
・ Recreating the data stream → The above error still appears
・ Recreate the table of the data stream source → The above error still appears
・ Recreate the replication settings (publication and replication slot) → the above error still appears

Is there any solution?

[Aris_O]

Hi @makoto1,

Welcome to Google Cloud Community.

According to the error message, the Materialized View has a base table containing Change Data Capture (CDC) upsert data that hasn't been applied within the specified time frame. This indicates that due to some configuration or setting, there have been changes in the underlying table that have not been recorded by the Materialized View.

You must make sure that the CDC data from the base table is accurately collected and applied to the Materialized View in order to fix this problem. You can follow the instructions listed below:

• Verify the CDC configuration: Verify that the base table's and the Materialized View's CDC configurations are accurate and current. Verify that the publication parameters and the replication slot are set up correctly.
• Verify the replication lag: Verify that the replication latency between the Materialized View and the base table is not excessively long. Unapplied CDC data may accumulate if the lag is too great, which could interfere with the Materialized View.
  
  
• Consider implementing a trigger-based CDC solution in place of the DataStream if the aforementioned methods are unsuccessful in fixing the problem. Real-time changes to the Materialized View can be seen and applied using a trigger-based CDC solution.
  
  
• Refreshing the Materialized View might help: try it and see if it fixes the problem. By using a command like REFRESH MATERIALIZED VIEW ;, you can update the Materialized View.

Here are some documentation you may use as reference:
https://cloud.google.com/datastream/docs/best-practices-stream-data?_ga=2.180893287.-1392753435.1676...
https://cloud.google.com/datastream-for-bigquery?_ga=2.180893287.-1392753435.1676655686
https://cloud.google.com/datastream/docs/diagnose-issues?_ga=2.214302039.-1392753435.1676655686