Databricks interview questions: Streaming tables and materialized views

Say this first: A materialized view, because it stores the computed result and the dashboard reads that instead of recomputing the aggregate on every load.

Why: 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.

CREATE MATERIALIZED VIEW gold.daily_revenue AS
SELECT order_date, region, SUM(amount) AS revenue
FROM silver.orders GROUP BY order_date, region

Common mistake: Calling a materialized view 'a view that is faster', with no mention that its data has a refresh time.

Follow-up they will ask: What does the dashboard show between refreshes?

Say this first: 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.

Why: 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.

DESCRIBE HISTORY gold.daily_revenue;
-- and inspect the materialized view's pipeline event log
-- for the refresh type recorded on each run

Common mistake: Assuming incremental refresh is guaranteed because the view was created as a materialized view.

Follow-up they will ask: Where exactly would you look to confirm the last refresh was full rather than incremental?

Say this first: 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.

Why: 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.

-- store the date, not the age
CREATE MATERIALIZED VIEW gold.orders_rollup AS
SELECT order_date, customer_id, SUM(amount) AS amount
FROM silver.orders GROUP BY order_date, customer_id

Common mistake: Adding a 'days since order' column for convenience and silently turning every refresh into a full rebuild.

Follow-up they will ask: Where would you put the days-since calculation instead?

Say this first: Read the refresh history and the backing pipeline's event log, which record the refresh type and the rows processed for each run.

Why: 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.

Senior depth: The dangerous property of materialized views is that the failure mode is silent and expensive rather than loud and broken. So I treat refresh type as an observable the same way I treat job duration: pull refresh history and event log metrics into a monitoring table, and alert on a step change in duration or input rows per view. I would also gate definition changes in review, because an innocuous-looking edit is the most common trigger. The point is to find out from a dashboard on Tuesday, not from finance at month end.

Common mistake: Inferring the refresh type from how long it felt, instead of reading the event log.

Follow-up they will ask: What alert would you set so this is caught in a day rather than a month?

Say this first: 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.

Why: 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.

ALTER MATERIALIZED VIEW gold.daily_revenue
  ADD SCHEDULE CRON '0 0 2,14 * * ?';
-- or from the loading job's final task
REFRESH MATERIALIZED VIEW gold.daily_revenue

Senior depth: Freshness should be expressed as a requirement and then implemented, rather than approximated with a tight cron. I ask what staleness the consumer can tolerate, then pick the cheapest mechanism that meets it: pipeline ordering if the MV and its source live together, an explicit REFRESH as the last task of the load if they do not, and a cron only when neither is available. The anti-pattern I push back on hardest is a short schedule used as insurance against not knowing when data arrives, because it pays continuously for a question you could answer once.

Common mistake: Setting an aggressive schedule to make the view 'fresh' without checking how often the source actually changes.

Follow-up they will ask: How would you handle a source that lands unpredictably rather than on a schedule?

Say this first: Materialized views and streaming tables are implemented on Declarative Pipelines, so creating one in DBSQL creates the pipeline that refreshes it.

Why: 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.

Senior depth: The thing I care about here is that an object created in two clicks carries the same production obligations as a pipeline someone deployed deliberately. So the rule I set is that anything a dashboard depends on is defined in a bundle, owned by a service principal, and deployed through CI, while the SQL editor is for exploration. That is not bureaucracy for its own sake — the recurring incident is a gold-layer materialized view that stops refreshing because its creator's account was deactivated, and nobody notices until the numbers stop moving.

Common mistake: Treating a DBSQL materialized view as a lightweight object with no infrastructure or owner behind it.

Follow-up they will ask: Who should own the materialized views in your gold schema?

Say this first: Streaming table for the append-only ingest with its checkpoint, materialized view for the aggregate — they have different update semantics, not just different names.

Why: 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.

CREATE STREAMING TABLE bronze.events AS
SELECT * FROM STREAM read_files('s3://landing/events/', format => 'json');

CREATE MATERIALIZED VIEW gold.events_daily AS
SELECT date(ts) AS d, count(*) AS n FROM bronze.events GROUP BY 1

Senior depth: I decide this on update semantics rather than on freshness, because freshness is tunable and semantics are not. Append-once ingest and restatable aggregates are genuinely different problems, and the moment you pick the wrong object you inherit a pile of hand-written state management — watermarks, late-data policies, dedup keys — that the other object would have handled. Where it gets interesting is a gold table that must be both low-latency and restatable; there I would be explicit about the staleness the business accepts rather than pretending one object gives you both for free.

Common mistake: Choosing between them on expected latency alone, without considering whether results need to be restated.

Follow-up they will ask: What breaks first if you try to maintain that aggregate as a streaming table?

Say this first: 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.

Why: 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.

SELECT statement_text, COUNT(*) AS runs, SUM(total_duration_ms)/1000 AS secs
FROM system.query.history
WHERE start_time >= current_date() - INTERVAL 7 DAYS
GROUP BY ALL ORDER BY secs DESC LIMIT 20

Senior depth: The trap in a serving layer is that materialized views are easy to create and nobody ever deletes one. So I design it as a small, owned, versioned set — a dozen grains, not two hundred — and make adding one a review conversation about whether an existing grain covers it. Then I keep a standing report of refresh cost per view against the read traffic it serves, and I retire the bottom of that list every quarter. Without that loop you end up paying continuously to keep results fresh for dashboards that were decommissioned a year ago.

Common mistake: Creating a materialized view per dashboard on request, ending with hundreds of overlapping refreshes nobody owns.

Follow-up they will ask: How would you find the materialized views that are no longer worth refreshing?