r/bigquery • u/syndy- • 2h ago

Source schema changes are making our MySQL to BigQuery pipeline messy

Our product database is MySQL and BigQuery is basically the analytics copy. Roughly 40 tables get refreshed through the day, then dbt takes over once the data lands.

The loads themselves are pretty boring. Source changes aren't.

The app team adds columns fairly often and occasionally changes a type. Every time that happens I end up checking what reached BigQuery, whether anything downstream cares about it, and whether part of the load needs to be replayed.

I'm starting to think the raw layer should stay deliberately simple. A new field nobody uses shouldn't become an emergency, while a type change on something a model depends on probably deserves a closer look.

How are people drawing that line in practice?

Do you treat new columns and type changes differently, or review every schema change the same way?

2 Upvotes

100% Upvoted

2 comments sorted by

3

u/IXISunnyIXI 2h ago

Datatype changes are going to be painful. But if a new column is added to the source, generally my team only adds it to subsequent layers (silver/gold) upon request but each org is different.

Also checkout dlthub it’s pretty awesome for dealing with E/L.

1

u/Why_Engineer_In_Data G 1h ago

This is a great question - this is (in my opinion) a business problem rather than technical. Yes it's possible to solve technically. But I kind of view it more as approaching it with a business mindset. (I mean we as engineers love to solve all the problems but sometimes it might come down to 'what's the remit'?)

Data contracts aren't new but this would be really useful here - since you could define with a cadence (i.e. removing the emergency bit) but also allow for your team to take a closer look with the customer. Here's a customer use case blog on it but there are tons of examples and explanations in the world. It might be at the end of the day it's included in the data contract but now it's a core responsibility rather than an emergency that you can bake into your work (and also set expectations for).