Designing for FAST refresh and query rewrite

Materialized views (MVs) turn expensive queries into predictable speed - if you design them right and plan for when they go sideways. Modern analytics stacks juggle joins across huge fact tables, late-arriving events, and real-time questions. You can recompute aggregates on every query (slow, spiky, expensive), or precompute them once, keep them fresh incrementally, and let the optimizer rewrite user queries to hit those precomputations. Well-designed MVs give you the second path. Poorly designed ones create staleness, drift, and bugs that surface at the worst time. This article covers how to design fast, incrementally maintainable MVs, enable query rewrite, and avoid the common failure modes.

Core concepts

Materialized view: a persisted snapshot of a query result - usually an aggregate or projection - maintained by the engine. Reads are fast because you hit precomputed data.

FAST (incremental) refresh: only process what changed since the last refresh using logs/streams/partitions, instead of recomputing everything.

Query rewrite: the optimizer transparently routes a query to an equivalent MV when it matches semantics (filters, groupings, projections), so apps don't need to know the MV exists.

You're balancing three things: freshness (how stale is acceptable?), cost (maintenance compute + storage), and coverage (which queries can be rewritten?). Pick the two that matter most and design around them.

Architecture patterns for fast maintenance

1. Change capture first. Feed MVs with explicit change signals: table streams (Snowflake), materialized view logs (Oracle), partition decorators (BigQuery), CDC topics (Kafka/Kinesis). No change signals means slow full refreshes.
2. Partition-aligned MVs. Partition source facts by a stable key (e.g., event_date) and partition the MV the same way. Maintenance then touches only changed partitions.
3. Deterministic, side-effect-free definitions. No UDFs with randomness, no non-deterministic timestamps (CURRENT_TIMESTAMP), no volatile functions. Determinism is often required for rewrite and to avoid refresh surprises.
4. Pre-join selective dimensions. Small, slowly changing dimensions (status codes, product categories) can be safely prejoined. Churny, high-cardinality dims (user attributes) are better joined at query time or handled via surrogate keys + late binding.
5. Micro-batch the stream. Sub-minute micro-batches (or engine-managed near-real-time) reduce lock time and backpressure while keeping latency good enough for dashboards.

Practical snippets per engine

Snowflake: MV with automatic incremental maintenance and rewrite

-- Base fact table (streaming into Snowflake)
CREATE OR REPLACE TABLE events (
  event_time TIMESTAMP_NTZ,
  event_date DATE AS (TO_DATE(event_time)) -- virtual partition key
, user_id STRING, sku STRING, price NUMBER(10,2)
);

-- Fast aggregate MV: daily revenue by SKU
CREATE OR REPLACE MATERIALIZED VIEW mv_daily_revenue_by_sku
  CLUSTER BY (event_date, sku) AS
SELECT
  event_date,
  sku,
  SUM(price) AS revenue,
  COUNT(*)   AS orders
FROM events
GROUP BY 1,2;

-- Tip: Suspend/resume during large backfills
ALTER MATERIALIZED VIEW mv_daily_revenue_by_sku SUSPEND;
-- backfill events...
ALTER MATERIALIZED VIEW mv_daily_revenue_by_sku RESUME;

Snowflake incrementally maintains the MV behind the scenes and can rewrite compatible queries to the MV automatically when semantics match. Keep the definition deterministic; avoid CURRENT_TIMESTAMP inside the MV.

BigQuery: incremental MV on partitioned source + automatic rewrite

-- Partitioned source
CREATE TABLE dataset.events (
  event_time TIMESTAMP,
  user_id STRING, sku STRING, price NUMERIC
) PARTITION BY DATE(event_time);

-- MV (BQ supports certain aggregates/expressions for MVs)
CREATE MATERIALIZED VIEW dataset.mv_daily_revenue_by_sku
PARTITION BY event_date
AS
SELECT
  DATE(event_time) AS event_date,
  sku,
  SUM(price) AS revenue,
  COUNT(*)   AS orders
FROM dataset.events
GROUP BY 1,2;

BigQuery auto-refreshes based on changed partitions and will rewrite compatible queries. Keep joins and expressions within supported limits.

PostgreSQL: concurrent refresh (no auto rewrite)

CREATE MATERIALIZED VIEW mv_daily_revenue_by_sku AS
SELECT DATE(event_time) AS event_date, sku,
       SUM(price) AS revenue, COUNT(*) AS orders
FROM events
GROUP BY 1,2;

-- Reduce blocking during refresh:
CREATE UNIQUE INDEX ON mv_daily_revenue_by_sku (event_date, sku);

-- Periodic refresh without blocking readers:
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_daily_revenue_by_sku;

Postgres doesn't auto-rewrite by default. Applications should point queries at the MV, or you add a view layer that selects from the MV.

Oracle: classic FAST refresh with logs + query rewrite

-- Change log on the base table
CREATE MATERIALIZED VIEW LOG ON events
  WITH ROWID, SEQUENCE (event_time, sku, price) INCLUDING NEW VALUES;

-- MV with FAST refresh and query rewrite
CREATE MATERIALIZED VIEW mv_daily_revenue_by_sku
  BUILD IMMEDIATE
  REFRESH FAST
  ENABLE QUERY REWRITE AS
SELECT TRUNC(event_time) AS event_date, sku,
       SUM(price) revenue, COUNT(*) orders
FROM events
GROUP BY TRUNC(event_time), sku;

ClickHouse: streaming MV pattern (no optimizer rewrite)

CREATE TABLE events (
  event_time DateTime,
  user_id String, sku String, price Decimal(10,2)
) ENGINE = MergeTree()
PARTITION BY toDate(event_time)
ORDER BY (toDate(event_time), sku);

