Dataset Reference

This chapter is generated from the canonical platform doc, which lives in docs/ (single source of truth).

SBFoundation Domain & Dataset Reference

Last Updated: 2026-08-30 (TASK-3649 / B-450.2 — grades gains lifecycle_scope: all, the first use of the new per-dataset symbol-lifecycle keymap key; entry count unchanged at 59)

This document is the comprehensive reference for all data domains and datasets in SBFoundation. For API usage and pipeline operations, see docs/api-usage.md. For dataset configuration details, see config/dataset_keymap.yaml.

Domain Overview

SBFoundation ingests data across 5 domains containing 59 datasets from 3 sources (FMP, FRED, FINRA). (Counts corrected against config/dataset_keymap.yaml on 2026-08-18 — the previous “4 domains / 54 datasets” wording predated the etf domain and the revenue-segmentation feeds.)

Domain Datasets Scope Description API Entry Point
eod 3 Mixed End-of-day prices and company profiles api.run_eod()
quarter 4 Global Quarterly financial statements and key metrics api.run_quarter()
annual 5 Global Annual financial statements, key metrics, and ratios api.run_annual()
eow 45 Mixed End-of-week: index constituents, macro indicators, FX rates, stock splits, analyst estimates, analyst rating actions (grades, F-450), earnings surprises, revenue segmentation, short interest api.run_eow()
etf 2 Per-ticker ETF reference data (info + holdings, F-343 / EU-2); folded into the EOW cadence bucket ingest-etf CLI

Execution order: eod → quarter → annual → eow

Data sources: - FMP (Financial Modeling Prep) — 27 datasets; requires FMP_API_KEY - FRED (Federal Reserve Economic Data) — 31 datasets; requires FRED_API_KEY - FINRA (Financial Industry Regulatory Authority) — 1 dataset; no API key required

EOD Domain (3 datasets)

End-of-day market data and company profiles. This is the primary daily-refresh domain.

Market Data

Dataset Scope Silver Table Key Columns Refresh API Path
eod-bulk-price global fmp_eod_bulk_price symbol, date Daily (1) eod-bulk
eod-price-history per_ticker fmp_eod_bulk_price symbol, date Daily (0) historical-price-eod/dividend-adjusted
  • eod-bulk-price: Daily bulk CSV of OHLCV + adjusted close for all instruments. Primary source for gold.fact_eod. At Gold promotion, OHLC columns are adjusted by the adj_close / close ratio so all prices are split- and dividend-adjusted.
  • eod-price-history: Per-ticker historical price backfill (deep-history backfill-eod-chunked + delisted survivorship backfill). Writes to the same Silver table as eod-bulk-price. B-147.6 (F-147): endpoint corrected from historical-price-eod/full (which returns no adjClose → NULL adj_close for ~28% of gold.fact_eod) to historical-price-eod/dividend-adjusted (returns adjOpen/adjHigh/adjLow/adjClose), mapped via the dedicated EodDividendAdjustedPriceDTO — adjClose populates both close and adj_close, so the rows are already-adjusted and identical to the bulk path through Gold’s back-adjustment math. B-147.9 (F-147): the keymap dto_schema api: keys are the projection authority for this adjusted endpoint. open/high/low/close declare api: adjOpen/adjHigh/adjLow/adjClose and adj_close declares api: adjClose (TASK-3881). The Silver path (DTOProjection._project_from_schema) does not read the DTO field metadata. Before that fix, about 8.95M adjusted rows (4,737 symbols) projected NULL open/high/low/close. The stored-row repair is the runbook’s B-147.9 section (TASK-3947). Not in the nightly EOD path (excluded by EodBronzeIngester); driven only by the backfill/delisted commands.
  • Fields: symbol, date, open, high, low, close, adj_close, volume, data_quality_flag (F-086 — DTO-time severity-wins flag: non_positive_close (error) / non_positive_volume (warn) / ohlcv_corrected (info))
  • Symbol filter: symbol_filter_col: symbol — only allowlisted instruments promoted to Silver
  • Calendar guard (B-448.6 / TASK-3874): both eod-bulk-price and eod-price-history declare calendar_guard: weekday_only — an optional keymap field (vocabulary: weekday_only; absent = no guard; an unknown value is rejected by DatasetKeymapEntry.from_payload). At Silver promotion SilverService._apply_calendar_guard drops every row whose row_date_col falls on a Saturday or Sunday, and quarantines the whole artifact (zero rows promoted, one WARNING) when its weekend-row fraction is ≥ sbsilver.settings.WEEKEND_ROW_QUARANTINE_FRACTION (0.05). Why: FMP’s historical-price-eod/dividend-adjusted endpoint returned WLL’s entire history shifted one calendar day early (878 Sunday bars), and the (symbol, date) MERGE turned that into overwrite + insert + stranded NULL-adj_close rows. Of 4,561 tickers compared across both endpoints, 4,545 are clean and 16 carry weekend bars. A shifted series mislabels its weekday rows too, which is why a payload over the threshold is refused rather than trimmed. US-holiday weekdays are deliberately not guarded (non-US listings trade on them). The stored-data repair is six deferred, approval-gated migrations under db/migrations/pending/20260911_101..106_*.sql.pending (operator TASK-3880). Detail → docs/sbfoundation-steps.md Step 3.
  • Quality thresholds (F-086 / TASK-313): eod-bulk-price carries an optional quality_thresholds block consumed by PriceDataQualityService during the factor phase. Defaults: flag_high_price_z=5.0, flag_high_volume_z=5.0, max_gap_business_days=5, and — TASK-3197 — adjacency_ratio_upper=4.0, adjacency_ratio_lower=0.25, adjacency_sub_cent_floor=0.01 for the fourth detector flag_adjacency_discontinuity (single-day adj_close discontinuity = vendor back-adjustment artifact; fires when the adjacent ratio falls outside [lower, upper], or when the prior close is sub-cent — below the $0.01 US minimum tick, hence an artifact denominator). TASK-3863 adds frozen_price_min_run_bars=21 for the fifth detector flag_frozen_price (an adj_close unchanged across >= N consecutive bars, one finding per streak; keyed on price rather than volume, since a frozen vendor series often carries real volume). Detect-only: neither detector mutates stored prices or gates a run. Override per-key in config/dataset_keymap.yaml; missing keys fall back to the service defaults. A key absent from sbsilver.settings.DEFAULT_QUALITY_THRESHOLDS is ignored by the resolver, so a new threshold must be added there first.
  • Documentation: FMP EOD Bulk | FMP Historical Price

Company Metadata

