Introduction
Database mirroring in Microsoft Fabric allows replication of entire databases and tables, allowing us to bring data from various systems together into a single analytics platform. It is a low-cost and low-latency solution because it makes the deployment of any complex ETL process unnecessary.
Mirroring stores data in Delta-Parquet format in OneLake , ensuring it is always up-to-date. This allows you to leverage various Fabric services, such as running Spark codes, executing notebooks, preparing and visualizing reports with Power BI, and performing numerous other data engineering and analytics activities.
In this blog post, we’ll walk you through the steps to configure a Fabric mirrored database from Snowflake.
Getting ready
To get started with the hands-on, we need to have a database in Snowflake and an active Fabric capacity.
For this demo, we’ve already created a database in Snowflake named ‘Contoso’, which contains six tables under the ‘Sales’ schema.
For mirroring to work, the following user permissions are required for the Snowflake database.
- CREATE STREAM
- SELECT table
- SHOW tables
- DESCRIBE tables
Creating a Mirrored Database
For this demo, we’ve already set up a Fabric workspace named ‘Snowflake Mirroring’, which is backed by a Fabric capacity. Here’s how to create a new mirrored database:1. Select ‘Mirrored Snowflake’: From the ‘New Item’ hub, choose ‘Mirrored Snowflake’.
2. Choose a Database Connection: In the new window, select the ‘Snowflake’ option.
3. Provide Connection Details: Enter the required details and credentials to establish a connection with your Snowflake account.
4. Select the Database: Choose the database you want to mirror in Fabric.
5. Select Tables for Replication: Choose the tables you want to replicate. You can also enable the ‘Automatically mirror future tables’ option if you want future tables to be replicated automatically. This option can be adjusted later via the ‘Configure replication’ setting.
6. Name the Destination: Provide a name for the destination and click the ‘Create mirrored database’ button.
This will start the replication process for the database.
7. Monitor Replication: You can monitor the replication process by clicking the ‘Monitor replication’ button.
When the replication starts, a SQL analytics endpoint and a default semantic model are created for advanced analytics and reporting. All these objects will be available in the respective workspace.
You can also view the source in the lineage view.
Controlling and Monitoring Mirroring
Within the mirrored database, you’ll find several options in the upper right corner to control and monitor the mirroring process.
Now that the Snowflake data is available in Fabric, you can use various Fabric services for analytics. For example, you can run SQL queries (read-only) using the SQL analytics endpoint.
Note that the replicated tables will retain the same schema name in Fabric as they have in Snowflake.
Configuring Replication
You can adjust the replication behavior using the ‘Configure replication’ option
By default, all data is allowed to be replicated. However, you can choose to replicate only specific tables. Additionally, if you don’t want future tables to be replicated automatically, you can disable the ‘Automatically mirror future tables’ option.
Costs Associated with Mirrored Snowflake
There are no mirroring (or data incoming) charges on the Fabric side.
However, Costs will be incurred in the Snowflake side for-
- Virtual warehouse compute charges: Costs associated with any activities that wake up and use the virtual warehouse compute.
- Cloud services compute charges: Costs associated with running queries. Cloud services costs will depend on the Snowflake edition.
Limitations of Mirroring
Currently, there are some limitations of mirroring Snowflake databases into Fabric. These limitations may be addressed in future updates:
Database level limitations
- Replication is not supported for any table names and column names that have special characters ,;{}()\= and spaces.
- If there are no updates in the source, the replicator engine may slow down with an exponentially increasing duration.
- Only native tables are supported for replication.
- A maximum of 500 tables can be replicated.
Network and firewall
- Currently, mirroring is not supported for the Snowflake instances that are placed in a virtual or private network.
Security
- Authentication is currently only supported via Snowflake username/password and Entra (SSO).
- Sharing recipients are required to be added to the workspace.
Performance
- In the case of updating most of the data in a big table, stopping and restarting mirroring is more efficient.
- Some schema changes may take some time to get reflected.
Regions
- Not all the Fabric regions currently support mirroring.
Final Remarks
Mirroring databases in Microsoft Fabric is a straightforward process that offers significant benefits for organizations using Snowflake. It’s not only simple but also cost-effective. While there are some current limitations, many of these are expected to be addressed in the future. Overall, mirroring has the potential to be a game-changer in the field of data engineering and analytics.
