Fourth in a series on building Metis, a trading engine asking: can understanding how energy flows let us see what moves natural gas prices before the market does? Previous posts covered SIMD performance debugging and why my data sources don’t share a clock.
In my last post, I started off by discussing this point: every discussion of alternative data eventually arrives at the same conclusion, buy a vendor feed. Clean, documented, supported. Someone else has already solved the ingestion problem, the schema problem, the reliability problem. You pay for the abstraction and you get to think about signals instead of pipelines.
The other path is to build it yourself, source by source, directly from the origin.
The standard argument for the first path is time. The standard argument for the second is cost. Both are true and both miss the point.
The real argument for building your own is this: when you buy a clean feed, you are also buying someone else’s decisions about what to record, how to record it, and what to leave out. Those decisions are invisible in the data. They’re not documented in the schema. You inherit them silently, and they become assumptions in every model downstream.
Building your own does not eliminate those decisions. But it forces you to make them consciously.
Before writing a single ingester, I had to question not just “how do I get this data” but “who recorded this, and why?”
That sounds philosophical. It has practical consequences.
Congressional bill data is where this is most visible. Congress.gov has a public API. It returns bill text, sponsor, co-sponsors, committee assignments, floor votes, and action dates. It’s well-documented and mostly reliable.
What it doesn’t return is anything that happened in a closed-door meeting. It doesn’t record why a senator who opposed a bill in committee suddenly voted for it on the floor. It doesn’t capture the conversation that happened before the vote. The API is a faithful record of the official process. The official process is not the same as the actual process.
This matters for signal construction. If you’re tracking Congressional activity as a leading indicator of energy policy change, you’re tracking the public record of a process that has a significant private component. The data tells you what was formally decided. It doesn’t tell you when the decision was actually made, or why it moved the way it did.
This isn’t a criticism of the data — it’s an accurate description of what it measures. The gap between the recorded vote and the actual decision is itself information, even if you can’t observe it directly. Building the pipeline yourself forces you to sit with that gap rather than trusting that a vendor has somehow resolved it.
CAISO electricity price data presents a different flavor of the same problem. The data itself is reliable — real-time LMP prices are published on a fixed schedule and the source is authoritative. The problem is encoding. CAISO occasionally publishes data with non-standard SQL-incompatible characters in field values — encoding artifacts that cause ingestion to fail silently or loudly depending on where in the pipeline the error surfaces. A vendor feed would handle this transparently. Building it yourself means you encounter it directly, which means you know it exists and you know what your handling of it is.
Silent failure is worse than loud failure. At least a loud failure tells you something went wrong.
The first architectural decision the pipeline makes isn’t about reliability or encoding. It’s about frequency.
The intuitive approach is to ingest everything as often as possible. More fresh data is better. Run everything daily.
This is wrong in two ways. It’s computationally wasteful — some sources publish weekly or monthly by definition, and re-fetching them daily produces identical results with wasted API calls. More importantly, it creates a false sense of resolution. Drought conditions don’t change daily. Building permits don’t change daily. Congressional action doesn’t change daily. Ingesting them daily doesn’t give you daily information — it gives you daily confirmation that nothing changed, dressed up as data.
The Metis pipeline uses three ingestion strategies, and the choice of strategy per source is a modeling decision:
Daily sources are those where the phenomenon genuinely changes at daily or sub-daily resolution: EIA natural gas storage, CAISO grid LMP prices, FRED macroeconomic indicators, weather, and maritime AIS vessel tracking. These run every day because the signal they carry can change every day.
Weekly sources run on Mondays: CME futures contracts, the US Drought Monitor, and EIA jet fuel consumption. The Drought Monitor publishes every Tuesday morning — running the ingester on Monday means the data is always fresh within a week rather than potentially stale for six days.
Monthly sources run on the first of each month: BLS Producer Price Index, FRED building permits, and Congressional bills. These sources update at monthly cadence by design. Fetching them more often produces noise, not signal.
The implementation isn’t a cron job with hardcoded intervals, but a strategy router. Each source has a configured mode — backfill, sliding_window, or incremental — and the router calculates the correct date range to fetch based on what’s already in the database. A source in backfill mode progressively extends its historical window one year at a time until it reaches the target depth, then automatically switches to incremental. The pipeline knows its own state.
This matters because historical ingestion and live ingestion are fundamentally different tasks. Historical data is a one-time event — you fetch it once, it doesn’t change, and you don’t need to fetch it again. Live ingestion is a maintenance task — you fetch only what’s new, with a small overlap window to catch corrections. Treating them as the same operation either wastes resources or misses corrections. Separating them is the right model.
The pipeline runs at laptop startup. This means the first thing it encounters, before any data source, is whatever network is available.
When wifi hasn’t initialized yet, some ingesters fail immediately on connection. When traveling with inconsistent connectivity, a source that normally succeeds might fail three times before succeeding on the fourth attempt. When a source undergoes scheduled maintenance, it’s down for a window and then back.
These aren’t edge cases. They’re the normal operating conditions of a system that runs on real hardware in a real environment.
The failure handling has three layers.
Retry with exponential backoff handles transient failures — the connection that wasn’t ready yet, the API that returned a 503. It then triggers a series of retries, each with a longer wait time than the last. If all attempts fail, the error is logged and the pipeline moves on to the next source. One source failing doesn’t block the others.
Backup table restoration handles the case where a source fails repeatedly over multiple runs. Before each ingestion run, the previous successful data is snapshotted to a backup table. If a live ingestion fails and the backup exists, the pipeline can restore from backup and mark the data as stale rather than missing. Stale data is better than no data for most downstream uses, as long as the staleness is known and propagated.
Data validation runs after every successful ingestion. Schema checks, null checks, duplicate key detection, chronological ordering verification. The CAISO encoding issue surfaces here — malformed data fails validation before it reaches the database, which means the failure is explicit rather than a silent corruption downstream.
The validation philosophy is that silent failure is the worst outcome. A system that fails loudly tells you something is wrong. A system that ingests malformed data silently tells you nothing, while producing wrong answers confidently.
In January, the full Metis project was a few gigabytes. By April it was over ten.
The codebase didn’t grow by ten gigabytes. The data did.
This is the unglamorous reality of building a live data system: data accumulates. Daily ingestion of eleven sources, some sub-hourly, compounds. The local SQLite database that was perfectly adequate in month one becomes unwieldy by month four — not in terms of query performance, but in terms of backup, portability, and the basic anxiety of having your entire dataset on one machine with no redundancy.
The solution was Cloudflare R2, used not as a replacement for the local database but as a durable second copy. After every successful ingestion run, the pipeline uploads the database to R2. The local copy is the working copy — all reads and writes happen there. The R2 copy is the backup — it exists so that a hard drive failure, or a corrupted database, or a machine that gets lost, doesn’t mean losing months of carefully ingested data.
This created a useful architectural clarity: the local database is the operational layer, R2 is the persistence layer, and the two roles stay separated. The ingestion pipeline writes locally and then backs up. It never reads from R2. The backup is append-only from the pipeline’s perspective.
The upsert pattern handles the overlap between historical and incremental data cleanly. Every ingester uses insert-or-replace semantics — if a record with the same key already exists, the new version wins. This means re-running any ingester is always safe. There’s no risk of duplicate rows from re-fetching overlapping windows. The database stays consistent regardless of how many times the same data is ingested.
The hardest part of building the data pipeline was not the engineering. It’s maintaining clarity about what the data actually represents versus what it appears to represent.
Congressional floor votes tell you what was formally decided. They don’t tell you why votes changed, what was negotiated in private, or which conversations happened before the session. CAISO prices are authoritative but occasionally arrive malformed. Drought severity is measured by the US Drought Monitor using a specific methodology that carries its own assumptions about what “severe” means in different agricultural and hydrological contexts. AIS vessel data tells you where vessels are, not why they’re there or where they’re going.
None of this makes the data useless. It makes the data limited in specific, knowable ways.
Building the pipeline yourself doesn’t give you better data than a vendor would. It gives you a more honest relationship with the data’s limitations, because you’ve had to make every decision yourself rather than inheriting them invisibly. You know which sources go down. You know which encoding issues surface. You know which official records have a private process behind them that doesn’t appear in the API.
That’s not a small thing. The assumptions that hurt you are always the ones you didn’t know you were making.
The pipeline is the input layer. What happens after ingestion — how eleven sources with different frequencies, schemas, and confidence levels get combined into a coherent signal — is the next problem.
The signal fusion layer is where the temporal alignment decisions from the last post actually execute. Weekly drought data has to coexist with sub-hourly LMP prices in the same fusion window. Congressional bills with multi-date lifecycles have to be represented as signals with appropriate lag and decay. AIS port state snapshots have to be weighted relative to sources with tighter measurement intervals.
That’s the next post.