Table migration transforms
The Census data from 2016 to 2022 saw multiple changes to the SF-SAC and validations that were applied. For example, there are entire sections of the form that did not exist before 2018.
The tables below describe the transformations that we applied to Census data tables in migrating to the GSA intake.
Where we say No transformation, this means that we passed data through without any modification or further validation.
Table: general
Transformations to data in the dissemination table general.
| Census |
GSA |
Data type |
| AUDITYEAR |
audit_year |
No transformation |
| TYPEOFENTITY |
entity_type |
Transformation recorded |
| FYENDDATE |
fy_start_date |
Subtracts 365 days from FYENDDATE and converts the result to the 'YYYY-MM-DD' format |
| FYENDDATE |
fy_end_date |
Converts FYENDDATE to the 'YYYY-MM-DD' format |
| AUDITTYPE |
audit_type |
Transformation recorded |
| PERIODCOVERED |
audit_period_covered |
Transformation recorded |
| EIN |
auditee_ein |
No transformation |
| AUDITEENAME |
auditee_name |
No transformation |
| STREET1 |
auditee_address_line_1 |
No transformation |
| CITY |
auditee_city |
No transformation |
| STATE |
auditee_state |
No transformation |
| ZIPCODE |
auditee_zip |
Removes hyphens.
Pads with a leading zero when zipcode only contains 4 or 8 digits. |
| AUDITEECONTACT |
auditee_contact_name |
No transformation |
| AUDITEETITLE |
auditee_contact_title |
No transformation |
| AUDITEEPHONE |
auditee_phone |
No transformation |
| AUDITEEEMAIL |
auditee_email |
Uses “GSA_MIGRATION” when AUDITEEEMAIL is empty |
| AUDITEEDATESIGNED |
auditee_certified_date |
Converts AUDITEEDATESIGNED to UTC 'YYYY-MM-DD HH:MM:SS.ffffff±HH' format. |
| CPAFIRMNAME |
auditor_firm_name |
No transformation |
| CPASTREET1 |
auditor_address_line_1 |
No transformation |
| CPACITY |
auditor_city |
No transformation |
| CPASTATE |
auditor_state |
No transformation |
| CPAZIPCODE |
auditor_zip |
Removes hyphens.
Pads with a leading zero when zipcode only contains 4 or 8 digits. |
| CPACONTACT |
auditor_contact_name |
No transformation |
| CPATITLE |
auditor_contact_title |
No transformation |
| CPAPHONE |
auditor_phone |
No transformation |
| CPAEMAIL |
auditor_email |
Uses “GSA_MIGRATION” when CPAEMAIL is empty |
| CPADATESIGNED |
auditor_certified_date |
Converts CPADATESIGNED to UTC 'YYYY-MM-DD HH:MM:SS.ffffff±HH' format. |
| COGAGENCY |
cognizant_agency |
No transformation |
| OVERSIGHTAGENCY |
oversight_agency |
No transformation |
| TYPEREPORT_FS |
gaap_results |
Transformation recorded |
| SP_FRAMEWORK |
sp_framework_basis |
Transformation recorded |
| TYPEREPORT_SP_FRAMEWORK |
sp_framework_opinions |
Transformation recorded |
| GOINGCONCERN |
is_going_concern_included |
No transformation† |
| MATERIALWEAKNESS |
is_internal_control_material_weakness_disclosed |
No transformation† |
| DUP_REPORTS |
is_aicpa_audit_guide_included |
No transformation† |
| DOLLARTHRESHOLD |
dollar_threshold |
Uses “-999999999” when DOLLARTHRESHOLD is empty |
| LOWRISK |
is_low_risk_auditee |
No transformation† |
| TOTFEDEXPEND |
total_amount_expended |
Uses sum of AMOUNT instead of TOTFEDEXPEND |
| AUDITOR_EIN |
auditor_ein |
No transformation |
| FACACCEPTEDDATE |
fac_accepted_date |
No transformation†† |
| CPAFOREIGN |
auditor_foreign_address |
No transformation |
| CPACOUNTRY |
auditor_country |
Uses “USA” when “US” is provided. Uses “USA” or when CPACOUNTRY is empty and CPASTATE is a valid US state. |
| UEI |
auditee_uei |
Uses “GSA_MIGRATION” when empty |
| MULTIPLEUEIS |
is_additional_ueis |
No transformation |
| AUDITEECERTIFYNAME |
auditee_certify_name |
Uses “AUDITEENAME” instead of “AUDITEECERTIFYNAME”. This is a bug that will be addressed. |
| AUDITEECERTIFYTITLE |
auditee_certify_title |
Uses “AUDITEETITLE” instead of “AUDITEECERTIFYTITLE”. This is a bug that will be addressed. |
| SP_FRAMEWORK_REQUIRED |
is_sp_framework_required |
Transformation recorded |
| SIGNIFICANTDEFICIENCY |
is_internal_control_deficiency_disclosed |
No transformation† |
| MATERIALNONCOMPLIANCE |
is_material_noncompliance_disclosed |
No transformation† |
| PYSCHEDULE |
agencies_with_prior_findings |
No transformation |
| SUPPRESSION_CODE |
is_public |
Uses false when entity type is “tribal” and SUPPRESSION_CODE is "IT" and true otherwise |
† The dissemination logic converts booleans to a “yes” or “no” text instead, this change is unrelated to the migration logic.
††: The dissemination logic converts this date from utc time zone to american samoa zone. This is transformation is unrelated to the migration logic
Table: federal_awards
Transformations to data in the dissemination table federal_awards.
| Census |
GSA |
Data type |
| CFDA_PREFIX |
federal_agency_prefix |
No transformation |
| CFDA_EXT |
federal_award_extension |
Transformation recorded |
| AWARDIDENTIFICATION |
additional_award_identification |
Uses “GSA_MIGRATION” when CFDA contains "U" or "RD" (case insensitive) and AWARDIDENTIFICATION is empty |
| FEDERALPROGRAMNAME |
federal_program_name |
No transformation |
| AMOUNT |
amount_expended |
Transformation recorded |
| CLUSTERNAME |
cluster_name |
Uses “GSA_MIGRATION” when CLUSTERNAME is empty |
| STATECLUSTERNAME |
state_cluster_name |
Uses “GSA_MIGRATION” when STATECLUSTERNAME is empty and CLUSTERNAME matches "STATE CLUSTER" |
| PROGRAMTOTAL |
federal_program_total |
Transformation recorded |
| CLUSTERTOTAL |
cluster_total |
Transformation recorded |
| DIRECT |
is_direct |
No transformation |
| PASSTHROUGHAWARD |
is_passthrough_award |
No transformation |
| PASSTHROUGHAMOUNT |
passthrough_amount |
Uses -999999999 when PASSTHROUGHAMOUNT is empty and PASSTHROUGHAWARD is "Y". Uses an empty string when PASSTHROUGHAMOUNT is empty or zero and PASSTHROUGHAWARD is "N". |
| MAJORPROGRAM |
is_major |
No transformation |
| TYPEREPORT_MP |
audit_report_type |
No transformation |
| LOANS |
is_loan |
No transformation |
| LOANBALANCE |
loan_balance |
Uses “GSA_MIGRATION” when LOANBALANCE is empty and LOANS is “Y”. Uses an empty string when LOANBALANCE is empty or zero and LOANS is “N”. |
| FINDINGSCOUNT |
findings_count |
Uses 0 when FINDINGSCOUNT is empty |
| ELECAUDITSID |
award_reference |
Transformation recorded |
| OTHERCLUSTERNAME |
other_cluster_name |
Uses “GSA_MIGRATION” when OTHERCLUSTERNAME is empty and CLUSTERNAME matches "OTHER CLUSTER NOT LISTED ABOVE" |
Table: notes_to_sefa
Transformations to data in the dissemination table notes_to_sefa.
| Census |
GSA |
Data type |
| TITLE |
title |
No transformation |
| CONTENT+TYPE_ID |
content |
No transformation (TYPE_ID=”3”) |
| CONTENT+TYPE_ID+NOTE_INDEX |
is_minimis_rate_used |
Transformation recorded |
| CONTENT+TYPE_ID |
accounting_policies |
Uses “GSA_MIGRATION” when CONTENT is empty and TYPE_ID=”1” |
| CONTENT+TYPE_ID |
rate_explained |
Uses “GSA_MIGRATION” when CONTENT is empty and TYPE_ID=”2” |
Table: findings
Transformations to data in the dissemination table findings.
| Census |
GSA |
Data type |
| FINDINGSREFNUMS |
reference_number |
No transformation |
| TYPEREQUIREMENT |
type_requirement |
Sorts and uppercases the string |
| MODIFIEDOPINION |
is_modified_opinion |
No transformation |
| OTHERNONCOMPLIANCE |
is_other_matters |
No transformation |
| MATERIALWEAKNESS |
is_material_weakness |
No transformation |
| SIGNIFICANTDEFICIENCY |
is_significant_deficiency |
No transformation |
| OTHERFINDINGS |
is_other_findings |
No transformation |
| QCOSTS |
is_questioned_costs |
No transformation |
| REPEATFINDING |
is_repeat_finding |
No transformation |
| PRIORFINDINGREFNUMS |
prior_finding_ref_numbers |
Uses “N/A” when PRIORFINDINGREFNUMS is empty |
Table: findings_text
Transformations to data in the dissemination table findings_text.
| Census |
GSA |
Data type |
| FINDINGREFNUMS |
finding_ref_number |
No transformation |
| TEXT |
planned_action |
Uses “GSA_MIGRATION” when TEXT is empty |
| CHARTSTABLES |
contains_chart_or_table |
Uses “GSA_MIGRATION” when TEXT is empty |
Table: corrective_action_plans
Transformations to data in the dissemination table corrective_action_plans.
| Census |
GSA |
Data type |
| FINDINGREFNUMS |
finding_ref_number |
No transformation |
| TEXT |
planned_action |
Uses “GSA_MIGRATION” when TEXT is empty |
| CHARTSTABLES |
contains_chart_or_table |
Uses “GSA_MIGRATION” when TEXT is empty |
Table: passthrough
Transformations to data in the dissemination table passthrough.
| Census |
GSA |
Data type |
| PASSTHROUGHNAME |
passthrough_name |
Uses “GSA_MIGRATION” when DIRECT =”N” and PASSTHROUGHID exists but no corresponding PASSTHROUGHNAME is found |
| PASSTHROUGHID |
passthrough_id |
Uses “GSA_MIGRATION” when DIRECT =”N” and PASSTHROUGHNAME exists but no corresponding PASSTHROUGHID is found. |
Table: secondary_auditors
Transformations to data in the dissemination table secondary_auditors.
| Census |
GSA |
Data type |
| CPAZIPCODE |
address_zipcode |
Removes hyphens.
Pads with a leading zero when zipcode only contains 4 or 8 digits. |
| CPATITLE |
contact_title |
No transformation |
| CPACITY |
address_city |
No transformation |
| CPASTATE |
address_state |
No transformation |
| CPACONTACT |
contact_name |
No transformation |
| CPAEIN |
auditor_ein |
No transformation |
| CPAFIRMNAME |
auditor_name |
No transformation |
| CPAEMAIL |
contact_email |
No transformation |
| CPAPHONE |
contact_phone |
No transformation |
| CPASTREET1 |
address_street |
No transformation |
Table: additional_ueis
Transformations to data in the dissemination table additional_ueis.
| Census |
GSA |
Data type |
| UEI |
additional_uei |
No transformation |
Table: additional_eins
Transformations to data in the dissemination table additional_eins.
| Census |
GSA |
Data type |
| EIN |
additional_ein |
No transformation |
