Configure Microsoft Fabric Database Mirroring for Snowflake

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


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.


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:
        AUTO_SUSPEND = 60
warehouse sizes in Snowflake
  • 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.
show tables in snowflake
  • DESCRIBE table – This statement returns metadata about the columns in a table.
describe table in snowflake

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:

create a new mirrored snowflake db

Give the new mirrored database a name:

name the mirrored database

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

create new conn or use existing

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.

connection info

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:

snowflake connection in azure data factory, with role option

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:

select database from dropdown

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

mirror all data

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.

table list of the database. Because of a bug, all tables are listed of the entire account

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

error because table doesn

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

table with preview

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..

monitor replication

This will show you the status of the different tables:

status of the different replicated 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:

sql analytics endpoint

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

cdc queries on snowflake side

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

Next Steps

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Koen Verbeeck 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 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

Article Last Updated: 2024-06-27

Comments For This Article

get free sql tips
agree to terms