{"id":101726,"date":"2024-04-03T17:00:04","date_gmt":"2024-04-03T17:00:04","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=101726"},"modified":"2024-09-03T20:04:46","modified_gmt":"2024-09-03T20:04:46","slug":"dynamic-partitioning-and-a-simple-incremental-load","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/blogs\/dynamic-partitioning-and-a-simple-incremental-load\/","title":{"rendered":"Dynamic Partitioning and a Simple Incremental Load"},"content":{"rendered":"

Let’s consider a simple statement for partitioning and save a table in a lakehouse:<\/p>\n

<\/p>\n

\n
df.<\/span>write.<\/span>mode(\"overwrite\"<\/span>).<\/span>format(\"delta\"<\/span>).<\/span>partitionBy(\"Year\"<\/span>,\"Month\"<\/span>,\"Day\"<\/span>).<\/span>save(\"Tables\/\"<\/span> +<\/span> table_name)\r\n<\/pre>\n<\/div>\n
Let’s consider we load the data daily, with all the transactions from the day. The table will save the transactions for each day in different partitions. We can expect the table to keep the partitions from previous day, months and years to be kept, achieving a kind of incremental load, right?<\/p>\n
Wrong.<\/p>\n
The files are neither, overwritten or deleted, but they are removed from the delta log. The records are not directly acessible in the delta table any more.<\/p>\n
\"A<\/p>\n
The table removes the previous partitions from the Delta Log<\/p>\n
There are two possible solutions: If you load the data once a day,\u00a0 you can replace the “overwrite”<\/em> by “append”<\/em>. This solution only works if each ingestion never brings a record already loaded before.<\/p>\n
<\/p>\n
\n
df.<\/span>write.<\/span>mode(\"append\"<\/span>).<\/span>format(\"delta\"<\/span>).<\/span>partitionBy(\"Year\"<\/span>,\"Month\"<\/span>,\"Day\"<\/span>).<\/span>save(\"Tables\/\"<\/span> +<\/span> table_name)\r\n<\/pre>\n<\/div>\n
However, if we load the data multiple times a day, bringing all the transactions from the day and containing records which are possibly already loaded in the warehouse, the “append”<\/em> mode doesn’t work, because it will create duplicated records.<\/p>\n
One of the solutions is to overwrite specific partitions instead of all of them. In this case, overwrite the “Day” partition.<\/p>\n
The Spark configuration called “partitionOverwriteMode”<\/em> can have the values static<\/em> or dynamic<\/em>. The default value is static<\/em>. The table removes all old partitions from the delta log by default. When we set this configuration to dynamic<\/em>,\u00a0 the table keeps the old partitions. It overwrites the ones coming in the dataframe.<\/p>\n
After we set this configuration to “dynamic”, <\/em>we can execute the same original statement to ensure the table will keep the old partitions:<\/p>\n
<\/p>\n
\n
spark.<\/span>conf.<\/span>set(\"spark.sql.sources.partitionOverwriteMode\"<\/span>, \"dynamic\"<\/span>)\r\ndf.<\/span>write.<\/span>mode(\"overwrite\"<\/span>).<\/span>format(\"delta\"<\/span>).<\/span>partitionBy(\"Year\"<\/span>,\"Month\"<\/span>,\"Day\"<\/span>).<\/span>save(\"Tables\/\"<\/span> +<\/span> table_name)\r\n<\/pre>\n<\/div>\n
\"A<\/p>\n
The table keeps the existing partitions, instead of overwritting them.<\/p>\n
Summary<\/h2>\n
Using dynamic partitioning and defining the partition key correctly is one simple and fast way to achieve an incremental load. Of course, this is only one among other options we will talk about later.<\/p>\n
You may also would like to take a look on how to set the configuration as default using environments<\/a><\/p>\n
 <\/p>\n","protected":false},"excerpt":{"rendered":"
Let’s consider a simple statement for partitioning and save a table in a lakehouse: df.write.mode(“overwrite”).format(“delta”).partitionBy(“Year”,”Month”,”Day”).save(“Tables\/” + table_name) Let’s consider we load the data daily, with all the transactions from the day. The table will save the transactions for each day in different partitions. We can expect the table to keep the partitions from previous day,……<\/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],"tags":[158998,158997],"coauthors":[6810],"class_list":["post-101726","post","type-post","status-publish","format-standard","hentry","category-blogs","category-microsoft-fabric","tag-lakehouse","tag-microsoft-fabric"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/101726","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=101726"}],"version-history":[{"count":7,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/101726\/revisions"}],"predecessor-version":[{"id":102026,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/101726\/revisions\/102026"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=101726"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=101726"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=101726"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=101726"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}