Spreading your SQL Server wings with Microsoft Fabric Data Warehouses

Reading Time: 8 minutes

In this post I want to cover spreading your SQL Server wings with Microsoft Fabric Data Warehouses. As part of a series of posts about spreading your SQL Server wings with the Microsoft Intelligent Data Platform.

You can find out more about this series of posts in an introductory post I published about the series.

I thought it was fitting to do this post now that Microsoft Fabric has become Generally Available (GA). Since I published my post about spreading your SQL Server wings with Microsoft Fabric just after it was launched during Microsoft Build 2023.

Plus, it also seems fitting since the details about the new DP-600 exam for Microsoft Fabric are now available. Because this post contains items that are in the study guide.

By the end of this post, you will have a good overview of Microsoft Fabric Data Warehouses, including CI/CD options. In addition, where your SQL Server background can prove to be useful and where some Power BI knowledge can come in handy.

Plus, I share plenty of links in this post. For instance, there are a couple of useful resources to help you get started towards the bottom of this post.

Recap about Microsoft Fabric

Microsoft Fabric unifies the functionality you can find in various applications together within one Software as a Service solution to deliver a unified analytics platform.

So, all you have to do is go to one website and all the functionality that you see below will be at your fingertips. Including the Data Warehouse experience.

To help with some jargon here, you can find out more about what I mean when I refer to Software as a Service (SaaS) in an old blog post called ‘SaaS, PaaS, and IaaS.. Oh my!’.

In reality, even though Microsoft Fabric has numerous data experiences you should configure some other services as well to get the most out of it. Especially if you want to work with it at an enterprise level.

For example, Microsoft Purview for your compliance and governance needs. In addition, Azure DevOps to work with Microsoft Fabric Git integration. As highlighted below.

Types of Warehouses

To clarify, there are two types of Warehouses that you can work with in Microsoft Fabric.

First type is the read-only SQL analytics endpoint that currently comes with a Lakehouse and will appear in mirrored databases when they become available.

Second type is the full Synapse Data Warehouse (aka Warehouse).

In this post I focus on the latter. However, some things I mention also apply to the SQL analytics endpoint as well.

About Microsoft Fabric Data Warehouses

In a nutshell, a Microsoft Fabric Data Warehouse allows you to use T-SQL to work with data at scale.

Something that will be very familiar to those of you who have worked with other Microsoft Datawarehouse solutions in the past. Such as Azure Synapse Analytics SQL Pools or Parallel Data Warehouse (PDW).

In reality, it uses some concepts similar to other applications and services as well due to its architecture.

One key point to remember is that Microsoft Fabric Data Warehouses separate storage from compute. Similar to Azure Synapse Analytics serverless SQL Pools.

Due to the fact that within Microsoft Fabric a compute engine does all the processing for the Data Warehouse. Whereas all the data is stored in OneLake.

Working with a warehouse

You can work with Data Warehouse directly within Microsoft Fabric through the Data Warehouse editor. Which opens immediately when you select a Warehouse item in a Microsoft Fabric Workspace. As you can see below.

As you can see above you can navigate around easily in the editor. Doing various tasks such as working with queries easily. Plus setting a sensitivity label if required.

You can immediately preview the contents of a table in the Warehouse editor by selecting it, as below.

However, you can connect to a Warehouse using familiar applications as well. Such as SQL Server Management Studio and Azure Data Studio.

You can do this by clicking on the settings icon in the editor to get the SQL connection string and then enter it in your application of choice. For example, in Azure Data Studio you can enter the below to connect to a Warehouse.

T-SQL support in Microsoft Fabric Data Warehouses

As I mentioned in a previous post back in July, there is a limited T-SQL surface area in Microsoft Fabric. With a reasonable number of T-SQL limitations listed by Microsoft. Plus, there is a list of general product limitations for Data Warehousing as well.

In addition, Data Warehouses support less data types. Which you can read about in the guide about data types in Microsoft Fabric.

However, you can still do the main basics with tables, views, functions and procedures. Plus, a lot of other things which I cover in the next section. Which means that those with a SQL Server background can start working with it easily.

When you are working with tables in a Warehouse you can create, alter and drop tables. In addition, you can perform select, insert, update and delete statements inside them.

You do not need to work with indexes due the Warehouse architecture. However, you can still work with primary keys, foreign keys, and unique keys.

Plus, some interesting optimizations done behind the scenes. For example, the automatic compaction for Fabric Warehouse which was introduced earlier this month.

Additional T-SQL support in Microsoft Fabric Data Warehouses

In addition, more T-SQL support is being added over time as well. Below are some examples of recent additions:

• CSV parser version 2, for faster ingestion of data from CSV files using the popular COPY INTO syntax.
• Support for the sp_rename stored procedure. Which has been popular with SQL server professionals over the years.
• Support for the TRIM function, which is a variation of the popular LTRIM and RTIM functions in SQL Server.
• Dynamic data masking is now supported in Data Warehouses. With similar functions as SQL Server apart from datetime.
• You are now able to clone tables with time travel. Which is a concept that is probably familiar to those of you who have worked with other Data Engineering solutions.

