Extract, Transform, Load (ETL)
Extract, transform, load (ETL) is the process that occurs when moving data from source systems to data warehouses by extracting data from the source, transforming the data, and then loading it to the warehouse.
- most common method used to prepare data for a data warehouse
Extracting Data
Extracting data is the process of accessing the source data from the system and then converting that data into a format that can be transformed and loaded into the data warehouse.
- How you extract data from a system is controlled by that system itself
- source system that stores the data has built-in tools to allow you to extract the data from that system
- typically only need read permissions to extract data
Transforming Data
Transforming data is the act of making the data more meaningful for the purposes of reporting and decision-making.
- also occur when the data must meet the data quality standards for an organization
- can involve many different techniques
- goal is to transition data from its natural state to the format needed for reporting or requirements
Transformation in Data Warehousing
- In data warehousing, the transformation step allows business users to access data that has already been cleaned and transformed
- By implementing a data warehouse and ETL processes,
- business removes the need for every user to complete the same redundant steps to transform the data
- data warehouse provides users with a clean data set ready to meet their reporting needs
- When the data warehouse uses a schema,
- the ETL process is what creates the change in how it is stored and how it will flow into the schema designed
Loading Data
Loading data is the process of moving the data into its target destination, such as a data warehouse.
- different methods can be used to load data into a data warehouse
- type of load used determines how the data is read from the source systems and placed into the data warehouse
- two types of loads:
- Full load
- loading all data into a data system for the first time
- Delta load
- loading new data into a data system and updating any existing data that has changed since the last load
- important to know the time of the delta load to know how up-to-date the information is
- Full load