How to Backtest a Trading Strategy in Snowflake SQL

By Fruit Stand Team · November 8, 2026 · Guides To backtest a trading strategy in Snowflake, compute the signal with window functions over daily prices, shift it one day so each trade uses only past data, then compound the daily returns into an equity curve. On SPY since 1993, a 50/200-day moving-average crossover returned 10.3% a year against 10.7% for buy-and-hold, with a maximum drawdown of 34% instead of 55%. Below is the full SQL, the results for SPY and QQQ, the trades it made, and the mistakes that make backtests look better than they are.

Prices come from Fruit Stand’s Historical Stock Prices listing and T-bill rates from our FRED listing, through November 6, 2026. Every query here was run on November 8, 2026. A backtest describes the past, and this isn’t investment advice.

The strategy

The 50/200-day crossover is the classic trend rule, often called the golden cross and the death cross:

Signal: in the market when the 50-day simple moving average of the price is above the 200-day average, out when it’s below.
Prices: adjusted_close, which includes splits and dividends, so the returns are total returns. See Adjusted Close vs. Close for why the raw close breaks here.
Timing: the signal is calculated at a day’s close and acted on the next day, so the strategy never trades on information it didn’t have yet.
Cash: when out of the market, the money earns the 3-month Treasury bill rate (FRED series DTB3).
Test: SPY (S&P 500) and QQQ (Nasdaq-100), each from its 201st trading day, when the 200-day average first exists.

Step 1: Build the daily positions

One query computes both averages, the position, and each day’s strategy return. Saving it as a table keeps the next queries short:

create or replace temporary table backtest as
with prices as (
    select code, date,
           adjusted_close / lag(adjusted_close) over (partition by code order by date) - 1 as daily_return,
           avg(adjusted_close) over (partition by code order by date
               rows between 49 preceding and current row) as sma_50,
           avg(adjusted_close) over (partition by code order by date
               rows between 199 preceding and current row) as sma_200,
           row_number() over (partition by code order by date) as day_number
    from security_price_history_vw                       -- Historical Stock Prices
    where exchange_code = 'US' and code in ('SPY', 'QQQ')
),
tbills as (
    select date, value as tbill_rate
    from fred.fred_series_observations_vw                -- FRED Series
    where series_id = 'DTB3' and value is not null
),
positions as (
    select p.code, p.date, p.daily_return, p.day_number,
           -- act on yesterday's signal, so no day uses information it couldn't have had
           lag(iff(p.sma_50 > p.sma_200, 1, 0)) over (partition by p.code order by p.date) as in_market,
           lag(t.tbill_rate) over (partition by p.code order by p.date) as tbill_rate
    from prices p
    asof join tbills t match_condition (p.date >= t.date)
)
select code, date, daily_return, in_market,
       in_market * daily_return
           + (1 - in_market) * (power(1 + tbill_rate / 100, 1 / 252) - 1) as strategy_return,
       iff(in_market <> lag(in_market) over (partition by code order by date), 1, 0) as is_trade
from positions
where day_number > 200;

The two listings are separate databases in your account, so qualify each view with the database name you chose when you got the listing. Three details do most of the work:

rows between 199 preceding and current row makes the window a fixed 200 trading days. Skipping the first 200 days (day_number > 200) drops the rows where the average covers fewer days.
lag(...) on the signal is what prevents look-ahead bias: today’s return is earned by yesterday’s decision.
asof join matches each trading day to the latest T-bill rate on or before it, so holidays and missing rate days don’t drop rows.

Step 2: Score it against buy-and-hold

Compound the daily returns with exp(sum(ln(1 + r))), track each curve’s running peak for drawdowns, and annualize:

with curves as (
    select *,
           exp(sum(ln(1 + daily_return)) over (partition by code order by date)) as buy_hold,
           exp(sum(ln(1 + strategy_return)) over (partition by code order by date)) as strategy
    from backtest
),
drawdowns as (
    select *,
           buy_hold / max(buy_hold) over (partition by code order by date) - 1 as buy_hold_dd,
           strategy / max(strategy) over (partition by code order by date) - 1 as strategy_dd
    from curves
)
select code,
       min(date) as start_date,
       max(date) as end_date,
       round(power(max_by(buy_hold, date), 365.25 / datediff(day, min(date), max(date))) - 1, 4) as buy_hold_cagr,
       round(power(max_by(strategy, date), 365.25 / datediff(day, min(date), max(date))) - 1, 4) as strategy_cagr,
       round(min(buy_hold_dd), 3) as buy_hold_max_drawdown,
       round(min(strategy_dd), 3) as strategy_max_drawdown,
       round(stddev(daily_return) * sqrt(252), 3) as buy_hold_volatility,
       round(stddev(strategy_return) * sqrt(252), 3) as strategy_volatility,
       round(avg(in_market), 2) as time_in_market,
       sum(is_trade) as trades
from drawdowns
group by code
order by code;

The results

Through November 6, 2026:

	SPY buy-and-hold	SPY crossover	QQQ buy-and-hold	QQQ crossover
Start	Nov 12, 1993	Nov 12, 1993	Dec 22, 1999	Dec 22, 1999
Annual return	10.68%	10.27%	8.38%	11.52%
Maximum drawdown	−55.2%	−33.7%	−83.0%	−36.5%
Volatility (annualized)	18.7%	13.8%	26.7%	18.8%
Time in the market	100%	77%	100%	74%
Trades	—	30	—	32

