📰 2024-03-08: Weekly Prophet!

[andrewhong5297]

This is your weekly summary of 20 PRs merged from 12 wizards. Great job everyone! 🎉
We had 25 added models 🟢 and 23 modified models 🟠 for 9 Sectors.

Sector	🔧 # PRs	🟢 # Added	🟠 # Modified	🧙 # Authors
dex	4	0	4	1
nft	1	0	1	1
prices	1	0	1	1
opensea	2	0	2	1
_sector	13	24	11	7
balancer	1	0	1	1
op	1	0	1	1
kyberswap	1	1	1	1
oneinch	1	0	1	1

SECTOR: dex

toggle to see all model updates

MODEL: dex_info.sql

🟠 Modified by:

🔧 PR: #5481, ADDED SYNTHSWAP (BASE) TO DEX.TRADES
🧙 Author: @sophie-analytics on 2024-03-04
📝 Summary: A new record for 'synthswap' with corresponding values was added to the existing list of records in the model.

🔧 PR: #5481, Added energon dex to dex.trades
🧙 Author: @sophie-analytics on 2024-03-04
📝 Summary: A new project called 'Energon' with corresponding details was added to the existing list of projects in the SQL model.

🔧 PR: #5481, Added voltswap to dex.trades
🧙 Author: @sophie-analytics on 2024-03-04
📝 Summary: A new row for 'VoltSwap' with corresponding values was added to the temporary table.

🔧 PR: #5481, Added crescent swap to dex.trades
🧙 Author: @sophie-analytics on 2024-03-04
📝 Summary: A new row for 'crescentswap' with corresponding values was added to the temporary table.

SECTOR: nft

toggle to see all model updates

MODEL: nft_marketplaces_info.sql

🟠 Modified by:

🔧 PR: #5469, Fix Magic Eden Ethereum & Polygon fees
🧙 Author: @hildobby on 2024-03-04
📝 Summary: A row was updated in the model to change 'MagicEden' to 'Magic Eden'.

SECTOR: prices

toggle to see all model updates

MODEL: prices_zksync_tokens.sql

🟠 Modified by:

🔧 PR: #5470, Add more tokens for zkSync prices
🧙 Author: @lgingerich on 2024-03-05
📝 Summary: wstETH, DAI, LINK, MATIC, LDO, INJ, CRO,
ARB,rETH,WOO,GALA ,CRV,RPL,KNC,WAGMI,SIS,
CAKE,DXTF,GRAI,ZZ ,WEFI ,LAUNCH UNI ,
SHIB ,GOVI,ZKPEPE,ZAT,KAT,ZKID

SECTOR: opensea

toggle to see all model updates

MODEL: opensea_v4_polygon_events.sql

🟠 Modified by:

🔧 PR: #5492, Update opensea_v4_polygon_events.sql
🧙 Author: @couralex6 on 2024-03-07
📝 Summary: A condition was added to filter out a specific transaction hash from the results.

🔧 PR: #5492, dup tx in opensea_v4_polygon_events.sql
🧙 Author: @couralex6 on 2024-03-01
📝 Summary: Two conditions were added to the WHERE clause: excluding rows where tx_hash is a specific value and another specific value.

SECTOR: _sector

toggle to see all model updates

MODEL: nft_arbitrum_base_trades.sql

🟠 Modified by:

🔧 PR: #5500, Add Element Arbitrum to NFT Trades - Ready for review
🧙 Author: @henrystats on 2024-03-07
📝 Summary: A reference to a new model element_arbitrum_base_trades was added in this pull request.

MODEL: element_arbitrum_base_trades.sql

🟢 Added by:

🔧 PR: #5500, Add Element Arbitrum to NFT Trades - Ready for review
🧙 Author: @henrystats on 2024-03-07
📝 Summary: This model creates a base table for trades on the Arbitrum blockchain by extracting data from various ERC721 and ERC1155 events. It enables data analysts to analyze and track filled sell and buy orders for both types of tokens, providing insights into trading activities on the Arbitrum network.

MODEL: referral_staging_rewards.sql

🟠 Modified by:

