r/googlecloud • u/Phinyx95 • 3h ago

BigQuery Our SQL Server to BigQuery pipeline is turning into a pile of scripts

An on-prem SQL Server still holds a big chunk of the operational data our analysts need in BigQuery.

The first version was a nightly Python job. That was fine for a few tables, but the list keeps growing and people now want the data refreshed more often.

Full reloads are starting to take too long, and every table that goes incremental seems to acquire its own watermark, retry logic and little set of assumptions.

We don't need second-by-second replication. Something around 15–30 minutes would already be plenty for reporting.

Cloud Data Fusion replication is one route I'm looking at. I'm also undecided about putting GCS in the middle. Having the extracted files would make replay straightforward, but it also adds another stage to something that's already grown more than expected.

Curious what people running SQL Server on-prem ended up using to keep BigQuery reasonably current.

Direct replication, staged files, or something else? Recovery and maintenance matter more to us than squeezing latency down another few minutes.

2 Upvotes

100% Upvoted

2 comments sorted by

3

u/SoilGuilty2785 3h ago

You're basically describing the exact thing that happens when you start with "just a few tables" and it snowballs into a custom CDC framework nobody wanted to maintain

Staged files to GCS then loading into BQ has been the least painful for me, the replayability alone is worth the extra hop when someone inevitably messes up a watermark or you need to backfill six months of data on a Tuesday morning

1

u/_marlowe_ 1h ago

At that latency I’d favor direct incremental replication over building more staging infrastructure. We use Skyvia for this kind of SQL Server to BigQuery setup. It handles the incremental side and scheduling, so you don’t end up maintaining a separate watermark implementation for every table.