📰 2023-11-17: Weekly Prophet! #4823

andrewhong5297 · 2023-11-17T15:07:09Z

andrewhong5297
Nov 17, 2023
Collaborator

This is your weekly summary of 34 PRs merged from 20 wizards. Great job everyone! 🎉
We had 36 added models 🟢 and 69 modified models 🟠 for 20 Sectors.

Sector	🔧 # PRs	🟢 # Added	🟠 # Modified	🧙 # Authors
dex	5	1	6	4
tokens	1	0	1	1
prices	1	0	1	1
_sector	10	10	14	10
meteora	1	1	0	1
oneinch	5	0	8	1
ens	2	4	8	2
balancer	2	8	12	2
cow_protocol	1	0	1	1
addresses_events	1	2	2	1
evms	1	0	1	1
mento	1	3	0	1
raydium	1	0	2	1
solana_utils	3	0	3	2
tokenlon	1	0	6	1
addresses	1	1	0	1
uniswap	1	2	1	1
staking	2	1	1	2
maker	1	0	1	1
safe	1	3	1	1

SECTOR: dex

toggle to see all model updates

MODEL: dex_solana_trades.sql

🟠 Modified by:

🔧 PR: #4802, add meteora dex and fix tensor cnft
🧙 Author: @andrewhong5297 on 2023-11-17
📝 Summary: In this diff, a new reference to the model 'meteora_v1_solana_trades' was added. This means that the SQL model now includes a reference to this specific table in addition to the existing references to 'phoenix_v1_trades', 'lifinity_v1_trades', and 'lifinity_v2_trades'. The loop over solana_dexes remains unchanged.

MODEL: dex_raw_pools.sql

🟢 Added by:

🔧 PR: #4807, init dex.raw_pools
🧙 Author: @grkhr on 2023-11-16
📝 Summary: This SQL model creates a query that retrieves information about pools created on various blockchains. It joins the 'pool_created_logs' table with the 'creation_traces' table to get details such as blockchain, type, version, pool address, tokens involved in the pool creation, creation block time and number, and contract address. The model also includes some TODOs and FYIs related to missing data from certain blockchains.

MODEL: dex_info.sql

🟠 Modified by:

🔧 PR: #4413, Celo Mento Protocol trades
🧙 Author: @tomfutago on 2023-11-14
📝 Summary: In this diff, a new row was added to the temporary table. The row represents a project called 'Mento' with the name 'mento', marketplace type 'Direct', and x_username as its identifier.

MODEL: dex_pools.sql

🟠 Modified by:

🔧 PR: #4413, Celo Mento Protocol trades
🧙 Author: @tomfutago on 2023-11-14
📝 Summary: In this diff, a reference to the 'mento_celo_pools' table was added.

MODEL: dex_aggregator_trades.sql

🟠 Modified by:

🔧 PR: #4752, Add tokenlon_trades
🧙 Author: @0xkeen on 2023-11-14
📝 Summary: The reference models that were added or removed in the given diff are:

Added: tokenlon_trades
Removed: dodo_aggregator_trades, zeroex_trades, kyberswap_aggregator_trades

MODEL: dex_trades.sql

🟠 Modified by:

🔧 PR: #4763, Oneinch updates 4
🧙 Author: @grkhr on 2023-11-13
📝 Summary: The reference models that were added or removed in the diff are: oneinch_lop_own_trades.

MODEL: dex_trades_seed.csv

🟠 Modified by:

🔧 PR: #4763, Oneinch updates 4
🧙 Author: @grkhr on 2023-11-13
📝 Summary: [changes too large] The model dex_trades_seed.csv was modified.

SECTOR: tokens

toggle to see all model updates

MODEL: tokens_ethereum_erc20.sql

🟠 Modified by:

🔧 PR: #4776, [EASY] Add missing tokens
🧙 Author: @harisang on 2023-11-13
📝 Summary: [changes too large] The model tokens_ethereum_erc20.sql was modified.

SECTOR: prices

toggle to see all model updates

MODEL: prices_base_tokens.sql

🟠 Modified by:

🔧 PR: #4787, wsteth, aero prices on base
🧙 Author: @ppclunghe on 2023-11-15
📝 Summary: The added token symbols are: wstETH, AERO

SECTOR: _sector

toggle to see all model updates

MODEL: nft_optimism_native_mints.sql

🟠 Modified by:

🔧 PR: #4816, [EASY]Add new join condition to correct nft.mints value
🧙 Author: @chuxinh on 2023-11-17
📝 Summary: The added logic includes a left join with the 'tokens_optimism_nft_bridged_mapping' table and a left join with the 'transfers_optimism_eth' table. The condition for joining these tables is that the transaction hash, block number, and 'from' address in transfers_optimism_eth should match with tx_hash, block_number, and 'to' address in nft_mints. Additionally, there is an additional condition that tr.value_decimal should be greater than 0. There is also a left join with the source table 'prices' using blockchain='optimism'.

MODEL: tensorswap_v2_solana_trades.sql

🟠 Modified by:

🔧 PR: #4802, add meteora dex and fix tensor cnft
🧙 Author: @andrewhong5297 on 2023-11-17
📝 Summary: The main logic added in this SQL model is a conditional statement that checks if the 'call_block_time' is greater than or equal to a specific timestamp. If it is, then the 'maxAmount' value is divided by 1e9 before being cast as double. Otherwise, it remains unchanged. This change was made as a temporary fix due to an unknown issue with decimals becoming larger. The other additions include calculating the taker fee and royalty fee based on certain calculations and assigning values for maker_fee and price columns.

MODEL: _schema.yml

🟠 Modified by:

🔧 PR: #4774, Add check_seed tests and seed files with new schema to base_trades models
🧙 Author: @Hosuke on 2023-11-16
📝 Summary: [changes too large] The model _schema.yml was modified.

MODEL: tokens_ethereum_balances.sql

🟢 Added by:

