Why Mirror BigQuery into Microsoft Fabric?
Organizations often store analytical data in BigQuery but want to use Microsoft Fabric’s capabilities such as:
- OneLake unified storage
- Lakehouse analytics
- Power BI semantic modeling
- Data engineering and pipeline orchestration
Mirroring enables near real-time replication of BigQuery tables into Microsoft Fabric without manual data movement, ensuring your data remains fresh and query-ready across environments.
Prerequisites
Before you begin, ensure the following:
- A valid Google Cloud account with access to BigQuery.
- Basic knowledge of Google BigQuery datasets and tables.
- A valid Microsoft Fabric account with workspace access.
Step-by-Step Implementation
Step1: Prepare Data in BigQuery
I created a google Big Query data warehouse, created a dataset called ms-fabric-mirroring_db and uploaded csv files and created that files as tables(1). In this datawarehouse we have two datasets and seven tables.
Step 2: Create a Workspace in Microsoft Fabric
Created a workspace called GCP_BQ_MR_WS. Click on New item(2) then in search bar type big(3) and click on Mirrored Google Big Query(preview) (4).
Step 3 : Now select Google BigQuery(5)
Step 4: Configure the Connection
Now fill all details such as, Connection name, Authentication kind, Service account email and Service Account JSON key file contents.(6) Note: Service account email and Service Account JSON key file contents you need get it from GCP cloud. Now click on Connect(7)
Step 5: Now it’s connecting to GCP Big Query data source(8)
Step 6: View Available Tables
Now we can able to see all seven tables which we saw earlier in step-1(9). Check the tick box ‘Automatically mirror future tablesAny new tables added to the source will be replicated to the destination.’ (10) and click on Connect (11)
Step 7: Now click on Create mirrored database(12)
Step 8: Now it’s creating mirrored database (13)
Step 9: Now Status is Running(14), No data has landed(15) if we want see the tables click on refresh(16)
Step 10: Verify the Mirrored Tables
Now here you’re seeing all the tables presented in my BigQuery(17) and at (18) we’re able see the status, Rows replicated and Last completed.
Step 11: Now we can add one more table to Big Query. Car-data-tb (19)
Step 12: Now navigate to Fabric and click on Refresh(20)
Step 13: Now it’s started loading the car-data-tb (21) ad click on refresh again
Step 14: Now if you see car-data-tb(22) has loaded all the rows and last completed time changed.
Step 15: Now from here you can see all details(23). If we can created semantic model(24) and we can do query on top of this tables (25)
Step 16: Let’s query and see the data. So, Now click on mirrored database dropdown(26) and selelct SQL analytics endpoint(27)
Step 17: Now I queried the bike-data-tb(28) and I'm able to see top 100 rows from that table(29)
Key Benefits of BigQuery Mirroring in Fabric
Mirroring leverages Google BigQuery Change Data Capture (CDC) to keep data synchronized with Microsoft Fabric’s OneLake, ensuring consistency and minimizing latency.
Conclusion
BigQuery Mirroring in Microsoft Fabric simplifies cross-cloud analytics by eliminating complex data pipelines. It provides a seamless and automated way to work with your GCP data directly inside Fabric for reporting, analysis, and machine learning.
Hope this walkthrough is helpful.
For a visual explanation, please refer to the detailed video : https://youtu.be/Pqllan2Rc4o?si=Nm_ll87IiLv0ro4r
— Inturi Suparna Babu
Super User
