Skip to content

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.

One use case to create a Microsoft Fabric Data Warehouse Database Project from a Lakehouse

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.

Tables in a Microsoft Fabric Lakehouse
Tables you can view in a Lakehouse

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


 CREATE VIEW vw_managed_products
 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.
Views that exist in a read-only lakehouse SQL endpoint
Tables you can view in Lakehouse SQL endpoint

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.

Shortcut based on delta table in Files folder

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

Lakehouse SQL endpoint with shortcut created

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.

Getting the SQL connection string to create a Microsoft Fabric Data Warehouse Database Project from a Lakehouse
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.

Connecting to the Microsoft Fabric Lakehouse using Azure Data Studio

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

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.

Database Project based on a Microsoft Fabric Lakehouse SQL endpoint
Database Project based on Lakehouse SQL endpoint

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


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.

Published inMicrosoft Fabric

Be First to Comment

Leave a Reply

Your email address will not be published. Required fields are marked *