A Stock Ranking Is a Database Transaction

Dec 19, 2019

December 19, 2019

Every December, screens that were stable for eleven months suddenly disagree. The query did not change. The input tables did.

A ranking can be wrong when every row is correct. During a refresh, one company may have today’s price and yesterday’s fundamentals, another may have the new filing and the old share count, and a third may still belong to the previous universe. Sorting those rows produces a valid order over a state that never existed.

Non-atomic factor refresh

Six correct rows, one impossible ranking

FRACTURED SNAPSHOT
t + 248 ms

Each line is the observed rank while one generation is replaced by the next. The endpoints are valid snapshots. Every intermediate ordering combines rows that were never simultaneously current.

The chart shows a single refresh. The first and last columns are coherent snapshots. The middle is a sequence of fractured reads. Every intermediate rank is reproducible from the rows returned by the system, but none is reproducible from a legitimate market state.

This distinction matters in a stock screener because ranking is cross-sectional. A ratio belongs to one security. A rank belongs to the security, the comparison universe, the observation cutoff, the normalization procedure, and every other row participating in the calculation.

The transaction boundary is wider than the query

For security $i$, define a factor value at snapshot $s$:

F_i(s) = f(P_i(s), X_i(s), C_i(s))

where $P$ is market data, $X$ is fundamental data, and $C$ is classification and corporate-action state.

The rank is:

R_i(s) = \operatorname{rank}\left(F_i(s), \{F_j(s): j \in U(s)\}\right)

The universe $U(s)$ is part of the input. So are the winsorization limits, sector medians, missing-value rules, currency conversion rates, and tie-breaking policy.

A weak implementation computes:

\widetilde{R}_i = \operatorname{rank}\left(F_i(s_i), \{F_j(s_j)\}\right)

with a different $s_j$ for each row. The database returns a total order. The order has no financial interpretation.

This is read skew. In distributed systems literature, a related form is called a fractured read. Values committed together are observed from different generations.

A conventional stock screener architecture tends to create this failure by accident:

prices update continuously
fundamentals update when parsers finish
security-master changes arrive from a separate feed
derived ratios recompute row by row
the ranking endpoint reads whatever is currently present

Each service is locally correct. The ranking endpoint is globally inconsistent.

Snapshot isolation can preserve a bad snapshot

Opening a repeatable-read transaction does not solve the problem when the tables were populated by independent commits.

Suppose 3,000 factor rows are replaced in batches of 100. A transaction opened after batch 17 sees the first 1,700 rows from generation 42 and the remaining 1,300 rows from generation 41. Snapshot isolation freezes that mixture perfectly.

The reader needs an application-level generation, not merely a database transaction ID.

The minimum useful snapshot key for a fundamental stock screener usually contains:

the market-data cutoff
the information-availability cutoff
the universe version
the security-master version
the corporate-action version
the currency-conversion version
the factor-definition version

The information-availability cutoff is separate from the economic period. A December quarter can be valid for December and unavailable until February. Point-in-time financial data must preserve both facts. The same boundary prevents look-ahead bias when production snapshots are reused in research.

MVCC can provide a coherent database read. The application still needs a generation that spans every store and every stage of the financial data pipeline.

The key should be explicit and immutable.

from dataclasses import dataclass
from datetime import date, datetime
from typing import Iterable, List


@dataclass(frozen=True)
class Snapshot:
    market_close: date
    known_at: datetime
    universe_version: str
    security_master_version: str
    corporate_actions_version: str
    fx_version: str
    formula_version: str


@dataclass(frozen=True)
class ScoreRow:
    security_id: int
    snapshot: Snapshot
    score_micros: int


class FracturedRead(RuntimeError):
    pass


def rank_rows(rows: Iterable[ScoreRow]) -> List[ScoreRow]:
    materialized = list(rows)
    snapshots = {row.snapshot for row in materialized}

    if len(snapshots) != 1:
        raise FracturedRead(
            "ranking input spans {} snapshots".format(len(snapshots))
        )

    return sorted(
        materialized,
        key=lambda row: (-row.score_micros, row.security_id),
    )

The permanent security identifier is the final sort key. Ties need an explicit order even when the product does not display one. Without it, pagination, caches, tests, and exports can disagree while showing identical scores.

Ticker is a presentation attribute. It is unsuitable as a tie-breaker because it can change, be reused, or vary by listing venue.

Cross-sectional transforms are batch operations

Many factor pipelines treat normalization as a scalar function:

pipeline = (
    "raw value",
    "winsorized value",
    "z-score",
    "weighted score",
)

The arrows hide global dependencies.

A winsorized value depends on cross-sectional quantiles. A z-score depends on the mean and dispersion of the comparison set. A sector-neutral value depends on classification state and every peer in the sector. A percentile depends on the cardinality of the eligible universe.

