Columns and types

VARCHAR

backtest_uuid
side
indicator_type
symbol
lookback_interval
comparator

INTEGER

strategy_idx
indicator_idx
lookback

DOUBLE

threshold

JSON

raw_params

Current logical schema for lake.backtest_indicators

Inspect the complete lake.backtest_indicators schema

Filter rows

Scroll sideways to compare all columns. Select a heading to sort.


backtest_uuid	VARCHAR
strategy_idx	INTEGER
indicator_idx	INTEGER
side	VARCHAR
indicator_type	VARCHAR
symbol	VARCHAR
lookback	INTEGER
lookback_interval	VARCHAR
comparator	VARCHAR
threshold	DOUBLE
raw_params	JSON

Current logical schema for lake.backtest_indicators

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

What this table contains

Backtest corpus: one row per indicator instance in a strategy. Join on `backtest_uuid` + `strategy_idx`. `lookback` is the window size (50 for a 50-day average) and `lookback_interval` its unit (Day, Minute, Hour); a 20-minute average is not a 20-day one, so filter the interval when comparing windows. `raw_params` is the indicator's full JSON.

Compare one daily window per strategy

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 one_setting step excludes a strategy using two different windows of the same indicator. The cohort deduplicates run membership within each setting. Other strategies and settings in a run may still differ, so this is descriptive rather than a controlled comparison.

SQL

WITH eligible_runs AS (
  SELECT *
  FROM lake.backtest_runs
  WHERE author_type <> 'optimizer'
  QUALIFY ROW_NUMBER() OVER (
    PARTITION BY experiment_id
    ORDER BY created_at DESC, backtest_uuid
  ) = 1
)
, one_setting AS (
  SELECT backtest_uuid, strategy_idx, indicator_type, lookback_interval,
         MIN(lookback) AS lookback
  FROM lake.backtest_indicators
  WHERE lookback IS NOT NULL AND lookback_interval = 'Day'
  GROUP BY backtest_uuid, strategy_idx, indicator_type, lookback_interval
  HAVING COUNT(DISTINCT lookback) = 1
), cohort_runs AS (
  SELECT DISTINCT r.experiment_id, r.percent_change,
         s.indicator_type, s.lookback_interval, s.lookback
  FROM eligible_runs r
  JOIN one_setting s ON s.backtest_uuid = r.backtest_uuid
)
SELECT indicator_type, lookback_interval, lookback,
       COUNT(*) AS experiments, MEDIAN(percent_change) AS median_return
FROM cohort_runs
GROUP BY indicator_type, lookback_interval, lookback
ORDER BY experiments DESC, indicator_type, lookback
LIMIT 20;

Python SQL workflow ↗TypeScript SQL workflow ↗

Submit the backtest_indicators 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 backtest_indicators 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.

Shell

python3 -m pip install nexustrade==1.42.0
export NEXUSTRADE_API_KEY="YOUR_LAKE_SCOPED_KEY"
python3 query.py

Python

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

Create a lake-scoped key ↗Read Parquet results with Python ↗

Coverage and timing

Inspect source coverage and timing before using the rows

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

Comparing SETTINGS of one indicator (a moving-average window, an RSI threshold) must restrict to strategies using exactly ONE value of that setting: a 50/200 crossover holds two windows and otherwise lands in both buckets.

For HOW an indicator was used (threshold, direction, which action it drove) use `lake.backtest_comparisons`. `comparator` and `threshold` here are NULL in snapshots exported before they were captured.

Discussion

No comments yet.

lake.backtest_indicators dataset schema