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 <= @EndDate
BEGIN
    -- For each date, extract the year, month, week, weekday, weekend, half year, start of the year, and start of the month
    INSERT INTO [dbo].[DateTable] (
        [Date],
        [Year],
        [Month],
        [Week],
        [Weekday],
        [Weekend],
        [HalfYear],
        [StartOfYear],
        [StartOfMonth]
    )
    SELECT
        @StartDate,
        YEAR(@StartDate),
        MONTH(@StartDate),
        DATEPART(WEEK, @StartDate),
        DATEPART(WEEKDAY, @StartDate),
        CASE WHEN DATEPART(WEEKDAY, @StartDate) IN (1, 7) THEN 1 ELSE 0 END,
        CASE WHEN MONTH(@StartDate) <= 6 THEN 1 ELSE 2 END,
        DATEFROMPARTS(YEAR(@StartDate), 1, 1),
        DATEFROMPARTS(YEAR(@StartDate), MONTH(@StartDate), 1);

    SET @StartDate = DATEADD(DAY, 1, @StartDate);
END;

-- Optionally, add any additional constraints or indexes to the final date table

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:

Reza Rad on FacebookReza Rad on LinkedinReza Rad on TwitterReza Rad on Youtube
Reza Rad
Trainer, Consultant, Mentor
Reza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He has a BSc in Computer engineering; he has more than 20 years’ experience in data analysis, BI, databases, programming, and development mostly on Microsoft technologies. He is a Microsoft Data Platform MVP for 12 continuous years (from 2011 till now) for his dedication in Microsoft BI. Reza is an active blogger and co-founder of RADACAD. Reza is also co-founder and co-organizer of Difinity conference in New Zealand, Power BI Summit, and Data Insight Summit.
Reza is author of more than 14 books on Microsoft Business Intelligence, most of these books are published under Power BI category. Among these are books such as Power BI DAX Simplified, Pro Power BI Architecture, Power BI from Rookie to Rock Star, Power Query books series, Row-Level Security in Power BI and etc.
He is an International Speaker in Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday and SQL user groups. And He is a Microsoft Certified Trainer.
Reza’s passion is to help you find the best data solution, he is Data enthusiast.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.

Leave a Reply