Market data and a 500-symbol screener

Every answer āagman gives rests on market data: a chart, a screen, a backtest, a live signal. Indian market data arrives from many sources, in different formats, on different schedules, and the free broker API tier that most retail users have includes none of it. So a large part of the platform is the unglamorous work of getting bars in, keeping them consistent and serving them to everything else.

This part covers:

• The sources, and what each feeds
• Ingestion with Prefect: idempotent, replayable, with a checkpoint per symbol
• Storage: ClickHouse for bars, Postgres for metadata, Parquet for the sandbox (and TimescaleDB for the live session)
• The market-data service in Fastify, and how live candles are built from ticks
• The screener: 500+ instruments, batched, with bounded memory, at more than 10× the throughput of a sequential per-symbol loop

Background: what “a bar” needs to mean#

A 15-minute RELIANCE bar should mean the same thing in a chart, a screen, a backtest and a live signal. That’s harder than it sounds. End-of-day files and intraday bars built from ticks disagree in small ways: auction sessions, late trades, corrections. Corporate actions (splits, bonuses) change historical prices. Index membership changes over time.

We settled on three rules:

1. One canonical bar per (venue, symbol, timeframe, timestamp). Every consumer reads the same row.
2. A correction replaces, it doesn’t edit. Reloading a bar writes a new row with a newer ingested_at, and ClickHouse’s merge keeps the latest.
3. Live and historical bars meet at the close. Live bars are provisional until an end-of-day reconciliation finalizes them.

Sources#

The following table lists the sources and what each feeds.

Instruments live in Postgres (instruments and an ingestion_state table) and the service has an instrument sync endpoint. Every symbol in a strategy document resolves against one instrument master, with each broker’s token attached.

Ingestion with Prefect#

All scheduled and backfill work runs as Prefect flows in a Python orchestrator, which calls the ingestion and export tools as separate processes so that service internals can change independently. The deployments are small: a weekday cron for the daily ingest, a weekly universe discovery, and a backfill flow that loops over a date range.

Every ingestion flow follows the same shape:

1. Resolve the universe from the configured indices, a static list, or a discovery snapshot.
2. Fetch from the provider at its rate limit (the configuration sets requests per minute and a delay between requests for each provider).
3. Normalize and validate into the canonical bar shape; the service has a bar validation module and rejects malformed bars.
4. Load idempotently into ClickHouse with an ingested_at version, and record a checkpoint in Postgres: ingestion_state keeps last_success_ts_utc and last_run_ts_utc per provider, symbol and timeframe.
5. Export finalized days to Parquet on S3.

Because loads are idempotent, every flow can be rerun for any date range. Failures are per symbol: a bad symbol is logged and retried independently and doesn’t block the rest of the universe. The backfill flow is the same ingestion with a date range and a per-date loop; the checkpoint table is what makes a long run resumable. The backfill covers the NIFTY 500 for the last 20 years. It started with the NIFTY 50, with checkpoints per symbol, so the output could be checked before the universe widened to the rest.

Every run also keeps the raw provider file, checks each day’s row count against a band, and stamps a data version on the export, so a backtest can name the exact data it ran on.

Corporate actions#

Splits and bonuses change price history. A 1:1 bonus halves the price; without adjustment, every long-horizon indicator sees a 50% crash that never happened. A factor table, built from the announcements pipeline (Part 6), sits next to the raw prices, so audits and adjusted series are both possible. Adjusted series are materialized from the raw bars and the factors.

Storage: one store per access pattern#

Part 1 introduced the split. Each store has one job.

ClickHouse for bars#

All bars live in one ClickHouse bars table, deduplicated by version:

CREATE TABLE IF NOT EXISTS bars (
  venue String, asset_class String, timeframe String, feed_type String,
  symbol String,
  ts_utc DateTime64(3, 'UTC'),
  open Float64, high Float64, low Float64, close Float64,
  volume Float64,
  source String,
  ingested_at DateTime64(3, 'UTC')
)
ENGINE = ReplacingMergeTree(ingested_at)
ORDER BY (venue, symbol, timeframe, ts_utc);

ReplacingMergeTree with ingested_at as the version column makes loads idempotent: reloading a day writes new rows that replace the old ones at merge time. Readers that need the guaranteed latest version use FINAL or argMax. There is also an hourly rollup view. ClickHouse holds backtest trades, equity curves and screener results as well.

TimescaleDB for the live session#

A Timescale hypertable holds 1-minute bars, with continuous aggregates to 5-minute, 15-minute and hourly bars, because live candles are written every minute and read as “the last N bars”. One detail is worth keeping whether or not you use Timescale: NSE’s session opens at 09:15 IST (03:45 UTC), so 15-minute and hourly buckets must align to 09:15, not to midnight, or you get a 09:00 to 09:15 bar that doesn’t exist. After the close, finalized bars move to ClickHouse.

