ETL means extract, transform, load: retrieve data from sources, make it structured and usable, then save it in a target system such as a data warehouse. Because transformation happens before loading, analysts can work with clean, consistent data without repeating the same preparation. section 11.5
ETL is particularly useful when:
- Ingesting data requires complex processing that is better handled outside the warehouse—for example, web scraping with Python rather than warehouse SQL. section 11.2.3
- A source has one known, stable analytical format, so it can arrive ready to use. section 11.2.3
- Sensitive information must be anonymized, pseudonymized, or removed before anything is stored in a data lake or warehouse. section 11.2.3
The alternative, ELT, loads data before transforming it. It can preserve raw data for multiple uses and make new sources available quickly, but increases storage needs and leaves quality problems—and potentially sensitive raw data—to be managed after loading. Choose the order according to the source, available technology, and project needs. section 11.2.3
Figure 11.9 ETL and ELT processes transform the data at different steps, leading to structured data in the warehouse.
The figure locates the transformation step before loading in ETL and after loading in ELT. section 11.2.3
Making weather data usable
A weather-data ETL can retain the input zip code as a join key, format dates as timestamps, and add fields that distinguish saved forecasts from the most recent forecast. Those choices let teams join weather to business data, examine forecast history, and serve operational dashboards. section 11.3.1
Put quality checks in the transformation step, before errors reach downstream users. For a daily forecast, checks can reject duplicate dates, humidity outside 0–100, negative wind speeds, and missing values. Also monitor whether scheduled workflows updated their targets: a well-structured table is not useful if it is stale. section 11.3.2 section 11.3.2
