Streaming table or materialized view? How to choose in a Databricks pipeline

Both keep a table up to date. One recomputes the answer, the other only reads what arrived. The difference shows up in your bill.

The job had been fine for a year. It read the bronze orders table, cleaned it up, joined a small product lookup, and wrote a silver table. Twenty minutes, every night, no complaints.

Then one Tuesday it was still running at nine in the morning.

Nobody had touched the code. The cluster was the same. The only thing that had changed is that bronze had grown from two million rows to ninety million, and the job was reading all ninety million every single night to produce a table that had only gained a few thousand new rows.

That is the moment most people meet this question for the first time. Should this thing be a materialized view, or a streaming table?

Watercolor diagram comparing a materialized view recomputing a whole result against a streaming table processing only new rows with a checkpoint

What a materialized view actually is

A materialized view is a saved query plus its saved answer.

You write a SELECT statement. Databricks runs it, stores the result as a real Delta table, and remembers the query that produced it. When you refresh it, Databricks works out the answer again and replaces what was there.

The important word is "again". A materialized view is defined by its result, not by the rows it has seen. If the source changed in any way, including updates and deletes in the middle of history, the view can still be correct, because it is willing to redo the work.

Sometimes it redoes all of it. Sometimes, when the query is simple enough and the source is a Delta table, it can work out a smaller update and do only that part. You do not control which path it picks directly. You influence it by how you write the query.

CREATE OR REFRESH MATERIALIZED VIEW workspace.default.orders_by_day
AS
SELECT
  order_date,
  COUNT(*) AS order_count,
  SUM(order_total) AS revenue
FROM workspace.default.bronze_orders
GROUP BY order_date

That is a good materialized view. It is an aggregate. The answer is small, the source can change anywhere, and you never want to think about which rows you have already counted.

What a streaming table actually is

A streaming table is defined by the rows it has read.

It keeps a checkpoint, which is a small piece of bookkeeping that records how far through the source it got. On each run it asks a narrower question: what is new since last time? Then it processes only that.

CREATE OR REFRESH STREAMING TABLE workspace.default.silver_orders
AS
SELECT
  order_id,
  customer_id,
  order_date,
  order_total,
  current_timestamp() AS ingested_at
FROM STREAM(workspace.default.bronze_orders)
WHERE order_total IS NOT NULL

The STREAM() wrapper is the whole difference. Without it you are reading the table. With it you are reading the changes to the table.

This is why the cost stays flat as the table grows. Ninety million rows in the source does not matter if only four thousand arrived today. That is the fix for the job in the story.

The decision, in three questions

Forget the feature lists. Ask these in order.

Does the source only ever gain rows? Streaming tables want append-only sources. If rows get updated or deleted in place, a plain streaming read will either fail or quietly miss the change, because the checkpoint has already moved past that part of the file. Append-only means a streaming table is available to you. Anything else and you are looking at a materialized view, or at change data feed, which is a bigger conversation.

Is the output a copy or a summary? Row-by-row work, cleaning, filtering, flattening, adding a timestamp, all suit a streaming table. Aggregations across the whole history, joins to slowly changing lookups, anything where a new row can change an old answer, suit a materialized view.

Will you need to reprocess history? Streaming tables are efficient because they refuse to look back. The day you fix a bug in the transformation logic, you have to full refresh the table, which drops the checkpoint and starts over. That is a normal operation, not a disaster, but it needs to be a decision you made rather than a surprise. Materialized views reprocess by design, so a logic fix is just a refresh.

A short version of all three: use a streaming table for bronze to silver, and a materialized view for silver to gold. That is the default, and the medallion layers line up with it almost too neatly. Just do not follow it past the point where it makes sense.

What each one costs when the table grows

This is the part people discover late.

A materialized view on a growing source has a cost that grows with the source, unless the query stays inside the shapes that allow an incremental update. Add a nondeterministic function, an unsupported join or a window that reaches across the whole table, and you have quietly signed up for a full recompute every refresh. It still gives the right answer. It just gives it more expensively every week.

A streaming table has a cost that grows with the new data, which is usually flat. Its risk is different. State is not free either. Stateful work such as deduplication or streaming aggregation keeps things in memory between runs, and without a watermark to tell it what is too old to matter, that state grows forever.

So the honest summary is that neither is cheap by default. One can hide a growing full scan, the other can hide growing state.

Two traps worth naming

Restating history on a streaming table. Someone finds three months of bad rows in bronze and fixes them in place. The streaming table downstream has already passed those files and will never look at them again. The silver table is now wrong and nothing failed. You need a full refresh, and you need to know that before it happens rather than after a stakeholder notices.

The silent full refresh on a materialized view. The view was incremental when you wrote it. Then someone added a helpful column using a function that cannot be tracked incrementally, and the refresh went from ninety seconds to forty minutes. Nothing broke, so nobody looked. Check the event log after you change a view definition, not just after you create it.

Try both on Free Edition

You can build the whole comparison in a Lakeflow Declarative Pipeline on Databricks Free Edition. Use one of our sample files as bronze, write a streaming table for the row-level clean up, then a materialized view for the daily summary on top of it, and watch the run details on the second run. The streaming table will report a handful of new rows. The materialized view will tell you whether it managed an incremental update or recomputed everything.

That second run is the lesson. Everything else here is just vocabulary for what you see in it.

Continue learning