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).
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
Track Changes as Deltas
Establish Transaction Type
Update in place.
Ideal Date/Time of Execution
After pipeline finishes but before 8pm on the 28th
Establish a kill/recovery plan
After the pipeline run on the night of the 27th finishes, update the updated_at field for all awards linked to BOTH:
P03 monthly financial_accounts_by_awards records
See example SQL below.
Let the pipeline on the night of the 28th run as planned.
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
All outlay balances published in FY2021 P03 replace prior balances that existed prior to P03 for awards with COVID DEFCs
All obligation amounts submitted in P03 are added to the obligation amounts that existed prior to P03