# Streaming tables vs materialized views: refresh and full refresh

When a pipeline runs again, a streaming table reads only the records that are new since its last run and keeps every row it wrote before, even after its query changes. A materialized view is recomputed from what it reads, so it always reflects its current query. To rebuild a streaming table with a changed query, run a full refresh of it, which clears its rows and checkpoint and reads the source again, and refresh the tables that read it.

- Page: https://tecyfy.com/data-engineering/course/medallion-pipelines/refresh
- Course: Data engineering course (https://tecyfy.com/data-engineering/course), Module 7: Pipelines: medallion with declarative pipelines, Streaming tables, materialized views and full refresh
- Parts of this module: A first pipeline, bronze to gold (https://tecyfy.com/data-engineering/course/medallion-pipelines); Streaming tables, materialized views and full refresh (https://tecyfy.com/data-engineering/course/medallion-pipelines/refresh); Rules, quarantine and a quality gate (https://tecyfy.com/data-engineering/course/medallion-pipelines/expectations); Lakeflow pipelines on Databricks (https://tecyfy.com/data-engineering/course/medallion-pipelines/on-databricks); Build it: the shop's pipeline, checked (https://tecyfy.com/data-engineering/course/medallion-pipelines/build-it)

At the end of this part you can:

- Say what a streaming table and a materialized view each do with old rows when the pipeline runs again
- Rebuild a streaming table after its query changes with --full-refresh, and update the tables above it with --refresh
- Catch a mistake in a pipeline before it runs, with spark-pipelines dry-run

[Part 1](/data-engineering/course/medallion-pipelines) built the shop's pipeline: `bronze.py`, `silver.py` and `gold.sql` in the `transformations/` folder of a project made by `spark-pipelines init --name shop`, with `make_files.py` writing 2 days of order files into `landing/`. To start here, make those files from part 1, write both days and run the pipeline once with `spark-pipelines run --conf spark.sql.catalogImplementation=hive`. Every run below uses that same command.

## Run it again with nothing new

Run the pipeline again, before any new file arrives. It ends with `Run is COMPLETED.`, and `python show.py bronze_orders` prints the same 5 rows. The 2 kinds of table did different things:

- `bronze_orders`, the streaming table, read nothing. Its checkpoint, in the `pipeline-storage/` folder named in the spec, lists both files as read.
- `orders` and `customer_spend`, the materialized views, were computed again from what they read, and came out the same.

That difference decides what happens when a query changes.

## Change a query in a streaming table

Say someone wants statuses in capitals, and adds 1 line to `bronze.py`, after `.load("landing")`:

```python
        .withColumn("status", F.upper("status"))
```

Then the shop's system drops the file of 2026-10-03, which cancels order 1002 and adds order 1005:

```bash
printf 'order_id,customer_id,status,amount,updated_at\n1002,C02,cancelled,12.00,2026-10-03 09:30:00\n1005,C02,placed,18.25,2026-10-03 16:20:00\n' > landing/orders-2026-10-03.csv
```

Run the pipeline, then `python show.py bronze_orders customer_spend`:

```text
bronze_orders
+--------+-----------+---------+------+-------------------+---------------------------------------+---------------------+
|order_id|customer_id|status   |amount|updated_at         |_bad                                   |source_file          |
+--------+-----------+---------+------+-------------------+---------------------------------------+---------------------+
|1001    |C01        |placed   |25.50 |2026-10-01 09:15:00|NULL                                   |orders-2026-10-01.csv|
|1001    |C01        |shipped  |25.50 |2026-10-02 08:00:00|NULL                                   |orders-2026-10-02.csv|
|1002    |C02        |CANCELLED|12.00 |2026-10-03 09:30:00|NULL                                   |orders-2026-10-03.csv|
|1002    |C02        |placed   |12.00 |2026-10-01 10:02:00|NULL                                   |orders-2026-10-01.csv|
|1003    |C03        |placed   |NULL  |2026-10-01 11:30:00|1003,C03,placed,abc,2026-10-01 11:30:00|orders-2026-10-01.csv|
|1004    |C01        |placed   |40.00 |2026-10-02 12:45:00|NULL                                   |orders-2026-10-02.csv|
|1005    |C02        |PLACED   |18.25 |2026-10-03 16:20:00|NULL                                   |orders-2026-10-03.csv|
+--------+-----------+---------+------+-------------------+---------------------------------------+---------------------+

customer_spend
+-----------+------+-----+
|customer_id|orders|spend|
+-----------+------+-----+
|C01        |2     |65.50|
|C02        |2     |30.25|
+-----------+------+-----+
```

Only the 2 new lines are in capitals. A streaming table never reads a row twice, so the rows it wrote before the change keep the old query's result. Databricks' docs give the same example with `LOWER` and `UPPER`. Gold is now wrong: silver took `CANCELLED` as 1002's newest state, and `status != 'cancelled'` does not match it, so C02's cancelled order still counts.

## Rebuild with a full refresh

A full refresh clears a table's rows and checkpoint and computes it from the start. Run it for bronze:

```bash
spark-pipelines run --conf spark.sql.catalogImplementation=hive --full-refresh bronze_orders
```

The log says:

```text
2026-10-18 08:15:12: Flow 'spark_catalog.default.orders' is EXCLUDED.
2026-10-18 08:15:12: Flow 'spark_catalog.default.customer_spend' is EXCLUDED.
2026-10-18 08:15:14: Flow spark_catalog.default.bronze_orders has COMPLETED.
2026-10-18 08:15:15: Run is COMPLETED.
```

Bronze reread all 3 files and every status is now in capitals. But naming 1 table leaves out every other flow, so silver and gold still hold the last run's results until they run again.

Capitals in bronze were the wrong idea anyway. Bronze keeps the data as it arrived, and cleaning belongs in silver, where it is recomputed every run; [part 3](/data-engineering/course/medallion-pipelines/expectations) does that there. Remove the `upper` line, and rebuild bronze and update the tables above it in 1 run:

```bash
spark-pipelines run --conf spark.sql.catalogImplementation=hive --full-refresh bronze_orders --refresh orders,customer_spend
```

Bronze is lower case again, 1002 is `cancelled` in silver, and `customer_spend` gives C02 1 order and 18.25. `--full-refresh-all` rebuilds every table, and a run with no option updates every table and reads only new records.

## Check before you run

`dry-run` reads every file and checks the code and the graph, without reading data or writing a table. Change `FROM orders` in `gold.sql` to `FROM order` and run `spark-pipelines dry-run --conf spark.sql.catalogImplementation=hive`:

```text
2026-10-18 08:15:44: Failed to resolve flow: 'spark_catalog.default.customer_spend'.
Error: [TABLE_OR_VIEW_NOT_FOUND] The table or view `spark_catalog`.`default`.`order` cannot be found. Verify the spelling and correctness of the schema and catalog.
```

Put `orders` back. A typo found this way costs a few seconds, not a failed run halfway through.

## 2 things to know when running locally

- In this course's runs on PySpark 4.2.0, a query on another pipeline table that used `withColumn`, such as `spark.read.table("bronze_orders").withColumn(...)`, ran, from the second run on, before that table had updated, and read the last run's rows. Write such queries with `select`, as this module does. `withColumn` inside bronze's own read, above, was not affected.
- Changing which columns a materialized view has fails on the next run with the local catalog, with an error that the table cannot be altered, and a full refresh does not help. Run `DROP TABLE` on it in a Spark session with `enableHiveSupport()`, as `show.py` makes, then run the pipeline.

## Check yourself

1. You add upper("status") to bronze_orders, and the next file has order 1002 cancelled. Why does customer_spend still count 1002's 12.00 for C02?

   A. Bronze wrote the new line as CANCELLED, silver took it as 1002's newest state, and gold's status != 'cancelled' does not match CANCELLED
   B. The streaming table did not read the new file
   C. The materialized views were not recomputed
   D. The old placed line of 1002 is newer than the cancelled one

2. You have fixed the query of bronze_orders and want bronze rebuilt from all the files, and silver and gold updated from it, in 1 run. Which command does that?

   A. spark-pipelines run, since the materialized views recompute anyway
   B. spark-pipelines run --full-refresh bronze_orders
   C. spark-pipelines run --full-refresh bronze_orders --refresh orders,customer_spend
   D. spark-pipelines dry-run --full-refresh-all

### Answers

1. A. Only the lines read after the change are upper case, because a streaming table never rereads what it wrote. Silver is recomputed and picks the cancelled line, the newest, but gold compares with lower-case 'cancelled', so the order still counts. A query change in a streaming table quietly mixes 2 versions of the query in 1 table.
2. C. A plain run keeps bronze's old rows. --full-refresh bronze_orders alone rebuilds bronze but leaves out every other flow, so silver and gold still hold the last run's results. Naming them in --refresh updates them in the same run. --full-refresh-all would also do it, by rebuilding every table, and dry-run writes nothing.

## Questions

### What is the difference between a streaming table and a materialized view?

A streaming table reads each record of its source once and appends it, so each run reads only what is new, which suits data that only grows, such as files landing. A materialized view stores the result of a query and is recomputed when the pipeline runs, so it always matches what it reads, which suits cleaning, joining and totals.

### What does a full refresh do in Spark Declarative Pipelines?

It clears a table's data and checkpoints and computes it again from scratch, so a streaming table rereads its whole source. Run it after changing a streaming table's query. --full-refresh names the tables to rebuild, --full-refresh-all rebuilds every table, and --refresh names tables to update without clearing them.

## Sources

- [Spark Declarative Pipelines programming guide](https://spark.apache.org/docs/latest/declarative-pipelines-programming-guide.html)
- [Streaming tables](https://docs.databricks.com/aws/en/ldp/streaming-tables)
- [Materialized views](https://docs.databricks.com/aws/en/ldp/materialized-views)
- [Pipeline concepts](https://docs.databricks.com/aws/en/ldp/concepts)