1852336 - "Adding a new field to Clients Daily" cookbook does not update `clients_last_seen_joined`

Status: RESOLVED FIXED

(Data Platform and Tools :: General, defect)

Description

[Mike Williams [:mwilliams]]

+++ This bug was initially created as a clone of Bug #1852117 +++

Cookbook is here: https://mozilla.github.io/bigquery-etl/cookbooks/common_workflows/#example-add-a-new-field-to-clients_daily

The cookbook has been updated per #1852117, but changes were expected in clients_last_seen_joined and are still not appearing. We should confirm that changes to clients_daily are expected to update clients_last_seen_joined as a downstream dependency, and then (if so) investigate why the changes aren't propagating.

Comment 1

[Mike Williams [:mwilliams]]

I'm not sure when I'll be able to get back to this, so just wanted to provide a brief follow up.

It looks like clients_last_seen_joined should be modified as a downstream dependency (it is affected), and is part of the downstream dependency graph that is built, but then is not modified. This table is a dependency of a dependency, so perhaps that plays a part, but I haven't investigated further on that. The dependency graph is huge so obviously the vast majority is not modified (correctly), but I do not know why this table is not being detected properly as needing schema changes. I also am not sure if this indicates a larger issue and that more tables may be missing (although :frank mentioning this table specifically and not others makes me think that there were not other tables causing errors with missing schema changes, so maybe there is something specific to this table causing it to be skipped).

Comment 2

[Anna Scholtz [:ascholtz]]

The issue we have here is that we are using the dry running Cloud Function to get the table schemas. We only update downstream dependencies of those tables where the schema changed compared to the table schema that is deployed.
Dry running clients_last_seen using the dry run function keeps failing due to timeouts, likely because the query is too complex (this is also the reason we put it on the dry run skip list). The dry run failure causes an exception being raised which stops the execution here: https://github.com/mozilla/bigquery-etl/blob/97380baf4e9a63377b16df90a315f4ec51bed094/bigquery_etl/cli/query.py#L1581
This means all of clients_last_seen downstream dependencies are not getting updated.

A workaround here is to not use the cloud function. I'll update the docs for this. But this also means that data science won't be able to update these table schemas themselves since they don't have the right permissions.

Comment 3

[BMO Github Automation]

Attachment: [mozilla/bigquery-etl] [Bug 1852336] Add more docs for updating clients_daily (#4316)