🐛 [Stream Firestore to BigQuery] gen-schema-view script: create optimal *view_latest query

[blikblum]

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

Issues filed here should be about a feature request for a Stream Firestore to BigQuery. To file a feature request that affects multiple extensions or the Firebase Extensions platform, please reach out to
Firebase support directly.

[REQUIRED] Step 2: Extension name

This feature request is for extension: _ Stream Firestore to BigQuery

What feature would you like to see?

The script gen-schema-view creates *view_latest queries with redundant operations.

The generated *view_latest query uses *raw_latest query that already exclude 'DELETE' operations and outdated versions

So the generated *view_latest query can remove is_deleted field since is always false and remove schema field partitioning e.g:

for each schema field, instead of

      FIRST_VALUE(JSON_EXTRACT_SCALAR(data, '$.gender')) OVER(
        PARTITION BY document_name
        ORDER BY
          timestamp DESC
      ) AS gender,

generate

JSON_EXTRACT_SCALAR(data, '$.gender') AS gender,

BTW: the comment in the generated query ("-- Given a user-defined schema over a raw JSON changelog, returns the") suggest that is was using previously the *raw_changelog table that would make sense the field partitioning and is_deleted check. This not true anymore

How would you use it?

The query/view performance would be optimal and easier to understand

[Gustolandia]

Hi @blikblum.

Thank you for your feature request. We agree it would be better with your suggestion, but it would be a breaking change. So it will have to be addressed much later.

Thank you though.
Best Regards,
Gustavo