Configure Microsoft Fabric Database Mirroring for Snowflake

By: Koen Verbeeck   |   Updated: 2024-06-27   |   Comments   |   Related: > Microsoft Fabric

Problem

We have a couple of Snowflake databases and would like to have that data available in Microsoft Fabric as well. Is there an easy solution to get the data quickly in Fabric? We don't have many technical people on staff, so writing complex ETL is not an option.

Solution

Database mirroring is a new feature in Microsoft Fabric that allows you to easily replicate a source database to your Fabric workspace. The concept of mirroring in Fabric is explained in the tip, What is Mirroring in Microsoft Fabric?, where a replication is set up between an Azure SQL Database and a Fabric workspace. But other possible sources exist, such as Snowflake and Azure Cosmos DB. In this tip, we'll explore how you can configure mirroring for Snowflake.

At the time of writing, the database mirroring feature is in public preview. This means that some functionality might be missing or there might be some bugs. It's also possible that the layout may change, and your experience may differ from the screenshots seen in this tip. Additional database vendors will most likely be included as alternative sources in the future.

Configure Mirroring for Snowflake

Snowflake is a truly cloud-native data warehouse. It was one of the first to separate storage from compute and have dynamic scaling options combined with auto-pause and auto-resume. If you want to learn more about the basic functionalities of Snowflake, check out the Snowflake tutorial (the screenshots are a bit out of date as there have been some big UI changes, but the core functionality is still the same). If you want to follow along with this tip, you can create a free 30-day trial. Any database in Snowflake can be mirrored (also cloned databases), and there's a sample database in Snowflake to get you started.

The following are prerequisites to begin with Snowflake database mirroring in Fabric:

• A warehouse in Snowflake. This is the compute, and if you want to save money, you can get started with the smallest option, the XS size. You can create a warehouse in Snowflake with the following SQL script:

CREATE OR REPLACE WAREHOUSE "MY_WAREHOUSE"
WITH    WAREHOUSE_SIZE = 'XSMALL'
        AUTO_SUSPEND = 60
        AUTO_RESUME = TRUE
        INITIALLY_SUSPENDED = TRUE;

• A workspace in Microsoft Fabric that is associated with a Fabric capacity. You can use the Fabric trial if needed (and if still applicable).
• As explained in the tip, What is Mirroring in Microsoft Fabric?, you also need to enable database mirroring in your tenant.
• In Snowflake, the user that connects to the source database needs the following permissions:
  • CREATE STREAM - These are a CDC-type feature in Snowflake. You can learn more about them in the tip Exploring Streams in Snowflake for Change Data Capture.
  • SELECT on the table - To retrieve the data itself.
  • SHOW tables – This statement returns metadata about all the tables in the database.

• DESCRIBE table – This statement returns metadata about the columns in a table.

Once the prerequisites are satisfied, we can start with the configuration of the replication. In the Fabric workspace, select the data warehouse persona (bottom left corner) and select the Mirrored Snowflake Database from the list:

Give the new mirrored database a name:

In the next screen, create a new connection or select an existing one.

When creating a new connection, specify the server name and the warehouse. You can give the connection itself a friendlier name (recommended) and specify the authentication type.

To authenticate, you can use either Snowflake (similar to SQL authentication in SQL Server) or a Microsoft account if you have configured Snowflake to use Azure Entra ID (formerly known as Azure Active Directory).

At the time of writing, there's no option to specify a role. In Azure Data Factory, this option does exist:

This is useful when the account you're using to connect is a member of different roles. Since you cannot specify a role for this connection in Fabric, it's very important that the default role of the user has all the required permissions to set up mirroring; otherwise, it will fail.

Once you have configured the connection, proceed to the next screen to select the database:

Initially, mirroring will be configured to replicate all tables of the source database, and you will be presented with an almost empty screen:

If you want to replicate only a subset of tables, you can deactivate the "Mirror all data" option. This will also allow you to view the individual tables and preview the data. At the time of writing, a bug causes the screen to show all tables from all databases. Since there are cloned databases, you see some tables appear twice.

If you click on a table that is not part of the database you selected in the previous screen, you'll receive an error:

Microsoft is aware of this bug; hopefully, it will be fixed soon. Selecting a correct table will give you a preview of the data:

Once you've selected the desired tables from the list, you can finish the configuration of database mirroring. It will take some time to initialize the first snapshot of the tables. When the replication is running, you can click on Monitor replication..

This will show you the status of the different tables:

If you want to add new tables to the replication, click Configure replication,, which will take you to the same setup screen as during the initial configuration. Just as with Azure SQL DB as a source, data will be copied to OneLake and transformed into delta tables. You can query these tables using a SQL Analytics Endpoint:

On the Snowflake side, you'll will continuously see queries that check for any changes (both data and metadata):

Keep in mind that setting up database mirroring in Fabric for your Snowflake database will incur costs on the Snowflake side.

Next Steps

• If you haven't already, check out the tip What is Mirroring in Microsoft Fabric?.
• You can find more Microsoft Fabric tips in this overview.
• If you're interested in learning more about Snowflake, you can use this collection of tips, or check out the tutorial.

About the author

Koen Verbeeck is a seasoned business intelligence consultant at AE. He has over a decade of experience with the Microsoft Data Platform in numerous industries. He holds several certifications and is a prolific writer contributing content about SSIS, ADF, SSAS, SSRS, MDS, Power BI, Snowflake and Azure services. He has spoken at PASS, SQLBits, dataMinds Connect and delivers webinars on MSSQLTips.com. Koen has been awarded the Microsoft MVP data platform award for many years.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips