1. The Universal Challenge: The "New Protocol" Learning Curve

2. Building an LLM-First Pipeline for Move Package Analysis

3. Case Study: Bluefin Pro Perps Analysis

   • LLM Pipeline Workflow

   • Prompt Iteration Highlights

   • Takeaways & Results

4. Final Outputs

   • SQL views and daily metrics

   • Charts and dashboards

5. Appendix: Technical References

   • Condensed Prompt Chat (all with superwhisper)

   • Final LLM SQL Output at end of convo:

   • Move Package Catalogue Tasks

     • Events

     • Objects

     • Snowflake Table Data Catalogue

     • Bluefin Daily Market View Output:

A persistent challenge in crypto data analytics is the sheer diversity of smart contracts. Every protocol ships its own set of contracts that must be fully understood before you can build data pipelines and extract actionable insights.

On Sui alone, there are 95k**+ Move packages** emitting 350,000+ events and nearly 900k objects—each with distinct schema and structure. Even protocols in the same category, such as the CLMM-based DEXs Cetus and Bluefin, can have significant implementation differences. Even when they don’t, the contracts must still be analyzed to confirm parity. This constant variability makes it challenging for analysts to keep pace with the rapid innovation happening on any chain.

I prototyped an LLM-enabled data pipeline to accelerate package analysis by strictly managing a local, context-rich environment. The system centers on two curated data catalogues that provide the model with the critical context it needs. I have attached the snowflake tasks and view in the appendix for additional reference:

1. Snowflake Data Catalogue – A SQL view extracts metadata for all relevant Snowflake tables. The results are exported as CSV, and a Python script converts each row into a .sql file, with a strict 1:1 mapping between table and file. This setup gives the LLM complete context for 400+ Snowflake tables, enabling it to selectively load only the subset needed for a given task.

2. Move Package Catalogue – Snowflake tasks index and store all unique event and object data for a specific package. The output is stored in an LLM-friendly format, giving the model a complete on-chain view for that package. With this holistic view of objects and events, the LLM can detect patterns and relationships more effectively.

This initial design keeps LLM access fully isolated—no direct database reads or writes. It also works well with cost-efficient models; for example, I primarily use Gemini Flash 2.5 (non-max mode) for most tasks.

These foundational tables and views provide exceptional context for the LLM. By comparison, Cursor rules have shown little additional performance benefit. There are some very basic rules that I currently use:

• Show sample datasets for core Snowflake tables (events, objects, transactions) and some non-core DeFi tables (coin info, prices). This is somewhat redundant with the Snowflake Data Catalogue now and was a legacy cursor rule.

• Include simple operational guidelines such as: write queries efficiently, check Snowflake SQL syntax, and be concise.

With this foundation in place, I tested the pipeline on a real-world case—analyzing Bluefin’s Pro Perps package.

1. Find relevant package data – Manually locate Bluefin Pro package objects and events from the Move Package Catalogue and export them for LLM use.

2. Prompt the LLM with context – Load the package data into the LLM and ask it to identify relevant events and generate a query for daily trading volume by perpetual market.

3. Iterate on results – Review outputs, make targeted corrections, and request additional metrics (funding rate, daily price, daily active users, open interest).

4. Plot the results and check accuracy

• First pass – Query worked, but BTC, ETH, and SOL market decimals were off by 1. Prompted the LLM to adjust.

• Subsequent passes – Added average daily funding rate, daily price, DAUs, and open interest.

• Challenge – Open interest calculations were consistently wrong; the LLM lacked the right DeFi context. Several prompts failed to fix it until switching from “daily average” to “end-of-day snapshot” calculations.

• Resolution – After explicitly flagging the snapshot logic as incorrect, the LLM revised it to calculate open interest correctly.

• LLM finds relevant events autonomously – Without any manual intervention, it correctly identified key events from the package-level context.

• Complex metrics need iteration – Multiple passes are normal for nuanced DeFi calculations like open interest.

• Flagging > explaining – Pointing out “this snapshot is wrong” was more effective than prescribing exact fixes.

