lake.sec_daily_ohlc dataset schema

Columns and types

These columns come from the same versioned logical catalog used by the query validator and engine. They describe the fields you can query. Some historical rows have missing values; fields absent from older shards can return typed NULL.

No matching rows. Clear the filter to see all records.

Current logical schema for lake.sec_daily_ohlc

Coverage and timing

Equity daily OHLC + light fundamentals (SEC SoT).

Covers the tradable STOCK + ETF universe. ETFs and other non-filers (SPY, QQQ, USO) are present as price-only rows: OHLC and volume are populated, `peRatioTTM` / `psRatioTTM` / `pbRatioTTM` and other statement-linked fields are usually NULL. A null ratio means the issuer files no statements, not that the ticker is missing — query ETF price history exactly like equities. `dividendYield` is ALREADY a percentage. Two percent is `dividendYield > 2`, not `> 0.02`. `marketCap` is `unadjustedClose * sharesOutstanding` as of that date, and is NULL for ETFs and for filers whose only share figure is a period average. Filter with `marketCap IS NOT NULL` rather than treating NULL as zero. Never re-derive market cap from `closingPrice`, which is split- and dividend-adjusted — that basis mismatch reported a $31B company as $31,363. `sharesAsOf` is when the share count was measured, not when it became public; between filings it is the precision bound on `marketCap`. `sharesAvailableAt`, `sharesAccession`, and `sharesSourceFilingUrl` identify the selected share source; `sharesOutstandingScope` distinguishes issuer-wide, class-specific, and unqualified vendor counts. Older shards may have NULL source lineage. For a multi-class issuer, an issuer-wide count multiplied by one listed class's price is only a one-price company-value approximation, not a class-weighted valuation or independent confirmation of the share count. Latest screens SHOULD pin to the newest session with full coverage, not raw `MAX(date)`: a partially published session can sit at the tip with a fraction of the universe. Use `WHERE date::DATE = (SELECT session FROM latest_full)` with `WITH latest_full AS (WITH sessions AS ( SELECT date::DATE AS session, COUNT(DISTINCT ticker) AS tickers FROM lake.sec_daily_ohlc WHERE date::DATE >= (SELECT MAX(date::DATE) FROM lake.sec_daily_ohlc) - INTERVAL 45 DAY GROUP BY 1 ), scored AS ( SELECT session, tickers, MEDIAN(tickers) OVER (ORDER BY session ROWS BETWEEN 20 PRECEDING AND 1 PRECEDING) AS trailing_median FROM sessions ) SELECT MAX(session) AS session FROM scored WHERE trailing_median IS NULL OR tickers >= 0.9 * trailing_median)`. The daily tip is only admitted after the SEC price refresh finalizes a session (21:00 America/New_York); in-progress Polygon bars are never written. Do NOT write `date < CURRENT_DATE` — that lags a finished tip by a calendar day after the refresh lands. This table is end-of-day closes, not live quotes. `ticker` is the symbol the security traded under ON THAT DATE. A company that changed ticker keeps its earlier rows under its earlier ticker (Facebook is `FB` before 2022-06-09 and `META` after; Cabot Oil & Gas is `COG` until 2021-10-01, then Coterra is `CTRA`), and a reused ticker holds a different company before its current holder listed (`CTRA` in 2019 is Contura Energy). For one company's history across a rename, join `lake.security_segments` on `ticker` and the date span and filter its `canonical_symbol`, e.g. `JOIN lake.security_segments s ON o.ticker = s.ticker AND o.date::DATE BETWEEN s.from_date AND COALESCE(s.to_date, DATE '9999-12-31') WHERE s.canonical_symbol = 'META'`.

Run a limited query

Use a registered API key with lake scope. Select needed columns, bind user-supplied values and restrict dates when the table has a time dimension. Query results are durable parts with a schema and manifest rather than an unbounded in-memory array.

This reference documents the schema and its meaning. Run queries in your signed-in workspace; this page does not execute SQL or show private results and datasets.

SELECT "ticker", "symbol", "date"
FROM lake.sec_daily_ohlc
LIMIT 20;