Dataset Scope Silver Table Key Columns Refresh API Path
company-profile-bulk global fmp_company_profile_bulk symbol Daily (1) profile-bulk
  • company-profile-bulk: Snapshot of all company profiles (name, exchange, sector, industry, country, market cap, IPO date, etc.). Paginated via part parameter. Feeds gold.dim_instrument, gold.dim_company, and the promotion allowlist.
  • Open operator follow-up (TASK-2677): on at least one observed run, FMP returned "Invalid or missing query parameter" for this recipe at part>=4 (an otherwise well-formed, unchanged-shape paginated request), and pages 1-3 also showed minor row-count shortfalls vs. the expected ~22,616 rows/page. BronzeService._process_paginated_recipe now retries this specific error (bounded, same backoff as the existing 429/502/503/504 classes) and, if the retry budget is exhausted, records it as a genuine Bronze failure (logged at ERROR with the raw FMP body, and surfaced on the ingest_bronze sidecar’s per-domain failed_pages alert) instead of the pre-fix behavior of silently treating it as end-of-pagination — but the root cause on FMP’s side (transient bulk-endpoint glitch vs. a durable part-parameter validation bug beyond some threshold) has not been confirmed with FMP support. An automated agent cannot open a support ticket; a human operator should verify the part parameter’s documented behavior for profile-bulk against FMP’s current API docs/support (see help_url above) and confirm whether the page>=4 failure recurs across the next several nightly runs (also tracked as Tier-4-pending on TASK-2677).
  • is_etf allowlist semantics (F-247 / EU-1): the bulk feed carries is_etf per row; InvestableUniverseService.rebuild() writes it onto every silver.fmp_promotion_allowlist row (allowed and rejected). With sbuniverse.settings.ALLOW_ETFS=False (the default, env SB_ALLOW_ETFS) ETFs reject with rejection_reason='ETF_FLAG', byte-identical to today; with ALLOW_ETFS=True the ETF arm is skipped so ETFs land is_allowed=TRUE and flow Silver→Gold (the funds / mutual-fund 5X / warrant / preferred / ADR / rights arms are untouched). Downstream surfaces partition by is_etf — the equity research _SPINE + StrategyUniverseService + UniverseRepo.get_filtered_tickers re-exclude ETFs so the equity factor cross-section stays equity-only (TASK-1903), and the F-247 / TASK-1904 EtfFactorIcService scores the 7 price factors against instrument_class="etf" only.
  • Documentation: FMP Profile Bulk

Quarter Domain (4 datasets)

Quarterly financial statements and key metrics ingested as global bulk CSVs. One file per quarter covers all allowlisted instruments.

Dataset Silver Table Key Columns Refresh API Path
income-statement-bulk-quarter fmp_income_statement_bulk_quarter symbol, period, calendar_year Daily (1) income-statement-bulk?period=quarter
balance-sheet-bulk-quarter fmp_balance_sheet_bulk_quarter symbol, period, calendar_year Daily (1) balance-sheet-statement-bulk?period=quarter
cashflow-bulk-quarter fmp_cashflow_bulk_quarter symbol, period, calendar_year Daily (1) cash-flow-statement-bulk?period=quarter
key-metrics-bulk-quarter fmp_key_metrics_bulk_quarter symbol, period, calendar_year Quarterly (90) key-metrics-bulk?period=quarter
  • Gold table: gold.fact_quarter — one row per (instrument_sk, period_date_sk, period). Built from income + balance sheet + cashflow via optional LEFT JOINs.
  • Key fields — income: revenue, gross_profit, operating_income, net_income, ebitda, eps, eps_diluted, interest_expense, depreciation_and_amortization, weighted_average_shs_out, weighted_average_shs_out_diluted
  • Key fields — balance sheet: total_assets, total_liabilities, total_stockholders_equity, cash_and_cash_equivalents, long_term_debt, net_debt, goodwill_and_intangible_assets, retained_earnings
  • Key fields — cashflow: operating_cash_flow, capital_expenditure, free_cash_flow, dividends_paid, common_stock_repurchased
  • Key fields — metrics: roic, invested_capital, capex_to_ocf, ev_to_ebitda, days_sales_outstanding, days_payables_outstanding, days_inventory
  • Symbol filter: All use symbol_filter_col: symbol
  • Row-level quality flag (F-086 / TASK-307+TASK-310): fmp_balance_sheet_bulk_quarter and fmp_income_statement_bulk_quarter each gain a nullable data_quality_flag VARCHAR column. DTOs stamp balance_sheet_identity_violation (severity warn) when |total_assets - (total_liabilities + total_stockholders_equity)| / |total_assets| > 0.05, and net_income_exceeds_revenue (severity warn) when revenue > 0 AND net_income > revenue. Each fired flag also lands a row in ops.silver_anomaly.
  • Season gate: QuarterService skips runs outside earnings windows unless year+period are provided explicitly.
  • Documentation: FMP Financial Statements Bulk | FMP Key Metrics Bulk

Annual Domain (5 datasets)

Annual (FY) financial statements, key metrics, and ratios. Same bulk CSV pattern as quarterly.

Dataset Silver Table Key Columns Refresh API Path
income-statement-bulk-annual fmp_income_statement_bulk_annual symbol, calendar_year Monday (1) income-statement-bulk?period=FY
balance-sheet-bulk-annual fmp_balance_sheet_bulk_annual symbol, calendar_year Tuesday (1) balance-sheet-statement-bulk?period=FY
cashflow-bulk-annual fmp_cashflow_bulk_annual symbol, calendar_year Wednesday (1) cash-flow-statement-bulk?period=FY
key-metrics-bulk-annual fmp_key_metrics_bulk_annual symbol, calendar_year Thursday (365) key-metrics-bulk?period=FY
ratios-bulk-annual fmp_ratios_bulk_annual symbol, calendar_year Friday (365) ratios-bulk?period=FY
  • Gold table: gold.fact_annual — one row per (instrument_sk, period_date_sk). Merged from income + balance sheet + cashflow + key metrics + ratios via optional LEFT JOINs.
  • Key fields — income: revenue, gross_profit, operating_income, net_income, ebitda, eps, eps_diluted, interest_expense, depreciation_and_amortization, weighted_average_shs_out, weighted_average_shs_out_diluted
  • Key fields — balance sheet: total_assets, total_liabilities, total_stockholders_equity, cash_and_cash_equivalents, long_term_debt, net_debt, goodwill_and_intangible_assets, retained_earnings
  • Key fields — cashflow: operating_cash_flow, capital_expenditure, free_cash_flow, dividends_paid, common_stock_repurchased
  • Key fields — ratios: gross_profit_margin, operating_profit_margin, net_profit_margin, effective_tax_rate, debt_ratio, interest_coverage
  • Symbol filter: All use symbol_filter_col: symbol
  • Row-level quality flag (F-086 / TASK-307+TASK-310): fmp_balance_sheet_bulk_annual and fmp_income_statement_bulk_annual each gain a nullable data_quality_flag VARCHAR column. Same detectors as the quarter variants — balance_sheet_identity_violation (warn) and net_income_exceeds_revenue (warn) — with each fired flag also landing a row in ops.silver_anomaly.
  • Season gate: AnnualService skips runs outside Jan–Mar unless year is provided explicitly.
  • Documentation: FMP Financial Statements Bulk | FMP Key Metrics Bulk | FMP Ratios Bulk

EOW Domain (45 datasets)

End-of-week data: index constituent history, macro indicators, FX rates, company delisted list, mergers & acquisitions, stock splits, analyst estimates, analyst rating actions (grades, F-450), earnings surprises, revenue segmentation, and FINRA equity short interest. Keymap default cadence is run_days: [sat]; since F-356 (EU-6) the weekly deep run (Friday NIGHTLY_FULL, or an ad-hoc --research-mode nightly-full) force-refreshes the whole bucket by bypassing that weekday gate (force_weekly=True → bypass_run_days=True). ⚠ That flag bypasses the min_age_days floor as well — the earlier claim here, “with min_age_days still gating”, was false and is corrected by B-102.1: BronzeService._process_run_request nulls the ingestion watermark under the flag, so decide_cadence can never return too_soon. Plain Mon–Thu NIGHTLY stays cadence-gated, and since B-102.1 that is true per plan variant: the weekday EOW branch hands the bypass only to date_list / snapshot recipes (the FRED series the EOW date-walk exists to gap-fill), while the seven symbol_list per-ticker datasets (analyst-estimates ×2, earnings-surprises, grades, revenue-product-segmentation, revenue-geographic-segmentation, stock-split) resolve their own declared run_days / min_age_days. Before that fix a permanently-unsatisfiable FRED anchor put the whole domain on the bypass branch every Monday and Tuesday, re-fetching all seven nightly. Bronze-only task: ingest_bronze_eow_task; Silver promotion: promote_silver_eow_task.

