TL;DR

Use lake.backtest_comparisons to study how an indicator was used, including its threshold, comparison direction and trading action. Keep opposite rules in separate cohorts, filter the indicator side with a nonnull threshold, and deduplicate experiments before calculating medians. These associations do not isolate an indicator's causal effect. The SQL shown here is a proposed query, not an executed result; inspect its scope, submit it once and read back the returned data before drawing conclusions.

Query name: lake.backtest_comparisons
Storage partition: snapshot
Columns: 10

Columns and types

VARCHAR

backtest_uuid
condition_path
side
action_type
indicator_type
lookback_interval
comparator

INTEGER

strategy_idx
lookback

DOUBLE

threshold

Current logical schema for lake.backtest_comparisons

Inspect the complete lake.backtest_comparisons schema

Filter rows

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


backtest_uuid	VARCHAR
strategy_idx	INTEGER
condition_path	VARCHAR
side	VARCHAR
action_type	VARCHAR
indicator_type	VARCHAR
lookback	INTEGER
lookback_interval	VARCHAR
comparator	VARCHAR
threshold	DOUBLE

Current logical schema for lake.backtest_comparisons

Query lake.backtest_comparisons 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 per atomic comparison, projected out of `lake.backtest_strategies.condition_tree`. Use this, not `lake.backtest_indicators`, whenever the question is about HOW an indicator was used: a threshold, a direction, or which action the rule drove (`action_type`).

Compare atomic rule cohorts

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 cohorts keep comparator, threshold, side and action distinct. A distinct experiment enters each cohort only once, including its median calculation, after optimizer exclusion and run deduplication. A run may occur in several different cohorts; this descriptive association does not isolate the causal effect of one indicator.

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
)
, cohort_runs AS (
  SELECT DISTINCT r.experiment_id, r.percent_change, c.indicator_type,
         c.comparator, c.threshold, c.side, c.action_type
  FROM eligible_runs r
  JOIN lake.backtest_comparisons c ON c.backtest_uuid = r.backtest_uuid
  WHERE c.threshold IS NOT NULL
)
SELECT indicator_type, comparator, threshold, side, action_type,
       COUNT(*) AS experiments, MEDIAN(percent_change) AS median_return
FROM cohort_runs
GROUP BY indicator_type, comparator, threshold, side, action_type
ORDER BY experiments DESC, indicator_type, comparator, threshold
LIMIT 20;

Python SQL workflow ↗TypeScript SQL workflow ↗

Submit the backtest_comparisons 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_comparisons 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_comparisons-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.

`comparator` is lessThan, greaterThan, lessThanOrEqual, greaterThanOrEqual or equal, normalised to read from THIS indicator's side: `30 > RSI` is stored as RSI lessThan 30. `threshold` is the literal on the OTHER side, so for `RSI < 30` the RSI row carries 30 and the constant row carries NULL; filter `threshold IS NOT NULL` for the indicator side of a rule.

`RSI < 30` and `RSI > 70` are opposite rules. Ranking RSI as one thing pools them and is almost always wrong.

Discussion

No comments yet.

lake.backtest_comparisons dataset schema