Question 1

A dashboard runs the same twelve-table aggregate on every page load. Would you make it a view, a materialized view, or a streaming table?

Accepted Answer

A materialized view, because it stores the computed result and the dashboard reads that instead of recomputing the aggregate on every load. A plain VIEW is a saved query: every reader pays the full cost of the aggregate again. A materialized view stores the result as a table and refreshes it on a schedule or on demand, so the dashboard reads precomputed rows. A streaming table is for the ingest side — it consumes new rows from a source incrementally using a checkpoint, which is what you want for appending raw data, not for serving an aggregate. The decision rule is how the result is produced and consumed: incremental ingest means streaming table, repeated expensive aggregation means materialized view, cheap or rarely used logic means a plain view. Materialized views on Databricks require serverless compute, which is worth knowing before you promise one. A VIEW recomputes on every read; an MV stores the result. Streaming tables ingest incrementally using a checkpoint. Repeated expensive aggregation is the MV case. Materialized views require serverless compute.

Question 2

A materialized view that refreshed in two minutes for months now takes ninety. Nobody changed the data volume. What happened?

Accepted Answer

It stopped refreshing incrementally and fell back to a full recompute, almost always because the definition changed or it now contains something the planner cannot reason about incrementally. Incremental refresh is not a setting; it is something the planner proves it can do for a given query shape. When it can, it reads only the changed rows from the sources and updates the stored result. When it cannot, it recomputes the whole thing, and on a large source that is the ninety minutes. The usual triggers are a changed definition — any edit can invalidate incremental state — a non-deterministic expression like current_timestamp() or rand(), or a join or aggregate pattern outside what incremental refresh supports. The refresh history and the pipeline event log for the materialized view record which path each refresh took, so you check rather than guess. The reason this hurts is that nothing fails: the view is correct, the dashboard is fine, and only the bill changes. Incremental refresh happens only when the planner can prove it is safe. A definition change, current_timestamp(), or rand() forces full recompute. Refresh history and the pipeline event log record which path ran. Nothing errors — the result stays correct and only the cost moves.

Question 3

Someone adds a column defined as datediff(current_date(), order_date) to a materialized view. What is your review comment?

Accepted Answer

A time-dependent expression makes the result a function of when it ran, so incremental refresh cannot be proven safe and every refresh becomes a full recompute. Incremental refresh works by reasoning that old output rows are still correct if their input rows did not change. current_date() and current_timestamp() break that reasoning: yesterday's output row is wrong today even though no input changed. The planner cannot verify the result incrementally, so it recomputes everything. The same applies to rand() and to user-defined functions the planner cannot analyse. The fix is to keep the stored result a pure function of the source columns — store order_date and compute the age at read time in the dashboard or in a thin view on top. That keeps the expensive aggregation precomputed and leaves the one cheap time-dependent expression to the reader. Time-dependent expressions make old output rows wrong without input changes. current_timestamp(), current_date(), and rand() all defeat incremental refresh. Keep the MV a pure function of source columns. Compute age or recency in a thin view on top of the MV.

Question 4

How do you actually verify whether a given materialized view refresh was incremental, rather than trusting that it was?

Accepted Answer

Read the refresh history and the backing pipeline's event log, which record the refresh type and the rows processed for each run. Every materialized view on Databricks is backed by a declarative pipeline, even when you created it with plain SQL in DBSQL. That pipeline has an event log, and the refresh history for the view shows each run with its outcome and metrics. What you are looking for is whether the run processed only changed rows or re-read the full source, and the input row counts make that obvious even before you read the label. Pair it with DESCRIBE HISTORY on the materialized view's own table to see the write pattern — a full overwrite each time is a strong signal. Make this a monitored metric rather than an investigation you do after a bill arrives: alert when a view's refresh duration or input row count jumps. Every MV is backed by a declarative pipeline with an event log. Refresh history shows per-run outcome, duration, and row metrics. Full source row counts on every run indicate full recompute. Alert on refresh duration jumps rather than waiting for the bill.

Question 5

A materialized view is refreshed every five minutes but the source table only lands new data twice a day. How would you fix the schedule?

Accepted Answer

