Adswerve considered adding this feature to target-bigquery, but chose not to. Our rationale is in this thread in singer.io Slack workspace

@ruslan_bergenov - Do you know if there are significant performance hits to adding new columns in bigquery? or is more related to complexity? (Many databases can almost instantly make column additions but I don't know if the same applies to bigquery.)

@joe_bossalini, fingers crossed it works now. Yes, it should load the latest release now. You can also point to the latest explicitly: https://github.com/adswerve/target-bigquery/releases/tag/0.11.1

@joe_bossalini, when you run your sync, do you pass a tap-catalog file? Or do you do discovery and generate a new catalog file each time your run your sync?

@aaronsteers, @joe_bossalini We were previously reluctant about adding a new feature to Adswerve target-bigquery: being able to add a new column during the append operation. We discussed this feature again, and we're willing to meet you halfway through, because we understand that this feature is important for you. However, we don't recommend supporting the following operations: • Appending or deleting a new field which is nested (we do have concerns about performance) • Deleting any field/column, whether at the top or nested level (this might compromise the integrity of data) • Changing a data type However, we're willing to support adding a new column at the top level during the append operation. We can do it with a table patch method. The steps would be: 1. compare destination table schema and the new data schema 2. patch the destination table, add the new column at the top level only Question: If we were to support adding a new column at the top level, would that solve about 80% of your issues with taps changing their schema? Potential next step: We could build this feature ourselves, we did some preliminary POC development.

My coworker just pointed out that if wedo end up adding columns in target-bigquery, this might be an easier  than checking schemas and sending a patch request:

job_config.schema_update_options=[bigquery.SchemaUpdateOption.ALLOW_FIELD_ADDITION]

we were able to add a new column during the append operation in our POC tests, but only at the top level

@joe_bossalini, @aaronsteers, sorry for the delay, wanted to follow up on a target-bigquery feature that you requested: ability to add a column during the append operation. In the example below, we were able to add the

addresses

field, but we were not able to add something like

addresses.description

to the existing

addresses

field. Would it work for you if we supported adding a column at the top level (not the nested sub-fields)? Would this cover most of your use cases?

For example, here's the destination table schema before appending

Here's the destination table schema after appending