lake.political_trade_events 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.political_trade_events

Coverage and timing

Canonical deduplicated, versioned congressional trade events. Preferred for counts, signals, rankings, profitability, and forward performance.

One row per version of a congressional trade, built from `political_trades`. Repeated reports of the same trade fold into one event, and an amendment that changes one field of it adds a version. Use this table to count or sum trades. Events are built per member of Congress, and only for members: `memberId` (Bioguide ID) is never NULL here and names one person across name spellings and chambers. Identify, group, rank and filter politicians by `memberId` and show `displayName`; `filerFirst` and `filerLast` are the name as filed on the row behind the version. When the question names a person, a `# Politician identity` section carries the resolved `memberId` — filter by that literal and never re-derive identity from `displayName`. Point-in-time: a trade counts from `firstAvailableAt`, and a version's values are public from `availableAt` until `supersededAt` (NULL means current). As of a date, read `availableAt <= as_of AND (supersededAt IS NULL OR supersededAt > as_of)`; trades disclosed in a window have `firstAvailableAt` inside it. Shards are yearly on `availableAt`, so a later version can sit in a later year's shard. Default methodology: when a user asks which politician was most profitable, performed best, earned the highest return, or asks a similar performance question without specifying a method, return the lookahead-free, realistically tradable result. Use transaction-date or same-day-close attribution only when explicitly requested, and label it as ex-post and non-tradable. Tradable forward returns: `firstAvailableAt` is the end of the filing date in America/New_York, so that date's closing price was already fixed before the disclosure became observable. The entry bar must be the first `sec_daily_ohlc` session with `price.date::DATE > CAST(firstAvailableAt AS DATE)`, never `>=`. Derive the requested horizon from later rows of that ticker's session sequence (for example `LEAD(..., 90)` for 90 trading sessions), not calendar-day equality. Compute a benchmark such as SPY over the exact realized entry and exit session dates used by each trade. `assetTypeLabel` is nullable and source wording varies in case. Excluding options must keep NULL labels, for example `assetTypeLabel IS NULL OR LOWER(assetTypeLabel) NOT LIKE '%option%'`; bare `NOT LIKE` silently drops NULL rows. `ticker` is the printed ticker, else the resolved one (see `political_trades.resolutionStatus`), uppercased, and NULL when neither exists. It is what the `PoliticalTrades` indicator matches to an asset, after the engine maps a renamed company's earlier ticker to its current one. `sourceDocId` and `sourceRowIndex` name the `political_trades` row a version's values come from, `sourceUrl` is that filing's original government page or PDF, and `contributorRowIds` is a JSON array of every row behind it as `docId:rowIndex`.

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 "eventId", "version", "chamber"
FROM lake.political_trade_events
LIMIT 20;