topic Restoring a table from a Delta live pipeline in Data Engineering

Restoring a table from a Delta live pipeline

Edthehead — Thu, 02 Jan 2025 03:28:24 GMT

I have a DLT pipeline running to ingest files from storage using autoloader. We have a bronze table and a Silver table.

A question came up from the team on how to restore DLT tables to a previous version in case of some incorrect transformation.

When we try to execute the RESTORE to version command we get the below error.

[STREAMING_TABLE_OPERATION_NOT_ALLOWED.UNSUPPORTED_OPERATION] The operation RESTORE is not allowed: The operation is not supported on Streaming Tables. SQLSTATE: 42601

Then I tried deleting some records from the Silver table and restart the pipeline. Since the tables are streaming, the deleted records are not repopulated from silver.

So my questions are :

1) What is the best way to restore a DLT table to its previous version and do a backfill or rerun of the last DLT run?

2) Do we have to do a full refresh to accomplish this?

Re: Restoring a table from a Delta live pipeline

Walter_C — Thu, 02 Jan 2025 12:10:39 GMT

The RESTORE command is not supported on streaming tables, which is why you encountered the error. Instead, you can use the TIME TRAVEL feature of Delta Lake to query previous versions of the table. You can use the VERSION AS OF or TIMESTAMP AS OF clauses in your SQL queries to access historical data.
Example:

SELECT * FROM my_table VERSION AS OF 10

Since the tables are streaming, simply deleting records and restarting the pipeline will not repopulate the deleted records. Instead, you need to perform a full refresh of the pipeline.
To do a full refresh, you can use the "Full Refresh all" option in the DLT pipeline UI. This will recompute and backfill all tables in the pipeline.
Alternatively, you can use the "Select Table for Refresh" option to refresh only specific tables, such as your Silver tables.