[ETL] ETL & Analytics Backlog

[idiom-bytes]

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.

• Use revenue + roundStakes from trueval and save it to slots table... #1183
• Improve fetching and ETL by just grabbing the appropriate data from subgraph #989
• Stop duplicating pair, timeframe, source and get it from from contract address
• 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
• [Lake] Data Store Objects - Rename functions to use sql nomenclature: fill becomes insert, override becomes upsert #1343
• 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. [ETL] Improve CSVDataStore.read() #1333

Low Priority Improvements:

• There are various fetches/queries that aren't optimal. [Lake][Subgraph] Improve query structure (and maybe performance) #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