This week, I built 2 things:
1. A full DeFi off-ramp Project analytics dashboard
2. An EVM โSmart Moneyโ overlap tracker
One of them exposed a serious flaw in my own analysis, while the other improved my research and front-run trading game.
Hereโs what happened ๐งต
I decided to take my skills up a notch by advancing into Data Engineering, and what better academy than @AnalyticSages could bring this need of mine to reality? So I signed up.
It's just week one, and I built a pipeline that pulls the top 100 coins from the CoinGecko API, cleans them with Pandas, and loads them into Postgres, all running in Docker with one command.
This is a basic ETL project, and it's something that I have always wanted to build.
Here were some of the key lessons while doing this first task:
1. Inside a container, "localhost" is not your laptop. It's the container itself. Containers find each other by service name, so my script reads the database host from an environment variable (DB_HOST) instead of hardcoding it.
The same code works on my laptop and inside Docker.
2. Library updates can break working code. A newer SQLAlchemy version changed its default Postgres driver, and my script crashed looking for a module I never installed. Naming the driver explicitly fixed it. I think this is the part I look forward to solidifying my knowledge on as we progress.
3. Two programs can't share one port. DBeaver kept rejecting my password because another Postgres on my machine was already sitting on port 5432. Moving Docker to 5433 solved it.
Week one down and I look forward to what's next.
Data Tribe: @AnalyticSages
Data Extraction source: @coingecko API
Here's a link to the project: https://t.co/vUEhlFJbMK
Week four is done, and this one was all about data modelling with dbt.
Instead of pulling new data, I went back to my own Week 2 Uniswap swap extraction and built a dbt project on top of it in three layers:
Staging cleans the raw data, intermediate holds all the logic, and the final table gives one clean row per swap that an analyst can just query.
The result still remains same: 205 swaps across ETH/USDC, USDC/USDT and WBTC/USDC, over $3M in volume, and 10 whale trades of over $100K each.
Here were some of the key lessons from this week:
1. Raw on-chain data isn't analyst-ready. The amounts have no decimals applied, and the price is hidden inside a number called sqrtPriceX96. The intermediate layer is where I decoded all of that into actual prices, trade direction and dollar volume.
2. Tests passing doesn't mean your numbers are right. All 82 of my dbt tests were green, but I still fed in trades with prices I already knew and checked the output matched exactly. I think this is the habit my onchain forensic work has given me.
3. The provided config was broken. dbt kept rejecting the database port because it was being passed as text. One small fix (as_number) and everything ran.
4. Make it reproducible. I committed a snapshot of the data so anyone can clone the repo and rebuild everything, even if the RPC is down.
With week four down, I look forward to week 5.
Data Tribe: @AnalyticSages
Data Extraction source: @Uniswap V3 pools on Ethereum mainnet
Here's a link to the project: https://t.co/QV60UG6yPC
Week three is done, and this one took me to the cloud.
I pulled every ERC-20 token transfer across 4,000 Ethereum blocks over RPC, saved them as Parquet files in Google Cloud Storage split by date, and connected them to BigQuery with an external table. That's 495,060 transfers I can query with SQL without copying a single row into BigQuery.
Here were some of the key lessons from this week:
1. An external table doesn't hold the data. It just points at the files in GCS. When I extracted more blocks, the same query for 14 Jan went from 81,864 to 302,949 rows without me touching the table.
2. Splitting the data by date saves money. When you filter WHERE dt = '...', BigQuery only opens the files for that day. Try to query without the filter and it refuses to run.
3. Free RPCs once again is not sustainable for data Jobs and it will humble you. They kept rate-limiting me, and the pipeline read every error with the word "limit" in it as "too many blocks", so it kept splitting until it crashed. I switched to my own Infura endpoint and smaller checkpointed chunks, so each re-run picked up where it stopped. I think this was the part where I learnt the most this week.
4. Jupyter Notebook outputs get committed too. I printed my API key in a cell and pushed it to GitHub ๐ Deleted the key immediately, but lesson learnt.
With week three down, I look forward to what's next.
Data Tribe: @AnalyticSages
Data Extraction source: Ethereum mainnet via @infura_io
Here's a link to the project: https://t.co/vvL7Fa3ZuC
Week 2 with @AnalyticSages is done.
This one was closer to what I already do every day.
I built a pipeline that pulls Uniswap V3 swaps directly from Ethereum mainnet using an RPC and https://t.co/uBZVAlHND9, decodes them, and loadsthem into Postgres.
I just give it a block range and it does the rest.
I ran it on 200 blocks across 3 pools (ETH/USDC, WBTC/USDC and USDC/USDT) and it pulled 205 swaps.
Here were some of the key lessons from this week:
1. You can't ask an RPC for too many blocks at once, or it will cap you. So I fetched the swaps in chunks of 10 blocks. 200 blocks became 20 small requests.
2. Raw logs are just hex. I used process_log with the Swap event ABI to turn them into readable data like the sender, amounts, price and tick. I've read this kind of data on Etherscan countless times, but decoding it myself hit different.
3. Running the script twice shouldn't double your data. Every swap can be identified by its tx hash and log index, so I made that the primary key and told Postgres to skip anything already saved. The second run added 0 new rows.
4. Don't just accept a zero. Two of the pools kept returning 0 swaps, and that didn't sit right with me. I checked and there was no contract at either address on Ethereum. One was actually a pool on Arbitrum. We replaced them with working mainnet pools, and I added a check that warns if a pool address is empty. I think this is where my on-chain investigation background really helped.
Week two down and I look forward to what's next.
Data Tribe: @AnalyticSages
Data Extraction source: @Uniswap V3 pools on Ethereum mainnet
Here's a link to the project: https://t.co/yt5RqMctdb
@mikekarnj Most times on a smaller scale then lower prices are justifiable
But when dealing with a large customer base you might see reasons to agree that their prices are justified.
Now, this is not the case all the time.