{"id":1617,"date":"2026-06-07T10:53:22","date_gmt":"2026-06-07T08:53:22","guid":{"rendered":"https:\/\/www.flip-design.de\/?p=1617"},"modified":"2026-06-07T10:53:22","modified_gmt":"2026-06-07T08:53:22","slug":"how-to-speed-up-table-inserts-in-translytical-dataflows","status":"publish","type":"post","link":"https:\/\/www.flip-design.de\/?p=1617","title":{"rendered":"How to Speed Up Table Inserts in Translytical Dataflows"},"content":{"rendered":"\n

The challenge I encountered when using this approach was that newly inserted data did not become visible in the table immediately. In most cases, it took one or two minutes before the records appeared in the table, and even longer before they were reflected in reports.<\/p>\n\n\n\n

Of course, this functionality is not designed for operational scenarios in the same way that applications such as Power Apps are. It is not intended to provide instant write-back capabilities for reporting solutions. Nevertheless, I spent quite some time trying to understand why this behavior occurs.<\/p>\n\n\n\n

The explanation is actually quite simple. Any data that is inserted, updated, or deleted must first be propagated to the underlying storage through the Delta mechanism. When using a SQL table within Microsoft Fabric, all changes are initially processed in OneLake. Since OneLake is optimized for analytical workloads rather than transactional workloads, data is not written immediately. Instead, changes are transferred once enough data has accumulated or after a certain amount of time has elapsed. This behavior is also related to the use of Parquet files as the underlying storage format.<\/p>\n\n\n\n

To work around this, I created an additional table containing more than 200 million rows. The table stores a numeric value as well as a comment field. The actual content written to this table is largely irrelevant. The purpose is simply to generate a large number of inserts or updates and thereby trigger processing more frequently.<\/p>\n\n\n\n

The business value that I actually want to store\u2014for example, a user comment\u2014is still written to the target table. The auxiliary table merely acts as a mechanism to encourage Fabric to process and publish changes more quickly.<\/p>\n\n\n\n

My Python function looks as follows (certainly not production-ready, but more than sufficient for testing purposes).<\/p>\n\n\n\n

import datetime\r\nfrom typing import Text\r\nimport fabric.functions as fn\r\nimport logging\r\n\r\nudf = fn.UserDataFunctions()\r\n\r\n\r\n# Select 'Manage connections' and add a connection to a Fabric SQL Database \r\n# Replace the alias \"<alias for sql database>\" with your connection alias.\r\n@udf.connection(argName=\"sqlDB\",alias=\"sql2\")\r\n@udf.function()\r\ndef write_one_to_sql_db(sqlDB: fn.FabricSqlConnection, CommentText: str) -> str:\r\n\r\n\r\n    # Replace with the data you want to insert\r\n    data = (CommentText)\r\n\r\n    # Establish a connection to the SQL database\r\n    connection = sqlDB.connect()\r\n    cursor = connection.cursor()\r\n \r\n    # Insert data into the table\r\n    insert_query = \"insert into dbo.Comments (CommentText) values (?);\"\r\n    cursor.execute(insert_query, data)\r\n\r\n    uq = \"update dbo.Zufallszahlen set zufallszahl = 6, CommentText = ?\"\r\n    cursor.execute(uq, data)\r\n\r\n    # Commit the transaction\r\n    connection.commit()\r\n\r\n    # Close the connection\r\n    cursor.close()\r\n    connection.close()    \r\n\r\n\r\n\r\n<\/code><\/pre>\n\n\n\n
\"\"<\/a><\/figure>\n\n\n\n
When the function is executed, it performs two actions. First, it updates all 200 million rows in the auxiliary table while also populating an additional column with the supplied comment. Second, it inserts the same comment into the target table.<\/p>\n\n\n\n
From a database administrator’s perspective, I would strongly advise against this approach. It is certainly not a performance-optimized solution, and I would not recommend it for production environments. However, based on my current experience, it is the only practical workaround I have found to accelerate the processing and visibility of changes in this specific scenario.<\/p>\n\n\n\n
While the method may seem excessive, it effectively triggers Fabric to process and publish changes more quickly, resulting in significantly shorter delays before the data becomes visible in both the table and the associated reports.<\/p>\n\n\n\n
\"\"<\/a><\/figure>\n","protected":false},"excerpt":{"rendered":"
The challenge I encountered when using this approach was that newly inserted data did not become visible in the table immediately. In most cases, it took one or two minutes before the records appeared in the table, and even longer … Continue reading →<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"","sticky":false,"template":"","format":"standard","meta":{"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0},"categories":[1],"tags":[],"_links":{"self":[{"href":"https:\/\/www.flip-design.de\/index.php?rest_route=\/wp\/v2\/posts\/1617"}],"collection":[{"href":"https:\/\/www.flip-design.de\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.flip-design.de\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.flip-design.de\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.flip-design.de\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1617"}],"version-history":[{"count":1,"href":"https:\/\/www.flip-design.de\/index.php?rest_route=\/wp\/v2\/posts\/1617\/revisions"}],"predecessor-version":[{"id":1620,"href":"https:\/\/www.flip-design.de\/index.php?rest_route=\/wp\/v2\/posts\/1617\/revisions\/1620"}],"wp:attachment":[{"href":"https:\/\/www.flip-design.de\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1617"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.flip-design.de\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1617"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.flip-design.de\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1617"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}