🔧 PR: #5516, add: fabric on referral staging
🧙 Author: @vinliao on 2024-03-07
📝 Summary: A reference to a model named 'paragraph_polygon_rewards' was removed, and references to models named 'fabric_ethereum_rewards', 'fabric_optimism_rewards', 'fabric_base_rewards', and 'fabric_zora_rewards' were added.

MODEL: nft_zksync_base_trades.sql

🟠 Modified by:

🔧 PR: #5458, Add Kreatorland and Element to nft_zksync_base_trades
🧙 Author: @thetroyharris on 2024-03-07
📝 Summary: Added references to three new dbt models: 'tevaera_zksync_base_trades', 'kreatorland_zksync_base_trades', and 'element_zksync_base_trades' in the list of NFT models.

MODEL: element_zksync_base_trades.sql

🟢 Added by:

🔧 PR: #5458, Add Kreatorland and Element to nft_zksync_base_trades
🧙 Author: @thetroyharris on 2024-03-07
📝 Summary: This model creates a base table for trades on the Zksync blockchain by extracting data from different order events related to ERC721 and ERC1155 tokens. It enables data analysts to analyze and track filled buy and sell orders for NFTs on the Zksync blockchain, providing insights into trading activities involving these specific token types.

MODEL: kreatorland_zksync_base_trades.sql

🟢 Added by:

🔧 PR: #5458, Add Kreatorland and Element to nft_zksync_base_trades
🧙 Author: @thetroyharris on 2024-03-07
📝 Summary: This model creates a base table that extracts and transforms data related to considerations and offers from a specific source. It then generates another table focusing on trades, including details like blockchain, project information, trade type, price calculations, fees breakdowns, NFT contract details, and more. The model aims to provide structured insights for data analysts regarding transactions in the context of Kreatorland activities on the Zksync platform.

MODEL: tokens_avalanche_c_balances.sql

🟢 Added by:

🔧 PR: #5505, Scroll avalanche balances
🧙 Author: @aalan3 on 2024-03-06
📝 Summary: This model creates a new table by fixing the schema of balances data from a source related to Avalanche cryptocurrency. It enriches this data, enabling analysts to work with improved and standardized balance information for further analysis.

MODEL: tokens_avalanche_c_balances_daily.sql

🟢 Added by:

🔧 PR: #5505, Scroll avalanche balances
🧙 Author: @aalan3 on 2024-03-06
📝 Summary: This model creates a table called balances_daily that aggregates daily token balances starting from September 25, 2020, specifically for the native token 'AVAX'. This enables data analysts to analyze and track the daily balances of AVAX tokens over time.

MODEL: tokens_avalanche_c_balances_daily_agg.sql

🟢 Added by:

🔧 PR: #5505, Scroll avalanche balances
🧙 Author: @aalan3 on 2024-03-06
📝 Summary: This model creates an enriched version of daily token balances by merging data from a base table. It enables data analysts to easily access and analyze aggregated daily balance information for Avalanche tokens.

MODEL: tokens_avalanche_c_balances_daily_agg_base.sql

🟢 Added by:

🔧 PR: #5505, Scroll avalanche balances
🧙 Author: @aalan3 on 2024-03-06
📝 Summary: This model creates a daily aggregated view of token balances on the Avalanche blockchain. It enables data analysts to easily analyze and track changes in token balances over time, providing valuable insights into user behavior and network activity.

MODEL: tokens_scroll_balances.sql

🟢 Added by:

🔧 PR: #5505, Scroll avalanche balances
🧙 Author: @aalan3 on 2024-03-06
📝 Summary: This model creates a new table that enriches raw balance data from a source, likely for tokens. It enables data analysts to work with improved and standardized balance information for further analysis and reporting.

MODEL: tokens_scroll_balances_daily.sql

🟢 Added by:

🔧 PR: #5505, Scroll avalanche balances
🧙 Author: @aalan3 on 2024-03-06
📝 Summary: This new dbt SQL model creates a table called balances_daily that enables data analysts to view aggregated daily balance information starting from October 10, 2023.