Index Constituent History

Dataset Silver Table Key Columns Refresh API Path
sp500-constituent-history fmp_sp500_constituent_history symbol, date_of_change Weekly (7, Sat) historical-sp500-constituent
nasdaq100-constituent-history fmp_nasdaq100_constituent_history symbol, date_of_change Weekly (7, Sat) historical-nasdaq-constituent
djia-constituent-history fmp_djia_constituent_history symbol, date_of_change Weekly (7, Sat) historical-dowjones-constituent
  • Gold table: gold.fact_index_constituent — one row per (index_sk, instrument_sk, effective_from). effective_to = NULL means still a member. Built by GoldIndexService by replaying Silver change-log tables.
  • Fields: symbol, date_of_change, added_security, removed_ticker, removed_security, reason
  • No symbol filter — index constituent data includes removed tickers by design.
  • Reliable from: SP500 Oct 2008; NASDAQ100 Jan 1995; DJIA Jan 1994.
  • Documentation: FMP SP500 | FMP NASDAQ | FMP DJIA

Company Metadata (EOW)

Dataset Scope Silver Table Key Columns Refresh API Path
company-delisted global fmp_company_delisted symbol Weekly (7, Sat) delisted-companies
mergers-acquisitions global fmp_mergers_acquisitions symbol, targeted_symbol, transaction_date Weekly (7, Sat) mergers-acquisitions-latest
  • company-delisted: List of delisted companies with IPO and delisting dates. Feeds gold.dim_instrument lifecycle columns (delisting_date). No symbol filter — this dataset defines the allowlist boundary.
  • mergers-acquisitions: FMP M&A “latest” feed (stable mergers-acquisitions-latest, paginated via page) capturing acquirer (symbol, company_name, cik) and acquired target (targeted_symbol, targeted_company_name, targeted_cik) plus transaction_date, acceptance_time, url. B-356.1: the legacy v4/mergers-acquisitions-rss-feed path 404’d under the /stable/ base (no promotable Bronze → silver.fmp_mergers_acquisitions was never created); corrected to the stable mergers-acquisitions-latest endpoint. B-356.2: two of the nine columns were mapped to vendor keys that do not exist — the payload sends acceptedDate and link, not acceptanceTime/url, so acceptance_time and url were empty on 100% of Silver rows while the other seven populated cleanly. Verified against stored Bronze Parquet (CLAUDE.md §2 constraint 7), not the vendor docs: all 48 stored files (4,701 rows, every one HTTP 200) carry the uniform column set symbol, companyName, cik, targetedCompanyName, targetedCik, targetedSymbol, transactionDate, acceptedDate, link, with acceptedDate/link non-null on 4,701/4,701. The fix remaps both — metadata={"api": ...} on MergersAcquisitionsDTO and an api: override on the keymap dto_schema column, because the Silver promotion path (DTOProjection._project_from_schema) resolves by SchemaColumn.name/.api and never reads the dataclass metadata; a DTO-only fix would have left Silver empty. Silver column names and types are unchanged (acceptance_time/url, both str/VARCHAR): acceptance_time matches SEC EDGAR’s own ACCEPTANCE-DATETIME header — the quantity this actually is — and keeping VARCHAR preserves the vendor string verbatim; renaming or retyping would be Silver DDL under F-455 change control for no domain gain. Bronze already holds the values, so recovery is a re-promote (promote-silver mergers-acquisitions) with no re-ingest and no vendor call. The delisting-reason source (F-312): targeted_symbol is the join key linking a delisted instrument to its M&A event. Silver-only; no Gold promotion yet.
  • Documentation: FMP Delisted Companies | FMP Latest Mergers & Acquisitions

Stock Splits

Dataset Scope Silver Table Key Columns Refresh API Path
stock-split per_ticker fmp_stock_split symbol, date Monthly (30, Sat) splits
  • Purpose: Historical stock split events (numerator, denominator, split type). Feeds gold.fact_stock_split.
  • Fields: symbol, date, numerator, denominator, split_type
  • Documentation: FMP Stock Splits

Macro / Economics Indicators

Dataset Source Silver Table Key Columns Refresh API Path
fred-dgs10 fred fred_dgs10 date Saturday (1) series/observations
fred-usrecm fred fred_usrecm date Saturday (28) series/observations
fred-dgs1mo fred fred_dgs1mo date Saturday (1) series/observations
fred-dgs3mo fred fred_dgs3mo date Saturday (1) series/observations
fred-dgs1 fred fred_dgs1 date Saturday (1) series/observations
fred-dgs2 fred fred_dgs2 date Saturday (1) series/observations
fred-dgs5 fred fred_dgs5 date Saturday (1) series/observations
fred-dgs30 fred fred_dgs30 date Saturday (1) series/observations
fred-t10y2y fred fred_t10y2y date Saturday (1) series/observations
fred-baa10y fred fred_baa10y date Saturday (1) series/observations
fred-vixcls fred fred_vixcls date Saturday (1) series/observations
market-risk-premium fmp fmp_market_risk_premium country Saturday (30) market-risk-premium
  • fred-dgs10: 10-Year Treasury Constant Maturity Rate (risk-free rate proxy for CAPM/DCF). Series starts 1962-01-01. Used by EowHistoryService as the representative EOW freshness cursor.
  • fred-usrecm: NBER U.S. Recession Indicator (1 = recession, 0 = expansion). Series starts 1967-01-01.
  • F-317 FRED macro-series expansion (9 datasets): Silver-only macro time-series (each series/observations, Saturday cadence, min_age_days: 1), sharing one generic FredObservationDTO over {date, value}. Data-only — no macro-style factor is derived from them yet (deferred to Wave 2).
    • fred-dgs1mo / fred-dgs3mo: 1-Month / 3-Month Treasury Constant Maturity Rate — the short-end of the curve, the correct excess-return risk-free tenor (the 10Y fred-dgs10 stays the cost-of-equity / WACC proxy).
    • fred-dgs1 / fred-dgs2 / fred-dgs5 / fred-dgs30: 1/2/5/30-Year Treasury Constant Maturity Rate — the Treasury constant-maturity yield curve.
    • fred-t10y2y: 10-Year minus 2-Year Treasury term spread (yield-curve / regime signal).
    • fred-baa10y: Moody’s Baa corporate yield minus 10-Year Treasury credit spread (credit-regime signal).
    • fred-vixcls: CBOE Volatility Index (VIX) close (volatility-regime signal).
  • market-risk-premium: Equity risk premium by country (total equity risk premium, country risk premium).
  • Gold: All FRED + market-risk-premium datasets are Silver-only — no instrument_sk FK. Consumed directly by the feature engine.
  • Requires: FRED_API_KEY for FRED datasets.
  • Documentation: FRED DGS10 | FRED USRECM | FRED DGS1MO | FRED DGS3MO | FRED DGS1 | FRED DGS2 | FRED DGS5 | FRED DGS30 | FRED T10Y2Y | FRED BAA10Y | FRED VIXCLS | FMP Market Risk Premium

