We Taught a Snowflake Warehouse to Judge World Cup Conviction and Write the Verdict Back to Solana

We Taught a Snowflake Warehouse to Judge World Cup Conviction and Write the Verdict Back to Solana

This is a submission for Weekend Challenge: Passion Edition. Target categories: Best use of Snowflake + Best use of Solana.

The single most common mistake in cross-platform hackathon projects is using one platform as a database and the other as a logo. A dashboard that reads Solana data through an API and renders it in a UI is not a Solana project. It's a dashboard.

The actual first question is: what can each platform do that the other physically cannot? Solana has behavioral data that exists nowhere else - every buy, sell, and hold, public and permanent. But a blockchain cannot compute aggregates over its own history; that's not what the runtime is for. Snowflake can chew through millions of decoded transactions with a declarative SQL DAG - but it has no way to make a statement the rest of the on-chain world can read and build on.

So I built the loop that gives each side the other's missing half: Solana (mainnet) --read--> Snowflake --compute--> Snowflake --write back--> Solana (devnet) live swaps and streaming conviction, an oracle account transfers, decoded ingest + Dynamic scoring + Cortex, any program can read by Helius Tables DAG ML.

FERVOR: A Real-Time Conviction Oracle

FERVOR is a real-time conviction oracle for World Cup fandom on Solana. It tracks sixteen national token communities from the 2026 field - Argentina to Japan, Morocco to Canada - wired into eight derby rivalries (ARG-BRA, FRA-ENG, POR-ESP, GER-NOR, USA-MEX, NED-BEL, CRO-MAR, JPN-CAN), and it does not measure hype, because following a crowd is cheap.

It measures conviction: the wallet that keeps buying its country's token while the price bleeds, the holder who never sells through a losing run. Passion, defined mechanically: holding when it hurts.

Every 5 minutes, a Snowflake task stages the freshly computed index and a signing bridge writes it to Solana devnet as a confirmed transaction:

{
  "oracle": "FERVOR",
  "source": "Snowflake MARTS.TEAM_FERVOR_INDEX",
  "team_id": 1,
  "team": "ARGENTINA",
  "fervor_index": 290,
  "momentum": 4
}

That JSON is Snowflake output living on a block explorer. And this is the league it produces - real numbers from the running system: Argentina leads on conviction (109 average days held), Japan is the surprise runner-up, England has the deepest bench of believers, and Norway folds under pressure. The chain said so.

The Architecture, Actually Explained

Six stages. Every stage does load-bearing work; there is no component whose only job is to name-drop a technology.

One database, six schemas, data flowing strictly left to right:

Stage 1: Ingest - A Cursor Trick That Makes Polling Feel Like Streaming

Helius decodes raw Solana transactions into clean JSON, so I ingest structured events instead of byte soup. The worker polls each tracked mint every 8 seconds, but the until cursor means each poll returns only transactions newer than the last one seen. Functionally, it's a stream:

// ingest/poller.ts - the cursor is the whole trick
const cursor = new Map<string, string>(); // address -> newest seen signature

async function fetchNewTxs(address: string): Promise<EnhancedTx[]> {
  const until = cursor.get(address);
  const url =
    `https://api.helius.xyz/v0/addresses/${address}/transactions` +
    `?api-key=${HELIUS_API_KEY}&limit=50` +
    (until ? `&until=${until}` : "");
  const res = await fetch(url);
  const txs = (await res.json()) as EnhancedTx[];
  if (txs.length > 0) cursor.set(address, txs[0].signature); // newest first
  return txs;
}

Landing is a multi-row INSERT ... SELECT PARSE_JSON(?) through the Node connector. Honest note: true Snowpipe Streaming needs the Java ingest SDK. 8-second micro-batches demo identically (rows appear in Snowflake seconds after they land on-chain) and were far more reliable to stand up in a weekend.

Stage 3: The Dynamic Tables DAG - No Cron, Declared Lag

I did not write a single scheduler for the transform layer. Each table declares TARGET_LAG = '1 minute' and Snowflake works out the refresh order:

RAW.SOLANA_TX_LANDING
  |- STAGING.TOKEN_TRANSFERS ----> MARTS.DEFECTION_FLOWS
  |- STAGING.SWAP_EVENTS -------> STAGING.PRICE_TICKS
    |- MARTS.WALLET_POSITIONS
      |- MARTS.WALLET_FERVOR ----> MARTS.TEAM_FERVOR_INDEX
        |- (1 min task) ML.TEAM_INDEX_HISTORY
          |- (5 min task) Cortex models
            |- (5 min task) ORACLE.PUBLISH_QUEUE

