Our schemas are broken down into three types of data: raw, decoded, and enriched. Raw data is pure data ETL'd from the blockchain. Decoded adds a layer of decoding to the raw data (ie decoding with ABIs and IDLs). Finally, enriched are application-specific tables built with DBT (uniswap, compound, magic eden etc). Coherent is developing an open-source library of DBT scripts that can be run on top of our dataset to produce any materialized view of the data. This allows developers to create any custom view of blockchain data they want.
Our raw dataset is ETL'd data in it's most basic form. We take the raw JSON RPC responses, flatten them and write them to tables. This data can be consumed through parquet or any of our data warehouses.
Our decoded tables add a layer on top of raw. We have the most comprehensive library of ABIs and IDLs that exists. We index over 5m smart contracts across all of the EVM chains and hundreds of Solana programs. We use these to decode upwards of 90% of logs and write these to tables. This is what forms the basis of our enriched tables. With this decoded data, it's possible to make virtually any materialized view per protocol.
Our enriched tables comprise of materialized views of per-protocol data that can be used to get application-specific data. Our entire system revolves around DBT, a framework for writing these materialized views on top of SQL. We'll officially launch our open-source library of DBT views at the end of Q1 2023.
For all smart contract blockchains (Ethereum, Solana, Sui), we provide a comprehensive list of verbs that connect onchain actions, including method calls and instructions, with their corresponding human-readable "verb." This enables applications like accounting, audit and tax to classify transactions and machine learning applications to build features around user intent. These tables map instruction or method id with human-readable verbs in a standardized way. For example, you can join with transactions and group by swaps to get the best view of onchain swaps.
Another key table is our rates table which provides USD rates for every currency we index. This allows you to get to-the-minute price data that can be used to better understand onchain values in a human-readable way.
Our contracts tables map contract metadata, including name, symbol, token decimals, etc, to contract address and blockchain.
DBT and protocol-specific tables coming end of Q1 2023