Fouzan Ahmed.
All projects
Data WarehousingAnalytics EngineeringML Feature EngineeringFinancial Data

Market Data Warehouse on Snowflake

A three-layer Snowflake warehouse over five years of daily prices, feeding point-in-time-correct forecasting models.

Personal / Portfolio project2026
View repositoryJump to architecture

Problem

  • Most from-scratch forecasting projects skip the warehouse and load a CSV straight into a notebook. That's fast to start but it hides two real problems: the pipeline doesn't scale past a toy dataset, and it's very easy to accidentally let a backtest see future data through a poorly bounded feature window, which quietly inflates accuracy in a way that doesn't hold up live.
  • I wanted to build the warehouse layer properly, raw through to a feature mart, and use Snowflake's own primitives (Time Travel, zero-copy clones, streams and tasks) to make point-in-time correctness structural rather than something I had to remember to enforce by hand.

Architecture

  1. Step 01

    Yahoo Finance

    5 years, 1,000 tickers

  2. Step 02

    S3 external stage

    COPY INTO raw layer

  3. Step 03

    Stream & Task

    Automated MERGE into staging

  4. Step 04

    Feature mart

    Window functions, clustering key

  5. Step 05

    Time Travel snapshot

    Point-in-time, no lookahead

  6. Step 06

    LSTM & linear regression

    Trained + backtested

Raw prices land, get staged, and feed a point-in-time feature mart that both models train against.

  • Three layers: a raw landing layer loaded via an S3 external stage with COPY INTO, a staging layer kept current by a Snowflake stream and task, and a feature mart built entirely in SQL window functions.
  • The environment itself was set up from scratch with separate load and query warehouses, distinct engineer and analyst roles under RBAC, 60-second auto-suspend, and a resource monitor to cap spend.
  • Five years of daily prices across 1,000 tickers, refreshed automatically: the stream tracks new rows in raw, and the task fires a MERGE into staging, gated by SYSTEM$STREAM_HAS_DATA so a day with no new data costs nothing.
  • For every backtest date, a zero-copy clone plus Time Travel takes a snapshot of the feature mart as it existed at that point, so the model only ever sees what would genuinely have been available.
  • LSTM and linear regression models train against the same point-in-time feature mart and get backtested against the same snapshots, so the two approaches are directly comparable.

Engineering decisions

  • Time Travel and zero-copy clones over a manually filtered training set. Filtering by date in application code is easy to get wrong once, silently, and never notice. Using Snowflake's native point-in-time snapshotting makes the lookahead-bias boundary a property of the data platform, not a rule I had to keep enforcing everywhere I touched the data.
  • Streams and tasks instead of a cron-triggered full reload. A full reload every day works but wastes warehouse time re-processing unchanged history. Gating the MERGE on SYSTEM$STREAM_HAS_DATA means an idle day costs nothing, and the pipeline only does work when there's actually new data.
  • Clustering key added reactively, not upfront. Rather than guessing at a clustering key before there was real query behavior to look at, I let Query Profile show where pruning was poor first, then added a (ticker, trade_date) key once the evidence justified it.
  • Separate load and query warehouses. Splitting ingestion compute from analyst query compute means a slow analyst query never competes with or blocks the daily load, and each warehouse can be sized and auto-suspended independently.

Implementation

  • Loaded five years of daily prices for 1,000 tickers from Yahoo Finance into S3, then into Snowflake's raw layer through an external stage using COPY INTO.
  • Built the staging refresh as a Snowflake stream on the raw table plus a scheduled task running a MERGE, so staging always reflects the latest raw data without a full reprocess.
  • Built the full feature set directly in SQL: AVG and SUM OVER with ROWS BETWEEN frames for moving averages, lagged returns, rolling volatility and RSI, all partitioned by ticker.
  • Used Query Profile to find that date-range filters were scanning far more partitions than they should, then added a clustering key on (ticker, trade_date), which cut partitions scanned on those filters by roughly an order of magnitude.
  • For each backtest date, took a zero-copy clone of the feature mart and used Time Travel to pin it to that point in time, giving every model run a reproducible, leakage-free snapshot to train and evaluate against.
  • Trained an LSTM and a linear regression baseline on the same feature mart and backtested both against the same point-in-time snapshots for a fair, reproducible comparison.

Challenges

  • Getting genuinely leakage-free backtesting right took more iterations than expected. It's easy to write a feature that technically only uses 'past' data but still leaks information indirectly through a rolling window computed after the fact. Time Travel snapshots at each test date closed that gap structurally.
  • Tuning warehouse auto-suspend and sizing to keep this genuinely close to zero-cost as a portfolio project, without giving up the ability to run real backtests on demand.
  • Diagnosing the clustering problem required actually reading Query Profile output rather than guessing, since the symptoms (slow date-filtered queries) had more than one possible cause.

Results

  • The (ticker, trade_date) clustering key cut partitions scanned on date-filtered queries by roughly an order of magnitude.
  • Streams and tasks keep the pipeline running on a genuine event-driven basis, an idle day triggers no MERGE and costs nothing.
  • Time Travel and zero-copy clones gave every backtest a reproducible, point-in-time-correct snapshot, with storage overhead kept near zero through Snowflake's shared micro-partitions.
  • Both the LSTM and linear regression baseline were trained and backtested against identical, leakage-free snapshots, making the comparison between them meaningful.

Technology

Warehouse

SnowflakeExternal StageCOPY INTOStreams & TasksMERGETime TravelZero-Copy Clone

Modeling & Governance

Clustering KeysQuery ProfileRBACResource Monitor

Feature Engineering

SQL Window FunctionsMoving AveragesRSIRolling Volatility

Machine Learning

LSTMLinear RegressionBacktestingModel Evaluation

Ingestion

AWS S3Python

More projects

APRA Data Engineering & Analytics Platform

Personal / Portfolio project

Problem

APRA publishes superannuation performance data as unversioned Excel filings, with no API and no easy way to compare funds over time.

Key result

Weekly pipeline runs unattended end to end, gated by dbt tests and CI, feeding a live 3-page Power BI dashboard.

PythonApache AirflowdbtAWS S3AWS RDSAWS EC2+8 more
ArchitectureGitHubProject details

NewsFlow Autonomous AI Newsletter & Video Agent

Personal project

Problem

Producing a newsletter and a companion video from breaking news end to end usually means a person doing research, writing, narration and publishing by hand, every single cycle.

Key result

The workflow runs from trigger to published newsletter and video with no manual step, and recovers from failures instead of stopping cold.

n8nLangChainLangGraphRAGMCPClaude API+5 more
ArchitectureGitHubProject details