Foreign Exchange Rates (FRED FX)

Dataset Source Silver Table Series Currency Convention Refresh
fred-dexuseu fred fred_dexuseu DEXUSEU EUR usd_per_foreign Saturday (1)
fred-dexusuk fred fred_dexusuk DEXUSUK GBP usd_per_foreign Saturday (1)
fred-dexusal fred fred_dexusal DEXUSAL AUD usd_per_foreign Saturday (1)
fred-dexusnz fred fred_dexusnz DEXUSNZ NZD usd_per_foreign Saturday (1)
fred-dexcaus fred fred_dexcaus DEXCAUS CAD foreign_per_usd Saturday (1)
fred-dexjpus fred fred_dexjpus DEXJPUS JPY foreign_per_usd Saturday (1)
fred-dexnous fred fred_dexnous DEXNOUS NOK foreign_per_usd Saturday (1)
fred-dexszus fred fred_dexszus DEXSZUS CHF foreign_per_usd Saturday (1)
fred-dexbzus fred fred_dexbzus DEXBZUS BRL foreign_per_usd Saturday (1)
fred-dexmxus fred fred_dexmxus DEXMXUS MXN foreign_per_usd Saturday (1)
fred-dexchus fred fred_dexchus DEXCHUS CNY foreign_per_usd Saturday (1)
fred-dexinus fred fred_dexinus DEXINUS INR foreign_per_usd Saturday (1)
fred-dexkous fred fred_dexkous DEXKOUS KRW foreign_per_usd Saturday (1)
fred-dexsius fred fred_dexsius DEXSIUS SGD foreign_per_usd Saturday (1)
fred-dextaus fred fred_dextaus DEXTAUS TWD foreign_per_usd Saturday (1)
fred-dexsdus fred fred_dexsdus DEXSDUS SEK foreign_per_usd Saturday (1)
fred-dexdnus fred fred_dexdnus DEXDNUS DKK foreign_per_usd Saturday (1)
fred-dexsfus fred fred_dexsfus DEXSFUS ZAR foreign_per_usd Saturday (1)
fred-dexhkus fred fred_dexhkus DEXHKUS HKD foreign_per_usd Saturday (1)
fred-ccusma02ilm618n fred fred_ccusma02ilm618n CCUSMA02ILM618N ILS foreign_per_usd Saturday (1) — OECD monthly avg (no daily H.10 shekel series)
  • TASK-3143 / F-444 FRED FX expansion (20 datasets): Silver-only daily exchange-rate series (each series/observations, Saturday cadence, min_age_days: 1, max_date_quantity: 8), all sharing the generic FredObservationDTO over {date, value} (key col date). Reporting currency → USD conversion source for a later task that converts non-USD Gold fundamentals to USD; the currency → series + quoting-convention mapping is governed in config/fx_pairs.yaml (loaded by sbcontracts.fx_pairs.FxPairConfigService).
    • Convention: usd_per_foreign — rate is USD per 1 unit foreign → to-USD factor = rate (multiply). foreign_per_usd — rate is foreign units per 1 USD → to-USD factor = 1/rate (divide).
    • Gold: Silver-only — no instrument_sk FK (FX series have no instrument). Requires FRED_API_KEY.
    • Caveat: the DEX* series ids and quoting directions in fx_pairs.yaml are best-effort per the design brief; the operator verifies them against live FRED metadata at ingest (F-444 §14.1 / TASK-3147).

Analyst Estimates & Earnings Surprises

Dataset Discriminator Scope Silver Table Key Columns Refresh API Path
analyst-estimates annual per_ticker fmp_analyst_estimates ticker, fiscal_year, fiscal_quarter, as_of_date Weekly (7, Sat) analyst-estimates?symbol=__ticker__&period=annual&limit=10
analyst-estimates quarter per_ticker fmp_analyst_estimates ticker, fiscal_year, fiscal_quarter, as_of_date Weekly (7, Sat) analyst-estimates?symbol=__ticker__&period=quarter&limit=20
earnings-surprises — per_ticker fmp_earnings_surprises ticker, date Quarterly (90, Sat) earnings?symbol=__ticker__
  • analyst-estimates: Forward-looking consensus estimates (EPS, revenue, EBITDA, net income) with high/low ranges and analyst count. Two recipes share one Silver table: discriminator=annual writes fiscal-year rows (fiscal_quarter=0); discriminator=quarter writes quarterly rows (fiscal_quarter=1..4). fiscal_year/fiscal_quarter are derived in AnalystEstimatesDTO.transform_df_content from the period-end date using a row-density heuristic (annual if rows/unique_years < 2). 2026-04-14: migrated from the retired v3 endpoint to /stable/analyst-estimates; upstream field names flattened (epsAvg, revenueAvg, ebitdaAvg, numAnalystsEps) and mapped back to unchanged Silver column names via api: keys in dto_schema. TASK-3001: as_of_date (synthesized by SilverService from the Bronze manifest coverage date — row_date_col stays unset) was appended to the natural key of BOTH recipe blocks, so the weekly Saturday re-pull persists a weekly consensus time-series (one row per snapshot date) instead of UPSERT-overwriting a single row per (ticker, fiscal_year, fiscal_quarter). AnalystEstimatesDTO.KEY_COLS is kept in lockstep.
  • earnings-surprises: Historical actual-vs-estimated EPS per reporting date. Feeds gold.fact_earnings_surprises and is aggregated into beat-rate statistics for gold.fact_eps_forecast. 2026-04-14: migrated from retired v3 /api/v3/earnings-surprises to /stable/earnings; fields epsActual / epsEstimated map to Silver actual_earning_result / estimated_earning.
  • Key fields — analyst-estimates: estimated_eps_avg, estimated_eps_high, estimated_eps_low, estimated_revenue_avg, estimated_revenue_high, estimated_revenue_low, estimated_ebitda_avg, estimated_net_income_avg, number_analyst_estimated
  • Key fields — earnings-surprises: actual_earning_result, estimated_earning
  • Symbol filter: Both use symbol_filter_col: ticker — ticker, not symbol (B-041.1 / TASK-3241): symbol_filter_col names the Silver column the allowlist gate filters on, and these DTOs project ticker. It read symbol until 2026-08, which made the Silver gate a silent no-op here (real enforcement was upstream in IngestPlannerService, which only ever requests allowlisted symbols). See CLAUDE.md §2 constraint 10.
  • Gold: Drives three Gold fact tables — fact_analyst_estimates, fact_earnings_surprises, fact_eps_forecast (the last built by EpsForecastService).
  • Documentation: FMP /stable/ Analyst Estimates | FMP /stable/ Earnings

Analyst Grades — sell-side rating actions (F-450 / EC-5)

