Copilot in Fabric Data Warehouse: Magic in T-SQL

Microsoft Copilot is utilized in different technologies in Microsoft; in Microsoft Fabric, one of the places that you can use Copilot is in the Data Warehousing workload, which will guide you through your SQL commands and enhance your SQL coding experience. I’ll show you how the Copilot in the Data Warehouse works in this article and video.

Video

Prerequisite: License

Before we start, it is important to know that the Copilot in Fabric Data Warehouse is only available in capacities that are F64 or higher (the equivalent of P1 or higher in Power BI Premium). It is not available in PPU, and it is not available in the Fabric Trial license.

The tenant administrator in your organization should have enabled the option to use Copilot (this option is enabled by default unless the tenant admin disables it)

After setting up the prerequisite, you can use the Copilot in the Data Warehouse.

SQL Analytics Endpoint

To work with the Copilot in the Warehouse, you have to go to the SQL Analytics Endpoint, which you can do by just clicking on your Warehouse.

Once in the Warehouse, you will see the Copilot icon there (assuming you have the prerequisite met)

Copilot Experience in Warehouse

Once you select Copilot, you will see the Copilot window added on the right side,

As you see in the above screenshot, there are multiple ways that the Copilot can help here;

Create a Date table using Copilot

You can ask Copilot to create a table for you. For example, I asked the question below;

create a date table using t-sql that includes columns for year, month, week, weekday, weekend, half year, start of the year, start of the month for the period of the past 20 year

And here is the response I got:

The generated code is as follows;

-- Step-by-step plan:
-- 1. Use a loop to generate dates for the past 20 years.
-- 2. For each date, extract the year, month, week, weekday, weekend, half year, start of the year, and start of the month.
-- 3. Create the final date table with the desired columns and insert the generated date information into it.
-- 4. Optionally, add any additional constraints or indexes to the final date table.

-- Use a loop to generate dates for the past 20 years
DECLARE @StartDate DATE = DATEADD(YEAR, -20, GETDATE());
DECLARE @EndDate DATE = GETDATE();

-- Create the final date table with the desired columns
CREATE TABLE [dbo].[DateTable] (
    [Date] DATE,
    [Year] INT,
    [Month] INT,
    [Week] INT,
    [Weekday] INT,
    [Weekend] BIT,
    [HalfYear] INT,
    [StartOfYear] DATE,
    [StartOfMonth] DATE
);

WHILE @StartDate 



Not only did it generate the code, but it also added some commentary at the top of the code and inline explaining what is done. Then,



 when I run the code, I get the table generated in my Warehouse;











Copilot in SQL Editor



One of the most exciting uses of Copilot is inside the SQL query editor of the Warehouse. Here, you get the Copilot inline experience. It starts with helping you write your SQL code.







For example, if I start a query like the below;







As you see, I just mentioned the two tables I want to join, and it picked up the key columns as the joining criteria and suggested that I accept the suggestion or keep writing the code. It is an amazing way to speed up writing T-SQL code.







Copilot to Fix Errors in the Code



Copilot can also help in fixing the errors you have in your SQL scripts. For example, the script below generates error when I run it.







The code above has two errors: One in using the row_number function and another in the joining criteria syntax. 
I can use the Fix option of Copilot, which then fixes both errors for me!







The code above now runs perfectly fine;











Copilot to Explain and Document



Last but not least, you can use Copilot to explain an SQL script for you. This is especially helpful when you are given a long piece of SQL code that you don’t know what it is doing;







As you can see in the above script, not only did Copilot add the heading comments about what the script is doing in general, but it also added inline commentary for each step and formatted the code in an easier-to-read style.







Copilot to write the Query



Not only can Copilot help you when you write your query or fix the errors, but it can also help you write the entire query. Because Copilot has knowledge of your tables in the warehouse, it can generate a query if you ask a question about it. Below is my question, and then the Copilot suggested a query. 











Summary



Copilot in Microsoft Fabric is a big improvement for your development activities in the data analytics tasks. The Copilot usage in the Warehouse is helping you with functions such as creating tables, writing SQL scripts for you, auto-completing the SQL scripts for you, fixing the errors, and explaining the code and formatting it. The copilot usage in the Data Warehouse can be a big time saver for database development tasks. However, always remember that this is a copilot; you should still be the pilot and just use the copilot to help.



Here are links to study more about some of the concepts mentioned in this article: