This repository was archived by the owner on Oct 8, 2026. It is now read-only.
Repository navigation
This repository was archived by the owner on Oct 8, 2026. It is now read-only.
Futures daily bars: restart the feed and make futures_data.ohlcv_1d trustworthy #105
Description
This is direction and a starting design from the trading-engine side, not a finished spec.
I am handing over the repair of the futures daily bars: restarting the Databento feed, de-duplicating and keying futures_data.ohlcv_1d, re-fetching the bad bars, and one metadata row. This is part of the parent issue #104, which holds the full document; section and evidence names below (E1 to E8) point into it. Where I have ruled, the ruling is below with its date; where a decision is still mine, the item says that it waits for my decision. The designs and queries here are where to start: question them and change them with evidence.
Related: #59 (the freshness check has said "futures stale" every day since 2026-08-09; this issue is the human-owned fix). #106 (per-contract bars) is the new data that follows; #110 (pipeline health) fixes the silent-failure path that let this run unseen.
Task list
- 16 Answer what re-ran the February load, which config and DAG are deployed, and whether backups exist
- 1 Restart the futures feed without changing any stored row
- 13 De-duplicate
futures_data.ohlcv_1dand add its primary key, then the targeted upsert - 14 and 20 Re-fetch the wrong-contract and empty bars, after classifying the partial-volume days
- 15 Back-fill the MES gap of 2026-04-13 to 2026-04-17 and check the neighbouring dates
- 21 Correct MBT's trading-hours string and answer the weekend-volume questions
The order is the order of the list: the answer to 16 gates 1 and 13.
1. What exists today
| object | state | evidence |
|---|---|---|
futures_data.ohlcv_1d (.v.0) |
165,037 rows, 36 symbols, 2010-06-07 to 2026-08-06; 970 duplicate rows in 306 symbol-dates; no key; a TimescaleDB 2.17.2 hypertable, 845 seven-day chunks, compression off (production, 2026-09-21) | E1 |
| the feed | last bar 2026-08-06 for 33 symbols, 2026-08-04 for 6J, ZC, ZF; zero inserts on the hypertable since the server restart of 2026-09-04 (production, 2026-09-21; please confirm) | E1 |
futures_data.ohlcv_1d_raw |
164,067 rows, keyed (ts_event, symbol), carries instrument_id; never two contracts on one (symbol, date) |
E1, E4 |
metadata.contract_metadata MBT row |
Trading Hours (EST) reads "Sunday - Friday 6:00 p.m. - 5:00 p.m."; the feed has real Saturday MBT bars from 2026-06-13 |
E6 |
What the code on data-ngin main (998b78f) shows:
- Two futures DAGs.
data_pipeline_dag(07:00,src/config/config.yaml,db_namealgo_data, 29 symbols incontracts/contract_valid.csv) andnew_data_pipeline_dag(07:05,src/config/new_config.yaml,db_namenew_algo_data, 36 symbols incontracts/new_full_contracts.csv). Schedules atdags/data_pipeline_dag.py:55anddags/new_data_pipeline_dag.py:55. - Both configs pin
start_date: "2026-02-03"(src/config/config.yaml:43,src/config/new_config.yaml:43), set on 2026-02-05 (commits dfa58ca, 22a218f). With a start date set,utils/dynamic_loader.py:117-119uses it on every run; only a blank start date takes the incremental path (:120-127, the target table'sMAX(time)plus one day). The duplicated window in production is exactly 2026-02-03 to 2026-02-05. So either production runs an uncommitted edit, or the futures DAG has not been succeeding since the 2026-08-06 pull. The Airflow run history tells which. - No key, untargeted insert. The model declares
(time, symbol)as the primary key (src/modules/db_models.py:30-31); the insert isON CONFLICT DO NOTHINGwith no target (src/modules/inserter/timescaledb_inserter.py:114-118), which only de-duplicates when a unique index exists. The raw table has its key and zero duplicates. - Failures do not fail the run.
src/orchestrator.py:108-109catches every per-symbol exception and logs it, so the task reports success even when every symbol failed (the fix is Pipeline health: no silent failures, a published run status and freshness view, and alerts through Grafana (S1) #110 part (a)). When you read the Airflow history, read the log lines, not the task state. - The cleaner's back-adjustment never fires (
src/modules/cleaner/databento_cleaner.py:221comparessymbol, which is always<root>.v.0). Leave it inert:.v.0must stay the raw traded price. - Backups.
src/backups/pg_backup.shandcrontab.sampleare in the repository;archive_modeis off on the server and there are no replicas (2026-09-21). Whether the script is installed is part of item 16.
What the engine reads: futures_data.ohlcv_1d through execute_market_data_query, get_symbols and get_latest_prices (trade-ngin src/data/postgres_database.cpp:908-986, :601-626, :655-687), so a de-duplication or a key change reaches all three reads. An engine change on an open trade-ngin pull request (AlgoGators/trade-ngin#98) reads one bar per symbol and date (highest volume wins); main still reads every copy. Each copy changes what the engine trades (E1).
2. Rulings that bind this issue
| date | ruling |
|---|---|
| 2026-09-17 | a usable Saturday MBT bar is tradeable under the per-symbol session rule; settlement is not modelled, so item 21 is a metadata fix and a cost-model question only |
| 2026-09-21 | the engine's loader rule (one bar per symbol per date, highest volume wins) is the reference; it is on an open trade-ngin pull request and not yet on main, which still reads every copy; the production copies are still yours to clean, and the rule stays after the clean-up |
| 2026-09-23 | the futures verification runs wait for the feed and for nothing else |
| 2026-09-24 | the engine gets an instrument-id continuity check (it will read ohlcv_1d_raw.instrument_id); the data owners re-fetch the wrong-contract bars |
| 2026-09-25 | .v.0 is Databento's symbol resolver and is standard; nothing in the current ingest is wrong; the adjusted series comes from #106 |
3. The work
| item | the work and its acceptance criteria | tier |
|---|---|---|
| 16 | Answer in writing: which data-ngin revision and config are deployed; whether time_range.start_date is pinned in the deployed config; which of the two futures DAGs writes the database the engine reads, and what the other one writes; what re-ran 2026-02-03 and 2026-02-04 about eight times, and the six October and November symbols (6A, 6L, HO, NG, ZF, ZT); whether any backup or point-in-time recovery exists, and whether pg_backup.sh is installed; whether a restored TimescaleDB copy can be made for a rehearsal. Accepted when each question has an answer, or "unknown" with who would know. |
answer |
| 1 | Restart the Databento futures ingest. Before restarting, make the deployed config's start_date blank (or confirm it is) so the scheduled path starts at MAX(time) plus one day and never revisits a stored date. Accepted when (i) every one of the 36 symbols has bars from 2026-08-07 (2026-08-05 for 6J, ZC, ZF) to the last completed session; (ii) no row dated on or before 2026-08-06 changed: the fingerprint query of E1 restricted to time <= '2026-08-06' returns the same values before and after; (iii) no new duplicate (the duplicate query of E1 over the new range returns 0). What I need back: the fingerprint before and after, the new row count per symbol, the first and last new date, the data-ngin revision that ran, and the cause of the stop (analysis 2). |
tier T1 |
| 13 | De-duplicate and key the table in the order of E1: (i) snapshot; (ii) de-duplicate the two windows keeping the highest-volume copy (for the one disagreeing pair keep 6A 2025-11-05 volume 76,895, close 0.6512); (iii) ADD CONSTRAINT ohlcv_1d_pkey PRIMARY KEY (symbol, "time"); (iv) later, a nullable loaded_at and the targeted upsert ON CONFLICT (symbol, "time") DO UPDATE ... WHERE ... IS DISTINCT FROM ... in timescaledb_inserter.py. Rehearse (ii) and (iii) on a restored TimescaleDB copy first; our scratch copy is a plain table and cannot. Run outside the 07:00 job. Accepted when count(*) = count(distinct (symbol, time)) = 164,067 over the pre-restart range; the 6A check returns 76,895 / 0.6512; the key exists; one manual pipeline run over an already-stored day succeeds and inserts 0 rows. What I need back: the snapshot table names and where the off-server CSV or dump sits, the exact statements, the row counts each reported (expected DELETE 1,276 and INSERT 306 for the preferred form, or DELETE 970 for the ctid form), the before and after totals. Whether this runs before or after that engine change is on main waits for my decision. |
tier T1 (judged on an engine build that contains that engine change, where the result must be byte-identical because the loader already keeps the same bar) |
| 14, 20 | First classify each symbol-date of item 20 and the bad bars of item 14 (analysis 4). Then re-fetch, in both ohlcv_1d and ohlcv_1d_raw: 2025-11-05 on 6B, 6C, 6E, 6J, 6M, GC, HG, SI; GC, ZL and ZS on 2025-09-03; ZM and ZR on 2025-10-10 (raw is already right for these two; ohlcv_1d has zero-volume flat bars); 6A 2025-11-05 (only the raw row is wrong). Store what the vendor returns, with the targeted upsert once the key exists. Accepted when every listed symbol-date has one row whose raw instrument_id matches both neighbouring sessions' contract (or a written reason it cannot), and a volume within the symbol's normal range (or a written reason). What I need back: for each symbol-date the row before and after (all fields and the raw instrument_id), and the statements. |
tier T2 (declared: exactly the symbol-dates listed) |
| 15 | Confirm on production and back-fill MES 2026-04-13 to 2026-04-17; check MES Friday 2026-04-24 and Sunday 2026-04-26, and MBT Sunday 2026-03-15 and Monday 2026-03-16 (E3). Accepted when the E3 query on production shows a bar for every CME session date on both symbols, or a written reason per missing date (a vendor gap is an acceptable answer). What I need back: the query output before and after. | tier T2 (declared: the MES and MBT dates listed) |
| 21 | Correct MBT's Trading Hours (EST) in metadata.contract_metadata (send me the new string before writing it). Answer: does the vendor's Monday MBT bar also count the weekend's trades in its volume; are the Sunday stub gaps (Sunday 2026-05-24 has no bar for any symbol; 2026-05-31 and 2026-06-07 have MBT only) source gaps? Accepted when the row reads the exchange's current hours and the answers are in writing. Never re-seed the table from an export older than the 2026-05-02 multiplier migration. |
tier T1 (no engine code on main calls the futures market-hours check) |
4. Analysis to do first
| # | question | output I expect back |
|---|---|---|
| 1 | What re-ran the load, and is there a backup? Inputs: the deployed config and revision, Airflow's own dag_run and task_instance history (queryable read-only), any manual back-fill script, the pinned start date above. The six October and November symbols are exactly the symbols in futures_data.new_data_*, whose data ends 2025-11-03, the day before the copies drop from three to two: a staging merge overlapping the daily job is the likely cause, not proven. |
a written answer, and whether a restore point exists |
| 2 | Why did the feed stop on 2026-08-06, and 6J, ZC and ZF on 2026-08-04? Was it the 2026-08-06 deploy? | the cause, and whether the same cause stopped the equity re-adjustment and the 127 equity symbols the same week (#107) |
| 3 | How did the good 6A copy get in? The keyed raw table kept the 196-lot copy (instrument 42006572, on no other day) and refused the 76,895-lot copy. Which fetch wrote which; did Databento restate 2025-11-05? | one paragraph; it decides whether "highest volume" is the right rule for any future conflict |
| 4 | Classify the thin bars (items 14 and 20), including the two 2026-07-01 rows of E2 (ZC, ZL: new contracts seen for one day, normal volumes). | a table: symbol, date, stored volume, vendor volume, raw instrument_id, neighbours' instrument_id, class (wrong contract / truncated / genuine thin / contract flip) |
| 5 | Before any delete: take the snapshot and the off-server dump named in item 13 and write down where they are. | the dump location |
The evidence is in #104: E1 (duplicates, fingerprint, the statements in full, the rollback), E2 (single bad bars), E3 (MES gap), E5 (partial-volume days), E6 (MBT).
5. Risks and what must not be touched
- Never overwrite, re-key or "adjust"
ohlcv_1dorohlcv_1d_rawin place to remove roll jumps; the adjusted series is Per-contract futures bars and the roll rule #106's new table. - Keep
ohlcv_1d_raw.instrument_idand its (ts_event, symbol) key exactly as they are. - No DELETE before the snapshot or dump of that row set exists and its location is written down: there is no point-in-time recovery.
- On the hypertable, a ctid-based delete must pair
ctidwithtableoid;CREATE INDEX CONCURRENTLYis not available; the key build locks all 845 chunks in one transaction (about 2,500 of the 6,400 lock slots), so run it when the database is idle. - Deploy the targeted
ON CONFLICT (symbol, "time")only after the key exists, or every insert errors. Manual back-fills with a plain INSERT will fail on overlap once the key exists; that is intended. - If you re-run the de-duplication later, order ties as the engine does (
volume DESC, close, open, high, low) so the two always keep the same bar. - Nothing in
trading.*orbacktest.*is yours to change.
6. How we test
Each item carries one tier (#104 section 6). Tier T1: an engine run on the data before and after your change is byte-identical; for item 1 that means every window ending on or before 2026-08-06. Tier T2: I declare beforehand which symbol-dates may move; every differing cell must fall under the declaration. Answer: a written answer. We run the engine on a scratch clone, BEFORE and AFTER, same engine build, column for column at tolerance 0. For every item send back: the exact statements in order, the row count each reported, the before and after totals and the E1 fingerprint, the snapshot or dump name and location, the time you ran them (UTC), and the data-ngin revision for anything the pipeline did.
Activity
raohemdutt commentedon Oct 6, 2026
Findings from the per-contract fetch of #106 that belong here. Read-only checks against production on 2026-10-06, about 13:30 UTC. Nothing was written.
State of the tables
futures_data.ohlcv_1d_rawfutures_data.ohlcv_1dfutures_data.ohlcv_1d_backup_before_dedupMore rows for items 14 and 20
Joining every
.v.0row through 2026-10-02 to the per-contract bar itsinstrument_idnames gives 213 rows where the stored bar differs from what the vendor returns today, and 16 with no vendor bar. None is on the dates already listed in item 14.The 2020 dates are the ones that matter: 51 stored closes there are not the vendor's close for the named contract. They need the same classification as item 20 (wrong contract, truncated, or a vendor restatement) before any re-fetch.
Item 15
The per-contract MES bars for 2026-04-13 to 2026-04-17 are in the #106 fetch, 3 or 4 contracts on each date.
MES.v.0still has no row on those five dates. The back-fill can come from those bars once the roll choice for each date is agreed, without a new vendor request.Related
The four index series resolve to full-size contracts for most of their history; details are on #106.
raohemdutt commentedon Oct 6, 2026
Status: In progress
Waiting on: Nothing
Next step: Gaby: confirm no new duplicate rows since the restart, then report against the issue's acceptance checks
Notes: Gaby restarted the feed (PRs #115 and #119, merged 5 and 6 October). Duplicate rows went from 970 to 973, so the restart may still be writing duplicates; not yet confirmed. Open point: whether the de-duplication runs before or after AlgoGators/trade-ngin#98 merges.