Dataset Scope Silver Table Key Columns Refresh API Path
grades per_ticker fmp_grades ticker, date, grading_company, new_grade Weekly (6, Sat) grades?symbol=__ticker__&limit=5000
  • Purpose (F-450 / TASK-3220…3226): firm-level sell-side rating actions — one row per grading firm × action. This is the platform’s first analyst rating feed; analyst-estimates above is forward consensus numbers (EPS / revenue / EBITDA), never a rating. DTO AnalystGradeDTO (sbcontracts.dtos.fundamentals.analyst_grade_dto) maps symbol/date/gradingCompany/previousGrade/newGrade/action → ticker/date/grading_company/previous_grade/new_grade/action.
  • new_grade is IN the natural key — deliberate. The obvious key (ticker, date, grading_company) is not unique: measured on AAPL, 2 collisions in 1,786 records (one byte-identical Jefferies duplicate, one genuine Wells Fargo Perform-vs-Market Perform variant on the same day). Adding action does not disambiguate. AnalystGradeDTO.KEY_COLS, the keymap key_cols, and the Silver PK are kept in lockstep.
  • first_seen_at is the survivorship receipt. Declared via the keymap’s insert_only_cols: [first_seen_at], so SilverService stamps it on INSERT and excludes it from the MERGE’s WHEN MATCHED THEN UPDATE SET clause — every other column (incl. the lineage trio) refreshes on re-promote, this one never does. It is the only durable evidence that this platform held a record before the vendor deleted it.
  • lifecycle_scope: all (B-450.2 / TASK-3649) — the ONLY dataset that carries it. Every other per-ticker dataset resolves its fetch universe as Allowlist ∩ is_actively_trading = TRUE (a join to silver.fmp_company_profile_bulk in sbbronze.domain_ingest._allowlist.resolve_allowlisted_tickers), which is correct for price history — a delisted name has none to fetch. It is self-defeating here: this feed exists because FMP purges grades as names age out, and the coverage detector below compares successive observations of a symbol, so never re-fetching the delisted cohort makes a purge of exactly those names undetectable. Measured 2026-08-30, the default resolved 6,410 of 9,831 allowlisted symbols, stranding 3,385 — 2,802 of them delisted — on a single observation. The default’s stated premise is also false for this endpoint: run 260818_7b8941 fetched all 9,731 symbols, HTTP 200 on every one, and 1,382 of the excluded names returned records (77,929 in total). Costs ~15 min per pull at ~230 files/min; the FMP 3,000/min throttle is not binding. EowBronzeIngester partitions its per-ticker recipes by scope and drives one Bronze batch per distinct scope, so the six other per-ticker EOW datasets are provably unaffected (pinned by a byte-identical-SQL assertion in tests/unit/sbbronze/test_lifecycle_scope_allowlist.py).
  • silver.fmp_grades_coverage — the vendor-purge detector. A derived Silver table (migration 20260818_004), not a keymap dataset — same status as silver.fmp_promotion_allowlist. One observation row per (ticker, pulled_at) carrying n_records, min_date, max_date, run_id. Written fail-open by GradesCoverageService (src/sbsilver/coverage/) from SilverService._project_artifact; a coverage failure logs a WARNING and the promote continues. Purely observational — nothing gates on it. It exists because the idempotent UPSERT promote can only ever make silver.fmp_grades grow, so a vendor purge leaves no trace there at all; a falling n_records or a forward-moving min_date for a symbol is the only visible purge signal. min_date/max_date are nullable so the most important observation of all — a total purge, n_records = 0 with no span — is representable.
  • Gold: gold.fact_analyst_grade (migration 20260818_005), PK (instrument_sk, date_sk, grading_company, new_grade), built by GoldFactService._build_fact_analyst_grade. Unlike every sibling builder in that file, the symbol → instrument_sk join is lifecycle-aware — explicit ipo_date / delisting_date predicates, so a rating can only attach to an instrument that was alive on the rating’s own date. Without them, ticker recycling staples a live company’s ratings onto a dead instrument_sk (measured: 3% of covered delisted names carry post-delisting records; P / Pandora Media, delisted 2019-01-31, returns 240 records through 2026-08-13 belonging to whoever now holds the ticker). Unresolved rows are counted and logged in two classes (symbol absent from dim_instrument vs instrument not alive on the grade date) rather than silently dropped.
  • grade_ordinal — fail-open, NULLABLE on purpose. config/analyst_grade_scale.yaml (loaded by sbcontracts.analyst_grade_scale) maps the bounded 23-string vendor vocabulary onto a 5-point ordinal (1 bearish … 3 neutral … 5 bullish). An unmapped string resolves to NULL and is logged once per distinct value — never coerced to the neutral midpoint, which would fabricate a “Hold” no analyst ever issued. The raw new_grade stays authoritative; the ordinal is derived and disposable, so a mapping judged wrong later is fixed additively in the YAML without touching the fact.
  • Deliberately NOT a factor: no factor YAML, no ACTIVE_FACTOR_IDS change, no gate math, no enforcement switch, no SPA surface. Display / diagnostics only.
  • HARD NON-GOAL — do not backtest against the vendor’s 2012–2026 history. FMP purges grade history continuously as names age out of the listed universe. Measured 2026-08-18: liquidity-matched delisted names show 35% coverage against a 91% live-liquid baseline (a 56pp gap), and 2016 / 2017 / 2018 delistings return exactly 0%. Beyond roughly three years back this is a survivor list, not a panel — and the missing names are disproportionately failures, so any factor measured on it looks better than reality and would clear the promotion gates for the wrong reason (the gates test whether a signal is real, not whether the panel is complete). The platform’s own accumulated history starts 2026-08 and becomes honestly testable in roughly four quarters. This is the first dataset ingested specifically because the vendor is known to delete it — hence the urgency is data decay, not feature demand.
  • Symbol filter: symbol_filter_col: ticker — ticker, not symbol (B-041.1 / TASK-3241); it names the Silver column, and AnalystGradeDTO projects ticker. See CLAUDE.md §2 constraint 10.
  • Plan variant: SymbolListPlan — a per-ticker full-history feed with no date pagination, so max_date_quantity is null by construction. max_symbol_quantity is also null deliberately: IngestPlannerService._dispatch_variant truncates the symbol list alphabetically at the cap, so any finite value would permanently pin the feed to the alphabetically-first N symbols. Conservatism rides on the weekly cadence gate (run_days: [sat] + min_age_days: 6) instead.
  • State series, not an event series: reiterations dominate — all-history 80% maintain / 10% upgrade / 10% downgrade; rating changes are sparse (~15/trading day across the liquid universe) while rating state is dense (~173 events/trading day).
  • Documentation: FMP /stable/ Grades

Revenue Segmentation (product / geographic)

Dataset Discriminator Scope Silver Table Key Columns Refresh API Path
revenue-product-segmentation FY per_ticker fmp_revenue_product_segmentation ticker, fiscal_year, segment Yearly (365, Sat) revenue-product-segmentation?symbol=__ticker__&period=annual
revenue-geographic-segmentation FY per_ticker fmp_revenue_geographic_segmentation ticker, fiscal_year, segment Yearly (365, Sat) revenue-geographic-segmentation?symbol=__ticker__&period=annual
  • TASK-3175: annual (period=annual, discriminator: FY) revenue breakdowns. The vendor returns one object per (symbol, fiscal year) carrying a nested data dict whose keys are dynamic segment names ({"iPhone": 209586000000, "Mac": ...} / {"Americas": ..., "Europe": ...}). RevenueSegmentationDTO.transform_df_content melts that dict 1→N into one Silver row per segment, lifting the parent identity columns onto every child (symbol→ticker, fiscalYear→fiscal_year, date, reportedCurrency→reported_currency). Audit-first: a malformed/absent data payload contributes zero rows and never raises.
  • Shared DTO, separate tables: both datasets map to the single RevenueSegmentationDTO (identical vendor shape) but — unlike analyst-estimates — each writes its own Silver table, so a product segment and a geographic segment of the same name never collide on the (ticker, fiscal_year, segment) natural key.
  • domain: eow is load-bearing: sbshared.settings.DOMAINS is [eod, quarter, annual, eow, etf]; a recipe whose domain is absent from that list is silently skipped with no error. A 2026-03 iteration of these very datasets used domain: fundamentals and consequently never produced a single row.
  • Key fields: segment (dynamic vendor segment name, part of the natural key), revenue, reported_currency, date (row_date_col, the fiscal period-end).
  • Symbol filter: both use symbol_filter_col: ticker — ticker, not symbol (B-041.1 / TASK-3241); it names the Silver column, and RevenueSegmentationDTO projects ticker. See CLAUDE.md §2 constraint 10.
  • Gold: both feeds promote into the single gold.fact_revenue_segment (TASK-3178), one row per (instrument_sk, period_date_sk, segment_kind, segment_name) where segment_kind discriminates 'product' vs 'geographic'. segment_name is a degenerate text column — vendor labels stored verbatim, never resolved to a dimension. Display-only: no _f column, no factor lift, ACTIVE_FACTOR_IDS untouched; the sole consumer is sbscreen (the screen_segments.json sidecar behind GET /api/screen-segments).
  • Documentation: FMP /stable/ Revenue Product Segmentation | FMP /stable/ Revenue Geographic Segmentation

