Materialized Views vs. Scheduled Queries: Architecture Comparison

1. Root Cause Analysis: The Paradox of Faster Dashboards and Higher Cloud Bills

In many data engineering organizations, query latency and query cost are treated as interchangeable metrics. If a complex Looker dashboard scans several terabytes of historical data and takes 11 seconds to load, precomputing the aggregation inside a Materialized View reduces the scan volume to a few megabytes and cuts response time to under two seconds. Because every individual dashboard execution now scans far less data, engineering teams naturally expect the monthly BigQuery bill to drop.

In the audited European retail environment, the initial technical rollout appeared completely successful:

  • Daily Event Ingestion: ~1.2 billion customer events per day across seven countries.
  • Transformation Layer: Over 600 scheduled analytical models.
  • Business Intelligence Footprint: ~1,800 internal users accessing dashboards via Looker.
  • Performance Improvement: Average dashboard response times fell from approximately 11 seconds to less than 2 seconds.
  • Organic Business Growth: Average daily traffic increased by 14%, and stored data volume increased by 17%.
  • Financial Outcome: Monthly BigQuery costs increased by 39%.

Executives and business analysts were satisfied with the sub-two-second dashboard responsiveness, but Finance flagged the 39% cost increase as unsustainable because it significantly outpaced the 14% traffic growth and 17% data volume growth.

To identify the exact cause of the billing spike, the engineering team examined Cloud Billing Export records broken down by job type and SKU. The billing telemetry revealed a clear divergence: foreground query costs triggered by Looker users had indeed decreased. However, background computation had increased almost proportionally—and continued to consume resources around the clock, even overnight and on weekends when nobody was opening reports.

Auditing the Platform Inventory

The investigation began with a complete inventory of every analytical acceleration and modeling mechanism deployed across the platform during the preceding year:

Optimization or Modeling TechniqueActive Production CountPrimary Execution TriggerCost Allocation Category
Materialized Views148Automatic background refresh upon base table updates + query-time delta mergeBackground & Foreground Compute + Storage
Scheduled Queries392Fixed time-based cron schedule (BigQuery Data Transfer Service)Batch Compute + Native Table Storage
Incremental Dataform Models176Orchestrated DAG pipeline runsBatch Compute + Native Table Storage
BI Engine Reservations9In-memory caching capacityDedicated Memory Capacity (GB/hour)
Standard Views840On-demand execution when queried by users or BI toolsForeground Compute Only

Taken in isolation, the inventory numbers looked reasonable for an enterprise supporting 1,800 Looker users. The root cause only emerged when engineers joined BigQuery Audit Logs with Looker query execution history to measure how frequently each of the 148 Materialized Views was actually read by human users or downstream reports:

  • 34 Materialized Views (23.0%) were queried hundreds of times every day.
  • 41 Materialized Views (27.7%) were queried only a few times each week.
  • 27 Materialized Views (18.2%) had not been queried for more than 60 days.
  • 19 Materialized Views (12.8%) had never been queried a single time after deployment.
  • 27 Materialized Views (18.2%) showed highly irregular usage tied to occasional operational reviews.

Why Unused Materialized Views Are Not Free

The architecture team initially assumed that the unused and rarely queried Materialized Views represented a negligible expense. Standard Views cost nothing when idle because they are simply saved SQL definitions. Materialized Views, however, store precomputed physical data that must remain synchronized with underlying base tables.

In a modern data platform where pipelines ingest streaming events or micro-batch updates every few minutes, source tables change constantly. Every incoming modification to a base table potentially triggers background maintenance work across all dependent Materialized Views.

To quantify the financial impact at the unit level, the team calculated the monthly economics of one representative reporting model built for a regional team:

  • Actual User Demand: The dashboard was opened an average of 22 times per month.
  • Source Update Frequency: The corresponding Materialized View processed source table updates every few minutes.
  • Monthly Maintenance Cost: Approximately $640 in background refresh compute.
  • Monthly Query Savings: Approximately $74 saved across the 22 foreground dashboard executions.
  • Net Financial Result: A recurring net loss of almost $570 every month ($566 exact difference) on a single view.

While a $570 monthly loss on one view might go unnoticed on an enterprise cloud bill, multiplying that pattern across more than a hundred low-demand views explained the platform-wide surge in background compute. Majority of the company’s Materialized Views were technically correct—they returned accurate data in under a second—but they were economically irrational.

2. Technical Mechanics: How Materialized Views and Scheduled Queries Differ Under the Hood