On SPY, the crossover gave up 0.4 points a year of return for a much smoother ride: it was out for most of the 2008 crash, and its worst loss was a third rather than more than half. $10,000 grew to $251,169 with the strategy and $284,394 buying and holding. On QQQ, it beat buy-and-hold outright, but mostly because of when the test starts. QQQ’s 200th trading day fell in December 1999, three months before the dot-com peak, and buy-and-hold then lost 83%. A backtest that starts at a bubble’s top flatters any rule that sidesteps the crash. The last 10 years tell a different story. From November 7, 2016, in a mostly rising market:

	Buy-and-hold	Crossover
SPY	15.24% a year	11.34% a year
QQQ	20.00% a year	18.88% a year

A trend rule pays for crash protection by being late, and in a decade with fast recoveries it was late in both directions.

The trades it made

select date, iff(in_market = 1, 'Buy', 'Sell') as action
from backtest
where code = 'SPY' and is_trade = 1 and date >= '2018-01-01'
order by date;

Date	Action
Dec 13, 2018	Sell
Mar 27, 2019	Buy
Apr 1, 2020	Sell
Jul 7, 2020	Buy
Mar 17, 2022	Sell
Jan 27, 2023	Buy
Apr 17, 2025	Sell
Jun 30, 2025	Buy

The 2020 trades show the weakness. The S&P 500 bottomed on March 23, 2020, but the 50-day average only crossed below the 200-day on April 1, so the strategy sold after the crash and bought back in July, higher. 2022 went better: it sold in March and missed most of that year’s decline.

Mistakes that inflate a backtest

Look-ahead bias. Trading on the same day’s signal, which uses that day’s close, lifts SPY’s result from 9.79% to 10.15% a year before cash interest. Always lag the signal.
Ignoring cash. Leaving cash at 0% understates a strategy that’s out of the market a quarter of the time. T-bill interest added about half a point a year: SPY’s crossover returned 9.79% without it and 10.27% with it.
Raw prices. The raw close drops on every split and every dividend, so moving averages built on it trigger false sell signals. Use adjusted_close.
Costs. At 0.1% per trade, 30 trades cost SPY’s crossover about 0.1 points a year (10.27% to 10.17%). Rules that trade weekly pay far more.
The start date. As QQQ shows, the first day of the test can decide the answer. Rerun from a few different starts before trusting a result.
Survivorship bias. Index ETFs like SPY and QQQ avoid it, but a stock backtest on today’s companies doesn’t include the ones that failed. The Historical Stock Prices listing doesn’t include delisted securities.
Overfitting. Testing many average lengths and keeping the best is easy in SQL, which makes it easy to fool yourself. Pick the rule first, or keep a period you don’t look at until the end.

Testing other tickers and rules

Change the code in (...) list to test any US stock, ETF or mutual fund in the listing; the queries handle many tickers at once because every window is partition by code. To test a different rule, change the iff(...) in the positions step: for example, iff(adjusted_close > sma_200, 1, 0) for the simpler price-above-200-day rule (add adjusted_close to the prices step’s columns first). For more SQL on the same data, see How to Calculate Inflation-Adjusted Returns in SQL, which joins the same two listings.

F.A.Q.

Can Snowflake run a backtest without Python?

Yes. A rule built from moving averages, price levels or other window calculations is plain SQL, as above. Python in Snowflake (Snowpark) helps for strategies with complex state, such as stop-losses that depend on the entry price.

Does the golden cross beat buy-and-hold?

Not on SPY since 1993: 10.27% a year against 10.68%, though with a 34% maximum drawdown instead of 55%. On QQQ from December 1999 it did beat buy-and-hold, largely because the test started just before the dot-com crash.

How do I avoid look-ahead bias in SQL?

Calculate the signal from data up to a day’s close, then use lag() so the position applies from the next day’s return. Never let a row’s own return decide its position.

Why use adjusted prices for a backtest?

Adjusted prices include splits and dividends, so returns are total returns and moving averages don’t jump on split days. The raw close would trigger false signals and leave out dividends.

How much compute does this use?

Very little. The backtest above covers about 15,000 rows, and window functions over the full price history of thousands of tickers run in seconds on a small warehouse.

To run this on any US stock, ETF or mutual fund, start a free 30-day trial of the Historical Stock Prices listing on the Snowflake Marketplace. The T-bill rates come from the FRED listing. The schema is in the dataset docs. Questions? Reach out at contact@fruitstand.dev.

Home

Datasets

Company

How to Backtest a Trading Strategy in Snowflake SQL

The strategy

Step 1: Build the daily positions

Step 2: Score it against buy-and-hold

The results

The trades it made

Mistakes that inflate a backtest

Testing other tickers and rules

F.A.Q.

​The strategy

​Step 1: Build the daily positions

​Step 2: Score it against buy-and-hold

​The results

​The trades it made

​Mistakes that inflate a backtest

​Testing other tickers and rules

​F.A.Q.

The strategy

Step 1: Build the daily positions

Step 2: Score it against buy-and-hold

The results

The trades it made

Mistakes that inflate a backtest

Testing other tickers and rules

F.A.Q.