Update latest period and quarter awards in ES - run on 1/28/2021



Touch affected awards updated date in order to let the nightly pipeline freshly calculate the total covid obligation and outlay amounts on all awards in Elasticsearch that were linked to FY2021 P03 monthly DABS submissions (there is no quarterly deadline for this timeframe, Q1 deadline is Feb 15, 2021).


See Bug

Assessment of Change

Urgency of Fix

Must be done on Thu 1/28/2021

Estimate Run Duration

A few hours at most (need to calculate number of affected (file-c-linked) awards)

Expected memory or disk usage

Long-running SQL update query. Run when there is not a great deal of other load on the DB (e.g. from the cache warmer).

Data Objects Affected

awards records

Track Changes as Deltas


Establish Transaction Type

Update in place.

Conflicting Activities

  • Be sure the pipeline from the night of the 27th has completed

  • Ensure the database is not already under heavy load

  • If there are more than perhaps 10M updates, take it in parts, so as not to throw off replication lag

  • Must complete no later than the beginning of the ES incremental index stage run in the pipeline run that starts on the 8pm schedule of the 28th

Ideal Date/Time of Execution

After pipeline finishes but before 8pm on the 28th

Establish a kill/recovery plan

  • Kill the update query and try again

  • Reduce updates into smaller chunks if necessary


  1. After the pipeline run on the night of the 27th finishes, update the updated_at field for all awards linked to BOTH:

    1. P03 monthly financial_accounts_by_awards records

    2. See example SQL below.

  2. Let the pipeline on the night of the 28th run as planned.

  3. It will pick up and recalculate the total_covid_obligation and total_covid_outlay value for all affected awards, such that they will be accurate and inclusive of P03 data by the time the pipeline finishes on the 28th


Subquery yields 149572 results in prod

Updated query for FY2021 P03

Acceptance Criteria

  1. All outlay balances published in FY2021 P03 replace prior balances that existed prior to P03 for awards with COVID DEFCs

  2. All obligation amounts submitted in P03 are added to the obligation amounts that existed prior to P03


Collins, William [USA]
February 6, 2021, 12:13 AM





Keith Hickey




Due Date