2013643 - add Lando analysis dataset in BigQuery for workflow analytics and dashboards

Status: RESOLVED FIXED

(Conduit :: Lando, task, P3)

Description

[Connor Sheehan [:sheehan]]

Summary

We should add an analysis dataset in BigQuery containing data from Lando. This would enable analyzing Lando-related workflow data without impacting the production transactional database.

Motivation

Currently, doing analysis on data in the Lando DB involves running commands manually in the shell against the production transactional DB. A BigQuery dataset would:

• Enable creating dashboards and running queries without impacting production
• Provide a user-friendly interface for data exploration
• Allow access for folks who don't have Lando admin shell access
• Enable cross-referencing Lando data with other datasets (Phab-ETL, Github-ETL, BMO-ETL, etc.)

Use Cases

• Analyze uplift workflow statistics and gain insight into further improvements
• Track landing job runtimes and queueing times
• Monitor failure rates
• When Treestatus port is completed: create dashboards showing time the tree has spent closed/opened and why

Additional Improvements

In support of this, we could start tracking metrics more aggressively in the database:

• Track the runtime of each attempt to run a job in the queue (instead of only tracking the latest runtime on the job)
• Explicitly store the amount of time each job spends in the queue before each individual run (instead of inferring total queue time from created_at and updated_at timestamps)

Implementation Options

1. Full DB import: Periodically import the full Lando DB into BigQuery. We would need to strip or exclude models which have sec-sensitive fields such as API keys if we went this route.
2. Management command export: Write a Django management command to export data from the transactional DB into BigQuery using the Django ORM with a specific set of queries. This would give us the flexibility to add new derived fields using Python and strip sec-sensitive data as required.

Comment 1

[BMO Github Automation]

Attachment: [mozilla-conduit/lando] lando-etl: add a management command to export data to BigQuery (Bug 2013643) (#967)

Comment 2

[Connor Sheehan [:sheehan]]

Attachment: GitHub Pull Request

Comment 3

[BMO Github Automation]

Authored by https://github.com/cgsheeh
https://github.com/mozilla-conduit/lando/commit/8322a921b7aa4edfa797d4d5f30e8155070e5e97
[main] lando-etl: add a management command to export data to BigQuery (Bug 2013643) (#967) r=shtrom,zeid

Comment 4

[Connor Sheehan [:sheehan]]

Attachment: GitHub Pull Request

Comment 5

[Connor Sheehan [:sheehan]]

Attachment: GitHub Pull Request

Comment 6

[Connor Sheehan [:sheehan]]

The initial uplift data set is created in BigQuery.

Still waiting on it to appear in STMO, after which we'll create queries and dashboards for the uplift project.

We'll track adding other models in separate bugs.

Comment 7

[BMO Github Automation]

Attachment: [mozilla/bigquery-etl] feat(lando): add lando metrics syndicate file (Bug 2013643) (#9024)

Comment 8

[Connor Sheehan [:sheehan]]

Attachment: GitHub Pull Request