Design a BigQuery materialized view that qualifies for incremental refresh and automatic query rewrite

Steps

1. Keep the defining query within incremental-MV limits: no LEFT/RIGHT/FULL OUTER JOIN, no window functions like ROW_NUMBER(), and aggregate outputs can't be further filtered/computed on or used as clustering columns.
2. If a JOIN is required, ensure only the left-side table(s) receive appended data — incremental MVs can't incrementally absorb append-only changes on the right side of a join.
3. Accept that OUTER JOIN/HAVING/UNION/analytic functions are allowed but make the view non-incremental — losing both incremental refresh and automatic query rewrite (smart tuning).
4. Set max_staleness deliberately: if the view goes more than 3 days without a refresh, queries against it start failing rather than silently returning stale data.
5. Stay under per-base-table caps — 100 materialized views per base table per project, 500 per base table per org — when planning multiple MVs over the same source.
6. Verify smart tuning is engaging by checking the query plan for a READ <materialized_view> step when querying the base table directly; it doesn't engage over tables with active CDC.