To understand why a Materialized View can cost fifteen times more than a Scheduled Query or a Standard View for the exact same dataset, we must examine how BigQuery executes and maintains each object based on official Google Cloud architecture principles.

How BigQuery Materialized Views Work

A Materialized View caches the output of an analytical query in managed storage and relies on two mechanisms to keep results accurate:

  1. Automatic Background Refreshes (MATERIALIZED_VIEW_REFRESH):When enable_refresh is active, a background daemon monitors the base tables referenced by the view. When new rows are appended to a base table, BigQuery performs an incremental refresh, scanning only the newly added storage blocks and merging them into the Materialized View. However, if the base table undergoes destructive Data Manipulation Language (DML) operations—such as UPDATE, DELETE, or MERGE statements typical in Change Data Capture (CDC) pipelines—BigQuery invalidates the affected partitions inside the Materialized View. During the next refresh cycle, BigQuery cannot simply process the changed rows; it must re-scan the entire invalidated partition of the base table from scratch. If the Materialized View is not partitioned in exact alignment with the base table, a single updated row in the base table invalidates the entire Materialized View, forcing a full table recomputation.
  2. Real-Time Delta Merging at Query Time:By default, BigQuery guarantees that querying a Materialized View returns data that is just as fresh as querying the base table directly. If the base table receives new streaming or micro-batch writes after the most recent background refresh timestamp (refresh_watermark), any incoming user query reads the precomputed Materialized View plus performs a live scan of the unrefreshed delta in the base table. Unless the engineer explicitly configures the max_staleness option to tolerate slightly older data, high-velocity base tables force foreground queries to scan active base table partitions even when a Materialized View is present.
  3. Smart Tuning (Automatic Query Rewrite):BigQuery can transparently rewrite queries written against base tables to use an existing Materialized View if the query’s aggregations, filters, and joins match the view’s structure. However, if a BI tool generates SQL with unsupported functions, mismatched joins, or columns missing from the Materialized View’s grouping keys, the optimizer silently bypasses the Materialized View and scans the base table directly—while the platform continues paying for background refreshes.

How Scheduled Queries and Incremental Models Work

A Scheduled Query (managed via BigQuery Data Transfer Service, Cloud Workflows, or Dataform incremental models) writes the output of a SQL transformation into a standard destination table on a fixed schedule:

  1. Decoupled Compute Triggers:A Scheduled Query runs strictly according to its cron schedule (for example, once every hour, or once every night at 02:00 UTC). If the upstream source table receives 500 micro-batch updates or MERGE operations during the day, a nightly Scheduled Query ignores those intermediate states and runs exactly once.
  2. Controlled Partition Updates:Engineers have full control over incremental boundaries. Using MERGE or partition overwrite patterns with explicit WHERE filters on the partitioning column, a scheduled transformation scans only the specific time window required by the business logic.
  3. Zero Query-Time Delta Overhead:Because the destination is a standard physical BigQuery table, Looker dashboards querying that table read only the precomputed table blocks. BigQuery never scans the upstream raw tables at dashboard load time.
  4. Full SQL Support and Schema Decoupling:Unlike Materialized Views—which enforce strict limitations on SQL syntax, join types, window functions, and schema modifications—Scheduled Queries support the complete GoogleSQL specification and do not lock upstream base table schemas against DDL changes.

Architectural Comparison Table

Technical DimensionMaterialized ViewScheduled Query / Incremental TableStandard View
Execution TriggerContinuous: triggered by base table data changes + refresh timerPeriodic: triggered strictly by a fixed cron scheduleOn-Demand: triggered only when a human or BI tool runs a query
Cost DriversBackground refresh compute + foreground query scan + MV storageScheduled batch compute + foreground query scan + table storageForeground query scan only (zero background compute, zero storage)
Effect of Base Table MERGE / UPDATEInvalidates modified partitions; forces full partition re-scan on refreshNo immediate effect; processed once during next scheduled runNo background effect; scanned only when queried
Data FreshnessReal-time (via delta merge) or bounded by max_staleness parameterSnapshot matching the timestamp of the last scheduled executionAlways real-time (queries raw source tables directly)
SQL Syntax FlexibilityRestricted subset of SQL (limited joins, aggregations, no window functions)Full GoogleSQL support (CTEs, window functions, complex joins, UDFs)Full GoogleSQL support
Schema Dependency on SourceHigh: source schema changes require MV validation and refresh testingLow: independent physical table; updated via deployment pipelineMedium: breaks at read time if referenced source columns are dropped
Cost When Nobody Opens the DashboardHigh if source table updates frequently (continuous background refreshes)Fixed and predictable based on schedule frequencyExactly $0.00