Equity Short Interest

Dataset Source Scope Silver Table Key Columns Refresh
equity-short-interest finra global finra_equity_short_interest symbol, settlement_date Weekly (7, Sat)
  • equity-short-interest: FINRA bi-monthly equity short interest regulatory files. Free, auth-free CSV files fetched by FinraFileAdapter from settlement-date-templated URLs. Feeds short interest analytics.
  • Fields: symbol, settlement_date, plus FINRA short interest columns.
  • Symbol filter: symbol_filter_col: symbol
  • No API key required — FINRA publishes auth-free regulatory data.
  • Gold: gold.fact_short_interest (F-070 / TASK-061) — built by GoldFactService._build_fact_short_interest() after _build_fact_annual() in the Gold core phase. Three _f features: short_interest_delta_f, short_interest_change_pct_f, and short_pct_float_f (the last uses fact_annual.weighted_average_shs_out_diluted as a proxy for true float via a point-in-time LEFT JOIN; resolves to NULL when no annual report is yet available).
  • Factor layer (F-299): gold.fact_short_interest now also feeds the factor pool — the platform’s first positioning-style factors, days_to_cover (a new days_to_cover_f column) and short_pct_float (reuses the existing short_pct_float_f column above), both direction: negative, experimental. FactorValueLiftService snaps each factor row’s knowledge date_sk forward to the first US market day on/after settlement_date + 18 calendar days (FINRA’s ~16-18 day dissemination lag) via a new TABLE_DATE_SK_EXPR["fact_short_interest"] entry — no dataset/schema/cadence change here, consumer-side only.
  • Ingestion cadence / n_obs accrual-rate context (TASK-2678): the keymap recipe’s data_source_path (equity/otcmarket/biweekly/shrt{settlement_date}.csv) and run_days: [sat] confirm FINRA settles this file bi-weekly — twice a month, one file per settlement date — not daily. days_to_cover / short_pct_float are nonetheless evaluated by sbdiag.FactorDiagnosticsService against the daily-cadence min_n_obs=60 bar (fact_short_interest is absent from sbdiag.settings.SOURCE_TABLE_CADENCE, so _resolve_cadence falls back to "daily"). At ~2 new settlement dates per month, organic forward accrual alone would take roughly 2.5 years (60 obs ÷ ~2 obs/month) to clear that bar — the 17 observed dates behind the original TASK-2533/2534/2535 finding came from a historical backfill, not from nightly accrual, and further backfill (not the passage of nightly runs) is what will actually move these two factors past min_n_obs. This is a genuine cadence mismatch in the same family as the F-292/LH-26 annual/quarterly-factor defect, but is deliberately not fixed by TASK-2678 — the fix here (insufficient_data status + auto-promote) works correctly regardless of which cadence bucket a factor is evaluated against; reclassifying fact_short_interest’s cadence bucket (e.g. a new "biweekly" CADENCE_PARAMS entry) is a separate, out-of-scope follow-on if the ~2.5-year accrual horizon proves unacceptable in practice.
  • Documentation: FINRA Equity Short Interest

ETF Reference Data (F-343 / EU-2)

Dataset Source Scope Silver Table Key Columns Refresh API Path
etf-info fmp per_ticker fmp_etf_info ticker Weekly (Sat) etf/info?symbol=__ticker__
etf-holdings fmp per_ticker fmp_etf_holdings ticker, asset Weekly (Sat) etf/holdings?symbol=__ticker__
  • Domain: etf (new recipe domain registered by F-343). Both datasets are Silver-only (no Gold promotion) — reference data for sector-concentration / correlation modeling.
  • etf-info: One row per ETF profile (expense ratio, AUM, asset class, domicile, inception, sector/country exposure summary). DTO EtfInfoDTO.
  • etf-holdings: N rows per ETF, one per underlying holding (asset symbol, name, weight, shares, market value). DTO EtfHoldingsDTO.
  • Symbol scope: both carry the declarative symbol_scope: etf keymap field, which scopes ingestion via IngestPlannerService to is_etf=TRUE allowlist symbols only (gated on SB_ALLOW_ETFS=ON, on since F-247).
  • Gold: none. Gold facts + holding→instrument_sk resolution + holdings-weighted proxies are deferred to EU-4.
  • Weekly cadence (F-355 / EU-5 → F-356 / EU-6): both datasets are folded into the EOW cadence bucket via the executor’s EOW Bronze+Silver phase (merged into result.eow). They auto-ingest deterministically on the weekly deep run (Friday NIGHTLY_FULL, or an ad-hoc --research-mode nightly-full), where the ETF nightly seam rides force_weekly=True → bypass_run_days=True past the run_days:[sat] weekday gate — and past the min_age_days:6 floor with it, since that flag nulls the ingestion watermark (B-102.1; the earlier “min_age_days:6 still gates” was false). A deep run is an operator-requested weekly refresh, so forcing it is the intent; reconciling the two BronzeService cadence paths is Fix C, deferred. This corrects F-355’s wording — the seam previously used bypass_run_days=False and so was rejected on every scheduled Mon–Fri nightly; it now actually fires each Friday. Renders under the weekly group on the Data-hub inventory. The ingest-etf CLI remains the on-demand path.
  • Tier-4: the live Bronze pull + constraint-7 field-casing verification + Silver promote is an operator step (TASK-2948).
  • Documentation: FMP /stable/ ETF Info | FMP /stable/ ETF Holdings

Gold Layer Summary

Gold tables are built from Silver data by GoldDimService, GoldFactService, and GoldIndexService. No Silver table is modified during Gold promotion.

Dimension Tables

Table Type Source
dim_date Static SQL bootstrap
dim_instrument_type Static SQL bootstrap
dim_country Static SQL bootstrap
dim_exchange Static SQL bootstrap
dim_sector Static SQL bootstrap
dim_industry Static SQL bootstrap
dim_index Static SQL bootstrap (SP500, NASDAQ100, DJIA)
dim_instrument Data-derived company-profile-bulk + company-delisted
dim_company Data-derived company-profile-bulk

Fact Tables