MODEL: tokens_scroll_balances_daily_agg.sql

🟢 Added by:

🔧 PR: #5505, Scroll avalanche balances
🧙 Author: @aalan3 on 2024-03-06
📝 Summary: This SQL model creates an enriched version of daily token balances by aggregating and transforming data from a base table. It enables data analysts to easily analyze and track changes in token balances over time at a daily level.

MODEL: tokens_scroll_balances_daily_agg_base.sql

🟢 Added by:

🔧 PR: #5505, Scroll avalanche balances
🧙 Author: @aalan3 on 2024-03-06
📝 Summary: This SQL model creates a daily aggregated view of token balances by utilizing raw balance data. It enables data analysts to easily analyze and track changes in token balances on a daily basis for reporting and insights.

MODEL: fabric_base_rewards.sql

🟢 Added by:

🔧 PR: #5453, add: fabric subscription protocol
🧙 Author: @vinliao on 2024-03-05
📝 Summary: This dbt SQL model creates a table called fabric_referral_rewards that enables data analysts to track and analyze referral rewards related to blockchain transactions. It pulls data from two sources in the fabric_base schema: SubscriptionTokenV1_evt_ReferralPayout and SubscriptionTokenV1Factory_call_deploySubscription.

MODEL: fabric_ethereum_rewards.sql

🟢 Added by:

🔧 PR: #5453, add: fabric subscription protocol
🧙 Author: @vinliao on 2024-03-05
📝 Summary: This dbt SQL model creates a table to track referral rewards on the Ethereum blockchain. It enables data analysts to analyze and report on referral payouts related to subscription token deployments, sourced from the 'fabric_ethereum' database.

MODEL: fabric_optimism_rewards.sql

🟢 Added by:

🔧 PR: #5453, add: fabric subscription protocol
🧙 Author: @vinliao on 2024-03-05
📝 Summary: This dbt SQL model creates a table called fabric_referral_rewards in the database. It enables data analysts to track and analyze referral rewards related to subscriptions on the Optimism blockchain by pulling data from two specific sources: SubscriptionTokenV1_evt_ReferralPayout and SubscriptionTokenV1Factory_call_deploySubscription in the fabric_optimism schema.

MODEL: fabric_zora_rewards.sql

🟢 Added by:

🔧 PR: #5453, add: fabric subscription protocol
🧙 Author: @vinliao on 2024-03-05
📝 Summary: This dbt SQL model creates a table called fabric_referral_rewards that enables data analysts to track and analyze referral rewards related to the Zora blockchain. It pulls data from two sources in the fabric_zora schema: SubscriptionTokenV1_evt_ReferralPayout and SubscriptionTokenV1Factory_call_deploySubscription.

MODEL: eas_attestation_details.sql

🟠 Modified by:

🔧 PR: #5374, EAS - add Scroll + tweaks
🧙 Author: @tomfutago on 2024-03-04
📝 Summary: A reference to a model named 'eas_scroll_attestation_details' was added, while the reference to 'eas_polygon_attestation_details' was removed.

MODEL: eas_attestations.sql

🟠 Modified by:

🔧 PR: #5374, EAS - add Scroll + tweaks
🧙 Author: @tomfutago on 2024-03-04
📝 Summary: A reference to a model named 'eas_polygon_attestations' was removed, and references to models named 'eas_scroll_attestations' were added.

MODEL: eas_schema_details.sql

🟠 Modified by:

🔧 PR: #5374, EAS - add Scroll + tweaks
🧙 Author: @tomfutago on 2024-03-04
📝 Summary: A reference to a schema details model for Polygon was removed, and references to schema details models for Polygon and Scroll were added.

MODEL: eas_schemas.sql

🟠 Modified by:

🔧 PR: #5374, EAS - add Scroll + tweaks
🧙 Author: @tomfutago on 2024-03-04
📝 Summary: A reference to a schema named 'eas_polygon_schemas' was removed, and references to schemas named 'eas_polygon_schemas' and 'eas_scroll_schemas' were added.

MODEL: eas_scroll_attestation_details.sql

🟢 Added by:

🔧 PR: #5374, EAS - add Scroll + tweaks
🧙 Author: @tomfutago on 2024-03-04
📝 Summary: This SQL model creates a table called eas_attestation_details that enables data analysts to access and analyze attestation details related to the EAS project using the Scroll blockchain technology.

MODEL: eas_scroll_attestations.sql

🟢 Added by:

🔧 PR: #5374, EAS - add Scroll + tweaks
🧙 Author: @tomfutago on 2024-03-04
📝 Summary: This dbt SQL model creates a table called eas_attestations that enables data analysts to track attestations in the 'scroll' blockchain for the 'eas' project. The table includes a column named 'schemaUID' and is part of version 1 of the schema.

MODEL: eas_scroll_schema_details.sql

🟢 Added by:

🔧 PR: #5374, EAS - add Scroll + tweaks
🧙 Author: @tomfutago on 2024-03-04
📝 Summary: This SQL model creates a schema details table for the 'eas' project with blockchain information, enabling data analysts to access and analyze specific details related to the 'scroll' blockchain version 1 within the EAS project.

MODEL: eas_scroll_schemas.sql

🟢 Added by:

🔧 PR: #5374, EAS - add Scroll + tweaks
🧙 Author: @tomfutago on 2024-03-04
📝 Summary: This dbt SQL model creates and enables a schema called 'eas_schemas' with specified attributes like blockchain, project, and version. This will help data analysts organize and manage data related to the EAS project within the specified blockchain environment.

MODEL: magiceden_ethereum_base_trades.sql

🟠 Modified by:

🔧 PR: #5469, Fix Magic Eden Ethereum & Polygon fees
🧙 Author: @hildobby on 2024-03-04
📝 Summary: Added a CTE to bundle whitelisted trades by grouping them. Adjusted the fees calculation logic to include platform and royalty fee addresses and amounts based on conditions. Updated the join condition in the main query to use bundled whitelisted trades instead of individual ones, with additional filtering criteria. Joined 'fees' table using block number, transaction hash, and currency contract address matching conditions in an outer join for Ethereum blockchain data retrieval.

MODEL: magiceden_v2_polygon_base_trades.sql

🟠 Modified by:

🔧 PR: #5469, Fix Magic Eden Ethereum & Polygon fees
🧙 Author: @hildobby on 2024-03-04
📝 Summary: A new CTE named bundled_whitelisted_trades was added to aggregate sellers from whitelisted_trades. The join condition in the fees CTE was updated to use bundled_whitelisted_trades instead of whitelisted_trades, and a filter condition using contains() function was replaced with NOT contains(). Additionally, a new condition involving contract addresses was added in the base trades CTE.

MODEL: synthswap_base_base_trades.sql

🟢 Added by:

🔧 PR: #5474, ADDED SYNTHSWAP (BASE) TO DEX.TRADES
🧙 Author: @sophie-analytics on 2024-03-04
📝 Summary: This dbt SQL model creates a table called uniswap_compatible_v2_trades that enables data analysts to analyze and track trades on the Uniswap V2 platform. The model sources data from two different tables related to synthetic assets trading, allowing for further analysis within the specified blockchain and project parameters.

MODEL: solidly_v3_base_base_trades.sql

🟢 Added by:

🔧 PR: #5477, Added solidly to dex.trades
🧙 Author: @sophie-analytics on 2024-03-04
📝 Summary: This dbt SQL model creates a table called uniswap_compatible_v3_trades that enables data analysts to analyze and track trades on the Uniswap decentralized exchange protocol version 3. It sources data from events related to swaps and pool creations in the specified blockchain project.

MODEL: energon_base_base_trades.sql

🟢 Added by:

🔧 PR: #5478, Added energon dex to dex.trades
🧙 Author: @sophie-analytics on 2024-03-04
📝 Summary: This dbt SQL model creates a table called uniswap_compatible_v2_trades that enables data analysts to analyze and track trades on the EnergonDex platform. It sources data from events related to pair swaps and pair creations in the Energon base blockchain project, version 1.

MODEL: voltswap_base_base_trades.sql