3. Quantitative Cost Modeling and Production Audit Queries

Rather than debating architectural preferences in abstract terms, the engineering team built a quantitative cost model using 90 days of production telemetry collected from four primary sources:

  1. Cloud Billing Export for verified compute and storage expenditure.
  2. BigQuery INFORMATION_SCHEMA.JOBS for job-level bytes processed, bytes billed, slot milliseconds, and refresh job execution counts.
  3. BigQuery Audit Logs for tracking query origins, referenced tables, and optimizer behavior.
  4. Looker Usage Statistics for mapping database queries to specific business dashboards and active human users.

For every reporting asset on the platform, the team calculated four concrete business metrics:

  • Monthly Maintenance Cost (background refreshes or scheduled executions)
  • Monthly Query Savings (reduction in foreground query scanning cost)
  • Refresh Frequency (how many times per day or month the precomputed state is updated)
  • Actual User Demand (how many times per month humans actually open the report)

WordPress-Safe Mathematical Cost Model

To evaluate any reporting workload over a 30-day billing period without relying on guesswork, define the following operational variables:

  • N_read = Total number of times users or BI dashboards execute the query per month.
  • C_base = Average compute cost of running a single unaccelerated query against the base table (Standard View).
  • C_pre = Average compute cost of reading the precomputed result from a Materialized View or reporting table.
  • C_delta = Average additional compute cost of scanning unrefreshed base table rows at query time (applies to Materialized Views when max_staleness is not set or exceeded).
  • N_refresh = Total number of background refreshes (for a Materialized View) or scheduled runs (for a Scheduled Query) per month.
  • C_refresh = Average compute cost of a single refresh execution or scheduled transformation run.
  • C_storage = Monthly storage cost for the precomputed physical table or Materialized View.

Using these variables, the total monthly cost for each of the three architectural options is calculated as follows:

Plaintext

1. Total Monthly Cost of a Standard View (TC_standard):
   TC_standard = N_read × C_base

2. Total Monthly Cost of a Scheduled Query Table (TC_scheduled):
   TC_scheduled = (N_refresh × C_refresh) + (N_read × C_pre) + C_storage

3. Total Monthly Cost of a Materialized View (TC_mv):
   TC_mv = (N_refresh × C_refresh) + (N_read × (C_pre + C_delta)) + C_storage

From these equations, we can calculate the Monthly Query Savings and the Net Monthly Financial Benefit of introducing a Materialized View over a Standard View:

Plaintext

Monthly Query Savings = N_read × (C_base − (C_pre + C_delta))

Monthly Maintenance Cost = (N_refresh × C_refresh) + C_storage

Net Monthly Benefit = Monthly Query Savings − Monthly Maintenance Cost

A Materialized View only saves money when Monthly Query Savings is greater than Monthly Maintenance Cost. By setting Net Monthly Benefit = 0 and solving for N_read, we obtain the Break-Even Query Demand Threshold:

Plaintext

N_read_breakeven = [(N_refresh × C_refresh) + C_storage] ÷ [C_base − (C_pre + C_delta)]

Applying the Formula to the Representative Retail Model

We can verify this formula using the numbers from the representative regional reporting model described in Section 1:

  • The dashboard was opened 22 times per month (N_read = 22).
  • Monthly query savings across those 22 executions equaled $74, meaning each execution saved approximately $3.36 (C_base − (C_pre + C_delta) = $3.36).
  • Monthly background maintenance cost equaled $640 ((N_refresh × C_refresh) + C_storage = $640).

Substituting those values into the Break-Even formula:

Plaintext

N_read_breakeven = $640 ÷ $3.36 per query = 190.5 queries per month

Because the dashboard only received 22 queries per month—far below the break-even threshold of 191 queries per month—the Materialized View was guaranteed to lose money:

Plaintext

Net Monthly Benefit = $74 − $640 = −$566 (a net financial loss of almost $570 per month)

Production SQL 1: Calculating Monthly Maintenance Cost per Materialized View

Engineers can run the following query against INFORMATION_SCHEMA.JOBS_BY_PROJECT to measure exact refresh frequency, terabytes billed, slot hours consumed, and estimated monthly maintenance cost for every Materialized View in a project:

SQL

-- Calculate 30-day background refresh frequency and maintenance cost per Materialized View
-- Replace `region-eu` with your BigQuery dataset region (for example, `region-us`)
DECLARE on_demand_rate_per_tb NUMERIC DEFAULT 6.25;
DECLARE slot_hour_rate NUMERIC DEFAULT 0.06;

