lake.canonical_annual_financials dataset schema

TL;DR

For historical annual financials, apply the decision date cutoff before choosing the latest eligible filing revision. In lake.canonical_annual_financials, date means availability and periodEnd means the reported fiscal year end; confusing them can introduce future information. The table retains SEC filing revisions, with canonical monetary fields in USD and coverage beginning in 2009. Foreign private issuers rely on annual statements here. The example query has not been executed, and its revision illustration uses hypothetical values.

Query name

lake.canonical_annual_financials

Storage partition

year

Columns

35

Why cutoff comes before revision selection

Illustrative version history, not actual company rows: the same fiscal period has value 100 available January 15 and a revised value 110 available February 15. A February 1 decision may use 100; selecting 110 before applying the availability cutoff would use future information.

What this table contains

Point-in-time annual (10-K, 20-F, 40-F) statements from SEC filings, every revision kept. `date` is availability, not period end.

Select financial revisions before a decision cutoff

Query example, not executed here. The dates and filters define the proposed research scope; returned rows and current coverage must be checked in your workspace.

The availability cutoff runs before ROW_NUMBER selects the latest eligible revision for each period. date is availability; periodEnd is the reported period. The fallback period key uses the saved date when periodEnd is NULL. Canonical monetary fields are in USD.

SELECT ticker, name, periodEnd, date, filed, totalRevenue, accession
FROM lake.canonical_annual_financials
WHERE ticker = 'AAPL'
  AND date < TIMESTAMP '2026-09-19'
QUALIFY ROW_NUMBER() OVER (
  PARTITION BY ticker, COALESCE(CAST(periodEnd AS DATE), CAST(date AS DATE))
  ORDER BY date DESC, accession DESC
) = 1
ORDER BY periodEnd DESC NULLS LAST, date DESC
LIMIT 20;

Submit the canonical_annual_financials example and read back its parts

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-canonical_annual_financials-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)
    )

Columns and types

Coverage and timing