I am creating a materialized view with a ROW_NUMBER() function in my query. Since it's an analytical query and incremental materialized views do not support those functions, I'm creating a non-incremental view with 30 mins refresh interval and 1 hour staleness limit.
My query looks something like:
CREATE MATERIALIZED VIEW `project.dataset.mat_vw`
AS SELECT * FROM (
SELECT * FROM (
SELECT * FROM `project.dataset.BASE_TABLE`
) WHERE xyz
) WHERE abc
In the project history I can see that bigquery admin refreshes the mat view, but the mat vw doesn't reflect the changes made to base table before the refresh on mat_vw is called.
In this case, since there are derived tables between the base table and materlized view,
- What is the base table?
- Why is it not picking up changes from BASE_TABLE?
- Do refreshes happen every 30 mins as configured, or is it in someway dependent on base table?
- How does the data staleness option work? What is it based on?