📰 2023-05-05: Weekly Prophet!

[andrewhong5297]

This is your weekly summary of 27 PRs merged from 15 wizards. Great job everyone! 🎉
We had 38 added models 🟢 and 38 modified models 🟠 for 19 Sectors.

Sector	🔧 # PRs	🟢 # Added	🟠 # Modified	🧙 # Authors
dex	1	0	1	1
nft	1	1	4	1
labels	2	0	2	2
tokens	6	0	7	3
prices	5	0	5	4
generic	1	1	0	1
zeroex	4	10	7	1
solana_utils	1	0	1	1
maker	1	0	1	1
_sector	2	7	1	1
nexusmutual	1	1	0	1
aave	1	17	1	1
models	1	1	0	1
foundation	1	0	1	1
superrare	1	0	1	1
lido	1	0	2	1
staking	1	0	1	1
contracts	1	0	1	1
op	1	0	2	1

SECTOR: dex

toggle to see all model updates

MODEL: dex_trades.sql

🟠 Modified by:

🔧 PR: #3256, Add Thena trades to dex trades
🧙 Author: @hsrvc on 2023-05-01
📝 Summary: The diff of the SQL model shows that 'thena_trades' reference model was added. No reference models were removed.

SECTOR: nft

toggle to see all model updates

MODEL: nft_arbitrum_aggregators.sql

🟢 Added by:

🔧 PR: #3267, Add new uniswap router to NFT aggregators
🧙 Author: @hildobby on 2023-05-04
📝 Summary: This SQL model creates a table that maps contract addresses to their corresponding names. It enables data analysts to easily identify contracts by name instead of having to remember or look up the associated address. The table only includes one record for now, but additional records can be added in the future as needed.

MODEL: nft_bnb_aggregators.sql

🟠 Modified by:

🔧 PR: #3267, Add new uniswap router to NFT aggregators
🧙 Author: @hildobby on 2023-05-04
📝 Summary: A new contract address and name were added to the SQL model for Uniswap's Universal Router.

MODEL: nft_ethereum_aggregators_manual.sql

🟠 Modified by:

🔧 PR: #3267, Add new uniswap router to NFT aggregators
🧙 Author: @hildobby on 2023-05-04
📝 Summary: The diff of the SQL model shows changes in the list of addresses and their corresponding names. One address was removed, two were added with new names, and some existing ones remained unchanged. The purpose or context for these addresses is not clear from this information alone.

MODEL: nft_optimism_aggregators.sql

🟠 Modified by:

🔧 PR: #3267, Add new uniswap router to NFT aggregators
🧙 Author: @hildobby on 2023-05-04
📝 Summary: This SQL model is a SELECT statement that creates a temporary table with three columns: contract_address, name and temp_table. The VALUES clause adds two rows to the table, one for bluesweep and another for okx. In addition, this diff adds another row to the temp_table using Uniswap's Universal Router 3 as its name and its corresponding contract address.

MODEL: nft_polygon_aggregators.sql

🟠 Modified by:

🔧 PR: #3267, Add new uniswap router to NFT aggregators
🧙 Author: @hildobby on 2023-05-04
📝 Summary: In this SQL model, two lines were added and one line was removed. The removed line contained an entry for Element NFT Marketplace Aggregator. The two added lines contain entries for Dew and Uniswap's Universal Router 3 with their respective contract addresses and names. These values are stored in a temporary table called temp_table.

SECTOR: labels

toggle to see all model updates

MODEL: labels_flashloans_ethereum.sql

🟠 Modified by:

🔧 PR: #3232, Aave Flashloans
🧙 Author: @hildobby on 2023-05-04
📝 Summary: This SQL model creates a table of flashloan users from different Ethereum-based protocols. It selects distinct addresses, assigns them to the 'flashloan_users' model and labels them as personas. The contributor is added for each protocol's data source, and the created_at date is set to October 8th, 2022 while updated_at reflects current time. Aave v1 Flashloan User was removed from this version of the code while new contributors were added for all other protocols in this merged pull request diff file.

MODEL: labels_trader_kyt.sql

🟠 Modified by:

🔧 PR: #3251, [Quickfix] Fix wrong selection
🧙 Author: @Hosuke on 2023-04-28
📝 Summary: The changes made to the SQL model involve adding and removing select statements. The removed statement selected a column called 'miner as address' from a table named 'labels_miners', while the added statement selected a column called 'address' from the same table. Additionally, there was an added union all clause that combined data from three different tables into one result set. Finally, there was an addition of two columns in the final select statement: blockchain and label_type were renamed to multi and usage respectively, while KYT.DexGuru remained unchanged as model_name.

SECTOR: tokens

toggle to see all model updates

MODEL: tokens_bnb_bep20.sql

🟠 Modified by:

