Compile List of Questions around Data Discrepancy

[sambodeme]

This ticket is to compile a list of key data mappings and questions in our data migration logic for the team to review/approve.

P.S.: For pattern matching, we ignore case and space occurrences.

1. Mappings used in Audit Information :

   Census Option	Census key	GSA/FAC Option
   "Unqualified"	"U"	"unmodified_opinion"
   "Qualified"	"Q"	"qualified_opinion"
   "Adverse"	"A"	"adverse_opinion"
   "Disclaimer"	"D"	"disclaimer_of_opinion"
   ???	"S"	"not_gaap"

Decision made on 12/06/2023: The mapping in the table above between Census Keys and GSA/FAC Options is correct and can be implemented as part of the data migration logic. On Census side the S key stands for Special Purpose Framework.

2. Handling SP_Framework Basis

   Census Option	GSA/FAC Option
   "cash"	"cash_basis"
   "other"	"other_basis"
   "regulatory"	???
   "contractual"	"contractual_basis"
   "tax"	"tax_basis"

Decision made on 12/06/2023: The GSA/FAC validation schema should be updated to include the regulatory_basis option.

3. Mapping used in General Information:

   Census Option	GSA/FAC Option
   "institution of higher education"	"higher-ed"
   "non-profit"	"non-profit"
   "local government"	"local"
   "state"	"state"
   "unknown"	"unknown"
   "tribal"	"tribal"
   Anything else	DataMigrationError

Decision made on 12/06/2023: The mapping in the table above is approved.

4. Handling of PDF Files:
   • Are we expecting to receive PDFs from Census?

Decision made on 12/06/2023: Yes, Census will provide PDFs to the GSA/FAC team. Currently, handling these files is considered out of scope.

5. Handling Missing Auditee UEI:
   • Confirm that missing auditee UEI will be replaced with GSAUEINOTFND.

Decision made on 12/06/2023: Use the GSA_MIGRATION keyword as the default value for any replacements.

6. Transformations Not Yet Activated:

   • For audits with "U", "u", "rd", or "RD" in the CFDA attribute and an empty award identification, we plan to use f"ADDITIONAL AWARD INFO - DBKEY {dbkey}". Is this correct?

   Decision made on 12/06/2023: Use the GSA_MIGRATION keyword as the default value for any replacements.

   • If the audit's LOANS attribute is "Y" and the loan balance is empty, it will be filled with 1. Is this correct?

   Decision made on 12/06/2023: Use the GSA_MIGRATION keyword as the default value for any replacements. Update the GSA/FAC validation schema to allow this keyword.

7. Negative Loan Balances:

   • How should we handle cases like dbkeys 148665 and 150450, which have a balance of -1.0?

   Decision made on 12/06/2023: Update the GSA/FAC validation schema to allow negative loan balance.

8. Handling Zero Passthrough Amounts:

   • Confirm that zero passthrough amounts will be replaced with an empty string ("").

   Decision made on 12/06/2023: The transformation should only replace zero passthrough amounts with empty strings when the is_passed flag is N.

9. Cluster Name Validation:

   • What is the procedure for handling cases where the cluster_name is not in the cluster name list, thus failing validation?

   Decision made on 12/06/2023: Allow all cluster_name values encountered to pass validation.

10. Handling 'N' in Audit's DIRECT Attribute:

    • If the audit's DIRECT attribute is "N" and the passthrough name or ID is empty, confirm that they can be filled with "NO PASSTHROUGH NAME PROVIDED" and "NO PASSTHROUGH ID PROVIDED" respectively.

Decision made on 12/06/2023: Use the GSA_MIGRATION keyword as the default value for any replacements.

11. Handling is_minimis_rate_used:
    • We may want to validate this approach.

Decision made on 12/06/2023: TBD .

12. ALN
    • How should to handle program numbers that fail to validate against our current regex? Should we rewrite them to "FIX," "UNK," or some other designation?

Decision made on 12/06/2023: If the agency prefix is validated successfully and the extension fails, replace the extension with GSA_MIGRATION. However, if the prefix itself does not validate, then the report migration should be failed..

13. audit_information.py

• Is it right to default dollar_threshold to 750,000 when no value is found, or use GSA_MIGRATION? I have seen different values for dollar_threshold in historic data, and that made me wonder if inserting a default value there might not be right. Also, I cannot think of a reason why this would be left empty, so I am more inclined to use the magic word GSA_MIGRATION here, but I am curious to know the team's decision.

Decision made on 12/12/2023: Use the GSA_MIGRATION keyword as the default value.

14. findings.py

• This is a simple one: in xform_prior_year_findings, do we default prior year findings to N/A when empty, or GSA_MIGRATION? Even though this is missing data, I am more inclined to use N/A here since it will only affect cases where users were not expected to insert any meaningful data, and we favored N/A instead of an empty cell in the 2023 validation.

Decision made on 12/12/2023: Default empty prior year fields to N/A and document.

15. report_id_generator.py

• I saw here that there is a risk of collusion in the way we generate report_id, and I am curious to know why. We use both the year and the dbkey in the report_id, so I would expect this to be unique. If it is not, then we have a bigger problem than the report_id because all our queries assume year/dbkey uniquely identifies a submission report.

Decision made on 12/12/2023: After a team analysis, it was determined that a collusion case is very unlikely to occur. Consequently, the ticket #2680 has been closed and marked as 'unplanned'.

16. general_information.py and secondary_auditors.py

• We may want to handle 4-digit and 8-digit ZIP codes in our first iteration. I have seen multiple instances like this and feel it would be right to do so. Options are: to let them through, or to prefix with a 0, or anything else the team agrees on.
  Decision made on 12/12/2023: 4-digit or 8-digit zips must be prepend with 0.

17. notes_to_sefa.py

• We don't have contains_chart_or_table in the historic data for this workbook, and because the value is disseminated, we want to agree on how we handle this. The code defaults to N, that is False, but we could also use GSA_MIGRATION, or better yet, find out where this field is hidden if it exists.

Decision made on 12/12/2023: Default empty fields to GSA_MIGRATION and document.

[gsa-suk]

The various types of CFDA values present in Census data: https://docs.google.com/spreadsheets/d/11yvfeS3RgxDGd_dcxA4RQLJhYrI8f_eYKsfUYE87w7E/edit#gid=781734012

[sambodeme]

Follow up tickets will be created to implement the aforementioned decisions

[sambodeme]

List of entity types #2799