SELECT
  destination_table.dataset_id AS mv_dataset,
  destination_table.table_id AS mv_name,
  COUNT(job_id) AS monthly_refresh_count,
  ROUND(COUNT(job_id) / 30.0, 1) AS avg_daily_refreshes,
  ROUND(SUM(total_bytes_processed) / POW(1024, 4), 4) AS total_tb_processed,
  ROUND(SUM(total_bytes_billed) / POW(1024, 4), 4) AS total_tb_billed,
  ROUND(SUM(total_slot_ms) / (1000 * 60 * 60), 2) AS total_slot_hours,
  ROUND((SUM(total_bytes_billed) / POW(1024, 4)) * on_demand_rate_per_tb, 2) AS est_on_demand_maintenance_usd,
  ROUND((SUM(total_slot_ms) / (1000 * 60 * 60)) * slot_hour_rate, 2) AS est_edition_slot_maintenance_usd,
  MAX(creation_time) AS last_refresh_job_utc
FROM
  `region-eu`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE
  creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY)
  AND (
    statement_type = 'MATERIALIZED_VIEW_REFRESH'
    OR job_id LIKE 'materialized_view_refresh_%'
  )
  AND state = 'DONE'
  AND error_result IS NULL
GROUP BY
  mv_dataset,
  mv_name
ORDER BY
  total_slot_hours DESC;

Production SQL 2: Correlating Refresh Costs with Actual User Query Demand

Whenever a user query reads a Materialized View—either because the user queried the Materialized View directly or because BigQuery Smart Tuning automatically rewrote a base table query to use the Materialized View—BigQuery records the Materialized View inside the referenced_tables array of the SELECT job in INFORMATION_SCHEMA.JOBS_BY_PROJECT.

The following query combines the complete inventory of Materialized Views from INFORMATION_SCHEMA.TABLES with both background refresh costs and actual foreground read counts over a 30-day window:

SQL

-- Complete 30-day FinOps audit: Inventory + Maintenance Cost + Actual User Demand
DECLARE on_demand_rate_per_tb NUMERIC DEFAULT 6.25;

WITH mv_inventory AS (
  SELECT
    table_catalog AS project_id,
    table_schema AS dataset_id,
    table_name AS mv_name,
    creation_time AS deployed_at
  FROM
    `region-eu`.INFORMATION_SCHEMA.TABLES
  WHERE
    table_type = 'MATERIALIZED VIEW'
),
maintenance_jobs AS (
  SELECT
    destination_table.dataset_id,
    destination_table.table_id AS mv_name,
    COUNT(job_id) AS refresh_executions_30d,
    ROUND(SUM(total_slot_ms) / (1000 * 60 * 60), 2) AS refresh_slot_hours_30d,
    ROUND((SUM(total_bytes_billed) / POW(1024, 4)) * on_demand_rate_per_tb, 2) AS maintenance_cost_usd_30d
  FROM
    `region-eu`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
  WHERE
    creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY)
    AND (
      statement_type = 'MATERIALIZED_VIEW_REFRESH'
      OR job_id LIKE 'materialized_view_refresh_%'
    )
    AND state = 'DONE'
  GROUP BY
    1, 2
),
user_demand AS (
  SELECT
    ref.dataset_id,
    ref.table_id AS mv_name,
    COUNT(DISTINCT jobs.job_id) AS user_query_count_30d,
    COUNT(DISTINCT jobs.user_email) AS distinct_users_30d,
    MAX(jobs.creation_time) AS last_queried_utc
  FROM
    `region-eu`.INFORMATION_SCHEMA.JOBS_BY_PROJECT AS jobs,
    UNNEST(jobs.referenced_tables) AS ref
  WHERE
    jobs.creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY)
    AND jobs.statement_type = 'SELECT'
    AND jobs.state = 'DONE'
    AND jobs.error_result IS NULL
    AND NOT STARTS_WITH(jobs.job_id, 'materialized_view_refresh_')
  GROUP BY
    1, 2
)
SELECT
  i.dataset_id,
  i.mv_name,
  i.deployed_at,
  COALESCE(m.refresh_executions_30d, 0) AS refresh_executions_30d,
  COALESCE(u.user_query_count_30d, 0) AS user_query_count_30d,
  COALESCE(u.distinct_users_30d, 0) AS distinct_users_30d,
  COALESCE(m.refresh_slot_hours_30d, 0) AS refresh_slot_hours_30d,
  COALESCE(m.maintenance_cost_usd_30d, 0) AS maintenance_cost_usd_30d,
  ROUND(
    SAFE_DIVIDE(COALESCE(u.user_query_count_30d, 0), COALESCE(m.refresh_executions_30d, 0)),
    2
  ) AS queries_per_refresh_ratio,
  CASE
    WHEN COALESCE(u.user_query_count_30d, 0) = 0
      THEN 'DROP CANDIDATE: Zero queries in 30 days'
    WHEN COALESCE(u.user_query_count_30d, 0) < 50
      THEN 'MIGRATE TO STANDARD VIEW OR SCHEDULED QUERY: Low demand (<50/month)'
    WHEN SAFE_DIVIDE(COALESCE(u.user_query_count_30d, 0), COALESCE(m.refresh_executions_30d, 0)) < 0.25
      THEN 'EVALUATE SCHEDULED QUERY OR TUNE REFRESH INTERVAL: Maintenance exceeds demand'
    ELSE 'HEALTHY: High utilization relative to refresh frequency'
  END AS architectural_action
