← All work

Case study 02

Market Sentiment Pipeline

Running daily 2026

A containerized ELT pipeline that turns unstructured financial news into a quantified signal for S&P 500 equities — and then grades its own predictions on a dashboard so the model can't quietly get worse.

The problem I was actually solving

Price data is structured and easy. News is neither, and it's where the information actually lives. I wanted a system that reads the day's news for a few hundred tickers, turns it into something a model can consume, and produces a signal on a fixed schedule without me touching it.

The hard part in a daily pipeline is never the model. It's what happens the third time a task reruns.

The constraint everything else follows from

A pipeline that runs every day will fail, get retried, and get backfilled. If a rerun can double-count a row, every downstream number is quietly wrong and nothing alerts you. So loads had to be idempotent before anything else was worth building.

Architecture

Market sentiment pipeline architecture Market data and news feeds are extracted by a daily containerized Airflow DAG, merged idempotently into BigQuery raw tables, scored for sentiment by Gemini, turned into features for a Random Forest, and surfaced with tracked accuracy in Looker Studio. Sources Market data daily OHLCV News feed unstructured text Airflow DAG — daily · containerized with Docker Extract pull + normalize Load MERGE, not INSERT Score Gemini → sentiment Predict Random Forest keyed on (ticker, date) rerun-safe by construction BigQuery — serverless warehouse raw_prices · raw_news features · sentiment predictions · outcomes Looker Studio signals + accuracy measured outcomes feed back into evaluation
Watching one daily run…
The loop is the point: predictions are written next to the outcomes they're later graded against, so the dashboard shows whether the model is still earning its place. Re-run it — nothing downstream changes, which is the whole argument below.

Why the merge matters — try it

This is the least glamorous decision in the project and the one everything else depends on. Both tables below receive the same day's data. One appends, one merges on (ticker, date). Press Run the same day again — which is what a retry, a backfill or a scheduler hiccup does — and watch them stop agreeing.

Same input, twice

INSERT — append

what most pipelines do

    MERGE — upsert on (ticker, date)

    what this one does

      append rows: 0 merge rows: 0 phantom rows: 0

      Decisions and what they cost

      DecisionWhyWhat it costs
      MERGE on a natural key, not append Makes every task rerunnable and every backfill safe. A retry writes the same result as the first run. More expensive per load than a bare append, and it requires an honest natural key — which forces you to actually understand the grain of your data.
      ELT, not ETL Land raw text first, transform in the warehouse. When the sentiment prompt changes, I can reprocess history instead of re-fetching it from a rate-limited source. Storage cost for raw data I may never query, and transformation logic lives in SQL where it's harder to unit test.
      LLM for sentiment instead of a classical classifier Financial news is full of negation, hedging and sarcasm. A bag-of-words model reads "beats lowered expectations" as good news. Non-deterministic scoring, a per-row API cost, and an external dependency in the critical path. Scores are cached so a rerun doesn't re-bill.
      Random Forest over a neural model Small tabular dataset, and I wanted feature importances I could actually read and argue with. Almost certainly leaves accuracy on the table versus a gradient-boosted or sequence model.
      Airflow for something one cron could trigger The value isn't scheduling — it's per-task retries, dependency ordering, and being able to see which step failed at 4am without reading logs by hand. Real operational weight for a personal project. A cron plus a shell script would have shipped in a day.
      Where it stands, honestly
      • The pipeline runs daily end to end and the dashboard is live.
      • This is not a trading strategy and I don't present it as one. It is a data engineering system that happens to produce a signal. I have not run it against transaction costs, slippage or a proper walk-forward backtest, so I make no claim about profitability.
      • The model is retrained on a fixed cadence rather than triggered by measured drift.
      • There is no alerting on task failure yet beyond Airflow's own UI — the next thing I'd add.

      What I'd tell you in an interview

      Ask me about the idempotency work. It's the least glamorous part and the part that makes the difference between a pipeline you can trust and a demo. I'd also rather talk about why the predictions table sits next to an outcomes table than about the model itself — a system that can't tell you when it's wrong isn't finished.

      Stack

      • Python
      • Apache Airflow
      • Docker
      • Google BigQuery
      • Gemini
      • scikit-learn
      • Looker Studio
      • GCP
      ← Previous Next case study →