BUG: FOPH DAG not downloading

[fccoelho]

The FOPH DAG stopped downloading new data in January 2023. Yet, the DAG does not log any error messages.

This silent failure has to be investigated to determine its cause. However the DAG code needs to be improved to validate that at each step the the results are as expected. For example in the line below the last date of of the table in the database is compared to the downloaded CSV in a if clause. However there is no else, so that we can take some action if they if the dates are equal, at the very least log the fact.

EpiGraphHub/containers/airflow/dags/foph_dag.py

Line 207 in e6bb214

if not equal:

In summary, the DAG is dreaming it is doing its job when it's not.

[luabida]

@fccoelho which tables are not updating? did you confirm if there is missing data compared to the data source?

[fccoelho]

I checked foph_cases_d with this query

but I think all tables maintained by this DAG have the same issue

[fccoelho]

I also noticed that the foph capture does not have unit tests in epigraphhub_py, maybe that's why it stopped working and no one noticed.

[luabida]

@fccoelho Something is a bit off when comparing the data from the query you sent with the csv that is being used to update the table: https://www.covid19.admin.ch/api/data/20230411-n9izx3j0/sources/COVID19Cases_geoRegion.csv

In the CSV there is no updates since 2023-01-01. Also, the sum column for every entries after this date in the query is at a oddly constant of 780000+. I'm thinking this could be a residue from the previous updates

[luabida]

I also noticed that the foph capture does not have unit tests in epigraphhub_py, maybe that's why it stopped working and no one noticed.

I'll add it to top priority

[fccoelho]

maybe the daily CSV's are not being updated, so we should check the weekly. I remember hearing a while ago that they would stop updating the data daily.

[luabida]

https://www.covid19.admin.ch/api/data/20230411-n9izx3j0/sources/COVID19Cases_geoRegion_w.csv the table looks updated, but the columns name are a bit different (with the week ref):

In [12]: [c for c in weekly if c not in daily]
Out[12]: 
['freq',
 'prct',
 'datum_dboardformated',
 'entries_diff_abs',
 'entries_diff_inz',
 'entries_diff_pct',
 'prct_diff',
 'timeframe_2w',
 'timeframe_4w',
 'sum2w',
 'sum4w',
 'mean2w',
 'mean4w',
 'sumTotal_last2w',
 'sumTotal_last4w',
 'inzmean2w',
 'inzmean4w',
 'inzsumTotal_last2w',
 'inzsumTotal_last4w',
 'inzsum2w',
 'inzsum4w']

Details

In [16]: sorted(weekly.columns)
Out[16]:
['datum',
'datum_dboardformated',
'datum_unit',
'entries',
'entries_diff_abs',
'entries_diff_inz',
'entries_diff_last',
'entries_diff_last_age',
'entries_diff_pct',
'entries_letzter_stand',
'entries_neu_gemeldet',
'freq',
'geoRegion',
'inz_entries',
'inzmean2w',
'inzmean4w',
'inzsum2w',
'inzsum4w',
'inzsumTotal',
'inzsumTotal_last2w',
'inzsumTotal_last4w',
'mean2w',
'mean4w',
'pop',
'prct',
'prct_diff',
'sum2w',
'sum4w',
'sumTotal',
'sumTotal_last2w',
'sumTotal_last4w',
'timeframe_2w',
'timeframe_4w',
'timeframe_all',
'type',
'type_variant',
'version']

In [17]: sorted(daily.columns)
Out[17]:
['datum',
'datum_unit',
'entries',
'entries_diff_last',
'entries_diff_last_age',
'entries_letzter_stand',
'entries_neu_gemeldet',
'geoRegion',
'inz_entries',
'inzdelta7d',
'inzmean14d',
'inzmean7d',
'inzsum14d',
'inzsum7d',
'inzsumTotal',
'inzsumTotal_last14d',
'inzsumTotal_last28d',
'inzsumTotal_last7d',
'mean14d',
'mean7d',
'offset_last14d',
'offset_last28d',
'offset_last7d',
'pop',
'sum14d',
'sum7d',
'sumTotal',
'sumTotal_last14d',
'sumTotal_last28d',
'sumTotal_last7d',
'sumdelta7d',
'timeframe_14d',
'timeframe_all',
'type',
'type_variant',
'version']

[fccoelho]

If the daily are not being updated. We leave the foph_*_d tables unchanged, from now on and switch to downloading into new tables called foph_*_w we should create a new dag for it (based on the daily) and disable the daily download but retain the source code.