topic Re: Warehouse - VACUUM or RESTORE? in Data Warehouse

Warehouse - VACUUM or RESTORE?

govindarajan_d — Thu, 29 Feb 2024 09:46:58 GMT

Hi All,

In Lakehouse we have the option to VACUUM the data in a periodic manner and we can use Spark to do the same. We can also restore the table to older version if required.

But is it possible to do the same in Warehouse? Because I don't see that option even though Warehouse also stores the data in Delta format.

Re: Warehouse - VACUUM or RESTORE?

AndyDDC — Thu, 29 Feb 2024 12:59:55 GMT

Hi govindarajan_d it's all automatic in the Warehouse (which is a bit of a pain) so no exposure to vacuum or optimise.

https://youtu.be/uKgXKAw00XM?si=hz1rllG1lJf5Hr9s

Re: Warehouse - VACUUM or RESTORE?

govindarajan_d — Thu, 29 Feb 2024 16:35:07 GMT

Ok. VACUUM and OPTIMIZE is kind of ok to have automatic maintenance.

How about RESTORE? If I can't do that, then it makes Warehouse less attractive option than lakehouse.

Re: Warehouse - VACUUM or RESTORE?

AndyDDC — Thu, 29 Feb 2024 18:34:44 GMT

In terms of restore, you can use restore points https://blog.fabric.microsoft.com/en-US/blog/the-art-of-data-warehouse-recovery-within-microsoft-fabric/#:~:text=Restore%20points%20are%20recovery%20points,any%20given%20point%20in%20time.

Re: Warehouse - VACUUM or RESTORE?

govindarajan_d — Fri, 01 Mar 2024 01:45:14 GMT

Hi AndyDDC,

Restore point restores all tables to a older version. So its not the same as Delta restore

Re: Warehouse - VACUUM or RESTORE?

AndyDDC — Fri, 01 Mar 2024 07:09:28 GMT

nope, no way of restoring a warehouse table individually. Only way I can think of is creating a clone of a table at certain points to "snapshot" the table

Re: Warehouse - VACUUM or RESTORE?

govindarajan_d — Fri, 01 Mar 2024 09:57:28 GMT

Ok. Then I think compared to Warehouse, a Lakehouse would be better in that regards. Warehouse needs to add that property if it needs to compete with modern warehouses.