T3 · Onchain Data Engineer
Turn scattered chain bytes into numbers others trust for decisions.
For data engineering and analytics.
“T3” here means this track. Linked “Chapter T17” means a course chapter; Chapter T3 follows a transaction from code to chain.
Who This Track Serves
You fetch data with scripts but cannot prove completeness. RPC loops and a table work, yet you cannot establish whether blocks are missing between genesis and today.
Your numbers differ from others without a known explanation. Daily volume differs by 8%, and your definition is scattered across SQL and scripts rather than explicit.
One reorganization led to many patches. They handle the observed case but not the next; the correct structure remains unclear.
You can query but cannot build. Complex platform SQL is familiar; constructing a pipeline from RPC and separating layers is not.
The dividing line: deliver a rerunnable pipeline with byte-identical results, rather than merely data.
Prerequisites
The path is long because decoding bytes into meaning requires contract standards. T13 depends on T9; T16 and T17 depend on that path.
- Chapter T1
- Chapter T3
- Chapter T4
- Chapters T7–T9
- Chapters T13–T15
- Chapter T16
- Chapter T17
- Chapter T25
| Required reading | Graph rationale |
|---|---|
| Chapter T1 · Blockchain Data | State is fact; events are projections. This determines layers |
| Chapter T3 and Chapter T4 | T25 depends on T3. RPC rate limits, paging, archive coverage bound the pipeline |
| Chapter T7 through Chapter T9 | ABI and standards turn numbers into transfer meaning; nonstandard implementations are historical dirty data |
| Chapter T13 through Chapter T15 | Required path to T16; idempotency and limits recur in pipelines |
| Chapter T16 · Databases and Onchain Data | Events as recorded facts, block heights as versions: modeling foundations |
| Chapter T17 · Indexer | Reorganizations, resumption, idempotency |
| Chapter T25 · Blockchain Data Infrastructure | Depends on T3 and Chapter T20; layers, partition keys, manifests |
T25's T20 dependency matters: data engineering requires business meaning. Indexing swaps requires understanding routes, pools, and quotes before designing tables.
What You Will Learn
- RPC
- Scanner
- Indexer
- ETL
- Postgres
- ClickHouse
- DuckDB
- Data Pipeline
Eight topics form four groups, each addressing one failure mode.
1. Collection: Know What Is Missing
Always answer which height is covered and whether gaps exist.
Practice node fallback and consistency comparisons, batch paging and rate limits, archive coverage, and saving progress only after data. A crash after a premature checkpoint permanently loses a range.
2. Processing: Identical Results on Replay
Keep raw bytes undecoded, decode structures by ABI, and aggregate business meaning separately. This avoids repeating costly collection when decoding changes.
Partition by block time rather than server time; partitions are entirely present or absent; reorganization rollback removes complete height ranges.
3. Storage: Choose Engines by Role
Three engines complement rather than replace one another:
| Role | Strength | Contents |
|---|---|---|
| Relational database | Transactions, constraints, point lookups | Cursors, checkpoints, strongly consistent tables, API read models |
| Column store | Large scans and aggregations | Decoded events and time-range analytical tables |
| Embedded analytics | Local/object-store columnar files without operations overhead | Exploration, backfill validation, one-off measurement comparisons |
Wrong choices cause slow relational aggregations over hundreds of millions of events or missing transaction semantics for column-store checkpoints.
4. Delivery: Numbers Others Can Question
The output is defensible numbers, not tables. Every metric specifies source tables and filters, covered heights, and update time.
APIs also need pagination, limits, caching, and explicit freshness.
Final Deliverable
A replayable complete indexing pipeline and external query interface.
The Capstone Project indexes one layer of a complete product. Here complete means all historical dirty data; replayable means no manual repairs.
Others can run it. One command starts dependencies; another indexes from a specified height. Document RPC/archive requirements, backfill duration, and fallback starting heights.
Provide a sample query exposing the first number.
Two runs of the same height range produce identical bytes. This defines replayability and is nonnegotiable.
Delete outputs and rerun an indexed range, comparing checksums. Mismatches reveal nondeterminism, often server time, unordered iteration, or random batch boundaries.
Record a real reorganization drill. Construct or replay a fork, demonstrating complete deletion and correct replacement.
Specify assumed depth and behavior beyond it—explicitly document exclusions.
Every external metric has a definition and coverage range. Include definition, source tables/filters, heights, and last update.
Responses carry freshness. Undated numbers can be more dangerous than no numbers.
Cross-check independently and report differences honestly. Recompute one metric from state or comparable public-platform queries.
Differences need not vanish, but explain every percentage point. Explained differences demonstrate understanding; unexplained ones are bugs.
Assess Your Readiness
Crashes can occur between writes.
Data first leaves a lagging checkpoint; restart repeats data harmlessly with idempotent writes.
Checkpoint first marks absent data complete, so it never gets replayed, creating an invisible gap.
Repeat rather than skip.
Replay requires determinism. Write-time partitions place identical data into different directories on different days.
Backfilling three-month-old history into today's partition also breaks time pruning.
Timestamps come from block headers rather than your server.
Logs are emitted projections, not state itself. Three biases recur:
Some state changes emit no events: direct transfers, internal balance changes, and older nonstandard implementations disappear.
Upgrade semantics can change despite identical event signatures; one ABI across history misinterprets them.
Failed transactions discard logs but consume gas and change nonces. Counting only successful interactions understates activity.
Use logs for event streams and independently check state quantities such as balances and positions.
Partition by block-derived date with a uniform timezone; sort within partitions by height and log index.
Dates support human time-range queries; heights support writes, deduplication, and replay, allowing skipping and exact coverage inspection.
Avoid hourly fragmentation and small-file overhead. Manifests record rows, heights, checksum, and decoder version.
Check:
- Can definitions change safely without notifying anybody? Otherwise version metrics.
- How quickly do consumers learn of errors? Automated consistency checks should precede complaints.
- Can someone rerun failed backfills during your two-week holiday? This tests documentation.
Common Mistakes
“Aggregate First; Raw Data Is Too Large”
Direct RPC-to-metrics saves storage until decoding fails. Raw data permits minutes of redecoding; without it, days of collection depend on providers.
Retain undecoded bytes. Their storage cost insures against inevitable decoding mistakes.
“Reorganizations Are Rare; Ship First”
They are normal chain behavior and cause silently incorrect ranges, rather than obvious errors, affecting balances and metrics without alerts.
Design rollback from day one, with explicit depth assumptions and beyond-boundary behavior.
“A Few Percentage Points Do Not Matter”
Users cannot know whether 8% becomes 80% elsewhere. Unwritten definitions make differences untraceable.
Provide executable definitions and independent comparisons. Explained differences are understanding; unexplained differences are bugs.
“Streaming Eliminates Backfills”
Streams address latency; batches address completeness. Outages permanently lose data in stream-only systems.
Keep both producing identical results, with batches able to replace streamed data consistently. Chapter T18's replayability covers this.
Next Steps
- Reread with your pipeline: Chapter T16, Chapter T17, Chapter T25.
- Read Chapter 23 · Onchain Observation: business meaning ultimately determines measurement correctness.
- Combine Track T4 · DeFi Engineer for indexed semantics, Track T6 · AI × Crypto Engineer for agent data, and Track T2 · Solana / SVM Engineer for non-EVM forms.
- Cross into Track N1 · Crypto Research & Investment. Independent data collection is easy for you, but often researchers' hardest section.
- Integrate the pipeline into a product through the Capstone Project.