FROM
  mv_inventory AS i
LEFT JOIN
  maintenance_jobs AS m
  USING (dataset_id, mv_name)
LEFT JOIN
  user_demand AS u
  USING (dataset_id, mv_name)
ORDER BY
  maintenance_cost_usd_30d DESC;

4. Production Workload Benchmarks: Three Real-World Scenarios

During the 90-day investigation, the engineering team modeled the true monthly cost of three implementation strategies across 148 Materialized Views, 392 Scheduled Queries, and more than 12 million dashboard executions:

  1. Materialized View maintained automatically by BigQuery.
  2. Scheduled Query writing precomputed results into a reporting table.
  3. Standard View executed on demand against the base tables.

Comparing three specific production workloads from the study illustrates why neither Materialized Views nor Scheduled Queries can be treated as a universal solution. It also demonstrates that the economic winner has very little to do with SQL complexity—and everything to do with how often humans actually look at the data.

Workload A: Executive Sales Dashboard

Business and Technical Characteristics:

  • User Demand: Viewed 4,800 to 5,300 times per day (~150,000 executions per month).
  • Source Update Frequency: Source table updated every minute with incoming customer transactions.
  • Query Complexity: High.
  • Unoptimized Scan Volume: Average query before optimization scanned 7.8 TB of data.
Implementation OptionMonthly CostAverage Response TimeEconomic & Technical Evaluation
Standard View$23,9008.9 secUnviable: repeats heavy multi-terabyte scans roughly 5,000 times per day.
Scheduled Query$6,7001.9 secGood latency, but frequent scheduled rebuilds across large datasets cost nearly 2x more than incremental MV refreshes.
Materialized View$3,4000.8 secClear Winner: Incremental refresh costs are minor compared to eliminating 5,000 heavy scans every day.

Why Materialized View Won in Workload A:

This scenario represents the exact workload profile that Materialized Views were designed to solve. Because executives and store operations teams opened the dashboard roughly 5,000 times every day, every single background refresh was reused across hundreds of subsequent dashboard requests. Furthermore, because the high-velocity transaction table received append-only inserts every minute, BigQuery could update the Materialized View incrementally without re-scanning historical partitions.

Workload B: Regional Marketing Dashboard

Business and Technical Characteristics:

  • User Demand: Viewed 18 to 25 times per month.
  • Source Update Frequency: Source data updated every 10 minutes.
  • Query Complexity: Medium.
Implementation OptionMonthly CostAverage Response TimeEconomic & Technical Evaluation
Standard View$423.4 secClear Winner: Executes only when needed; 3.4 seconds is completely acceptable for a dashboard opened 20 times a month.
Scheduled Query$610.9 secEconomical alternative ($19/month premium) if sub-second response time is strictly required.
Materialized View$6180.8 secSevere Financial Loss: Costs nearly 15 times more than a Standard View to shave 2.6 seconds off 22 monthly views.

Why Standard View and Scheduled Query Won in Workload B:

This result surprised many engineers on the team. The Materialized View delivered the fastest dashboard (0.8 seconds), but it cost $618 per month compared to just $42 for a Standard View—nearly 15 times more expensive. Because the source table updated every 10 minutes (roughly 4,320 times per month) while humans only opened the report 18 to 25 times per month, the platform spent almost all of its compute budget refreshing data that nobody was reading. Simply executing the query on demand via a Standard View delivered a 3.4-second response time at a 93% cost reduction.

Workload C: Monthly Financial Report

