topic Re: Databricks SDP (Spark declarative Pipelins) Overwrite table in Data Engineering

Databricks SDP (Spark declarative Pipelins) Overwrite table

pvrcloudtech — Sun, 04 Oct 2026 15:21:10 GMT

1) Consider I have orders folder which orders_1.csv file and it is loaded to orders tables

orders/ ============> load to order table

orders_1.csv

next day new file (orders_2.csv) arrived

orders/ =========> take only 2nd file -----> overwrite the order table

orders_1.csv

orders_2.csv

Here streaming table only appending the data to orders table. Materialized view is considering both files.

My requirement is overwrite the orders table.

Can you help me how to do that ??

Re: Databricks SDP (Spark declarative Pipelins) Overwrite table

SumeshKashyap — Sun, 04 Oct 2026 16:16:50 GMT

Streaming tables are append-only by design, so they can't overwrite. Use a materialized view that keeps only the rows from the newest file, via the _metadata column:

sql

CREATE OR REFRESH MATERIALIZED VIEW orders AS

SELECT * EXCEPT (file_time)

FROM (

SELECT *,

_metadata.file_modification_time AS file_time

FROM read_files('/path/orders/', format => 'csv', header => true)

)

QUALIFY file_time = MAX(file_time) OVER ();

Each refresh then replaces the table contents with only the latest file (orders_2.csv, then orders_3.csv, and so on).

If the folder grows large, ingest with a streaming table (Auto Loader) into a bronze table, storing _metadata.file_name and _metadata.file_modification_time as columns, and build the same "latest file only" materialized view on top of it. That way all the files aren't re-read on every refresh.

Hope this helps

Re: Databricks SDP (Spark declarative Pipelins) Overwrite table

vannurswamy — Sun, 04 Oct 2026 16:25:25 GMT

A Streaming Table may not be the right fit for this requirement. It is designed to process new files incrementally, so when orders2.csv arrives, it will append the new data.

If each new file is a full snapshot and should completely replace the previous data, you can use a batch job to read only the latest file and overwrite the target Delta table using:

df.write.mode("overwrite").saveAsTable("orders")

Another option is to keep only the latest file in a current/ folder and move older files to an archive/ folder.

So in this case, I would prefer a batch overwrite approach rather than a Streaming Table.