IssueTracker

Problem you have encountered:

When querying a materialized view (MV) built on CDC-based tables (populated via DataStream) that recently had their staleness setting changed from 8 hours to 2 hours, the MV returns inflated aggregated metrics (or duplicate rows when not aggregated) compared to running the same query directly on the base tables. This occurs even when the underlying base table data (e.g., for a specific date) remains unchanged beyond the staleness period. Adjusting the MV’s max staleness to exactly twice that of the base tables appears to temporarily mitigate the issue in some cases (not always), but the problem ultimately recurs. Toggling Use cached results on and off or changing the allow_non_incremental_definition parameter (true/false) does not resolve the problem.

What you expected to happen:
The materialized view should produce results identical to those from direct queries on the base tables—without duplicate entries or inflated aggregation values.

Steps to reproduce:

1. Create Dummy Base Tables:
   Assume you have two CDC-based tables: project.dataset.transactions and project.dataset.users. The transactions and users table are continuously updated via Datastream with a known staleness of 2 hours (used to be 8HR a week ago).

2. Create a Materialized View:
   Use the following dummy query as an example:

   CREATE MATERIALIZED VIEW project.dataset.dummy_mv
   OPTIONS (
     enable_refresh = true,
     refresh_interval_minutes = 240,
     max_staleness = INTERVAL "4" HOUR -- two times of the staleness of base tables as required by BQ
   ) AS
   SELECT
       DATE(t.transaction_date) AS transaction_date,
       u.user_id,
       SUM(t.amount) AS total_amount,
       COUNT(*) AS transaction_count
   FROM `project.dataset.transactions` t
   JOIN `project.dataset.users` u
       ON t.user_id = u.user_id
   WHERE t.transaction_date > '2024-01-01'
   GROUP BY transaction_date, u.user_id;
   

3. Query the Materialized View:
   Execute a query similar to:

   SELECT *
   FROM project.dataset.dummy_mv
   WHERE transaction_date = '2025-02-07'
   ORDER BY transaction_count DESC;
   

4. Query the Based tables:

   WITH cte as (
       SELECT
           DATE(t.transaction_date) AS transaction_date,
           u.user_id,
           SUM(t.amount) AS total_amount,
           COUNT(*) AS transaction_count
       FROM `project.dataset.transactions` t
       JOIN `project.dataset.users` u
           ON t.user_id = u.user_id
       WHERE t.transaction_date > '2024-01-01'
       GROUP BY transaction_date, u.user_id
   )
   SELECT *
   FROM cte
   WHERE transaction_date = '2025-02-07'
   ORDER BY transaction_count DESC;
   

   We should see much higher total_amount and transaction_count from the MV when compared to the direct query against the base tables.

Other information (workarounds you have tried, documentation consulted, etc):

• The base tables are updated via CDC (DataStream), and their staleness settings were recently changed from 8 hours to 2 hours.
• Temporarily, setting the MV’s max_staleness to twice the base table’s staleness (i.e., 4 hours) can sometimes mask the issue, but the problem eventually recurs.
• Toggling the allow_non_incremental_definition parameter (true/false) did not resolve the problem.
• We have reviewed the BigQuery Materialized Views documentation and various community discussions, but none provided a definitive solution.
• I am not familiar with the internal workings of BQ, but this behavior may suggest that the smart tuning or max_staleness settings of materialized views may be misaligned with CDC updates, then causing overlapping refresh windows that lead to duplicate processing of data.

Please let me know if further details or additional reproduction steps are needed. Thank you for your assistance.