Changing one row can change every output row.

That makes a cross-sectional factor ranking closer to a database transaction than a map operation. The unit of recomputation is the snapshot.

For deterministic ranking, keep the irreversible parts of the calculation in integer space. Floating-point arithmetic is appropriate for research. Published ranks need a canonical representation.

The following function accepts a factor already scaled to signed 64-bit integers. Missing observations use the smallest representable integer. Ties receive an exact midrank in parts per million. The security identifier stabilizes the internal ordering without changing the midrank.

from typing import Tuple

import numpy as np


MISSING = np.iinfo(np.int64).min
PPM = 1_000_000


def midrank_ppm(
    values: np.ndarray,
    security_ids: np.ndarray,
) -> Tuple[np.ndarray, np.ndarray]:
    values = np.asarray(values, dtype=np.int64)
    security_ids = np.asarray(security_ids, dtype=np.uint64)

    if values.ndim != 1 or security_ids.ndim != 1:
        raise ValueError("inputs must be one-dimensional")
    if values.shape != security_ids.shape:
        raise ValueError("inputs must have equal length")
    if np.unique(security_ids).size != security_ids.size:
        raise ValueError("security_ids must be unique")

    valid = values != MISSING
    valid_index = np.flatnonzero(valid)
    output = np.full(values.size, MISSING, dtype=np.int64)

    if valid_index.size == 0:
        return output, valid
    if valid_index.size == 1:
        output[valid_index[0]] = PPM // 2
        return output, valid

    order = valid_index[
        np.lexsort(
            (
                security_ids[valid_index],
                values[valid_index],
            )
        )
    ]
    ordered_values = values[order]
    count = order.size

    start = 0
    while start < count:
        stop = start + 1
        while stop < count and ordered_values[stop] == ordered_values[start]:
            stop += 1

        # Zero-based midrank:
        # ((start + stop - 1) / 2) / (count - 1)
        numerator = (start + stop - 1) * (PPM // 2)
        percentile = (numerator + (count - 1) // 2) // (count - 1)
        output[order[start:stop]] = percentile
        start = stop

    return output, valid

Apply factor direction before this transform. A high-is-good factor can enter unchanged; a low-is-good factor can enter with its sign reversed. Keep the direction in the formula version so a semantic change cannot reuse an old cache entry.

This representation has useful properties:

equal raw values always receive equal percentile scores
the result is independent of thread scheduling
serialization is exact
weighted composites can remain in signed 64-bit integer arithmetic
cache equality does not depend on a floating-point tolerance

A composite score can be formed from integer percentile factors and integer weights. Check overflow before multiplication. With percentile values bounded by one million and weights expressed in basis points, a 64-bit accumulator leaves ample room for a practical factor set.

The numeric representation still needs semantic metadata. A value of 731442 has no meaning without the factor direction, universe, missing-value rule, and snapshot key. Missing factors should remain missing through the rank transform; coverage policy belongs in the composite formula.

The same deterministic core can serve quantitative finance Python research and the production ranking service. Research convenience should not create a second scoring definition.

Build, seal, then publish

A robust refresh has three phases.

Build. Write every derived row under a new generation identifier. Readers cannot discover the generation through the production pointer.

Seal. Validate row counts, universe membership, duplicate identifiers, factor coverage, extrema, and checksums. Compute ranks only after the generation is complete.

Publish. Move one pointer from the previous sealed generation to the new sealed generation.

The pointer update is the transaction boundary visible to readers.

For a single filesystem, an atomic rename is sufficient. The following publisher writes immutable manifests and updates CURRENT with os.replace. The directory sync closes a failure window on filesystems that acknowledge the rename before persisting the directory entry.

import hashlib
import json
import os
import tempfile
from pathlib import Path
from typing import Any, Dict


def canonical_json(value: Dict[str, Any]) -> bytes:
    return json.dumps(
        value,
        sort_keys=True,
        separators=(",", ":"),
        ensure_ascii=True,
    ).encode("ascii")


def durable_replace(path: Path, payload: bytes) -> None:
    path.parent.mkdir(parents=True, exist_ok=True)
    descriptor, temporary_name = tempfile.mkstemp(
        prefix=".{}.".format(path.name),
        dir=str(path.parent),
    )
    temporary = Path(temporary_name)

    try:
        with os.fdopen(descriptor, "wb") as handle:
            handle.write(payload)
            handle.flush()
            os.fsync(handle.fileno())

        os.replace(str(temporary), str(path))

        if hasattr(os, "O_DIRECTORY"):
            directory_fd = os.open(str(path.parent), os.O_DIRECTORY)
            try:
                os.fsync(directory_fd)
            finally:
                os.close(directory_fd)
    finally:
        if temporary.exists():
            temporary.unlink()


def publish_snapshot(root: Path, manifest: Dict[str, Any]) -> str:
    manifest_bytes = canonical_json(manifest)
    snapshot_id = hashlib.blake2b(
        manifest_bytes,
        digest_size=16,
    ).hexdigest()

    immutable_path = root / "manifests" / (
        "{}.json".format(snapshot_id)
    )
    if not immutable_path.exists():
        durable_replace(immutable_path, manifest_bytes)

    durable_replace(
        root / "CURRENT",
        (snapshot_id + "\n").encode("ascii"),
    )
    return snapshot_id

An object store generally lacks atomic rename. Put the immutable data in the object store and keep the current snapshot pointer in a transactional database row. Update it with compare-and-swap semantics. A reader should resolve the pointer once per request and pass the resulting snapshot identifier through every downstream call.

Do not resolve CURRENT separately for prices, factors, and rankings. That recreates the fractured read at the service boundary.

Cache keys need the generation

A ranking response is a function of more than the screen expression.

A safe cache key resembles:

cache_key = (
    snapshot_id,
    universe_id,
    formula_version,
    filter_hash,
    sort_spec,
    page,
)

A cache key based only on the URL can serve a ranking from generation 41 beside company detail pages from generation 42. Purging the cache during publication narrows the window but cannot make a multi-node purge atomic.

Immutable snapshot keys remove the purge from the correctness path. Old entries expire naturally. New requests use a new key immediately after the pointer changes.

The same rule applies to downloadable CSV files, background alerts, portfolio exports, and saved screens. Persist the snapshot identifier with the result. “Run again” can use the current snapshot. “Show me what I saw” must use the original one.

Universe changes are data changes

A stock ranking algorithm often treats the universe as a filter applied after scores are computed. That is safe only for raw scalar factors.

Cross-sectional transforms depend on membership. Remove a company and every percentile can move. Reclassify a company and every sector-neutral score in two sectors can move. Add a newly public company and quantile boundaries can move without any incumbent filing a new statement.

The universe version therefore belongs upstream of normalization.

This creates a practical ordering constraint:

freeze security-master and classification state
derive eligible universe membership
freeze point-in-time market and fundamental inputs
compute cross-sectional transforms
compute composite scores
assign deterministic ranks
seal and publish the generation

Reversing steps two and four produces a score whose stated universe does not match the population used to normalize it.

The ranking API should expose its evidence

A reproducible stock screener should return enough metadata to identify the calculation:

response_metadata = {
    "snapshot_id": "4d9d6dc77f9e8a22c5c3f9fe65799b71",
    "market_close": "2019-12-18",
    "known_at": "2019-12-19T06:00:00Z",
    "universe_version": "us-common-2019-12-19",
    "formula_version": "quality-value-v7",
    "row_count": 2874,
}

This is evidence, not decoration. It lets support reproduce a customer result, lets research compare production generations, and lets monitoring distinguish a legitimate rebalance from a data defect.

Expose freshness by domain. One timestamp labeled “data updated” conceals too much. Prices, filings, foreign exchange rates, classifications, and corporate actions can have different cutoffs while still belonging to one sealed snapshot.

Tests should attack the publication boundary

Unit tests for ratio formulas will not detect a fractured ranking. The failure appears between components.

The highest-value tests construct incomplete generations deliberately:

publish half the factor rows and verify that no reader can resolve the generation
change the universe without recomputing percentiles and require the seal to fail
duplicate a permanent security identifier and require the build to fail
reorder equal-score rows and verify byte-identical output
interrupt the publisher between manifest write and pointer update
keep an old worker alive during publication and verify that one request uses one snapshot
recompute the same sealed generation on another machine and compare checksums
load a saved screen against its original snapshot after a newer generation is live

A useful invariant is stronger than “all rows have a snapshot ID”:

\left|\{\text{snapshot\_id}(r): r \in \text{response}\}\right| = 1

Apply it at the API boundary, not only inside the ranking job.

Year-end is when weak consistency becomes visible

Year-end screens combine several stressors: index reconstitutions, tax-driven price moves, thin staffing, delayed international closes, annual model revisions, and a larger-than-usual volume of saved-screen comparisons.

The resulting rank changes look plausible. That is why the defect survives.

A company moving from rank 42 to rank 57 rarely triggers an exception. A customer sees a different result on refresh, an alert fires without a durable cause, or a backtest cannot reproduce a production screen. The system has emitted an answer without retaining the state that defined it.

The engineering rule is strict:

Never publish a rank until every input row, universe member, transform, and formula belongs to one sealed generation.

A stock screener is a database problem. A stock ranking is the transaction.