🔧 PR: #3274, Add EDU, wBETH, RDNT to tokens
🧙 Author: @hsrvc on 2023-05-04
📝 Summary: This SQL model selects the contract address, symbol and decimals from a table and creates a temporary table with additional rows containing hardcoded values for different tokens. The added lines include token addresses, symbols and decimal places for ID, LVL, THE, EDU wBETH and RDNT tokens.

MODEL: tokens_ethereum_erc20.sql

🟠 Modified by:

🔧 PR: #3262, Add EDU, wBETH, RDNT to tokens
🧙 Author: @hsrvc on 2023-05-04
📝 Summary: The token symbol that was added is 'wBETH'. No tokens were removed.

🔧 PR: #3262, add 79 new tokens
🧙 Author: @bh2smith on 2023-05-01
📝 Summary: The following token symbols were added: HOMER, Pangea, SENDOR, PEPA, MOONED, BOBI,WOW,BMUSK ,DEGEN,WALTER,DVT ,SSB,BONK,RBX,CZ ,PEEPEE REST,Moonbase,MIA OK FRANK,pPEPE GECKO AVATR pepecoin CHIVA GRIMES PLSXIOU SLSD LFG Tesla AMC GM YEET NOBI BBBY GME IMFAO FUTURE MOMPEP HaHa DFV HODL ELON VB NPC FADER SATOSHI ALCXFRAXBP-f THEPEP KEKW BALAJI POV. The following tokens were removed: None.

MODEL: tokens_avalanche_c_erc20.sql

🟠 Modified by:

🔧 PR: #3260, Adding fBOMB, OATH, spGLCR and SUB
🧙 Author: @discochuck on 2023-05-04
📝 Summary: The added token symbols are fBOMB, OATH, spGLCR and SUB. No tokens were removed.

🔧 PR: #3260, Adding Wrapped MEMO and XEX
🧙 Author: @discochuck on 2023-05-02
📝 Summary: The tokens that were added are WMEMO and XEX.

🔧 PR: #3260, Adding GLCR, WLRS, XWLRS, MIGHT, MAGIK
🧙 Author: @discochuck on 2023-05-01
📝 Summary: The following token symbols were added: GLCR, XWLRS, MIGHT, MAGIK. No tokens were removed from the model.

MODEL: tokens_avalanche_c_nft_curated.sql

🟠 Modified by:

🔧 PR: #3261, Adding Walrus Anglers Club
🧙 Author: @discochuck on 2023-05-01
📝 Summary: The SQL model selects the contract address, name and symbol of different tokens. The '+' lines add new token information to the existing list.

SECTOR: prices

toggle to see all model updates

MODEL: prices_ethereum_tokens.sql

🟠 Modified by:

🔧 PR: #3254, add accepted range usd test on dex/nft schema
🧙 Author: @jeff-dude on 2023-05-04
📝 Summary: [changes too large] The model prices_ethereum_tokens.sql was modified.

🔧 PR: #3254, NFT restructuring
🧙 Author: @0xRobin on 2023-05-02
📝 Summary: The tokens that were added are: eth-ethereum, 0xbtc-0xbitcoin, 1inch-1inch and aave-new. The tokens that were removed are: xrio-realio-network-utility-token-exchangable, pepe-pepe1 and grain-granary. Note: There is no information on the total number of words in the list so I cannot cut it off at a maximum of 100 words.

🔧 PR: #3254, revert eth erc20 price
🧙 Author: @jeff-dude on 2023-05-02
📝 Summary: The token symbols that were removed from the SQL model are 'eth-ethereum'. The token symbols that were added to the SQL model are '0xbtc-0xbitcoin', '1inch-1inch', and 'aave-new'.

🔧 PR: #3254, add PancakeSwap Token on Ethereum
🧙 Author: @fahimahmedx on 2023-04-28
📝 Summary: The token symbol 'CAKE' was added.

MODEL: prices_polygon_tokens.sql

🟠 Modified by:

🔧 PR: #3268, To track $TETU price
🧙 Author: @surmaaa on 2023-05-02
📝 Summary: The token symbol that was added is TETU and the token symbol that was removed is OATH.

SECTOR: generic

toggle to see all model updates

MODEL: equal_rowcount_with_sources.sql

🟢 Added by:

🔧 PR: #3279, Source rowcount generic test
🧙 Author: @0xRobin on 2023-05-04
📝 Summary: This dbt SQL model enables data analysts to test whether the row count of a given model matches the sum of row counts from its source tables. It does this by comparing the number of rows in each table and returning an error if there is a difference between them. This helps ensure that all relevant data has been included in the final output, allowing for more accurate analysis.

SECTOR: zeroex

toggle to see all model updates

MODEL: zeroex_arbitrum_native_fills.sql

🟢 Added by:

