What is the problem?

There are 2 major concerns right now.

  1. Some queries that are run by the intelligence team take 15mins-1hr to run which is affecting developer productivity.

  2. The cost of running these queries is burning a hole in the company’s pocket. You can take a look at them here.

Laundry List Dump

Data Engineering

1. Checkout matic_flat
2. TG relies on BQ for address links and details
3. Slow because scanning the whole tld raw tables is more than 700+GB
4. Saved ~$2000 with the way new chain is done in BQ
5. Resources exceeded issues while running DAGs
6. Partitioned table-yes and partition filter should be required
7. Look at policy tags for IAM


Daily DAG for new chains
https://c9da2c9725276ac0dp-tp.appspot.com/admin/airflow/graph?dag_id=cardano_bt_ch_bq_daily&execution_date=
TG Daily Dag
New Chain : https://c9da2c9725276ac0dp-tp.appspot.com/admin/airflow/tree?dag_id=tg_v21_matic_export_m18_v6
Old Chains
https://c9da2c9725276ac0dp-tp.appspot.com/admin/airflow/tree?dag_id=tg_bitcoin_export_m15_v6
New Chains : BSC,ADA,MATIC,STELLAR,TRON,Hedera
Aggregated Table Examples
Address Details: intelligence-team.matic_flat.address_details_daily
Address Links : intelligence-team.matic_flat.address_links_daily
CODE

Intelligence Team

1. TLD view is slow
2. Fee in the tld table (good to have)
3. unique number of address inputs and outputs in the txns
4. count of unique values both on the input side as well as on the output side
5. Given the address what is the incoming and outgoing exposure (direct and indirect)
6. Ethereum and bitcoin is the most problematic one 
7. Missing data
8. https://merkle-science.atlassian.net/wiki/spaces/DS/pages/25133072/Clustering+Heuristics+-+Bitcoin+Family
9. https://www.notion.so/merklescience/Sample-Queries-e18fd4060457402a99e0746882db46b2

1. TLD table (Eth, BTC and USDT) - Frequently used and time consuming
2. Address tags and cluster ids - frequently used
3. Meta data in TLD view such as total input addresses, output addresses per transaction hash, fees etc.
4. Unflattened tables - Unflattened version of tld tables
5. KYBB and EDD tables  - table view for entity (KYBB) and address (EDD) - direct and indirect exposure
6. Modifying existing heuristics based on new tables
CODE