• Documentation is built-in – Successful conversations leave the LLM with the full context to auto-generate comments for tables, views, and columns.

The end result is an underlying table that makes it effortless to plot something like this:

Take a look at all of these Bluefin Pro package events. They have different event names, they have module names, as well as event JSON information. Now the file is a little messy, but are you able, can you infer which events I should focus on and create a SQL query so that I can get the trading volume broken down by perp market every day? @bbluefin_pro_package_events.csv The prompt worked on the first try. After initial inspection I noticed that the BTC, ETH, and SOL market decimals were off by 1. I told it to "fix it". Okay, this looks almost correct. It looks like the BTC perp market and the ETH perp market are one decimal off. So like BTC says, for example, 93 million, but I think it's closer to 9.3 million. Same with ETH. Can you please fix that just for BTC and ETH? The rest of the decimals look good, though. Fix was implemented. Then I realized that Solana market decimals were off too. Also I casually mentioned if it could find the funding rate from the events and calculate the daily average funding rate. I didn't specify the event or the event type structure because LLM has the package context already. Ah, my bad. It looks like Solana, the Soul Perp market also has an extra decimal, so please move it. It says 32 million, but I think it should be closer to 3.2 million. Otherwise it looks good. Additionally, are you able to add a column to get the average funding rate, the average daily funding rate for each market?@bluefin_pro_package_events.csv I also asked for a daily average price column, a daily active user count, and a daily average open interest as well. These were all added in after a first request. Upon inspecting the query outputs, I notiecd that the open interest was denominated in the native token amounts. So I asked "add usd open interest column too".

The open interest calculation was incorrect. The LLM doesn't have the right DeFi context to properly calculate open interest so I guided it a little. The re-prompt did not fix it properly: Okay, well, so I like the columns, but I'm not sure that the output is correct. How exactly are you calculating the average? Because I think this is a little complex because you have to, you can't just take all of the orders and then take the average. That's going to over count the numbers because you're not a count, you need to properly account for like how many orders are open at every funding interval versus how many get closed and then take the average of just the open orders at every funding interval.

I changed query prompt approach again. Still incorrect. Um, actually instead of doing a daily average, could we just take a daily snapshot instead? Uh, do this, take the snapshot for the end of every day for all the positions that are open.

Third time is the charm! The problem was isolated to the snapshot calculation and pointed out and the LLM was able to revise the snapshot calculations to correctly calculate the open interest. No, this isn't correct still. The numbers for the open interest in USD amounts are still way too high. I don't know what you're doing here. I don't know how to troubleshoot this either, but the numbers are still way too high. So I don't know if the snapshot is being calculated properly. I would guess you're still accounting for positions that are...you haven't properly accounted for positions that are closed, maybe?