🔧 PR: #4640, Token balances ethereum
🧙 Author: @aalan3 on 2023-11-15
📝 Summary: This SQL model creates a new table called 'balances_enrich' by enriching the data from the existing table 'tokens_ethereum_base_balances'. It enables data analysts to have access to additional information and insights related to Ethereum token balances.

MODEL: tokens_ethereum_base_balances.sql

🟢 Added by:

🔧 PR: #4640, Token balances ethereum
🧙 Author: @aalan3 on 2023-11-15
📝 Summary: This SQL model creates a union of two tables, tokens_ethereum_base_balances_erc20 and tokens_ethereum_base_balances_native. It enables data analysts to retrieve the combined data from these two tables. If incremental mode is enabled, it also applies a filter based on the block_time column.

MODEL: tokens_ethereum_base_balances_erc20.sql

🟢 Added by:

🔧 PR: #4640, Token balances ethereum
🧙 Author: @aalan3 on 2023-11-15
📝 Summary: This SQL model creates a view called 'balances_base' that retrieves data from the 'tokens_ethereum_base_transfers' table. It filters the data based on the blockchain being Ethereum and token standard being ERC20. This view enables data analysts to easily access and analyze balance information for Ethereum tokens using standardized criteria.

MODEL: tokens_ethereum_base_balances_native.sql

🟢 Added by:

🔧 PR: #4640, Token balances ethereum
🧙 Author: @aalan3 on 2023-11-15
📝 Summary: This SQL model creates a view called 'balances_base' that retrieves data from the 'tokens_ethereum_base_transfers' table. It filters the data based on the blockchain being Ethereum and token standard being native. This model enables data analysts to easily access and analyze balance information for Ethereum tokens with a native token standard.

MODEL: tokens_ethereum_transfers.sql

🟠 Modified by:

🔧 PR: #4640, Token balances ethereum
🧙 Author: @aalan3 on 2023-11-15
📝 Summary: In this diff, the main change is in the transfers_enrich model. The blockchain parameter remains unchanged as 'ethereum'. However, there is a modification in the transfers_base parameter. The reference to 'tokens_ethereum_transfers_base' has been replaced with 'tokens_ethereum_base_transfers'. Additionally, a new parameter called native_symbol has been added with a value of 'ETH'.

MODEL: dex_celo_base_trades.sql

🟠 Modified by:

🔧 PR: #4413, Celo Mento Protocol trades
🧙 Author: @tomfutago on 2023-11-14
📝 Summary: In this diff, three new models (mento_v1_celo_base_trades, mento_v2_celo_base_trades) were added to the base_models list. These models are then used in a common table expression called base_union.

MODEL: mento_v1_celo_base_trades.sql

🟢 Added by:

🔧 PR: #4413, Celo Mento Protocol trades
🧙 Author: @tomfutago on 2023-11-14
📝 Summary: This SQL model creates a view called 'base_trades' that combines data from three different tables in the 'mento_celo' database. It selects various columns related to token exchanges, including block time, block number, token amounts bought and sold, addresses of the tokens involved in each trade, taker and maker information, project contract address, transaction hash and event index. The final query then selects specific columns from the 'base_trades' view to create a result set with additional information such as blockchain type (celo), project name (mento), version number (1), block month and date.

MODEL: mento_v2_celo_base_trades.sql

🟢 Added by:

🔧 PR: #4413, Celo Mento Protocol trades
🧙 Author: @tomfutago on 2023-11-14
📝 Summary: This SQL model creates a table that captures data related to swaps on the Celo blockchain for the Mento project. It includes information such as block month, block date, block time, block number, taker (buyer), maker (seller), token bought amount in raw format, token sold amount in raw format, USD value of the transaction amount (null for now), addresses of tokens bought and sold, project contract address involved in the swap transaction and other relevant details. This model enables data analysts to analyze swap transactions on Celo blockchain for the Mento project.

MODEL: slugs_optimism_rewards.sql

🟢 Added by:

🔧 PR: #4777, Add Slugs on optimism to the referral rewards sector
🧙 Author: @datadanne on 2023-11-14
📝 Summary: This SQL model creates a dataset that includes information about blockchain events related to the 'optimism' project and the 'slugs' category. It provides details such as block number, block time, transaction hash, referrer address, referee address, currency contract, reward amount raw value, project contract address,and sub-transaction ID. The model joins two sources of data and applies incremental updates if specified. The dataset is filtered to include only events where 'isCustom' is true.

MODEL: referral_staging_rewards.sql

🟠 Modified by:

🔧 PR: #4770, Add Slugs on optimism to the referral rewards sector
🧙 Author: @datadanne on 2023-11-14
📝 Summary: The model added a reference to 'slugs_optimism_rewards' and kept the references to 'rabbithole_optimism_rewards', 'rabbithole_polygon_rewards', and 'soundxyz_ethereum_rewards'. It also removed the reference to 'soundxyz_optimism_rewards'. The SELECT statement remains unchanged.

🔧 PR: #4770, [easy] add zora_zora_rewards to referral sector
🧙 Author: @0xRobin on 2023-11-14
📝 Summary: The model added a reference to 'zora_zora_rewards'.

MODEL: nft_zora_transfers.sql

🟢 Added by:

🔧 PR: #4767, Add zora to nft.transfers
🧙 Author: @hildobby on 2023-11-14
📝 Summary: This dbt SQL model creates a table called 'nft_transfers' that combines data from multiple sources related to NFT transfers on the Zora blockchain. It enables data analysts to analyze and gain insights into transactions, ERC721 transfers, ERC1155 single transfers, and ERC1155 batch transfers on the Zora blockchain.

MODEL: nft_transfers.sql

🟠 Modified by:

🔧 PR: #4767, Add zora to nft.transfers
🧙 Author: @hildobby on 2023-11-14
📝 Summary: In this diff, a reference to the 'nft_zora_transfers' table was added to the SQL model.

MODEL: quix_v1_optimism_events.sql

🟠 Modified by:

