Microsoft Fabric Capabilities – VACUUM command
Mastering Microsoft Fabric is not so easy, however following some best practices will help you to reduce your spends and improve performance. With this post we started our new series focused on Microsoft Fabric capabilities and preparing for DP-600 and DP-700 exams. It’s our first article explaining why it’s important using Vacuum command and what it does.
Is it so easy to master Lakehouses?
Microsoft Fabric, as well as, Power BI seems like an ecosystem with significantly low “entrance fee”. You don’t need to spend more than a day, week, or month to create your first stunning dashboard or data “warehouse”. It’s really easy to use drag n’ drop in Power BI Desktop to build a report using excel data or use Dataflows Gen2 for extracting data from Salesforce or any other supported source to a Lakehouse. However, it’s just a visible part of the “Fabric iceberg”.
Let’s look at the Lakehouse and learn more about what’s happening behind the tables view. All the tables in the Lakehouse are Delta Tables. Delta Lake is an open-source architecture used in different systems, including Databricks, Fabric, etc. In a nutshell, it’s a combination of parquet files (storage layer) with metadata layer (Delta Tables) and time machine (Delta Log). It’s a quite simplified definition, however it’s enough at this stage. If you eager to learn more about it, you can do it here: https://docs.delta.io/latest/index.html#
Behind the table view
Go back to our Lakehouse and open one of the tables in the Lakehouse Explorer. It’s Delta Table. You can see all data here, notice data types, etc. It’s crucial to use proper data types for your data and always define them. Sometimes can upload data with “any” data type to delta table, however, you should not ever think about doing this, unless you like pain.
Now you can explore what happens under the hood. Just invoke your table menu using the right mouse button and click “View files”.
It’s quite interesting that my Lakehouse stores all the versions of data from the beginning. And yes, I’m paying for every byte stored in the cloud. Fortunately, I don’t have a massive table, so it’s cheap. However, if I don’t need to keep historical versions for long, it’s a very good idea to remove legacy data from Lakehouse.
How to use “VACUUM cleaner”
It’s very easy to do it using VACUUM command. There are few ways to do it:
- Using Lakehouse Explorer:
a. Just invoke table menu and select Maintenance.
b. Select “Run VACCUM command using retention threshold” and set it. Default value is 7 days.
c. Press “Run now”
- Run as SQL command in a notebook:
%%sql
VACUUM my_lakehouse.my_table RETAIN 168 HOURS;
That’s it. Once the operation completes, you’ll notice you have parquet files just for last XX days.
That’s all for today. I hope you find this helpful, and you’ll invoke VACUUM cleaner for your lakehouse and keep it clean.
You should have noticed some other maintenance commands like Optimize or Apply V-Order. We’ll cover these topics in our next blog posts.
To learn more, you can also join our Flex or Intensive DP-600 Microsoft Fabric Analytics Engineer course.