create or replace task PIPELINE_V2_GROOT_DB.PIPELINE_V2_GROOT_SCHEMA.POPULATE_EVENT_TYPE_SAMPLES_TASK
	warehouse=DEFI_WH
	schedule='USING CRON 0 * * * * America/New_York'
	COMMENT='Populates and updates the EVENT_TYPE_SAMPLES_TB with new unique event types and their sample data, avoiding full scans by only processing recent events and checking for existing event types.'
	as BEGIN
  CREATE TABLE IF NOT EXISTS PIPELINE_V2_GROOT_DB.PIPELINE_V2_GROOT_SCHEMA.EVENT_TYPE_SAMPLES_TB (
      EVENT_TYPE VARCHAR(16777216) PRIMARY KEY,
      PACKAGE VARCHAR(16777216) COMMENT 'The package ID part of the event type.',
      MODULE VARCHAR(16777216) COMMENT 'The module name part of the event type.',
      FIRST_APPEARANCE_TRANSACTION_DIGEST VARCHAR(16777216),
      SAMPLE_EVENT_JSON VARIANT,
      FIRST_APPEARANCE_TIMESTAMP_MS NUMBER(38,0),
      FIRST_APPEARANCE_UTC TIMESTAMP_LTZ(3),
      LAST_UPDATED_AT TIMESTAMP_NTZ(9) DEFAULT CURRENT_TIMESTAMP()
  )
  COMMENT='Auxiliary table storing a single sample transaction and event JSON for each unique event type. Populated by POPULATE_EVENT_TYPE_SAMPLES_TASK.';  INSERT INTO PIPELINE_V2_GROOT_DB.PIPELINE_V2_GROOT_SCHEMA.EVENT_TYPE_SAMPLES_TB (
      EVENT_TYPE,
      PACKAGE,
      MODULE,
      FIRST_APPEARANCE_TRANSACTION_DIGEST,
      SAMPLE_EVENT_JSON,
      FIRST_APPEARANCE_TIMESTAMP_MS,
      FIRST_APPEARANCE_UTC
  )
  SELECT
      e.event_type,
      SPLIT_PART(e.event_type, '::', 1) AS package,
      SPLIT_PART(e.event_type, '::', 2) AS module,
      ANY_VALUE(e.transaction_digest) AS first_appearance_transaction_digest,
      ANY_VALUE(e.event_json) AS sample_event_json,
      MIN(e.timestamp_ms) AS first_appearance_timestamp_ms,
      TO_TIMESTAMP_LTZ(MIN(e.timestamp_ms), 3) AS first_appearance_utc
  FROM
      event_parquet e
  WHERE
      e.timestamp_ms > COALESCE(
          (SELECT MAX(first_appearance_timestamp_ms) FROM PIPELINE_V2_GROOT_DB.PIPELINE_V2_GROOT_SCHEMA.EVENT_TYPE_SAMPLES_TB),
          0
      )
      AND NOT EXISTS (
          SELECT 1
          FROM PIPELINE_V2_GROOT_DB.PIPELINE_V2_GROOT_SCHEMA.EVENT_TYPE_SAMPLES_TB ets
          WHERE ets.event_type = e.event_type
      )
  GROUP BY
      e.event_type;
END;

create or replace view PIPELINE_V2_GROOT_DB.PIPELINE_V2_GROOT_SCHEMA.DATA_CATALOGUE_VIEW(
	TABLE_SCHEMA,
	TABLE_NAME,
	TABLE_TYPE,
	DESCRIPTION,
	COLUMN_METADATA_JSON,
	COLUMN_COMMENTS,
	LAST_ALTERED
) COMMENT='Provides a comprehensive catalogue of tables and views within the PIPELINE_V2_GROOT_SCHEMA, including table-level descriptions and detailed metadata for each column (name, type, and comment).'
 as
SELECT
    t.TABLE_SCHEMA AS TABLE_SCHEMA,       -- Schema name
    t.TABLE_NAME AS TABLE_NAME,           -- Table name
    t.TABLE_TYPE AS TABLE_TYPE,           -- Type: BASE TABLE, VIEW, etc.
    t.COMMENT AS DESCRIPTION,             -- Table-level description
    OBJECT_AGG(c.COLUMN_NAME, c.DATA_TYPE::VARIANT) AS COLUMN_METADATA_JSON,       -- Column names and types as a JSON object (cast DATA_TYPE to VARIANT)
    ARRAY_AGG(c.COMMENT) WITHIN GROUP (ORDER BY c.ORDINAL_POSITION) AS COLUMN_COMMENTS,        -- Column-level comments as an array
    t.LAST_ALTERED AS LAST_ALTERED        -- Last altered time
FROM INFORMATION_SCHEMA.TABLES t
LEFT JOIN INFORMATION_SCHEMA.COLUMNS c
    ON t.TABLE_CATALOG = c.TABLE_CATALOG
    AND t.TABLE_SCHEMA = c.TABLE_SCHEMA
    AND t.TABLE_NAME = c.TABLE_NAME
WHERE t.TABLE_SCHEMA = 'PIPELINE_V2_GROOT_SCHEMA'
GROUP BY t.TABLE_SCHEMA, t.TABLE_NAME, t.TABLE_TYPE, t.COMMENT, t.LAST_ALTERED;