CREATE TABLE agg_daily_revenue_by_sku (
  event_date Date,
  sku String,
  revenue Decimal(18,2),
  orders UInt64
) ENGINE = SummingMergeTree()
PARTITION BY event_date
ORDER BY (event_date, sku);

CREATE MATERIALIZED VIEW mv_ingest TO agg_daily_revenue_by_sku AS
SELECT
  toDate(event_time) AS event_date,
  sku,
  sumState(price) AS revenue,
  countState()    AS orders
FROM events
GROUP BY event_date, sku;

In ClickHouse, MVs are ingestion-time pipelines. You query the target table directly.

Cassandra: be careful

Cassandra's built-in MVs have a history of correctness/repair issues under certain write patterns. Most teams model pre-aggregated tables with Spark/Flink jobs or use table-per-query patterns instead of relying on Cassandra MVs for critical analytics.

Query rewrite: how engines decide to use your MV

Optimizers check some combination of:

• Substitution: the query is a subset/superset of the MV query (same grouping keys, compatible filters).
• Determinism: no volatile functions.
• Freshness policy: the MV isn't too stale for the current session/warehouse policy.
• Cost: using the MV is cheaper than scanning base tables.

To make rewrite likely: keep MV columns minimal and aligned to common GROUP BY keys and filters; avoid ambiguous expressions and precompute casts and date bucketing (DATE(event_time) rather than scattered DATE_TRUNC(...) variations); and don't include columns that explode cardinality without purpose.

Best practices for FAST refresh

• Partition by ingestion date, not event date, for maintenance. If you need event-date reporting, compute it as a column but keep physical partitioning tied to ingestion for guarantees.
• Idempotent aggregates: use sums/counts; avoid non-associative expressions that break incremental math.
• Late-arriving data plan: define a grace window (e.g., 3 days) to reprocess recent partitions. Schedule a nightly touch-up refresh for the window.
• Backfill protocol: suspend MV maintenance during massive backfills; bulk load; resume; then run a targeted catch-up.
• Observability hooks: emit metrics - rows changed per refresh, lag (seconds), CPU/time per partition, rewrite hit rate. Alert on drift or lag growth.
• Small dimension churn: if a dimension changes frequently, prefer surrogate keys and join at query time. Otherwise you'll thrash MV maintenance.

Common failure modes (detect and fix)

1. Staleness drift - dashboard numbers disagree with raw queries. Detect by comparing MV timestamp/rowcount vs base partitions and setting freshness SLA alerts. Fix by tightening micro-batch interval, increasing resources, narrowing the refresh window, and checking change logs aren't lagging.
2. Partial refresh / orphaned partitions - missing days or SKUs. Detect by reconciling MV keys against a canonical date × SKU set. Fix by forcing refresh for affected partitions, validating change-log completeness, and adding retry with backoff.
3. Rewrite misses - queries fall back to base tables and costs spike. Detect via rewrite hit rate (many warehouses expose it). Fix by aligning query templates to MV columns/filters, removing volatile functions, and simplifying expressions.
4. Maintenance storms - refresh jobs pile up after spikes. Detect via queue depth/lag metrics. Fix by rate-limiting concurrent partition refresh, autoscaling compute, and widening micro-batch size temporarily.
5. Schema evolution breakage - refresh fails after a column type or name change. Fix by using views as a compatibility layer, evolving schemas with additive changes first, and re-creating MVs during breaking changes with a cutover plan.
6. Double counting from late updates - revenue too high after CDC replays. Fix with UPSERT-safe pipelines, dedupe on stable event IDs, and aggregates that can be corrected incrementally.
7. Storage bloat - MVs outgrow their savings. Fix by dropping unused columns, compressing, pruning partitions aggressively, and archiving cold partitions to cheaper storage.

Design checklist

• What is the SLA for freshness and the rewrite hit rate target?
• Which change signal powers incremental refresh (stream/log/partition)?
• Are grouping keys stable and aligned with top query patterns?
• Is the definition deterministic and join-light?
• Do we have an observability dashboard for lag, errors, rewrite hits, storage?
• What's the late-data window and the backfill procedure?
• How will we retire or migrate the MV when query patterns change?

Engine comparison

Engine	Incremental Refresh	Query Rewrite	Joins in MV	Notes
Snowflake	Yes (automatic)	Yes (cost-based)	Limited/simple best	Deterministic, great ops; suspend/resume for big backfills
BigQuery	Yes (partition-aware)	Yes (supported shapes)	Limited	Keep definitions within supported operators; great for dashboards
Oracle	Yes (MV logs)	Yes (enable rewrite)	Supported with rules	Classic FAST refresh; very mature
PostgreSQL	No (built-in); concurrent full	No (by default)	Yes	Use REFRESH ... CONCURRENTLY; app aims queries at MV
ClickHouse	Yes (streaming to target)	No	N/A	MVs are pipelines; query the target table
Cassandra	Risky for analytics	No	N/A	Prefer purpose-built tables + stream processing

When not to use MVs

• Your queries are highly ad-hoc and don't repeat.
• Dimensions change constantly and you can't tolerate refresh lag.
• You don't own the base table schemas (they break you often).
• You lack monitoring - blind MVs will betray you.

If any of these are true, build derived tables via batch/stream jobs you fully control, expose them as stable serving models, and skip automatic MVs.

Takeaways

• Align MV partitions with your ingestion/change signal.
• Keep definitions deterministic and minimal to maximize rewrite.
• Build a late-data window and a backfill playbook.
• Track rewrite hit rate, refresh lag, and storage growth weekly.
• Revisit the MV portfolio quarterly; retire what no longer pays for itself.