Scaling issue with backfilling data

[ohnorobo]

We're currently having an issue where attempting to run a full backfill (over all data from 2018-2023) runs a job that succeeds, but which ends up having dropped 2/3 of the expected rows. The dropping is not spread out evenly, but is caused by 2/3 of server ips to be dropped entirely.

Here's an example of that the missing data look like:

Running over only smaller amounts of data causes the jobs to correctly write all the data. In particular writing the data out one year per job causes it to work correctly.

Example that succeeded:

• 2022 backfill - python3.9 -m pipeline.run_beam_tables --env=prod --user_dataset=agix.http_scan_full_2022 --start_date="2022-01-01" --end_date="2022-12-31" --scan_type=http --full
• 2021 backfill - python3.9 -m pipeline.run_beam_tables --env=prod --user_dataset=agix.http_scan_full_2022 --start_date="2021-01-01" --end_date="2021-12-31" --scan_type=http
• 2020 backfill - python3.9 -m pipeline.run_beam_tables --env=prod --user_dataset=agix.http_scan_full_2022 --start_date="2020-01-01" --end_date="2020-12-31" --scan_type=http
• 2019 backfill&authuser=1&supportedpurview=project) - python3.9 -m pipeline.run_beam_tables --env=prod --user_dataset=agix.http_scan_full_2022 --start_date="2019-01-01" --end_date="2019-12-31" --scan_type=http
• 2018 backfill - python3.9 -m pipeline.run_beam_tables --env=prod --user_dataset=agix.http_scan_full_2022 --start_date="2018-07-27" --end_date="2018-12-31" --scan_type=http
• 2023 backfill - python3.9 -m pipeline.run_beam_tables --env=prod --user_dataset=agix.http_scan_full_2022 --start_date="2023-01-01" --end_date="2023-03-14" --scan_type=http

Example that dropped data:

• Full https backfill - python3.9 -m pipeline.run_beam_tables --env=prod --user_dataset=agix.https_scan_full --scan_type=https --full

One thing we're seeing in the jobs with issues is the scaling message Autoscaling: Unable to reach resize target in zone us-east1-c. QUOTA_EXCEEDED: Instance 'abc' creation failed: Quota 'IN_USE_ADDRESSES' exceeded. Limit: 575.0 in region us-east1. we're also seeing the error Autoscaling: Unable to reach resize target in zone us-east1-c. ZONE_RESOURCE_POOL_EXHAUSTED_WITH_DETAILS: Instance 'abc' creation failed: The zone 'projects/censoredplanet-analysisv1/zones/us-east1-c' does not have enough resources available to fulfill the request. '(resource type:compute)'.

The job is also not scaling to as many workers at it wants

[ohnorobo]

Here's the current shape of the production data

[ohnorobo]

I've gone through and backfilled the dev data, here's the current state:

[ohnorobo]

@agiix also updated prod to the same data as dev