🔧 PR: #3126, 0x_native fills (eth, poly, op, arb, multi)
🧙 Author: @RantumBits on 2023-05-02
📝 Summary: [changes too large] The model zeroex_arbitrum_native_fills.sql was added.

🟠 Modified by:

🔧 PR: #3126, zeroex - native_fills - decimals update
🧙 Author: @RantumBits on 2023-05-04
📝 Summary: The diff of the SQL model shows changes made to a query that retrieves data from an exchange proxy. The changes involve replacing division by 10 raised to the power of decimals with division by pow(10, decimals) in calculating maker_asset_filled_amount and taker_asset_filled_amount. Additionally, there are some minor modifications such as changing matcha_limit_order_flag value and adding/removing conditions for volume_usd calculation based on symbol values. Finally, protocol_fee_paid_eth is set to NULL in two queries where it was previously calculated using contract addresses.

MODEL: zeroex_bnb_native_fills.sql

🟢 Added by:

🔧 PR: #3126, 0x_native fills (eth, poly, op, arb, multi)
🧙 Author: @RantumBits on 2023-05-02
📝 Summary: [changes too large] The model zeroex_bnb_native_fills.sql was added.

🟠 Modified by:

🔧 PR: #3126, zeroex - native_fills - decimals update
🧙 Author: @RantumBits on 2023-05-04
📝 Summary: The diff of the SQL model shows changes made to a query that retrieves data from three different sources. The changes involve replacing division by 10^x with pow(10, x) for consistency and using COALESCE instead of CASE statements to calculate volume_usd. Additionally, the matcha_limit_order_flag column is now set to FALSE in all cases and protocol_fee_paid_eth is cast as null instead of being divided by 1e18.

MODEL: zeroex_ethereum_native_fills.sql

🟢 Added by:

🔧 PR: #3126, 0x_native fills (eth, poly, op, arb, multi)
🧙 Author: @RantumBits on 2023-05-02
📝 Summary: [changes too large] The model zeroex_ethereum_native_fills.sql was added.

🟠 Modified by:

🔧 PR: #3126, zeroex - native_fills - decimals update
🧙 Author: @RantumBits on 2023-05-04
📝 Summary: The changes made to the SQL model involve replacing division by 10^x with pow(10, x) for calculating asset amounts and prices. Additionally, a new column 'volume_usd' is added which calculates the USD value of each trade based on its maker/taker token amount and price. The 'protocol_fee_paid_eth' column is also modified to divide by 1e18 instead of using an integer division operator. Finally, some minor formatting changes are made at the end of the file related to conditional statements that do not affect functionality.

MODEL: zeroex_optimism_native_fills.sql

🟢 Added by:

🔧 PR: #3126, 0x_native fills (eth, poly, op, arb, multi)
🧙 Author: @RantumBits on 2023-05-02
📝 Summary: [changes too large] The model zeroex_optimism_native_fills.sql was added.

🟠 Modified by:

🔧 PR: #3126, zeroex - native_fills - decimals update
🧙 Author: @RantumBits on 2023-05-04
📝 Summary: The diff of the SQL model shows changes made to four different queries. In each query, the changes involve replacing division by 10 raised to a power with division by pow(10, x). Additionally, in some queries there are minor modifications such as changing boolean values and adding or removing lines that do not affect the main logic.

MODEL: zeroex_polygon_native_fills.sql

🟢 Added by:

🔧 PR: #3126, 0x_native fills (eth, poly, op, arb, multi)
🧙 Author: @RantumBits on 2023-05-02
📝 Summary: [changes too large] The model zeroex_polygon_native_fills.sql was added.

🟠 Modified by:

🔧 PR: #3126, zeroex - native_fills - decimals update
🧙 Author: @RantumBits on 2023-05-04
📝 Summary: The diff of the SQL model shows changes made to calculate volume and asset filled amount for different types of orders. The main change is in how decimals are handled while calculating maker_asset_filled_amount, taker_asset_filled_amount, and volume_usd. Instead of using 10^decimals, pow(10, decimals) is used which gives more accurate results. Additionally, some columns were renamed or added/removed based on the type of order being processed (e.g., matcha_limit_order_flag). Finally, protocol_fee_paid_eth was set to null for OtcOrderFilled as it doesn't have a protocol fee paid column.

MODEL: zeroex_bnb_api_fills.sql

🟠 Modified by:

🔧 PR: #3265, zeroex - bnb
🧙 Author: @RantumBits on 2023-05-04
📝 Summary: The diff of the SQL model shows changes made to a query that retrieves data from two tables. The first table is zeroex_v2_bnb and the second one is bnb. In both cases, some columns were added or removed. For example, in the first table (zeroex_v2_bnb) a new column called 'taker' was added while in the second one (bnb) some conditions were modified and two outer joins with another table called tokens_erc20 were changed to include additional logic for token addresses. Overall, these changes seem aimed at improving data retrieval performance by optimizing queries used to extract relevant information from these tables.

🔧 PR: #3265, zeroex - bnb - topic & default token updates
🧙 Author: @RantumBits on 2023-05-02
📝 Summary: The ERC20BridgeTransfer SQL model was modified in a pull request. The WHERE clause was updated to filter by a different topic1 value, and the contract addresses for taker_token and maker_token were changed from '0xeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee' to '0xbb4cdb9cbd36b01bd1cbaebf2de08d9173bc095c'. Two LEFT JOINs were added with conditions that join on minute values between all_tx.block_time and tp.minute or mp.minute respectively.

MODEL: zeroex_native_fills.sql

🟢 Added by:

🔧 PR: #3126, 0x_native fills (eth, poly, op, arb, multi)
🧙 Author: @RantumBits on 2023-05-02
📝 Summary: This SQL model creates a unified table by merging data from multiple ZeroEx native fills tables (for Ethereum, Polygon, Optimism, Arbitrum and Binance Smart Chain). This enables data analysts to easily query and analyze all the native fills across different networks in one place.

MODEL: zeroex_native_trades.sql

🟢 Added by:

🔧 PR: #3126, 0x_native fills (eth, poly, op, arb, multi)
🧙 Author: @RantumBits on 2023-05-02
📝 Summary: This SQL model creates a query that selects data from the 'zeroex_native_fills' table and combines it with any other tables specified in the 'zeroex_models' list. The resulting output includes information on token trades made through 0x API, such as token symbols, amounts bought/sold, USD value of trades, contract addresses involved in transactions and more. This enables data analysts to easily access and analyze trade data across multiple sources within a single query.

MODEL: zeroex_bnb_native_fills_test.sql

🟢 Added by:

🔧 PR: #3126, 0x_native fills (eth, poly, op, arb, multi)
🧙 Author: @RantumBits on 2023-05-02
📝 Summary: This SQL model creates a unit test to compare the data in two tables, zeroex_bnb_native_fills and zeroex_bnb_native_fills_sample. It checks if the maker and taker tokens match between both tables. The model then calculates the percentage of mismatches between these two tables. If this percentage is greater than 10%, it will return an error message indicating that there are significant differences between these two datasets. This enables data analysts to ensure that their data is accurate by comparing it with a sample dataset before using it for analysis or reporting purposes.

MODEL: zeroex_ethereum_native_fills_test.sql

🟢 Added by:

🔧 PR: #3126, 0x_native fills (eth, poly, op, arb, multi)
🧙 Author: @RantumBits on 2023-05-02
📝 Summary: This new dbt SQL model creates a unit test to compare the maker and taker tokens of two tables, zeroex_ethereum_native_fills and zeroex_ethereum_native_fills_sample. It then calculates the percentage of mismatches between these two tables. This enables data analysts to ensure that their data is accurate by comparing it with a sample dataset. If there are too many mismatches (greater than 10%), this may indicate an issue with the data or code that needs further investigation.

MODEL: zeroex_polygon_native_fills_test.sql

🟢 Added by:

🔧 PR: #3126, 0x_native fills (eth, poly, op, arb, multi)
🧙 Author: @RantumBits on 2023-05-02
📝 Summary: This SQL model creates a unit test to compare the data in two tables, zeroex_polygon_native_fills and zeroex_polygon_native_fills_sample. It checks if the maker and taker tokens match between both tables. The model then calculates the percentage of rows that do not match between these two tables. This enables data analysts to ensure that their ETL pipeline is working correctly by comparing sample data with actual production data, identifying any discrepancies or errors in their system.

SECTOR: solana_utils

toggle to see all model updates

MODEL: solana_utils_latest_balances.sql

🟠 Modified by:

🔧 PR: #3278, Remove incremental solana_utils_latest_balances.sql
🧙 Author: @andrewhong5297 on 2023-05-04
📝 Summary: This diff of a dbt SQL model shows that the WHERE clause was modified to filter for only the latest balance. The previous version had an additional conditional statement that filtered by day, but this was removed in the updated version.

SECTOR: maker

toggle to see all model updates

MODEL: maker_ethereum_accounting.sql

🟠 Modified by:

🔧 PR: #3276, Updated maker_ethereum.accounting to distinguish a newly recognized type of income
🧙 Author: @web3lyt on 2023-05-04
📝 Summary: The SQL model adds and removes code related to the yield generated by two Jars, RWA009-A and RWA007-A. The changes include renaming a table from hvb_yield_trxns to rwa_yield_trxns, adding '0xef1b095f700be471981aae025f92b03091c3ad47' as an acceptable value for the column named usr, joining with another table called ilk_list_manual_input, changing a descriptor from 'HVB Yield' to 'RWA Yield'.

SECTOR: _sector

toggle to see all model updates

MODEL: nft_ethereum_trades_beta.sql

