lake.political_trade_events dataset schema

Scroll sideways to compare all columns. Select a heading to sort.

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

Current logical schema for lake.political_trade_events

Query lake.political_trade_events using the column names and types below. The query validator and engine use this logical catalog; a field absent from an older shard returns typed NULL.

Save this page's SQL as query.sql and the runner below as query.py. Use a registered key with lake scope from Developers. The runner submits the displayed political_trade_events query once, saves its operation, waits on that same ID, saves the returned manifest and downloads its Parquet parts.

This submission has not been executed for the reference page. Inspect the SQL, dates and resource limits before running it. Reuse the request key only for this identical SQL and limits; use a new key when changing them.

python3 -m pip install nexustrade==1.42.0
export NEXUSTRADE_API_KEY="YOUR_LAKE_SCOPED_KEY"
python3 query.py

import json
from pathlib import Path
from nexustrade import NexusTradeClient

# Save the SQL displayed on this page as query.sql.
sql = Path("query.sql").read_text()
client = NexusTradeClient.from_environment()
operation = client.create_lake_query(
    {"query": sql, "limits": {"timeoutSeconds": 30, "maxRows": 50,
                              "maxResultBytes": 1048576}},
    idempotency_key="reference-political_trade_events-20261012-v1",
)
Path("query-operation.json").write_text(json.dumps(operation, indent=2))
finished = client.wait_for_lake_query(operation["id"])
if finished["status"] != "completed":
    raise RuntimeError(finished)
manifest = client.get_lake_query_manifest(operation["id"])["result"]
Path("query-manifest.json").write_text(json.dumps(manifest, indent=2))
print(json.dumps(manifest, indent=2))
for part in manifest["parts"]:
    Path(f"query-{part['part']}.parquet").write_bytes(
        client.download_lake_query_part(operation["id"], part["part"], max_bytes=1048576)
    )

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`.