Materialized Views in BigQuery Explained

A dashboard that shows daily revenue asks the same question every time it loads: group a very large table by day and add it up. Running that against the base table each time is slow and, on BigQuery, billed each time. Materialized views exist for this. BigQuery stores the result of the query, keeps it up to date in the background, and answers from the stored result. What makes them more than a cached table is that the answer is still correct when the base table has changed since the last refresh.

How a materialized view stays correct

An ordinary view stores a query and runs it on every use. A table built by a scheduled query stores a result and is stale until the next run. A materialized view does something in between (Google Cloud: materialized views):

• It is refreshed for you. BigQuery recomputes the stored data in the background as the base table changes.
• It returns fresh data anyway. If rows have been added since the last refresh, BigQuery reads the stored result and only the new rows from the base table, and combines them.
• It can serve queries that never mention it. With smart tuning, a query written against the base table is rerouted to the view when the view can answer it.

The second point is the one worth seeing in numbers.

The arithmetic, on real data

The script models that behaviour on the New York yellow taxi trips for January 2024 (NYC TLC: trip record data). The view groups trips by day and payment type. Its last refresh saw data up to 30 January; then the trips of 31 January arrived. The model runs on DuckDB and its stored view is an ordinary table. It is not BigQuery; it shows the arithmetic that the documentation describes.

# A model of how a materialized view answers a query after new rows arrive: stored results plus the
# changes since the last refresh. Run on DuckDB with real trip data. It is not BigQuery, and the
# "view" here is an ordinary table; the model shows the arithmetic, not BigQuery's implementation.
import duckdb

con = duckdb.connect()
con.execute("""CREATE TABLE trips AS
    SELECT CAST(tpep_pickup_datetime AS DATE) AS day, payment_type, total_amount
    FROM read_parquet('../../dedata/yellow_tripdata_2024-01.parquet')
    WHERE CAST(tpep_pickup_datetime AS DATE) BETWEEN DATE '2024-01-01' AND DATE '2024-01-31'""")

VIEW_QUERY = "SELECT day, payment_type, COUNT(*) AS trips, SUM(total_amount) AS revenue FROM {source} GROUP BY day, payment_type"
REFRESHED_UP_TO = "2024-01-30"                  # the last refresh saw everything up to this day

con.execute("CREATE TABLE stored_view AS " +
            VIEW_QUERY.format(source=f"(SELECT * FROM trips WHERE day <= DATE '{REFRESHED_UP_TO}')"))
con.execute(f"CREATE VIEW appended AS SELECT * FROM trips WHERE day > DATE '{REFRESHED_UP_TO}'")

QUESTION = "SELECT COUNT(DISTINCT day), SUM(trips), ROUND(SUM(revenue), 2) FROM ({source})"
count = lambda table: con.execute(f"SELECT COUNT(*) FROM {table}").fetchone()[0]

answers = [
    ("base table, full recompute", VIEW_QUERY.format(source="trips"), count("trips")),
    ("stored view only (stale)", "SELECT * FROM stored_view", count("stored_view")),
    ("stored view + appended rows", "SELECT * FROM stored_view UNION ALL " + VIEW_QUERY.format(source="appended"),
     count("stored_view") + count("appended")),
]
print(f"{'how the question is answered':31} {'rows read':>10} {'days':>5} {'trips':>10} {'revenue':>15}")
for label, source, rows_read in answers:
    days, trips, revenue = con.execute(QUESTION.format(source=source)).fetchone()
    print(f"{label:31} {rows_read:10,} {days:5} {trips:10,} {revenue:15,.2f}")

# A change that is not an append: negative amounts on one day are corrected in the base table.
DAY = "2024-01-10"
fixed = con.execute(f"SELECT COUNT(*) FROM trips WHERE day = DATE '{DAY}' AND total_amount < 0").fetchone()[0]
con.execute(f"UPDATE trips SET total_amount = 0 WHERE day = DATE '{DAY}' AND total_amount < 0")
print()
print(f"after an UPDATE of {fixed:,} rows on {DAY}")

still_valid = f"(SELECT * FROM stored_view WHERE day <> DATE '{DAY}')"
from_base = f"(SELECT * FROM trips WHERE day = DATE '{DAY}' OR day > DATE '{REFRESHED_UP_TO}')"
after = [
    ("view not partitioned: recompute", VIEW_QUERY.format(source="trips"), count("trips")),
    ("view partitioned by day", f"SELECT * FROM {still_valid} UNION ALL " + VIEW_QUERY.format(source=from_base),
     count(still_valid) + count(from_base)),
]
for label, source, rows_read in after:
    days, trips, revenue = con.execute(QUESTION.format(source=source)).fetchone()
    print(f"{label:31} {rows_read:10,} {days:5} {trips:10,} {revenue:15,.2f}")

how the question is answered     rows read  days      trips         revenue
base table, full recompute       2,964,606    31  2,964,606   79,455,941.50
stored view only (stale)               150    30  2,862,486   76,770,043.55
stored view + appended rows        102,270    31  2,964,606   79,455,941.50

after an UPDATE of 1,009 rows on 2024-01-10
view not partitioned: recompute  2,964,606    31  2,964,606   79,481,416.70
view partitioned by day            197,265    31  2,964,606   79,481,416.70

• A full recompute reads 2,964,606 rows. This is what every dashboard load costs without a view.
• The stored view alone is small and wrong. Its 150 rows cover 30 days, and the total is short by a day.
• The stored view plus the appended rows is right. It reads 102,270 rows, 3.4% of the table, and returns exactly the full answer: 31 days, 2,964,606 trips, the same revenue to the cent.

