{"id":100341,"date":"2023-11-08T23:56:47","date_gmt":"2023-11-08T23:56:47","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=100341"},"modified":"2024-09-03T20:15:17","modified_gmt":"2024-09-03T20:15:17","slug":"fabric-data-warehouse-news-you-need-to-know","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/blogs\/fabric-data-warehouse-news-you-need-to-know\/","title":{"rendered":"Fabric Data Warehouse News you need to know"},"content":{"rendered":"<p>Microsoft Fabric, including the Data Warehouse, evolves faster than we can analyze and write about it. Here I\u2019m summarizing the most recent news I looked at.<\/p>\n<h2>Clone Table<\/h2>\n<p>We have a specific statement to clone a table. But what exactly does it mean?<\/p>\n<p>The Clone Table feature promises to create an image of the table on a specific point in time or with the current information. The documentation is not precise, because at some points it says it\u2019s only a clone of the structure, but we can see the data on the table.<\/p>\n<p><em>What\u2019s the advantage of this over a simple SELECT INTO statement? <\/em><\/p>\n<p>I tested both to discover. The images below illustrate how the clone table executes in only 3 seconds and the SELECT INTO takes 32 seconds for the same task.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"679\" height=\"288\" class=\"wp-image-100342\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2023\/11\/a-screenshot-of-a-computer-description-automatica-42.png\" alt=\"A screenshot of a computer\n\nDescription automatically generated\" \/><\/p>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"538\" height=\"268\" class=\"wp-image-100343\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2023\/11\/a-screenshot-of-a-computer-description-automatica-43.png\" alt=\"A screenshot of a computer\n\nDescription automatically generated\" \/><\/p>\n<p>However, this feature is in a very early stage. As a result, it shows some strange behaviours:<\/p>\n<ul>\n<li>The cloned table doesn\u2019t appear in the one lake explorer<\/li>\n<li>A UI feature is promised and documented but not available yet<\/li>\n<li>The cloned table doesn\u2019t appear in the list of tables. A refresh results in an error. We can only access it from a query.<\/li>\n<\/ul>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"1058\" height=\"266\" class=\"wp-image-100344\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2023\/11\/a-screenshot-of-a-computer-program-description-au-3.png\" alt=\"A screenshot of a computer program\n\nDescription automatically generated\" \/><\/p>\n<p><em>How does the clone happen in only 3 seconds?<\/em><\/p>\n<p>Without the access to the files in the onelake, it\u2019s difficult to say. My bet would be something related to the difference between copy files or copy records.<\/p>\n<p><strong>References:<\/strong><\/p>\n<p><a href=\"https:\/\/learn.microsoft.com\/en-us\/fabric\/data-warehouse\/tutorial-clone-table-portal\">https:\/\/learn.microsoft.com\/en-us\/fabric\/data-warehouse\/tutorial-clone-table-portal<\/a><\/p>\n<p><a href=\"https:\/\/learn.microsoft.com\/en-us\/sql\/t-sql\/statements\/create-table-as-clone-of-transact-sql?view=fabric&amp;preserve-view=true\">https:\/\/learn.microsoft.com\/en-us\/sql\/t-sql\/statements\/create-table-as-clone-of-transact-sql?view=fabric&amp;preserve-view=true<\/a><\/p>\n<h2>sp_rename for tables<\/h2>\n<p>This is an inheritance from Azure Synapse, which never supported sp_rename. Problem solved. A small step for the technology, a big step for the data engineer.<\/p>\n<p><strong>Reference:<\/strong><\/p>\n<p><a href=\"https:\/\/blog.fabric.microsoft.com\/en-GB\/blog\/announcing-fabric-warehouse-support-for-sp_rename\/\">https:\/\/blog.fabric.microsoft.com\/en-GB\/blog\/announcing-fabric-warehouse-support-for-sp_rename\/<\/a><\/p>\n<h2>Data Warehouse Dynamic Data Mask<\/h2>\n<p>Another important addition is starting to appear on Synapse Data Warehouse, but it\u2019s still missing some details.<\/p>\n<p>Dynamic Data Mask is a very interesting feature to help protect sensitive data. However, in Synapse there are still some features missing:<\/p>\n<p>Dynamic Data Mask with Alter Table is not working very well<\/p>\n<p>We need to provide the <a href=\"https:\/\/www.red-gate.com\/simple-talk\/blogs\/dynamic-data-mask-is-now-useful-and-no-one-noticed-it\/\">Unmask permission to users and roles. I wrote an article when this feature<\/a> was finally made available in SQL Server and Azure SQL. However, it\u2019s not available in Synapse yet.<\/p>\n<p><strong>Reference:<\/strong><\/p>\n<p><a href=\"https:\/\/learn.microsoft.com\/en-us\/fabric\/data-warehouse\/dynamic-data-masking\">https:\/\/learn.microsoft.com\/en-us\/fabric\/data-warehouse\/dynamic-data-masking<\/a><\/p>\n<h2>New CSV Parser for the Data Warehouse<\/h2>\n<p>I wrote before about the <a href=\"https:\/\/www.red-gate.com\/simple-talk\/cloud\/azure\/performance-of-querying-blob-storage-with-sql\/\">differences between the CSV Parser 1.0 and 2.0 in Synapse Analytics<\/a>.<\/p>\n<p>Checking the information from the old article, you may notice this is not a small advance, it\u2019s a big deal.<\/p>\n<p><strong>Reference:<\/strong><\/p>\n<p><a href=\"https:\/\/blog.fabric.microsoft.com\/en-GB\/blog\/announcing-improvements-to-csv-data-ingestion-in-fabric-warehouse\/\">https:\/\/blog.fabric.microsoft.com\/en-GB\/blog\/announcing-improvements-to-csv-data-ingestion-in-fabric-warehouse\/<\/a><\/p>\n<h2>Save As View\/Table available for Visual Queries<\/h2>\n<p>The queries feature, either SQL or Visual, are a great data exploration tool. It allows our users to explore the data before building any report, to discover exactly what\u2019s hidden in the data before building reports.<\/p>\n<p>The possibility to create tables and views also on the Visual Queries, as we already had on SQL ones, completes the data exploration scenario.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"1090\" height=\"303\" class=\"wp-image-100345\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2023\/11\/a-screenshot-of-a-computer-description-automatica-44.png\" alt=\"A screenshot of a computer\n\nDescription automatically generated\" \/><\/p>\n<p>The bad news:<\/p>\n<p>An old bug on <strong>Visual Query<\/strong> continues to be alive and kicking, making this feature not so useful. On the image above, there is a join between <em>fact_sale <\/em>table and <em>dimension_city <\/em>table. However, either the <strong>View SQL<\/strong> button or the <strong>Save as view<\/strong> button only show the SQL from <em>fact_table<\/em>, not the entire query with the join.<\/p>\n<p>The image below shows the <strong>Save as view<\/strong> option, unfortunately without the <strong>JOIN<\/strong> it should have.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"591\" height=\"674\" class=\"wp-image-100346\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2023\/11\/a-screenshot-of-a-computer-description-automatica-45.png\" alt=\"A screenshot of a computer\n\nDescription automatically generated\" \/><\/p>\n<p><strong>Reference:<\/strong><\/p>\n<p><a href=\"https:\/\/learn.microsoft.com\/en-us\/fabric\/data-warehouse\/visual-query-editor#save-as-view\">https:\/\/learn.microsoft.com\/en-us\/fabric\/data-warehouse\/visual-query-editor#save-as-view<\/a><\/p>\n<h2>Row Level Security<\/h2>\n<p>Row Level Security is one of the biggest differences between Data Warehouse and Lakehouse. Only a Data Warehouse has this feature, making the Data Warehouse a great candidate to be the gold layer in a medallion architecture, for example.<\/p>\n<p>This feature has the same working method as in SQL Server: We create a function to filter the security by user and create a security policy to link the function to a table.<\/p>\n<p>The example on the first reference below uses the <strong>USER_NAME() <\/strong>function. However, when an application connects to a database, we may be using an application identity, instead of a user identity. This could break the row level security.<\/p>\n<p>The application has the capability to set a session context value with the name of the real user accessing the data. The security function would check the session context and ensure the row level security.<\/p>\n<p>This architecture comes from SQL Server and it\u2019s completely available in Microsoft Fabric Data Warehouse. It\u2019s explained on the 2<sup>nd<\/sup> reference below.<\/p>\n<p><strong>Reference:<\/strong><\/p>\n<p><a href=\"https:\/\/learn.microsoft.com\/en-us\/fabric\/data-warehouse\/row-level-security\">https:\/\/learn.microsoft.com\/en-us\/fabric\/data-warehouse\/row-level-security<\/a><\/p>\n<p><a href=\"https:\/\/learn.microsoft.com\/en-us\/azure\/data-api-builder\/azure-sql-session-context-rls\">https:\/\/learn.microsoft.com\/en-us\/azure\/data-api-builder\/azure-sql-session-context-rls<\/a><\/p>\n<h2>Summary<\/h2>\n<p>Microsoft Fabric has not been released yet. Documentations and announcements appear before all the feature details are available. However, this information shows us the direction things are evolving and what we should be looking for.<\/p>\n<p>&nbsp;<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Microsoft Fabric, including the Data Warehouse, evolves faster than we can analyze and write about it. Here I\u2019m summarizing the most recent news I looked at. Clone Table We have a specific statement to clone a table. But what exactly does it mean? The Clone Table feature promises to create an image of the table&#8230;&hellip;<\/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":[123648,123645,158997,101611],"coauthors":[6810],"class_list":["post-100341","post","type-post","status-publish","format-standard","hentry","category-blogs","category-microsoft-fabric","category-powerbi","tag-data-platform","tag-data-warehouse","tag-microsoft-fabric","tag-power-bi"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/100341","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=100341"}],"version-history":[{"count":1,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/100341\/revisions"}],"predecessor-version":[{"id":100347,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/100341\/revisions\/100347"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=100341"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=100341"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=100341"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=100341"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}