Why cutoff comes before revision selection
Original value 100
January 15
Yes
Revised value 110
February 15
No
Hypothetical inputs demonstrate availability ordering; no SQL output is fabricated.
Show the table · 2 rows, 3 columns
Scroll sideways to compare all columns. Select a heading to sort.
| Original value 100 | January 15 | Yes |
| Revised value 110 | February 15 | No |
No matching rows. Clear the filter to see all records.
Hypothetical inputs demonstrate availability ordering; no SQL output is fabricated.
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
SEC XBRL annual fundamentals, same shape as sec_quarterly_financials.
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. Native-currency values require the reportingCurrency field for comparisons.
SELECT ticker, name, periodEnd, date, filed, totalRevenue, accession
FROM lake.sec_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 filed DESC NULLS LAST, date DESC, accession DESC
) = 1
ORDER BY periodEnd DESC NULLS LAST, date DESC
LIMIT 20;Submit the sec_annual_financials example and read back its parts
Run the exact SQL through the Python SDK
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 sec_annual_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-sec_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
VARCHAR
- ticker
- symbol
- name
TIMESTAMP
- date
DOUBLE
- totalRevenue
- netIncome
- ebitda
- grossProfit
- freeCashFlow
- totalAssets
- totalLiab
- commonStockSharesOutstanding
Showing 12 of 34 recorded rows. The complete table is below.
Current logical schema for lake.sec_annual_financials
Inspect the complete lake.sec_annual_financials schema
Scroll sideways to compare all columns. Select a heading to sort.
| ticker | VARCHAR |
| symbol | VARCHAR |
| date | TIMESTAMP |
| name | VARCHAR |
| totalRevenue | DOUBLE |
| netIncome | DOUBLE |
| ebitda | DOUBLE |
| grossProfit | DOUBLE |
| freeCashFlow | DOUBLE |
| totalAssets | DOUBLE |
| totalLiab | DOUBLE |
| commonStockSharesOutstanding | DOUBLE |
| shortTermDebt | DOUBLE |
| longTermDebt | DOUBLE |
| operatingIncome | DOUBLE |
| operatingCashFlow | DOUBLE |
| capitalExpenditures | DOUBLE |
| filed | TIMESTAMP |
| periodEnd | TIMESTAMP |
| availabilitySource | VARCHAR |
| cik | BIGINT |
| accession | VARCHAR |
| rawArchiveKey | VARCHAR |
| rawSha256 | VARCHAR |
| sourceFilingUrl | VARCHAR |
| quarterlyFlowProvenance | VARCHAR |
| revenueDerivation | VARCHAR |
| filedValueCorrections | VARCHAR |
| filedPeriodCorrection | VARCHAR |
| reportingCurrency | VARCHAR |
| periodStart | TIMESTAMP |
| taxonomy | VARCHAR |
| fiscalYear | BIGINT |
| fiscalPeriod | VARCHAR |
No matching rows. Clear the filter to see all records.
Current logical schema for lake.sec_annual_financials
Query lake.sec_annual_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.
Coverage and timing
Inspect source coverage and timing before using the rows
SEC XBRL annual fundamentals, same shape as sec_quarterly_financials.
Annual rows in the same shape as `sec_quarterly_financials`, and with the same restatement behavior — dedup on `(ticker, periodEnd)` by `filed DESC` before aggregating. Use `year(cast(date as date))` for a calendar year.
No comments yet.