That is why an incremental materialized view can promise current results without rescanning the table.

The second half of the output shows the limit of the trick. After an UPDATE to rows on 10 January, the stored figures for that day are no longer valid. The documentation is explicit that updates and deletions prevent incremental updating, and that BigQuery then falls back to the original query until the view is refreshed (Google Cloud: use materialized views). Partitioning limits the damage: when the view is partitioned in line with the base table, each partition is judged separately, so only the changed day is recomputed. In the model that is 197,265 rows instead of 2,964,606, with the same answer.

The lesson for design: materialized views suit tables that are appended to. A base table that is frequently updated or deleted from gives little benefit unless the view is partitioned.

Creating one

CREATE MATERIALIZED VIEW analytics.daily_revenue
PARTITION BY DATE(pickup_day)
CLUSTER BY payment_type
OPTIONS (enable_refresh = true, refresh_interval_minutes = 60)
AS
SELECT TIMESTAMP_TRUNC(pickup_at, DAY) AS pickup_day,
       payment_type,
       COUNT(*) AS trips,
       SUM(total_amount) AS revenue
FROM analytics.trips
GROUP BY pickup_day, payment_type;

The statement follows the documented form for a base table partitioned by day on pickup_at (Google Cloud: create materialized views). The view's partitions must line up with the base table's, so the truncation in the view cannot be coarser than the base table's partitions. Two pieces of advice from the same page shape a good definition:

• Design for many queries, not one. Group by the columns people filter on instead of baking a filter such as one customer into the view. A view grouped by day and payment type answers questions by day, by payment type, by month and overall.
• In a join, put the fact table first. A view with joins can be updated incrementally only when the first, leftmost table is appended to. A change to any other table invalidates it.

Refresh and staleness

By default the stored data is refreshed within 5 to 30 minutes of a change to the base table, and no more often than every 30 minutes. That cap is the refresh_interval_minutes option, which can be set from 1 minute to 7 days. Automatic refresh is best-effort, and a manual refresh is always available (Google Cloud: manage materialized views):

ALTER MATERIALIZED VIEW analytics.daily_revenue
SET OPTIONS (refresh_interval_minutes = 120);

CALL BQ.REFRESH_MATERIALIZED_VIEW('analytics.daily_revenue');

Freshness has a price. When the view has not been refreshed recently, combining it with the changes costs more than reading it alone. For dashboards that do not need the last few minutes, the max_staleness option trades freshness for cost:

CREATE MATERIALIZED VIEW analytics.daily_revenue_relaxed
OPTIONS (enable_refresh = true, refresh_interval_minutes = 60,
         max_staleness = INTERVAL "4:0:0" HOUR TO SECOND)
AS
SELECT DATE(pickup_at) AS day, COUNT(*) AS trips
FROM analytics.trips
GROUP BY day;

If the last refresh was within the interval, BigQuery answers from the view alone and does not touch the base table. Outside it, the view and the changes are combined as usual. The interval must be between 30 minutes and 3 days.

What is not allowed

Incremental views accept a restricted form of SQL. The main restrictions:

• Aggregates must be outputs. You can store COUNT(*), but not COUNT(*) / 10, and you cannot filter or join on an aggregate. Do the arithmetic in the query that reads the view.
• Only certain aggregate functions, including COUNT, COUNTIF, SUM, AVG, MIN, MAX, ANY_VALUE and APPROX_COUNT_DISTINCT.
• No window functions, self-joins or user-defined functions.
• No non-deterministic functions such as CURRENT_DATE() or RAND().
• No nesting. A materialized view cannot be built on another one.
• No editing. The query of a view cannot be changed after creation; drop it and create it again.

A non-incremental view, created with allow_non_incremental_definition = true, accepts most SQL. It gives up the two best properties in exchange: every refresh runs the full query, results are only as fresh as the last refresh, and smart tuning does not apply.

One more limit is commercial. In the Standard edition, existing materialized views can be queried directly but not created or refreshed. Creation, refresh and smart tuning need the Enterprise editions or on-demand pricing (Google Cloud: BigQuery editions).

Materialized view, or a table built on a schedule?

Choose a materialized view when the query is a plain aggregation and the base table grows by appending. Choose a scheduled query when the logic is beyond what a view may contain, or when daily freshness is enough.

Checking that it works

A view that is never used is a cost with no benefit. After creating one:

1. Run a dry run of a dashboard query. The estimate reflects the rewrite, so a much smaller figure means the view is being used.
2. Look at the query plan, which shows whether a materialized view was scanned and how many bytes came from it and from the base table.
3. Compare bytes billed before and after for the queries it was built to serve.

For the pricing behind those bytes, see BigQuery cost optimization. For the table design that makes views effective, see partitioning and clustering.

Warehouse modelling and performance are covered in the Data Engineering course.

Sources

• Introduction to materialized views (Google Cloud Documentation) — Google Cloud
• Create materialized views (Google Cloud Documentation) — Google Cloud
• Use materialized views (Google Cloud Documentation) — Google Cloud
• Manage materialized views (Google Cloud Documentation) — Google Cloud
• Understand BigQuery editions (Google Cloud Documentation) — Google Cloud
• TLC Trip Record Data (NYC Taxi and Limousine Commission) — NYC Taxi and Limousine Commission