🔧 PR: #4783, fix implicit coercion bugs: change from decimal to double
🧙 Author: @jeff-dude on 2023-11-13
📝 Summary: In this SQL model, the calculation for platform fee amount and percentage has been modified. The previous calculations used a cast function and division by power(10,t1.decimals), while the updated calculations use explicit casting with 'double' keyword and multiplication by 2.5. Additionally, the calculation for platform fee amount in USD now includes multiplying by p1.price.

MODEL: quix_v2_optimism_events.sql

🟠 Modified by:

🔧 PR: #4783, fix implicit coercion bugs: change from decimal to double
🧙 Author: @jeff-dude on 2023-11-13
📝 Summary: The main logic that was added in this SQL model is the conversion of numeric values using the double data type. The calculations for platform_fee_amount_raw, platform_fee_amount, platform_fee_amount_usd, and platform_fee_percentage now use the new syntax with explicit casting to double. This change ensures accurate calculations and conversions for these fields.

MODEL: quix_v3_optimism_events.sql

🟠 Modified by:

🔧 PR: #4783, fix implicit coercion bugs: change from decimal to double
🧙 Author: @jeff-dude on 2023-11-13
📝 Summary: In this SQL model, the calculation for platform fee amount and percentage has been modified. The previous calculations used integer division and casting, while the new calculations use double precision floating-point numbers. Additionally, the platform fee amount in USD is now calculated by multiplying with a price value from another table. The royalty fee amount remains unchanged.

MODEL: quix_v4_optimism_events.sql

🟠 Modified by:

🔧 PR: #4783, fix implicit coercion bugs: change from decimal to double
🧙 Author: @jeff-dude on 2023-11-13
📝 Summary: The main logic that was added in this SQL model is the conversion of certain calculations to use the double data type instead of cast. Specifically, the calculation for platform_fee_amount_raw, platform_fee_amount, and platform_fee_amount_usd now use the syntax (double '2.5' * ...). Additionally, there is a new line that casts '2.5' as a double and assigns it to the variable platform_fee_percentage.

MODEL: quix_v5_optimism_events.sql

🟠 Modified by:

🔧 PR: #4783, fix implicit coercion bugs: change from decimal to double
🧙 Author: @jeff-dude on 2023-11-13
📝 Summary: In this SQL model, the logic for calculating platform fee amounts and percentages has been modified. The previous calculations used a fixed value of 2.5 as the platform fee percentage, but now it is casted as a double and multiplied with other values to calculate the platform fee amount in raw units, in token units (divided by decimals), and in USD. The calculation for royalty fees remains unchanged.

MODEL: dex_avalanche_c_base_trades.sql

🟢 Added by:

🔧 PR: #4717, Add trades model of avalanche_c of Uniswap
🧙 Author: @lequangphu on 2023-11-13
📝 Summary: This SQL model creates a union of multiple base models, specifically the 'uniswap_v3_avalanche_c_base_trades' model. It adds additional columns to the unioned data, including 'from', 'to', and 'index'. This enables data analysts to analyze trade data from different sources in a unified manner and perform further analysis on transactions involving specific addresses or indices.

MODEL: uniswap_v3_avalanche_c_base_trades.sql

🟢 Added by:

🔧 PR: #4717, Add trades model of avalanche_c of Uniswap
🧙 Author: @lequangphu on 2023-11-13
📝 Summary: This SQL model creates a table called 'uniswap_compatible_v3_trades' that enables data analysts to analyze trades on the Uniswap V3 protocol in the Avalanche C blockchain. It pulls data from two sources: 'Pair_evt_Swap' and 'Factory_evt_PoolCreated'.

MODEL: dex_base_trades.sql

🟠 Modified by:

🔧 PR: #4717, Add trades model of avalanche_c of Uniswap
🧙 Author: @lequangphu on 2023-11-13
📝 Summary: In this diff, a new reference to the model 'dex_avalanche_c_base_trades' was added.

SECTOR: meteora

toggle to see all model updates

MODEL: meteora_v1_solana_trades.sql

🟢 Added by:

🔧 PR: #4802, add meteora dex and fix tensor cnft
🧙 Author: @andrewhong5297 on 2023-11-17
📝 Summary: This SQL model creates a dataset that captures swap transactions on the Solana blockchain. It includes information such as the project, version, block time, token pair traded, trade source (direct or outer executing account), token amounts bought and sold (both raw and converted to decimals), USD value of the transaction, fee tier and fee in USD if available. It also includes details about the tokens involved (symbol and mint address) as well as vault addresses for both tokens.

SECTOR: oneinch

toggle to see all model updates

MODEL: oneinch_ethereum_calls_transfers.sql

🟠 Modified by:

🔧 PR: #4820, oneinch: add call_remains to calls_transfers 3/3 (eth)
🧙 Author: @grkhr on 2023-11-17
📝 Summary: In this diff, the 'tags' parameter with the value 'prod_exclude' was removed from the dbt SQL model. The unique_key parameter was also modified to only include 'unique_call_transfer_id' as its value.

MODEL: oneinch_calls.sql

🟠 Modified by:

🔧 PR: #4782, oneinch: add call_remains to calls_transfers 3/3 (eth)
🧙 Author: @grkhr on 2023-11-17
📝 Summary: In this diff, a new column called 'call_remains' was added to the SQL model. The previous column 'call_output' was also modified to include an additional value of 'any_value'.

🔧 PR: #4782, oneinch: hotfix performance issue
🧙 Author: @grkhr on 2023-11-14
📝 Summary: In this SQL model, the logic that was added includes changing the aggregation function for 'block_time' from 'max' to 'group', and changing the aggregation functions for 'tx_from', 'tx_to', 'tx_success', and 'call_success' from 'max' to

MODEL: oneinch_calls_transfers_amounts.sql

🟠 Modified by:

🔧 PR: #4792, oneinch: add call_remains to calls_transfers 3/3 (eth)
🧙 Author: @grkhr on 2023-11-17
📝 Summary: In this diff, the line for 'call_remains' was removed and then added back. The other columns remain unchanged.

