BigQuery - load data with changing schema I have situation when an external system is providing data to BigQuery instance as JSON files.
They are rows, that have been updated in that external system, I need this data in BQ for later processing.
It is not a full snapshot of data, only changed rows.
I need to get this data from the external system, periodically join with data I already have in BQ and export for another system.
The problem occurs in a situation, when source external system changes they database schema - it's NoSQL database, they can add some columns. They can do it in every moment without notifying us about it. And then we are expected, to export data with new column already in export.
Any ideas on how to handle this situation? I was wondering about creating a new table with every load and then use a wildcard query, but it will not work when tables that are wildcarded have different columns structure.



EDIT to make question clearer.
I am loading JSON input files into BigQuery. There can be added new columns to the JSON structure at any time.
I need to load all incoming data and then have the possibility to query all of it as with the latest schema I received in the last JSON file. So like dynamically "add" missing columns to old data.
I'm reading your question as "how to detect schema changes in in-coming data and automatically change out-going data to match?"

Obviously, you're going to need to examine the meta-data of the in-coming records. Since its JSON this should be easily handled by whatever technology you use to accept these records. Then you'll need a local store holding the meta data your application is already aware of. As JSON is a tree structure your local meta data store will model a tree. As records with new schema are detected this local store can be updated.

You will need a second local store to hold the structure of your output document and a third to map the input fields to output fields.

The next three parts cannot be automated. If there are transformations from input to output a human will have to write them. Where in the output document structure the new values should appear will also have to be hand-written, as will the mapping from input to output.

My feeling is this will be horribly inefficient. It may be possible, depending on your technology, to use reflection to alter the run-time definition of your output class to accommodate new columns. Good luck.