This guide covers the Prices & Asset Metadata Pipeline, deployed to Crosschain, which involves Streamline, Bronze, Silver and Gold DBT models associated with various third-party price-based APIs, such as Coingecko and Coinmarketcap.

• There are three (3) general pipelines: Tokens, Native, Provider
  • Tokens: Assets with token addresses
    • This pipeline serves the ez_asset_metadata and ez_prices_hourly views
    • token_address is a crucial column for joining prices into other tables for USD pricing and other analysis
  • Native: Assets that are Native to the respective blockchain, such as ETH, POL, BNB, SOL etc.
    • Managed manually via the silver__native_assets_seed.csv seed file
    • This pipeline is unioned into ez_asset_metadata and ez_prices_hourly, with the is_native column to differentiate between native assets and token assets
  • Provider: Raw, non-transformed, non-imputed prices and asset metadata directly from the APIs
    • Includes QUALIFY filter(s) to ensure uniqueness
    • This pipeline serves the dim_asset_metadata and fact_prices_ohlc_hourly views

• Raw prices and asset metadata are built using third-party price APIs: Coingecko and Coinmarketcap
  • These are external APIs and we do not have control over the outputs, uptime or integrity of the data. We build the pipeline in a way that mitigates outages, missing or incorrect data.

1. Bronze Layer

   • x

2. Silver Layer

   • x

3. Gold Layer

   • x

• crosschain.bronze.x
• crosschain.bronze.x
• crosschain.bronze.x
• crosschain.silver.x
• crosschain.silver.x
• crosschain.silver.x
• crosschain.price.x
• crosschain.price.x

---

This guide covers how to build and deploy all tables and views associated with the Prices & Asset Metadata pipeline, in each respective Blockchain's repository, primarily with references to the Bronze, Silver and Gold.

• Use the crosschain.price.dim/fact tables to research which blockchains, platforms and symbols should be included in each respective chain's set up. Because this data is primarily sourced from third-party APIs, we do not have control over the names/symbols and therefore, data may be labeled incorrectly or in a misleading/incomplete fashion.
  • Some blockchains, such as Kaia or Polygon, may require multiple blockchains, platforms, symbols or token_address filters for the complete_token_prices/asset_metadata tables. See the prices macros documentation for more information on how to handle.

1. Bronze Schema

   • Each bronze model should be materialized as a view. No other model-level configuration parameters are necessary.

   {{ config (
       materialized = 'view',
       tags = ['bronze','prices','native','phase_3']
   ) }}

   • Add the following bronze schema models (bronze__<table_name>). Please reference fsc_evm price macros for details.
     • bronze__complete_native_asset_metadata.sql: Asset metadata associated with a specific blockchain(s) Native Asset(s) and the applicable symbol(s).
     • bronze__complete_native_prices.sql: Prices associated with a specific blockchain(s) Native Asset(s) and the applicable symbol(s).
     • bronze__complete_provider_asset_metadata.sql: Raw asset metadata for a specific platform(s), non-transformed and sourced directly from third-party price providers such as Coingecko and Coinmarketcap.
     • bronze__complete_provider_prices.sql: Raw open, high, low, close prices for all platforms, non-transformed and sourced directly from third-party price providers such as Coingecko and Coinmarketcap. This view will be joined with bronze__complete_provider_asset_metadata.sql in the silver layer to enable platform specific filtering.
     • bronze__complete_token_asset_metadata.sql: Metadata for assets with token_address, associated with a specific blockchain(s).
     • bronze__complete_token_prices.sql: Prices for assets with token_address, associated with a specific blockchain(s).

2. Silver Schema

   • Each silver model should be materialized as an incremental table, with the following configuration parameters.

   {{ config(
       materialized = 'incremental',
       incremental_strategy = 'delete+insert',
       unique_key = 'complete_native_asset_metadata_id',
       tags = ['silver','prices','native','phase_3']
   ) }}

   • Add the following silver schema models (silver__<table_name>).
     • silver__complete_native_asset_metadata.sql: Directly references bronze__complete_native_asset_metadata.sql and includes incremental logic on modified_timestamp.
     • silver__complete_native_prices.sql: Directly references bronze__complete_native_prices.sql and includes incremental logic on modified_timestamp.
     • silver__complete_provider_asset_metadata.sql: Directly references bronze__complete_provider_asset_metadata.sql and includes incremental logic on modified_timestamp.
     • silver__complete_provider_prices.sql: Includes an INNER JOIN between bronze__complete_provider_prices.sql and bronze__complete_provider_asset_metadata on asset_id to enable platform specific filtering and adds incremental logic on modified_timestamp.
     • silver__complete_token_asset_metadata.sql: Directly references bronze__complete_token_asset_metadata.sql and includes incremental logic on modified_timestamp.
     • silver__complete_token_prices.sql: Directly references bronze__complete_token_prices.sql and includes incremental logic on modified_timestamp.
   • Add respective .yml files for each silver model, which includes DBT tests.

3. Gold Schema (price)

   • Each Gold price model should be materialized as a view and includes the persist_docs param.

   {{ config(
       materialized = 'incremental',
       incremental_strategy = 'delete+insert',
       unique_key = 'dim_asset_metadata_id',
       post_hook = "ALTER TABLE {{ this }} ADD SEARCH OPTIMIZATION ON EQUALITY(asset_id, token_address, symbol, name),SUBSTRING(asset_id, token_address, symbol, name)",
       tags = ['gold','prices','phase_3']
   ) }}

   • Add the following price schema models (price__<table_name>).
     • price__dim_asset_metadata.sql: Directly references silver__complete_provider_asset_metadata.sql and updates column names.
     • price__ez_asset_metadata.sql: Unions all data from silver__complete_token_asset_metadata.sql and silver__complete_native_asset_metadata.sql, adds the is_native BOOL column and updates other column names.
     • price__ez_prices_hourly.sql: Unions all data from silver__complete_token_prices.sql and silver__complete_native_prices.sql, adds the is_native BOOL column and updates other column names.
     • price__fact_prices_ohlc_hourly.sql: Directly references silver__complete_provider_prices.sql and updates column names.
   • Add respective .yml files for each gold model, which includes table and column level descriptions.

• crosschain.silver.complete_token_prices