🔧 PR: #4792, oneinch: add call_remains to calls_transfers 1/n
🧙 Author: @grkhr on 2023-11-16
📝 Summary: The added logic includes selecting multiple columns from a table named 'oneinch_{blockchain}_calls_transfers'. The selected columns include blockchain, block_time, tx_hash, tx_from, tx_to, and more. The WHERE clause filters out rows where contract_address is null and tx_success is not null.

MODEL: oneinch_bnb_calls_transfers.sql

🟠 Modified by:

🔧 PR: #4818, oneinch: add call_remains to calls_transfers 2/3 (bnb)
🧙 Author: @grkhr on 2023-11-16
📝 Summary: In this diff, the 'tags' parameter with the value 'prod_exclude' was removed from the dbt SQL model. The unique_key parameter was also modified to only include 'unique_call_transfer_id' as its value.

MODEL: oneinch_lop.sql

🟠 Modified by:

🔧 PR: #4763, Oneinch updates 4
🧙 Author: @grkhr on 2023-11-13
📝 Summary: In this diff, the 'gnosis', 'optimism', and 'zksync' elements were removed from a list of supported networks, while the 'polygon' element was added.

MODEL: oneinch_lop_own_trades.sql

🟠 Modified by:

🔧 PR: #4763, Oneinch updates 4
🧙 Author: @grkhr on 2023-11-13
📝 Summary: The main logic added in this SQL model is the creation of two CTEs (Common Table Expressions) called 'tokens_src' and 'tokens_dst'. These CTEs select data from a reference table called 'tokens_erc20' and rename some columns. The purpose of these CTEs is to retrieve information about source tokens and destination tokens, including their symbols and decimals. This information will be used later in the model to calculate token amounts in USD.

SECTOR: ens

toggle to see all model updates

MODEL: ens_node_names.sql

🟠 Modified by:

🔧 PR: #4812, Revert 'ENS source.yml updated, ens_registrations, ens_node_names upd…
🧙 Author: @jeff-dude on 2023-11-16
📝 Summary: The added logic includes a union query that selects specific columns from the 'PublicResolver_v2_evt_AddrChanged' table in the 'ethereumnameservice_ethereum' source. The selected columns are address, node, block_number, block_time, tx_hash, and evt_index. There is also an optional condition to filter rows based on the evt_block_time column if it meets certain criteria.

🔧 PR: #4812, ENS source.yml updated, ens_registrations, ens_node_names updated
🧙 Author: @nyssarex on 2023-11-16
📝 Summary: The added logic in this SQL model includes a union with a select statement from another source table. The select statement retrieves specific columns from the source table and applies some filtering based on the block time. This logic is then used to populate a CTE called 'registrations'. There is also an additional conditional filtering based on incremental mode for both the initial query and the unioned query.

MODEL: ens_view_registrations.sql

🟠 Modified by:

🔧 PR: #4812, Revert 'ENS source.yml updated, ens_registrations, ens_node_names upd…
🧙 Author: @jeff-dude on 2023-11-16
📝 Summary: The added logic includes a SELECT statement that retrieves all columns from the table ETHRegistrarController_1_evt_NameRegistered in the ethereumnameservice_ethereum source. The result is then combined with the results of two other SELECT statements from tables ETHRegistrarController_2_evt_Name and ETHRegistrarController_3_evt_NameRegistered. The removed logic includes a UNIONed SELECT statement that retrieves specific columns from table ETHRegistrarController_4_evt_NameRegistered in the ethereumnameservice_ethereum source.

🔧 PR: #4812, ENS source.yml updated, ens_registrations, ens_node_names updated
🧙 Author: @nyssarex on 2023-11-16
📝 Summary: The added logic includes a UNION of three different sources from the 'ethereumnameservice_ethereum' database. The SELECT statement retrieves various columns such as contract_address, evt_tx_hash, evt_index, evt_block_time, etc., from the 'ETHRegistrarController_4_evt_NameRegistered' table.

MODEL: ens_view_expirations_postgres.csv

🟢 Added by:

🔧 PR: #4812, Revert 'ENS source.yml updated, ens_registrations, ens_node_names upd…
🧙 Author: @jeff-dude on 2023-11-16
📝 Summary: [changes too large] The model ens_view_expirations_postgres.csv was added.

🟠 Modified by:

🔧 PR: #4812, ENS source.yml updated, ens_registrations, ens_node_names updated
🧙 Author: @nyssarex on 2023-11-16
📝 Summary: [changes too large] The model ens_view_expirations_postgres.csv was removed.

MODEL: ens_view_registrations_postgres.csv

🟢 Added by:

🔧 PR: #4812, Revert 'ENS source.yml updated, ens_registrations, ens_node_names upd…
🧙 Author: @jeff-dude on 2023-11-16
📝 Summary: [changes too large] The model ens_view_registrations_postgres.csv was added.

🟠 Modified by:

🔧 PR: #4812, ENS source.yml updated, ens_registrations, ens_node_names updated
🧙 Author: @nyssarex on 2023-11-16
📝 Summary: [changes too large] The model ens_view_registrations_postgres.csv was removed.

MODEL: ens_view_registries_postgres.csv

🟢 Added by:

🔧 PR: #4812, Revert 'ENS source.yml updated, ens_registrations, ens_node_names upd…
🧙 Author: @jeff-dude on 2023-11-16
📝 Summary: [changes too large] The model ens_view_registries_postgres.csv was added.

🟠 Modified by:

🔧 PR: #4812, ENS source.yml updated, ens_registrations, ens_node_names updated
🧙 Author: @nyssarex on 2023-11-16
📝 Summary: [changes too large] The model ens_view_registries_postgres.csv was removed.

MODEL: ens_view_renewals_postgres.csv

🟢 Added by:

🔧 PR: #4812, Revert 'ENS source.yml updated, ens_registrations, ens_node_names upd…
🧙 Author: @jeff-dude on 2023-11-16
📝 Summary: [changes too large] The model ens_view_renewals_postgres.csv was added.