The result is a per-minute index series where each of the sixteen nations charts its own story.

Two pieces of the transform SQL earned their place the hard way.

Decoding BUY/SELL without per-DEX parsers. Helius does not emit a parsed swap event for every venue, so I classify from token-balance movement: fee payer receives the tracked token = BUY, sends it = SELL. Routed swaps get their legs summed, and self-arbitrage transactions (fee payer both sends AND receives the same token in one tx) are dropped entirely - an arb bot carries zero conviction signal:

-- warehouse/02_staging_dt.sql (excerpt)
team_legs AS (
  SELECT
    l.signature,
    MIN(l.block_time) AS block_time,
    l.raw_payload:feePayer::string AS wallet,
    tt.value:mint::string AS mint,
    r.team_id,
    IFF(
      tt.value:toUserAccount::string = l.raw_payload:feePayer::string,
      'BUY', 'SELL'
    ) AS side,
    SUM(tt.value:tokenAmount::float) AS token_amount
  FROM dedup l,
  LATERAL FLATTEN(input => l.raw_payload:tokenTransfers) tt
  JOIN REF.TEAM_TOKENS r ON r.mint = tt.value:mint::string
  WHERE
    tt.value:toUserAccount::string = l.raw_payload:feePayer::string
    OR tt.value:fromUserAccount::string = l.raw_payload:feePayer::string
  GROUP BY
    l.signature,
    l.raw_payload:feePayer::string,
    tt.value:mint::string,
    r.team_id,
    side
  -- self-arb filter: a signature with BOTH a BUY and a SELL row for the
  -- same token makes this window count 2, and the tx is excluded
  QUALIFY COUNT(*) OVER (PARTITION BY l.signature, r.team_id) = 1
)

The 24-hour price change that survives trading gaps. The naive version is LAG(price, 24) over hourly buckets. That counts ROWS, not HOURS - one quiet hour and your "24h change" silently becomes a 25h change. ASOF JOIN matches each tick to the nearest tick at or before 24 hours earlier, with a warm-up fallback for series younger than a day:

-- warehouse/02_staging_dt.sql (excerpt)
SELECT
  cur.team_id,
  cur.window_start,
  cur.price_usd,
  100.0 * (
    cur.price_usd - COALESCE(day_ago.price_usd, first_tick.price_usd)
  ) / NULLIF(COALESCE(day_ago.price_usd, first_tick.price_usd), 0)
    AS price_change_24h_pct
FROM hourly cur
ASOF JOIN hourly day_ago
  MATCH_CONDITION(
    DATEADD('hour', -24, cur.window_start) >= day_ago.window_start
  )
  ON cur.team_id = day_ago.team_id
LEFT JOIN (
  -- warm-up: earliest tick, for series younger than 24h
  SELECT team_id, price_usd
  FROM hourly
  QUALIFY ROW_NUMBER() OVER (PARTITION BY team_id ORDER BY window_start) = 1
) first_tick ON first_tick.team_id = cur.team_id;

Prices use the hourly median, not VWAP - SOL-leg attribution is heuristic, and one mis-attributed leg destroys a volume-weighted mean. The dips these series capture are the entire thesis.

The Conviction Score - The Creative Payload

Per wallet, per country, 0 to 100:

-- warehouse/03_marts_dt.sql (excerpt)
ROUND(
  GREATEST(0, LEAST(100,
    25 * LN(1 + a.hold_duration_days) / LN(1 + 365)
    + 30 * LEAST(1, COALESCE(c.buy_against_decline, 0) / 5.0)
    + 25 * LEAST(1, COALESCE(c.buy_at_loss, 0) / 5.0)
    - 20 * IFF(a.last_sell_time > DATEADD('day', -3, SYSDATE()), 1, 0)
  )), 1
) AS fervor_score

Two engineering decisions worth stealing:

• Tenure in fractional days (DATEDIFF('second', ...) / 86400.0). Whole-day granularity freezes longevity at zero for 24 hours and flat-lines the index on launch day.
• Zero-signal wallets are excluded from the national average. The raw swap feed is dominated by one-shot MEV bots whose score is 0 by construction. Averaging them in measures bot traffic, not the fan base:

ROUND((
  COALESCE(AVG(IFF(f.fervor_score > 0, f.fervor_score, NULL)), 0) * 0.6
  + LEAST(100, COUNT_IF(f.fervor_score >= 60) / 10.0) * 0.4
) * 10, 0) AS fervor_index -- 0 to 1000, this exact number goes on-chain

And the emotional centerpiece, MARTS.DEFECTION_FLOWS: the same wallet selling one country and buying another within 24 hours. Selling ARGENTINA to buy BRAZIL is not a portfolio rebalance. It is treason, and it gets its own Sankey.