Visual Query Editor

You can get a good appreciation for the basics in the ‘Get started with data warehouses in Microsoft Fabric‘ module in Microsoft Learn. Which will also cover how you can query using the Visual Query Editor.

Those with a SQL Server background might find this a refreshing way to work with queries visually within Microsoft Fabric. Especially now that you can save your visual query as a view.

Warehouse modelling in the Warehouse Editor

Now this is something that I personally think is a nice touch in the Warehouse Editor. That I think SQL Server professionals with a Power BI background will appreciate this as well.

Because you are able to perform Warehouse modelling directly in the Warehouse Editor. Which allows you to model the data in a Warehouse the same way that you can do in Power BI Desktop. As you can see below.

So, you can get your data ready for Power BI reports. Which you can create easily in the editor by clicking on ‘New report’.

Scratching below the surface of Microsoft Fabric Data Warehouses

Now the above is all well and good. But what else makes Warehouses in Microsoft Fabric so appealing.

Well for a start, Warehouses do not work in isolation in Microsoft Fabric. They heavily integrate with other items within Microsoft Fabric as well. Below are just some examples of how the Data Warehouse interacts some of the other experiences and with OneLake.

I mentioned earlier that automatic compaction for Fabric Warehouse has been introduced. However, more is being introduced to help with the performance and to keep data consistent.

For instance, the ability to work with Delta Lake logs. Which allows other engines that support Delta tables to be able to read the logs. Opening up some interesting possibilities. For example, allowing Data Scientists to query the tables in the Warehouse using notebooks.

Just like with SQL Server, you can work with a variety Dynamic Management Views within a Data Warehouse. Be aware that they are case sensitive if you are testing older SQL scripts. Just be aware that a lot of the ones you can see in the sys schema are not supported.

In addition, those who worked with performance tuning in SQL Server made be interested in the fact that you can work with statistics in Data Warehouses.

Plus, Query Insights has been added. Which allows you to analyze and tune query performance based on data saved over a period of time. It should be an easy to adopt for those who have worked with Query Store in SQL Server or monitored activity in SQL Server.

CI/CD for Microsoft Fabric Data Warehouses

In a previous post I covered Git integration and deployment pipeline updates on the Microsoft Fabric roadmap. In that post I mention the fact that support for Data warehouse in deployment pipelines is expected before the end of this quarter.

Once that is available you can look to deploy your Warehouse schemas to different workspaces with deployment pipelines.

For now, your best options to perform CI/CD for the schema of your Microsoft Fabric Data Warehouses are the same way as you do for SQL Server databases. Using either a state-based or a migration-based approach.

One key difference is that you must connect using the SQL connection string for your deployments. Instead of a server name.

To help with some jargon here, a state-based database deployment is where you deploy updates based on an entire state of something. Whereas a migration-based deployment is where you tend to apply incremental updates.

State-based deployments you can create a database project which essentially contains the schema of your database in code form. You then create a dacpac file based on the contents of the database project.

You can now deploy updates using this dacpac file gracefully with the new target platform thanks to a recent SqlPackage update.

I covered working with state-based deployments in the below posts:

• CI/CD for Microsoft Fabric Data Warehouses using Azure DevOps
• CI/CD for Microsoft Fabric Data Warehouses using YAML Pipelines

If you prefer a migration-based deployments, you can look to use the DBUp .NET Framework along with the dbops PowerShell module. In addition, it is worth noting that you can also use premium tools to do migration-based deployments. Just be aware that they come with a cost.

Microsoft Fabric Data Warehouse material to help spread your SQL Server wings

Microsoft has been working on Microsoft Fabric Data Warehouses for some time and it shows.

To align with the original announcement during Microsoft Build a Microsoft Fabric Launch Digital event took place at the same time. You can watch both the Day 1 video and the Day 2 video on YouTube now.

In addition there are some interesting videos from Microsoft Ignite sessions that you can watch.

For example, the ‘Unify your data across domains, clouds, and engines in OneLake‘ video shows a good mirrored databases demo.

In addition to the videos, there is other forms of online material available. For instance, there is a website that covers Microsoft Fabric Data Warehousing.

Plus, Microsoft provides some really good end-to-end tutorials in Microsoft Fabric to help you get up to speed with it. You can also find a learning path to help you get started with Microsoft Fabric in Microsoft Learn.

Furthermore, there is already an online Fabric community that you can join.

Final words about spreading your SQL Server wings with Microsoft Fabric Data Warehouses

I hope this post about spreading your SQL Server wings with Microsoft Fabric Data Warehouses has proved to be useful.

Because this post has been in the works for a while now and I thought it was appropriate to publish it after Microsoft Fabric became Generally Available.

Of course, if you have any comments or queries about this post feel free to reach out to me.