Question 1

Why move data into SQL Server rather than reporting on BigQuery?

Accepted Answer

Usually because the consumers are already there. Established stored procedures, scheduled reports and a business intelligence tool pointed at SQL Server represent real investment, and moving every one of them is a bigger project than landing a modelled table where they already look. This way the modelling happens once in the data warehouse and the reporting layer stays put.

Question 2

How do I control the shape of the destination table?

Accepted Answer

Through the model rather than after the fact. A model source can be a table, a view, or custom BigQuery SQL, and the last of those is what most people use here, because a warehouse table rarely has the shape a reporting schema wants. Nested and repeated BigQuery values are worth flattening into scalar columns in that SQL, since a relational reporting layer is easier to work with when the columns are already flat.

Question 3

How is the data actually loaded?

Accepted Answer

Bulk table replication stages rows into a scratch table and then merges into the destination, which keeps the visible table consistent while a load runs. Model syncs write directly instead, batching inserts and issuing updates per row, with failed batches retried individually so errors are attributed to specific records. Schema is created for you, and where a type is not compatible a migration is planned rather than the wrong value being written.

Question 4

What does connecting BigQuery require?

Accepted Answer

A service account key, or workload identity federation if you would rather not hold a key. You can optionally scope the connection to a project and a location, which is worth doing when datasets live in more than one region. On the SQL Server side, an instance that is not publicly reachable can be connected through an SSH tunnel using a bastion host, which is common given how many SQL Server instances sit inside a private network.

Connect BigQuery with Microsoft SQL Server

Connect BigQuery and Microsoft SQL Server with Polytomic

Staged then merged

Reaches private instances

Custom SQL as the source

Trusted by businesses worldwide

Step-by-step integration guide

Connect BigQuery in Polytomic

Connect Microsoft SQL Server in Polytomic

Create a sync between BigQuery and Microsoft SQL Server

Map fields and enable the sync

Frequently asked questions

The sync and ETL platform that does it all

Powerful data sync

Advanced analytics

Enterprise security

Easy setup

Ready to connect BigQuery with Microsoft SQL Server?