Per-contract daily option-flow rows, split by participant class (institutional _i, retail _r, market-maker _m). One row per unique option contract that traded on the session, uniquely identified by (date, figi, symbol, callput, strikeprice, expirationdate).

• Grain: (date, figi, symbol, callput, strikeprice, expirationdate)
• History: 2020-01-02 →
• Timing: T+1 (published between 05:00 and 09:00 UTC on the calendar day after the trading date — Friday's data lands Saturday)

*_qty fields are raw traded contract counts (integer). *_premium fields are dollar premium (qty × traded price × adj_shares_deliverable). *_delta / *_gamma / *_vega fields are the traded contracts as greek-weighted USD notional: dollar notional multiplied by the respective per-contract greek, then scaled by adj_shares_deliverable. Delta-weighted notionals are naturally signed by option type (calls +, puts −); net_* = buy_* − sell_* and inherits the sign. The _i + _r + _m ≈ 0 accounting identity holds row-by-row on the notional columns (up to rounding). floor_* is exchange-floor / broker-committed volume, held in its own columns because Masscrest does not track these trades and their side (buy vs sell) is not available. Additional per-contract context columns carry the last-observed underlying spot, forward, IV and greeks for the session (useful as an end-of-day mark).

Fields

Join hints

For per-underlying daily aggregates (rolled up across every contract on the same (date, figi)), use underlying_option_flows_01_day. For underlying prices and turnover, join to px_01_day on (date, figi) and self-adjust using split_factors. For contract-level metadata (deliverable per contract, corporate-action epochs) the values in this table are already point-in-time; no secondary join is needed.

Gross-volume accounting

Each trade has a customer side (buy_{r,i}_qty / sell_{r,i}_qty) and a market-maker counterparty (buy_m_qty / sell_m_qty, rebuilt to absorb the customer imbalance so the row-level _i + _r + _m ≈ 0 identity holds on notionals).

DO: aggregate on qty — one entry per trade.

DON'T: sum the cohort legs (buy_r_qty + sell_r_qty + buy_i_qty + sell_i_qty + buy_m_qty + sell_m_qty + floor_qty) — inflates volume by ~30-35% because the market-maker leg gets added on top of the customer legs.

10-minute-bucketed sibling of option_flows_01_day. Per-contract option-flow rows at a 10-minute cadence: one row per unique option contract that traded within the bucket, keyed on (date, ten_min_timeframe, figi, symbol, callput, strikeprice, expirationdate).

• Grain: (date, ten_min_timeframe, figi, symbol, callput, strikeprice, expirationdate)
• History: 2020-01-02 →
• Timing: T+1 (published between 05:00 and 09:00 UTC on the calendar day after the trading date — Friday's data lands Saturday)

Request window is capped at 30 days per call on the REST endpoint. Omitted dates default to the most recent 30. For multi-year intraday history use flat files at gs://prod-masscrest/v0/option_flows/10_min/; the REST cap keeps response sizes bounded.

Bucket boundary convention

ten_min_timeframe is the end of the 10-minute bucket, in New York wall-clock time stored as a naive DateTime. Bucket covers (ten_min_timeframe − 10 min, ten_min_timeframe]. First RTH bucket ends at 09:40:00, last at 16:00:00 (39 buckets per full RTH session). Join to px_10_min on (date, ten_min_timeframe, figi); both sides use the same NY-wall-clock convention.

Field semantics match option_flows_01_day exactly: the same buy / sell / net triples across the participant classes, greek-weighted USD notionals, the accounting identity _i + _r + _m ≈ 0 (row-by-row), and the same last_* end-of-bucket context columns. The only structural difference is the added ten_min_timeframe dimension.

Fields

Join hints

For per-underlying 10-min aggregates (rolled up across every contract on the same (date, ten_min_timeframe, figi)), use underlying_option_flows_10_min. For underlying prices and turnover, join to px_10_min on (date, ten_min_timeframe, figi) and self-adjust using split_factors. For daily rollups of the same contracts, sum across ten_min_timeframe or use option_flows_01_day directly.

Gross-volume accounting

Each trade has a customer side (buy_{r,i}_qty / sell_{r,i}_qty) and a market-maker counterparty (buy_m_qty / sell_m_qty, rebuilt to absorb the customer imbalance so the row-level _i + _r + _m ≈ 0 identity holds on notionals).

DO: aggregate on qty — one entry per trade per bucket.

DON'T: sum the cohort legs (buy_r_qty + sell_r_qty + buy_i_qty + sell_i_qty + buy_m_qty + sell_m_qty + floor_qty) — inflates per bucket volume by ~30-35% because the market-maker leg gets added on top of the customer legs.

Per-underlying × callput daily aggregation. Same participant-split option-flow columns as option_flows_01_day, rolled up across every contract on the same (date, figi, callput) grain rather than kept at contract resolution. Every underlying has up to two rows per session: callput = 'C' and callput = 'P'. Adds context columns (contract count, aggregate share count, session-end underlying mark, 30-day ATM IV).

• Grain: (date, figi, callput)
• History: 2020-01-02 →
• Timing: T+1 (published between 05:00 and 09:00 UTC on the calendar day after the trading date — Friday's data lands Saturday)

Underlying price, turnover, and metadata are not baked into this table. Join to px_01_day (adjust locally with split_factors) and stock_metadata on figi.

Difference vs option_flows_01_day

option_flows_01_day is per-contract. underlying_option_flows_01_day collapses every contract on (date, figi, callput) into a single row, preserving the call vs put dimension for cross-sectional or callput-aware research (put-only skew, gross calls-vs-puts turnover, callput-aware Δ). Query with callput=CP to have both rows summed back into one at query time; query with callput=C or callput=P for a single side. No contract-level filters here (no dte / abs_delta axes); this table is pre-aggregated inside the pipeline.

Fields

Gross-volume accounting

Each trade has a customer side (buy_{r,i}_shares / sell_{r,i}_shares) and a market-maker counterparty (buy_m_shares / sell_m_shares, rebuilt to absorb the customer imbalance so the row-level _i + _r + _m ≈ 0 identity holds on notionals). Same convention applies to _premium / _delta / _gamma / _vega.

DO: aggregate on total_shares — one entry per trade.

DON'T: sum the cohort legs (buy_r_shares + sell_r_shares + buy_i_shares + sell_i_shares + buy_m_shares + sell_m_shares) — inflates volume by ~30-35% because the market-maker leg gets added on top of the customer legs.

10-minute-bucketed sibling of underlying_option_flows_01_day. Per-underlying × callput option-flow aggregation at a 10-minute cadence, with contract-count context and session-end marks.

• Grain: (date, ten_min_timeframe, figi, callput). Up to two rows per underlying per 10-min bucket ('C' and 'P').
• History: 2020-01-02 →
• Timing: T+1 (published between 05:00 and 09:00 UTC on the calendar day after the trading date — Friday's data lands Saturday)

Underlying price, turnover, and metadata are not baked into this table. Join to px_10_min (adjust locally with split_factors) and stock_metadata on figi.

Request window is capped at 30 days per call on the REST endpoint. For multi-year intraday history use flat files at gs://prod-masscrest/v0/underlying_option_flows/10_min/.

Bucket boundary convention

ten_min_timeframe is the end of the 10-minute bucket, NY wall-clock, naive DateTime. Bucket covers (ten_min_timeframe − 10 min, ten_min_timeframe]. First RTH bucket: 09:40:00; last: 16:00:00. Same convention as option_flows_10_min and px_10_min; join directly on (date, ten_min_timeframe, figi).

Difference vs option_flows_10_min

option_flows_10_min is per-contract. underlying_option_flows_10_min collapses every contract on (date, ten_min_timeframe, figi, callput) into a single row, keeping the call vs put dimension and adding contract-count / session-mark / IV context columns. This is the intended surface for cross-sectional or callput-aware research.

buy_m_shares / sell_m_shares are for directional cohort analysis (net market-maker positioning on the row), not for gross volume totals. The same convention applies to the _premium, _delta, _gamma, and _vega cohort columns — sum buy_r + sell_r + buy_i + sell_i + buy_m + sell_m and you double-count the market-maker leg on every unit.

Fields

Gross-volume accounting

Each trade has a customer side (buy_{r,i}_shares / sell_{r,i}_shares) and a market-maker counterparty (buy_m_shares / sell_m_shares, rebuilt to absorb the customer imbalance so the row-level _i + _r + _m ≈ 0 identity holds on notionals). Same convention applies to _premium / _delta / _gamma / _vega.

DO: aggregate on total_shares — one entry per trade per bucket.

DON'T: sum the cohort legs (buy_r_shares + sell_r_shares + buy_i_shares + sell_i_shares + buy_m_shares + sell_m_shares) — inflates per bucket volume by ~30-35% because the market-maker leg gets added on top of the customer legs.

Daily flow-signal derivatives computed on the underlying-level nets (net_i_*, net_r_*, greeks included) from underlying_option_flows_01_day. Per-(date, figi, callput) grain. All fields are trailing-window transforms (no forward-looking information), so a row at date = D is safe to use for a decision at close of D (or, more conservatively, at open of D+1).

• Grain: (date, figi, callput) where callput ∈ {'C', 'P', 'CP'}.
• History: 2020-01-02 →
• Timing: T+1 (published between 05:00 and 09:00 UTC on the calendar day after the trading date — Friday's data lands Saturday; same publish window as flows).

Signal design

This is an in-house Masscrest signal built to flag options activity that is extreme against a stock's own history. Each raw daily net is summed over a 21-trading-day trailing window and then z-scored against its own 2-year rolling mean and standard deviation, scoped to (figi, callput).

• _21dma = 21-trading-day rolling SUM of the raw daily net, PARTITION BY (figi, callput). (Historically shipped as a rolling mean; arithmetically equivalent for the z-score, since the sum is just the mean multiplied by 21. The distributed series is now the sum.)
• z_net_*_21dma = z-score of _21dma against its 2-year (504 trading day) trailing mean and sample standard deviation, computed at query time. Minimum 63 non-null observations in the trailing window required, else null. Rounded to 2 decimals on emit.

The raw rolling μ and σ series are computed on the fly at query time and are not distributed as separate columns — only the resulting z-score ships.

The calculation is deliberately simple. Options activity is not stationary across stocks or over time, and z-scores can reach unrealistic multiples of 3 SD during earnings, M&A, or other catalysts. Masscrest does not cap, winsorise, or otherwise regularise the output: keeping the formula plain avoids overfitting to any specific regime and lets clients replicate the transform end-to-end from underlying_option_flows_01_day if they want to. Validation of the single-name spike predicate lives at /research/systematic-single-stock-signal; the industry-aggregated version and its long-short performance live at /research/industry-flow-signal.

callput semantics

Every row is scoped to one callput bucket:

• C: calls only. Raw nets and greeks are direct sums of the call-side contracts.
• P: puts only. Raw nets and greeks are direct sums of the put-side contracts.
• CP: pre-computed combined view. Nets (shares / premium / delta) are C − P because bullish call flow and bearish put flow both express bullish conviction, so the nets combine sign-flipped. Greeks (gamma / vega) are C + P because they are naturally signed by option type (calls +, puts −) and summing captures total book exposure.

Rolling windows PARTITION BY (figi, callput); each callput row has its own independent trailing 21dma and 2y baseline, so z(C) + z(P) ≠ z(CP). Filter to exactly one callput per query or triple-count.

Warm-up

All fields are computed from 2020-01-02 forward. The 21-day rolling sum needs 21 non-null daily nets; the z-score needs ≥ 63 non-null observations in the trailing 504-session (2-year) window and emits NULL until that threshold is met.

Transform chain: daily net → 21-day trailing sum (_21dma) → z-score against 2y rolling μ/σ of the 21dma (z_*).

Fire semantics live at the consumer

The signal table does not persist signal_* (2σ / 3σ fire) or hp_* (22-day holding-period) columns; derive them at query time from the z-scores. Masscrest's canonical spike predicate is:

WHERE z_net_i_delta_21dma > 2 AND callput = 'CP'    -- 2σ institutional-delta spike
WHERE z_net_i_delta_21dma > 3 AND callput = 'CP'    -- 3σ institutional-delta spike

Both retail (net_r_*) and institutional (net_i_*) transforms ship. By convention Masscrest treats large institutional-side deviations as the primary tradable spike; the retail-side z-scores are available for symmetric analysis (crowded-consensus vs smart-money-vs-retail divergence checks, see the crowding_tag in the taxonomy resource).

Adding liquidity and stock-return context

This table only carries the signal itself, not stock liquidity or stock returns. If you want to filter for liquid names (e.g. "ignore anything under $10M average daily dollar turnover") or overlay stock returns for validation, join px_01_day on (date, figi). Dollar turnover (turnover) can be averaged directly; for split-adjusted returns from close, also join split_factors.

Fields

Z-score precision

z_* fields are rounded to 2 decimals on emit. When comparing a client-side derived spike predicate (z > 2) against the rounded value, expect edge cases on low-vol names where the true unrounded z sits just under 2.00 but the rounded value equals 2.00.

CP synthesis is pre-computed, not query-time

The 'CP' rows are materialised at publish time, not derived at query time. Each (figi, callput) bucket has its own independent 21dma and 2y baseline trajectory; z_net_i_delta_21dma on the CP row is derived from CP's own trailing series, not from summing per-side z-scores.

Deriving fire flags and holding periods

The table does not persist pre-computed fire flags (e.g. "2σ spike today = TRUE") or holding-period booleans. Compute them at query time from the z-scores: WHERE z_net_i_delta_21dma > 2 AND callput = 'CP' for the canonical 2σ institutional-delta spike. For a 22-day holding-period rule (fire within the last N days and the same z has not gone negative since), a self-join or a rolling max(z) window on the same series is enough.

Group-level daily flow-signal derivatives: sector / industry / etf / adr / single_stock / all rollups of the underlying-level nets and greeks aggregated in underlying_flow_signal_01_day. Same 50-flow-metric shape across 5 layers × 10 metrics. Per-(date, group_key, callput) grain.

• Grain: (date, group_key, callput)
• History: 2020-01-02 →
• Timing: T+1 (published shortly after underlying_flow_signal_01_day on the option-analytics workflow, typically 06:00-11:00 UTC on the calendar day after the trading date — Friday's data lands Saturday).

Response-shape rewrite (2026-08-14)

Every row is keyed by a single group_key STRING column. group_type on the REST endpoint takes the same set of canonical values (case-insensitive on input) — a mixed-case sector name (e.g. "Communication Services"), a mixed-case industry name (e.g. "Semiconductors"), or one of the rollup literals "etf" / "adr" / "single_stock" / "all". The row echoes that value back in group_key.

Group semantics

Why sector / industry exclude ETFs and ADRs

FMP tags each ETF's sector with the issuer's legal-entity sector (typically Financial Services for the sponsor, not the underlying-exposure sector). Pooling ETFs into sector rollups inflated Financial Services gross flow by 85%+ — meaningless for cohort analysis. Same-shape distortion for ADRs. The 2026-08-11 pipeline change excludes both from sector and industry aggregations; they get their own top-level etf and adr branches. As a consequence, sum(sector) ≠ all — the residual is precisely the etf + adr contribution.

Valid sector values

The ~11 distinct group_key strings emitted when group_type = 'sector'. Case-insensitive on input; canonical mixed-case on the wire. As-of 2026-08-14 snapshot; may drift as figis are reclassified or new sectors are added.

Valid industry values

The ~150 distinct group_key strings emitted when group_type = 'industry'. Case-insensitive on input; canonical mixed-case on the wire. As-of 2026-08-14 snapshot; may drift as existing figis are reclassified or new industries are added. For the live authoritative list, call the grouped-flow-signal endpoint with group_type=industry and take the distinct group_key values from the response.

Every industry with any flow on a given date is emitted (2026-08-28 revision — the time series is gap-free). Every industry-day carries populated raw daily nets AND populated rolling / z fields; n_figi is exposed on the row so consumers can filter thin-breadth days at query time. See "Thin-industry handling" below for the z-magnitude comparability caveat on structurally-thin industries such as Copper, Uranium, Consulting Services, Regulated Water, Conglomerates, Waste Management, and Railroads.

Signal formula

• _21dma = 21-trading-day rolling SUM of the raw group-level net, PARTITION BY (group_key, callput). (Historically shipped as a rolling mean; arithmetically equivalent for the z-score, since the sum is just the mean multiplied by 21. The distributed series is now the sum.)
• z_net_*_21dma = z-score of _21dma against its 2-year (504 trading day) trailing mean and sample standard deviation, computed at query time. Minimum 63 non-null observations in the trailing window required, else null. Rounded to 2 decimals on emit.

The raw rolling μ and σ series are computed on the fly at query time and are not distributed as separate columns — only the resulting z-score ships.

Every industry-day is emitted with populated rolling / z fields. The 21dma and 2y baseline include ALL days regardless of n_figi — thin days contribute their (smaller) raw values to the trailing window, so each industry's z is calibrated against its own historical breadth mix and populates on every day. Cross-industry z magnitudes aren't directly comparable when breadth profiles differ meaningfully — filter on n_figi at query time if comparability matters. See "Thin-industry handling" below.

Aggregate identities

• single_stock + etf + adr = all per (date, callput), 0 residual.
• sum(sector) ≈ single_stock per (date, callput); small residual (< 1% of gross flow) attributable to single-stock figis without a sector label.
• Because sector / industry exclude ETFs and ADRs, sum(sector) ≠ all — the difference is precisely the etf + adr gross flow. Historical rollups produced before 2026-08-11 rolled ETFs+ADRs into sector; those runs have been superseded.
• sum(industry) ≈ single_stock per (date, callput). Every industry row (thin or full-breadth) contributes its raw flow to the sum, so this identity holds tighter than it did before 2026-08-28.

Thin-industry handling

Every group with any flow on a given date is emitted — the time series is gap-free. Every row (thin or full-breadth) carries populated raw daily nets AND populated rolling / z fields:

• n_figi is populated with the honest constituent count (visible in the response) — consumers filter thin-breadth days at query time.
• Raw daily nets (net_i_shares, net_r_shares, …, net_r_vega) are populated with the actual group sum.
• Rolling / derived columns (_21dma, z_*) are populated on every row. The 21dma and 2y baseline include ALL days regardless of n_figi — thin days contribute their (smaller) raw values to the trailing window and to the 2y μ/σ used to z-score. Each industry's z-score is therefore calibrated against its own historical breadth mix — a chronically-thin industry's baseline is set to its own thin-day scale, so z fires when flow is unusually large for that industry. Cross-industry z magnitudes aren't directly comparable when breadth profiles differ meaningfully (a +2σ on a 5-FIGI industry is not directly comparable to a +2σ on a 50-FIGI industry). Filter on n_figi at query time when magnitude comparability matters.

Sector, etf, adr, single_stock, and all branches always exceed 10 contributing FIGIs so the caveat is a no-op for those branches. Only industries — historically ~46 including Copper, Uranium, Consulting Services, Regulated Water, Conglomerates, Waste Management, Railroads — are materially affected. Before 2026-08-28 those industries were dropped entirely and visible as gaps in the served time series; now they emit continuous rows with populated raw flow AND populated (but caveated) z-scores. Live coverage: 11 sectors + ~150 industries + 4 rollups (all, etf, adr, single_stock) — every group with flow that day appears in the cross-section.

Classification is not strictly PIT

The underlying flow data is point-in-time, but each figi's sector, industry, and ETF / ADR classification uses the most recent labelling rather than the label in force at the time. META, for example, rolls up under Communication Services for its entire history even though it was classified as Technology in 2019. This keeps historical rollups comparable under a single consistent scheme as companies drift between sectors over time.

callput semantics

Every row is scoped to one callput bucket. C = calls only, P = puts only, CP = pre-computed combined view (nets are C − P because bullish call flow and bearish put flow both express bullish conviction, so nets combine sign-flipped; greeks are C + P because they are naturally signed by option type). Rolling windows partition by (group_key, callput); each callput row has its own independent trailing 21dma and 2y baseline, so z(C) + z(P) ≠ z(CP). Filter to exactly one callput per query or triple-count.

Warmup NULL conventions

• ..._21dma NULL when a (group_key, callput) has < 21 days of history in the trailing 21-row window.
• z_..._21dma NULL when the 21dma series has < 63 non-null observations in the trailing 504-session (2-year) window, or the window's sample stddev is 0.
• These are legitimate NULLs; do not fill.

REST endpoints

• GET /v0/grouped_flow_signal/01_day: ranged. Requires group_type = a single canonical group_key value (sector / industry name or one of the rollup literals etf / adr / single_stock / all; case-insensitive on input, fuzzy-suggestion on unknown values). Returns rows for a date range at that one key.
• GET /v0/grouped_flow_signal/01_day/snapshot: single-day cross-sectional. All filters optional. date defaults to latest. Omit both category and group_type → every group_key with any flow on the day (up to ~168 rows on the default callput = 'CP'). category (snapshot-only: industry / sector / rollup / all) narrows to a whole family in one call. group_type (single canonical group_key value) narrows to one key. category and group_type are mutually exclusive.

Fields

CP synthesis is pre-computed, not query-time

Same rule as underlying_flow_signal_01_day: the 'CP' rows are materialised at publish time, and each (group_key, callput) bucket has its own independent 21dma and 2y baseline trajectory.

Group classification is snapshot-only

Load-bearing, worth repeating: the sector / industry / etf / adr / single_stock labels used to route flow into each row come from each figi's latest-known stock_metadata phase (current phase for live names, last-live phase for delisted names) applied across every historical date. Do NOT use this table for PIT backtests where a stock's historical group membership matters; use underlying_flow_signal_01_day and roll up client-side.

Daily close (unadjusted) for the covered US single-stock and ETF universe, keyed on FIGI.

Access: this table is not directly queryable via the MCP or REST API. Split-adjusted price columns (adj_close, adj_open, adj_volume, total_turnover) are returned as LEFT-JOIN columns on get_underlying_option_flows_daily (MCP) and GET /v0/underlying_option_flows/01_day (REST). Use those endpoints for daily price context — passing traded_underlying=<TICKER> and callput='CP' returns one row per date with prices attached to the flow payload. For bulk historical pulls, the parquet flat-file surface is at gs://prod-masscrest/v0/px_data/01_day/.

• Grain: (date, figi, symbol) [1]
• History: 2020-01-02 →
• Timing: T+1 (published between 05:00 and 09:00 UTC on the calendar day after the trading date — Friday's data lands Saturday)

[1] The underlying storage carries symbol to preserve point-in-time ticker context, but every join to Masscrest surfaces should use figi.

Fields

Unadjusted semantics

Prices and volume in this table are unadjusted (contemporaneous). To compute split-adjusted series (safe returns across split events, share-count normalisation, historical strike comparisons), join to split_factors on figi where date BETWEEN valid_from AND valid_to and apply cum_split_factor per the reconstruction snippet in the flat-files section. The split is deliberate: it lets you reload px_01_day and split_factors independently, and gives you an explicit knob when you need to invert an adjustment.

10-minute OHLCV bars (unadjusted) for the covered US single-stock and ETF universe, keyed on FIGI. Same underlying universe and FIGI join key as px_01_day.

Access: this table is not directly queryable via the MCP or REST API. Split-adjusted price columns (adj_close, adj_open, adj_volume, total_turnover) are returned as LEFT-JOIN columns on get_underlying_option_flows_10min (MCP), intraday_flow_day (MCP fat tool for a single symbol × single day), and GET /v0/underlying_option_flows/10_min (REST). Use those endpoints for intraday price context — the LEFT-join emits one row per (date, ten_min_timeframe, figi) with prices attached to the flow payload. For bulk historical pulls, the parquet flat-file surface is at gs://prod-masscrest/v0/px_data/10_min/.

• Grain: (date, ten_min_timeframe, figi, symbol)
• History: 2020-01-02 →
• Timing: T+1 (published between 05:00 and 09:00 UTC on the calendar day after the trading date — Friday's data lands Saturday)

Bucket boundary convention

ten_min_timeframe is the end of the 10-minute bucket, in New York wall-clock time stored as a naive DateTime. Bucket covers (ten_min_timeframe − 10 min, ten_min_timeframe]. First RTH bucket ends at 09:40:00, last at 16:00:00 (39 buckets per full session). On-boundary 1-minute bars (e.g. 10:00:00 close) land in the closing bucket, not the opening bucket of the next.

Coverage

Regular trading hours only (09:30–16:00 ET). No pre-market, no after-hours. Halted sessions produce no rows for the halted intervals.

Fields

Unadjusted semantics

Prices and volume in this table are unadjusted (contemporaneous). To compute split-adjusted series, join to split_factors on figi where date BETWEEN valid_from AND valid_to and apply cum_split_factor per the reconstruction snippet in the flat-files section. The split is deliberate: it lets you reload px_10_min and split_factors independently, and gives you an explicit knob when you need to invert an adjustment.

Ticker-renamed FIGIs

A very small number of FIGIs carry rows under two different symbol values within a single day (the point-in-time symbol at the moment of the trade). If you aggregate over a universe by figi alone, use sum(turnover) for turnover and argMax(close, turnover) for a representative close per (date, ten_min_timeframe, figi). Don't collapse via any(...) or you'll pick a stale legacy row.

Point-in-time (PIT) reference table for every underlying Masscrest tracks. Maps FIGI to listed ticker, company name, sector / industry, ETF and ADR flags, and the activity window during which that (ticker, FIGI) pair was live. Historical ticker changes, dual-class shares, and shell-reuse events are all encoded as separate phase rows.

• Grain: (figi, symbol, phase_start), one row per contiguous period during which a FIGI traded under a given symbol.
• History: all open phases plus historical phases as far back as the OpenFIGI record supports (many phases start 1900-01-01 as a "no constraint" sentinel).
• Timing: reference table, rebuilt in full each trading day. Re-sync daily to pick up new phases and any corrections.
• Typical use: join to any flow / price table on figi with date BETWEEN phase_start AND phase_end to attach a point-in-time symbol, sector, name, ETF flag, etc.

Known quirks

• For ETFs (isEtf='true', which includes ETCs and ETPs), sector and industry describe the sponsor legal entity, not the fund's underlying exposure. All BlackRock / iShares sector ETFs show sector='Financial Services' because BlackRock is a financial-services firm. Filter isEtf='true' out before aggregating flow by sector, otherwise Financial Services gross flow inflates by 85%+.
• For ADRs (isAdr='true'), sector and industry describe the underlying foreign company's business. HDB is Financial Services (HDFC Bank), BABA is Consumer Cyclical (Alibaba). Usually the label you want. Filter on isAdr if you need to separate US-domiciled sector totals from foreign ADRs.
• The served rollup v0.grouped_flow_signal_01_day already excludes ETFs and ADRs from its sector and industry group_keys. ADRs have their own adr group_key and ETFs their own etf. This quirk only matters when joining v0.stock_metadata directly.

Symbol conventions

• Equity tickers use the dotted dual-class convention (BRK.B, RDS.A), matching every other Masscrest surface. When OPRA options data references the same underlying via its no-dot OCC root (BRKB), the equity join still uses the dotted form. Options tables carry both: traded_symbol (OCC root) for options joins and traded_underlying (dotted equity) for FIGI / equity joins.
• Open phases use phase_end = 2099-12-31 as the sentinel. Closed phases (renames, delistings, ticker recycles) carry a real end date.
• terminal_symbol is the current ticker for the same FIGI as of today. Historical phases point forward at their entity's today-identity; join on it for "all history for whatever is called META today," even for the older FB rows.

Multi-phase tickers

~1% of tickers have multiple stock_metadata rows across time — one per FIGI-era — from corporate reorgs (ISIN flip, e.g. XOM's 2026-07-07 Exxon → ExxonMobil Holdings restructure) or ticker recycles after a delisting (PARA was Paramount, then Banzai after 2026-08-07 under a different FIGI).

• Current era only: WHERE symbol='X' AND phase_end='2099-12-31'
• Full history across eras: WHERE symbol='X', resolve joins with date BETWEEN phase_start AND phase_end
• Isolate one era: filter by figi rather than symbol

Flow, price, and split tables are already attributed to the FIGI authoritative on each row's date.

Fields

Cumulative split-adjustment factors per FIGI, structured as a slowly-changing dimension (SCD2). Delivered so clients can back-adjust unadjusted historical prices (see px_01_day / px_10_min), or invert a Masscrest split-adjusted price back to the raw contemporaneous print for a specific period.

• Grain: one row per continuous split-factor phase per figi, (figi, valid_from, valid_to), non-overlapping intervals covering each FIGI's timeline. date BETWEEN valid_from AND valid_to matches exactly one row per (figi, date).
• History: full history for each covered FIGI, back to the earliest known split. Pre-split-history phases use valid_from = 1900-01-01 as a "no earlier bound" sentinel.
• Timing: reference table, rebuilt in full each trading day whenever a new split lands. Re-sync daily.
• Coverage: currently ~30k FIGIs. Historical splits back to 1962 and forward-looking upcoming ex-dates are both covered.

How to use the factor

cum_split_factor on a date X means: to convert a raw close on date X into a current-shares-equivalent price, divide by this factor. It equals 1.0 on and after the most recent split; > 1 before a forward split (e.g. 4.0 before a 4-for-1); < 1 before a reverse split. On or after the latest split within an entity's active period, the factor is 1.0.

Fields

Precision

Cumulative factors are stored at 12 decimal places to preserve heavy reverse-split stacks (e.g. UVXY has thirteen reverse splits; its true cum factor at 1900-01-01 is ~6.7e-11). Do NOT round to 6 decimals when consuming; that historically truncated ~200 heavy-reverse-splitter FIGIs to 0.0 and silently zeroed downstream price adjustments. For the handful of penny-stock cases where the true cumulative product underflows Float64 precision entirely (e.g. stacked 1-for-50000 × 1-for-19000 × 1-for-10000 ≈ 3.5e-20), we emit NULL rather than 0.0 so consumers can detect and skip rather than silently multiplying prices by zero.

No-split entities

FIGIs that never split carry a single row spanning the entity's full active phase with cum_split_factor = 1.0. Same shape as those that split; no special-case handling needed.