Table Source Datasets Grain
fact_eod eod-bulk-price (+ _f feature columns computed by EodFeatureService / TechnicalFeatureService, incl. F-266 seasonality_same_month_f — same-calendar-month return seasonality, Heston-Sadka 2008) (instrument_sk, date_sk)
fact_quarter quarterly income + balance sheet + cashflow + key metrics (instrument_sk, period_date_sk, period)
fact_annual annual income + balance sheet + cashflow + key metrics + ratios (instrument_sk, period_date_sk)
fact_stock_split stock-split (instrument_sk, split_date)
fact_index_constituent sp500/nasdaq100/djia constituent history (index_sk, instrument_sk, effective_from)
fact_analyst_estimates analyst-estimates (annual + quarter) (instrument_sk, fiscal_year, fiscal_quarter)
fact_earnings_surprises earnings-surprises (instrument_sk, date_sk)
fact_eps_forecast analyst-estimates + earnings-surprises (instrument_sk, fiscal_year, fiscal_quarter)
fact_fundamental_annual annual fundamentals + market cap (built by FundamentalFactorService) (instrument_sk, fiscal_year)
fact_signal_score _f predictors from fact_eod + fact_fundamental_annual (built by MLSignalService); one row per (instrument_sk, score_date_sk, model_id) so multiple algorithm/hyperparam variants coexist (instrument_sk, score_date_sk, model_id)
fact_portfolio_target per-instrument target weights from a portfolio construction algorithm (built by PortfolioConstructionService); one row per (portfolio_id, instrument_sk, rebalance_date_sk) so multiple algorithm/hyperparam variants coexist (portfolio_id, instrument_sk, rebalance_date_sk)
fact_analyst_grade grades (F-450) — lifecycle-aware ipo_date/delisting_date instrument join; grade_ordinal NULLABLE (unmapped vendor strings resolve to NULL, never coerced). Display / diagnostics only — never a backtest panel (see the EOW Analyst Grades section) (instrument_sk, date_sk, grading_company, new_grade)
fact_screener month-end snapshot joining fact_eod price/technical features + fundamentals + composite_signal_s from fact_signal_score + target_weight_w from fact_portfolio_target (instrument_sk, rebalance_date_sk)

Fundamentals knowledge-date dissemination lag (F-442 / LH-29, default OFF): the factor knowledge date_sk derived from the four reported-fundamentals tables — fact_fundamental_quarter, fact_fundamental_annual, fact_valuation_annual, fact_moat_annual — carries an optional dissemination lag (switch SB_FUNDAMENTALS_DISSEMINATION_LAG_ENABLED, sbfactors.settings; default OFF → byte-identical). When ON, the knowledge date is pushed to period-end + N calendar days (quarterly +60d / annual +90d) snapped forward to the first US market day, so factor values are stamped on-or-after the provable SEC filing date (closing the F-319 EDGAR PIT look-ahead). Announcement-grain / estimate / short-interest tables (fact_earnings_momentum, fact_eps_forecast, fact_short_interest) are excluded — already PIT-safe.

Event-dated factor forward-fill onto the daily grid (F-359): a factor may declare research.forward_fill_market_days: N (factor YAML research: block). When present, the lift emits daily-dense gold.fact_factor_value rows — carrying each event value forward from its knowable date_sk up to N market days, capped at the next event, carry-forward only (PIT-safe). sue_f declares 63 md (PEAD drift window, anchored on the earnings-announcement date), so sue_f is daily-dense in fact_factor_value while its source table fact_earnings_momentum stays event-grain (one row per announcement). earnings_growth_qoq_f also declares 63 md since TASK-3118 and is likewise daily-dense in fact_factor_value while its source fact_fundamental_quarter stays quarter-grain — but only because the dissemination lag above is enforced: the lift anchors the carry on the RESOLVED knowledge date_sk (first US market day on/after period-end + 60 calendar days), never the quarter-end period_date_sk, which is a period-END proxy ~40–60 days before the actual SEC filing. With the lag switch OFF the same declaration would carry each quarter’s value across the whole pre-filing window — a look-ahead wider than the pre-F-359 one. Factors without the key stay event-grain in fact_factor_value, byte-identical to the exact-date_sk lift.

Silver-Only Datasets (No Gold Promotion)

Silver Table Reason
fred_dgs10 No instrument_sk FK — macro time-series
fred_usrecm No instrument_sk FK — macro indicator
fred_dgs1mo No instrument_sk FK — macro time-series
fred_dgs3mo No instrument_sk FK — macro time-series
fred_dgs1 No instrument_sk FK — macro time-series
fred_dgs2 No instrument_sk FK — macro time-series
fred_dgs5 No instrument_sk FK — macro time-series
fred_dgs30 No instrument_sk FK — macro time-series
fred_t10y2y No instrument_sk FK — macro time-series
fred_baa10y No instrument_sk FK — macro time-series
fred_vixcls No instrument_sk FK — macro time-series
fred_dexuseu / fred_dexusuk / fred_dexusal / fred_dexusnz / fred_dexcaus / fred_dexjpus / fred_dexnous / fred_dexszus / fred_dexbzus / fred_dexmxus / fred_dexchus / fred_dexinus / fred_dexkous / fred_dexsius / fred_dextaus / fred_dexsdus / fred_dexdnus / fred_dexsfus / fred_dexhkus / fred_ccusma02ilm618n No instrument_sk FK — FX rate time-series (TASK-3143; ILS via OECD monthly CCUSMA02ILM618N, task_6f964919)
fmp_market_risk_premium No instrument_sk FK — country-level data
kenneth_french_factors No instrument_sk FK — market-wide factor return series
fmp_etf_info Reference data (F-343 / EU-2); Gold facts + holding→instrument_sk resolution deferred to EU-4
fmp_etf_holdings Reference data (F-343 / EU-2); Gold facts + holding→instrument_sk resolution deferred to EU-4

Bespoke reference tables (not in config/dataset_keymap.yaml). The following Silver table is populated by a dedicated fetch→Silver service that bypasses the Bronze recipe engine entirely, so it has no keymap entry and no Bronze parquet behind it. Its provenance is a lightweight fetched_at / source_url pair rather than the standard §10.4 3-column Bronze lineage:

  • kenneth_french_factors (F-316): the Kenneth R. French Data Library factor-return series — Fama-French 5-factor (mkt_rf/smb/hml/rmw/cma), Carhart momentum (umd), and the risk-free rate (rf), keyed on (date, frequency) with frequency ∈ {daily, monthly}. Fetched via sbops.kenneth_french_service.KennethFrenchFactorService (stdlib urllib/zipfile, no new runtime dependency); values are stored as decimals (French serves percent). The live download is a Tier-4 operator step. A keymap identity entry was deferred (it would require editing the F-102 exact-count inventory guard for no runtime benefit — the table is populated out of band).

Designed Earliest Date

The designed_earliest_date field on each dataset entry in config/dataset_keymap.yaml is the per-dataset commitment of how far back the platform aspires to ingest. Used by the Combined Coverage Heatmap (F-083) to draw the green per-row marker and to contextualize gaps in MIN(coverage_from) against design intent. null indicates a forward-looking or snapshot dataset where historical depth does not apply.