🟠 Modified by:

🔧 PR: #4812, ENS source.yml updated, ens_registrations, ens_node_names updated
🧙 Author: @nyssarex on 2023-11-16
📝 Summary: [changes too large] The model ens_view_renewals_postgres.csv was removed.

SECTOR: balancer

toggle to see all model updates

MODEL: balancer_v2_arbitrum_liquidity.sql

🟠 Modified by:

🔧 PR: #4711, Changes to balancer_v1 spells
🧙 Author: @viniabussafi on 2023-11-15
📝 Summary: In the given SQL model, the following changes were made: - A new column pool_address was added by extracting a substring from pool_id.

Two new columns version and blockchain were added with static values.
The existing column 'arbitrum' as blockchain was removed.

MODEL: balancer_v2_avalanche_c_liquidity.sql

🟠 Modified by:

🔧 PR: #4711, Changes to balancer_v1 spells
🧙 Author: @viniabussafi on 2023-11-15
📝 Summary: In this SQL model, the following changes were made: - A new column pool_address was added, which is a substring of the pool_id column.

The constant value '2' was added as a new column named version.
The constant value 'avalanche_c' was added as a new column named blockchain.

MODEL: balancer_liquidity.sql

🟠 Modified by:

🔧 PR: #4711, Changes to balancer_v1 spells
🧙 Author: @viniabussafi on 2023-11-15
📝 Summary: In this diff, a reference to the model 'balancer_v2_ethereum_liquidity' was removed and replaced with a reference to 'balancer_v1_ethereum_liquidity'. Additionally, two new references were added: 'balancer_v2_optimism_liquidity' and 'balancer_v2_arbitrum_liquidity'. The SELECT statement now includes the columns pool_address and version.

MODEL: balancer_v2_base_liquidity.sql

🟠 Modified by:

🔧 PR: #4711, Changes to balancer_v1 spells
🧙 Author: @viniabussafi on 2023-11-15
📝 Summary: In this SQL model, a new column called 'pool_address' is added to the result set. It uses the BYTEARRAY_SUBSTRING function to extract a substring of length 20 from the 'pool_id' column. Additionally, two new columns are added: 'version' with a value of '2' and 'blockchain' with a value of 'base'. The rest of the logic remains unchanged.

MODEL: balancer_v1_ethereum_liquidity.sql

🟠 Modified by:

🔧 PR: #4711, Changes to balancer_v1 spells
🧙 Author: @viniabussafi on 2023-11-15
📝 Summary: In this SQL model, the main logic that was added includes:

A new CTE called 'pool_labels' which selects the address and name from a specific table.
Additional columns (decimals) were added to the 'prices' CTE.
The calculation for token balance and protocol liquidity in the 'dex_prices_1' CTE was modified to handle cases where decimals are null or missing.
The calculation for liquidity in the final SELECT statement was changed to use protocol_liquidity_usd instead of amount_usd.

MODEL: balancer_v1_ethereum_pools_tokens_weights.sql

🟠 Modified by:

🔧 PR: #4711, Changes to balancer_v1 spells
🧙 Author: @viniabussafi on 2023-11-15
📝 Summary: The main logic added in this diff is the casting of the 'denorm' and 'sum_denorm' columns to DOUBLE data type. This is done in order to perform a division operation between them, resulting in the calculation of the 'normalized_weight'. The result is then selected along with other columns from tables settings and sum_denorm, using an inner join on pool addresses.

MODEL: balancer_v1_ethereum_trades.sql

🟠 Modified by:

🔧 PR: #4711, Changes to balancer_v1 spells
🧙 Author: @viniabussafi on 2023-11-15
📝 Summary: In this SQL model, the following changes were made: - A CTE called 'swap_fees' was added to calculate swap fees for each swap transaction.

The CTE 'v1' was modified to include the calculated swap fee percentage and join with the 'swap_fees' table.
A new CTE called 'prices' was added to fetch USD prices for Ethereum tokens.
The SELECT statement at the end of the model was modified to include additional columns such as blockchain, version, block_date, and block_month.

MODEL: balancer_v2_ethereum_liquidity.sql

🟠 Modified by:

🔧 PR: #4711, Changes to balancer_v1 spells
🧙 Author: @viniabussafi on 2023-11-15
📝 Summary: In the given SQL model, the following changes were made:

A new column pool_address was added by extracting a substring from pool_id.
The column version was added with a value of '2'.
The column blockchain now has a value of 'ethereum'.

MODEL: balancer_v2_gnosis_liquidity.sql

🟠 Modified by:

🔧 PR: #4711, Changes to balancer_v1 spells
🧙 Author: @viniabussafi on 2023-11-15
📝 Summary: In the given SQL model, the following changes were made:

A new column pool_address was added by extracting a substring from pool_id.
The value of the column version was changed to '2'.
The value of the column blockchain was changed to 'gnosis'.

MODEL: balancer_v2_optimism_liquidity.sql

🟠 Modified by:

🔧 PR: #4711, Changes to balancer_v1 spells
🧙 Author: @viniabussafi on 2023-11-15
📝 Summary: In the given SQL model, the following changes were made: - A new column pool_address was added by extracting a substring from pool_id.

Two new columns version and blockchain were added with static values.
The logic for selecting data remains unchanged.

MODEL: balancer_v2_polygon_liquidity.sql

🟠 Modified by:

🔧 PR: #4711, Changes to balancer_v1 spells
🧙 Author: @viniabussafi on 2023-11-15
📝 Summary: In the given SQL model, the following changes were made:

A new column pool_address was added by extracting a substring from pool_id.
The column version was added with a value of '2'.
The column blockchain now has a value of 'polygon' instead of being hardcoded as 'polygon'.
No other significant changes were made.

MODEL: balancer_v2_arbitrum_bpt_supply.sql

🟢 Added by:

🔧 PR: #4726, Init bpt_supply
🧙 Author: @thetroyharris on 2023-11-15
📝 Summary: [changes too large] The model balancer_v2_arbitrum_bpt_supply.sql was added.

MODEL: balancer_v2_avalanche_c_bpt_supply.sql

🟢 Added by:

🔧 PR: #4726, Init bpt_supply
🧙 Author: @thetroyharris on 2023-11-15
📝 Summary: [changes too large] The model balancer_v2_avalanche_c_bpt_supply.sql was added.

MODEL: balancer_bpt_supply.sql

🟢 Added by:

🔧 PR: #4726, Init bpt_supply
🧙 Author: @thetroyharris on 2023-11-15
📝 Summary: This SQL model creates a view that combines data from multiple tables representing different blockchain platforms. It includes information such as block time, block number, LP virtual supply raw and LP virtual supply for each pool ID. This enables data analysts to easily access and analyze the BPT (Balancer Pool Token) supply across various blockchain platforms in a single query.

MODEL: balancer_v2_base_bpt_supply.sql

🟢 Added by:

🔧 PR: #4726, Init bpt_supply
🧙 Author: @thetroyharris on 2023-11-15
📝 Summary: [changes too large] The model balancer_v2_base_bpt_supply.sql was added.

MODEL: balancer_v2_ethereum_bpt_prices.sql

🟠 Modified by:

🔧 PR: #4726, Init bpt_supply
🧙 Author: @thetroyharris on 2023-11-15
📝 Summary: The added logic includes a SELECT statement that retrieves the 'END' column as 'median_price' from the table 'price_formulation'. The result is then ordered in descending order based on the second and third columns.

MODEL: balancer_v2_ethereum_bpt_supply.sql

🟢 Added by:

🔧 PR: #4726, Init bpt_supply
🧙 Author: @thetroyharris on 2023-11-15
📝 Summary: [changes too large] The model balancer_v2_ethereum_bpt_supply.sql was added.

MODEL: balancer_v2_gnosis_bpt_supply.sql

🟢 Added by:

🔧 PR: #4726, Init bpt_supply
🧙 Author: @thetroyharris on 2023-11-15
📝 Summary: [changes too large] The model balancer_v2_gnosis_bpt_supply.sql was added.

MODEL: balancer_v2_optimism_bpt_supply.sql

🟢 Added by:

🔧 PR: #4726, Init bpt_supply
🧙 Author: @thetroyharris on 2023-11-15
📝 Summary: [changes too large] The model balancer_v2_optimism_bpt_supply.sql was added.

MODEL: balancer_v2_polygon_bpt_supply.sql

🟢 Added by:

🔧 PR: #4726, Init bpt_supply
🧙 Author: @thetroyharris on 2023-11-15
📝 Summary: [changes too large] The model balancer_v2_polygon_bpt_supply.sql was added.

SECTOR: cow_protocol

toggle to see all model updates

MODEL: cow_protocol_ethereum_app_data.sql

🟠 Modified by:

🔧 PR: #4789, fix columns in app data cow
🧙 Author: @olgafetisova on 2023-11-15
📝 Summary: In this SQL model, the following changes were made: - The 'widget' column was added to the 'content.metadata' CTE.

The 'widget' column was added to the 'unpacked_referrer_app_data' CTE.
Additional columns related to UTM parameters were added to the final query: utm_source, utm_medium, utm_content, utm_campaign, and utm_term.
Additional columns related to widget information were also added: widget_app_code and widget_environment.

SECTOR: addresses_events

toggle to see all model updates

MODEL: addresses_events_first_activity.sql

🟠 Modified by:

🔧 PR: #4768, Add zora to addresses_events.first_funded_by
🧙 Author: @hildobby on 2023-11-15
📝 Summary: The SQL model added a reference to 'addresses_events_zora_first_activity' in the SELECT statement.

MODEL: addresses_events_first_funded_by.sql

🟠 Modified by:

🔧 PR: #4768, Add zora to addresses_events.first_funded_by
🧙 Author: @hildobby on 2023-11-15
📝 Summary: The diff shows that a new reference to 'addresses_events_zora_first_funded_by' was added in the SQL model. The SELECT statement remains unchanged.

MODEL: addresses_events_zora_first_activity.sql

🟢 Added by:

🔧 PR: #4768, Add zora to addresses_events.first_funded_by
🧙 Author: @hildobby on 2023-11-15
📝 Summary: This SQL model creates a summary of blockchain transactions from the 'zora' source. It includes information such as the address, first activity to, first block time and number, first transaction hash, first function called, and the amount transferred in native currency. The model also performs a left join with another table to filter out duplicate functions. If it is an incremental update, it further filters based on date range and checks for null values in a specific column before grouping by address.

MODEL: addresses_events_zora_first_funded_by.sql

🟢 Added by:

🔧 PR: #4768, Add zora to addresses_events.first_funded_by
🧙 Author: @hildobby on 2023-11-15
📝 Summary: This SQL model creates a table called 'addresses_events_first_funded_by' that is specific to the blockchain 'zora'. It enables data analysts to analyze and track the first funding events associated with addresses on the zora blockchain.

SECTOR: evms

toggle to see all model updates

MODEL: evms_info.sql

🟠 Modified by:

🔧 PR: #4786, Celo evms.info native token address fix
🧙 Author: @tomfutago on 2023-11-15
📝 Summary: In this SQL model, a row was removed for the 'celo' cryptocurrency and a new row was added with updated information. The address associated with 'celo' changed from 0xe452e6ea2ddeb012e20db73bf5d3863a3ac8d77a to 0x471EcE3750Da237f93B8E339c536989b8978a438. The URL for 'celoscan.io' remained the same.

SECTOR: mento

toggle to see all model updates

MODEL: mento_celo_pools.sql

🟢 Added by:

🔧 PR: #4413, Celo Mento Protocol trades
🧙 Author: @tomfutago on 2023-11-14
📝 Summary: This SQL model creates a union of two pool models, 'mento_v1_celo_pools' and 'mento_v2_celo_pools'. It selects various columns including blockchain, project, version, pool details (fee, tokens), creation block time and number, and contract address. This enables data analysts to analyze and compare pools across different versions in the Celo blockchain ecosystem.

MODEL: mento_v1_celo_pools.sql

🟢 Added by:

🔧 PR: #4413, Celo Mento Protocol trades
🧙 Author: @tomfutago on 2023-11-14
📝 Summary: This SQL model creates a view that combines data from three different tables in the 'mento_celo' database. It selects specific columns from each table and assigns constant values to some columns. The resulting view includes information about blockchain, project, version, pool address, fee (set as null), token0 and token1 addresses, creation block time and number for each record.

MODEL: mento_v2_celo_pools.sql

🟢 Added by:

🔧 PR: #4413, Celo Mento Protocol trades
🧙 Author: @tomfutago on 2023-11-14
📝 Summary: This SQL model creates a view that retrieves data from two tables in the 'mento_celo' database. It selects specific columns and renames them accordingly. The model also includes a left join to filter out any records where the exchangeId exists in both tables, effectively selecting only active exchanges. Additionally, if incremental mode is enabled, it applies an incremental predicate based on the evt_block_time column for efficient data retrieval.

SECTOR: raydium

toggle to see all model updates

MODEL: raydium_v3_trades.sql

🟠 Modified by:

🔧 PR: #4781, Incremental token accounts (try again)
🧙 Author: @andrewhong5297 on 2023-11-14
📝 Summary: A left join was added to the model, linking a table called 'solana_utils_token_accounts' with another table named 'tr_1'. This join is based on the condition that the address in tk_1 matches the account_destination in tr_1. Additionally, a comment line was added for testing purposes.

MODEL: raydium_v4_trades.sql

🟠 Modified by:

🔧 PR: #4781, Incremental token accounts (try again)
🧙 Author: @andrewhong5297 on 2023-11-14
📝 Summary: In the given SQL model, a condition is added to filter data based on the call block time. If a project start date is provided, it checks if the call block time is greater than or equal to that date. Additionally, there is a comment added '--force'.

SECTOR: solana_utils

toggle to see all model updates

MODEL: solana_utils_token_accounts.sql

🟠 Modified by:

🔧 PR: #4780, Incremental token accounts (try again)
🧙 Author: @andrewhong5297 on 2023-11-14
📝 Summary: In this SQL model, the changes made include:

Renaming the table alias aa for better readability.
Adding a conditional statement to filter data based on incremental updates using is_incremental() and incremental_predicate().
Overall, these changes improve the clarity and efficiency of the code.

🔧 PR: #4780, make token_accounts incremental
🧙 Author: @andrewhong5297 on 2023-11-13
📝 Summary: In the given SQL model, the changes made include:

Renaming variables and table aliases.
Adding a conditional statement to filter data based on incremental predicate if it exists.

🔧 PR: #4780, Revert 'make token_accounts incremental (#4779)'
🧙 Author: @jeff-dude on 2023-11-13
📝 Summary: The added logic in this SQL model includes creating a CTE called 'distinct_accounts' that selects specific columns from the 'account_activity' table. It filters out rows where the token_mint_address is null and groups the results by token_mint_address and address. The max_by function is used to select the maximum value of token_balance_owner based on block_time, while min(block_time) gives us the earliest created_at timestamp for each group.

SECTOR: tokenlon

toggle to see all model updates

MODEL: tokenlon_ethereum_trades.sql

🟠 Modified by:

🔧 PR: #4752, Add tokenlon_trades
🧙 Author: @0xkeen on 2023-11-14
📝 Summary: In the given SQL model, a new column called 'block_month' was added. This column is included in the SELECT statement along with other existing columns like project, version, block_date, block_time, token_bought_symbol and token_sold_symbol.

MODEL: tokenlon_v5_ethereum_amm_v1_trades.sql

🟠 Modified by:

🔧 PR: #4752, Add tokenlon_trades
🧙 Author: @0xkeen on 2023-11-14
📝 Summary: In this SQL model, the logic for casting the 'token_bought_amount_raw' and 'token_sold_amount_raw' columns has been changed. Previously, they were cast as DECIMAL(38, 0), but now they are cast as UINT256. Additionally, a COALESCE function has been added to calculate the value of 'amount_usd' based on different conditions.

MODEL: tokenlon_v5_ethereum_amm_v2_trades.sql

🟠 Modified by:

🔧 PR: #4752, Add tokenlon_trades
🧙 Author: @0xkeen on 2023-11-14
📝 Summary: In this SQL model, the logic for casting the 'token_bought_amount_raw' and 'token_sold_amount_raw' columns has been changed. Previously, they were cast as DECIMAL(38, 0), but now they are cast as UINT256. Additionally, there is a coalesce function that calculates the value of 'amount_usd' based on either its existing value or by multiplying 'token_bought_amount_raw' with price from another table.

MODEL: tokenlon_v5_ethereum_pmm_v5_trades.sql

🟠 Modified by:

🔧 PR: #4752, Add tokenlon_trades
🧙 Author: @0xkeen on 2023-11-14
📝 Summary: In this SQL model, the logic for casting the 'token_bought_amount_raw' and 'token_sold_amount_raw' columns has been changed. Previously, they were cast as DECIMAL(38, 0), but now they are cast as UINT256. Additionally, a COALESCE function has been added to calculate the value of 'amount_usd' based on different conditions.

MODEL: tokenlon_v5_ethereum_rfq_v1_trades.sql

🟠 Modified by:

🔧 PR: #4752, Add tokenlon_trades
🧙 Author: @0xkeen on 2023-11-14
📝 Summary: In this SQL model, the logic for casting the 'token_bought_amount_raw' and 'token_sold_amount_raw' columns has been changed. Previously, they were cast as DECIMAL(38, 0), but now they are cast as UINT256. Additionally, there is a coalesce function that calculates the value of 'amount_usd' based on either its existing value or by multiplying 'token_bought_amount_raw' with price from another table.

