[ETL] ETL & Analytics Backlog

Background / motivation

There were many remaining issues inside of duckdb itnegration, and github issues. Most tickets have been closed, including the really important ones so we can consolidate tasks and prioritize/tackle them in a clean manner.

Only the ones actively in-development and in-discussion are open.

Please re-open and address tickets 1-by-1 as you take them.

Outstanding items

[Post-DuckDB Merge - Core Functionality - Important]

[ ] ETL-Incremental #1001
[ ] Readme for working the lake and completing UX e2e is documented and works well end-to-end #1002
[ ] ETL-Incremental loops(true) updating the lake #1107
[ ] Finish cherry-picking/integrating remaining issues into main branch #1248
[ ] If you delete CSVs and rebuild RAW tables, you get duplicate data #1087
[ ] Improve visualizing the lake data so we can properly understand whats going on #1104

[Post-DuckDB - Other Functionality - To be prioritized] These have been fronzen/closed/need to be reviewed such that we can continue to expand subgraph/ETL functionality.

[ ] Improve fetching and ETL by just grabbing the appropriate data from subgraph #989
[ ] Use revenue + roundStakes from trueval and save it to slots table... #1183
[ ] ETL - Cleanup payout, truevals, and revenue calculations - #1183
[ ] Build predictoor income dashboard using latest dash/duckdb/incremental #612
[ ] DuckDB - Re-enable subscription table #1085
[ ] DuckDB - Re-enable bronze_slots tables - #595
[ ] PredictoorETL is handling st_ts and end_ts correctly #1086
[ ] Data Store Objects - Rename functions to use sql nomenclature: fill becomes insert, override becomes upsert
[ ] OHLCV + CSVDS will be updated after DuckDB has been updated #769
[ ] CSVDataWriter will perform very poorly when loading data between ranges -> csv_data_store.read(st_ts, end_ts)
[ ] DuckDB - Implement "silver predictions" #665
[ ] DuckDB - Port latest ETL "silver predictions" to use duckdb/sql + close old PR #741
[ ] DuckDB - Silver predictions SQL PR #848
[ ] Calculate stake vs. df rewards #746
[ ] Incremental silver tables #610
[ ] Build predictoor income dashboard #615
[ ] Predictoor Income - Revenue vs. expenses plot #624
[ ] Predictoor Income - Hook up filtering, users, and anything else #623
[ ] Ingest & hook up pdr-contracts into ETL #616
[ ] Evolve pipeline from calculating checkpoints to using explicit checkpoints #983
[ ] Cleanup PredictSlot and update accuracy app to use data from lake #1041
[ ] App/accuracy endpoint should be using lake data #1041
[ ] Test app/accuracy endpoint is working correctly #1048
[ ] Fix CSVDataStore.read() such that it uses st_ut and fin_ut effectively... the current implementation does not scale and could eventually lead to OOM.

Low Priority Improvements:

[ ] There are various fetches/queries that aren't optimal. https://github.com/oceanprotocol/pdr-backend/issues/575

DoD:

[ ] Use this ticket as an epic to clean up tech debt
[ ] Create new tickets for each resolution
[ ] Please update items from the list above as they complete

oceanprotocol / pdr-backend