Many insurers and MGAs now run both Snowflake and Microsoft Fabric. Bordereaux processing and data engineering live in Snowflake, while Power BI, and increasingly the business-facing analytics for underwriters and DA oversight, lives in Fabric. The question is how to get the data across without building and running yet another set of pipelines.
Mirroring is Fabric's answer. You point a mirrored database at a Snowflake database, choose the tables, and Fabric keeps a near-real-time copy in OneLake as Delta tables. Today I bring Snowflake data into OneLake with Fabric pipelines and shortcuts. Mirroring is the lower-code option, and I've been exploring it with a sample delegated authority model. Here's how to set it up and what to watch for.
What mirroring does
- Takes an initial snapshot of each selected table, then replicates changes continuously.
- Writes the data into OneLake in Delta format, inside a Mirrored database item.
- Gives you a SQL analytics endpoint on the mirrored data and a default semantic model, so Power BI can use Direct Lake.
- The mirrored tables are read-only in Fabric. Snowflake stays the system of record.
The replication compute and the OneLake storage for mirrored data are largely covered by your Fabric capacity. There's a free mirroring storage allowance that scales with capacity size. But the Snowflake side still costs money, which is the part people forget (more on that below).
What to mirror
Mirror the gold star schema, not the bordereaux staging layers:
- Facts:
fact_risk_transaction,fact_premium_transaction,fact_claim_transaction - Dimensions:
dim_coverholder,dim_broker,dim_contract(binding authority),dim_contract_section,dim_class_of_business,dim_date
Step 1: Prepare Snowflake
Create a dedicated user and role for Fabric. Mirroring uses Snowflake table streams to pick up changes, so the role needs to be able to create streams and read the tables.
1: USE ROLE SECURITYADMIN; 2: 3: CREATE ROLE IF NOT EXISTS fabric_mirror_role; 4: CREATE USER IF NOT EXISTS fabric_mirror_user 5: DEFAULT_ROLE = fabric_mirror_role 6: DEFAULT_WAREHOUSE = mirror_wh 7: TYPE = SERVICE; -- authentication set up per your org's standards 8: 9: GRANT ROLE fabric_mirror_role TO USER fabric_mirror_user; 10: 11: USE ROLE SYSADMIN; 12: CREATE WAREHOUSE IF NOT EXISTS mirror_wh 13: WAREHOUSE_SIZE = XSMALL AUTO_SUSPEND = 60 AUTO_RESUME = TRUE; 14: 15: GRANT USAGE ON WAREHOUSE mirror_wh TO ROLE fabric_mirror_role; 16: GRANT USAGE ON DATABASE da_analytics TO ROLE fabric_mirror_role; 17: GRANT USAGE ON SCHEMA da_analytics.gold TO ROLE fabric_mirror_role; 18: GRANT SELECT ON ALL TABLES IN SCHEMA da_analytics.gold TO ROLE fabric_mirror_role; 19: GRANT CREATE STREAM ON SCHEMA da_analytics.gold TO ROLE fabric_mirror_role;
Check the current Microsoft documentation for the exact privilege list before you set this up. It's short, but it has changed as the feature has matured. Using a dedicated warehouse lets you see mirroring's Snowflake cost on its own line.
Step 2: Create the mirrored database in Fabric
- In your workspace: New item → Mirrored Snowflake.
- Create a connection: the Snowflake account server name (
<account>.snowflakecomputing.com), the warehouse, and the credentials forfabric_mirror_user. - Choose the database, then either mirror all data or select specific tables. Selecting tables explicitly is safer. "All data" also picks up new tables automatically, which you may not want.
- Click Mirror database. The status page shows each table move from Running through the initial snapshot to Replicating, with row counts.
Step 3: Use it
From the SQL analytics endpoint you can query the tables with T-SQL, create views, and, because everything is in OneLake, join mirrored Snowflake data with Lakehouse or Warehouse data in the same workspace. Here's a loss ratio view by coverholder and year of account, combining mirrored facts with a planning table that the underwriting team maintains in a Fabric Lakehouse:
1: WITH prem AS ( 2: SELECT contract_section_key, SUM(gross_premium_gbp) AS gwp_gbp 3: FROM da_mirror.gold.fact_premium_transaction 4: GROUP BY contract_section_key 5: ), 6: clm AS ( 7: SELECT contract_section_key, 8: SUM(paid_gbp + outstanding_gbp) AS incurred_gbp 9: FROM da_mirror.gold.fact_claim_transaction 10: WHERE is_latest_position = 1 11: GROUP BY contract_section_key 12: ) 13: SELECT ch.coverholder_name, 14: cs.year_of_account, 15: cs.class_of_business, 16: SUM(p.gwp_gbp) AS gwp_gbp, 17: SUM(COALESCE(c.incurred_gbp, 0)) AS incurred_gbp, 18: SUM(COALESCE(c.incurred_gbp, 0)) / NULLIF(SUM(p.gwp_gbp), 0) AS incurred_loss_ratio, 19: MAX(pl.planned_loss_ratio) AS planned_loss_ratio 20: FROM prem p 21: JOIN da_mirror.gold.dim_contract_section AS cs ON cs.contract_section_key = p.contract_section_key 22: JOIN da_mirror.gold.dim_coverholder AS ch ON ch.coverholder_key = cs.coverholder_key 23: LEFT JOIN clm c ON c.contract_section_key = p.contract_section_key 24: LEFT JOIN uw_lakehouse.dbo.business_plan AS pl 25: ON pl.class_of_business = cs.class_of_business 26: AND pl.year_of_account = cs.year_of_account 27: GROUP BY ch.coverholder_name, cs.year_of_account, cs.class_of_business;
For Power BI, build a semantic model on the mirrored database in Direct Lake mode. Reports now read Snowflake data with no import refresh and no DirectQuery round trips to Snowflake. If your Power BI reports currently use DirectQuery against Snowflake, this can remove a noticeable share of your Snowflake credit usage.
Things to watch for
- It's near-real-time, not real-time. Changes usually show up within a minute or two, sometimes longer after big month-end loads. That's more than enough for bordereaux, which arrive monthly anyway.
- Snowflake compute still runs. Picking up changes means queries against the streams on your warehouse. During month-end, when the facts change constantly,
mirror_whstays busy. WatchWAREHOUSE_METERING_HISTORYfor the first couple of month-ends. - Prefer mirroring curated tables. Mirror the gold layer, not bordereaux staging tables that get truncated and reloaded. A full reload in Snowflake means a full re-replication.
- Data type mapping. Most types map cleanly, but check
VARIANT/semi-structured columns and very high-precision numbers and timestamps, which may arrive as strings or with reduced precision. Keep premium and claim amounts as fixed-precisionNUMBERcolumns, and flatten any semi-structured bordereau fields into typed columns in Snowflake first. - There's a table limit per mirrored database (a few hundred). For big estates, split by subject area into several mirrored databases. That also keeps permissions tidier.
- Watch out for schema changes. Adding columns is generally handled, but some DDL changes on the source can require the table to be re-synced. Coordinate with whoever owns the Snowflake models.
- Security doesn't carry over. This is the big one in DA. Snowflake row access policies and masking policies don't come with the data. Mirroring reads with the mirror user's rights. If coverholders or class underwriters should only see their own business, or claimant details must be masked, re-apply that in Fabric (SQL endpoint permissions, RLS in the semantic model, or OneLake security), or only mirror data that's already safe for the audience.
Mirroring vs. other options
| Option | Good for | Watch out for |
|---|---|---|
| Mirroring | Low-maintenance, near-real-time replication of curated tables | Snowflake compute for change capture, security re-implementation |
| Pipeline / Copy job | Scheduled batch copies, transformations on the way | You own the pipeline, watermarks and failures |
| Iceberg + OneLake shortcut | One copy of the data, readable by both platforms | More setup, and you need to understand Iceberg catalogs |
| Power BI DirectQuery to Snowflake | Always-current data, no copy | Report speed and Snowflake cost at scale |
For a classic "Snowflake for bordereaux engineering, Power BI for underwriting and oversight" setup, mirroring looks like the simplest option. It needs less code than any other way of moving data between the two platforms.
