How to Build Warehouse DB Project Locally or within CI Pipeline Using MSSQL Database Projects

[evrise]

Hi,

I am currently working on a database project in Azure Data Studio using MSSQL Database Projects. My goal is to build the project locally. The project involves creating views that reference tables created using Spark and stored in a Microsoft Fabric Lakehouse.

Here is the specific challenge I am facing:

DB Project Build: How can I configure and build my database project, ensuring that it references the tables in the Fabric Lakehouse?

I would greatly appreciate detailed steps, configurations, and any relevant scripts or examples that could help in setting up both local builds and CI pipeline builds for this scenario.

Thank you for your assistance!

[AndyDDC · Accepted solution]

Hi @evrise in order to build your Warehouse project locally that has a view that references a Lakehouse table, you'll need to create another database project locally for the Lakehouse database and then create a Database Reference.

The issue at the moment is that the only database reference method that seems to work is dacpac, so once you have created the Lakehouse database project, you'll then need to successfully build it to generate a dacpac, then you can create a database reference in the Warehouse project that points to the lakehouse dacpac file.  

Please note you can't actually do anything with the Lakehouse dacpac file (like deploy to lakehouse as it's not supported) other than use it as a database reference.

I hope that helps.

-----------------------------------------------------------------------------

If my reply has been useful, please consider providing Kudos and

marking it as the solution.

-----------------------------------------------------------------------------

[evrise]

Thanks a lot, @AndyDDC. Excuse me for the additional question. How do you handle changes in the referenced Lakehouse DACPAC? As you mentioned, the only way to create tables is through Spark, not DB Project. So, when the Lakehouse is changed, do you always need to follow those steps and rebuild the DACPAC for the Lakehouse?

Do you have it set up in your CI pipeline so that a new DACPAC for the Lakehouse is always created there? Or do you have a method to build the DACPAC once and then reuse it?

Thanks

[AndyDDC]

In terms of keeping the Lakehouse dacpac updated, yes you'll need to do a diff in Azure Data Studio to get the latest changes and sync with the local db project, and build to generate the latest dacpac.

I haven't tried this myself, but I would imagine the CI process (eg in Azure DevOps) would simply build and output the dacpac for the latest version on the lakehouse db project.

[Shreya_Barhate]

Hey @AndyDDC and @evrise  ,

I’m trying to publish the database reference created using the DACPAC file of SQL DB in a data warehouse project in Azure Data Studio.

I’m facing an error:

Deploy dacpac: Verification of the deployment plan failed.
Warning SQL0: A project which specifies Azure Synapse Serverless SQL Pool as the target platform may experience compatibility issues with Synapse Data Warehouse in Microsoft Fabric.
Error SQL72030: An incompatible collation combination has been detected between the source and target. Deploying a case insensitive model to a case sensitive target can produce unintended changes and possible data loss. Consider using a case sensitive source model collation.

Is there any way to specify collation? Is this the correct way to sync the warehouse project? Can you please let me know?

[AndyDDC]

Are you trying to publish the lakehouse database? If so you won't be able to as the sql endpoint is read only. Only the Warehouse service can be deployed to

[Shreya_Barhate]

No not on lakehouse database
I'm trying to publish the DACPAC file in Synapse Data Warehouse in Microsoft Fabric

[Anonymous]

Hi @evrise ,

Your requirement is to understand how to connect Fabric warehouse from visual studio and save as a Database Project, right?

First, create a Warehouse and then create a view.

Click on the Settings icon in the warehouse.

Navigate to the About section and find the SQL connection string at the bottom of the page.

Open Azure Data Studio and create a new connection.

Select "Microsoft SQL Server" as the connection type.

Enter the connection string copied from Fabric Data Warehouse.

Select "Microsoft Entra ID" as the authentication type and complete the authentication.

Select your warehouse and view.

In Azure Data Studio, install the "SQL Database Projects" extension.

Create a new SQL database project.

Add the Fabric Data Warehouse you just imported and you're ready to go.

Finally, remember to save the project.

If you have any other questions please feel free to contact me.

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!