Business and Technical Characteristics:

  • User Demand: Generated once every month and consumed by the Finance department.
  • Source Update Frequency: Continuous historical transaction updates and accounting reconciliations.
  • Query Complexity: High (large aggregation across historical transaction records).
Implementation OptionMonthly CostAverage Response TimeEconomic & Technical Evaluation
Standard View$12641 secLow cost, but a 41-second wait time degrades interactive filtering during financial reviews.
Scheduled Query$37InstantClear Winner: Computes the heavy historical aggregation on a predictable nightly schedule at 95% lower cost than an MV.
Materialized View$812InstantIrrational Expense: Continuously synchronizes historical transaction changes all month for a report generated once a month.

Why Scheduled Query Won in Workload C:

Both the Scheduled Query and the Materialized View provided instant dashboard performance for the Finance team. However, maintaining a continuously refreshed Materialized View over historical transaction tables cost $812 per month, whereas computing the result once each night via a Scheduled Query cost $37 per month. When business reporting follows a predictable daily or monthly cadence, decoupling transformation execution from real-time source ingestion saves more than 95% of compute cost without sacrificing user experience.

5. Human Behavior, Latency Economics, and Hidden Engineering Overhead

After reviewing the benchmark data across all 148 Materialized Views and 392 Scheduled Queries, the engineering team stopped looking at SQL syntax in isolation and started analyzing human behavior.

The Pareto Distribution of Dashboard Usage

One platform-wide statistic permanently changed the company’s data architecture guidelines:

  • More than 72% of all reporting assets were opened fewer than 50 times per month.
  • Only 9% of reporting assets were viewed more than 500 times per day.

Despite this stark asymmetry, almost every optimization initiative during the previous two years had defaulted to Materialized Views. Whenever a stakeholder complained that a report felt slow—or whenever an engineer wanted to showcase fast query performance during a sprint review—a Materialized View was created. The organization had systematically optimized for theoretical benchmark performance instead of actual user demand.

Treating Response Time as a Business Metric, Not an Engineering Trophy

The investigation forced a cultural shift: every technical optimization must have an economic justification.

Not every latency reduction delivers business value. For example, reducing a dashboard’s load time from 2.1 seconds to 0.7 seconds looks impressive during an engineering demonstration. However, user telemetry proved that human analysts did not notice or care about a 1.4-second difference on an internal report. Conversely, reducing monthly cloud spending by $18,000 while keeping dashboard response times comfortably under three seconds generated immediate, measurable value for the business.

The Hidden Cost of Architectural Dependency and Schema Lock-In

Beyond direct BigQuery billing, the audit uncovered a second major expense that rarely appears on cloud cost dashboards: engineering maintenance overhead.

Materialized Views create tight architectural coupling between raw source tables and the presentation layer. In BigQuery, whenever upstream source schemas change—such as modifying column types, restructuring nested records, or refactoring partitioning logic—dependent Materialized Views require validation, refresh testing, and coordinated deployment checks. If a base table undergoes a breaking DDL change, dependent Materialized Views become invalid and fail to refresh until recreated.

Over a two-year period, the retail company estimated that data engineers had spent approximately 420 hours maintaining, troubleshooting, and redeploying Materialized Views that collectively saved less than $9,000 annually in query costs. Even before accounting for cloud compute waste, the engineering labor required to keep those low-value Materialized Views alive exceeded the infrastructure savings they produced.

6. Practical Solutions: A Step-by-Step Remediation Path (From Simple to Complex)

To fix the cost imbalance without disrupting the 1,800 Looker users, the engineering team implemented a structured remediation strategy ordered from the simplest non-breaking changes to comprehensive architectural governance.

Solution 1 (Simple): Decommission Unused Views and Tune Refresh & Staleness Parameters

The simplest and fastest intervention requires zero changes to downstream BI dashboards:

  1. Drop Zero-Usage Views Immediately: Any Materialized View that recorded zero queries over the past 60 days (46 views in the retail case study—19 never queried and 27 dormant for over 60 days) can be dropped safely. If the view was created solely for Smart Tuning, dropping it does not break any SQL queries because queries already reference the base table.
  2. Increase refresh_interval_minutes and Configure max_staleness: For Materialized Views that serve high-traffic dashboards (hundreds or thousands of views per day) where data does not need to be accurate to the exact second, configure refresh_interval_minutes and max_staleness explicitly. This prevents BigQuery from running background refreshes every few minutes and stops foreground queries from scanning the base table’s real-time delta.

SQL

