Redundant results files

[sfsinger19103]

Scenario: A state publishes:

• On Nov 4, a preliminary election-day results file by precinct
• On Nov 5, a preliminary absentee results file by county
• On Nov 7, an updated absentee results file by county.

How should the system handle this? If all three files are munged, and then all results for 2020 General are pulled for the state, some absentee votes will be counted twice, while some other absentee and all the election-day votes will be counted once.

[sfsinger19103]

One way:

Add a step between finalizing the list of files to be munged and actually munging:

• Run through the results.ini files.
• For each jurisdiction-election pair represented, pull list of all results files already in the database.
• Require data-loading user to indicate which (if any) of the results files already in the database will be obsolete once new files are loaded. Also identify any new files that should not be loaded.
• Encourage careful double-checking

Then, during the munging, for each given jurisdiction-election pair:

• remove vote counts associated to the redundant files (as identified by data-loading user)
• munge the new files.

Questions:

• how to handle requests for the state's data in the time between the dropping of the old, redundant info and the loading of the new info?
• if analysis is cached, how to uncache it to allow new data to be presented to front-end user?

[ericmtsai]

Conversation from Slack:
eric 12:39 PM
@stephanie thinking more, a heavy-handed and resource intensive (but accurate?) way to solve your issue might be:

1. In the VoteCount table keep the datafile ID from the uniqueness constraint
2. Not sure where we ended up on the datafile table, but add download_date to the constraint. Then the issue here becomes, what happens if the process is somebody uploads the day after file, then the week after file, then the day after file again? That would be problematic.
3. In the codebase, ensure that the next file uploaded has a download date later than an existing file for that election/jurisdiction. This is subject to some degree of human error, but as you mentioned, not sure there's any way to get around that
4. Then in the code, start a DB transaction before any of the dataloading processes occur
5. While loading the data, we would need some way of tracking whether things get newly inserted (but as you mentioned, ignoring the _datafile constraint in this case)
6. If after the data is processed, database.most_recent_election() has hardcoded param #5 shows that some new data was inserted, then we COMMIT then transaction
7. Otherwise we ROLLBACK
8. Then the Analyzer code just needs to ensure that it pulls data associated with the max(download_date) to work correctly
9. And there may be some other nuances with your example in the issue that you wrote up, such as accounting for different files for different vote types
10. So essentially, we're trying to load the data and using database transactions to our advantage, rather than writing data removal code. This seems all possible with the tools we're currently using: https://www.oddbird.net/2014/06/14/sqlalchemy-postgres-autocommit/ (edited)

eric 12:57 PM
I think we discussed a good portion of this, but wanted to put it all together. Also, I wasn't sure whether to put this into the issue itself or not because it seems to me like that issue describes essentially upserting the data, whereas we went down a new path of keeping all the data across the incremental files (as I was calling them)

eric 1:11 PM
Actually we could avoid the transaction/commit/rollback stuff altogether by writing the new data to a temp table and then checking the data against what already exists in the "real" table. So first we load all data into a temporary table, then we check to see if it already exists using SQL's EXCEPT which is set difference. If the EXCEPT returns anything, then we insert into the the real table, otherwise we drop the temp table. However, we can always update the download_date in the datafile table to keep a record of the latest date the data was tried

Stephanie 1:15 PM
Note there is already an upload timestamp on the _datafile table. We could use that instead of a human-created download-date file.
Potential problem: person uploads files in wrong order. But that's recoverable -- load the latest file again -- and we could put in some features to help users not do that.

eric 1:16 PM
I actually don't think that will be a problem because that data will already exist in the database, so the EXCEPT will filter that out

[sfsinger19103]

Addressed in #245: we will unload and reload all data for the given election-state pair

[sfsinger19103]

Need to add optional obsolete_dir parameter to run_time.ini and move unloaded files from archive_dir