1646362 - Express queries used in https://sql.telemetry.mozilla.org/dashboard/accessibility-inspector as an incremental query in bigquery-etl.

Status: RESOLVED FIXED

(Data Platform and Tools :: General, enhancement, P2)

Description

[Yura Zenevich [:yzen] (please use "needinfo")]

As per discussion with William:
This would be better expressed as an incremental query in bigquery-etl (it would be faster + cheaper to generate, and more comprehensive).

https://sql.telemetry.mozilla.org/dashboard/accessibility-inspector

Comment 1

[William Lachance (:wlach)]

I can take a look at this sometime in the next couple weeks, this is a good test case for bigquery-etl.

Comment 2

[William Lachance (:wlach)]

Taking this up now, sorry for delay.

Comment 3

[GitHub Bugzilla PR Linker]

Attachment: Link to GitHub pull-request: https://github.com/mozilla/bigquery-etl/pull/1171

Comment 4

[William Lachance (:wlach)]

Attachment: image.png

This landed, but it doesn't look it's actually working. The DAG failed to run over the weekend for unclear reasons. When I click on a failing task, Airflow says Task [bqetl_devtools.telemetry_derived__devtools_accessiblility_panel_usage__v1] doesn't seem to exist at the moment

Comment 5

[Jeff Klukas [:klukas] (UTC-4)]

I had to hit the "Refresh" button on the DAG page in order to have the tree show up with the new tasks. I initially thought that Airflow was failing to load them, but it was a matter of local cache.

I can now get to logs for the task and it shows "Partitioning specification must be provided in order to create partitioned table". My best guess is that we have not yet created the table, and the query will not work until we have the skeleton there.

Comment 6

[William Lachance (:wlach)]

(In reply to Jeff Klukas [:klukas] (UTC-4) from comment #5)

I can now get to logs for the task and it shows "Partitioning specification must be provided in order to create partitioned table". My best guess is that we have not yet created the table, and the query will not work until we have the skeleton there.

Yup, this was the issue. The table is created and the dag is now running fine: https://sql.telemetry.mozilla.org/queries/73804/source#184536

Will backfill soon, then I think we can close this.

Comment 7

[William Lachance (:wlach)]

Backfill complete: https://sql.telemetry.mozilla.org/queries/73804/source#184533

Looks like there's been a big spike in DAU since August! (verified this was real by re-running the original query with similar results)