Not delete old achilles results and compare data between ETLs in Heel

[vojtechhuser]

Expected behavior

• achilles results tables are not deleted but archived
• data from previous ETL are compared to new version of the data
  (e.g., big change in number of outpatients visit for historic year may indicate ETL problem)

Actual behavior

• resutls tables are deleted

[djb2188]

We are working on this internally at CU. Let's all chat. Would love to hear your ideas.

[alondhe]

At Janssen, we build a new database for each load, and do not delete any Achilles results tables. We do compare Achilles results from load to another simply by viewing the 2 URLs side by side, but I agree, we should have a true comparison feature. Good idea!

[ericaVoss]

We've been talking about this for awhile. Like @alondhe we just do it the manual way but looking at the results in the web page, but it would be nice to do it a more sophisticated way.

[vojtechhuser]

Well, not all Achilles run is a milestone run.

As I was thinking about it...... ... the delete can stay and we can have a R function that creates a milestone copy and assigns it a name.

createAchillesMilestone(milestone_id='2018q2')

and it will make a copy of tables into their xxx_archive counterparts with extra the column milestone_id

[vojtechhuser]

Implement all terms defined by CHOP team (we just spoke about this in DQ tutorial team)

[vojtechhuser]

It would be nice even in v 1.6 to make an archive copy of the schema prior deletion. Introduce an *_archive tables and copy the content there

[alondhe]

DBAs could handle schema archival, no? I just worry this just adds more functionality to worry about across multiple db platforms.

[vojtechhuser]

ok. can you elaborate how in R we would trigger archiving of the achilles_result table?

[alondhe]

As @vojtechhuser and I discussed, perhaps the best option is to have exportToRds functions to archive old CDM results without having to keep old CDMs active on a database platform.

@rtmill and I discussed the need to have a standard way to tag versions of CDMs. Currently, sites handle this locally, but the tools cannot utilize these conventions. Versions of a CDM, lineage with former and future CDMs.

Achilles 2 should provide a broader vision of database characterization, in which we can compare across sources and within sources.

[vojtechhuser]

I am ready to develop milestone_archive() function which would save into .rds file all rows in ACHILLES_RESULTS table. If I get some likes, I will develop it. Please provide your modifications to this idea.

[alondhe]

@clairblacketer has been developing an Achilles compare script that could be part of 2.0. Perhaps as part of this effort, we would add an RDS archival function.