Broken relationship between audio and audio_set at ingestion in the production API DB

[obulat]

Problem

The API was expecting audio_set_foreign_id at the top level, but the catalog did not expose it. It was a part of the audio_set JSONB column.

Description

We should add the audio_set_foreign_id to the materialized view.

[krysal]

@obulat Thanks for investigating and finding the root cause of the issue! I changed the linking of issues before noticing you had updated this. Do you believe that the data refresh process will automatically update the existing data or do we have to do it manually? We can close this once confirming the existing audio files are correctly linked.

[obulat]

I'm not sure if the data refresh DAG updates the materialized view. I'm sure @stacimc knows, though :) If it does, then a data refresh will fix the issue automatically. Otherwise, we will need to refresh the matview separately, before doing the data refresh.

[stacimc]

On a regular data refresh, the matview gets refreshed. This is the SQL that runs: https://github.com/WordPress/openverse-catalog/blob/main/openverse_catalog/dags/common/popularity/sql.py#L374

I'm not sure I follow what needs to happen here -- do you just need the view refreshed, or does a column need to be added? The audio matview is created/defined here and the audioset matview here. If changes need to be made to the views structure, we would make those changes there and then run the recreate_audio_popularity_calculation DAG to completely recreate everything.

If a view refresh is all that's needed, then yes this would be resolved with a data refresh. The data refreshes are not currently running, however (pending API latency investigation).

[obulat]

Thank you for clarification, @stacimc!
We will need to run recreate_audio_popularity_calculation, not simply refresh the matview. This is because we will need to recreate the columns to add the new audio_set_foreign_id column. I ran the recreate_audio_popularity_calculation DAG locally to test that this works.

Do we normally run this manually?

[zackkrida]

Do we normally run this manually?

@obulat yes that's not scheduled, only triggered manually in airflow.

[zackkrida]

Is this resolved from a code perspective? Or do we want to wait to close this until we've run recreate_audio_popularity_calculation in production?

[krysal]

Yes, it remains open until confirmed it's fixed once we run the data refresh again.

[AetherUnbound]

We've added the column to the upstream catalog view, run an audio data refresh, and can now see audio_set_foreign_identifier values in the production API DB!

deploy@localhost:openledger> select count(*) from audio;
+--------+
| count  |
|--------|
| 817198 |
+--------+
SELECT 1
Time: 0.110s
deploy@localhost:openledger> select count(*) from audio where audio_set_foreign_identifier IS NOT NULL;
+--------+
| count  |
|--------|
| 705981 |
+--------+
SELECT 1
Time: 0.242s

With that I believe this issue can be closed 😄