🟢 Added by:

🔧 PR: #5480, Added voltswap to dex.trades
🧙 Author: @sophie-analytics on 2024-03-04
📝 Summary: This model creates a table called uniswap_compatible_v2_trades that enables data analysts to analyze trades compatible with Uniswap v2 on the VoltSwap platform. It includes information about pairs being swapped and pair creation events sourced from the Volt Base blockchain for project 'voltswap' version 2.

MODEL: crescentswap_base_base_trades.sql

🟢 Added by:

🔧 PR: #5481, Added crescent swap to dex.trades
🧙 Author: @sophie-analytics on 2024-03-04
📝 Summary: This model creates a table called uniswap_compatible_v3_trades that enables data analysts to analyze Uniswap V3 trades on the blockchain. It sources data from events related to swaps and pool creations in the Crescent project version 1, allowing for deeper insights into trading activities within this environment.

MODEL: tokens_polygon_balances_daily.sql

🟠 Modified by:

🔧 PR: #5493, Balances polygon fix
🧙 Author: @aalan3 on 2024-03-03
📝 Summary: A reference to a dbt model tokens_polygon_balances_daily_agg was added in the balances_daily model. The start date was set to '2021-05-28' and a native token value of 'MATIC' was specified.

MODEL: tokens_polygon_balances_daily_agg_base.sql

🟠 Modified by:

🔧 PR: #5493, Balances polygon fix
🧙 Author: @aalan3 on 2024-03-03
📝 Summary: A new parameter was added to the balances_fix_schema macro call in the SQL model. The value '0x00000000000000000000000...1010' was included as an additional argument when calling the macro with source data from 'tokens_polygon' and schema 'polygon'.

SECTOR: balancer

toggle to see all model updates

MODEL: balancer_v1_ethereum_liquidity.sql

🟠 Modified by:

🔧 PR: #5472, Convert balancer_v1_ethereum.liquidity to table
🧙 Author: @viniabussafi on 2024-03-05
📝 Summary: Added logic:

• Removed conditional logic related to incremental processing for filtering data based on a specific time frame.
• Added a join condition with the eth_prices table on the day column.

SECTOR: op

toggle to see all model updates

MODEL: op_token_distributions_optimism_project_wallets.sql

🟠 Modified by:

🔧 PR: #5496, Update Multisig wallet of projects in OP distribution tracker
🧙 Author: @intensodefi on 2024-03-05
📝 Summary: Four new entries were added to the values list with addresses and corresponding labels. All four have a 'Multisig' label.

SECTOR: kyberswap

toggle to see all model updates

MODEL: kyberswap_aggregator_base_trades.sql

🟢 Added by:

🔧 PR: #5482, Add Kyberswap Base to Aggregator Trades - Ready for review
🧙 Author: @henrystats on 2024-03-04
📝 Summary: This model creates a dataset that includes information about token swaps on the KyberSwap platform. It provides details such as the tokens involved in each swap, their amounts, USD value, transaction details, and contract addresses. Additionally, it joins with other sources to include token symbols and prices for further analysis by data analysts.

MODEL: kyberswap_aggregator_trades.sql

🟠 Modified by:

🔧 PR: #5482, Add Kyberswap Base to Aggregator Trades - Ready for review
🧙 Author: @henrystats on 2024-03-04
📝 Summary: A reference to a new table kyberswap_aggregator_base_trades was added. The condition for filtering data based on the block date was replaced with a dynamic incremental predicate using the block time field.

SECTOR: oneinch

toggle to see all model updates

MODEL: oneinch_swaps.sql

🟠 Modified by:

🔧 PR: #5479, 1inch: updated the processing of unoswap logic & fixed some inconveniences in use 1inch spellbooks
🧙 Author: @max-morrow on 2024-03-04
📝 Summary: A new column 'call_type' was added to the model. The logic for determining the value of 'contracts_only' was updated based on conditions related to the protocol and method. Additionally, changes were made in how certain columns are concatenated into the 'flags' field. Finally, a new unique key generation function was added using specific columns for uniqueness in selecting final records from swaps table joined with amounts table.