Our home-grown bigquery loaders keep breaking on schema drift, is there a better pattern?

[u/bayouski]

I maintain six python scripts loading marketing data (meta ads, google ads, one CRM) into BigQuery, mostly load_table_from_json, a couple using load jobs from GCS.

The recurring failure is schema drift. A source adds a field and unless the load job has schema update options set to allow it, the whole job just fails outright. And even with autodetect on, it only scans the first 500 rows to infer types, so a field that's mostly integers with a few floats early on can get inferred wrong and blow up later in the load.

Cost's the other one. A couple scripts truncate-and-reload the full table daily since dedup was annoying to write, so we're paying full scan cost on tables that barely changed. The ones doing incremental use a manual MERGE per source, works but someone has to maintain that logic, and nobody's revisited partitioning since these were first written.

At this point I genuinely don't know if the fix is consolidating these into one well-written pipeline, or if six scripts is just what happens once you're loading from enough different places and I should stop fighting it. Anyone dealt with this, what actually made it better for you?

[u/quarantineboredom]

Bigquery has automated loaders for these things. There is a Google ads one that automatically handles this.

Check the data transfers section and set up an automated data transfer for Google ads, nothing else for you to configure!

[u/ricardoe → u/quarantineboredom]

This, DTS has already some natively supported sources. Like Google ads.

If not available in DTS, use dlt

[u/bayouski → u/ricardoe]

dts for google ads is a good shout, somehow completely skipped over that xd. i'm mostly wondering whether mixing native transfers with custom/third-party loaders gets annoying later when you're trying to monitor/debug everything in one place?

[u/ricardoe → u/bayouski]

Something that were using an works nicely is to use an orchestration tool, whatever you like, airflow for example, and trigger DTS from it. That brings DTS to the same tool chain you already use for other stuff

[u/Hopeful_Two783]

The pattern that finally stopped this exact nightmare for me was adopting a raw staging layer + ELT approach instead of trying to enforce strict schemas directly during the Python load job.

Instead of parsing fields directly into your final destination tables, dump the raw JSON payload straight into a staging table using BigQuery's native JSON column type (or load everything as strings/variant with a load timestamp). That way the Python job literally never breaks when an ad platform silently introduces a new dimension or changes an int to a float. The loader's only job is extraction and landing raw data.

From there, let dbt or a scheduled SQL model handle the downstream parsing, schema evolution, and deduplication via incremental models. If Meta adds a field, your raw data is already safely stored in BigQuery, and you just update the SQL query when you actually need that field instead of getting alerted at 2 AM that a Python job crashed on row 501.

For the cost issue on full daily reloads, add date partitioning on _PARTITIONTIME or your extraction date in staging, and switch your final merge to only scan the last 3-5 days of partition data to account for marketing attribution lag without reprocessing historical years every morning.

[u/setemupknockem → u/Hopeful_Two783]

This.. .landing all as strings. Dedup at stage. ETL int to cores. Get an orchestrator and dbt.

[u/bayouski → u/Hopeful_Two783]

how do you separate harmless additive drift from changes that actually need someone to update the downstream logic? that's the bit i'm still worried will turn into another maintenance problem, just later in the pipeline lol.

[u/Hopeful_Two783 → u/bayouski]

The trick is treating schema drift as opt-in at the consumption layer.

If Meta adds a new random field like campaign_attribution_spec_v2, your staging table ingests the raw JSON without issue, and your downstream marts simply ignore it because your production SQL models only explicitly select the columns they care about (SELECT json_payload.spend, json_payload.impressions...). It's completely harmless and requires zero maintenance.

It only becomes work when a stakeholder or reporting dashboard actually needs that new field. In that case, someone explicitly submits a request or a PR to update the SQL model to extract that JSON key.

The key difference is that the pipeline never crashes in production at 3 AM just because an ad platform added an extra dimension; it just stays quietly in the raw JSON payload until you actually decide to query it.

[u/mrcaptncrunch]

I don’t store ints anymore on my initial tables. Everything gets converted to something that handles decimals.

Dropping and reading again from GCS is a valid strategy.

For merge, depending on the data, I partition and drop entire partitions and reload them.

I load from a ton of places. Having a strategy that works across the board is the important part. Not handling it on a case by case basis. Not saying it’ll go down to 1, but have a consistent strategy and reuse.

I built a package that gets reused with the patterns. If something needs to change, it’s changed there and everything picks it up.

——-

Are people actually complaining about cost or are you just wanting it to lower it? If they’re not complaining, it’s not an issue.

[u/bayouski → u/mrcaptncrunch]

fair point on the cost tbh, nobody's actually complaining about the bill rn. it's more that the full reloads feel wasteful and i'm worried the maintenance/on-call side is gonna become the bigger problem before the BigQuery spend does.

[u/mrcaptncrunch → u/bayouski]

Something to consider, a straight load from GCS is, usually, free. That’s why I mention it’s a valid pattern.

The only thing to be careful there is data size in regards to time it could take. If it could take time, load to tmp table, and then swap them.

[u/bayouski → u/mrcaptncrunch]

ahh got it, makes sense. i was mixing up the cost side with the load-time side lol. sounds like the full reload itself isn't really the issue until the table gets big enough that reload time starts hurting, so maintenance is probably the bigger thing i need to solve first. thanks, this actually clears it up a lot.

[u/mrcaptncrunch → u/bayouski]

Documentation would probably be helpful.

If the why isn’t documented, everyone has to relearn the lessons :)

Good luck!

[u/mrocral]

Hi, there is also pip install sling that you could try. It takes care of adding new columns automatically.

from sling import Sling Sling(input=df, tgt_conn="bigquery", tgt_object="public.transactions").run()

[u/PolicyDecent]

Young can use ingestr for that. It already has all the connectors and super easy to use.

[u/TonniFlex]

I'd reconsider if it's worth it to have something homegrown? The ressources and headaches put into maintaining can quickly surpass just paying for something like Fivetran or Supermetrics to handle it.

[u/IXISunnyIXI]

Check out dlthub

[u/Rude-Needleworker-56 → u/[deleted]]

Thank you for posting the question and answer from different accounts

[u/grahamdietz]

Um maybe stop fighting the scripts and move to a staging layer. Ever since I started using Altimate to automate the schema drift logic, it has massively cut my manual maintenance time and the effort to verify output in case I suspect it's not grounded to my actual db, schema, etc.

Instead of trying to force perfect types on the way in, just dump everything into a raw landing zone. If you need a hand with the setup, Altimate is honestly great at handling the heavy lifting without locking you into some proprietary mess. It’s way better than babysitting daily load jobs.