📰 2023-05-12: Weekly Prophet! #3334
andrewhong5297
announced in
Prophet (Weekly Updates)
Replies: 2 comments
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
-
This is your weekly summary of 14 PRs merged from 8 wizards. Great job everyone! 🎉
We had 12 added models 🟢 and 26 modified models 🟠 for 18 Sectors.
SECTOR: dex
toggle to see all model updates
MODEL: dex_trades.sql
🟠 Modified by:
🔧 PR: #3315, Added new camelot_arbitrum_trades spell to dex_trades
🧙 Author: @ytoast on 2023-05-10
📝 Summary: Based on the given diff, it appears that the reference model 'camelot_trades' was added to this SQL model. No reference models were removed.
SECTOR: tokens
toggle to see all model updates
MODEL: tokens_ethereum_erc20.sql
🟠 Modified by:
🔧 PR: #3282, [Erc20] Add 173 missing tokens
🧙 Author: @bh2smith on 2023-05-09
📝 Summary: [changes too large] The model tokens_ethereum_erc20.sql was modified.
🔧 PR: #3282, [Erc20] add about 2900 mainnet tokens
🧙 Author: @bh2smith on 2023-05-08
📝 Summary: [changes too large] The model tokens_ethereum_erc20.sql was modified.
🔧 PR: #3282, [Easy] [ERC20] Add 117 missing tokens
🧙 Author: @gentrexha on 2023-05-05
📝 Summary: [changes too large] The model tokens_ethereum_erc20.sql was modified.
MODEL: tokens_optimism_erc20_curated.sql
🟢 Added by:
🔧 PR: #3159, OP - Add all ERC20 tokens to erc20.tokens
🧙 Author: @MSilb7 on 2023-05-05
📝 Summary: [changes too large] The model tokens_optimism_erc20_curated.sql was added.
🟠 Modified by:
🔧 PR: #3159, Update ATST Decoding + Bad Curve Decoding + Static Updates
🧙 Author: @MSilb7 on 2023-05-08
📝 Summary: A new token called 'OPepe' with a contract address of '0x384fabB05F28389Afc16fC8bcB08B55eD97Fa597', 18 decimals and an underlying type has been added to the SQL model.
MODEL: tokens_optimism_nft_curated.sql
🟠 Modified by:
🔧 PR: #3295, Update ATST Decoding + Bad Curve Decoding + Static Updates
🧙 Author: @MSilb7 on 2023-05-08
📝 Summary: The diff of the SQL model shows that three new contract addresses and their corresponding names were added to an existing table. No columns or lines were removed from the original model.
MODEL: tokens_optimism_erc20.sql
🟠 Modified by:
🔧 PR: #3159, OP - Add all ERC20 tokens to erc20.tokens
🧙 Author: @MSilb7 on 2023-05-05
📝 Summary: [changes too large] The model tokens_optimism_erc20.sql was modified.
MODEL: tokens_optimism_erc20_generated.sql
🟢 Added by:
🔧 PR: #3159, OP - Add all ERC20 tokens to erc20.tokens
🧙 Author: @MSilb7 on 2023-05-05
📝 Summary: This SQL model creates a deterministic list of tokens pulled from various sources, including Aave and Yearn vault factories, for the Optimism blockchain. The resulting table includes columns for contract address, symbol, decimals (with handling if L2 token factory gets decimals wrong), token type (underlying or receipt), and the source of the mapping data. This enables data analysts to easily access information on these tokens in order to perform analysis on their usage within various protocols or across different blockchains.
MODEL: tokens_optimism_erc20_stablecoins.sql
🟠 Modified by:
🔧 PR: #3159, OP - Add all ERC20 tokens to erc20.tokens
🧙 Author: @MSilb7 on 2023-05-05
📝 Summary: A new row was added to the SQL model with a contract address, token symbol, base currency and description. The row represents a crypto-backed asset.
MODEL: tokens_optimism_erc20_transfer_source.sql
🟢 Added by:
🔧 PR: #3159, OP - Add all ERC20 tokens to erc20.tokens
🧙 Author: @MSilb7 on 2023-05-05
📝 Summary: This dbt SQL model selects the latest transfer event for each contract address from an ERC20 token on the Optimism network. It groups the results by contract address and, if run incrementally, only includes transfers that occurred within the past week. The model enables data analysts to easily query and analyze recent transfer activity for specific ERC20 tokens on Optimism.
MODEL: tokens_erc20.sql
🟠 Modified by:
🔧 PR: #3159, OP - Add all ERC20 tokens to erc20.tokens
🧙 Author: @MSilb7 on 2023-05-05
📝 Summary: Based on the given SQL model, it appears that a reference model named 'tokens_optimism_erc20' was added to the aggregation. No reference models were removed from this SQL model.
SECTOR: prices
toggle to see all model updates
MODEL: prices_bnb_tokens.sql
🟠 Modified by:
🔧 PR: #3306, Update aave token
🧙 Author: @aalan3 on 2023-05-08
📝 Summary: The token symbols that were added or removed are: Added:
MODEL: prices_ethereum_tokens.sql
🟠 Modified by:
🔧 PR: #3296, [Prices] Remove StakeDAO from prices.usd
🧙 Author: @bh2smith on 2023-05-08
📝 Summary: The token symbol that was removed is SDT. The token symbols that were added are not explicitly mentioned in the model, but we can infer them from the list of tokens provided. They are: SAN, SAND, SDAO, SENT, SHIB and SKL.
SECTOR: camelot
toggle to see all model updates
MODEL: camelot_arbitrum_trades.sql
🟢 Added by:
🔧 PR: #3315, Added new camelot_arbitrum_trades spell to dex_trades
🧙 Author: @ytoast on 2023-05-10
📝 Summary: This SQL model creates a unified view of trade data from multiple decentralized exchanges (DEXs) on the Arbitrum network. It enables data analysts to query and analyze trade information such as token symbols, amounts, prices in USD, addresses involved in trades, and transaction details across different DEXs using a single query.
MODEL: camelot_v1_arbitrum_trades.sql
🟢 Added by:
🔧 PR: #3315, Added new camelot_arbitrum_trades spell to dex_trades
🧙 Author: @ytoast on 2023-05-10
📝 Summary: This SQL model creates a view that enables data analysts to query and analyze decentralized exchange (DEX) trading activity on the Arbitrum blockchain. The view includes information such as token pairs, amounts traded, prices in USD, transaction details and more. It also joins with other tables to provide additional context such as token symbols and contract addresses. This model is designed for use in dbt projects using Camelot schema conventions on the Arbitrum blockchain.
MODEL: camelot_trades.sql
🟢 Added by:
🔧 PR: #3315, Added new camelot_arbitrum_trades spell to dex_trades
🧙 Author: @ytoast on 2023-05-10
📝 Summary: This SQL model creates a unified table of trade data from the Camelot Arbitrum DEX. It combines multiple tables into one, allowing analysts to easily query and analyze all trade data in one place. The resulting table includes information such as token symbols, amounts, addresses, and transaction details for each trade on the platform.
SECTOR: _sector
toggle to see all model updates
MODEL: x2y2_ethereum_base_trades.sql
🟢 Added by:
🔧 PR: #3314, Restructure x2y2 to new NFT trades setup
🧙 Author: @0xRobin on 2023-05-10
📝 Summary: This SQL model creates a view that extracts data from the X2Y2_r1_evt_EvInventory table in the x2y2_ethereum database. It selects various fields related to NFT trades, including buyer and seller addresses, trade type and category, price information, fees paid to platform and royalty holders as well as other relevant details. The resulting view enables data analysts to perform analysis on NFT trading activity such as calculating revenue generated by platform fees or royalties paid out over time.
MODEL: nft_ethereum_trades_beta.sql
🟠 Modified by:
🔧 PR: #3304, Restructure x2y2 to new NFT trades setup
🧙 Author: @0xRobin on 2023-05-10
📝 Summary: A new reference to a table called 'x2y2_ethereum_base_trades' was added in the SQL model. The comment suggests that a CTE should be removed and ETH should be included in the general prices table once everything is migrated.
🔧 PR: #3304, Restructure Element to new NFT trades setup
🧙 Author: @0xRobin on 2023-05-08
📝 Summary: A new reference to a table called 'element_ethereum_base_trades' was added. The comment suggests that a CTE should be removed and ETH should be included in the general prices table once everything is migrated.
MODEL: element_ethereum_base_trades.sql
🟢 Added by:
🔧 PR: #3304, Restructure Element to new NFT trades setup
🧙 Author: @0xRobin on 2023-05-08
📝 Summary: This SQL model creates a base table of trades by merging data from different sources related to ERC721 and ERC1155 orders. It enables data analysts to easily access and analyze trade information in a single table. The SELECT statement at the end retrieves all columns from the base_trades table for further analysis.
SECTOR: cow_protocol
toggle to see all model updates
MODEL: cow_protocol_ethereum_eth_flow_orders.sql
🟠 Modified by:
🔧 PR: #3321, [CoW] ETH Flow - add column environment
🧙 Author: @bh2smith on 2023-05-10
📝 Summary: The SQL model adds a column called 'environment' to the eth_flow_orders table, which is derived from the contract_address field in the event table. If contract_address equals '0x40a50cf069e992aa4536211b23f286ef88752187', then environment is set to 'prod'. If it equals '0xd02de8da0b71e1b59489794f423fabba2adc4d93', then environment is set to 'barn'. The validity column has been modified so that its value comes from unpacking data instead of being hardcoded as infinite.
SECTOR: uniswap
toggle to see all model updates
MODEL: uniswap_v3_ethereum_trades.sql
🟠 Modified by:
🔧 PR: #3299, Add fees to uniswap v3 trades
🧙 Author: @ytoast on 2023-05-09
📝 Summary: This SQL model adds a new field called 'fee' to an existing query that retrieves data from Ethereum transactions. The COALESCE function is used to avoid redundant joins with the transactions table. Two LEFT JOINs are performed on the tokens_erc20 table, and one LEFT JOIN is performed on the prices_usd table. No significant changes were made except for adding a new field and some minor formatting adjustments at the end of the file.
SECTOR: oneinch
toggle to see all model updates
MODEL: oneinch_unoswap_v5_ethereum_trades.sql
🟠 Modified by:
🔧 PR: #3316, exclude another tx in existing bug for oneinch trades
🧙 Author: @jeff-dude on 2023-05-09
📝 Summary: The diff shows changes made to a SQL model. A few transactions did not fit into the join on line 122 and were corrected by changing it to line 123. For these transactions, '+2' was changed to '+3'. On v1 engine, the transaction amounts were forced to be zero via an update statement as full refresh is less common there. Additionally, a new transaction hash filter '0xad7d5814544440bdcb22760f8f2f0594958e9e6417249d96d92bf78cd05a80f5' was added while removing another one if the join is fixed.
SECTOR: clipper
toggle to see all model updates
MODEL: clipper_trades.sql
🟠 Modified by:
🔧 PR: #3291, Add clipper.exchange on Polygon
🧙 Author: @amalashkevich on 2023-05-08
📝 Summary: In this SQL model, a list of clipper models is defined. The
clipper_ethereum_tradesmodel is included in the list, while theclipper_arbitrum_tradesmodel is removed and then added back to the list along with a new addition ofclipper_polygon_trades.MODEL: clipper_polygon_trades.sql
🟢 Added by:
🔧 PR: #3291, Add clipper.exchange on Polygon
🧙 Author: @amalashkevich on 2023-05-08
📝 Summary: This SQL model creates a union of two existing tables,
clipper_v1_polygon_tradesandclipper_v2_polygon_trades, to provide a comprehensive view of trades made on the Clipper platform. The resulting table includes information such as blockchain, project, token symbols and amounts bought/sold, USD value of the trade, addresses involved in the transaction and more. This enables data analysts to perform analysis on all trades made across both versions of Clipper using one consolidated dataset.MODEL: clipper_v1_polygon_trades.sql
🟢 Added by:
🔧 PR: #3291, Add clipper.exchange on Polygon
🧙 Author: @amalashkevich on 2023-05-08
📝 Summary: This SQL model creates a view that enables data analysts to query direct token swaps on the Polygon network using Clipper smart contracts. The view includes information such as the blockchain, project, version, block date and time, token symbols and addresses for both sold and bought tokens. It also provides details about the taker/maker of each transaction along with contract address and transaction hash. Additionally it calculates USD value of each swap based on current prices from external sources like Uniswap or Sushiswap etc., which can be used for further analysis by data analysts.
MODEL: clipper_v2_polygon_trades.sql
🟢 Added by:
🔧 PR: #3291, Add clipper.exchange on Polygon
🧙 Author: @amalashkevich on 2023-05-08
📝 Summary: This SQL model creates a view that enables data analysts to query and analyze transaction data from the Polygon blockchain's Clipper project. The view includes information on token pairs, amounts bought and sold, USD value of transactions, contract addresses involved in the transactions, and more. It also joins with other tables to provide additional context such as token symbols and prices at the time of transaction. This model supports incremental updates based on block time or last week's date for efficient querying over large datasets.
SECTOR: coinbase_wallet_quests
toggle to see all model updates
MODEL: coinbase_wallet_quests_optimism_distributor_addresses.sql
🟠 Modified by:
🔧 PR: #3295, Update ATST Decoding + Bad Curve Decoding + Static Updates
🧙 Author: @MSilb7 on 2023-05-08
📝 Summary: In this SQL model, two rows were added to the existing values. The new rows contain distributor addresses and quest names for Quest 3 - Attestation and Quest 4 - Deposit. The rewards token used in all quests is a variable called
op_token.SECTOR: contracts
toggle to see all model updates
MODEL: contracts_optimism_contract_creator_address_list.sql
🟠 Modified by:
🔧 PR: #3295, Update ATST Decoding + Bad Curve Decoding + Static Updates
🧙 Author: @MSilb7 on 2023-05-08
📝 Summary: This SQL model adds several rows to a temporary table called 'curated_list'. Each row contains an Ethereum address and the name of a project associated with that address. The added rows include projects such as Union Protocol, Socket, Metamask, Kwenta, Mirror and nftperp.
SECTOR: curvefi
toggle to see all model updates
MODEL: curvefi_optimism_trades.sql
🟠 Modified by:
🔧 PR: #3295, Update ATST Decoding + Bad Curve Decoding + Static Updates
🧙 Author: @MSilb7 on 2023-05-08
📝 Summary: The added logic includes a UNION ALL statement that selects data from the 'stableswap' table. The SELECT statement retrieves various columns and applies some conditions to filter out certain rows. A subquery is used to check if there are any matching records in another table, and if not, the row is included in the result set. There are also additional conditions applied based on whether or not it's an incremental update. Finally, there's an INNER JOIN with another table called 'curvefi_optimism_pools'.
SECTOR: gamma
toggle to see all model updates
MODEL: gamma_optimism_uniswap_pools.sql
🟠 Modified by:
🔧 PR: #3295, Update ATST Decoding + Bad Curve Decoding + Static Updates
🧙 Author: @MSilb7 on 2023-05-08
📝 Summary: A new contract address was added to the SQL model for wsteth.cbeth.01.s. The SELECT statement includes lp_name and a LOWER function applied to addr, as well as three tuples with hardcoded values for different pairs of tokens and their respective contract addresses.
SECTOR: op
toggle to see all model updates
MODEL: op_retropgf_optimism_round2_recipients.sql
🟠 Modified by:
🔧 PR: #3295, Update ATST Decoding + Bad Curve Decoding + Static Updates
🧙 Author: @MSilb7 on 2023-05-08
📝 Summary: In this SQL model, changes were made to the formatting of recipient_name and recipient_category columns. The trim function was added to remove any leading or trailing white spaces from these columns. Additionally, a regular expression was used to extract only numerical values from the award_amount column and cast it as a double data type. Finally, an op_token value was assigned to each row in the award_token column.
MODEL: op_token_distributions_optimism_project_wallets.sql
🟠 Modified by:
🔧 PR: #3295, Update ATST Decoding + Bad Curve Decoding + Static Updates
🧙 Author: @MSilb7 on 2023-05-08
📝 Summary: The diff of the SQL model shows additions and removals of addresses and their corresponding labels. Multiple parties used a bribe, which was removed. The label for one address was changed from 'USDC/agEUR: Velodrome' to 'USDC/agEUR: Uniswap'. Several new addresses were added with the same label as an existing address.
SECTOR: optimism_attestationstation
toggle to see all model updates
MODEL: optimism_attestationstation_optimism_events.sql
🟠 Modified by:
🔧 PR: #3295, Update ATST Decoding + Bad Curve Decoding + Static Updates
🧙 Author: @MSilb7 on 2023-05-08
📝 Summary: The diff of the SQL model shows changes made to the key and val columns. The REGEXP_REPLACE function was added to replace invisible characters in both columns. Additionally, a CASE statement was added to check if the value is empty or not before casting it as varchar(100). If it's not empty, then unhex(substring(val, 3)) is cast as varchar(100), otherwise bytea2numeric_v3(substring(val, 3)) is cast as varchar(100). Finally, split() function was used on val column after applying these changes.
SECTOR: aave
toggle to see all model updates
MODEL: aave_v3_tokens.sql
🟠 Modified by:
🔧 PR: #3159, OP - Add all ERC20 tokens to erc20.tokens
🧙 Author: @MSilb7 on 2023-05-05
📝 Summary: The removed lines were joining the model with another table to get decimals and symbol for underlying tokens. The added lines removed this join, which means that the output of the model will no longer include columns for underlying decimals and symbols.
SECTOR: the_granary
toggle to see all model updates
MODEL: the_granary_optimism_tokens.sql
🟠 Modified by:
🔧 PR: #3159, OP - Add all ERC20 tokens to erc20.tokens
🧙 Author: @MSilb7 on 2023-05-05
📝 Summary: The SQL model selects distinct values of blockchain, arate_type, atoken_symbol and atoken_name from a subquery. The subquery filters data based on the evt_block_time column being greater than or equal to one week ago. Two columns (underlying_decimals and underlying_symbol) were removed from the select statement. A left join with another table named tokens_erc20 was also removed from the query.
SECTOR: yearn
toggle to see all model updates
MODEL: yearn_optimism_vaults.sql
🟢 Added by:
🔧 PR: #3159, OP - Add all ERC20 tokens to erc20.tokens
🧙 Author: @MSilb7 on 2023-05-05
📝 Summary: This dbt SQL model creates a table that extracts data from the 'ReleaseRegistry_call_newVault' source in the 'yearn_optimism' database. It filters for successful calls and selects specific columns such as block time, block number, underlying token, vault symbol and name. This enables data analysts to analyze new vault creations on the Yearn platform with a focus on optimism blockchain technology. The model also includes an optional incremental filter for recent data analysis within one week of current date/time.
Beta Was this translation helpful? Give feedback.
All reactions