lake.political_filings 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_filings

Query lake.political_filings 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_filings 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_filings-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)
    )

The logical view is backed by canonical manifest-resolved data. Query this name rather than guessing a physical database collection or object-storage path.

House and Senate periodic transaction reports, one row per indexed filing. `availableAt` is the point-in-time column: a filing may inform a decision only when `availableAt <= as_of`. It comes from the date named in `availabilitySource`, the House disclosure index filing date or the Senate eFD filed date. Never use a transaction date as availability.

Filings whose trades were not extracted are rows too, so completeness is a query. `extractionStatus` is `ok` when the filing's trades are in `political_trades`, `failed` with a `failureReason` when extraction did not finish (the daily job retries it), and `unsupported` for a source form with no gated extraction.

`ok` means a filing was read, not that every value is right: the rows are what a model read from the filed page, and known-hard cells are dense micro-print dates and checkbox columns that share a bound. `contractVersion` and `extractionModel` say what read a filing, so rows read by an older contract can be found; a filing is read again and its rows replaced whole when an operator names it.

Filer identity: `memberId` is the member's Bioguide ID from congress-legislators and `displayName` is their official name. One person has one `memberId` across every spelling of the filed name and across both chambers, so identify, group and filter politicians by `memberId` and show `displayName`; `filerFirst` and `filerLast` are the name as filed and vary between filings. `identitySource` is `legislators` or `override` for a member, `non_member` for a House employee or candidate who never served (`memberId` NULL), and `unresolved` when no member could be decided. 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`.

`parseMethod` is how the filing was read: `text` and `scan` are House PDFs with and without a usable text layer, `html` and `paper` are Senate electronic and paper reports. `amendedReportDate` is set on a filing that amends an earlier report. `reportDate` is the date a Senate eFD report title prints; a report and its numbered amendments share it, and it can differ from `filingDate`.