Data is not correctly uploaded in the database?

[JulienPeloton]

Take https://fink-portal.org/ZTF24aahtdhb. The latest AVRO alert packet (20240416) gives

Note that here, noisy data and valid data have the same markers. The portal, at the same period, gives:

Possible scenarios:

1. HBase jobs have failed
2. Code to populate Hbase table for upper limit is not correct (recent changes Add more columns for uppervalid alerts #785)

[JulienPeloton]

Some more inside: the script (fink-broker/bin/index_archival.py) looks at the most recent measurement in the history, and finds if the status was upper or uppervalid. If it is valid, the whole history is not considered.

In an ideal world, if the point in the history is valid, no need to push data from the history indeed, because it was pushed when receiving data from the previous valid alert then. But this assumes we did not miss the previous valid alert! Missing an alert can happen if: Fink was down or HBase crashed. This does not happen frequently, but it does. In that case (e.g. receiving the 2nd valid alert), we just start from the last valid alert, but we do not push data in the history...

How could we check a valid alert in the history has been indeed processed and pushed by Fink? (without pushing all data blindly as we were doing before...)

[JulienPeloton]

For that specific night and that specific object though (2024/03/27) something fishy happened. The history says that the previous alert was valid, and that it happened on the same night:

import pyspark.sql.functions as F
from fink_utils.spark.utils import check_status_last_prv_candidates

df = spark.read.format('parquet').load('archive/science/year=2024/month=03/day=27')
df = df.filter(df['objectId'] == 'ZTF24aahtdhb')

kinds = ['valid', 'upper', 'uppervalid']
for kind in kinds:
  df = check_status_last_prv_candidates(df, status=kind)

df.select(kinds).show()
+-----+-----+----------+
|valid|upper|uppervalid|
+-----+-----+----------+
| true|false|     false|
+-----+-----+----------+

df.select(F.element_at('prv_candidates.jd', -1)).show()
+---------------------------------+
|element_at(prv_candidates.jd, -1)|
+---------------------------------+
|                  2460396.8996991|
+---------------------------------+

But looking at the raw data, no sign of it:

df_raw = spark.read.format('parquet').load('archive/raw/year=2024/month=03/day=27')
df_raw = df_raw.filter(df_raw['objectId'] == 'ZTF24aahtdhb')
df_raw.count()
Out[35]: 1

I inspected streaming & database logs, but there is no sign of problems...

[JulienPeloton]

And another generic problem: we check only the last measurement, and push data if it is upper or uppervalid. But truly only one at a time. So if the last measurement is upper, but the history contains earlier uppervalid, we will not push these uppervalid... 😭 and vice-versa for uppervalid.