🟢 Added by:

🔧 PR: #3129, NFT restructuring
🧙 Author: @0xRobin on 2023-05-02
📝 Summary: This dbt SQL model creates a macro called 'enrich_trades' that enables data analysts to enrich trade data from the Ethereum blockchain with additional information such as token metadata, prices, and aggregations. The macro takes in various models as inputs including base models for trades and transactions, token models for NFTs and ERC20 tokens on Ethereum, price models for USD forward fill prices, aggregator markers to identify specific aggregators of interest.

🟠 Modified by:

🔧 PR: #3129, Fix eth erc20 address
🧙 Author: @0xRobin on 2023-05-04
📝 Summary: A Common Table Expression (CTE) named cte_prices_patch was added to the SQL model. It selects data from a table called prices_usd_forward_fill, filters it by blockchain and symbol, and then appends an additional row for Ethereum with its own contract address, blockchain type, decimals value, minute timestamp value of the trade price in USD and symbol. The CTE is used in another CTE called enriched_trades which enriches trades data using various models including this new one. Finally all columns are selected from enriched_trades table

MODEL: archipelago_ethereum_base_trades.sql

🟢 Added by:

🔧 PR: #3129, NFT restructuring
🧙 Author: @0xRobin on 2023-05-02
📝 Summary: This dbt SQL model creates a table that combines trade, token, and fee events from the Archipelago Ethereum data source. It enables data analysts to analyze secondary market trades of NFTs on the platform by providing information such as buyer/seller addresses, contract addresses for NFTs and currencies used in trades, price amounts (including platform and royalty fees), and sub-transaction IDs. The model also filters out specific recipients from royalty payments when calculating fees.

MODEL: foundation_ethereum_base_trades.sql

🟢 Added by:

🔧 PR: #3129, NFT restructuring
🧙 Author: @0xRobin on 2023-05-02
📝 Summary: This SQL model creates a table that combines data from multiple Foundation Ethereum events related to NFT trades. The resulting table includes information such as the trade category, type, price, buyer and seller addresses, platform fees and royalty amounts for each trade. This enables data analysts to analyze NFT trading activity on the Foundation marketplace by various dimensions such as date or project contract address.

MODEL: superrare_ethereum_base_trades.sql

🟢 Added by:

🔧 PR: #3129, NFT restructuring
🧙 Author: @0xRobin on 2023-05-02
📝 Summary: This SQL model creates a table that contains all sales data on the SuperRare platform, including primary and secondary sales. It enables data analysts to analyze trade categories, seller/buyer information, price amounts in raw and decimal formats, fees charged by the platform for each sale type (primary/secondary), royalty fee amount (for secondary sales only), NFT contract address and transaction hash. The model also allows analysts to distinguish between primary and secondary trades based on whether or not Superrare was involved in the initial minting of an NFT.

MODEL: check_nft_migrations_ethereum.sql

🟢 Added by:

🔧 PR: #3129, NFT restructuring
🧙 Author: @0xRobin on 2023-05-02
📝 Summary: This SQL model creates a comparison between the migrated and reference data of NFT trades on Ethereum blockchain. It enables data analysts to check if there are any discrepancies in the total amount, platform fee amount, and royalty fee amount between these two datasets. The results are filtered to show only those records where there is a discrepancy greater than 0.05%.

MODEL: check_nft_rowcount_foundation.sql

🟢 Added by:

🔧 PR: #3129, NFT restructuring
🧙 Author: @0xRobin on 2023-05-02
📝 Summary: This SQL model creates a unit test that compares the count of records in foundation_ethereum_base_trades table with the sum of counts from four other tables (market_evt_ReserveAuctionFinalized, market_evt_BuyPriceAccepted, market_evt_OfferAccepted, and market_evt_PrivateSaleFinalized). If there is any difference between these two counts, it returns those records. This enables data analysts to ensure that all relevant events are being captured accurately in the database.

MODEL: check_nft_rowcount_superrare.sql

🟢 Added by:

🔧 PR: #3129, NFT restructuring
🧙 Author: @0xRobin on 2023-05-02
📝 Summary: This SQL model creates a unit test that compares the count of records in a base table with the sum of counts from multiple source tables. It enables data analysts to verify if all expected events are being captured and processed correctly by comparing the total number of events across different sources against those stored in the base table. If there is any difference between these two counts, it indicates an issue that needs to be investigated further.

SECTOR: nexusmutual

toggle to see all model updates

MODEL: nexusmutual_ethereum_capital_pool_eth_daily_transaction_summary.sql

🟢 Added by:

🔧 PR: #3259, Capitial pool eth transactions
🧙 Author: @guyhow on 2023-05-04
📝 Summary: This dbt SQL model creates a table that summarizes Ethereum transactions involving specific addresses. It enables data analysts to track the daily amount of Ether (ETH) flowing into and out of these addresses, excluding certain internal transfers. The model uses conditional statements to filter relevant transactions based on their success status, block time, sender and receiver addresses. The resulting table includes columns for date (day), ETH ingress (incoming flow) and ETH egress (outgoing flow).

