BigQuery trigger change mask

[falmanna]

[READ] Step 1: Are you in the right place?

YES

[REQUIRED] Step 2: Extension name

firestore-bigquery-export

What feature would you like to see?

I would love to get access to the update mask of the trigger, or at least the old data. If not storing it in bigquery, make it possible to access this data in the transformer function.

How would you use it?

I am using bigquery extension to do some KPI analysis, and some of my KPIs require knowing when a field did change. Currently, it is not trivial to tell due to not having the updateMask or the oldData in the stored event in Bigquery.

If there is a workaround please do let me know.

[yamankatby]

Hi @falmanna, Thank you for filing this issue.

I wonder if the changelog table serves your need? The changelog table is a standard SQL table that contains all of the updates that have occurred when syncing from Firestore.

You can find it in the Bigquery Explorer next to the set where the extension syncs the Firestore collection.

[falmanna]

Hey @yamankatby Sure thing, but I am talking about having the document change mask, or the old document as part of the table as well, or at least make them available to the transform function.

Now we only have the full data AFTER the change, but it is not trivial to know which fields got updated. I've done some hacky SQL queries to get this data for some fields, but it is not really convenient.

This data is already available as part of firestore trigger, so I think it is also helpful to expose it in the extension.

[yamankatby]

Heya @falmanna Thank you for your response 🙌, however, I'm not sure if I understood your problem correctly, can you please update the issue to provide more information about the context, problem, and the solution you are expecting.

Also, I'm not quite sure what you mean by "change mask"

[falmanna]

Hi @yamankatby Thanks for following up. I think I did explain the issue clearly in the context of firestore triggers.

So to simplify this, basically, I need the extension to provide either the document oldValue or the updateMask. If not add it to bigquery directly, maybe at least enable access to these fields in the optional transform function.

Why do I think they are necessary? Well, this is a data changelog, and currently, it is hard to find out which fields got updated (which is very useful for some data analysis queries) without doing a lot of data transformation.

I hope that this made it clearer :)

[dackers86]

Thanks @falmanna.

That description has provided everything that we need!

[falmanna]

This appears to be added in v0.1.28?

EDIT: is it possible to reference fields from old_data in the schema builder?

[dackers86]

Hi @falmanna.

This may possible, and perhaps suited as a separate issue.

Related issue added: https://github.com/firebase/extensions/issues/1372