-- Solution 1: Adjust refresh frequency and allow bounded staleness
-- Eliminates query-time base table delta scans and reduces background refresh frequency
ALTER MATERIALIZED VIEW `analytics_prod.mv_executive_sales_summary`
SET OPTIONS (
  enable_refresh = true,
  refresh_interval_minutes = 60,
  max_staleness = INTERVAL '1:0:0' HOUR TO SECOND
);

Solution 2 (Medium): Convert Low-Frequency Materialized Views to Partitioned Scheduled Queries

When a dashboard is opened tens of times per week (or a few times per month) and requires fast load times over complex historical aggregations, replace the Materialized View with a native partitioned and clustered table updated by a Scheduled Query or incremental Dataform model.

Step 2.1: Write an Incremental Partition-Bounded Transformation

Instead of letting a Materialized View recompute partitions every 10 minutes whenever an upstream pipeline runs, execute an incremental MERGE once per night (or every few hours) that scans only the most recent partitions:

SQL

-- Solution 2: Incremental Scheduled Query replacing an expensive Materialized View
-- Scans only a 3-day rolling window rather than recomputing continuously
DECLARE lookback_window DATE DEFAULT DATE_SUB(CURRENT_DATE(), INTERVAL 3 DAY);

MERGE INTO `analytics_prod.rpt_regional_marketing_summary` AS target
USING (
  SELECT
    DATE(event_timestamp) AS report_date,
    country_code,
    campaign_id,
    channel_name,
    COUNT(DISTINCT order_id) AS total_orders,
    SUM(revenue_amount_eur) AS total_revenue_eur
  FROM
    `core_prod.fct_customer_events`
  WHERE
    DATE(event_timestamp) >= lookback_window
  GROUP BY
    1, 2, 3, 4
) AS source
ON target.report_date = source.report_date
   AND target.country_code = source.country_code
   AND target.campaign_id = source.campaign_id
   AND target.report_date >= lookback_window
WHEN MATCHED THEN
  UPDATE SET
    target.channel_name = source.channel_name,
    target.total_orders = source.total_orders,
    target.total_revenue_eur = source.total_revenue_eur,
    target.updated_at_utc = CURRENT_TIMESTAMP()
WHEN NOT MATCHED THEN
  INSERT (
    report_date,
    country_code,
    campaign_id,
    channel_name,
    total_orders,
    total_revenue_eur,
    updated_at_utc
  )
  VALUES (
    source.report_date,
    source.country_code,
    source.campaign_id,
    source.channel_name,
    source.total_orders,
    source.total_revenue_eur,
    CURRENT_TIMESTAMP()
  );

Step 2.2: Deploy the Destination Table and Scheduled Query with Terraform

To manage the scheduled transformation cleanly in version control without schema lock-in, provision the partitioned destination table and the BigQuery Data Transfer schedule via Terraform (ensuring require_partition_filter = true is placed at the top level of the google_bigquery_table resource):

Terraform

resource "google_bigquery_table" "rpt_regional_marketing_summary" {
  dataset_id               = "analytics_prod"
  table_id                 = "rpt_regional_marketing_summary"
  project                  = var.gcp_project_id
  deletion_protection      = true
  require_partition_filter = true

  time_partitioning {
    type  = "DAY"
    field = "report_date"
  }

  clustering = ["country_code", "channel_name", "campaign_id"]

  labels = {
    environment       = "production"
    optimization_type = "scheduled_query"
    managed_by        = "terraform"
  }

  schema = jsonencode([
    { name = "report_date", type = "DATE", mode = "REQUIRED" },
    { name = "country_code", type = "STRING", mode = "REQUIRED" },
    { name = "campaign_id", type = "STRING", mode = "REQUIRED" },
    { name = "channel_name", type = "STRING", mode = "NULLABLE" },
    { name = "total_orders", type = "INT64", mode = "NULLABLE" },
    { name = "total_revenue_eur", type = "NUMERIC", mode = "NULLABLE" },
    { name = "updated_at_utc", type = "TIMESTAMP", mode = "REQUIRED" }
  ])
}

resource "google_bigquery_data_transfer_config" "regional_marketing_nightly_schedule" {
  display_name           = "rpt_regional_marketing_summary_nightly_sync"
  project                = var.gcp_project_id
  location               = "EU"
  data_source_id         = "scheduled_query"
  schedule               = "every day 03:00"
  service_account_name   = var.scheduler_service_account_email

  params = {
    query = file("${path.module}/sql/merge_rpt_regional_marketing_summary.sql")
  }
}

Solution 3 (Complex): Establish an Enterprise Decision Matrix and Architecture Governance Gate