Stage 4: Cortex - ML Without Leaving SQL, Plus the Gotcha That Cost an Hour

Three models, retrained every 5 minutes by a task:

-- warehouse/04_ml_cortex.sql (excerpt)
CREATE OR REPLACE SNOWFLAKE.ML.ANOMALY_DETECTION ML.FERVOR_ANOMALY_MODEL(
  INPUT_DATA => SYSTEM$QUERY_REFERENCE(
    'SELECT team_id, ts, fervor_index
     FROM FERVOR.ML.TEAM_INDEX_HISTORY
     WHERE NOT COALESCE(is_synthetic, FALSE) -- never train on demo spikes
     AND ts < DATEADD(minute, -15, SYSDATE())'
  ), -- strict train/detect split
  SERIES_COLNAME => 'TEAM_ID',
  TIMESTAMP_COLNAME => 'TS',
  TARGET_COLNAME => 'FERVOR_INDEX',
  LABEL_COLNAME => ''
);

The gotcha: my first version trained on ts < now and detected on the last hour. Cortex rejected it with "All evaluation timestamps must be after the last timestamp in fitting data." Training and detection must not overlap - split both at the same boundary, strict < on one side, >= on the other.

The is_synthetic flag is the honesty mechanism. A demo needs a passion spike on camera, so scripts/demo_spike.py injects labeled rows that detection SEES but training NEVER fits. The detector flags the surge because it genuinely is one.

The crowd-pleaser is one function call:

SNOWFLAKE.CORTEX.COMPLETE(
  'mistral-large2',
  'In two punchy sentences, hype up the fanbase of the '
  || team_name || ' token community. Fervor index '
  || fervor_index || '/1000, '
  || devoted_wallet_count || ' diamond-hand wallets, average hold '
  || avg_hold_days || ' days. No preamble, no hashtags.'
)

And it goes further than a scheduled task: the dashboard's AI insights tab calls Cortex on demand, from inside the app - no external API, the LLM runs where the data lives. Pick a team and mistral-large2 writes an analyst brief from the real numbers:

# warehouse/streamlit_app.py (excerpt) - LLM inference inside the warehouse
@st.cache_data(ttl=300)
def cortex_brief(prompt: str) -> str:
    esc = prompt.replace("'", "''")
    return str(
        session.sql(
            f"SELECT SNOWFLAKE.CORTEX.COMPLETE('mistral-large2', '{esc}')"
        ).collect()[0][0]
    )

prompt = (
    f"You are a crypto market analyst covering World Cup fan tokens. "
    f"3 bullets: one strength, one risk, one outlook. {team} index "
    f"{idx}/1000 ({chg:+.0f} over {window}), {bel} believers of {tot} "
    f"wallets, buy pressure {bp:.0f}%, arch-rival is {rival}."
)

The same tab shows a conviction-health table in percentages per nation: share of wallets with a signal, share of believers, dip buys per wallet, 6-hour buy pressure, and Cortex's forecast for the next 12 minutes.

The FORECAST model does more than fill a column. The AI tab opens with a Conviction outlook: every nation's index projected 12 minutes out, drawn as a dumbbell (live index to forecast) so you read who is strengthening and who is cracking in one glance, a durability score blending forecast trajectory, hold time, momentum and buy pressure, and a league-wide brief Cortex writes from the ranked board - naming the best-positioned fan base and the one most at risk, with an explicit note that it forecasts holding behavior, not token price.

The market tabs render real 1-hour candlesticks built in SQL - no OHLC API, just MIN_BY / MAX_BY over decoded swap executions:

SELECT
  TIME_SLICE(s.block_time, 1, 'HOUR') AS h,
  MIN_BY(s.price_usd, s.block_time) AS open,
  MAX(s.price_usd) AS high,
  MIN(s.price_usd) AS low,
  MAX_BY(s.price_usd, s.block_time) AS close,
  SUM(s.sol_amount) AS volume
FROM STAGING.SWAP_EVENTS s
WHERE s.team_id = ? AND s.block_time >= DATEADD('hour', -72, CURRENT_TIMESTAMP())
GROUP BY 1
ORDER BY 1;

And the USD leg is genuinely live: the bridge pushes the real SOL/USD rate into the warehouse every 60 seconds, so the whole DAG's pricing tracks the market (SOL was $78.22 while I wrote this):

// bridge/server.ts (excerpt) - the warehouse gets a live price artery
async function updateSolPrice() {
  const res = await fetch(
    "https://api.coingecko.com/api/v3/simple/price?ids=solana&vs_currencies=usd"
  );
  const px = (await res.js