Data processing converts raw data into a usable format for analysis. It can happen at any point in a data pipeline, before or after data is stored in a warehouse. section 11.2.3
ETL transforms data before loading it; ELT loads raw data first and transforms it later. ETL can suit complex transformations, static formats, or sensitive data that should be scrubbed before storage. ELT can suit raw data that may support multiple uses, but can increase storage costs and leave quality issues to address in the warehouse. section 11.2.3
Choose a processing approach based on the project’s needs and available technology:
- Consider ETL when transformations are complex, data has one fixed use, or sensitive information should not be stored raw. section 11.2.3
- Consider ELT when raw data may be reused in different ways or is too large to transform in memory before saving. section 11.2.3
- For large processing tasks, parallel processing divides work into chunks and recombines them into the output. section 11.2.3
Figure 11.9 ETL and ELT processes transform the data at different steps, leading to structured data in the warehouse.
Compare where ETL and ELT place transformation in the data pipeline. section 11.2.3
Table 11.1 ELT can offer significant advantages in terms of speed and flexibility of use, but it will result in higher volumes of data being stored in your organization’s infrastructure.
|
ELT advantages
|
ELT disadvantages
|
|---|---|
|
Data can be analyzed and represented in numerous ways
|
Increased data storage costs
|
|
Data can be updated and surfaced very quickly
|
Data quality problems need to be handled in the warehouse
|
|
New data sources can easily be added to the warehouse, compared to ETL
|
There are security risks with saving raw, sensitive information in the warehouse
|
Review ELT’s speed and flexibility alongside storage, data quality, and security trade-offs. section 11.2.3