To prevent expensive background workloads from accumulating again in the future, the engineering team embedded a formal economic decision matrix and a five-question approval gate into every architecture review.

The Workload Decision Matrix

Actual Dashboard UsageRecommended Architectural SolutionEngineering Rationale
Thousands of executions per dayMaterialized ViewHigh read reuse amortizes continuous background refresh costs across thousands of daily requests.
Hundreds of executions per dayCompare Materialized View vs. Scheduled QueryCalculate the Break-Even threshold using source update frequency, DML churn, and required freshness.
Tens of executions per weekScheduled Query (or Incremental Dataform Model)Decouples transformation cost from source ingestion frequency while maintaining fast dashboard response.
Rare or ad-hoc analysis (<50/month)Standard ViewEliminates 100% of background compute and storage costs; relies on base table partitioning and clustering.

The Five Mandatory Architecture Review Questions

From that point onward, no engineer could deploy a new Materialized View into production without providing documented, evidence-based answers to five questions during pull request review:

  1. How many times will this data actually be queried? (Verified via Looker usage logs or realistic user count projections).
  2. What is the estimated monthly refresh cost? (Calculated from base table update frequency and partition size).
  3. How much foreground query cost will it eliminate? (Measured by comparing raw base table scan bytes against precomputed scan bytes).
  4. Is the current response time already acceptable? (If a Standard View over a partitioned table loads in under 3 seconds, precomputation is unnecessary).
  5. Would a Scheduled Query achieve the same business outcome at a lower operational and maintenance cost?

If those five questions could not be answered with empirical evidence showing positive net savings, the Materialized View was not approved.

7. Production Outcomes and Executive Recommendations

Six months after adopting this FinOps governance framework, the engineering team audited the retail platform to measure the long-term technical and financial results:

  • Materialized View Footprint Reduced by 58.8%: The number of active Materialized Views decreased from 148 to 61, retaining only those that served high-concurrency operational workloads.
  • Controlled Growth in Scheduled Queries: Scheduled Queries and incremental models increased modestly to cover periodic reports that required fast rendering without continuous synchronization.
  • Unchanged User Experience: Average Looker dashboard response time across all 1,800 internal users remained virtually unchanged, staying consistently below the three-second threshold.
  • Verified Financial Savings: Monthly BigQuery expenditure fell by approximately $27,000 per month, representing more than $320,000 in annual cloud savings.
  • Reduced Engineering Friction: Eliminating 87 unnecessary Materialized Views removed rigid schema dependencies on core tables, accelerating upstream data pipeline deployments.

Equally important, the analytical platform became much easier to reason about. Data architects stopped debating which BigQuery feature was technically superior in the abstract and started evaluating workload economics.

Summary of Evidence Collected During the Audit

To replicate this investigation in any Google Cloud environment, engineering teams should collect and correlate telemetry across these seven sources:

  • Cloud Billing Export (SKU-level cost verification)
  • BigQuery INFORMATION_SCHEMA.JOBS (foreground query scans vs. background refresh jobs)
  • BigQuery Audit Logs (query rewrite tracking and principal attribution)
  • Looker Dashboard Usage History (actual human viewing frequency per report)
  • Materialized View Refresh Statistics (refresh intervals, watermarks, and full partition invalidations)
  • Dataform Execution History (DAG run costs and incremental partition volumes)
  • Cloud Monitoring Performance Metrics (slot utilization, reservation autoscaling baselines, and P95 query latency)

Executive Recommendations

  1. Never introduce a Materialized View without calculating its monthly refresh cost. Measure the update frequency and DML pattern of the source table before enabling background synchronization.
  2. Measure actual dashboard usage before optimizing performance. With over 72% of enterprise reports opened fewer than 50 times per month, most dashboards do not generate enough read traffic to pay back continuous precomputation.
  3. Treat response time as a business metric, not an engineering trophy. Sub-second latency is valuable on high-concurrency operational tools, but reducing latency from 2.1 seconds to 0.7 seconds on a rarely viewed report wastes budget.
  4. Review optimization decisions every six months as usage patterns evolve. A dashboard built for a major product launch may receive thousands of views per day in its first month and drop to ten views per month two quarters later.
  5. Optimize for cost per business decision, not for isolated benchmark numbers. Both Materialized Views and Scheduled Queries are powerful tools, but the wrong optimization applied to the wrong workload is simply another recurring cloud expense.

The Question Every CTO and Head of Data Should Ask

“Are we paying to accelerate dashboards that people actually use—or dashboards that looked impressive during the architecture review?”

Similar Posts