SECTOR: aave

toggle to see all model updates

MODEL: aave_flashloans.sql

🟢 Added by:

🔧 PR: #3232, Aave Flashloans
🧙 Author: @hildobby on 2023-05-04
📝 Summary: This SQL model creates a unified table by merging data from six different Aave flashloan models across various blockchains. This enables data analysts to easily query and analyze Aave flashloan activity on multiple chains in one place, including information such as the amount borrowed, fees paid, recipient addresses and more. The model also includes an optional filter for incremental updates within the past week.

MODEL: aave_arbitrum_flashloans.sql

🟢 Added by:

🔧 PR: #3232, Aave Flashloans
🧙 Author: @hildobby on 2023-05-04
📝 Summary: This SQL model creates a unified table by merging data from the specified Aave V3 Arbitrum Flashloans models. It enables data analysts to easily query and analyze flashloan transactions on the Aave protocol across different projects, versions, currencies, and recipients. The model also includes fields for block time, number, transaction hash and fee information. If used incrementally it will only include data from the past week based on block time.

MODEL: aave_v3_arbitrum_flashloans.sql

🟢 Added by:

🔧 PR: #3232, Aave Flashloans
🧙 Author: @hildobby on 2023-05-04
📝 Summary: This SQL model creates a view of flash loans on the Aave v3 protocol in Arbitrum. It includes data such as block time, amount borrowed, fee paid, currency symbol and recipient address. The model also calculates the USD value of each loan using price data from an external source. This enables analysts to track flash loan activity on Aave v3 in Arbitrum and analyze trends over time.

MODEL: aave_avalanche_c_flashloans.sql

🟢 Added by:

🔧 PR: #3232, Aave Flashloans
🧙 Author: @hildobby on 2023-05-04
📝 Summary: This SQL model creates a union of two Aave flash loan models, allowing data analysts to query and analyze data related to flash loans on the Avalanche network. The model includes information such as blockchain, project, version, block time and number, amount borrowed in various currencies including USD equivalents and fees paid. This enables analysts to better understand trends in flash loans on the Avalanche network over time.

MODEL: aave_v2_avalanche_c_flashloans.sql

🟢 Added by:

🔧 PR: #3232, Aave Flashloans
🧙 Author: @hildobby on 2023-05-04
📝 Summary: This SQL model creates a view that enables data analysts to query flash loan transactions on the Avalanche C chain for Aave v2. The view includes information such as block time, block number, loan amount and fee in both raw and converted (USD) values, transaction hash, currency contract address and symbol, recipient of the loaned funds and contract address. It also joins with a price feed table to provide USD conversion rates at the time of each transaction.

MODEL: aave_v3_avalanche_c_flashloans.sql

🟢 Added by:

🔧 PR: #3232, Aave Flashloans
🧙 Author: @hildobby on 2023-05-04
📝 Summary: This SQL model creates a view that enables data analysts to query flash loan transactions on the Avalanche C chain for Aave v3. The view includes information such as block time, block number, loan amount and fee in both raw and converted (USD) values, transaction hash and index, currency contract address and symbol with decimals. Additionally it provides recipient's address along with the contract address of the executed function. This model also joins token prices from an external source to calculate USD value of loans at specific times.

MODEL: aave_ethereum_flashloans.sql

🟢 Added by:

🔧 PR: #3232, Aave Flashloans
🧙 Author: @hildobby on 2023-05-04
📝 Summary: This SQL model creates a unified view of Aave flashloan data across multiple versions of the Ethereum blockchain. It enables data analysts to easily query and analyze flashloan transactions, including details such as block time, amount borrowed, transaction hash, fee paid and recipient address. The model also includes filtering options for incremental updates within the past week.

MODEL: aave_v1_ethereum_flashloans.sql

🟢 Added by:

🔧 PR: #3232, Aave Flashloans
🧙 Author: @hildobby on 2023-05-04
📝 Summary: This SQL model creates a view of flash loans from Aave on the Ethereum blockchain. It includes information such as block time, amount borrowed and fees paid in both raw and USD values, transaction hash, currency symbol and decimals used for the loan, recipient address of the loaned funds. The model also joins with a price feed to calculate amounts in USD. This enables data analysts to easily query flash loan data from Aave on Ethereum for analysis or reporting purposes.

MODEL: aave_v2_ethereum_flashloans.sql

🟢 Added by:

🔧 PR: #3232, Aave Flashloans
🧙 Author: @hildobby on 2023-05-04
📝 Summary: This SQL model creates a view of flash loans from Aave v2 on Ethereum, including the amount borrowed, fee paid, currency symbol and decimals used. It also calculates the USD value of each loan using current market prices. This enables data analysts to easily query and analyze flash loan activity on Aave v2 in order to gain insights into borrowing trends and usage patterns.

MODEL: aave_v3_ethereum_flashloans.sql

🟢 Added by:

🔧 PR: #3232, Aave Flashloans
🧙 Author: @hildobby on 2023-05-04
📝 Summary: This dbt SQL model creates a table that summarizes flash loans data from Aave v3 on Ethereum. It includes information such as the block time and number, loan amount in both raw and converted values, transaction hash, fee, currency contract details (symbol and decimals), recipient address, contract address of the flash loan initiator. The model also joins with a price source to calculate USD value of each loan at the given minute. This enables data analysts to easily analyze Aave v3 flash loans on Ethereum blockchain in terms of volume and value over time.

MODEL: aave_fantom_flashloans.sql

🟢 Added by:

🔧 PR: #3232, Aave Flashloans
🧙 Author: @hildobby on 2023-05-04
📝 Summary: This SQL model creates a view that combines data from the Aave v3 Fantom flashloans table(s) and enables analysts to query blockchain, project, version, block time and number, amount (in cryptocurrency), amount in USD equivalent at the time of transaction, transaction hash ID's , event index numbers for each transaction fee amounts paid by users or recipients of funds. It also includes currency contract addresses and symbols as well as recipient addresses. The view can be queried incrementally based on a specified date range.

MODEL: aave_v3_fantom_flashloans.sql

🟢 Added by:

🔧 PR: #3232, Aave Flashloans
🧙 Author: @hildobby on 2023-05-04
📝 Summary: This SQL model creates a view of flash loans on the Aave v3 protocol in Fantom blockchain. It enables data analysts to query and analyze flash loan transactions, including block time, amount borrowed and fee paid in both raw and USD values, transaction hash, currency contract address and symbol used for borrowing. The model also joins with an external source to provide real-time price information for each currency used in the transactions.

MODEL: aave_optimism_flashloans.sql

🟢 Added by:

🔧 PR: #3232, Aave Flashloans
🧙 Author: @hildobby on 2023-05-04
📝 Summary: This SQL model creates a view that combines data from the Aave v3 Optimism Flashloans table(s) and enables analysts to query blockchain, project, version, block time and number, amount (in cryptocurrency), amount in USD equivalent at the time of transaction, transaction hash ID (tx_hash), event index number (evt_index), fee paid for the flashloan in cryptocurrency or USD equivalent at the time of transaction. It also includes information on currency contract address and symbol as well as recipient's wallet address. The view can be queried incrementally based on a specified date range.

MODEL: aave_optimism_schema.yml

🟠 Modified by:

🔧 PR: #3232, Aave Flashloans
🧙 Author: @hildobby on 2023-05-04
📝 Summary: [changes too large] The model aave_optimism_schema.yml was modified.

MODEL: aave_v3_optimism_flashloans.sql

🟢 Added by:

🔧 PR: #3232, Aave Flashloans
🧙 Author: @hildobby on 2023-05-04
📝 Summary: This SQL model creates a view of flash loans on the Aave v3 protocol on the Optimism blockchain. It includes information such as block time, amount borrowed, fee paid, currency symbol and contract address. The model also calculates the USD value of each loan using current market prices from an external source. This enables data analysts to analyze flash loan activity on Aave v3 and gain insights into borrowing trends and usage patterns over time.

MODEL: aave_polygon_flashloans.sql

🟢 Added by:

🔧 PR: #3232, Aave Flashloans
🧙 Author: @hildobby on 2023-05-04
📝 Summary: This SQL model creates a union of two Aave flash loan models, aave_v2_polygon_flashloans and aave_v3_polygon_flashloans, allowing data analysts to query both models at once. The resulting table includes information on blockchain, project, version, block time and number, amount borrowed in various currencies (with USD conversion), transaction hash and index, fee paid for the loan in ETH or other currency if applicable. This enables analysts to perform more comprehensive analysis on Aave flash loans across different versions of the protocol.

MODEL: aave_v2_polygon_flashloans.sql

🟢 Added by:

🔧 PR: #3232, Aave Flashloans
🧙 Author: @hildobby on 2023-05-04
📝 Summary: This SQL model creates a view of flash loans on the Polygon network's Aave v2 project. It includes data such as block time, amount borrowed and fees paid in both raw and converted USD values, transaction hash, currency symbol and contract address. The model enables data analysts to easily query this information for analysis or reporting purposes.

MODEL: aave_v3_polygon_flashloans.sql

🟢 Added by:

🔧 PR: #3232, Aave Flashloans
🧙 Author: @hildobby on 2023-05-04
📝 Summary: This SQL model creates a view of flash loans on the Polygon network for Aave v3. It includes information such as block time, amount borrowed and fees paid in both raw and converted USD values, transaction hash, currency contract address and symbol, recipient address and contract address. The model enables data analysts to easily query this information for analysis or reporting purposes.

SECTOR: models

toggle to see all model updates

MODEL: enrich_trades.sql

🟢 Added by:

🔧 PR: #3129, NFT restructuring
🧙 Author: @0xRobin on 2023-05-02
📝 Summary: This SQL model creates a macro that enriches NFT trades data by adding transaction, NFT token, and ERC20 token information. It also handles USD columns and adds aggregator columns while calculating platform and royalty rates. The macro applies to base models of different blockchain projects' versions using UNION ALL statements. Finally, the enriched data is selected from the joined tables containing all necessary information for analysis purposes such as buyer/seller addresses, trade category/type (buy/sell/swap), price in USD with decimals handled correctly among others.

SECTOR: foundation

toggle to see all model updates

MODEL: foundation_ethereum_events.sql

🟠 Modified by:

🔧 PR: #3129, NFT restructuring
🧙 Author: @0xRobin on 2023-05-02
📝 Summary: The added code creates a Common Table Expression (CTE) named 'all_foundation_trades' that selects the values 'ethereum' and 'foundation' for columns named 'blockchain' and 'project', respectively. This CTE is used later in the SQL model to join with other tables.

SECTOR: superrare

toggle to see all model updates

MODEL: superrare_ethereum_events.sql

🟠 Modified by:

🔧 PR: #3129, NFT restructuring
🧙 Author: @0xRobin on 2023-05-02
📝 Summary: The removed code was a filter that checked if the amount of tokens transferred in an Ethereum transaction was greater than zero. The added code included two left outer joins to tables named erc20 and transfers_for_tokens_sold_from_auction. These joins were made on specific columns from the main table, and they allowed for additional data to be pulled into the query. No further information is provided about what this data represents or how it will be used.

SECTOR: lido

toggle to see all model updates

MODEL: lido_ethereum_accounting.sql

🟠 Modified by:

🔧 PR: #3264, Tokens decimals source fix
🧙 Author: @ppclunghe on 2023-05-01
📝 Summary: The diff of the SQL model shows changes made to a Lido Ethereum accounting revenue table. The changes include renaming 'slashing insurance' to 'slashing provision', and updating associated account names, labels, and categories. Additionally, there are updates made to the calculation of value_base_token and value_usd columns in the SELECT statement. Finally, some minor modifications were done on grouping by certain fields in order for them to be compatible with other tables used in this model.

MODEL: lido_ethereum_accounting_fundraising.sql

🟠 Modified by:

🔧 PR: #3264, Tokens decimals source fix
🧙 Author: @ppclunghe on 2023-05-01
📝 Summary: The diff of the dbt SQL model shows that a filter condition was removed from the fundraising_txs table where value != 0. This means that all rows will be included in the output, regardless of their value. The rest of the code remains unchanged and includes a union with another table to capture ETH inflow.

SECTOR: staking

toggle to see all model updates

MODEL: staking_ethereum_entities.sql

🟠 Modified by:

🔧 PR: #3263, Add Swell ETH staking
🧙 Author: @hildobby on 2023-05-01
📝 Summary: A new staking pool contract address was added for Swell in the Liquid Staking category. No other changes were made to the SQL model.

SECTOR: contracts

toggle to see all model updates

MODEL: contracts_optimism_contract_creator_address_list.sql

🟠 Modified by:

🔧 PR: #3257, OP Static Updates & Add RPGF Distributions to Token Tracking
🧙 Author: @MSilb7 on 2023-05-01
📝 Summary: The diff shows additions to a SQL model that creates a temporary table called 'curated_list'. The new rows added to the table include contract addresses and their corresponding project names.

SECTOR: op

toggle to see all model updates

MODEL: op_token_distributions_optimism_project_wallets.sql

🟠 Modified by:

🔧 PR: #3257, OP Static Updates & Add RPGF Distributions to Token Tracking
🧙 Author: @MSilb7 on 2023-05-01
📝 Summary: The diff of the SQL model shows additions and removals of various pools, bridges, distributors and partner funds. The added lines include new pools for Lido, Angle Finance and Synthetix on Velodrome as well as new bridges for sUSD on Hop Protocol. Additionally, there are retroPGF payouts included in the distributor wallets section.

MODEL: op_token_distributions_optimism_transfer_mapping.sql

🟠 Modified by:

🔧 PR: #3257, OP Static Updates & Add RPGF Distributions to Token Tracking
🧙 Author: @MSilb7 on 2023-05-01
📝 Summary: The SQL model has been updated to include a new column called 'op_for_retropgf' which calculates the amount of tokens distributed from the foundation's grants descriptor to RetroPGF. The existing columns have also been cast as double.