Schema updates for tables with not associated query.sql

[quiiver]

We have a table who's definition is managed by bqetl but does not have a corresponding query SQL file.

https://github.com/mozilla/bigquery-etl/blob/main/sql/moz-fx-data-shared-prod/search_terms_derived/sanitization_job_metadata_v2/schema.yaml

When the schema is updated in the schema.yaml file the artifact deploy does not update the table schema (it does update the table metadata). Additionally using the bqetl command bqetl query schema update <table_ref> appears to complete but does not update the table schema.

┆Issue is synchronized with this Jira Task

[whd]

For that table specifically I'd expect bqetl query schema update to only be runnable by DSRE/Airflow or possibly workgroup:search-terms/sanitized-writer since access to that dataset is heavily restricted.

I would expect (or hope) that artifact deployment via Airflow would automatically update this schema.

[quiiver]

Yeah, i would expect the CLI command to fail when it either doesn't have a schema to update or the executor does not have the correct permissions.

[BenWu]

bqetl query schema update looks for a corresponding query.sql and doesn't do anything if it doesn't exist (code). I agree that it should fail in that case.

bqetl query schema deploy looks for query.* or script.sql and runs the sql generators if none are found. This will fail if there are still no queries to deploy.

Kind of hacky, but as a workaround, you can add a query.py stub to allow the schema to be picked up by the schema deploy. I did that here https://github.com/mozilla/bigquery-etl/blob/main/sql/moz-fx-data-shared-prod/telemetry_derived/public_data_report_hardware_v1/query.py
I agree that we should consider making this unnecessary though