Dataset Discriminator Designed Earliest Notes
eod-bulk-price — 1980-01-01 Matches platform FROM_DATE.
eod-price-history — 1980-01-01 Per-ticker delisted backfill.
company-profile-bulk — — Snapshot.
company-delisted — — Snapshot.
income-statement-bulk-quarter — 1985-01-01 Conservative per-symbol floor.
balance-sheet-bulk-quarter — 1985-01-01
cashflow-bulk-quarter — 1985-01-01
key-metrics-bulk-quarter — 1985-01-01
income-statement-bulk-annual — 1985-01-01
balance-sheet-bulk-annual — 1985-01-01
cashflow-bulk-annual — 1985-01-01
key-metrics-bulk-annual — 1985-01-01
ratios-bulk-annual — 1985-01-01
sp500-constituent-history — — Change-log of current-as-of constituents.
nasdaq100-constituent-history — — Change-log.
djia-constituent-history — — Change-log.
stock-split — 1980-01-01
analyst-estimates annual — Forward-looking.
analyst-estimates quarter — Forward-looking.
earnings-surprises — 1980-01-01 Pull-back to platform floor.
revenue-product-segmentation FY — Vendor returns full available FY history per symbol.
revenue-geographic-segmentation FY — Vendor returns full available FY history per symbol.
market-risk-premium — — Snapshot.
fred-dgs10 — 1962-01-02 FRED series start.
fred-usrecm — 1900-01-01 Clamped to align with the pre-1960 bucket.
fred-dgs1mo — 2001-07-31 FRED series start.
fred-dgs3mo — 1982-01-04 FRED series start.
fred-dgs1 — 1962-01-02 FRED series start.
fred-dgs2 — 1976-06-01 FRED series start.
fred-dgs5 — 1962-01-02 FRED series start.
fred-dgs30 — 1977-02-15 FRED series start.
fred-t10y2y — 1976-06-01 FRED series start.
fred-baa10y — 1986-01-02 FRED series start.
fred-vixcls — 1990-01-02 FRED series start.
fred-dexuseu … fred-dexhkus (19 daily DEX*) + fred-ccusma02ilm618n (ILS, monthly OECD) — 1971-01-04 (DEX*) / 1957-01-01 (ILS) Conservative floor; FRED returns from each series’ actual start (TASK-3143; ILS is OECD monthly, task_6f964919).
equity-short-interest — 2007-01-01 FINRA reporting begins 2007.

Vendor Earliest Date

The vendor_earliest_date field is the hard runtime floor on the dataset — the earliest period the upstream vendor actually serves data for. Distinct from designed_earliest_date (aspirational depth). Consumed by the F-082 quarter_ingest_schedule and annual_ingest_schedule prechecks (TASK-325) to clamp the history-extension window to max(FROM_DATE, vendor_earliest_date), so the schedule never asks FMP for a period it returns empty for.

Seeded from observed MIN(date) of each Silver table after multiple nightly runs. Update this field when the vendor extends coverage (lowering the floor) or when we learn of a stricter floor than what’s currently set. null (or omitted) means no known floor; the precheck falls back to FROM_DATE.

Dataset Vendor Earliest Source of observation
income-statement-bulk-quarter 1984-12-31 silver_min on run 260513_7e0c61
balance-sheet-bulk-quarter 1983-06-30 silver_min on run 260513_7e0c61
cashflow-bulk-quarter 1985-06-30 silver_min on run 260513_7e0c61; FMP returned empty for all 4 quarters in 1984.
key-metrics-bulk-quarter 1983-06-30 silver_min on run 260513_7e0c61
income-statement-bulk-annual 1983-12-31 FMP returned empty for year=1982 on run 260513_7e0c61.
balance-sheet-bulk-annual 1983-12-31 same
cashflow-bulk-annual 1983-12-31 same
key-metrics-bulk-annual 1983-12-31 same
ratios-bulk-annual 1983-12-31 same

Per-Dataset Ingest Caps (F-102)

The max_date_quantity and max_symbol_quantity keymap fields (F-102) replace the four global INGEST_*_COUNT constants formerly in sbshared.settings. The planner (sbbronze.ingest_planner.IngestPlannerService) reads these caps to decide:

  • DateList variant — (ticker_scope=global, date_key set, max_date_quantity > 1). Planner returns up to max_date_quantity ISO dates to ingest.
  • SnapshotGate variant — (date_key=null OR max_date_quantity=null OR max_date_quantity=1). Planner returns a bool gate (ingest yes/no).
  • SymbolList variant — (ticker_scope=per_ticker). Planner returns up to max_symbol_quantity symbols (or all allowlisted when null).
Dataset Discriminator Variant max_date_quantity max_symbol_quantity
eod-bulk-price — DateList 20 —
company-profile-bulk — Snapshot null —
income-statement-bulk-quarter — DateList 4 —
balance-sheet-bulk-quarter — DateList 4 —
cashflow-bulk-quarter — DateList 4 —
income-statement-bulk-annual — DateList 4 —
balance-sheet-bulk-annual — DateList 4 —
cashflow-bulk-annual — DateList 4 —
key-metrics-bulk-quarter — DateList 4 —
key-metrics-bulk-annual — DateList 4 —
ratios-bulk-annual — DateList 4 —
fred-dgs10 — DateList 8 —
fred-usrecm — DateList 8 —
fred-dgs1mo — DateList 8 —
fred-dgs3mo — DateList 8 —
fred-dgs1 — DateList 8 —
fred-dgs2 — DateList 8 —
fred-dgs5 — DateList 8 —
fred-dgs30 — DateList 8 —
fred-t10y2y — DateList 8 —
fred-baa10y — DateList 8 —
fred-vixcls — DateList 8 —
fred-dexuseu … fred-dexhkus (19 daily DEX*) + fred-ccusma02ilm618n (ILS monthly) — DateList 8 —
market-risk-premium — Snapshot null —
company-delisted — Snapshot null —
mergers-acquisitions — Snapshot null —
sp500-constituent-history — Snapshot null —
nasdaq100-constituent-history — Snapshot null —
djia-constituent-history — Snapshot null —
stock-split — SymbolList null null
eod-price-history — SymbolList null null
analyst-estimates annual SymbolList null null
analyst-estimates quarter SymbolList null null
earnings-surprises — SymbolList null null
grades — SymbolList null null
revenue-product-segmentation FY SymbolList null null
revenue-geographic-segmentation FY SymbolList null null
equity-short-interest — DateList 8 —

When introducing a new dataset, set max_date_quantity per the variant intent. Defaults: 20 for daily EOD-style data, 8 for weekly cadences, 4 for quarterly/annual fundamentals, null for snapshots.

Self-healing gap-fill (O-093 / TASK-2812): the nightly bulk EOD ingest plan (eod-bulk-price) forward-fills up to max_date_quantity (20) missed market days per night, so a single run can absorb roughly a four-week outage. In the no-backlog steady state the plan is still just [today] (or [] on a non-market day); the cap only affects the saturated/outage path.

Quick Reference

Metric Count
Total datasets (keymap entries) 59
DateList variant (F-102) 42
Snapshot variant (F-102) 7
SymbolList variant (F-102) 10
Global-scope datasets 49
Per-ticker datasets 10 (stock-split, eod-price-history, analyst-estimates ×2, earnings-surprises, grades, etf-info, etf-holdings, revenue-product-segmentation, revenue-geographic-segmentation)
FMP datasets 27
FRED datasets 31
FINRA datasets 1
Daily refresh (eod) 3
Weekly refresh (Sat, eow) 40
Monthly refresh 2
Quarterly refresh 2
Yearly / cadence refresh 2
Gold fact tables 13 (listed above; fact_short_interest + fact_revenue_segment also exist and are documented in their own dataset sections)
Gold dimension tables 9 (7 static + 2 data-derived)