Create a Microsoft Fabric Data Warehouse Database Project from a Lakehouse

Reading Time: 5 minutes

In this post I want to cover how you can create a Microsoft Fabric Data Warehouse Database Project from a Lakehouse using Azure Data Studio.

To clarify , when I refer to a Lakehouse in this post I mean a Microsoft Fabric Lakehouse.

You might want create a Microsoft Fabric Data Warehouse Database Project from a Lakehouse for a variety of reasons.

For instance, if you have imported a collection of files into a Lakehouse and created Lakehouse tables based on them. From there, you want to deploy the schema of the tables to one or more Microsoft Fabric Data Warehouses.

One way to do this is to export the schema into a Database Project. You can create a dacpac file based on the contents of that Database Project via various methods.

For instance, you can perform CI/CD for Microsoft Fabric Data Warehouses using Azure DevOps. Like in the below diagram.

By the end of this post, you will takeaway the below three points:

1. How to view external tables in a Lakehouse SQL endpoint.
2. That the Lakehouse SQL endpoint is read-only.
3. How to create a Microsoft Fabric Data Warehouse Database Project from a Lakehouse.

One key point to note is that Microsoft Fabric is now generally available. You can read more about this in detail in the official post by Ryan Majidimehr.

Microsoft Fabric Lakehouse

For this particular post I used the Lakehouse that you create in the exercise for the ‘Work with Delta Lake tables in Microsoft Fabric‘ Microsoft Learn module. Which is one of the modules included in the ‘Get Started with Microsoft Fabric‘ learning path.

After completing the exercise in the module, the below tables are created in the Lakehouse.

In addition, I created the below view within a notebook.

 %%sql

 CREATE VIEW vw_managed_products
 AS
 SELECT ProductID, ProductName FROM managed_products 

Once the view was created I went into the Microsoft Fabric workspace and selected the read-only SQL endpoint. You can do this various ways. Including directly in the Lakehouse explorer or from the Microsoft Fabric workspace as below.

Doing this basically opens a read-only version of the Data Warehouse editor. Which allows you to query the delta tables presented in the Lakehouse using T-SQL.

One key point to remember is that the SQL endpoint is currently read-only.

Before I go any further there are two interesting observations that I want to point out here.

• Any views you create in the Lakehouse do not appear as part of the read-only SQL endpoint.
• Only managed tables in the Lakehouse are shown in the read-only Lakehouse SQL Endpoint. As you can see below.

In other words, delta tables that are created in the files folder are not included by default. However, you can work around this by adding the location of the delta table as a shortcut as below.

Once done, the table is available in the Lakehouse SQL endpoint.

Create a Microsoft Fabric Data Warehouse Database Project

Within the Lakehouse SQL endpoint I went to the settings section to get the SQL connection string.

Once I copied the connection string, I went into Azure Data Studio and filled in the relevant connection details. Using the connection string as the server name and giving it a user-friendly name.

Once connected I could view the below tables in Azure Data Studio.

One key point to remember here is that these table are read-only due to the fact I am using the read-only SQL Endpoint.

From there I create a database project from it exactly the same way as I showed in a previous post. Where I covered how to share a Microsoft Fabric Data Warehouse Database Project with the new target platform.

Which is by right clicking the endpoint and selecting ‘Create Project From Database’. Once I filled out the Database Project details Azure Data Studio created the below database project for me.

Rather interestingly, the performance views were also created. Of course, if you were to deploy to a Data Warehouse you would probably want to remove them from the Database Project.

To verify that all was well, I selected to manage the project and saw that it is using the latest target platform.

In addition, I opened up the ‘shortcut_to_products.sql’ file within the Database Project and saw that it contained the below code:

CREATE TABLE [dbo].[shortcut_to_products] (
    [ProductID]   VARCHAR (8000) NULL,
    [ProductName] VARCHAR (8000) NULL,
    [Category]    VARCHAR (8000) NULL,
    [ListPrice]   VARCHAR (8000) NULL
);

GO

Final words

I hope this post about creating a Microsoft Fabric Data Warehouse Database Project from a Lakehouse has been an interesting read for some of you.

In reality, I hope you takeaway the three points I mentioned earlier in the post. Because they are all important things to know when working with Microsoft Fabric.

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