Drive refresh from the data arriving rather than from a clock — schedule it after the load, or let the pipeline that produces the source trigger it. A five-minute schedule on a twice-daily source runs hundreds of refreshes that find nothing to do, and even a cheap incremental refresh is not free once you multiply it. ALTER MATERIALIZED VIEW ... SCHEDULE lets you set a cron that matches reality, which for a twice-daily load is two refreshes a day plus perhaps a safety run. Better still, have the job that loads the source call REFRESH MATERIALIZED VIEW as its last task, so freshness is tied to the load rather than to a guess about when the load finishes. If the view sits inside a declarative pipeline with its sources, the engine handles the ordering for you. Then check the refresh history for a while to confirm you are not refreshing before the load lands, which is the failure mode of a badly guessed cron. A clock schedule on an event-driven source wastes most of its runs. SCHEDULE sets a cron; REFRESH after the load ties freshness to the data. Putting the MV in the same pipeline as its sources handles ordering. Verify in refresh history that refresh happens after the load, not before.

Question 6

An analyst created a materialized view in DBSQL and is surprised to find a pipeline in the workspace that they did not create. Explain it.

Accepted Answer

Materialized views and streaming tables are implemented on Declarative Pipelines, so creating one in DBSQL creates the pipeline that refreshes it. The same engine that runs Lakeflow Declarative Pipelines backs materialized views and streaming tables, including the ones created with plain SQL from a SQL editor. That is why each one has a pipeline, an event log, and a refresh history rather than just being a table. It also explains several things that otherwise look odd: refreshes run on serverless compute, the failures show up in a pipeline event log rather than in query history, and the object has an owner identity whose permissions the refresh runs with. The operational consequence is ownership — a materialized view created by an individual refreshes as that individual, and when they leave, the refresh breaks. Production views should be created and owned by a service principal, which usually means defining them in a pipeline or a bundle rather than ad hoc in the editor. MVs and streaming tables are implemented on Declarative Pipelines. That is why they have a pipeline, an event log, and refresh history. Refresh runs with the owner's identity on serverless compute. An individually owned production MV breaks when that person leaves.

Question 7

Bronze ingest from a cloud path, and a gold aggregate over it. Which is the streaming table and which is the materialized view, and why not use one for both?

Accepted Answer

Streaming table for the append-only ingest with its checkpoint, materialized view for the aggregate — they have different update semantics, not just different names. A streaming table processes each source record once, tracked by a checkpoint, which is exactly right for files landing in storage: new files are picked up, old ones are not re-read, and the table grows by appending. A materialized view defines a result over its sources and keeps that result correct, which may mean updating or replacing rows when the inputs change — that is what an aggregate needs, since one late record changes an existing group's total. Using a streaming table for the aggregate forces you to handle restatement yourself with stateful aggregation and watermarks. Using a materialized view for ingest means re-evaluating a growing source instead of consuming only new files. Match the object to the semantics: append-once means streaming table, derived result that can change means materialized view. Streaming tables process each source record once, tracked by a checkpoint. Materialized views keep a derived result correct as inputs change. Late-arriving records restate an aggregate, which is MV semantics. Using a streaming table for an aggregate pushes restatement onto you.

Question 8

Two hundred dashboards hit the same handful of silver tables and the warehouse bill is dominated by repeated aggregation. How do you design the serving layer?

Accepted Answer

Build a small set of owned materialized views at the grains the dashboards actually use, refreshed on data arrival, and point every dashboard at those rather than at silver. Start from query history: group the warehouse's expensive queries by shape and find the handful of grains that account for most of the aggregation cost. Materialize those, deliberately and with owners, so one refresh replaces thousands of ad hoc recomputations. Keep the definitions free of time-dependent expressions so refresh stays incremental, and drive refresh from the loading pipelines rather than from clocks. The governance half matters as much: define the materialized views in asset bundles owned by service principals, so they are reviewed, deployed, and attributable, and so a departing employee does not take a gold table's refresh with them. Then watch two numbers — refresh cost per view, and warehouse cost avoided — and retire views whose refresh costs more than the queries they replaced, because a stale unused materialized view is pure spend. Derive the grains to materialize from query history, not from requests. Keep definitions pure so refresh stays incremental. Define them in bundles owned by service principals, not in the editor. Retire views whose refresh cost exceeds the query cost they avoid.