🐛 [fs-bq-schema-views] Excessive `first_value() over (partition by)` clauses causes BQ resource exhaustion

[jhanners]

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

Yes

[REQUIRED] Step 2: Describe your configuration

• Extension name: fs-bq-schema-views
• Extension version: 0.4.12
• Configuration values (redact info where appropriate):

npx @firebaseextensions/fs-bq-schema-views \
  --schema-files=${schemaFile} \
  --non-interactive \
  --project=${gcpEnv} \
  --big-query-project=${gcpEnv} \
  --dataset=firestore_export \
  --table-name-prefix=${table}

[REQUIRED] Step 3: Describe the problem

Steps to reproduce:

• Spin up a changelog table with 11M+ rows.
• Create a schema view with 200+ columns.
• Try to count the rows in the raw latest view:

select count(*) from `{model name}_raw_latest`

Expected result

We can count the rows in the view.

Actual result

BQ error:

Resources exceeded during query execution: The query could not be executed in the allotted memory. Peak usage: 123% of limit. Top memory consumer(s): sort operations used for analytic OVER() clauses: 99% other/unattributed: 1%

[CorieW]

Hi @jhanners. Thanks for creating this issue! We'll review it as soon as possible.

[cabljac]

this is likely us using OVER() clause in the view somewhere i think

[jhanners]

@cabljac Yes. Each of the 200+ columns in the schema view is computed via over clause.