ETL (Extract, Transform, Load)
A data integration process that pulls raw data from multiple sources, converts it into a clean and structured format, and loads it into a centralized database or data warehouse for analysis.
Open-source projects like Google's blockchain-etl export Ethereum and Bitcoin data to Google BigQuery, allowing users to write standard SQL queries against the entire history of the blockchain without running their own archive nodes.
graph LR
Center["ETL (Extract, Transform, Load)"]:::main
Rel_indexing_search["indexing-search"]:::related -.-> Center
click Rel_indexing_search "/terms/indexing-search"
Rel_consensus_mechanism["consensus-mechanism"]:::related -.-> Center
click Rel_consensus_mechanism "/terms/consensus-mechanism"
Rel_edge_computing["edge-computing"]:::related -.-> Center
click Rel_edge_computing "/terms/edge-computing"
classDef main fill:#7c3aed,stroke:#8b5cf6,stroke-width:2px,color:white,font-weight:bold,rx:5,ry:5;
classDef pre fill:#0f172a,stroke:#3b82f6,color:#94a3b8,rx:5,ry:5;
classDef child fill:#0f172a,stroke:#10b981,color:#94a3b8,rx:5,ry:5;
classDef related fill:#0f172a,stroke:#8b5cf6,stroke-dasharray: 5 5,color:#94a3b8,rx:5,ry:5;
linkStyle default stroke:#4b5563,stroke-width:2px;
🧠 Knowledge Check
🧒 Explain Like I'm 5
Imagine you are baking a cake using ingredients from different stores. **Extract** is going to the store, the farm, and the mill to get eggs, milk, and wheat. **Transform** is cracking the eggs, pasteurizing the milk, and grinding the wheat into flour in your kitchen. **Load** is putting the mixed batter into the cake pan to bake. In software, ETL takes raw, messy data from different apps, cleans it up, and puts it into one big, organized [database](/en/terms/database) so managers can read clear reports.
🤓 Expert Deep Dive
ETL vs. ELT Architecture
Traditional ETL requires a dedicated transformation server (e.g., Informatica, Talend) and limits the data warehouse to storing only the final, curated models. This requires rigid schema-on-write. ELT (enabled by tools like dbt - data build tool) loads raw data into the warehouse first (schema-on-read), leveraging the massive parallel processing (MPP) capabilities of Snowflake or BigQuery to transform data via SQL. ELT preserves raw data, allowing analysts to rebuild transformations retroactively.
Blockchain ETL Complexity
Extracting blockchain data is notoriously difficult due to chain reorganizations (reorgs) and node unreliability. A robust Blockchain ETL pipeline must handle 'uncle' or 'orphaned' blocks gracefully. Transformation requires ABI (Application Binary Interface) decoding. For example, a raw Ethereum event log contains topics (indexed parameters) and data (unindexed parameters) encoded in ABI format. The ETL pipeline must map the Keccak-256 hash of the event signature (e.g., Transfer(address,address,uint256)) to the raw log, then parse the hex strings into standard SQL types (addresses, timestamps, large integers/BigInts) before loading.
❓ Frequently Asked Questions
What is the difference between ETL and ELT?
In ETL (Extract, Transform, Load), data is transformed in a separate processing engine before it is loaded into the data warehouse. In ELT (Extract, Load, Transform), raw data is loaded directly into the data warehouse, and transformations are performed inside the warehouse using its own computing power (e.g., using SQL in Snowflake or BigQuery).
Why is the Transform step necessary?
Raw data from different sources is often messy and incompatible. Dates might be in different formats, databases might use different ID schemes, and text might contain errors. Transformation cleans, standardizes, and joins this data so that it can be accurately analyzed.
How is Blockchain ETL different from traditional ETL?
Blockchain data is extracted from RPC nodes in a raw, unreadable hexadecimal format. The transformation step requires an ABI (Application Binary Interface) to decode these hex strings into readable function calls and event logs. Additionally, blockchain ETL must handle chain reorganizations (where recent blocks are rewritten).