📍 [Epic] Migration data loading: final push

[danswick]

What problems would we like to solve?

1. Some historical data is still missing from our public dissemination data.
2. All migration records (information about how we interpreted data while migrating) have yet to be loaded into a production database.
3. SF-SAC data cannot be loaded using the same techniques we used for loading dissemination data. It's not clear how we can do so while maintaining a clean foreign key relationship between the user table and the other SF-SAC tables while the database is live and submissions are being added.

How do we know we’re done?

1. Data from each of the data categories described below has been loaded into production and can be verified using verification methods to be determined and documented.
2. Scripts and other data loading processes are documented in a single place and can be replicated later if needed.

Who will work on this epic?

@gsa-suk @rocheller123 @sambodeme @jadudm

Where are we now?

This table describes the status of each category of data-that-needs-loading.

Field	Count	Load method	Status	Date completed
singleauditchecklists (and friends)	~3,000	Utility	✅ In prod	Late June ‘24
dissemination_	~3,000	Shell script	✅ In prod	Late June ‘24
singleauditchecklists (and friends)	~300	Utility	Backed up to Drive and need to be loaded. These are leftovers from the ~3,000 batch. ✅ In prod	12/10/24
dissemination_	~300	Shell script	Backed up to Drive and need to be loaded. These are leftovers from the ~3,000 batch. ✅ In Prod	09/09/24
dissemination_	~277,000		✅ In prod	Late Jan ‘24
singleauditchecklists (and friends)	~277,000	Shell script	Backed up to S3 and need to be loaded. ✅ In prod	12/10/24
migrationstatus	~280,000	Shell script	Backed up to drive and need to be loaded. ✅ In Prod	09/09/24
Historic_ tables	?	?	Backed up to S3 and need to be loaded. These are the Census tables. ✅ In prod	09/12/24
PDF-only	212	?	Needs decision on how to handle. These are leftover reports that are just PDFs with no SF-SAC data.

Links! Tickets, documents, repos, etc. Things we’ve used to track work in recent months:

• Data loading utilities repo: https://github.com/GSA-TTS/fac-historic-data-processing
• Tracking tickets:
  • Original epic: Epic: Historic Data Migration #2837
  • Previous epic: 📍 Next steps in data migration #3364
  • Tracking some of the remaining work: Loading data #3994
• Documents:
  • Load_final_migration_dissem_data_to_Preview_Prod: https://docs.google.com/document/d/1usiTwvbHhSx6av9QrO7lpXcqClDtx4u6VI6TWONgf4I/edit
  • Load_migration_status_to_Preview_Prod: https://docs.google.com/document/d/1TCmf_rSXbPagQfhwOYWi84mZSnLyyWalRtCu06jVYP8/edit
  • Load_script_for_dissemination: https://docs.google.com/document/d/1baSE3IjPfyan4g5qJ7iKWj6cauBsEK3T43MibXlutwU/edit

What needs to happen next

This list divides the work up into three categories: housekeeping, SF-SAC strategy and loading, and loading everything else. The project team should break the work up into more specific/detailed tickets if needed.

Next steps

Preview Give feedback

1. Housekeeping: determine a "source of truth" for the data in each of the above categories and document them.
2. Housekeeping: review the linked :memo: documents above and either commit the code snippets to a codebase or mark them as deprecated.
3. SF-SAC loading #4281
   9 of 9
   eng +1
   

   
4. SF-SAC loading: depending on which approach is decided, capture the steps here. For example, if we need to take intake offline, we need to draft/review/deploy communications ahead of time.
5. Load migrationstatus data using the methods previously used to load the 3000 records. Load 318 census migration audits and ~280,000 migration status records to Prod #4266
6. Load Historic_ data using the methods previously used to load the 3000 records. #4277 Load census tables from Prod S3 to production database #4279
7. Load the 300 remaining dissemination records using the methods previously used to load the 3000 records. Load 318 census migration audits and ~280,000 migration status records to Prod #4266
8. https://github.com/GSA-TTS/fac-team/issues/21

Notes previously: https://docs.google.com/document/d/1wC8PC3_VeAz09-msIL_uIRO9a3tzz1nOgqnbMdWoczE/edit

[gsa-suk]

09/09/24 - Load 318 dissemination data and migration status to Prod (Sudha, Hassan, Rochelle, Matt)
#4266

[gsa-suk]

09/12/24 - Load census historical tables to Prod - (Sudha, Matt, Rochelle)

#4279

[danswick]

• Testing should be completed in the next couple of days.
• Need to determine communications plan: how long to give notice after testing is complete.

[gsa-suk]

SAC data load procedure - https://docs.google.com/document/d/1CsCICVy0FZO4YxvO7RobQUcEltEBOkO0RkieN--GFcs/edit
Final data load related documents are in GDrive - https://drive.google.com/drive/folders/1ZdzHe0s9w_7gb6ansOYbPbkv5WhFware

[gsa-suk]

Testing SAC load procedure with Prod data loaded into Staging:

1. Updated audit_singleauditcheklist with user id from prod auth_user.
2. Loaded 275K + 316 historic sacs to prod table in Staging.
3. Working on exporting audit_singleauditchecklist with 330+K audits from Staging to the GFE.

[gsa-suk]

10/22/24 -

1. Copied 275K+316 reportfile and access data to Staging S3.
2. Loaded 275K+316 reportfile and access to Staging.
3. Ran tests to verify loaded data in Staging.

[gsa-suk]

10/25/24 -

Tested 'Maintenance mode toggle' in Staging. This worked well. When maintenance mode was turned on, sac could not be created. When maintenance mode was turned off, sac could be created.

[gsa-suk]

10/29/24 -

Copied ~4.5 G sac data files to Prod S3. #4421

[danswick]

SAC data load SSH record: #4527

[Leighdiddy]

Closing as completed on 12/10/24

[danswick]

@Leighdiddy this isn't quite complete yet. There are still a couple of smaller tables that need to be loaded.

[danswick]

Following #4540, this is done!