load_submission

Purpose

Provide a walkthrough of the load_submission management command.

Overview

load_submission is responsible for pulling File A, B, C data and Submission metadata from Broker for a single DABS submission. It is executed from the command line via ./manage.py load_submission submission_id and is an integral part of the nightly USAspending pipeline where its execution is initiated indirectly via the load_multiple_submissions command, which is the entrypoint of the Broker Submission Loader.

load_submission accepts two command line arguments:

• submission_id is a required positional argument used to indicate a single Broker submission.submission_id to be loaded.
• --test is an optional switch that causes the loader to utilize a "phony cursor" that reads data from usaspending_api/etl/tests/etl_test_data.json rather than attempting to pull data from Broker. As the name implies, this is strictly for testing.

Steps

1. Attempt to find USAspending submission using broker_submission_id.
2. If it does not exist:
   1. Create a stub.
3. If it does exist:
   1. Delete the existing submission using rm_submission.
4. Find the agency to which this submission belongs.
5. Fill in the stub or recreate the submission record if it was deleted.
6. Retrieve File A data from Broker's certified_appropriation table.
   1. Fetch the referred to TAS from the DB and save in an in-memory dict to prevent further fetching of this oft-referenced (by File A, File B, and File C records) data.
      1. If the referred to TAS did not exist, save it in a skipped_tas dictionary for logging later, and SKIP THIS File A RECORD
   2. Save File A record in USAspending's appropriation_account_balances table, reversing the sign of the gross_outlay_amount_by_tas_cpe column.
   3. Calculate final_of_fy for appropriation_account_balances.
   4. Log count of File C rows skipped due to missing TAS
7. Retrieve File B data from Broker's certified_object_class_program_activity table.
   1. Fetch the referred to TAS from the DB and save in an in-memory dict to prevent further fetching of this oft-referenced (by File A, File B, and File C records) data.
      1. If the referred to TAS did not exist, save it in a skipped_tas dictionary for logging later, and SKIP THIS File B RECORD
   2. Save File B record in USAspending's financial_accounts_by_program_activity_object_class table, reversing the sign of the transaction_obligated_amount column or any columns that end in _cpe or fyb.
   3. Calculate final_of_fy for financial_accounts_by_program_activity_object_class.
   4. Log count of File B rows skipped due to missing TAS
8. Retrieve File C data from Broker's certified_award_financial table where rows have a non-zero, non-null transaction_obligated_amou amount.
   1. Uppercase all text fields in the certified_award_financial record.
   2. Fetch the referred to TAS from the DB and save in an in-memory dict to prevent further fetching of this oft-referenced (by File A, File B, and File C records) data.
      1. If the referred to TAS did not exist, save it in a skipped_tas dictionary for logging later, and SKIP THIS File C RECORD
   3. Look up an award record from previously loaded award data using piid, fain, or uri, by way of the C-to-D linkage process, and link this File C record to it if 1 is found, otherwise leave NULL.
   4. Save File C record in USAspending's financial_accounts_by_awards table, reversing the sign of the transaction_obligated_amount column or any columns that end in _cpe or fyb.
   5. Log count of File C rows skipped due to missing TAS
   6. Return an array of award IDs that were linked to File C records (which appear to have nothing done with them)

DEPRECATED

Submission Chaining

⚠️ "Submission Chaining" is deprecated, and will be removed since it was found that the end-result was not used anywhere in the application. Steps above that accommodating this had references to submission chaining removed

A quick word about submission chaining. This is a concept that exists in USAspending that is not found in Broker. Many numeric values in submissions are reported as CPE (Current Period End) which means they are cumulative for the fiscal year right up until the end of the period they represent. In order to calculate periodic net values, we link submissions to their previous submission in the fiscal year which allows us to quickly calculate a period's net values by subtracting period N-1's CPE values from period N's. This will be important in a minute.