Explain the difference between a complete refresh and an incremental (fast) refresh of a materialized view, and what a database needs in place for the incremental form to be possible.
answer
- complete = recompute all; fast = apply deltas
- fast needs change logs on base tables
- cost shifts onto the write path
- SUM/COUNT ok; MAX with deletes, DISTINCT, outer joins problematic
- silent fallback to complete = refresh lag
basics
~20 sComplete refresh re-runs the whole defining query and replaces all stored rows. Incremental (fast) refresh applies only the changes since the last refresh, using a change log of the base tables. It needs recorded deltas and a query shape the engine can maintain incrementally.
solid answer
~50 s**Complete refresh** discards the stored contents and re-executes the defining query from scratch. It is always correct and always available, but its cost scales with the whole dataset, so it grows as the base tables grow regardless of how little changed. **Incremental ("fast") refresh** computes only the delta: it reads a log of base-table changes since the last refresh and applies the corresponding inserts, updates and deletes to the stored rows. Cost scales with the volume of change, so it stays cheap on a large table with modest churn. Incremental refresh is not free to enable. The engine must capture base-table changes — typically materialized-view logs or change-tracking structures on every base table — and the view definition must be *incrementally maintainable*. Simple projections, filters, inner joins on keys, and SUM/COUNT aggregates usually qualify. Outer joins, DISTINCT, MAX/MIN with deletes, window functions, self-joins and non-deterministic expressions frequently disqualify it and silently force a complete refresh.
code
sql · 14 lines-- delta since the last watermark
WITH delta AS (
SELECT store_id, sale_date, SUM(amount) AS d_total, COUNT(*) AS d_orders
FROM orders
WHERE changed_at > :last_watermark
GROUP BY store_id, sale_date
)
MERGE INTO daily_sales t
USING delta d
ON (t.store_id = d.store_id AND t.sale_date = d.sale_date)
WHEN MATCHED THEN UPDATE SET total = t.total + d.d_total,
orders = t.orders + d.d_orders
WHEN NOT MATCHED THEN INSERT (store_id, sale_date, total, orders)
VALUES (d.store_id, d.sale_date, d.d_total, d.d_orders);go deeper
Know the two names and the one-line difference: recompute everything versus apply only the changes.
Explain the prerequisites for incremental refresh — change logs plus a maintainable query shape — and give one aggregate that qualifies and one that does not.
Talk about where the cost moves (onto writes), how you detect a silent fallback, and log growth if refreshes stop.
Frame it as a cost-placement decision across the system: refresh-time batch cost versus per-write tax versus a streaming read-model outside the database.
## Why there are two modes A materialized view holds a derived result. After the base data changes, the stored rows are wrong. Refreshing means making them right again, and there are only two ways to do that: recompute everything, or figure out what changed and patch the difference. ## Complete refresh A complete refresh re-executes the defining query and replaces the stored contents. Its properties: - **Always correct, always possible.** Any query definition can be recomputed. - **Cost proportional to the whole dataset.** Refreshing a rollup over a billion-row fact table costs a billion-row scan whether one row changed or a million did. - **Simple to reason about.** No dependency on change logs, no drift risk. This is the default, and for many systems it is the right answer: run it nightly, or every fifteen minutes on a moderate dataset, and stop thinking about it. ## Incremental (fast) refresh Incremental refresh reads the base-table changes recorded since the previous refresh and applies the equivalent changes to the materialized rows. For a `SUM(amount) GROUP BY store_id` rollup, inserting one order means adding one amount to one group row — constant work instead of a full scan. This requires two things. **1. Change capture.** The engine needs to know what changed. Classically this is a *materialized view log* on each base table: a side table that records changed row keys (and often old/new values and the operation type) whenever a base row is inserted, updated or deleted. That log is maintained on the base table's write path, so incremental refresh moves cost from refresh time onto every write. On a write-heavy table that is a real tax. **2. An incrementally maintainable definition.** Not every query can be patched from deltas. Roughly: - *Usually maintainable:* projections and filters; inner joins where the join keys are available in the logs; additive aggregates like `COUNT(*)` and `SUM`, which can be adjusted by adding or subtracting a contribution; `AVG` when stored as sum and count. - *Usually not, or only with restrictions:* `MAX`/`MIN` under deletes (deleting the current maximum forces re-scanning the group to find the new one); `DISTINCT` and set operations; outer joins; window functions; self-joins; correlated subqueries; anything non-deterministic such as `now()` or a random ordering. Engines differ enormously in what they support: some offer rich fast refresh with a validation routine that tells you *why* a view is not fast-refreshable, some offer incremental maintenance only for a narrow class of aggregate views, and some offer no incremental refresh at all and expect you to hand-roll a delta job. ## The trap: silent downgrade A common production failure is requesting fast refresh on a view that does not qualify. Depending on the engine you either get an error at creation time (good) or a silent fall back to complete refresh (bad) — the job that was budgeted at five seconds now takes twenty minutes, and it only shows up as growing refresh lag. Whenever you rely on incremental refresh, verify with the engine's eligibility check and monitor actual refresh duration, not just success. ## The hand-rolled alternative If the engine cannot maintain your view incrementally, the equivalent pattern is a summary table plus a delta job: keep a watermark (last processed timestamp or change id), read base rows changed since the watermark, and merge the aggregated delta into the summary. This is exactly what fast refresh does, with you owning the correctness — the hard parts are late-arriving rows, deletes, and updates that move a row between groups. ## Choosing Start with complete refresh. Move to incremental only when complete refresh no longer fits the staleness window or the maintenance window, and accept that you are buying cheaper refreshes with more expensive writes and a more fragile definition.
- Why is a materialized view containing MAX(x) per group hard to refresh incrementally?Inserts are easy: the new maximum is the greater of the stored value and the new row. Deletes are not: if the deleted row held the maximum, the correct new value can only be found by re-reading the whole group. So the engine must either re-scan affected groups or refuse fast refresh. Additive aggregates like SUM and COUNT have no such problem because a delete simply subtracts a contribution.
- What is the hidden cost of enabling incremental refresh on a high-write table?Change capture runs on the write path. Every insert, update and delete on the base table also writes a change-log row, which adds latency and I/O to the transaction and creates a table that must itself be purged as refreshes consume it. If the log is never consumed — a refresh job that stops running — it grows unbounded and can fill storage.
saying these in an interview costs you the question
- Believing incremental refresh is always available if you just ask for it
- Not knowing that change logs on base tables tax every write
- Assuming complete refresh is "the slow legacy way" rather than the correct default
- Thinking incremental refresh handles deletes for free
- Ignoring silent fallback to complete refresh as a cause of refresh lag