Parquet for the sandbox#

The backtester runs in a sandbox with no network (Part 4), so it can’t query ClickHouse. A flow exports finalized bars to Parquet on S3, partitioned as venue=X/asset=Y/tf=Z/date=D/symbol=S.parquet, and the worker downloads the files it needs (with an LRU disk cache) and uploads them into the sandbox before the run.

The market-data service#

Reads from the agent tools go through one TypeScript service built on Fastify. It exposes bars and instruments from ClickHouse and Postgres, and runs ingestion and Parquet export commands.

Why Fastify and TypeScript for a data service? Its consumers were TypeScript (the agent service and the app), and we wanted request and response schemas shared with them. The service uses Zod contracts for the canonical market data schemas.

Live candles from ticks#

The live path takes broker ticks and produces 1-minute bars: a market-data aggregator keeps WebSocket connections to broker and provider feeds, de-duplicates across feeds, synthesizes 1-, 5- and 15-minute candles in memory, and publishes each completed candle to a Redis channel that strategy evaluators subscribe to. Three decisions are worth keeping:

• Bars close on exchange time, not wall-clock time. A slow network can delay a bar; it can’t put a late trade into the wrong one.
• Evaluators subscribe to bar closes, not ticks. A live strategy evaluates at bar close and acts at the next bar, the same rule as the backtester, so paper and backtest results can be compared fairly.
• End-of-day reconciliation wins. After the close, the exchange file replaces the day’s provisional bars.

The screener#

“NIFTY 500 stocks with RSI(14) below 30, volume above 1.5× the 20-day average, and price above the 200-day SMA” is the most common research request. The first implementation looped over symbols: load a symbol’s bars, compute the indicators, evaluate the rule, next symbol. It was correct and slow, and its cost was dominated by per-symbol overhead, not by arithmetic.

The screener runs the same compiled plan as a backtest (Part 2), with intent: SCREENER, through a screener adapter. The plan for a screener has no risk or sizing, which the plan validator enforces.

How it works#

The adapter processes the universe in batches:

1. Partition the universe into batches. The batch size is a field on the plan, with an environment default; when neither is set, the universe is processed sequentially.
2. Load each batch’s bars together, as one wide frame: one column per symbol for each price field.
3. Compute indicators on the wide frame, so a rolling calculation covers a whole batch in one vectorized call instead of one call per symbol. There’s a test for batch SMA on wide data.
4. Evaluate the rules at the evaluation timestamp (the last bar by default, or a given date) and collect matches with an indicator snapshot for each symbol.
5. Release the batch’s memory before the next one, and stream results to the writer.

The adapter’s own description of its evaluation is “per-symbol evaluation to keep memory bounded”: the rule evaluation stays per symbol, and the savings come from loading and computing indicators in batches. Callers can also pass safety limits (a maximum number of symbols, bars per symbol, or memory in megabytes), so a very large universe or an unexpectedly long history fails safely instead of taking a worker down.

def screen(plan, symbols, batch_size):
    for batch in batches(symbols, batch_size):
        wide = load_ohlcv_batch_wide(plan=plan, symbol_batch=batch)   # one frame per batch
        indicators = compute_indicators(plan.indicators, wide)         # vectorized
        for symbol in batch:
            yield evaluate(plan.rules, symbol, indicators)             # per symbol, bounded
        release(wide, indicators)

On 500+ instruments, the batched, vectorized version ran at more than 10× the throughput of the sequential per-symbol loop it replaced. The memory bound comes from batching and the limits above.

One detail made the batched version correct, not only fast: the last closed bar. During market hours, a screen should evaluate the last closed bar for the timeframe, never a forming one, so two users asking the same question in the same window get the same answer.

What I’d do differently#

Materialize adjusted series from day one. We added the adjustment-factor table after the first long-horizon backtests showed bonus-driven phantom drawdowns. Adjusting on read puts corporate-action logic in the hot path of every query, so I’d materialize adjusted series nightly from the first release and keep raw for audit.

Treat the quality gate as part of the schema. Validation lives in the ingestion code, next to the parsing. I’d define the rules once, beside the table schema, so every new source inherits them on day one instead of whenever someone remembers to copy them.

Conclusion#

The data platform is mostly decisions about time: when a bar closes, which store owns it, when it becomes final, and how a correction propagates. Versioned upserts, per-symbol checkpoints and a Parquet export the sandbox can trust answer the daily half of that. Provisional live bars and the end-of-day reconciliation answer the live half. The next part covers a different kind of data, unstructured and messy: NSE corporate announcements.

I built āagman as Founding Software Engineer at KotiLabs, July 2025 to April 2026.