MODEL: tokenlon_trades.sql

🟠 Modified by:

🔧 PR: #4752, Add tokenlon_trades
🧙 Author: @0xkeen on 2023-11-14
📝 Summary: In the given SQL model, a new column called 'block_month' was added. This column is included in the SELECT statement along with other existing columns like project, version, block_date, block_time, token_bought_symbol and token_sold_symbol.

SECTOR: addresses

toggle to see all model updates

MODEL: addresses_base_dex.sql

🟢 Added by:

🔧 PR: #4758, base chain dex & dex-agg routers
🧙 Author: @RantumBits on 2023-11-14
📝 Summary: This SQL model creates a table that includes the columns 'address', 'dex_name', and 'distinct_name'. It populates this table with values from a VALUES statement, which specifies different addresses, dex names, and distinct names for various entities such as exchanges and routers. This model enables data analysts to query and analyze information related to these entities based on their addresses, dex names, or distinct names.

SECTOR: uniswap

toggle to see all model updates

MODEL: uniswap_avalanche_c_trades.sql

🟢 Added by:

🔧 PR: #4717, Add trades model of avalanche_c of Uniswap
🧙 Author: @lequangphu on 2023-11-13
📝 Summary: This SQL model creates a view that combines data from multiple Uniswap V3 Avalanche C trades tables. It includes information such as blockchain, project, version, block date and time, token symbols and amounts bought/sold, USD value of the trade, addresses involved in the trade (buying/selling), taker/maker details, contract address used for the project on Avalanche C chain. This view enables data analysts to easily access and analyze consolidated trading data from multiple sources within Uniswap V3 on Avalanche C chain.

MODEL: uniswap_v3_avalanche_c_trades.sql

🟢 Added by:

🔧 PR: #4717, Add trades model of avalanche_c of Uniswap
🧙 Author: @lequangphu on 2023-11-13
📝 Summary: This SQL model creates a dataset that provides information on Uniswap v3 trades in the Avalanche blockchain. It includes details such as the block date, block time, token symbols for bought and sold tokens, amounts of tokens bought and sold, USD value of the trade, addresses of the tokens involved in the trade, taker and maker addresses, project contract address,and transaction details. The model joins multiple tables to retrieve this information from different sources.

MODEL: uniswap_trades.sql

🟠 Modified by:

🔧 PR: #4717, Add trades model of avalanche_c of Uniswap
🧙 Author: @lequangphu on 2023-11-13
📝 Summary: The diff shows that a reference to the 'uniswap_avalanche_c_trades' table was added to the SQL model.

SECTOR: staking

toggle to see all model updates

MODEL: staking_solana_stake_actions.sql

🟢 Added by:

🔧 PR: #4457, Add solana staking actions
🧙 Author: @andrewhong5297 on 2023-11-13
📝 Summary: This SQL model creates a table that combines data from three different sources: 'account_activity', 'stake_call_Merge', 'stake_call_Withdraw', and 'stake_call_Split'. It filters the data based on certain conditions and joins them together to create a unifi

📰 2023-11-17: Weekly Prophet! #4823

andrewhong5297 Nov 17, 2023 Collaborator

SECTOR: dex

toggle to see all model updates

MODEL: dex_solana_trades.sql

🟠 Modified by:

MODEL: dex_raw_pools.sql

🟢 Added by:

MODEL: dex_info.sql

🟠 Modified by:

MODEL: dex_pools.sql

🟠 Modified by:

MODEL: dex_aggregator_trades.sql

🟠 Modified by:

MODEL: dex_trades.sql

🟠 Modified by:

MODEL: dex_trades_seed.csv

🟠 Modified by:

SECTOR: tokens

toggle to see all model updates

MODEL: tokens_ethereum_erc20.sql

🟠 Modified by:

SECTOR: prices

toggle to see all model updates

MODEL: prices_base_tokens.sql

🟠 Modified by:

SECTOR: _sector

toggle to see all model updates

MODEL: nft_optimism_native_mints.sql

🟠 Modified by:

MODEL: tensorswap_v2_solana_trades.sql

🟠 Modified by:

MODEL: _schema.yml

🟠 Modified by:

MODEL: tokens_ethereum_balances.sql

🟢 Added by:

MODEL: tokens_ethereum_base_balances.sql

🟢 Added by:

MODEL: tokens_ethereum_base_balances_erc20.sql

🟢 Added by:

MODEL: tokens_ethereum_base_balances_native.sql

🟢 Added by:

MODEL: tokens_ethereum_transfers.sql

🟠 Modified by:

MODEL: dex_celo_base_trades.sql

🟠 Modified by:

MODEL: mento_v1_celo_base_trades.sql

🟢 Added by:

MODEL: mento_v2_celo_base_trades.sql

🟢 Added by:

MODEL: slugs_optimism_rewards.sql

🟢 Added by:

MODEL: referral_staging_rewards.sql

🟠 Modified by:

MODEL: nft_zora_transfers.sql

🟢 Added by:

MODEL: nft_transfers.sql

🟠 Modified by:

MODEL: quix_v1_optimism_events.sql

🟠 Modified by:

MODEL: quix_v2_optimism_events.sql

🟠 Modified by:

MODEL: quix_v3_optimism_events.sql

🟠 Modified by:

MODEL: quix_v4_optimism_events.sql

🟠 Modified by:

MODEL: quix_v5_optimism_events.sql

🟠 Modified by:

MODEL: dex_avalanche_c_base_trades.sql

🟢 Added by:

MODEL: uniswap_v3_avalanche_c_base_trades.sql

🟢 Added by:

MODEL: dex_base_trades.sql

🟠 Modified by:

SECTOR: meteora

toggle to see all model updates

MODEL: meteora_v1_solana_trades.sql

🟢 Added by:

SECTOR: oneinch

toggle to see all model updates

andrewhong5297
Nov 17, 2023
Collaborator