lake.backtest_runs 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.backtest_runs

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

Anonymized backtest corpus: one row per run; other backtest_* tables join on backtest_uuid.

`backtest_uuid` identifies a run. Select it whenever strategy examples are wanted: NexusTrade hydrates the full strategy JSON from `lake.backtest_strategies` by that id. `portfolio_uuid` is a stable anonymized portfolio hash.

DEDUPLICATE ON `experiment_id` before any average, median or ranking. It hashes the strategy logic plus tickers plus window, and cloned or forked portfolios repeat one experiment under many `backtest_uuid`s (about 60% of rows are repeats), so an aggregate over raw rows measures what got cloned rather than what performed. Counting rows is fine. It is a hash: never pattern-match it.

`author_type` is 'optimizer' for genetic-search output and 'unknown' otherwise. Filter `author_type <> 'optimizer'` for any question about what performs well: a genetic search is a population of attempts, not strategies anyone chose to keep, and it is about a third of the corpus.

`tickers`, `distinct_indicator_types`, `distinct_action_types` and `warnings` are VARCHAR[]. Filter by overlap, e.g. `array_length(list_intersect(tickers, ['NVDA']::VARCHAR[])) > 0`.

`asset_class` is equity, crypto, options or mixed. `cadence` is daily or intraday. `signal_frequency_class` is unconstrained, sub_daily, daily, weekly, monthly or quarterly.

`alpha_pct` and the `baseline_*` columns are populated only for runs that carried validation statistics (nearly all options runs, very few daily-equity runs). Restrict to non-null rows before comparing them across asset classes. Return per day is `percent_change / duration_days`.