Set `serum_id` to `lot_number` for CDC titer imports about fauna HOT 4 CLOSED

If so, would it be possible to do a one-time delete of everything from 2019-onward and a fresh upload that maps the lot number to the serum id column? Then subsequent updates wouldn't change the index, right?

Yup, I can track down the date that we started using the new database dump and delete records in cdc_tdb/flu starting from that date. Then we can re-upload all records with the changed serum id.

Surfacing lot_number in addition to serum_id sounds much easier technically, although this means adding a column that only the CDC will use and that serum_id remains meaningless for them.

Oh right! The lot_number column only exists in the CDC database, but does not exist for the other CCs. That would mean we would have to special case the columns to use in the tdb/download script based on the database. Hmm, I think your original proposal is better 🤔

I did a little more digging into the fauna/tdb code and the parse step automatically assigns the ferret_id to the serum_id, so we may also want to remove the ferret_id in the column map.

from fauna.

One small issue with changing the underlying data for serum_id is that it is one of the index fields for generating the record index. We need to remember to delete the records already in fauna and re-upload them with the new serum_id so that we don't have duplicate titers.

Another issue is do we need to change over the serum_id for older titer measurements? The mapping of columns for CDC data only applies to their new data dump that only goes back to September 2019. I would have to do some digging to figure out how the older titers serum_id was set.

Stepping back a bit, I think the current values of serum_id and lot_number are accurate for our data model. If the CDC is wanting to use the lot number for discussions, then maybe we can just include this column in our downloads from fauna instead of overhauling the data model.

from fauna.

Good points! I hadn't thought about repopulating the database. I was thinking more about updating the mapping now so future imports (new records) use the lot number instead. Would that kind of change disrupt uploads of new data, though, because the uploads work from the full CDC database TSV and all older records would get an updated index? If so, would it be possible to do a one-time delete of everything from 2019-onward and a fresh upload that maps the lot number to the serum id column? Then subsequent updates wouldn't change the index, right?

Surfacing lot_number in addition to serum_id sounds much easier technically, although this means adding a column that only the CDC will use and that serum_id remains meaningless for them. This becomes a new field to display in the measurements tooltips, a filter and group-by column to add to the measurements config, etc. It's not a big deal, but it reduces the value of the original serum id.

The issue of recreating fauna from older data could be important to figure out eventually, if we really hope to deprecate fauna in favor of a cloud-based file store solution...but this probably isn't the place to discuss that undertaking. 😅

from fauna.

Yup, I can track down the date that we started using the new database dump and delete records in cdc_tdb/flu starting from that date.

Never mind, just realized the start date of the new database dump doesn't matter because the data contains tests from 2019. Like you said, we can delete records based on assay_date. The earliest assay_date included in the database dump is 2019-09-03.

from fauna.