lake.canonical_quarterly_financials dataset schema

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 canonical_quarterly_financials 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-canonical_quarterly_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)
    )

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

Query lake.canonical_quarterly_financials 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.

Point-in-time quarterly statements from SEC filings, every revision kept.

The backtest engine reads canonical statements. For a historical signal, filter availability before selecting a period revision, and match the statement basis used by the strategy. Availability dating alone does not prevent lookahead if a query selects a later revision.

`date` is when the figure became PUBLICLY KNOWABLE (the filing date). `periodEnd` is the fiscal period end, and they are far apart. Filter on `date` for point-in-time correctness; filter on `periodEnd` when the user names a specific fiscal quarter or year.

One row per FILING, so a restated quarter appears more than once. Collapse to the latest revision with `QUALIFY ROW_NUMBER() OVER (PARTITION BY ticker, periodEnd ORDER BY date DESC) = 1`.

`revenueDerivation` records the exact inputs to constructed Q4 revenue (`annual_minus_prior_quarters`: the annual and Q1–Q3 rows; `annual_minus_nine_month_ytd`: the annual row and the nine-month year-to-date fact, used when Q1–Q3 were not all filed before the 10-K), including retained direct/composed raw fact identities. Values in this JSON remain in native reporting units even on USD canonical rows. Cash-flow revisions preserve this revenue origin; NULL on legacy shards means unknown provenance.

`quarterlyFlowProvenance` carries the exact inputs for YTD-subtracted operating cash flow and capex when published by the newer exporter; older shards can have NULL until republished.

Coverage starts with SEC XBRL filings in 2009; trailing-twelve-month values exist from about mid-2010. Pre-2009 vendor history is not served.