{"id":98289,"date":"2023-09-27T20:02:13","date_gmt":"2023-09-27T20:02:13","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=98289"},"modified":"2024-09-03T20:15:18","modified_gmt":"2024-09-03T20:15:18","slug":"microsoft-fabric-and-the-delta-tables-secrets","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/blogs\/microsoft-fabric-and-the-delta-tables-secrets\/","title":{"rendered":"Microsoft Fabric and the Delta Tables Secrets"},"content":{"rendered":"

Microsoft Fabric storage uses OneLake <\/strong>and Delta Tables<\/strong>, the core storage of all Fabric objects, as explained in my introduction to Microsoft Fabric<\/a>.<\/p>\n

Either if you use lakehouses, data warehouses, or even datasets, all the data is stored in the OneLake<\/strong>. OneLake<\/strong> uses Delta Tables as storage. In this way, we need to be aware of some “secrets” related to delta table storage in order to make the best decisions possible for our data platform solution.<\/p>\n

Let’s analyze some of the Parquet<\/strong> and Delta Table<\/strong> behaviors.<\/p>\n

Parquet and Delta<\/h2>\n

Parquet is an open source format which became famous as one of the most used formats in data lakes. It’s a columnar storage format intended to store historical data.<\/p>\n

One very important “secret”, and the main subject of this blog is: Parquet format is immutable<\/em><\/strong>. It’s not possible to change the content of a Parquet file. It’s intended for historical data.<\/p>\n

Although the main objective is for historical data, many people and companies worked on this problem. The result was Delta Tables<\/strong>.<\/p>\n

Delta Tables<\/strong> use the Parquet<\/strong> format, it’s not a different format. The immutable behavior continues. However, Delta Tables<\/strong> use an additional folder as a transaction log. The operations are registered in the delta logs, marking records with updates and deletes.<\/p>\n

In this way, the underlying immutable behavior is still present, but we can work with the data in a transactional way, allowing updates and deletes, for example.<\/p>\n

Time Travel<\/h2>\n

The delta logs allow us to make what’s called Time Travel<\/strong>. We can retrieve the information from a delta table in the way it was on a specific date and time, as long as the logs are kept complete.<\/p>\n

The access to the entire historical of data changes is an important resource for a data storage.<\/p>\n

Data Modelling<\/h2>\n

The need to keep historical data is way older than technologies such as Delta Time Travel, which allow us to keep them. The Data Modelling techniques, such as Data Warehouse<\/strong> modelling, proposed solutions for historical storage a long time ago.<\/p>\n

One of the features used for this purpose is called Slowly Changing Dimensions<\/strong>, or Dimension Type 2<\/strong>. When we design a start schema, we decide which dimensions should keep an entire history and which ones aren\u2019t worth the trouble and a simple update on the records would be enough.<\/p>\n

For example, let’s consider a dimension called Customer<\/em>. If we decide to keep the dimension as a SCD<\/strong> dimension, every time a customer record is changed in production, we create a new version of the record in the intelligence storage (data warehouse, data lake, whatever the name).<\/p>\n

On the other hand, if we decide that a dimension is not worth keeping a history, we can just update the record in the intelligence storage when needed.<\/p>\n

The decision of using a SCD<\/strong> dimension or not, and many more, are all made during modelling. They are independent of any technical feature capable of keeping the history of the data. The history is designed during modelling and kept by us.<\/p>\n

Choosing Between Time Travel and Data Modelling<\/h2>\n

We have the possibility to use data modelling to control the history of our storage, or use the technical features, such as Time Travel.<\/p>\n

This leads to several possibilities with different consequences:<\/p>\n\n\n\n\n\n
\n

Approach<\/strong><\/p>\n<\/td>\n

\n

Possible Results<\/strong><\/p>\n<\/td>\n<\/tr>\n

\n

We can choose to rely on time travel for the entire history storage of our data<\/p>\n<\/td>\n

\n

This will tie the history of your data solution with a specific technological feature. It also creates the risk of performance issues related to executing updates in a delta table. Let’s talk more in depth about the technology and leave the decision to you.<\/p>\n<\/td>\n<\/tr>\n

\n

We can choose to rely on the modelling for the entire history and not depend on the time travel feature<\/p>\n<\/td>\n

\n

This creates additional modelling and ingestion work, plus additional work to avoid performance issues with the delta log. The work to avoid performance issues with the delta log may be easier than if we were really relying on it.<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

The decision whether we should rely on modelling, on technology or stay somewhere in the middle is complex enough to generate many books. What’s important on this blog is to understand the decision is present when designing an architecture with Microsoft Fabric<\/strong>.<\/p>\n

In order to make a well-informed decision, we need to understand how the delta tables process updates\/deletes.<\/p>\n

Lakehouse Example<\/h2>\n

The example will be made using a table called Fact_Sale<\/em>. You can use a pipeline to import the data from https:\/\/azuresynapsestorage.blob.core.windows.net\/sampledata to the files area of one Fabric Lakehouse. The article about Lakehouse and ETL<\/a> explains how to build a pipeline to bring data to the Files<\/strong> area.<\/p>\n

The article https:\/\/www.red-gate.com\/simple-talk\/blogs\/microsoft-fabric-using-notebooks-and-table-partitioning-to-convert-files-to-tables\/<\/a>\u00a0explains this import process and how we can partition the data by Year and Quarter, making a more interesting example. The notebook code to import the partitioned data to the Tables<\/strong> area of the lakehouse is the following:<\/p>\n

<\/p>\n

\n
from<\/span> pyspark.sql.functions<\/span> import<\/span> col, year, month, quarter\r\ntable_name =<\/span> 'fact_sale'<\/span>\r\ndf =<\/span> spark.<\/span>read.<\/span>format(\"parquet\"<\/span>).<\/span>load('Files\/fact_sale_1y_full'<\/span>)\r\ndf =<\/span> df.<\/span>withColumn('Year'<\/span>, year(col(\"InvoiceDateKey\"<\/span>)))\r\ndf =<\/span> df.<\/span>withColumn('Quarter'<\/span>, quarter(col(\"InvoiceDateKey\"<\/span>)))\r\ndf =<\/span> df.<\/span>withColumn('Month'<\/span>, month(col(\"InvoiceDateKey\"<\/span>)))\r\ndf.<\/span>write.<\/span>mode(\"overwrite\"<\/span>).<\/span>format(\"delta\"<\/span>).<\/span>partitionBy(\"Year\"<\/span>,\"Quarter\"<\/span>).<\/span>save(\"Tables\/\"<\/span> +<\/span> table_name)\r\n<\/pre>\n<\/div>\n
The default size for a Parquet file in the Fabric environment is 1GB (1073741824 bytes). This is defined by the session configuration spark.microsoft.delta.optimizeWrite.binSize <\/strong>and has the purpose to avoid the delta lake small files problem. Although this is a common problem, the writing on delta tables can cause consequences when the file is too big. Let’s analyze this as well.<\/p>\n
You can find more about Spark configuration on this blog<\/a> and more about the small files problem on this link<\/a>.<\/p>\n
On our example, this configuration generated a single parquet file for each quarter, this will help to identify what’s happening during the example.<\/p>\n
\"A<\/p>\n
The Data we Have<\/h2>\n
We need to identify the total of records we have, the total records for each quarter, and the minimum and maximum SalesKey<\/em> value in each quarter in order to build the example.<\/p>\n
We can use the SQL Endpoint to run the following queries:<\/p>\n
 SELECT<\/span>\u00a0Count<\/span>(<\/span>*<\/span>)<\/span>\u00a0AS<\/span>\u00a0TotalRecords<\/span> 
\nFROM<\/span>\u00a0\u00a0\u00a0fact_sale<\/span>\u00a0 <\/span><\/div>\n
\"A<\/p>\n
 SELECT<\/span>\u00a0quarter<\/span>,<\/span> 
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Count<\/span>(<\/span>*<\/span>)<\/span>\u00a0\u00a0\u00a0\u00a0\u00a0SalesCount<\/span>,<\/span> 
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Min<\/span>(<\/span>salekey<\/span>)<\/span>\u00a0MinKey<\/span>,<\/span> 
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Max<\/span>(<\/span>salekey<\/span>)<\/span>\u00a0MaxKey<\/span> 
\nFROM<\/span>\u00a0\u00a0\u00a0fact_sale<\/span> 
\nGROUP<\/span>\u00a0\u00a0BY<\/span>\u00a0quarter<\/span>\u00a0 <\/span><\/div>\n
\"A<\/p>\n
Test query and Execution Time<\/h2>\n
For test purposes, let’s use the following query:<\/p>\n
 SELECT<\/span>\u00a0customerkey<\/span>,<\/span> 
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Sum<\/span>(<\/span>totalincludingtax<\/span>)<\/span>\u00a0TotalIncludingTax<\/span> 
\nFROM<\/span>\u00a0\u00a0\u00a0fact_sale<\/span> 
\nGROUP<\/span>\u00a0\u00a0BY<\/span>\u00a0customerkey<\/span>\u00a0 <\/span><\/div>\n
This query makes a grouping on all our records by CustomerKey<\/em>, across the quarter partitions, creating a total of Sales by customer. On each test, I will execute this query 10 times and check the initial, minimum, and maximum execution time.<\/p>\n
 <\/p>\n\n\n\n\n\n\n\n
\n
First 10 Executions<\/strong><\/p>\n<\/td>\n<\/tr>\n
\n
Initial<\/p>\n<\/td>\n
\n
2.639 seconds<\/p>\n<\/td>\n<\/tr>\n
\n
Minimum<\/p>\n<\/td>\n
\n
1.746 seconds<\/p>\n<\/td>\n<\/tr>\n
\n
Maximum<\/p>\n<\/td>\n
\n
3.32 seconds<\/p>\n<\/td>\n<\/tr>\n
\n
Average<\/p>\n<\/td>\n
\n
2.292 seconds<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
Updating one single record<\/h2>\n
As the first test, let’s update one single sales record in each quarter. We will use a notebook to execute the following code:<\/p>\n
<\/p>\n
\n
%%<\/span>sql\r\nupdate fact_sale set<\/span> TotalIncludingTax=<\/span>TotalIncludingTax *<\/span> 2<\/span>\r\nwhere SaleKey =<\/span> 6000000<\/span>;\r\n\r\nupdate fact_sale set<\/span> TotalIncludingTax=<\/span>TotalIncludingTax *<\/span> 2<\/span>\r\nwhere SaleKey =<\/span> 15624569<\/span> ;\r\n\r\nupdate fact_sale set<\/span> TotalIncludingTax=<\/span>TotalIncludingTax *<\/span> 2<\/span>\r\nwhere SaleKey =<\/span> 35270205<\/span>;\r\n\r\nupdate fact_sale set<\/span> TotalIncludingTax=<\/span>TotalIncludingTax *<\/span> 2<\/span>\r\nwhere SaleKey =<\/span> 45032105<\/span>;\r\n<\/pre>\n<\/div>\n
After executing these updates, if you look on the table Parquet<\/strong> files, you will notice each parquet file containing the record updated was duplicated.<\/p>\n
\"A<\/p>\n
The parquet file continues to be immutable, in order to update a record, the entire file is duplicated with the record update and the delta logs register this update.<\/p>\n
In our example, we updated 4 records, but each one was in a different parquet file. As a result, all the parquet files were duplicated (one for each quarter).<\/p>\n
Remember the default file size is 1GB. A single record update will result in the duplication of a 1GB file. The big file size may have a bad side effect if you decide to use upserts or deletes too much.<\/p>\n
Testing the Execution<\/h2>\n
Let\u2019s test the execution of our sample query again and get the number after these duplication of parquet files:<\/p>\n\n\n\n\n\n\n\n\n
\n
After Updating 1 record<\/strong><\/p>\n<\/td>\n<\/tr>\n
\n
Initial<\/p>\n<\/td>\n
\n
6.692 seconds<\/p>\n<\/td>\n<\/tr>\n
\n
Minimum<\/p>\n<\/td>\n
\n
1.564 seconds<\/p>\n<\/td>\n<\/tr>\n
\n
Maximum<\/p>\n<\/td>\n
\n
3.166 seconds (ignoring initial)<\/p>\n<\/td>\n<\/tr>\n
\n
Average<\/p>\n<\/td>\n
\n
2.8955 seconds<\/p>\n<\/td>\n<\/tr>\n
\n
Average Ignoring Initial<\/p>\n<\/td>\n
\n
2.4374 seconds<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
There is a initial execution substantially slower and the average, even ignoring the initial execution, is slower, but not much.<\/p>\n
Updating many records<\/h2>\n
Let’s change a bit the script and update a higher volume of records each quarter. You can execute the script below many times and each time you execute you will see the parquet files being duplicated.<\/p>\n
<\/p>\n
\n
%%<\/span>sql\r\nupdate fact_sale set<\/span> TotalIncludingTax=<\/span>TotalIncludingTax *<\/span> 2<\/span>\r\nwhere SaleKey <<\/span> 7000000<\/span>;\r\n\r\nupdate fact_sale set<\/span> TotalIncludingTax=<\/span>TotalIncludingTax *<\/span> 2<\/span>\r\nwhere SaleKey ><\/span> 14624564<\/span> AND SaleKey <<\/span> 18624564<\/span>;\r\n\r\nupdate fact_sale set<\/span> TotalIncludingTax=<\/span>TotalIncludingTax *<\/span> 2<\/span>\r\nwhere SaleKey ><\/span> 28270201<\/span> AND SaleKey <<\/span> 35270201<\/span>;\r\n\r\nupdate fact_sale set<\/span> TotalIncludingTax=<\/span>TotalIncludingTax *<\/span> 2<\/span>\r\nwhere SaleKey ><\/span> 42032102<\/span> AND SaleKey <<\/span> 45032102<\/span>;\r\n<\/pre>\n<\/div>\n
Result<\/h2>\n
After executing the update script 4 times, we end up with 6 files on each partition. The original file, the file created by the update of a single record and the 4 files created when updating multiple record.<\/p>\n
\"A<\/p>\n
These are the results of the test query execution:<\/p>\n\n\n\n\n\n\n\n\n
\n
After Updating 1 record<\/strong><\/p>\n<\/td>\n<\/tr>\n
\n
Initial<\/p>\n<\/td>\n
\n
11.894 seconds<\/p>\n<\/td>\n<\/tr>\n
\n
Minimum<\/p>\n<\/td>\n
\n
1.553 seconds<\/p>\n<\/td>\n<\/tr>\n
\n
Maximum<\/p>\n<\/td>\n
\n
2.286 seconds (ignoring initial)<\/p>\n<\/td>\n<\/tr>\n
\n
Average<\/p>\n<\/td>\n
\n
2.9394 seconds<\/p>\n<\/td>\n<\/tr>\n
\n
Average Ignoring Initial<\/p>\n<\/td>\n
\n
1.9494 seconds<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
The test seems to illustrate only the initial query is affected and affected a lot. After the data is in cache, the files in the delta table don\u2019t affect the query, or at least, it seems so.<\/p>\n
On one hand, this illustrates the problem. On the other hand, we are talking about a few seconds difference for a set of 50 million records.<\/p>\n
Cleaning the Table<\/h2>\n
The process of cleaning the table from unlinked parquet files is executed by the statement VACUUM<\/strong>. There are some important points to highlight about this process:<\/p>\n
    \n
  • If you decide to manage yourself the data history using data modelling, this needs to be a regular process on tables affected by updates and deletes.<\/li>\n
  • On the other hand, if you decide to use Time Travel to manage history, you can’t execute this process, otherwise you will lose the time travel capability.<\/li>\n<\/ul>\n
    This process needs to be executed very carefully. You can’t try to delete files while you have some process in execution over the data. You need to ensure this will only be executed while you don’t have anything running over the data.<\/p>\n
    The default method to ensure this is to only delete files older than one specific time. For example, if you want to delete unlinked files younger than 168 hours, you need to activate a special spark session configuration to ensure you are aware about what you are doing.<\/p>\n
    In this way, the example below, which activates this configuration and executes the VACUUM<\/strong> with 0 retention, is only for test purposes, not for production scenarios.<\/p>\n
    <\/p>\n
    \n
    spark.<\/span>conf.<\/span>set(\"spark.databricks.delta.retentionDurationCheck.enabled\"<\/span>, \"false\"<\/span>)\r\n<\/pre>\n<\/div>\n
     <\/p>\n
     <\/p>\n
    <\/p>\n
    \n
    %%<\/span>sql\r\nVACUUM 'Tables\/fact_sale'<\/span> RETAIN 0<\/span> HOURS\r\n<\/pre>\n<\/div>\n
    After executing this cleaning, the additional files will disappear and only the most updated will remain.<\/p>\n
    Onelake is for all<\/h2>\n
    This process affects not only the lakehouse<\/strong>, but the data warehouse as well. In the lakehouse, the SQL Endpoint<\/strong> is read-only, but the Data Warehouse<\/strong> is read-write with MERGE<\/strong> operations.<\/p>\n
    Conclusion<\/h2>\n
    Microsoft Fabric<\/strong> is a PaaS environment for the entire Enterprise Data Architecture and capable of enabling the most modern architectures, such as Data Mesh<\/strong>.<\/p>\n
    However, we should never lose track of the data concepts, such as the fact the data intelligence storage is intended to be read-only and for historical purposes. Our architectural decisions may have an impact on the result which may not be so obvious in a PaaS environment.<\/p>\n","protected":false},"excerpt":{"rendered":"
    Microsoft Fabric storage uses OneLake and Delta Tables, the core storage of all Fabric objects, as explained in my introduction to Microsoft Fabric. Either if you use lakehouses, data warehouses, or even datasets, all the data is stored in the OneLake. OneLake uses Delta Tables as storage. In this way, we need to be aware……<\/p>\n","protected":false},"author":50808,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[2,159164,159166],"tags":[123646,159034,158998,158997,101611],"coauthors":[6810],"class_list":["post-98289","post","type-post","status-publish","format-standard","hentry","category-blogs","category-microsoft-fabric","category-powerbi","tag-data-lake","tag-delta","tag-lakehouse","tag-microsoft-fabric","tag-power-bi"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/98289","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/50808"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=98289"}],"version-history":[{"count":11,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/98289\/revisions"}],"predecessor-version":[{"id":98393,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/98289\/revisions\/98393"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=98289"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=98289"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=98289"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=98289"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}